1級FPがはじめて自分の家計簿を作るまで（第2話）家計支出を集計するややマニアックな方法

このような意欲をもって去年末に家計簿をつくってみることにしました。
前回につづいてその過程をお送りしています！

前回記事：1級FPがはじめて自分の家計簿を作るまで（1）「まずはデータの記録から！」
（まずは家計簿アプリからデータをExcelに移動しました。）

1級FPがはじめて自分の家計簿を作るまで（第1話）「まずはデータの記録から！」

今回は、私が実際におこなったExcelの集計方法をご紹介します。
あまりピンとこないかもしれませんが、「ふーん、こんなことやってるんだね。」とサラッとみていただければ。

Excelを使ってデータを集計してみよう（5ステップ）

支出データをExcelにインポートした後、集計作業にはいりました。
作業は次のように行いました。

【2-1】抽出したCSVデータをエクセルで加工する。
【2-2】ピポットテーブルを作成する。
【2-3】ピボットテーブルからコピペして数値だけのデータを用意する。
【2-4】表に「支出割合」の欄を追加し、簡単な関数を入れる。
【2-5】カテゴリごとに支出の多い順番（降順）に並び替える。

それぞれみていきますね。

（2-1） 抽出したCSVデータを加工する。

Zaimという家計簿アプリからExcelに抜き出したデータを加工します。
まずは直近の3ヶ月分のデータを取得し、下図のようなCSVファイルのインポートします。

このシートでは項目が見づらいのでフィールドを目立つようにハイライトしました。

実はここからが一番大変なのですが、このレコードをざっと見て「重複部分がないか」「カテゴリーに誤りはないか」をチェックしていきます。

「MECE」という言葉のとおり「ぬけやもれがない状態」が理想です。
ただし、完璧にする必要はありません。ざっくりで大丈夫です。

【注意1】「カテゴリ」と「カテゴリの内訳」の分類をチェックしよう

家計簿システムが自動的にカテゴリを仕訳してくれますが、ときどき誤っている場合があるので注意が必要です。

（例）
店名「デパート名」→カテゴリ「衣服代」
店名「カルディ」→カテゴリ「カフェ代」
店名「DMMドットコム（仮想通貨取引）」→カテゴリ「エンタメ」
店名「オンラインサロン」→カテゴリ「美容代」

この場合、それぞれのカテゴリを「食費」「投資」「書籍」などに分類しなおします。
よく利用する店舗は初期設定しておくと楽ですね。

【注意2】支出に関係ない取引や重複部分は行削除しよう

単なる口座振替やPayPayへの入出金など支出に関係ないところは行削除しましょう。
また重複部分があればそれも行削除！

【注意3】未登録の店舗の情報を追加しよう

家計簿アプリに登録していない支払元やお店名についてはデータに反映されていません。その場合、マニュアルでExcelに追加しましょう。

私の場合は「現金」や「PayPay」「JCBカード」などが未登録でしたので、直接Excelに追加しました。

(2-2) ピポットテーブルを作成しよう

データを整備したら、次はいよいよピボットテーブルの作成です。
はじめてピボットテーブルを使うかたはこの記事に基本的な使い方を書きましたのでご参考にしてください。

参考記事：【脱！初心者】家計簿作成の前におさえるべきExcel「ピポットテーブル」の使い方。

ピボットテーブルでは「フィルター」「列」「行」「値」という4つの枠にフィールド（項目）を指定する必要があります。ここで「どのフィールドをどの枠に指定すればいいのか」が命運を左右するのです。

ここでの最適なフィールドは次でしょうね！

列：「日付」
行：「カテゴリ」
値：「支出」

すると次のように月別カテゴリーごとの支出が集計されます。

ピボットテーブルのフィールドは最初のうちは慣れません。
「どのフィールドをどこに配置するか」は試行錯誤しながら、パターンをつかんでいきましょう。

【補足】フィルターに支払い先を追加しておこう

ここで「フィルター」に「支払い元店舗名」を追加しておくことをおすすめします。
なぜなら「どこで支払いが行われているか」という情報は非常に重要だからです。
今後の生活全般において一番改善が図れるデータとなります。


たとえばファミリーマートに1番多く支払いをしている場合、以下の対策が考えられますね。

●ファミペイを入れる（→ファミペイのポイントが貯まります。）
●ファミマのレシート取っておき、日用品についてはファミマよりもっと安いドラッグストアで買う（→単純に買い物コストを下げられます）
●どのようなアイテムを買っているかをチェックしてコンビニに行く回数も減らす（→時間の短縮につながります）

(2-3) 作成したピボットテーブルをコピペして値貼り付けする

作成済みのピボットテーブルは元データとリンクしているので、データだけ利用したい場合は不便ですね。そこで、「値貼り付けのコピペ」をして別の表を用意しましょう！

値貼り付けをすると、データが「数値」になりますのでいろいろと加工ができるようになります。

(2-4)表に「支出割合」の欄を追加し、簡単な関数を入れる。

支出額の数字は算出されましたが、もうひとつデータが欲しいですね。
「どのカテゴリがもっとも支出に影響があるか？」です。

そこで、各カテゴリーの金額が全体の支出額に対してどれだけの割合かを求めてみましょう。

作成したExcelの表に「支出割合」という欄を追加して、「各カテゴリの支出額÷支出総計額」の関数を入力します。

以下のようになります。

ここで注意しておきたいのは、「支出総計額」を固定すること！
つまり、「絶対参照」を使うということです。

具体的には上のスクリーンショットのようにC37のセルの列と行の前に＄を付けます。
（Windowsであれば「F4」のショートカットを使いましょう）
すると、ドラッグしてもコピペしても「C37」の参照位置がずれません。

【余談】
人によっては「支出割合」（各カテゴリー支出額÷総支出額）ではなく「収入に対する割合」（各カテゴリー支出額÷総収入額）を利用する方もいます。

ただ、私は現在「無収入」であるため「支出割合」を利用しました。
そのあたりは好みでいいと思いますが、ここでは便宜上「支出割合」を活用することにします。

(2-5) カテゴリごとに支出の多い順番（降順）に並び替える。

支出割合が出たところで、つぎは順番を整えましょう。
Excelの「並び替え」機能で「降順」になおします。
ここでは単月ではなく3ヶ月の合計額で並び替えましょう。

ここで一応、データを降順に並べ変える方法を記しますね。


①まず「データ」＞「並び替え」をクリック。

②「並び替えるレベルを使い」というポップアップが出るので以下のように設定する。

「列」：「全体の合計/支出」
「並べ替え」のキー：「値」
「順序」：「最大から最小」

このような感じです。

すると3ヶ月の支出合計額をベースとして降順に順番がなおされます。


【注意】
指定範囲に「総計」や「見出し」が入っているとそれも文字列として並べ替えされます。後から修正するか「先頭行を列見出しとする」にチェックを入れて調整しましょう。

Excelは便利だけどデータの管理手段はなんでもOK！

私にとってExcelの機能を使うと家計簿作成は難しくはなかったのですが……手間ですよね。

ここまで書いておいてなんですが、皆さんがここまでExcelに精通しなくてもいいですよね。手入力でもクレカ履歴でもとにかく支出を管理できればそれでOKです。

ということで、次はここまで集めたデータを使って分析してみましょう！

次回記事：1級FPがはじめて自分の家計簿を作るまで（最終話）家計簿は人生で3回つくればそれでいい説 (「べつに家計簿は毎日つけなくてもいいのでは？」という身も蓋もない結論に……）

1級FPがはじめて自分の家計簿を作るまで（最終話）家計簿は人生で3回つくればそれでいい説