案例分享：近期，古老师接到了来自某工厂内部一位物料控制专员的紧急求助，他表示在日常工作中遇到一个棘手的问题——他们所依赖的一张关键物料需求运算表在打开过程中出现了显著的延迟现象，表现为响应速度极慢，操作过程极度“卡顿”，这严重影响了工作效率。因此，该专员迫切希望古老师能够运用专业知识和技术手段，深入探究这一问题的根本原因，并提出针对性的优化解决方案，以期显著提升物料需求运算表的运行效率和用户体验。
在收到该物料控制人员发送过来的表单后，古老师对其进行了细致入微的检查，最终找出了问题的关键所在。原来，该物料需求运算表应用了一些效率较低的公式来处理大规模的数据运算任务，而鉴于该工厂目前尚未采用信息化管理系统，大量的物料需求数据均需借助这张表格承载，其中物料记录已超过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等功能，这些方法可以在处理大量数据时显著提高运算效率。
我是古哥计划，专注生产计划18年，头条号作者,职场问答专家，优质职场领域创作者。关注我，每天学习PMC相关知识。
