告别手动更新：WPS动态数组公式助力项目跟进表实现全自动化统计

优秀创作者

在WPS尚未引入动态数组功能的旧版本中，当数据发生变动时，应对公式无法实时同步问题可采取两种策略。其一，预先将公式填充至一个较大范围；其二，借助数据更新过程同步进行公式的填充操作，以实现同步效果。

上述两种方法及数据透视表均存在一个共同问题，即无法实现公式或数据透视表的实时同步，用户需手动填充公式或手动刷新数据透视表。为解决这一问题，我们有必要引入WPS全新推出的动态数组公式，以实现公式自动填充的实时同步功能。

今日，古老师通过引用一个案例，生动阐释了在由传统公式法与数据透视表转换为动态数组后，所实现的数据更新，公式自动同步效果。下图展示了一个项目跟进表，每天，负责人员会在此表中录入新增项目信息。其中，序号为人工填写，统计结果亦由手动计算得出。特别是在项目状态发生变动时，还需在统计表中手动添加相应的状态标题列。显然，这样的操作方式无法实现一键全自动化。

借助WPS新引入的动态数组函数功能，我们将逐步构建解决方案，旨在实现项目跟进表的一键统计状态结果与自动编号功能。一键统计过程中，系统将按照项目负责人对各自负责项目进行汇总，包括对应项目的各类状态数据（如备案数量、成交数量、报价数量），并依据跟进项目数量进行降序动态排序，突出显示跟进项目最多的负责人。

自动编号

首先用编号函数配合统计函数进行自动编号，录入以下公式：

=SEQUENCE(COUNTA(C3:C4000))

函数解释：

COUNTA(C3:C4000)

COUNTA 函数的作用是计算指定区域内非空单元格的数量。在这个例子中，它统计从C3到C4000范围内所有非空单元格的个数。用来代表实际存在的项目数量或者数据条目的总数，因为空白单元格不会被计入。

SEQUENCE(n)

SEQUENCE 函数在Excel中创建一个动态数组，生成一个从1开始、递增步长为1的整数序列，序列包含 n 个元素。这里的 n 应该是一个具体的数值，代表序列中元素的总数。

此公式将 COUNTA(C3:C4000) 的结果作为 SEQUENCE 函数的参数 n。这意味着 SEQUENCE 将根据C3到C4000区域内非空单元格的实际数量，生成相应长度的整数序列。这个序列将自动适应数据的变化，即每当该范围内新增或删除非空单元格时，序列的长度会随之调整，始终保持与有效数据项的数量一致。用于为项目跟进表中的项目自动编号，确保编号与实际项目数量始终保持同步。

二维条件

对项目跟进表进行统计分析的一种较为高效的方法是采用二维统计分析法。在此方法中，我们可以设定两个关键维度：

垂直维度：项目负责人，用以区分不同人员负责的项目。

水平维度：项目状态，涵盖诸如“备案中”、“已成交”和“已丢单”等多种状态类别。

通过构建这样纵横交错的二维框架，我们能够清晰地组织和呈现项目数据。接下来，借助于各类统计函数，即可对这些按维度划分的数据进行深入分析，轻松得出所需的各种统计数据。这样的统计过程不仅结构化、系统化，而且能够有效地揭示项目进展及负责人业绩等方面的内在规律与关联，极大地提升了统计分析的效率与准确性。

为了创建动态数组区域，分别录入以下函数：

垂直维度：=DROP(UNIQUE(C3:C4000), -1)

水平维度：=TOROW(DROP(UNIQUE(E3:E4000), -1))

公式解释：

对于垂直维度：

使用UNIQUE 函数对用户列 C3:C4000 进行去重处理，得到项目负责人列表中的唯一值。预设这个大范围（C3:C4000）是为了确保在该范围内更新数据时，动态数组能自动扩展以适应数据变化。

鉴于预留范围可能包含空白单元格，UNIQUE 函数处理后的数组末尾可能会出现零值。为消除这一影响，使用 DROP 函数结合其最后一个参数 -1，表示丢弃数组的最后一个元素。这样一来，我们成功获取了项目负责人列表的唯一值，且无冗余零值。

对于水平维度：

同样采用UNIQUE 函数对 E3:E4000 范围内的项目状态进行去重，以获得所有唯一的项目状态类别。

由于水平方向上的数据需要转换为行向量以便于后续操作，这里使用TOROW 函数将去重后的项目状态数组转化为单行的动态数组。

总之，通过上述函数的运用，我们分别构建了项目负责人（垂直维度）和项目状态（水平维度）的动态数组区域，确保在数据更新时，这些数组能自动扩展并保持唯一性，为后续的二维统计分析提供了基础。

效果如下图所示：

统计数据

在获取到垂直维度（项目负责人）和水平维度（项目状态）的动态数组数据后，我们便可以利用统计函数对表1的项目跟进表进行多条件统计。统计的两个条件分别是项目负责人和项目状态。为此，我们输入以下公式以得到动态统计结果：

=COUNTIFS(C3:C4000, G3#, E3:E4000, H2#)

函数释义：

统计区域：C3:C4000，代表项目负责人列。由于预留了C3:C4000这一大范围，当表内数据更新时，统计区域能够自动扩展，确保统计结果的准确性和时效性。

条件1：G3#，此处引用了垂直维度动态数组的结果。该数组由公式 =DROP(UNIQUE(C3:C4000), -1) 生成，包含了项目负责人列的唯一值。在统计时，G3#表示当前统计行对应的项目负责人。

条件2：E3:E4000，指定了项目状态列，同样预留了足够的范围以实现动态扩展。

条件3：H2#，对应水平维度去重后的状态值。该值来源于水平维度动态数组，该数组由公式 =TOROW(DROP(UNIQUE(E3:E4000), -1)) 创建，包含所有唯一的项目状态类别。在统计时，H2#表示当前统计列对应的项目状态。

综上所述，通过公式=COUNTIFS(C3:C4000, G3#, E3:E4000, H2#)，我们得以基于项目负责人和项目状态这两个条件，对项目跟进表进行动态统计，得出每个负责人在特定项目状态下对应的项目汇总数量。实际效果如附图所示。

统计项目

经过上述操作，我们已经清晰地了解到每个项目负责人所对应的各个项目状态及其数量，例如负责人1对应的项目状态分布为：备案中4个、已成交5个等。在此基础上，为了进一步计算每个项目负责人的总项目数，传统方法固然可以采用SUM函数直接求和，但鉴于SUM函数无法适应动态数据环境，我们转而采用WPS办公软件中的最新函数BYROW，以实现动态统计。请输入以下公式：

=BYROW(I3#,SUM)

函数释义：

I3#：此区域为先前使用函数 =COUNTIFS(C3:C4000, G3#, E3:E4000, H2#) 得到的统计结果，即每个项目负责人对应各项目状态的具体数量。I3# 代表这些统计结果所在的动态数组区域。

BYROW：WPS提供的新函数，用于对指定区域内的每一行数据应用指定的计算逻辑。在这里，我们用它来遍历 I3# 区域中的每一行（即每个项目负责人的所有状态统计值）。

SUM：即对当前行（即某个项目负责人所有状态的统计数量）进行求和，返回该负责人的总项目数。

综上所述，WPS的BYROW函数结合SUM的表达式，实现了对每个项目负责人所有项目状态数量的逐行求和，从而得到每个负责人汇总的项目总数，且该结果能够随数据动态变化而自动更新。

效果如下：

自动排名

通过上述公式，我们已成功实现了一键统计项目跟进表的各项指标。然而，尚有一个需求未能满足，即根据每个项目负责人跟进项目的数量进行自动降序排序，来突出显示跟进项目最多的负责人。因此，我们需要继续设计相应的公式以达成这一目标。

该公式的实现原理相对复杂，其核心在于运用VSTACK和HSTACK函数将分散的动态数组区域整合为一个整体，随后利用SORT函数按照项目负责人跟进项目的数量进行降序排列。以下是完整的公式： =SORT(LET(A,DROP(UNIQUE(C3:C4000),-1),B,TOROW(DROP(UNIQUE(E3:E4000),-1)),HSTACK(VSTACK("项目负责人",A),VSTACK("汇总",BYROW(COUNTIFS(C3:C4000,A,E3:E4000,B),SUM)),VSTACK(B,COUNTIFS(C3:C4000,A,E3:E4000,B)))),2,-1)

公式解析：

LET函数：用于定义多个中间变量，并在后续计算中引用它们。这里定义了两个变量：

A: 通过 DROP(UNIQUE(C3:C4000), -1) 得到项目负责人列的唯一值（去除末尾可能存在的零值）。

B: 通过 TOROW(DROP(UNIQUE(E3:E4000), -1)) 得到项目状态列的唯一值，并转换为单行动态数组。

HSTACK函数：水平堆叠多个数组，形成一个新的二维数组。这里堆叠了三个部分：

VSTACK("项目负责人", A)：垂直堆叠字符串"项目负责人"与项目负责人列表A，构成第一列。

VSTACK("汇总",BYROW(COUNTIFS(C3:C4000,A,E3:E4000,B),SUM))：垂直堆叠字符串"汇总"与每个项目负责人对应所有项目状态的汇总数量（使用BYROW函数与SUM的表达式）。这构成第二列。

VSTACK(B, COUNTIFS(C3:C4000, A, E3:E4000, B))：垂直堆叠项目状态列表B与每个项目状态对应每个项目负责人的项目数量。这构成第三列至最后一列。

SORT函数：对上述整合后的二维数组进行排序。参数说明：

排序区域：由LET函数返回的整合后的二维数组。

排序依据列：2，即基于第二列（"汇总"列）进行排序。

排序方式：-1，表示降序排列。