엑셀의 SUMPRODUCT 함수에 대해 알아보겠습니다.

이 글에서는 SUMPRODUCT 함수와 관련된 문제를 주로 소개하는 excel에 대한 관련 지식을 제공합니다. 이 함수는 조건부 합계와 계산이라는 두 가지 주요 기능을 결합할 뿐만 아니라 복잡한 시나리오에서도 사용할 수 있습니다. 순위 처리 시 모든 분들께 도움이 되기를 바랍니다.

관련 학습 권장 사항: excel 튜토리얼

오늘은 매우 일반적으로 사용되며 실용적인 기능인 SUMPRODUCT를 공유하겠습니다. 우리 모두 알고 있듯이 조건부 합산과 계산은 테이블 사용자가 직면하는 가장 일반적인 두 가지 문제입니다. 이 기능은 조건부 합산과 계산의 두 가지 주요 기능을 결합할 뿐만 아니라 복잡한 시나리오에서 순위 처리에도 사용할 수 있습니다. 어떤 사람들은 단 하나의 기능으로 Excel 세계의 절반을 정복했다고 들었습니다. 그래서 그들은 그것을 배워야 합니다.

기본 구문

먼저 기본 구문을 살펴보겠습니다. SUMPRODUCT의 공식적인 구문 설명은 주어진 배열 세트의 배열 사이에 해당하는 요소를 곱하고 그 결과의 합을 반환하는 것입니다. 구문 형식은 다음과 같습니다.

=SUMPRODUCT（array1,array2,array3, …）

- SUM은 합계를 의미하고, PRODUCT는 곱셈을 의미하며, SUMPRODUCT는 이름 그대로 작동합니다.

내 손 좀 봐, 와이, 투, 스리... 정리하자면, SUMPRODUCT 함수는 다음과 같은 세 가지 특징을 가지고 있습니다.

1> 기본적으로 배열 연산을 수행합니다.

2> 매개변수의 숫자가 아닌 배열 요소를 0으로 처리합니다.

3> 매개변수의 크기는 동일해야 합니다. 그렇지 않으면 오류 값이 반환됩니다.

특징 분석

SUMPRODUCT의 이력서를 보고 많은 친구들이 멍하니 바라보고 있을 텐데요, 이러한 특징은 무엇을 의미할까요? 어떤 종류의 작업을 수행할 수 있나요? 실제로는 명확하지 않습니다.

손가락을 튕겨 몇 가지 예를 들어보겠습니다.

위 데이터 표에서 볼 수 있듯이 C열은 제품의 단가이고, D열은 판매량입니다. 이제 C9 셀의 총 판매량을 계산해야 합니다.

C9 다음 수식을 입력하면 결과가 11620.60

=SUMPRODUCT(C3:C7, D3:D7)

간단한 SUMPRODUCT 함수입니다. 연산 프로세스는 다음과 같습니다. C7*D7

이 동일해질 때까지 두 영역 배열 C3:C7 및 D3:D7의 요소, 즉 C3*D3, C4*D4, C5*D5를 각각 곱합니다. 각 품목의 판매액을 계산하고 최종적으로 합산합니다.

SUMPRODUCT 함수의 첫 번째 기능으로 인해 배열 간 연산을 지원하므로 수식이 여러 연산을 수행하더라도 수식 입력을 종료하기 위해 세 개의 배열 키를 누를 필요가 없습니다. .

어떤 친구들은 공식을 다음과 같이 쓸 수도 있다고 말했습니다:

=SUMPRODUCT(C3:C7*D3:D7)

또는 다음 배열 공식을 사용할 수도 있습니다.

=SUM(C3:C7*D3:D7)

그럼 이 세 가지 공식의 차이점은 무엇인가요?

먼저, 대부분의 경우 SUMPRODUCT 함수는 배열 연산을 수행하기 위해 수식 입력을 끝내기 위해 배열 삼중키가 필요하지 않지만, SUM 함수는 필요합니다.

두 번째로 SUMPRODUCT 기능의 또 다른 매우 중요한 기능에 대해 이야기하겠습니다.

…

위 표를 약간 수정하여 "펜"의 판매 수량을 아직 계산되지 않음으로 변경했습니다. 또한 C9 셀의 총 매출을 계산해야 합니다.

이 때, 수식

=SUMPRODUCT(C3:C7*D3:D7)

또는 배열 수식

=SUM(C3:C7*D3:D7)

을 사용하면 오류값 #VALUE가 반환됩니다!

오류값이 반환되는 이유는 D4 셀 때문입니다. "아직 계산되지 않음"은 텍스트 값입니다. 텍스트 값은 수학 연산에 직접 참여할 수 없으므로 C4*D4는 오류 값 #VALUE!를 반환하므로 전체 수식의 결과가 오류 값을 반환하게 됩니다.

다음 수식을 사용하면 그런 문제가 없으며 올바른 결과가 직접 반환됩니다.

=SUMPRODUCT(C3:C7,D3:D7)

이것이 SUMPRODUCT 함수의 두 번째 기능입니다. 배열 요소의 숫자 값은 0으로 처리됩니다.

이 예에서 "아직 계산되지 않은" 셀 D4의 값은 숫자 값이 아닌 텍스트입니다. SUMPRODUCT는 이를 적극적으로 0으로 처리하므로 C4*D4 결과도 0이고 나머지 배열 요소는 다음과 같이 계산됩니다. 평소와 같이 11385.60의 결과를 얻습니다.

SUMPRODUCT는 숫자가 아닌 배열 요소를 0으로 처리한다는 점에 유의해야 합니다. 소위 숫자가 아닌 배열 요소에는 논리값과 텍스트가 포함되지만 오류 값이 포함되지 않습니다. 또한 수식은 이 예의 첫 번째 수식과 같이 잘못된 값을 반환합니다.

…

SUMPRODUCT 함수의 두 가지 기능에 대해 이야기한 후 세 번째 기능에 대해 이야기해 보겠습니다. 배열 매개변수의 크기는 동일해야 합니다. 그렇지 않으면 오류 값이 반환됩니다.

우리는 계속해서 제품의 총 판매량을 계산하기 위해 위 그림의 예를 예시로 사용합니다. C9에 수식을 입력하면

=SUMPRODUCT(C3:C7,D3:D6)

결과가 어떻게 될까요?

오류 값: #VALUE!

이유는 무엇인가요?

细心的你肯定已经注意到了，两个区域数组，C3:C7明显显比D3:D6多了一个元素，C3和D3结对子，C4和D4结对子……那么C7和谁结对子呢？女人们都嫁了，结果剩下一个光棍，这日子没法过了！一个萝卜一个坑，只有萝卜没有坑，这不是要萝卜死吗？

——于是SUMPRODUCT就不高兴了，它给你一个错误值#VALUE!，明确告诉你，和谐时代幸福岁月，日子不能这么过。

这就是SUMPRODUCT函数的第三个特点：数组参数必须有相同的尺寸，否则返回错误值。

下面是一道练习题，你看看，能用SUMPRODUCT函数做出来吗？

案例拓展

假设下面这张图，是某个公司工资发放的部分记录表（数据纯属虚拟，如有雷同，那是穿越）。A列是工资发放的时间，B列是员工所属的部门，C列是员工姓名，D列是相关员工领取的工资金额。

——那么，问题和广告都来了：

1

员工西门庆领取了几次工资？

这是一个单条件计数的问题,通常我们使用COUNTIF函数,但如果使用SUMPRODUCT函数，一般写成这样：

=SUMPRODUCT((C2:C13="西门庆")*1)

先判断C2:C13的值是否等于”西门庆”，相等则返回TRUE，不等则返回FALSE，由此建立一个有逻辑值构成的内存数组。

上文已经说过，SUMPRODUCT有一个特性，它会将非数值型的数组元素作为0处理，逻辑值自然是属于非数值型的数组元素，为了避免SUMPRODUCT函数把逻辑值视为0，造成统计错误，我们使用*1的方式，把逻辑值转化为数值，TRUE转化为1，FALSE转化为0，最后统计求和。

2

员工西门庆领取了多少工资？

这是一个单条件求和的问题,通常我们使用SUMIF函数,如果使用SUMPRODUCT函数，我们可以写成这样：

=SUMPRODUCT((C2:C13="西门庆")*D2:D13)

依然首先判断C2:C13的值是否等于”西门庆”，得到逻辑值FALSE或TRUE，再和D2:D13的值对应相乘。TRUE乘以数值，得到数值本身。FALSE乘以数值返回0。最后统计求和得出结果。

看完了上面两个问题，有些朋友可能会在心里想，貌似SUMPRODUCT能干的事，SUMIF和COUNTIF也能做到，而且做的更好，那么还要SUMPRODUCT干啥嘞？

乡亲们呐，话不能这么说，SUMPRODUCT可是上得厅堂下得厨房，对工作环境不挑不拣，它对参数类型没有啥特别要求，COUNTIF和SUMIF就不同了，他俩要求个别参数，必须是区域（Range型），不支持数组，比如下面这两个问题，COUNTIF和SUMIF就要绕了。

3

二月份外交部发放了几次工资？总额是多少？

第1个问题，二月份外交部发放了几次工资？

这是一个多条件计数的问题。

第一个条件，发放工资的时间必须是二月份；第二个条件，发放工资的部门必须是外交部。

如果使用多条件计数函数COUNTIFS，判断发放工资的时间是否属于六月份，会简单问题复杂化。而使用SUMPRODUCT函数，咱们可以把公式写成这样：

=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部"))

……

第2个问题，统计二月份外交部发放了多少工资？

这是一个常见的多条件求和问题。

如果使用SUMIFS函数，判断发放工资的时间是否属于六月份，也会简单问题复杂化。

SUMPRODUCT跃然而至：

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部"),D2:D13)

或者：

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部")*D2:D13)

打个响指，关于这两个形式的SUMPRODUCT函数的区别，咱们上文已有详细说明——你还记得吗？

上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦，可以归纳为:

=SUMPRODUCT((条件一)*(条件二)……,求和区域)

4

二月份外交部和步兵部合计发放了多少工资？

解决了上面的问题，相信大家已经晓得如何计算二月份外交部发放多少工资了，那么二月份外交部和步兵部合计发了多少工资，又当怎么计算呢

我们经常见有些性格朴素的表亲们把公式写成这样：

=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部")*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="步兵部")*D2:D13)

这些表亲们估计心想，不就是计算两个部门吗？甭说两个，二十个咱也能算，一个加一个，一直加到二十个，世上无难事，只怕有心人嘛，一砖加一砖，长城就建成了，一泡加一泡，长江就奔流了……

呃……公式写的那么长，先不谈计算速度，首先它累手啊，万一写错了，又要修改，那也是麻烦他妈哭麻烦——麻烦死了。

其实我们可以写成这样：

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={"外交部","步兵部"})*D2:D13)

5

排名应用

认识了SUMPRODUCT函数在条件计数和求和方面的用法，最后，咱们再来看一个它在排名上的使用方法。

如上图所示，某个月某个公司某些人领了某些工资，然后呢，他们想看看自己的工资，在部门内的排名情况，比如说步兵部的鲁智深都是老员工了，非常想知道自个工资在各自部门排几号。

当然啦，不排不知道，一排就傻掉。

SUMPRODUCT是这么解决这个问题的，D2输入公式向下复制：

=SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9))+1

（思考，为什么公式的最后+1，而不是直接写成如下：）

=SUMPRODUCT(($A$2:$A$9=A2)*(C2<=$C$2:$C$9))

结束语

唠唠叨叨说了这么多，眼睛都说酸麻了，是到了该结束的时候啦。

最后，请思考两个小问题：

第1个问题：下面SUMPRODUCT函数有几个参数？

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")*D2:D13)

下面这个SUMPRODUCT函数又有几个参数？

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")，D2:D13)

第二个问题：

SUMPRODUCT为什么有时候比SUMIF/COUNTIF计算速度慢？

相关学习推荐：excel教程

위 내용은 엑셀의 SUMPRODUCT 함수에 대해 알아보겠습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!