物料需求运算表优化实战:从“卡顿”到“流畅”的转变 300

古哥计划
古哥计划

优秀创作者

案例分享:近期,古老师接到了来自某工厂内部一位物料控制专员的紧急求助,他表示在日常工作中遇到一个棘手的问题——他们所依赖的一张关键物料需求运算表在打开过程中出现了显著的延迟现象,表现为响应速度极慢,操作过程极度“卡顿”,这严重影响了工作效率。因此,该专员迫切希望古老师能够运用专业知识和技术手段,深入探究这一问题的根本原因,并提出针对性的优化解决方案,以期显著提升物料需求运算表的运行效率和用户体验。

在收到该物料控制人员发送过来的表单后,古老师对其进行了细致入微的检查,最终找出了问题的关键所在。原来,该物料需求运算表应用了一些效率较低的公式来处理大规模的数据运算任务,而鉴于该工厂目前尚未采用信息化管理系统,大量的物料需求数据均需借助这张表格承载,其中物料记录已超过20000行之多,这就加剧了因公式效率低下而导致的运算性能瓶颈问题。

因此,针对上述问题,古老师采取了利用数据透视表以及替换原有低效函数公式的策略来进行优化。这两种方法各自具有独特优势,在实际应用中相得益彰。经过一番精心调整与测试后,物料需求运算表的运行效率得到了显著提升,从而有效解决了打开速度慢、操作卡顿的问题。

数据透视法

数据透视表是那些尚未实施信息化软件管理、却需处理数万乃至数十万行数据分析任务的工厂的理想工具。该方法操作简便,且具备出色的运算速度。然而,这种方法存在一个局限性:每当源数据有所增加时,就需要手动更新并重新定义数据透视表的范围,无法实现与源数据的实时同步更新。

优化前:

计算总需求用的公式为:

=SUMIFS(D:D,C:C,C9994)

SUMIFS函数在处理少量条件求和场景时,无疑是一个极佳的选择,然而,当数据规模超过一万行以上时,其运算效率则会明显下降,成为亟待解决的关键问题。为此,此处我们采用了数据透视表结合引用的方式,以应对此类大数据量下的条件求和的难题。

优化后:

创建一个透视表,通过透视料号和销售需求得到汇总需求数量,再录入函数:

=XLOOKUP(C10002,需求透视!$A$3:$A$10000,需求透视!$B$3:$B$10000)

并下拉填充,得到下图所示结果

不足之处在于:每当此表中的数据需求有所增加时,需要手动扩展公式范围,并同步更新数据透视表。当前公式引用的范围限定为A3:A10000,若超出这一范围,则必须手动调整引用区间。

在此需要注意的是,在编写公式时应避免使用类似“A:A”、“B:B”这种全列引用方式,因为一旦如此操作,将意味着引用了整列数据(例如A列可能多达104万行),这不仅会导致计算效率严重降低,还可能造成不必要的资源消耗。也是造成表格运算慢的原因之一。

更换高效公式

优化前:

在计算物料累计需求时,使用了SUMIFS函数配合锁定单元格的写法,如=SUMIFS($D$3:D9929,$C$3:C9929,C9929),并且这是一个随着单元格下拉填充而逐行进行条件累计运算的公式设定。在这种情况下,公式每向下填充一行,就需要对整个累计区域进行一次计算,这就必然导致运算速度极其缓慢。

优化后:

首先对原始数据进行合理排序,然后通过条件判断构建循环机制,以逐一引用每一行数据的方式取代原公式中每向下填充一行就需要遍历并计算整个累计区域的做法。这样一来,可以有效减少不必要的重复计算,提高运算效率。

步骤一:排序数据公式:=SORT(B3:D10002,2),对源数据中第2列料号进行排序,使得相同的料号排在一起。

使用以下公式:

=LET(A,SORT(B3:D50000,2),FILTER(A,CHOOSECOLS(A,1)<>0))

可以让表格更加具有扩展性,即每当表1数据发生变化时,此处数据亦能随之自动更新。

步骤二:

把上面排序的结果转换成一列的公式:

录入公式:

=CHOOSECOLS(LET(A,SORT(B3:D50000,2),FILTER(A,CHOOSECOLS(A,1)<>0)),2)

再输入累计求和公式:

=SCAN(0,G3#,LAMBDA(X,Y,LET(A,OFFSET(Y,,1),IF(Y=OFFSET(Y,-1,),X+A,A))))

总需求:

公式1:=UNIQUE(G3#)

公式2:=XLOOKUP(K3#,G3#,I3#,,,-1)

通过采用上述动态数组公式代替低效率公式,不仅实现了数据的实时联动更新,更显著提升了物料需求运算表格的运算效率,从而成功解决了该物控员所提出的表格运算慢的问题。

最后总结

在处理包含超过1万行数据的表格时,应格外注意避免使用可能导致运算速度大幅降低的函数,如SUMIFS、COUNTIFS等,并且要限制不合理的引用范围,尽量不要采用全列引用如A:A、B:B。对于累计计算的部分,应当减少锁定首行的引用范围写法,

例如:$D$3:D9929,$C$3:C9929,

建议优先考虑采用数据透视表技术和一些高效动态数组公式来替代,例如SORT、XLOOKUP、SCAN等功能,这些方法可以在处理大量数据时显著提高运算效率。

299 WPS表格自动化编号:升序与累计编号的实用方法

298 输入法配合WPS表格公式,轻松实现虚拟数据批量生产:助力讲师快速培训演示

297 探究二维与一维报表的奥秘:掌握三种实用转换策略,应对庞大数据量挑战

296 从理论到实践:WPS 内测SCAN函数在销售金额稳定性评估中的神奇运用

295 新旧交织,效率升级:WPS表格新函数REDUCE、BYROW等在复杂业务场景下的灵动

294 WPS内测版新功能揭秘:“SCAN”迭代函数的运用与实践

293 借助WPS动态数组公式实现智能扩展填充与数据转换自动化

292 MRP原理课后的表格制作练习题目:技能与实战双丰收

运用WPS/Lambda及Scan函数实现动态数组累计需求分析 No291

巧用WPS AI,为上百名女员工打造3·8女神节专属祝福与精美卡片 290

从单一公式到复杂逻辑:通过实战案例揭示PMC面试中数据分析与解决问题的能力区分 No 289

MRP多阶需求运算报表-WPS表格版本 (8 No 288

MRP多阶需求运算报表-WPS表格版本 (7) No 287

MRP多阶需求运算报表-WPS表格版本 (6) No 286

MRP多阶需求运算报表-WPS表格版本 (5) No 285

MRP多阶需求运算报表-WPS表格版本 (4) No 284

MRP多阶需求运算报表-WPS表格版本 (3) No 283

MRP多阶需求运算报表-WPS表格版本 (2) No 282

MRP多阶需求运算报表-WPS表格版本 (1) No 281

巧用WPS中UNIQUE与SUM函数,一步解决跨门店商品库存成本合计问题 No 280

利用XLOOKUP快速实现培训未参加人员的扣分 NO 279

278 挑战WPS AI 写出复杂的多层嵌套公式

277 超预期,原来WPS AI也可以写出复杂的多层嵌套公式

276 巧妙用VSTACK、HSTACK实现出货货柜合并的表格变形

275 录入查询产品型号的首字,提取对应首字相同的所有型号,并按销量降序

表格中录入简称查找包含全称的所有数据 No.274

浙江省
浏览 716
1
2
分享
2 +1
1
1 +1
全部评论 1
 
HC.旋
666
· 江苏省
回复