COUNTIFS 函数示例：动态双向计数

概括

要使用公式执行动态双向计数，可以使用Excel 表格、UNIQUE 函数以及连接到UNIQUE 返回的溢出区域的COUNTIFS 函数。在所示示例中，单元格 G5 中的公式为：

=COUNTIFS(data[Color],F5#,data[Size],G4#)

其中数据是一个基于 B5:D16 区域中数据的 Excel 表格。COUNTIFS 函数的结果会溢出到 G5:I9 区域中。请注意，COUNTIFS 函数的结果是符合条件的记录数。下面提供了一个使用相同条件对“数量”列求和的公式。

动态数组公式仅在Excel 365和 Excel 2021 中可用。

通用配方

=COUNTIFS(table[column1],spill1#,table[column2],spill2#)

解释

本例的目标是创建一个公式，对 B5:D16 区域内的所有颜色和尺寸组合进行动态双向计数。所示解决方案需要四个一般步骤：

创建一个名为data的Excel 表格
使用UNIQUE 函数列出唯一颜色
使用UNIQUE 函数列出唯一尺寸
使用COUNTIFS 函数生成计数

创建 Excel 表格

Excel 表格的一大优势在于，当添加或删除行时，表格会自动调整大小。在本例中，我们只需创建一个名为“ data”的新表格，并将 B5:D16 单元格区域内的数据填充到表格中即可。

表格现在会根据需要自动扩展或收缩。

列出独特的颜色

下一步是列出“颜色”列中从 F5 单元格开始的唯一颜色。为此，我们使用UNIQUE 函数。F5 单元格中的公式为：

=UNIQUE(data[Color]) // unique colors

这正是该解决方案的动态特性所在。即使表格中的数据发生变化，UNIQUE 函数仍会持续输出唯一颜色列表。

列出唯一尺寸

为了进行双向计数，我们还需要从单元格 G4 开始列出所有唯一尺寸。我们可以使用与计算颜色相同的公式来实现这一点：

UNIQUE(data[Size]) // unique sizes

但是，与颜色不同，我们需要这个列表水平排列。要将输出从垂直改为水平，我们需要将 UNIQUE 公式嵌套在TRANSPOSE 函数中。G4 单元格中的公式为：

=TRANSPOSE(UNIQUE(data[Size])) // horizontal array

UNIQUE 函数返回一个如下所示的垂直数组：

{"L";"M";"S"}

TRANSPOSE 函数会将此数组转换为如下所示的水平数组：

{"L","M","S"}

注意第二个数组中使用的是逗号而不是分号。

计算唯一值数量

现在我们已经具备了计算数量所需的条件。由于工作表中的溢出区域包含不同的尺寸和颜色，我们可以使用COUNTIFS 函数来完成这项任务。G5 单元格中的公式如下：

=COUNTIFS(data[Color],F5#,data[Size],G4#)

使用 COUNTIFS 函数时，条件以范围/条件对的形式输入。第一对条件针对颜色：

data[Color],F5# // all colors, unique colors

第二 组范围/标准针对的是尺寸：

data[Size],G4# // all sizes, unique sizes

数据变化时

这种公式方法的关键优势在于它能够即时响应数据变化。如果新增行引用了现有颜色和尺寸， COUNTIFS 函数返回的溢出范围保持不变，COUNTIFS 函数只会返回更新后的计数结果。如果新增行包含新的颜色和/或尺寸，UNIQUE 函数会捕获这些变化，并根据需要扩展溢出范围。如果从表中删除行，溢出范围也会根据需要缩小。在所有情况下，溢出范围都代表当前唯一颜色和尺寸的列表，COUNTIFS 函数使用这些值返回当前的计数结果。

一键式配方

在最新版本的 Excel 中，我们可以使用 LET 函数和两个新函数HSTACK和VSTACK，编写一个包含所有功能的公式，生成如下所示的完整汇总表：

=LET(
  colors,UNIQUE(data[Color]),
  sizes,TRANSPOSE(UNIQUE(data[Size])),
  counts,COUNTIFS(data[Color],colors,data[Size],sizes),
  HSTACK(VSTACK({"Color"},colors),VSTACK(sizes,counts))
)

注意：VSTACK 和 HSTACK 仍处于测试阶段，可通过 Excel 365 的 Beta 频道获取。

简而言之，我们使用 UNIQUE 函数提取唯一的颜色和尺寸，并使用 COUNTIFS 函数生成所有计数。LET函数用于将这三个结果分别赋值给变量colors、sizes和counts。然后，我们使用HSTACK和VSTACK函数来构建最终表格。由于 HSTACK 是最后一个运行的函数，因此它返回最终结果，即一个包含多个单元格值的数组。有关 LET 函数的更多信息，请参阅此示例，其中详细介绍了 LET 函数的用法。

上面的公式很好地说明了动态数组公式将如何彻底改变未来的公式求解方式。

透视表选项

透视表也是解决此问题的绝佳方法，并且还能提供更多功能。然而，它的一个缺点是需要刷新才能显示最新数据。而公式则会在数据更改时立即更新。

动态双向求和

上面的示例执行的是动态双向计数。但是，您可以使用相同的方法轻松创建动态双向求和。要计算“数量”列的双向求和，只需将 COUNTIFS 函数替换为SUMIFS 函数即可：

=SUMIFS(data[Qty],data[Color],F5#,data[Size],G4#)

SUMIFS 动态双向求和

请注意，SUMIFS 函数需要一个额外的（第一个）参数sum_range，用于指定求和范围。用于确定颜色和尺寸组合的范围/条件对与 COUNTIFS 公式中使用的相同。

非动态解

如果您使用的是没有 UNIQUE 函数的旧版 Excel，仍然可以使用 COUNTIFS 函数创建非动态计数。

详情页