[Excel]忘记乘积求和,SUMPRODUCT其实还能这么用……

你用过SUMPRODUCT函数吗? 除了能够实现,pivot table中乘和运算外,SUMPRODUCT还能干嘛? 本期,火箭君将带你揭示, SUMPRODUCT不被你所熟知的另一项用处。

函数SUMPRODUCT基础使用

SUMPRODUCT函数公式非常简单,公式后直接在括号内输入若干组数组即可,而公式则会将若干数组间的数据相乘,再求总和。

举例而言,如果你有一组数据为 {2,3,4},另一组数据为{5,10,20}。如果你使用SUMPRODUCT函数,那你会得到120。(因为25+310+4*20=120)


这不就是一个乘法公式和一个求和公式的结合体么?貌似很一般啊? 但看完后面的叙述,火箭君觉得你会对这个公式另眼相看!

SUMPRODUCT的多条件查询

说道多条件查询,火箭君曾经在去年的推文让vlookup搞定多条件查询中,提及使用vlookup函数实现多条件查询的方法。但我们今天的主角SUMPRODUCT函数也同样能够实现多条件查询。而且火箭君觉得实际使用中,利用SUMPRODUCT进行多条件,尤其是3个以上条件查询时,更为便利。

何为多条件查询?

顾名思义,“多条件”就是超过2个条件,也就是说要查询的结果必须符合条件1和条件2,甚至是条件1到条件n。 从数据结构上来看,可能条件是按两个方向进行排列的: 亦或者是并行排列的

SUMPRODUCT实现2个条件查询

需要查询的数据位于B2:B19这个范围,而限制条件分别对应C12和C13单元格。 整个公式可以分成这么几个部分:

  • (A2:A9=C12) – 依次对比A2至A9单元格中的字符串是否与C12单元格相同,若相同则为1,否则为0。并由此生成一个8行1列的数组。

  • (B1:I1=C13) – 同理,依次对比B1至I1单元格中的字符串是否与C13单元格相同,若相同则为1,否则为0。并由此生成一个1行8列的数组。

  • B2:I9 – 一个8行8列的数组。

下面可能是唤醒你高中数学的时间了,火箭君带你梳理下这三个部分相乘的计算过程。 1. (A2:A9=C12) *(B1:I1=C13) 得到了一个新的8×8数组

2.再乘以B2:I9,则是两个8×8数组的进一步相乘,最后的结果就为¥8,377

SUMPRODUCT实现3个以上条件查询

公式依旧不复杂,对应条件1、2、3的判断生成了3个数组,再进行简单的数组相乘计算,最后找到了满足3个条件的数值。而这就是为什么 火箭君在上文中提到,利用sumproduct函数来实现3个以上条件查询时,要比vlookup函数来得更为简单,整个公式也更易读。

不知本期的SUMPRODUCT函数的介绍,除了让你更进一步认识了这个函数,有没有同时唤起你残存的高中数学知识?