前回の記事「共働きパパの「家計の見える化」」で紹介しましたExcelのピボットテーブルで作る簡単家計簿の作り方を画像付きで分かりやすく説明していきます。
本記事で紹介する簡単家計簿は私自身2019年から使っている家計簿になります！
また、家計簿アプリを使っているけど、機能が多すぎて使い方が分からない、無料版だといろいろ制限があるなど、家計簿アプリではなくExcelのピボットテーブルで作る家計簿に興味がある方は参考にして頂ければと思います。
ピボットテーブルとは
Excelのピボットテーブルとは、膨大なデータを「ドラッグ＆ドロップ」という直感的な操作だけで、多角的に集計・分析できる非常に強力な機能です。
- 数千、数万行あるデータから、カテゴリごとの合計値や平均値を一瞬で算出できます。
- 行と列を入れ替えるだけで、「月ごとの支出」から「項目ごとの推移」へ、瞬時に視点を切り替えられます。
- 集計された数字をダブルクリックするだけで、その内訳（元データ）を別シートに展開し、詳細を確認できます。
家計簿完成イメージ
この記事で紹介する家計簿の完成イメージになります。
※金額等の値はサンプル値になっています。
図１：家計簿完成イメージ
家計簿作成手順
それではさっそく、家計簿の作成手順をご紹介します。
① ピボットテーブルの元となる表を作成
まずは、Excelを開いてピボットテーブルの元となる表を作成します。
前回ご紹介した記事「共働きパパの「家計の見える化」」で『マネーフォワード ME 』からデータを取得して貼り付ける表になります。
今回ご紹介する家計簿の列は[年]、[月]、[日]、[項目名]、[大カテゴリ]、[中カテゴリ]、[小カテゴリ]、[金額]、[メモ]としています。
表の[年]、[月]、[日]、[項目名]、[大カテゴリ]、[中カテゴリ]、[小カテゴリ]、[金額]、[メモ]に記載する内容を表に纏めました。
| 項目 | 説明 | 記入例 |
|---|---|---|
| 年 | 収支の年 | 「25年」（2025年） |
| 月 | 収支の月 | 「01月」 |
| 日 | 収支の日 | 「01日」 |
| 項目名 | 収支の項目名称 | 「〇〇〇スーパー」 |
| 大カテゴリ | 固定値「収入」または「支出」 | 「収入」または「支出」 |
| 中カテゴリ | 家計簿の一番目のカテゴリ階層 | 「食費」 |
| 小カテゴリ | 家計簿の二番目のカテゴリ階層 | 「食料品」、「外食」 |
| 金額 | 収入または支出の金額 | 「-1980」 |
| メモ | メモ | 「〇〇の誕生日で〇〇購入」 |
図２：ピボットテーブルの元となる表
② ピボットテーブルの作成
[テーブルまたは範囲から(T)]を選択
『挿入』タブの『ピボットテーブル』→『テーブルまたは範囲から(T)』を選択すると『テーブルまたは範囲からのピボットテーブル』ダイアログが表示されます。
図３：ピボットテーブルの元となる表（テーブルまたは範囲から(T)）
[テーブルまたは範囲からのピボットテーブル]ダイアログ設定
『テーブルまたは範囲からのピボットテーブル』ダイアログの設定を行います。
設定する内容について説明します。
図４：テーブルまたは範囲からのピボットテーブル
表または範囲の選択
ピボットテーブルで集計・分析したい「元データ」の範囲を指定するエリアです。
今回は『① ピボットテーブルの元となる表を作成』で作成した表を選択します。
ピボットテーブルを配置する場所を選択してください
作成するピボットテーブル（集計画面）をどこに出力するかを選びます。今回は[新規ワークシート(N)]を選択します。
- [新規ワークシート(N)]：現在のシートとは別に、新しいシートを自動で作成し、そこにピボットテーブルを配置します。
- [既存のワークシート(E)]：すでに存在するシートの、指定したセルにピボットテーブルを埋め込みます。
複数のテーブルを分析するかどうかを選択
[このデータをデータモデルに追加する(M)]：複数の異なるテーブルをリレーション（関連付け）して、一つのピボットテーブルでまとめて分析したいときにチェックします。今回はチェックなしになります。
設定後のピボットテーブル
『テーブルまたは範囲からのピポットテーブル』を設定して『OK』ボタンを押下すると別シートにピボットテーブルが作成されます。
図５：設定後のピボットテーブル
③ フィールド設定
別シートにピボットテーブルを作成することができたら次にピボットテーブルの各フィードの設定を行います。
使用するフィールドの振り分け
ピボットテーブルに使用する[年]、[月]、[大カテゴリ]、[中カテゴリ]、[小カテゴリ]、[金額]のチェックボックスにチェックを入れます。
『列 』に[年]、[月]、『行』に[大カテゴリ]、[中カテゴリ]、[小カテゴリ]、『値』に[合計／金額]を設定します。
※ドラッグ＆ドロップで移動できます。
図６：フィールド振り分け後のピボットテーブル
各フィールドの設定
フィールドの[▼]をクリックして『フィールドの設定』を選択して『フィールドの設定』ダイアログでフィールドの設定を行います。
図７：フィールドの設定
全てのフィールドに対して『フィールドの設定』ダイアログの項目を設定します。
図８：「フィールド設定」ダイアログ
『フィールドの設定』ダイアログの各項目の説明と設定内容は下の通りになります。
小計
自動で小計行を挿入するかどうかを決めます。今回は全てのフィールドに対して[自動(A)]を設定しています。
- [自動(A)]：Excelがデータの種類を自動判別して、最適な小計を自動で表示します。
- [なし(E)]：小計行を一切表示せず、フラットな明細だけを並べます。
- [指定(C)]：合計だけでなく、[平均]、[最大]、[最小]など、独自の計算方法で小計を出したいときに選択します。
フィルター
新しい項目が追加された際に自動的に集計対象（フィルターのチェックON状態）に含める設定です。
[新しいアイテムを手動フィルターに含める(I)]にチェックが入っていないと、新設された項目のデータがピボットテーブル上で無視されてしまいます。
今回は全てのフィールドの「新しいアイテムを手動フィルターに含める(I)」にチェックをしています。
レイアウト
データの並び方を指定します。
- ① [アイテムのラベルをアウトライン形式で表示する(S)]：1行目に[大カテゴリ（食費）]、2行目に下がって[中カテゴリ（食料品）]というように、階段状にズラして表示する形式です。
- ② [アイテムのラベルを表形式で表示する(I)]：[大カテゴリ]、[中カテゴリ]、[小カテゴリ]がそれぞれ独立した列にきれいに整列する形式です。
- ③ [アイテムのラベルを繰り返す(R)]：チェックを入れると、空欄になっている部分（食費の配下にある行など）にもすべて「食費」「食費」と文字が設定されます。
- ④ [アイテムのラベルの後ろに空行を入れる(B)]：大カテゴリの区切り（例：食費の集計行の下）に、自動で1行空行（空白行）を挿入して、視覚的なメリハリをつけます。
- ⑤ [データのないアイテムを表示する(W)]：「今月は一度も使わなかったカテゴリ（実績0円）」であっても、行としてスキップせずに「0」または空欄のまま表に残す設定です。
各フィールドのレイアウトの設定内容を纏めています。（図上の①～⑤は上のリストの番号に対応しています。）
| フィールド | ① | ② | ③ | ④ | ⑤ |
|---|---|---|---|---|---|
| 年 | 〇 | チェックなし | チェックなし | チェックなし | |
| 月 | 〇 | チェックなし | チェックなし | チェックなし | |
| 大カテゴリ | 〇 | チェックなし | チェックなし | チェックなし | |
| 中カテゴリ | 〇 | チェックなし | チェックなし | チェックなし | |
| 小カテゴリ | 〇 | チェックなし | チェックなし | チェックなし |
印刷
紙やPDFで印刷する際、大カテゴリが変わるタイミングで自動的に改ページする設定です。
今回、全てのフィールドの[アイテムの後ろに改ページを入れる(P)]のチェックは外しています。
設定後のピボットテーブル
各フィールドの設定を行うと下の図のピボットテーブルが完成します。
図９：設定後のピボットテーブル
④ デザインの設定
以降はデザインの設定になりますので自分に合ったデザインを選択してください。
この記事では私が使っている家計簿の設定内容を説明します。
レポートのレイアウト
『デザイン』タブの『レポートのレアウト』からレイアウトを選択します。
この記事では[表形式で表示(T)]を選択しています。
- [コンパクト形式で表示(C)]：Excelのデフォルトの形式です。[大カテゴリ]、[中カテゴリ]、[小カテゴリ]を設定しても、それらがすべて「A列（1つの列）」の中に階段状に折りたたまれて表示されます。
- [アウトライン形式で表示(O)]：[大カテゴリ]、[中カテゴリ]がそれぞれ独立した列に分かれますが、データの始まりの行が一段ずつ下にズレるため、少し階段のような余白が残るデザインになります。
- [表形式で表示(I)]：A列に[大カテゴリ]、B列に[中カテゴリ]、C列に[小カテゴリ]となり最上部にそれぞれの見出し（ヘッダー）が明示されます。
- [アイテムのラベルをすべて繰り返す(R)]：空白になっているセル（例：[食費]の配下にある複数の中カテゴリの横など）に、すべて[食費]、[食費]と文字を補完して埋め尽くす設定です。
- [アイテムのラベルを繰り返さない(N)]：同じカテゴリが連続する場合、2行目以降の文字を非表示します。
図１０：レポートのレイアウト
図１１：表形式で表示
ピボットテーブルのスタイル
『デザイン』タブの『ピボットテーブル スタイル』から自分のお気に入りのスタイルを選択してください。
本記事では自分で色など決めたいので、『ピボットテーブル スタイル』の[なし]を選択して自分で色などスタイルを設定しています。
図１２：ピボットテーブル スタイル
『ピボットテーブル スタイル』の[なし]を選択すると下の図のような表になります。
図１３：ピボットテーブル スタイル（なし）
独自のスタイル設定
最後に自分の好きなセル色や罫線の種類等を設定すれば完成です！
[中カテゴリ]の順番を変更（例：食費を一番上にする）したい場合は[中カテゴリ]を選択した状態で上にあげれ簡単に移動できます。
図１４：ピボットテーブル（完成）
まとめ
今回は、Excelのピボットテーブルを使った家計簿の作り方を紹介しました。
ピボットテーブルと聞くと難しそうに感じますが、実際に使ってみると、「月別の支出集計」、「カテゴリごとの分析」、「固定費の確認」などを、意外と簡単に行うことができます。
特に家計簿との相性は非常によく「何となくお金を使っている状態」から「どこにどれくらい使っているか分かる状態」へ変わるだけでも、家計管理はかなりラクになります。
我が家では
- クレジットカードで支払いを集約
- マネーフォワード ME でデータ管理
- Excelで分析
という流れで家計を見える化しています。
ピボットテーブルは、一度作ってしまえば毎月の分析もかなりラクになります。
家計を分析して改善する第一歩として、ぜひ試してみてください。