|
Excel的SUMIF/SUMIFS/COUNTIF/COUNTIFS函数可以用于条件求和/条件计数，但是当条件区域为合并单元格时，这些函数的结果是错误的。 本文和大家分享当条件区域是合并单元格时，如何进行条件求和。 在本文结尾附有条件计数的小案例。当你理解如何在合并单元格区域进行条件求和时，也能轻松解决条件计数问题。 一、案例 如下图所示，A1：B10为产品销量表。要求计算D2单元格指定产品的总销量。
二、计算步骤 在E2单元格输入公式 =SUM((LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)=D2)*$B$2:$B$10) 按Ctrl+Shift+Enter结束公式输入。
公式解析： （1）对A列取消合并，结果如下图所示。 可见只有A2、A5、A7单元格有数据，A3：A4、A6、A8：A10均为空值。这也是使用SUMIFS函数条件求和结果错误的原因。
本例使用LOOKUP+IF函数将A2：A10中空值的单元格补齐相应的产品数据。 （2）IF($A$2:$A$10<>"",ROW($2:$10))返回A2：A10非空单元格的行号与FALSE组成的数组， 即{2;FALSE;FALSE;5;FALSE;7;FALSE;FALSE;FALSE} （3） LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10) 即 LOOKUP(ROW($2:$10), {2;FALSE;FALSE;5;FALSE;7;FALSE;FALSE;FALSE} ,$A$2:$A$10) 该公式表示LOOKUP函数在{2;FALSE;FALSE;5;FALSE;7;FALSE;FALSE;FALSE}中查找{2;3;4;5;6;7;8;9;10}，并返回与查找到的值同一行的A2：A10的值。 例如查找值为“2”，则返回A2单元格的值“产品A； 查找值为“3”，在{2;FALSE;FALSE;5;FALSE;7;FALSE;FALSE;FALSE}中没有3，LOOKUP函数查找小于“3”的最大值，也就是“2”，返回A2单元格的值“产品A”。 以此类推，LOOKUP函数最终返回的结果为{"产品A";"产品A";"产品A";"产品B";"产品B";"产品C";"产品C";"产品C";"产品C"}。这样就可以将解析（1）中提到的A2：A10中的空值补齐。 （4） LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)=D2，将LOOKUP函数返回的{"产品A";"产品A";"产品A";"产品B";"产品B";"产品C";"产品C";"产品C";"产品C"}与D2单元格指定的产品名称比较，返回的结果为一组True和False值，即{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE} （5） SUM((LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)=D2)*$B$2:$B$10)，将满足条件的销量求和。 扩展（合并单元格条件计数）： A1：B10为公司各部门人员名单。你知道如何用公式统计“销售部”总人数吗？
可以在E2单元格输入公式： =SUM(--(LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)=D2)) 按Ctrl+Shift+Enter键结束公式输入。
 END |
|
|
