売上リストで月と担当者ごとのクロス集計を行う(SUMIFS,SUMPRODUCT)
月日と担当者別に売上が記述された売上リストを使ってクロス集計を行う方法です。複雑なデータであればピボットテーブルを使うと便利ですが、ここでは Excel の関数である SUMIFS 関数および SUMPRODUCT 関数を使ってクロス集計を行う方法について解説します。
(Last modified: )
SUMIFS関数でクロス集計する
最初に SUMIFS 関数を使ってクロス集計を行います。次のシートを見てください。
売上リストには月と担当者と売上が記述されています。 SUMIFS 関数を使ってクロス集計し、集計した売上を表示する H4 セルに次のように数式を入力しました。
=SUMIFS($D$3:$D$13,$B$3:$B$13,$G4,$C$3:$C$13,H$3)
SUMIFS 関数では複数の条件と一致した値を合計した結果を取得できます。 1 番目の条件として売上、 2 番目の条件として担当者を指定して売上を合計し取得します。
Enter キーを押すと H4 セルに 3 月の "遠藤" の売上を集計した結果が表示されます。
H4 セルから J5 セルにも同じように数式を入力しました。
月と担当者ごとにそれぞれ売上を集計することができました。
解説
SUMIFS 関数では合計範囲として D3:D13 、 1 つ目の条件範囲として B3:B13 、 2 つ目の条件範囲として C3:C13 をそれぞれ絶対参照で指定しています。
1 つ目の条件として月の値、 2 つ目の条件として担当者の名前を指定して売上の合計を取得します。あとで他のセルに数式をコピーするため、それぞれ一部を絶対参照で指定しています。
今回使用した関数の解説は下記を参照されてください。
SUMPRODUCT関数でクロス集計する
次に SUMPRODUCT 関数を使ってクロス集計を行います。次のシートを見てください。
売上リストには月と担当者と売上が記述されています。 SUMPRODUCT 関数を使ってクロス集計し、集計した売上を表示する H4 セルに次のように数式を入力しました。
=SUMPRODUCT(($B$3:$B$13=$G4)*($C$3:$C$13=H$3),$D$3:$D$13)
SUMPRODUCT 関数では配列と配列の各データを乗算した値を合計した結果を取得できます。月と担当者という 2 つの条件を使って 1 と 0 からなる配列を作成し、そのあとで売上の配列と乗算することで月と担当者ごとの売上を合計し取得します。
Enter キーを押すと H4 セルに 3 月の "遠藤" の売上を集計した結果が表示されます。
H4 セルから J5 セルにも同じように数式を入力しました。
月と担当者ごとにそれぞれ売上を集計することができました。
解説
例えば H4 セルに入力した数式では SUMPRODUCT 関数の 1 番目の配列として (B3:B13="3月")*(C3:C13="遠藤") 、 2 番目の配列として D3:D13 を指定しています。 1 番目の配列の部分で何をしているのかを解説します。
1 番目の配列では (B3:B13=G4) の部分で月が "3月" であれば TRUE 、そうでなければ FALSE を取得します。また (C3:C13="遠藤") の部分で担当者が "遠藤" であれば TRUE 、そうでなければ FALSE を取得します。
それぞれを * 演算子で乗算しますが TRUE*TRUE は 1 、それ以外の TRUE*FALSE や FALSE*FALSE は 0 となります。これで 1 番目の配列は 1 か 0 の値が含まれる配列となりました。(論理値と論理値を乗算すると 1 か 0 になるので、数値に変換するために N 関数や *1 を行う必要はありません)。
これで 1 と 0 で構成される 1 番目の配列が作成できました。この配列と売上のデータが入力されている配列を SUMPRODUCT 関数で乗算することで、指定した月と担当者の売上の合計を集計しています。
今回使用した関数の解説は下記を参照されてください。
-- --
Excel の関数である SUMIFS 関数および SUMPRODUCT 関数を使ってクロス集計を行う方法について解説しました。
( Written by Tatsuo Ikura )
著者 / TATSUO IKURA
プログラミングや開発環境構築の解説サイトを運営しています。