EXCEL
SUMIFS関数マトリクス表月次推移表

SUMIFS 関数を使って 縦軸が品名 横軸が月 の マトリクス 集計表 を作る 販売実績 の分析

2021.05.26

今回は、月次の分析に使えるマトリックス集計表を、 SUMIFS 関数を使って作成する方法を解説しています。

マトリックス集計表とは、縦軸に商品名、横軸に販売した月を配置して、商品毎の各月の販売実績が一目で分りやすく集計した表のことです。

いつ、何の商品を、価格いくらで販売したかを、商品を販売する度に、データが蓄積されています。

このデータを使って、マトリックス集計表に集計することで、どの商品が何月に多く売れたのか、月毎の販売傾向が分りやすく、翌年の販売計画、仕入計画を立てる時の元情報として使っています。

意外と簡単で、有効な表ですから、是非使ってみて下さい。

また、一度作成すれば、あとは随時販売データを追加していくだけで、マトリクス表は自動で更新されていきますので、毎回いちいち作成し直す必要がなくなり、手間が省けて効率がるメリットもあります。

SUMIFS 関数の仕様

＝SUMIFS（合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …）
合計対象範囲とは、列を指定します。この列のうち、次の引数以降で指定する条件に一致した行の値を合計します。
指定した条件範囲と、指定した条件の組み合わせで、一致する行を探します。一致する行の値を合計します。条件範囲と条件の組み合わせは、複数指定できます。

では、実際にやってみましょう

次の販売実績表を例として、マトリクス表を作成していきます。

月日の列に販売した日付、販売品の列に販売した商品名、金額の列に販売金額を入力しています。

そして、その横に集計用の表を作成します。

縦軸（G列）に品名、横軸に月（H~J）を準備し、H4～J6へ集計します。

月単位で集計するため、元の表に月の列を追加で作成します。

日付から月を求める関数｜MONTH関数を使います

E4のセルを選択し、＝MONTH(B4)＆”月”　と入力します。すると、4月が表示されました。そのセルを、下にコピーします。

SUMIFS 関数を入力する

H4は、4月に販売したノートの金額。ということです。

SUMIFS 関数のカッコの中には次のように式を記入します

=SUMIFS($D$4:$D$8, $E$4:$E$8, H$3, $C$4:$C$8, $G4)
  • 最初の式：合計対象範囲ですから、D4:D8の絶対参照で$D$4:$D$8
  • 二番目の式：条件範囲1には、「4月」を検索する範囲、E4:E8の絶対参照で$E$4:$E$8
  • 三番目の式：条件1には、検索条件「4月」が入力されたセル、H3の混合参照でH$3
  • 四番目の式：条件範囲2には、「ノート」を検索する範囲、C4:C8の絶対参照で$C$4:$C$8
  • 五番目の式：条件2には、検索条件「ノート」が入力されたセル、G4の混合混合参照で$G4
  • 合計対象範囲、検索範囲1および2などのように、○○範囲は固定するため、絶対参照にします。
  • 三番目の式は、4月、5月、6月と進むとカラムはHからI、Jと変化するのでアドレスのHは相対参照、変化させたくない3を絶対参照とします。
  • 5番目の式は、逆に行方向には変化させたいが、列方向は固定したいので、Gを絶対参照4を相対参照とします。
SUMIFS

4月に購入したノートは150円しかないので、H4のセルには150と表示されました。

H4の式を、J6までコピーします。

H4をコピーして、罫線などそのままにしたいので、数式の貼り付けでペーストします。

列の合計と行の合計を、一気に一瞬で入力する。

さらに、H4:K7を選択して、[Shift]+[Alt]+[=]を同時に押して見てください。
一気に縦計と横計の SUM 関数が入力されました。

マトリクス集計表を見ると、次のことが判ります。

月間の売上推移は、4月、5月は450円あった売上が、6月は250円に減りました。
3ヶ月の売上は、1150円なので、年間ではその4倍の4600円くらいと見込めます。
商品別では、ノートが一番多く売り上げていますが、6月は売上なしとなり、逆に定規の売上が200円発生しています。人気品目が移行しているかもしれません。
今後の売上アップを狙うには、ノートの売り上げを元に戻すよう、対策すればよいかもしれません。

このように、集計することで、現状の把握と、次の施策の立案、年間予測など色々なことが判るようになります。

まとめ

  1. 販売の月日、品名、金額を記録していくことで、データが蓄積します。このデータを元に、縦軸に品名、横軸に月のマトリックス集計表を作成するには、 SUMIFS 関数を使います
  2. SUMIFS 関数のパラメータ指定は、○○範囲は絶対参照、条件は縦軸項目はカラム固定、横軸項目は行固定の混合参照で行うと、コピーが簡単にできる
  3. 縦合計、横合計を追加して、分析しやすくする

因みに、合計範囲、条件範囲は未入力の行も含めて、下に長くとっておくと、後でデータを追加するごとに式を変更することなく集計表が更新されていきます。
例では、D4D8をD4D999に、E4E8をE4E999、C4C8をC4C999にしておくという感じです。

今回も有り難うございました。

コメント

タイトルとURLをコピーしました