ExcelのSUMPRODUCT関数について話しましょう

この記事では、excel に関する関連知識を提供します, 主に SUMPRODUCT 関数の関連問題を紹介します. この関数は、条件付き合計とカウントの 2 つの主要な関数を統合するだけでなく、複雑なシナリオでのランキング処理について見てみましょう。皆様のお役に立てれば幸いです。

関連する学習の推奨事項: excel チュートリアル

今日は、非常によく使用される実用的な関数である SUMPRODUCT を紹介します。ご存知のとおり、条件付き合計とカウントはテーブル ユーザーが遭遇する 2 つの最も一般的な問題であり、この関数は条件付き合計とカウントという 2 つの主要な機能を組み合わせるだけでなく、複雑なシナリオでのランキング処理にも使用でき、さらにはたった 1 つの関数で Excel の世界の半分を征服した人もいると聞きました...だから、彼らはそれを学ぶ必要があります。

基本構文

まず基本構文を見てみましょう。SUMPRODUCT の公式構文の説明では、指定された配列セット内の配列間に対応する要素を追加します。そして積の合計を返します。構文形式は次のとおりです:

=SUMPRODUCT（array1,array2,array3, …）

- SUM は合計を意味し、PRODUCT は乗算を意味します。パラメーターは乗算されてから合計されます。SUMPRODUCT はその名前が示すとおりの動作をします。

私の手を見てください、ワイ、トゥ、スリ... まとめると、SUMPRODUCT 関数には次の 3 つの特徴があります:

1> デフォルトで配列演算を実行します。

2> パラメータ内の数値以外の配列要素は 0 として扱われます。

3> パラメータは同じサイズである必要があり、そうでない場合はエラー値が返されます。

特徴分析

SUMPRODUCTの履歴書を読んだ後、ぼんやりとしか理解できていない友人も多いと思いますが、この特徴は何を意味するのでしょうか？どのような作業ができるのでしょうか？実際のところ、それは明らかではありません。

ちょっと指を鳴らして、いくつか例を挙げてみましょう。

上記のデータテーブルに示すように、列 C は製品の単価、列 D は販売数量です。次に、合計売上高を計算する必要があります。セルC9。

C9 次の式を入力して結果 11620.60

=SUMPRODUCT(C3:C7, D3:D7)

を取得します。これは単純な SUMPRODUCT 関数です。その演算プロセスは次のとおりです。2 つの領域配列 C3:C7 と D3:D7 の要素をそれぞれ乗算します。つまり、C3*D3、C4*D4、C5*D5...C7*D7

# まで乗算します。

##まず各商品の売上金額を計算し、最後に合計することを意味します。

SUMPRODUCT 関数の最初の特徴は、配列間の演算をサポートしていることであるため、数式は複数の演算を実行しますが、数式の入力を終了するために 3 つの配列キー を押す必要はありません。 。

一部の友人は、この数式は次のように書くこともできると言っています:

=SUMPRODUCT(C3:C7*D3:D7)

または、次の配列数式を使用することもできます。

=SUM(C3:C7*D3:D7)

では、これら 3 つの式の違いは何でしょうか?

まず、ほとんどの場合、SUMPRODUCT 関数では配列演算を実行するために数式入力を終了するために配列のトリプル キーは必要ありませんが、SUM 関数では必要です。

2 番目に、SUMPRODUCT 関数のもう 1 つの非常に重要な機能について説明します。

....

上記の表を少し変更し、「ペン」の販売数量を「未カウント」に変更しました。セル C9 の総売上高を計算することも必要です。

このとき、数式:

=SUMPRODUCT(C3:C7*D3:D7)

または配列数式:

=SUM(C3:C7*D3:D7)

を使用すると、エラー値 #VALUE !## が返されます。

#エラー値が返される理由は、セル D4 がテキスト値として「まだカウントされていない」ためです。テキスト値は数学的演算に直接関与できないため、C4*D4 はエラー値 #VALUE! により、数式全体の結果が返されます。エラー値。

次の数式を使用する場合にはそのような問題はなく、正しい結果が直接返されます:

=SUMPRODUCT(C3:C7,D3:D7)

This is SUMPRODUCT 関数の 2 番目の特徴は、数値以外の配列要素を 0 として扱うことです。

この例では、セル D4 の「まだカウントされていない」値は数値ではなくテキストです。SUMPRODUCT はそれを積極的にゼロとして扱うため、C4*D4、結果もゼロになり、他の配列も要素は通常どおり計算し、11385.60 の結果を取得します。

SUMPRODUCT は非数値配列要素を 0 として扱うことに注意してください。いわゆる非数値配列要素には論理値とテキストが含まれますが、エラー値は含まれません。配列要素に次の値が含まれる場合エラー値。この例の最初の数式のように、数式はエラー値も返します。

……

SUMPRODUCT 関数の 2 つの機能について説明した後、3 番目の機能について説明します。配列パラメーターは同じサイズでなければなりません。同じサイズでなければ、エラー値が返されます。

引き続き、上の図の例を例として使用して、製品の総売上高の計算を続けます。 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教程

以上がExcelのSUMPRODUCT関数について話しましょうの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。