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

2024年03月优秀创作者

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

在收到该物料控制人员发送过来的表单后，古老师对其进行了细致入微的检查，最终找出了问题的关键所在。原来，该物料需求运算表应用了一些效率较低的公式来处理大规模的数据运算任务，而鉴于该工厂目前尚未采用信息化管理系统，大量的物料需求数据均需借助这张表格承载，其中物料记录已超过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)，并且这是一个随着单元格下拉填充而逐行进行条件累计运算的公式设定。在这种情况下，公式每向下填充一行，就需要对整个累计区域进行一次计算，这就必然导致运算速度极其缓慢。