Excel公式练习43： 统计满足多个条件的条目数量

导语：继续研究来自于excelxor.com的案例。这个案例与之前相比并不复杂，但要求公式最简。

本次的练习是：如下图1所示，左边的表格是一个测试表，学生要根据单元格A3:A12中的国家名，在列B和列C相应的单元格中填写该国家的首都和使用的货币。右边的表格是正确答案。

图1

要求在单元格C1中输入一个公式，计算学生答对的数量。在图1所示的示例中，答案是4，也就是说左边的表格中有四行是正确的，如表中高亮显示的行。

公式要求：

1. 尽可能简短，即公式不仅要能够得到正确的答案，而且字符数尽可能少。

2. 引用的单元格区域必须同时包含行和列，不允许只出现行或列（例如3:12、A:C）。

3. 不允许使用名称。

先不看答案，自已动手试一试。

公式

下面是两个解决方案，去掉等号后都是56个字符。

公式1：

=COUNT(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G12,))

公式2：

=SUM(COUNTIFS(A3:A12,E3:E12,B3:B12,F3:F12,C3:C12,G3:G12))

这两个公式中，公式2更好些。因为公式1理论上有可能出错，例如，如果表中同时存在France-Paris-Euro和Franc-Eparise-Uro（不存在该名字的国家、城市或货币），则会得出不正确的结果。然而，发生这种可能性的机率应该非常小，但为确保万无一失，可以使用下面的公式：

=COUNT(MATCH(A3:A12&"|"&B3:B12&"|"&C3:C12,E3:E12&"|"&F3:F12&"|"&G3:G12,))

即在公式1中添加合适的分隔符。

公式解析

先看看公式1：

=COUNT(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G12,))

这是一个简单而优雅的解决方案，是很好的公式技巧之一，提供了一种获取所需数量的方法，而不需要使用诸如VLOOKUP或INDEX/MATCH函数组合。

公式首先分别连接两个表中同一行的字符串，在十个字符串中执行一系列的匹配查找。因此，公式1可转换为：

=COUNT(MATCH({"FranceParisManat";"SerbiaBelgradeDinar";"LithuaniaVilniusZłoty";"HungaryBudapestForint";"AndorraChisinauManat";"UkraineSofiaHryvnia";"ArmeniaYerevanDram";"RomaniaRomeLeu";"BulgariaSofiaLev";"CroatiaBerlinLira"},{"AndorraAndorra laVellaEuro";"ArmeniaYerevanDram";"BulgariaSofiaLev";"CroatiaZagrebKuna";"FranceParisEuro";"HungaryBudapestForint";"LithuaniaVilniusLitas";"RomaniaBucharestLeu";"SerbiaBelgradeDinar";"UkraineKievHryvnia"},))

这里，省略了MATCH函数的参数match_type，Excel默认为精确匹配即等效于该参数指定为0，这样公式1转换为：

=COUNT({#N/A;9;#N/A;6;#N/A;#N/A;2;#N/A;3;#N/A})

COUNT函数忽略传递给它的参数中的错误值，因此，公式1的结果为：

4

再看看更为健壮的公式2：

=SUM(COUNTIFS(A3:A12,E3:E12,B3:B12,F3:F12,C3:C12,G3:G12))

注意，当参数criteria指定的值包含多个元素时，在合理地强制转换（例如作用在COUNTIFS上的外部函数和数组公式CSE输入）后，Excel将分别计算数组里的每个元素。

进一步说，这里有多个参数criteria指定的值都由多个元素（E3:E12、F3:F12、G3:G12）组成，Excel执行一系列单独的COUNTIFS计算。换句话说，公式2中的COUNTIFS表达式等同于执行以下十个单独的计算中的每一个：

=COUNTIFS(A3:A12,E3,B3:B12,F3,C3:C12,G3)

=COUNTIFS(A3:A12,E4,B3:B12,F4,C3:C12,G4)

=COUNTIFS(A3:A12,E5,B3:B12,F5,C3:C12,G5)

…

=COUNTIFS(A3:A12,E12,B3:B12,F12,C3:C12,G12)

然后汇总结果。

以这种方式，不难理解为什么这种构造能够为我们提供想要的结果，因为上述公式显然分别等于：0（测试表中A列为“Andorra”且B列中对应的条目为“Andorra la Vella”且C列中的对应条目为“Euro”的行数），1（A列=“Armenia”，B列=“Yerevan”且C列为“Dram”的行数），1（A列=“Bulgaria”，B列=“Sofia”，C列=“Lev”的行数）等。这样，公式2转换为：

=SUM({0;1;1;0;0;1;0;0;1;0})

结果为：

4