ピボットテーブル、使ってますか?
ビジネスの現場でよく用いられる「クロス集計表」(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関数を使って、月ごと／支出費目ごとの金額を集計
上の家計簿をまとめたのは、東京都内在住Sさんの奥さん。家計簿アプリを、銀行口座やクレジットカード会社のマイページと連携して、手入力した現金支払分といっしょに、スマホ上で一元管理しています。
とある休日の午後、Sさんが仕事づかれでゴロゴロしていると、とつぜん、静寂を切り裂くカン高い悲鳴が響きわたりました。
「キャ〜ァァァ!!!」
何ごとかとあわててダイニングルームへかけつけると、奥さんがスマホを片手に、頭を抱えています。「先月分のクレジットカード支払が大変なことに…。」とスマホ画面をSさんに突きつけます。
「せっかくの休養日なのに…」とため息をつきながら、Sさん、にわか家計調査隊として支出急増の原因分析に取りかかります。
家計費目は、標準のものから変えていませんので、
- 食費
- 通信費
- 現金・カード
- 水道・光熱費
- 交通費
- 教養・教育
- 住宅
- 自動車
- 健康・医療
- 交際費
- 趣味・娯楽
- 日用品
- 保険
- 衣服・美容
- 特別な支出
- 税・社会保障
- その他
- 未分類
- 収入
という分類にしています。
まず、パソコンから家計簿ソフトのマイページにログインして、過去1年間分の全家計簿データ(2000件超ありました)をCSVダウンロードしました。その一覧データを、「縦軸に家計費目」「横軸に月(12ヶ月分)」へとクロス集計します。
日付データは”2020/01/01”という形式で、Excelが問題なく日付データと認識しそうではありましたが、今後いろいろな加工をする可能性も考えてシリアル値化。
ついでに、どの月に含まれるデータかを切り分ける判定基準として、各月の月初日・月末日を確認する参照テーブルを作成しました。
「費目ごと」「月ごと」のクロス集計表を作成するために組み立てた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ヶ月間)の家計支出クロス集計表がコレ。
「原因判明!」Sさんが、皮肉っぽい口調で奥さんに説明します。
「問題は”食べ過ぎ”です。11月から12月にかけての食費の集計金額をみてごらん。11月95,772円、12月100,672円って、家族4人なのに一体どれだけのご馳走を食べてるっていうんだろう。そう思わないかい?」
「ちなみに、このSUMIFS関数の「合計対象範囲」「条件範囲」は、全部「セル範囲」ではなくて「列全体」(C列/E列/G列)で指定しておいたから。今後、家計簿データがどれだけ増えていっても、同じ計算式でクロス集計できるはずだよ。」
奥さんはムスッと黙ってしまいましたが、一応納得はしたようで、この議論はここまでで終わりとなりました。
まとめ
ピボットテーブルを使わなくても、SUMIFS関数でクロス集計は可能です。
将来のデータ拡張性を考えて、引数は列全体で指定しておきましょう。
やはり頻繁にクロス集計をしなおしたり、分析手法を変えて多面的な見方をするためには、ピボットテーブルに慣れてしまった方が、楽で正確です。
けれども、引数の入れ間違えなどをしなければ、関数だけでも正確なクロス集計表を作ることは十分可能です。
たとえば、家計簿データであったり、職場の「商品台帳」のような、定型大容量のデータでしたら、まずは関数だけでもデータ分析可能なので、実際に手を動かして集計してみてください。
昨日奥さんをムッとさせてしまったSさん、今日は仕事帰りに晩酌用の缶ビールを2本買って帰宅したら、奥さんに「昨日の集計表、分析しといたわよ〜。」と言われました。
嫌な予感しかせず、スーツも脱がずにノートPCを立ち上げると、思わずため息をもらしました。いつの間に「条件付き書式」なんていう小技を使えるようになったんだろう。Sさんの作ったクロス集計表の、食費が月8万円を超えている月に、「飲みすぎ!」マークをつけられてしまいましたとさ、おしまい、おしまい……。
