|
上期我们已经讲了Sumproduct函数的多条件查找与求和，单条件求和与查找，其实Sumproduct函数功能十分强大。今天让我们继续学习Sumproduct函数的进阶应用。 首先让我们回顾下Sumproduct函数的语法和注意事项：  语法：Sumproduct(array1,array2,array3, ...)Array1, array2, array3, ... 为 2 到 255 个数组，其相应元素需要进行相乘并求和。 数组参数必须具有相同的维数，否则，函数 Sumproduct 将返回错误值 #VALUE!。 特别提醒：Sumproduct函数的计算区域不采用一整列计算，一般是采用单元格区域，比如A1:A100,而不采用A:A。 函数 Sumproduct 将非数值型的数组元素作为 0 处理。 一、 数组求和 Sumproduct函数支持数组求和。前几期我曾说过sum函数的应用，当sum函数对数组求和时，必须按ctrl enter shift组合键，数组公式才能生效。Sumproduct函数不要按三键求和，写完公式后直接按enter就能对数组公式进行求和。 上图为某水果店报表，请问总价是多少？ 思路：如果按常规方法做，先求出每种水果的总价，最后再累加得出水果总价。如果水果很多的话，这种方法既耗时费力，又容易出错。经观察，我们发现，各种水果单价和数量相乘，最后再累加即可得到总价。我们可以采用Sumproduct函数数组公式来进行求和。 公式 =Sumproduct(B2:B8*C2:C8) 公式解读：B2*C2 B3*C3 …B8*C8。 该公式为数组公式，Sumproduct函数支持数组运算，因此不用按ctrl enter shift组合键，就能得到结果。 二、 Sumproduct函数二维区域求和 上表为某公司一季度业绩表，请问各部门一季度各月累计业绩多少？ 思路:经观察，我们发现汇总表为二维区域表。一个单元格对应两个字段，就是二维表。在右表中，F2单元格对应两个字段，一个字段是部门，一个是时间。我们可以用Sumproduct函数的多条件求和。 公式 =Sumproduct(($A$2:$A$11=F$1)*($B$2:$B$11=$E2),$C$2:$C$11) 公式解读：Sumproduct函数的参数必须维度一致，A2:A11, B2:B11, C2:C11的维度一致。我们构造公式要满足两个条件，一个是部门名字，一个是时间。F$1是混合引用，当我们把公式进行右拉和下拉，其列号会发生变化，而行号被固定住。$E2道理也一样。如果不理解，可以参看本订阅号历史文章：引用的切换和智能匹配。该公式进行左右上下拖动后会自动进行匹配，不用调整参数。 二维区域的引用快捷思路： 经观察，右表中单元格两个字段条件的规律如下： F2=F1*E1,F3=F1*E2, G2=G1*E2,G3= G1*E2。 对于部门来说，行号1没有发生变化，而列号发生变化，因此我们可以用混合引用F$1来表示。同理对于月度来说，列号不变，行号发生变化，我们可以用$E1来表示。综合起来就是F$1*$E1。 三、 Sumproduct函数模糊求和 上图为某公司一季度业务表，请问青春部一月累计业绩多少？ 思路:在A列中有青春1部，青春2部.也就是说部门中只要含有青春二字，求其一月累计业绩。 公式 =Sumproduct(ISNUMBER(FIND('青春',A2:A11))*(B2:B11=F2),C2:C11) 公式解读： Find函数用来对中某个字符串进行定位，以确定其位置。 Find函数进行定位时，总是从指定位置开始，返回找到的第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串。 语法为：find（要查找的字符串，查找的单元格，从第几个字符开始查找）如果省略最后一个参数，则默认从第一个字符开始查找。 FIND('青春',A2:A11)是找出A列中如果含有青春二字就返回数字，否则返回错误值。我们可以用F9来试运算该函数得到： 在find函数外围还有一个ISNUMBER函数，构成嵌套函数。这是判断数字的函数。 ISNUMBER函数只有一个参数value，表示进行检验的内容，如果检验的内容为数字，将返回TRUE，否则将返回FALSE。其函数语法为：ISNUMBER（value）是判断函数，最终返回逻辑值真和假。 我们可以用F9来试运算该嵌套函数（isnumber和find嵌套函数）得到：，最后再用Sumproduct函数进行求和。在sumproduct函数中，TRUE当做1来处理，FALSE当做0来处理。因此我们就能用sumproduct、ISNUMBER、FIND函数嵌套来进行模糊查找。 GIF操作如下： 本教程的源数据表格百度网盘网址为：http://pan.baidu.com/s/1gfAKWZD Sumproduct函数运用很广泛，在业内号称万能函数。掌握Sumproduct函数，必将让你的函数水平百尺竿头更进一步。如果能将所学的知识，互相融会贯通，那么你离成功就不远了。 |
|
|
