使用动态数组创建汇总表

一张汇总表会把一大串原始数据行压缩成一个小巧、易读的区块：每个类别占一行，旁边显示相应的总计。想象一下，一份包含数百行的销售记录，最后变成一张整洁的表格，列出每个区域及其总收入。

过去的做法是使用手动数据透视表，并且必须手动刷新。现代做法则使用动态数组公式，数据一发生变化，公式就会立即自动更新。无需按钮，也无需刷新。

在本课中，您将结合使用三个强大的工具：使用 UNIQUE 列出类别，使用 SUMIFS 计算每个类别的总计，使用 FILTER 提取匹配的行。它们结合起来，就能构建实时汇总。

假设有一个名为销售的工作表，其中包含三列：A 列是区域，B 列是产品，C 列是金额，数据填充在第 2 行到第 200 行。

我们的目标是创建一份汇总表，显示每个不重复的区域及其销售总额。第一个挑战是获取一份干净的区域列表，而不是手动输入，因为以后可能会出现新的区域。

• A2:A200 中包含许多重复的区域名称，例如东部、西部、东部、北部。
• 我们只需要：东部、西部、北部，每个区域列出一次。

这份不重复列表是整个汇总表的基础。

UNIQUE 函数接收一个单元格区域，并且每个值只返回一次。它会溢出，也就是说，一个公式会填充与不重复值数量相同的单元格。

在 E2 单元格中输入以下公式，区域列表就会自动显示在下方：

如果之后在数据中添加了新的区域，溢出列表也会自动扩展。您永远不需要修改公式。

现在，我们需要计算 E 列中每个区域的金额总计。只有当另一个区域满足某个条件时，SUMIFS 才会将一个区域中的值相加。

其结构为 SUMIFS(sum_range, criteria_range, criteria)。将以下公式放在 F2 中，也就是第一个区域的旁边：

E2# 引用是关键所在。# 符号表示从 E2 开始的整个溢出区域。因此，这一个公式就能计算 UNIQUE 生成的每个区域的总计。

溢出引用 E2# 始终指向某个公式生成的完整区块，无论该区块扩展到多大。这正是汇总表能够动态更新的原因。

当 UNIQUE 找到 3 个区域时，E2# 就有 3 个单元格高，而 SUMIFS 会返回 3 个总计。当数据扩展到 5 个区域时，两个区域会一起扩展，无需进行任何修改。

• E2 = 顶部的单个单元格。
• E2# = 从 E2 开始的整个溢出数组。

请熟悉 # 符号；它是仪表板公式的核心。

按顺序排列总计后，汇总结果会更易于阅读。将区域列表套入 SORT，即可按字母顺序显示类别；您也可以按总计对整张表进行排序。

要在 E2 中按字母顺序列出区域：

由于 F 列中的总计仍然引用 E2#，区域排序后，总计也会自动重新对齐。两列始终保持同步。

有时，您需要查看某个类别对应的原始行，而不仅仅是总计。FILTER 会返回所有满足条件的行，并将它们溢出显示。

要显示区域等于 H1 单元格中值的所有销售行：

如果 H1 中是东部，您就会得到所有东部行。将 H1 改为西部后，该区块会立即自动重写。这是仪表板中下钻视图的基础。

当没有任何匹配项时，FILTER 会引发 #CALC! 错误。为了保持结果整洁，请将可选的第三个参数设置为备用消息。

当匹配项数量为零时，第三个参数会显示：

现在，没有销售记录的区域会显示一条友好提示，而不是错误。请始终在仪表板中添加这个备用设置，以免意外选择破坏布局。

汇总表通常不仅会显示每个区域的金额，还会显示该区域的订单数量。COUNTIFS 会统计符合条件的行数，就像 SUMIFS 一样，只是它不需要求和区域。

将以下公式放在总计旁边的 G 列中：

现在，您的三列表格会显示区域、销售总额和订单数量，而这一切都由 E2# 中溢出的区域列表驱动。所有内容都会同步刷新。

以下是并排放置的完整方案：

• E2： =SORT(UNIQUE(Sales!A2:A200)) 列出区域。
• F2： =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) 计算每个区域的总计。
• G2： =COUNTIFS(Sales!A2:A200, E2#) 统计每个区域的数量。

只有 E2 中的公式需要输入一次；F 列和 G 列会根据 # 引用自动溢出。只要在销售工作表中的任意位置添加一笔新销售记录，三列就会自动更新，无需点击任何按钮。

与手动输入值或刷新数据透视表相比，由公式驱动的汇总表具有明显优势：

• 实时：数据发生变化时立即重新计算。
• 自动调整大小：通过 UNIQUE 和 # 引用自动显示新的类别。
• 透明：任何人都可以直接在单元格中读懂其逻辑。

需要注意的是，溢出区域需要有空白空间才能扩展；我们将在后续课程中介绍溢出受阻的情况。现在，请在公式下方预留空间。

您构建了一张能够自动维护的汇总表：

• UNIQUE 将每个类别列出一次，并溢出显示结果。
• SORT 对该列表进行排序，使其更易于阅读。
• SUMIFS 和 COUNTIFS 使用 E2# 溢出引用，计算并统计每个类别。
• FILTER 提取匹配的行，用于下钻查看；没有匹配项时会显示备用消息。

由于每个公式都以溢出列表为依据，添加新数据后，整个汇总表都会自动更新，无需手动操作。下一步，您将完全使用公式重新创建完整的数据透视式报表。