【实用公式解析】“或”逻辑下的汇总

分类：计数

文末有视频，详细解析本文公式。

问题

---

这是一个很简单的问题，但是围绕这个问题的讨论和扩展可以让我们理解在实际工作中应该如何着手解决问题。

假设数据如上图，左表中记录了各种批次产品的重量，右表是需要分析的两种产品。要求计算这两种产品出现的次数。

简单实现方法

---

如果我们要计算一种产品出现的次数，可以使用COUNTIFS函数。两个产品的情况稍有不同。

COUNTIFS函数可以支持多个条件，而且这多个条件可以针对同一列，但是针对同一列的这些条件是“与”的关系，必须同时成立。

这与我们的要求矛盾。

实现方法也很简单，只要用两个COUNTIS就好了：

=COUNTIFS(B5:B24,F5)+COUNTIFS(B5:B24,F6)

分别计算每个产品出现的次数，然后相加。

当然，也可以使用SUMPRODUCT函数，

=SUMPRODUCT(    (B5:B24=F5)+(B5:B24=F6))

其中的 (B5:B24=F5)+(B5:B24=F6)就相当于两个逻辑值数组相加：

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}+{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

两个逻辑数组相加，Excel会先转换为数值，

{1;0;0;0;0;0;0;1;1;0;0;0;0;1;0;1;0;0;0;1}+{0;1;0;0;0;1;0;0;0;1;0;0;0;0;1;0;0;0;0;0}

外层的SUMPRODUCT不过是将这个求和的结果数组全部汇总。

SUMPRODUCT+COUNTIFS方案

---

上述方案有一个小问题：如果条件过多，比如：要求计算10种产品的合计次数，无论是哪种方法都会导致过于冗长的公式。

我们可以使用下面的方法：

=SUMPRODUCT(    COUNTIFS(B5:B24,F5:F6))

写法非常简洁。

内部的公式：

 COUNTIFS(B5:B24,F5:F6)

实际上返回一个数组，针对F5:F6的每个单元格，得到一个计数结果，然后通过外层的SUMPRODUCT相加。

重复计数

在上面的实现方法中，有一个潜在的问题：

重复计数。

考虑一个变形的问题：

假设我们的两个条件不是苹果或者李子，而是苹果或者重量大于50。仍然使用上面的方法就会得到错误的结果，错误的原因是存在一些既是苹果，又重量大于50的条目，这些条目被重复计算了两次。

在“或”逻辑下汇总，如果条件对应的是多列，就会存在重复计数的问题。

解决方式是减去重复计数。

公式1：

=COUNTIFS(B5:B24,H5)+COUNTIFS(C5:C24,J5)-COUNTIFS(B5:B24,H5,C5:C24,J5)

公式2：

=SUMPRODUCT(    --(((B5:B24=H5)+(C5:C24>50))>0))

都可以得到正确的结果。

公式1很直白。我们简单的解释一下公式2：

参照上文简单实现时的解释，在内部两个比较表达式相加时，我们得到两个逻辑值数组的求和：

{TRUE,......} + {TRUE,......}

在求和前，它们被转换为数值数组：

{1, ......}+{1, ......}

结果是:

{2,......}

这里，那些被重复计数的条目对应的值变成了2，我们只需要将其变为1就好了。首先，使用：

{2,......}>0

将得到一个逻辑值数组：

{TRUE, ......}

而

--{TRUE,......}再次转化为数值：

{1,......}

外层SUMPRODUCT计算合计即可。

更详细的解析，请看视频

Excel+Power Query+Power Pivot+Power BI

---

Power Excel 知识库    按照以下方式进入知识库学习

Excel函数   底部菜单：知识库->Excel函数

自定义函数  底部菜单：知识库->自定义函数

Excel如何做  底部菜单：知识库->Excel如何做

面授培训  底部菜单：培训学习->面授培训

也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。