Excel公式练习79：多个OR条件计数 / 开普饭

excelperfect

本次的练习是：在Excel中，我们经常要基于多个OR条件进行计数或求和。如下图1所示，我们想要获取左侧数据中：“项目”是“T恤衫”或“连帽衫”，“颜色”是“红色”、“蓝色”或“绿色”，“城市”是“北京”或“上海”的数量。

图1

在图1中，单元格F9中是“项目”是“T恤衫”或“连帽衫”的数量；单元格G9中是“项目”是“T恤衫”或“连帽衫”，“颜色”是“红色”、“蓝色”或“绿色”的数量；单元格H9是“项目”是“T恤衫”或“连帽衫”，“颜色”是“红色”、“蓝色”或“绿色”，“城市”是“北京”或“上海”的数量。

为方便起见，我们定义了命名区域，如上图1中所示。

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

解决方案

可以使用SUMPRODUCT函数与ISNUMBER/MATCH函数的组合来编写公式求得结果。

在单元格F9中的公式为：

=SUMPRODUCT(ISNUMBER(MATCH(项目,{'T恤衫','连帽衫'},0))*1)

公式中的MATCH函数在“项目”列中查找“T恤衫”或“连帽衫”，返回由数字（匹配项）或#N/A错误值组成的数组：

{1;#N/A;#N/A;2;1;#N/A;1;2;#N/A;1;#N/A;2;#N/A;#N/A}

传递给ISNUMBER函数转换为布尔值TRUE/FALSE组成的数组：

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

乘以1将TRUE/FALSE值转换为1/0组成的数组：

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

传递给SUMPRODUCT函数求和得到满足条件的项目数：

在单元格G9中的公式为：

=SUMPRODUCT(ISNUMBER(MATCH(项目,{'T恤衫','连帽衫'},0))*ISNUMBER(MATCH(颜色,{'红色','蓝色','绿色'},0)))

公式中，每个ISNUMBER函数的原理与上面相同，将两个1/0组成的数组对应项相乘，得到同时满足“项目”列中是“T恤衫”或“连帽衫”，“颜色”列中是“红色”、“蓝色”或“绿色”的数量。

依此类推，在单元格H9中的公式为：

=SUMPRODUCT(ISNUMBER(MATCH(项目,{'T恤衫','连帽衫'},0))*ISNUMBER(MATCH(颜色,{'红色','蓝色','绿色'},0))*ISNUMBER(MATCH(城市,{'北京','上海'},0)))

上述公式中，花括号内的数组是硬编码值，我们可以使用单元格区域来替换：

=SUMPRODUCT(ISNUMBER(MATCH(项目,F3:F4,0))*ISNUMBER(MATCH(颜色,G3:G5,0))*ISNUMBER(MATCH(城市,H3:H4,0)))

使其更具灵活性。

小结：

1. 注意MATCH函数获取结果的过程。

2. 乘号用来实现与条件。

注：本次的练习整理自exceljet.net。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。

Excel公式练习79： 多个OR条件计数