公式自动化实战分享：下拉VS动态数组VS扩展填充技巧

优秀创作者

全文约2300字；

阅读时间：约8分钟；

听完时间：约12分钟；

在PMC生产计划的日常工作中，涉及众多表格应用场景，诸如排程表、物料需求表和欠料表等。这些表格不仅包含丰富多样的数据，还融入了各式表格函数，极大地提升了工作效率。新版本的表格软件更是引入了多种函数输入方法，从最普遍的下拉填充法到便捷的数组一键填充法等，极大丰富了操作方式。

今天，古老师将为我们分享他在运用这些不同函数写法过程中的心得与体会。

下拉填充法

WPS中的公式下拉填充法是一种常用的功能，它允许用户快速地将一个单元格中的公式应用到下方的一系列连续单元格中。

以下图中统计工艺顺序的案例的说明，具体操作步骤如下：

准备公式：首先，在一个单元格内输入或编辑好你需要的公式。在单元格E4中输入了一个求和公式=COUNTIFS($B$4:B4,B4)。

选择单元格：将鼠标指针移到该单元格的右下角，这时鼠标指针会变为一个小十字形状，称为“填充柄”。

下拉填充：

简单下拉：点击并按住填充柄，向下拖动到你想要填充公式的最后一个单元格位置，松开鼠标，公式会根据相对引用自动调整并应用于所选的每个单元格。

双击填充：另一种更快的方式是，当你看到填充柄时，直接双击左键，Excel会自动识别并填充到数据结尾或遇到空白单元格为止。

效果如下图所示：

填充的优势：

简便操作：仅需结合公式录入与鼠标拖曳，就能迅速完成公式的批量填充。

灵活引用：能够轻松依据上下单元格关系，动态引用本列数据，提升数据处理的灵活性。

填充的劣势：

易引发引用错误：若未留意相对引用的特性，可能导致填充时引用范围出现偏差，影响数据准确性。

空行干扰问题：双击填充功能在遭遇数据间的空行时，可能提前终止填充过程，未能延伸至数据末尾。

动态数组法

使用动态数组的公式写法相比传统的下拉填充方法，展现出多方面的优势，其中最显著的是其一键展开的高效性，彻底告别了手动拖拽公式的过程。

此外，由于动态数组公式自动适应数据范围，它自然而然地规避了手动引用单元格时可能遇到的问题，这意味着在多数情况下无需再费心考虑使用绝对引用（即锁定单元格的美元符号）的问题，大大简化了公式设计的复杂度。

要将用于统计产品工艺顺序的常规公式：

=COUNTIFS($B$4:B4, B4)

转换为采用数组功能的公式，可以借助MAP和LAMBDA函数实现如下：

=MAP(B4:B12, LAMBDA(X, COUNTIFS(B4:X, X)))

公式解释：

此数组公式运用了MAP函数，它遍历从B4到B12的范围（代表产品工艺序列），对每个元素X应用一个由LAMBDA定义的匿名函数。这个匿名函数计算在从B4到当前元素X的范围内，与X相同的值的数量，即计算X在该连续范围内的累计出现次数。这样，对于列表中的每一个项目，都能直接得到其在当前观测范围内的累计计数，实现了工艺顺序的高效统计。

效果如下图所示：

可扩展公式：

传统填充公式会根据实际数据量自动扩展，即数据有多少，公式便填充多少。相比之下，数组公式在应用时并不依赖于手动拖拽填充，而是预先设定好目标范围，通过“一键”计算完成整个区域的填写。为了确保函数设计的高效性和未来数据的兼容性，关键在于增强数组公式的可扩展性。

为此，在设计数组公式时，可以通过扩大引用范围并结合使用FILTER等筛选函数来策略性地应对预留空间可能引入的空值问题。这样做不仅预先规划了数据的增长空间，还确保了计算结果的精确性。

将动态数组公式调整为：

=MAP(B4:B120, LAMBDA(X, COUNTIFS(B4:X, X)))

此时，映射范围已扩展至B4至B120（实际应用中，此范围应根据具体情况灵活调整）。扩展后，你可能会注意到在数据序列末端出现了多余的“0”值。为解决这一问题，接下来的步骤便是将这些“0”值过滤出去。

定义这个结果为A后，录入以下函数：

=LET(A,MAP(B4:B120,LAMBDA(X,COUNTIFS(B4:X,X))),FILTER(A,A<>0))

该公式释义如下：

首先，通过LET函数定义了一个名为A的变量，其值来源于先前设定的动态数组公式，该公式计算了B4至B120范围内每个项目的累计出现次数。

接着，利用FILTER函数从数组A中筛选出所有不等于0的值，以此移除扩展范围导致的多余“0”项，确保结果的精确性与实用性。

效果如下图所示：

此公式的一大优点在于，当数据区域B4:B120有新增内容时，公式能够自动扩展其效果，从而真正实现全自动化处理，无需手动拖拽填充或调整公式引用范围，极大提升了效率。

另一种实现动态扩展并过滤空值的方法是采用TOCOL(区域, 3)函数。该函数的作用是将数组转换为一列数据，其中参数3指示函数同时排除错误值和空值。选取一个足够大的列范围与TOCOL函数的这一特性相结合，就能有效地达到与筛选非零值相似的效果，即自动忽略任何后续扩展中可能出现的空白单元格，保持数据的连贯性和准确性。

筛选法适用于那些函数参数不直接支持数组形式的情况，而TOCOL转换法则特别适合于那些能够直接处理数组作为参数的函数应用场景。因此，在具体实践中，应根据函数特性和实际需求灵活选择这两种方法，以实现数据处理的最佳效果。

最后总结

在总结今日的分享中，古老师不仅深入浅出地对比了下拉填充法与动态数组法在PMC生产计划表管理中的应用，还展示了如何通过巧妙的函数设计与技巧提升工作效率。下拉填充以其操作简便和灵活性，在处理简单序列和快速填充方面表现出色，尽管存在一定的局限性，如容易因疏忽导致的引用错误及空行问题。而动态数组公式，特别是结合MAP、LAMBDA以及FILTER或TOCOL的使用，展现了强大的自动化和扩展能力，不仅简化了复杂计算，还确保了数据处理的准确性和前瞻性。

古老师的实践心得强调了在实际工作中灵活选用方法的重要性。当面对数据变化频繁、需要高度自动化处理的场景时，动态数组法及其配套函数的组合成为优选，它们能够有效适应数据增长，减少人工干预，保障数据统计的连续性和可靠性。反之，在处理简单引用或对现有数据进行快速格式化时，下拉填充依旧不失为一种便捷的选择。

总之，无论是追求极致效率的动态数组自动扩展，还是依赖于直观操作的下拉填充，关键在于理解每种方法的适用场景与限制，结合工作实际灵活应用。古老师的分享不仅是对函数技巧的传授，更是对工作智能化、高效化理念的倡导，鼓励我们在日常的PMC管理乃至更广泛的表格处理任务中，不断探索、优化我们的工具箱，以技术赋能生产计划，驱动业务流程的持续改进。

351 WPS表格16729版升级挑战：动态数组功能与#SPILL!错误详解

350 PMC管理库存监控新视角：日度变动量解析与实战策略

349 WPS正则表达式实战：快速破解员工住址信息整理难题

348 从杂乱无章到井然有序：优化PMC收发货数据分析实录

347 优化办公流程：MAP函数巧解非标日期，提速数据分析

345 销售数据深度透视：‘首尾分析’引领PMC决策新境界

344 智能化革新！PMC专员如何一键优化订单追踪报表

343 告别手动核对，WPS智能公式优化工厂五一假日排班管理

342 智能订单管理：一键自动化编号，重塑PMC效率巅峰

341 SCAN函数应用：连续达标奖金计算法：高效自动化策略激发生产力

340 仓库管理革新：WPS之PIVOTBY函数引领出入库数据转型新篇章

339 BYROW XLOOKUP革新：采购数据分析的智能提速策略