前回は、家計簿にまつわる「これまでの失敗」と、そこから導いた「目標」と「目的」を整理しました。
今回は、その目的を実現するための土台となる
Excel入力設計と、Power Queryによるデータ加工・Power BIモデリング
について解説します。
なぜ加工やモデリングが必要か？ それは、
人が使いやすい入力形式と、分析しやすいデータ構造は一致しない
ためです。このギャップを埋めることが、継続できる家計簿設計の肝となります。
前回記事はこちらから👇
妻からの「5つの要望」と設計思想
今回の家計簿構築では、ユーザー（妻）の要望をよく聞いたうえで、設計をスタートしました。
ユーザーの要望
- 予算は月単位で費目ごとに設定したい
- 収入は種類別で管理したい（給与・賞与・児童手当など）
- 支出はレシート単位で確認しながら入力したい
- 入力はできるだけ楽にしたい（リスト選択・自動入力）
- 固定費は毎月同じ形で入力したい
設計思想：あえて「自動化しない」
一般的には「クレカ連携」や「レシート自動読み取り」が推奨されますが、我が家ではあえて「手入力」を採用しました。これは、費目の精度を上げることと、支出を見直すきっかけを作るためです。
① 費目の振り分けを正確におこなうため
買い物をすると、複数の費目が混在することは多々あります。例えばスーパーの買い物では、
- 食費
- お酒やお菓子等の嗜好品
- 日用品
などが混ざるので、これらを正確に分けるためには人の判断が不可欠です。自動入力の場合は、クレンジング工数がかかるため、手間自体はそこまで変わらないと判断しました。
② レシートチェックを通して支出を意識するため
レシートを見ることで
- 「これは本当に必要だったか」
- 「今安いから、次はまとめ買いしよう」
といった気づきが生まれます。せっかくかけた労力を活用し、行動変容に繋げます。
入力設計：入力しやすさと分析しやすさを両立する5つのExcelシート
入力と分析を両立するため、４つのデータ入力シート（①支出予算、②収入実績、③変動費実績、④固定費実績）と分析軸シート（⑤項目, ３つの表）の計５つで構成しました。
①支出予算（月次更新）
入力のしやすさを考え、あえて「横持ち（縦軸：費目、横軸：年月）」にしています。
費目別にデータが横に並んでいると比較しやすく、入力漏れ・重複が起こりにくいためです。
※後工程で「縦持ち」に変換します。
②収入実績（月次更新）
収入は種類別に管理（給与・賞与・児童手当）します。
給与、賞与以外にも入金（お祝い金等）があることを考慮し1行1データで入力できる「縦持ち」としました。これにより、備考も入力できます。
③変動費実績（メイン、日次更新）
最もデータ数が多く、決済ごとにデータが発生するため「縦持ち」とした。
キー概念：2つの口座
- 本来支出口座 →費目に紐づく「本来負担すべき口座」
- 実際引落口座 →支払方法に紐づく「実際に引き落とされる口座」
なぜ２つの口座を分けて記載するか？
例えば、「夫小遣い」による支出は、
「本来支出口座：夫・銀行C（夫小遣い口座）」から引き落とすべきですが、QR決済Aを使うと、「実際引落口座夫・銀行E（家計口座）」から引き落とされてしまいます。
→このズレを管理し、精算して解消しないと、資金管理が破綻します。
実装
- VLOOKUPで口座を費目や支払い方法に自動紐づけ
- IF式で「精算要否」を判定
（基本は、本来支出口座「＝」実際引落口座なら「精算なし」、「≠」なら「精算あり」）
→月次で精算（口座へ資金を移動）します。
④固定費実績（月次更新）
変動費と同じ構造の表を使用します（後工程で結合するため）。費目の並びや個数の変動はないため、フォーマットをコピペし、金額を修正する運用としています。
⑤項目マスタ（分析軸, 固定）
表記ゆれ防止、並び順制御のためにマスタをExcel側で作成しておきます。
ここをExcel側で持つと、専門知識がない家族でも、Excelさえ直せば分析軸を更新できます。
データ加工：Power Queryで「分析できる形」に変える
加工の概略
- アンピボット：予算シートの「横持ち」データを「縦持ち」データに変換
- テーブル結合：変動費と固定費を統合
- ID付与（キー設定）：マスタと紐づけるためのIDを付与
テーブル構成
ディメンション（マスタ）
ファクト（数値）
なぜこの構成とするのか？
このようにテーブルを分けて管理するのは、
異なる性質のデータを無理に一つにまとめないためです。
- 粒度の違い（日次 vs 月次）
支出は日単位、予算は月単位でデータが登録される
→ 同じ表に入れると、集計や比較が複雑になる - データの性質の違い（予算 vs 実績）
「計画」と「結果」は役割が異なる
→ 分けておくことで、差異分析がしやすくなる
今後データテーブル自体（例：F_投資実績）を加える場合に、追加の接続が容易です。
データ間の接続：Power BIでデータ可視化を準備する
準備したテーブルを「スタースキーマ」で接続します。
スタースキーマとは
スタースキーマとは、データを
ディメンション（分類軸）とファクト（数値）
に分けて管理するデータ構造のことです。ディメンションを外側、ファクトを中央に配置すると星形に見えることからこの名前が付いており、分析や集計をシンプルに行えるのが特徴です。
この構成のメリット
- グラフ作成が直感的にできる
→「どの分類軸で（費目）」「何を集計するか（支出実績）」を選ぶとグラフが作れる - DAX（計算式）がシンプルになる
→複雑な条件を式に記載せずとも、集計や比較がしやすい - データの構造が整理される
→どのデータを使えばよいか迷いにくくなる
つまり
「何を・どう集計するか」を迷わず選べる構造
ができます。
実際の構成を図に示します。
実体験からの注意点
Power Queryは便利ですが、
一度エラーが出ると原因特定に時間がかかります。
そのため、あらかじめエラーの原因を潰しておくことが、継続運用のポイントです。
- 項目名は最初に固める（変更しない前提で設計する）
→ Power Queryは列名をもとに処理するため、途中で名前を変更するとエラーとなる。 - データ読み込みは「シート」ではなく「テーブル」で行う
→ 「シート」読み込みでは、余白メモや不要データも対象となる。
→ Excel側で「テーブル」化することで、必要な範囲だけを読み込める。
これらは一見小さなポイントですが、
「更新できない家計簿」になるリスクを防ぐ重要な設計です。
まとめ
今回構築したのは、
「入力しやすいExcel」と「分析しやすいデータ構造」を両立させた基盤
です。
家計簿を
単なる「記録」ではなく、「意思決定を支えるデータ基盤」
として設計することで、資産形成の強力な武器になります。
次回は、このモデルを活用したビジュアル作成と、DAXによる計算値（メジャー）作成について解説します。お楽しみに！
コメント