ピボットテーブルを使わなくても、SUMIFS関数でらくらくクロス集計

ピボットテーブルを使わなくても、SUMIFS関数でらくらくクロス集計

ピボットテーブルを使わなくても、

SUMIFS関数でらくらくクロス集計

ピボットテーブル、使ってますか?

ビジネスの現場でよく用いられる「クロス集計表」(2軸、もしくは3軸での多面的データ集計)を作成するのに、とても便利な機能です。

「でも、どうもうまくピボットテーブルを使いこなすことができないなぁ」とお悩みのアナタ。まだ、ほかの手がありますよ。

この記事では、SUMIFS関数を使って、ピボットテーブルを使わずに2軸のクロス集計表をまとめるまでの手順をご紹介します。

サンプル資料として、一般家庭の家計簿データを用いていますが、これを職場の「売上日報」や「商品管理台帳」に置きかえてみれば、同じような集計を、関数だけでカンタンにできることがおわかりいただけるでしょう。

Excel関数を覚える練習ついでに「家計の見直し」もして、月々のおこづかいアップを目指しちゃいましょう!!

SUMIFS関数って何?

SUMは、数式の引数に入れた数値、もしくは合計対象範囲の値を加算する関数です。

構文: SUM(数値1,[数値2],...)、もしくはSUM(合計対象範囲)
例: =SUM(10,2) =SUM(A1:A5)

SUMIFは、引数で指定した合計対象範囲の値を、検索条件に合致する場合のみ加算する関数です。

構文: SUMIF(範囲, 検索条件, [合計対象範囲])
例: =SUMIF(A1:A5,”リンゴ”,B1:B5)

※A列に並ぶくだもの名称から、リンゴだけを抜き出して個数を合計

では、SUMIFS関数とはどのような関数でしょうか? IFSのSは「複数」を意味しますので、検索条件が複数ある、ということです。最大127組までの条件範囲と条件を指定できます(Excel2007以降で利用できます)。

構文: SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)

では、実際にこのSUMIFS関数を使って、家計簿ソフトからダウンロードしたCSVファイルを加工し、家計分析をしてみましょう。

ピボットテーブルを使わなくても、SUMIFS関数でらくらくクロス集計

SUMIFS関数を使って、月ごと/支出費目ごとの金額を集計

上の家計簿をまとめたのは、東京都内在住Sさんの奥さん。家計簿アプリを、銀行口座やクレジットカード会社のマイページと連携して、手入力した現金支払分といっしょに、スマホ上で一元管理しています。

とある休日の午後、Sさんが仕事づかれでゴロゴロしていると、とつぜん、静寂を切り裂くカン高い悲鳴が響きわたりました。

「キャ〜ァァァ!!!」

何ごとかとあわててダイニングルームへかけつけると、奥さんがスマホを片手に、頭を抱えています。「先月分のクレジットカード支払が大変なことに...。」とスマホ画面をSさんに突きつけます。

「せっかくの休養日なのに...」とため息をつきながら、Sさん、にわか家計調査隊として支出急増の原因分析に取りかかります。

家計費目は、標準のものから変えていませんので、

  • 食費
  • 通信費
  • 現金・カード
  • 水道・光熱費
  • 交通費
  • 教養・教育
  • 住宅
  • 自動車
  • 健康・医療
  • 交際費
  • 趣味・娯楽
  • 日用品
  • 保険
  • 衣服・美容
  • 特別な支出
  • 税・社会保障
  • その他
  • 未分類
  • 収入

という分類にしています。

まず、パソコンから家計簿ソフトのマイページにログインして、過去1年間分の全家計簿データ(2000件超ありました)をCSVダウンロードしました。その一覧データを、「縦軸に家計費目」「横軸に月(12ヶ月分)」へとクロス集計します。

日付データは”2020/01/01”という形式で、Excelが問題なく日付データと認識しそうではありましたが、今後いろいろな加工をする可能性も考えてシリアル値化。

ついでに、どの月に含まれるデータかを切り分ける判定基準として、各月の月初日・月末日を確認する参照テーブルを作成しました。

ピボットテーブルを使わなくても、SUMIFS関数でらくらくクロス集計

「費目ごと」「月ごと」のクロス集計表を作成するために組み立てたSUMIFS関数がコレ。

ピボットテーブルを使わなくても、SUMIFS関数でらくらくクロス集計

2020/1月の食費合計算出方法

(※以下、他の費目、他の月の計算方法も、参照セルを変えるだけで同様の処理)

=SUMIFS(E:E,G:G,$L2,C:C,">="&$O$2,C:C,"<="&$O$3)

E列 ; 合計対象範囲(金額)

G列 ; 条件範囲1(参照する支出費目テーブル)

$L2 ; 条件1(食費)

C:C ; 条件範囲2(支出日付)

“>=”&$O$2 ; (日付のシリアル値が43831(2020/01/01)以上

C:C ; 条件範囲3(支出日付)

“<=”&$O$3 ; (日付のシリアル値が43861(2020/01/31)以下

SUMIFS関数を使って出来上がった、1年間(12ヶ月間)の家計支出クロス集計表がコレ。

ピボットテーブルを使わなくても、SUMIFS関数でらくらくクロス集計

「原因判明!」Sさんが、皮肉っぽい口調で奥さんに説明します。

「問題は”食べ過ぎ”です。11月から12月にかけての食費の集計金額をみてごらん。11月95,772円、12月100,672円って、家族4人なのに一体どれだけのご馳走を食べてるっていうんだろう。そう思わないかい?」

「ちなみに、このSUMIFS関数の「合計対象範囲」「条件範囲」は、全部「セル範囲」ではなくて「列全体」(C列/E列/G列)で指定しておいたから。今後、家計簿データがどれだけ増えていっても、同じ計算式でクロス集計できるはずだよ。」

奥さんはムスッと黙ってしまいましたが、一応納得はしたようで、この議論はここまでで終わりとなりました。

まとめ

ピボットテーブルを使わなくても、SUMIFS関数でクロス集計は可能です。

将来のデータ拡張性を考えて、引数は列全体で指定しておきましょう。

やはり頻繁にクロス集計をしなおしたり、分析手法を変えて多面的な見方をするためには、ピボットテーブルに慣れてしまった方が、楽で正確です。

けれども、引数の入れ間違えなどをしなければ、関数だけでも正確なクロス集計表を作ることは十分可能です。

たとえば、家計簿データであったり、職場の「商品台帳」のような、定型大容量のデータでしたら、まずは関数だけでもデータ分析可能なので、実際に手を動かして集計してみてください。

昨日奥さんをムッとさせてしまったSさん、今日は仕事帰りに晩酌用の缶ビールを2本買って帰宅したら、奥さんに「昨日の集計表、分析しといたわよ〜。」と言われました。

嫌な予感しかせず、スーツも脱がずにノートPCを立ち上げると、思わずため息をもらしました。いつの間に「条件付き書式」なんていう小技を使えるようになったんだろう。Sさんの作ったクロス集計表の、食費が月8万円を超えている月に、「飲みすぎ!」マークをつけられてしまいましたとさ、おしまい、おしまい......。

ピボットテーブルを使わなくても、SUMIFS関数でらくらくクロス集計

小さなお悩みでも、
お気軽にご相談ください!

お急ぎの方はお電話にてお問い合わせください

050-6867-2130
せるワザロゴ

セルワザでは、役に立つEXCELワザをご紹介しております!