• exceleaveit

ピボットテーブルを使わなくても、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関数を使って、月ごと/支出費目ごとの金額を集計


上の家計簿をまとめたのは、東京都内在住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万円を超えている月に、「飲みすぎ!」マークをつけられてしまいましたとさ、おしまい、おしまい......。




ExceLeaveITでは分析シュミレーションツールや、SUMIFS数を活用した業務効率化ツールを提供しております。

是非一度、お問い合わせください!お問い合わせはこちら!


3回の閲覧0件のコメント