很多 Excel 初学者总是对函数公式有一种执念,都以为公式越长水平越高。
其实不然,能从所有方式中用最简单、最高效的那一种达成目的,才是解决问题的终极要旨。
比如今天这例多条件查找求和的案例,求助者强调一定要给出公式解法。
也好,权当是给新人学习和练手,今天一次性教大家两个公式。
案例:
下图 1 的左侧为各部门销售人员的订单明细,请在右侧的数据表中,根据部门和姓名查找并计算出每位销售的订单总数。
效果如下图 2 所示。
其实这个案例最简单的解法莫过于数据透视表,只要将“部门”和“姓名”拖动到行区域,将“订单数”拖动到值区域,进行求和,就直接得出结果。
不过既然求助者一定要用公式,也正好借此机会教大家两个比较经典的公式解法。
解决方案 1:
1. 在 G2 单元格中输入以下公式 --> 下拉复制公式:
=SUMIFS(C:C,A:A,E2,B:B,F2)
解释公式前先来学习一下 sumifs 函数。
SUMIFS 函数说明:
作用:
对满足所有参数条件的区域求和。
语法:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
参数:
sum_range:必需,要求和的单元格区域;criteria_range1:必需,用 criteria1 测试的区域;criteria1:必需,定义对 criteria_range1 中的哪些单元格求和的条件;[criteria_range2, criteria2], ...:可选,其他区域及其关联条件。最多可以输入 127 对区域/条件。
公式释义:
=SUMIFS(C:C,A:A,E2,B:B,F2)
如果同时满足以下条件,则对 C 列的对应单元格求和:A 列中等于 E2 的所有单元格,并且B 列中等于 F2 的所有单元格解决方案 2:
1. 在 G2 单元格中输入以下公式 --> 下拉复制公式:
=SUMPRODUCT((A:A=E2)*(B:B=F2)*1,C:C)
公式释义:
sumproduct 函数的作用是返回对应的区域或数组的乘积之和;(A:A=E2)*(B:B=F2)*1:括号中两个条件会分别生成两组由 true 和 false 组成的数组;将两个数组结果相乘,并且乘以 1,即将所有同时满足两个条件的结果变成 1,不满足的则为 0;SUMPRODUCT(...,C:C):将 C 列中的每个单元格分别与上述的数组相乘求和,只有与结果为 1 的数组相乘才会保留原单元格数值,将这些数值相加即为最终答案
有关 sumproduct 函数的详解,请参阅 Excel函数(四) – sumproduct函数计数、排名、求和等等。