ピボットテーブルを使わなくても、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月から1