|
在excel中，对于sumproduct函数的说明是返回相应的数组或者区域乘积的和。看上去就是一组数据和另一组数据先分别相乘然后求和嘛，其实sumprodct函数的用法不只是看上去这么简单，它兼有sum函数、product函数、sumif函数、sumifs函数、countif函数、countis等函数的功能。本文就详细给大家介绍一下sumproduct函数的用法吧。 一、基本用法。对于sumproduct函数，公式参数特别简单，即=SUMPRODUCT（数组1,数组2,数组3, ...），每个数组之间用逗号隔开，表示数组之间先相乘再求和。 如下图所示，在E2单元格中输入函数=SUMPRODUCT(C2:C21,D2:D21)，计算过程为188*5+232*7+292*4+……224*2=23308，直接就求出来总销售额，而不用求出每个地区每个产品的销售额再求和。 在F2中输入函数=SUMPRODUCT(F9:F28)，因为只有一组数据，所以返回的结果就是对这组数据求和，相当于sum函数。 在G2单元格输入函数=SUMPRODUCT(E4,F4)，则表示E4单元格的数值乘以F4单元格的数值。相当于product函数。 所以我们可以看出来，这个函数只要有逗号，那么就是逗号隔开的区域相乘，且逗号两边区域的单元格个数必须相同。 图一 二、条件求和。 在下图中的E2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*C2:C21)，就会算出A01产品的销量合计（图中绿色部分），这个公式中仍然只有一组参数，B2:B21=B2是在B列内容中判断条件是否是A01，返回的结果是TRUE;FALSE;FALSE……，(B2:B21=B2)*C2:C21表示逻辑值与销售数量相乘，返回{188;0;0;0;283;0;0;0;327;0;0;0;288;0;0;0;211;0;0;0}，可以看到FALSE与数值相乘返回的是0，最后的sumproduct函数仅表示求和，因为只有一个参数。 在F2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*(C2:C21>200)*C2:C21)，就会算出A01产品中销量大于200的合计数，对于这种多条件求和，其实原理和单条件求和一样，条件之间用乘号隔开即可。 在G2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*C2:C21,D2:D21)，算出的是A01产品的销售额。首先看到有一个逗号了，说明这里的sumproduct函数的参数就有两个，然后(B2:B21=B2)*C2:C21表示A01产品的数量（不是A01返回的数量是0），加上逗号后面D2:D21，表示的单价先相乘再求和，最后就算出A01产品的销售额了。 看完这三个公式，A01产品中销量大于200的销售额的公式也应该明白了，即SUMPRODUCT((B2:B21=B2)*(C2:C21>200)*C2:C21,D2:D21)，和你想的一样吗？ 图二 三、条件计数。 在下图中E2单元格输入公式=SUMPRODUCT((B2:B21=B2)*1)后，可以统计出A01产品的数量。(B2:B21=B2)表示在产品型号中条件是A01，计算结果是返回的是TRUE;FALSE;FALSE……FALSE，但是此处为什么要在后面乘以1呢？因为sumproduct是对数值计算，而(B2:B21=B2)返回的结果是文本。而true*1=1，false*1=0，所以(B2:B21=B2)*1计算结果是1;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0，进行求和就会算出5。 在F2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*1+(B2:B21=B3)*1)，会统计出A01和B02产品的合计个数，因为求合计个数是或的关系，两个条件满足一个即可，所以两个条件之间用加号连接。 在G2单元格中输入公式=SUMPRODUCT((C2:C21>200)*1*(C2:C21<> 图三 四、模糊条件求和。 如下图所示，如果想求出北方地区（东北、华北、西北）A01产品的销售额，那么在E2单元格中输入公式=SUMPRODUCT(--(ISNUMBER(FIND('北',A2:A21)))*(--(B2:B21=B2))*C2:C21,D2:D21)即可。FIND('北',A2:A21)表示查找“北”在单元格中的位置，如果能找到，返回字符的位置，找不到返回#VALUE!。ISNUMBER(FIND('北',A2:A21)）表示如果find函数结果是数值，isnumber返回true，否则返回false。而isnumber函数前加--表示减负数（作用和上面乘以1相同），最终把true返回到1，false返回0。--(B2:B21=B2)判断产品是否为A01，如果是返回1，否则返回0。此时就相当于多条件求和，逗号前面条件和数量相乘，逗号后面为单价，最终符合条件的值相乘并求和。 图四 五、中国式排名。 excel中的排名函数rank函数返回的是西方国家惯用的排名方式，而我们中国式排名如果有并列名次，不会占用下面的名次，下面是sumproduct函数与rank函数排名结果对比。 利用sumprodunct排名时，在E2单元格输入公式=SUMPRODUCT(($C$2:$C$21>C2)/COUNTIF($C$2:$C$21,$C$2:$C$21))+1即可。这个函数比较难理解。对于E2单元格，COUNTIF($C$2:$C$21,$C$2:$C$21)函数表示条件计数，如果有重复值，则返回重复的个数，此处返回的结果是1;1;2;1;1;2;1;1;1;1;1;1;1;1;2;1;2;1;1;1，而用1/COUNTIF($C$2:$C$21,$C$2:$C$21)表示相同的数字只统计一次（因为每个重复的数字都被平均了）。返回结果为1;1;0.5;1;1;0.5;1;1;1;1;1;1;1;1;0.5;1;0.5;1;1;1，然后（$C$2:$C$21>C2)/COUNTIF($C$2:$C$21,$C$2:$C$21)的返回结果为0;1;0.5;1;1;0.5;1;1;1;1;0;0;1;0;0.5;0;0.5;0;0;1，其中$C$2:$C$21>C2采用的是相对引用，所以向下填充会返回不同的结果。直接决定了计算的相对名次。然后再用sumproduct函数对上面返回结果求和，最后+1对结果修正。 图五 这就是sumproduct函数的常见用法，如果本文的用法理解并熟练运用，那么有一些其他的用法自然就会了，比如隔列求和，组内排序等。最后提醒的是，注意函数中逗号，乘号，加号的运用，函数中的参数应为数值格式，且各个参数区域中的单元格个数相同。 |
|
|
