【Excel】「貯める」「殖やす」に役立つExcel③ Excel家計簿の作成方法
家計簿の完成図
Excelの集計機能であるピボットテーブルを使って月ごとに費目別集計が出来ます。
データ入力に必要な2枚のシート
上図の左側が「家計簿入力表」シートで、右側が「費目」シートです。
「家計簿入力表」シート
レシートや領収書の内容を入力するためのシートです。
4行目に「取引件数」「収入合計」「支出合計」「現在の残高」の集計欄を設定しています。
また、6行目以降だけが正しく集計できるように、5行目は空白行にしておきます。
「費目」シート
費目をVLOOKUP関数で入力するためのシートです。
ピボットテーブルを使った費目別集計の際、入力表に「給与」と入力すべきところを「給料」と入力してしまうなどの間違いがあると正しく集計されません。
VLOOKUP関数を使用すれば、入力間違いを防ぐとともに入力の手間を減らすことが可能です。
家計簿入力表に必要な数式
4行目の数式を作成する
4行目には「取引件数」「収入合計」「支出合計」「現在の残高」を表示させます。
今後もずっとデータを入力するため、集計範囲を2000行に設定しておきます。
①セル「A4」：取引件数
- 数式：＝COUNT(A7:A2000)
- 数式の意味：A列に入力された日付の数を数える
②セル「G4」:収入合計
- 数式：＝SUM(G7:G2000)
- 数式の意味：G列に入力された収入の金額を合計する
③セル「H4」:支出合計
- 数式：=SUM(H7:H2000)
- 数式の意味：H列に入力された支出の金額を合計する
④セル「I4」：残高
- 数式：＝G4-H4
- 数式の意味：収入合計から支出合計を引き算する
曜日の数式を入力する
⑤セル「B7」：曜日
- 曜日の数式：＝TEXT(A7,”aaa”)
- 数式の意味：A列にある日付から曜日を求める
さらに、IF関数を使って空白処理を行います。
空白処理とは、「もしも、参照元が空白（空欄）なら、計算結果も空欄にする」という処理のことです。
以下のように数式を作成します。
- 空白処理：＝IF(A7=””,””,TEXT(A7,”aaa”))
費目の数式を入力する
⑥セル「F7」：費目
- 費目の数式：＝VLOOKUP(E7,費目シート!$A$3:$B$26,2,0)
- 数式の意味：費目シートにあるデータを検索する
さらに、IFERROR関数を使ってエラー処理を行います。
VLOOKUP関数の数式が入力されているセルは、検索値の参照セルが空欄の場合に、エラーが表示されてしまいます。
このエラー処理には、エラー値を別の値に置き換えるIFERROR関数を使用します。
- エラー処理：＝IFERROR(VLOOKUP(E7,費目シート!$A$3:$B$26,2,0),””)
ちなみに、曜日の数式を入力する際に使用したIF関数の空白処理と、IFERROR関数のエラー処理はほとんど同じことが出来ます。
今回IFERROR関数を使用した数式をIF関数の空白処理に変更しても問題ありません。。違いは、強いて言えば以下の通りです。
- エラー処理（IFERROR関数）はエラーにしか対応できない
- 空白処理（IF関数）はエラーにもエラー以外にも対応できる
残高の数式を入力する
残高は「7行目」と「8行目以降」で数式が異なります。
8行目以降には「前回の残高＋今回の収入－今回の支出」という数式を作成します。
⑦-1 セル「I7」
- 残高の数式：＝F7
- 数式の意味：繰越金の収入をそのまま参照する
⑦-2 セル「I8」
- 残高の数式：＝I7+G8-H8
- 数式の意味：「前回の残高」と「今回の収支」で残高を求める
- 空白処理：＝IF(A8=””,””,I7+G8-H8)
数式を作成したら、曜日・費目・残高の数式をオートフィルでコピーします。
残高は8行目の数式をコピーします。
入力規則の設定（リスト入力）
E列にある費目用のコードをリストから入力できるように設定します。
コードの列を選択したら、「データ」タブにある「データの入力規則」ボタンをクリックしてウィンドウを開きます。
「データの入力規則」ウィンドウが立ち上がったら
- 「設定」タブをクリックします
- 「入力値の種類（A)」を「リスト」にします
- 「元の値（S)」に「費目シート」のセル範囲「A3:A26」を絶対参照で指定します（$A$3：$A$26)
- 「OK」ボタンをクリックします
また、C列の「決済」には銀行名などの入出金の経路を直接リスト設定しています。
２つのシートを並べて表示する
費目をコードから入力する場合、画面にコードが表示されていると入力しやすいので、「入力表シート」と「費目シート」を左右に並べて表示させます。
- 「表示」タブをクリックします
- 「新しいウインドウを開く」ボタンをクリックすると新しいウィンドウが開かれます
- 続けて「表示」タブをクリックします
- 「整列」ボタンをクリックします
- 「左右に並べて表示」をクリックします
- 「OK」ボタンをクリックします
左右に2つのウィンドウが表示されたら、右側のウィンドウを費目シートに切り替えます。
この状態で、費目シートを見ながらデータ入力を行います。
ウィンドウ枠の固定
常に6行目の見出しが表示されるようにウィンドウ枠を固定します。
- 7行目全体を範囲選択します
- 「表示」タブをクリックします
- 「ウィンドウ枠の固定」ボタンをクリックし、一覧から「ウィンドウ枠の固定（F）」をクリックします
ピボットテーブルで集計する
エクセルの集計機能の中でも、便利で使いやすいのがピボットテーブルです。今回の例では、毎月の収入と費目別の支出を集計しています。
ピボットテーブルの挿入
「家計簿入力表」シートをもとにピボットテーブルを作成します。
- 表内セルをアクティブにします
- 「挿入」タブをクリックします
- 「ピボットテーブル」ボタンをクリックします
- 集計したい表の全体が選択されていることを確認し、「OK」ボタンをクリックします
集計項目を並べる
ピボットテーブルを作成したら集計内容を決めていきます。
画面右側の作業ウィンドウで操作をします。
ピボットテーブルにデータが表示されました。
このデータをもとに、毎月の収入に対する費目別の支出割合を求めることが出来るようになります。
私は以下のような表を作成して支出の割合を求めています。
(おわり)
