Excel関数を学ぶなら万能のSUMPRODUCT！

前回の記事『Excel関数学習の魔法のAGGREGATE、1つが実は19の価値もある！ 》では、強力な統計関数について学びました。今日は SUMPRODUCT 関数について説明します。sumproduct 関数は 2 つのデータ列の積の和を求めることができます。ただし、この関数を使用するとき、特に論理値や論理値を使用した後は、注意すべき点がたくさんあります。配列. 一緒にやってみましょう. 説明をチェックしてください!

1. SUMPRODUCT の本質

SUMPRODUCT 関数は非常に単純です。 2 つのデータ列の積の合計を計算するための簡単な例を使用して、関数の基本的な機能を説明します。

上の図は、合計価格 通常、単価×数量を計算し、合計した結果がD8に表示されます。 SUMPRODUCT 関数を使用すると、単価と数量を使用して合計価格を直接計算できます (式 1: =SUMPRODUCT(B2:B7,C2:C7))。結果は D9 に示されています。

この式では、単価領域 (B2:B7) と数量領域 (C2:C7) の 2 つのパラメーターが使用されます。関数の機能は次のとおりです。最初のパラメータを変換するには、最初のパラメータ (単価) と 2 番目のパラメータ (数量) のデータを乗算し、合計します。

2. ほとんどのエラーの理由

この関数を使用すると、多くの友人がエラー値を受け取ることがよくありますが、その主な理由は、領域サイズの選択が次のとおりです。たとえば、次の場合、最初のパラメータには 7 つのセルがあり、2 番目のパラメータには 6 つのセルしかありません:

Use SUMPRODUCT 関数は、次のことを確認する必要があります。各パラメータの領域サイズは同じですが、多くの友人はこれに気づいていません。

3. もう 1 つの一般的な書き方は、カンマを乗算記号 (*) に変更することです (*)

この例では、別の書き方があります。一般的に使用される式は次のとおりです。 =SUMPRODUCT(B2:B7*C2:C7)

計算結果は次のとおりであることがわかります。 =SUMPRODUCT(B2:B7,C2: C7) と同じです。一貫性があります。

一貫した結果により、多くの友人が「この 2 つの違いは何ですか?」という疑問が生まれました。

4. カンマと乗算記号 (*) の違い

最初の式のカンマを乗算記号に変更するだけですが、乗算記号 (*) ですが、式の意味が変わります。最初の式 (SUMPRODUCT(B2:B7,C2:C7)) には 2 つのパラメーターがありますが、2 番目の式 (B2:B7*C2:C7) には 1 つのパラメーターがあります。 (パラメーターの数を確認するには、パラメーターがカンマで区切られているかどうかを確認する必要があります。) 最初の式では、2 つの領域を乗算するステップは関数によって完了します。この関数は 2 つのことを行います。まず、2 つの領域を掛け合わせます。対応するデータが乗算され、積が加算されます。 2 番目の式では、2 つの領域の乗算が配列計算によって行われ、関数は積の値を加算するという 1 つのことだけを実行します。

意味の変化による影響は何ですか?

例を通して見てみましょう:

上記の式では、カンマ (,) が使用されており、2 つの独立したパラメーターがあります。 SUMPRODUCT 関数は、まず 2 つのデータを対応するように乗算し、乗算中にデータをチェックし、数値以外のデータを 0 として扱い、積を加算します。したがって、B1「単価」とC1「数量」は0として扱われ、計算式では正しい結果が得られます。

コンマを * に置き換えると、数式の結果が間違っています。なぜ？ SUMPRODUCT 関数は、現時点では積の追加のみを行います。パラメータ B2:B7*C2:C7 は配列の乗算演算ですが、計算領域にテキストが含まれているため (テキストは乗算できません)、この配列の計算結果にはエラー値が含まれています。数式で「B2:B7*C2:C7」を選択し、F9 を押すと、B2:B7*C2:C7 の演算結果が表示されます。

最初の結果が表示されます。 ( 演算結果(単価×数量)が誤差値となります。次に、SUMPRODUCT はエラー値を含むデータを合計します。結果はエラーになるはずです。

5. SUMPRODUCT

で乗算記号 (*) を使用する際の重要なポイント

上記の内容には 2 つの意味があります:

まず、カンマを使用する場合と * を使用する場合は同じ結果になる場合がありますが、意味はまったく異なります。

2 番目に、SUMPRODUCT 関数で乗算記号 (*) を使用する場合は、2 つの点に注意する必要があります: 1 つ目は、テキストなどの未計算のコンテンツがあってはいけないこと、2 つ目は、2 セット以上の配列がある場合です。を乗算すると、データ領域のサイズは同じになります。カンマを使用する場合は、データ領域のサイズが一貫していることを確認するだけで済みます。

6. 実際、乗算記号には大きな利点もあります

A を使用します。B は SUMPRODUCT (A, B) に置き換えられます。および SUMPRODUCT (A*B)。カンマの場合、A と B は両方同時に数値または配列でなければなりません。一方を数値、もう一方を配列にすることはできません。乗算記号の場合、A と B は両方とも数値にすることができます値または配列を同時に指定したり、一方を数値、もう一方を配列にすることもできます。

つまり:

#√SUMPRODUCT

##カンマ、		乗算記号*
##SUMPRODUCT （C1,B1）	##SUMPRODUCT	（C1*B1） √
(C1:C9,D1:D9) √		SUMPRODUCT(C1:C9*D1:D9)	√
#SUMPRODUCT(C1:D9,F1:G9)	√	SUMPRODUCT(C1:D9*F1:G9)	√
SUMPRODUCT(C1:C9,D1)	×	#SUMPRODUCT(C1:C9*D1)	√
SUMPRODUCT(C1:D9,F1)	# ×	SUMPRODUCT(C1:D9*F1)	√

#

したがって、SUMPRODUCT 関数のアプリケーションは、乗算記号を使用して拡張されます。これから説明するのは、乗算記号を使用した SUMPRODUCT 関数の応用です。

7. これらの SUMPRODUCT の式を理解していますか?

上記の内容をご理解いただければ、この機能の基本的な使い方は問題ありません。しかし、次のような多くの SUMPRODUCT 式をまだ理解できない可能性が非常に高いです:

これは実際には条件付きカウントの問題です。もう一度見てください。

これは複数条件の合計問題であり、次のようなものがあります:

この表では、ほぼすべての統計問題の種類は SUMPRODUCT 関数を使用して解決できるため、1 つずつ例を示しません。上に挙げた3つの公式だけを理解できる友人は少ないかもしれません。

SUMPRODUCT 関数の使用方法は理解できても、これらの数式を理解できないのはなぜですか?その理由は、論理値と配列という 2 つの知識点を理解していないからです。

8. 論理値と配列について少し学習しましょう

まず論理値について話しましょう。論理値は 2 つだけです。値、TRUE と FALSE。数式で何らかの比較や判定を行うと論理値が生成されますが、SUMPRODUCT((I2:I22="張三")*1) を例にとると、 (I2:I22="張三") ））は判断です。通常、セル単位で判断しますが、I2=「張三」というのは、I2セルの内容が「張三」であるかどうかを判定し、そうであればTRUE、そうでなければFALSEとなります。領域を使用して判定すると、実際には配列であるデータのセットが取得されます。たとえば、I2:I22="Zhang San" は論理値のセットを取得します。F9 を使用して計算結果を確認できます:

興味がある場合は、営業担当者を 1 人ずつ比較すると、Zhang San の対応する値がすべて TRUE であることがわかります。

論理値は直接合計できないため、数値に変換する必要があり、変換方法は論理値に対して加算、減算、乗算、除算などの演算を行うことです。この式では、*1 には次の効果があります。その効果は次のとおりです:

*1 の演算後、TRUE は 1 になり、FALSE は 0 になります。理由は聞かないでください。これは Excel で規定されている方法であり、これが論理値と数値の対応関係です。

わかりました。まず論理値のセットを取得し、次に *1 を使用してそれらを数値のセットに変換し、それらを合計して、条件によってカウントするという目的を達成します。 =SUMPRODUCT((I2:I22="Zhang San")*1) という式は理解できましたか?

これで、論理値と配列演算の第一原理を理解しました: 数値のグループが数値で計算されるとき、数値のグループ内の各データはその数値で計算されます。先ほどの計算式ではこのように計算されます。

9. SUMPRODUCT の式がどれほど複雑であっても、すべてを理解してください。

2 番目の式を見てみましょう: =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="Second Branch")*( G2:G22="シャツ")*J2:J22)。

この式の長さには注目しないでください。実際、学んだばかりの知識を使用して解読するのは非常に簡単です。この数式にはまだパラメーターが 1 つしかありませんが、このパラメーターは 4 つの配列で構成されており、そのうちの 3 つは論理値、つまり (MONTH(F2:F22)=3)、(H2:H22="Second Branch")、および (G2: G22「シャツ」)。これら 3 組の論理値は 3 つの判定を完了し、月 = 3、店舗 = 第 2 支店、名前 = シャツの 3 つの条件に対応します。具体的な内容は図の通りです:

これは密集しているように見えますが、乗算すると 1 と 0 の塊になり、結果は次のようになります:

商品結果には 1 が 2 つだけあり、実際には 3 月の 2 番目の支店のシャツに関する 2 つのデータに対応します。このときの数式は、論理値の集合（すでに0と1）にデータの集合（数量）を掛けたものとなり、関数は合計を完了します。

この例では、配列計算の 2 番目の原則に注意する必要があります。複数 (2 つを含む) の配列 (この例では 4 つの配列) が計算されるとき、配列内の対応する位置のデータは次のようになります。配列に含まれるデータの数が同じである必要があります。

これまで、配列計算の 2 つの原則を理解しました。次に、簡単な例を示します:

配列 (A1:A9) とデータ ( B1) を乗算すると、この数値グループにこのデータがそれぞれ乗算されます;

2 つの配列 (A1:A9 および B1:B9) が乗算される場合、それは乗算されます。最初の数値グループと 2 番目の数値グループの対応する位置にあるデータ。

ここでこの式を振り返ってください=SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="2 番目の支店")*(G2:G22="シャツ")* J2:J22)、頭痛の種は少なくなるはずです。

実際には、配列の計算原理はさらに複雑です。興味のある友人は、関連情報を確認してください。ここで簡単な原理を学ぶことができます。 3 番目の式を見てみましょう: =SUMPRODUCT((H2:H22=H​​2)*(G2:G22={"sweater","shirt"})*L2:L22)。この数式は、前の 2 つとは少し異なります。(G2:G22={"sweater","shirt"})この部分では定数配列を使用します。実際、この数式は次のように記述する必要があります。 =SUMPRODUCT((H2:H22=H​​2)*(G2:G22="セーター")*L2:L22) SUMPRODUCT((H2:H22=H​​2)*(G2:G22="シャツ")*L2: L22 )。

この式では 2 つの SUMPRODUCT 関数が使用されています。1 つ目は本店のセーターの売上を計算するもので、2 つ目は本店のシャツの売上を計算するものです。 . それぞれ参照してください。誰でも理解できるはずです。 2 つの SUMPRODUCT 関数は、「sweater」と「shirt」を除いてまったく同じです。この場合、定数配列を使用して 2 つの内容を結合し、数式を簡素化できます。

今日の説明を通じて、私たちは一般的な SUMPRODUCT 式を理解し、SUMPRODUCT 関数の乗算記号の使用に関する混乱を解決し、SUMPRODUCT 関数の応用をさらに習得しました。また、論理値と配列に関するいくつかの基本も学びました。これは、他の複雑な数式を理解するのに役立ちます。

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

以上がExcel関数を学ぶなら万能のSUMPRODUCT！の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。