エクセル家計簿の作り方(基本編)

こんにちは、macoです。

みなさん家計簿は手書き派ですか？アプリ派ですか？

私はエクセル派です。エクセル家計簿は、毎月発生する項目はコピペ。

入力は月二回程度、ズボラな私が6年続いています。

コンセプトは、”シンプル”

家庭の場合、年間収支だけ分かれば、キャッシュフローは大体見えます。

ネットで探すと無料から有料のものまでありますが、凝っているものが多かったので、やめました。アレンジして、ファイルが破損してしまう可能性を考えると、自分で作った方が早いです。

そして、その経験をきっかけに、このnoteを書きました。

とにかくシンプルがモットーです。

文章で説明するのが難しい部分は動画をいれています。

今後も、総資産管理、年間予算管理、カード決済実績、光熱費分析、ライフイベントと資産形成計画なども、共有していきたいと思います。

まずは、”エクセル家計簿（基本）”の流れをご紹介します。

①データベースシートの作成

A) 一番下、タブの名前を”Data”に変更する。

B)【A1～L1列】まで、”年、月、日、年月日、品名、購入先/支払先、支払い方法、引落し日、税込み金額、科目、科目２、科目３”の順に入力。　

②年月日の書式と関数設定

A）数字に自動で、年、月、日をつけるように設定する。(任意)

 a. 該当のセルを選択→右クリック→【セルの書式設定】を選択。

b.【ユーザー定義】→＠”年”（手入力）→OK　(月、日も同様に)

Ｂ）関数（=A2&"年"&B2&"月"&C2&"日"）年月日にする(必須) ★日付順に並び替えできる様にするため。

③日付順に並び替える設定

A) 過去から現在まで、順番になるように設定します。

a. A1セルを選択→【Ctrl＋A】で全選択→【並べ替えとフィルタ】→【ユーザー設定の並べ替え】

b. プルダウンで優先キーを【年月日】→OK→OK

これで、日付の設定は完了です。

④支払い方法マスタ、科目マスタの設定

A) ご家庭のキャッシュフローに沿った《支払い方法》《科目一覧》を作成してください。

ex.） 《支払マスタ》：現金、電子マネー、口座振込、クレジットカード(締日、支払日)の一覧リスト。

ex.) 科目マスタ：親カテゴリ（科目）：貯蓄・収入・支出など、子カテゴリ（科目2）細分化した項目、給与、賞与、食費、日用品など。

ex.) 科目マスタ②：孫カテゴリ(科目3)は、さらに細分化できるもの。

B) 一覧表を使用して、プルダウンで簡単に仕分けできるようにします。

a. Ｇ２セルを選択→【データ】→【データの入力規則】→【設定】→【リスト】→元の値を、支払マスタから引っ張ってくる。

Ｃ）科目マスタのプルダウン設定（名前の管理を使用）

★point★　科目マスタは、項目が多いのと、INDIRECT関数を使用する為、名前の管理を使用して簡素化します。

a.  A1（科目）～A8（電子マネー）まで選択→【数式】→【選択範囲から作成】→【上端行】→OK

b. 名前の管理で確認すると、科目という名前で、リスト登録されています。

c. Dataシートに戻り、Ｊ2を選択→【データ】→【データの入力規則】

d.【設定】→【リスト】→【元の値】に(=科目）と手入力→OK

e. そうすると、プルダウンで選択できるようになります。

f. 次に、科目2・科目3は、A2から、表全体を選択→【左端列】→OK

ｇ. DATAシートの科目2・科目3には =INDIRECT($Ｊ2) と入力します。※科目3の場合は、INDIRECT($K2)です。

h. そうすると、前列の科目に紐づいた項目がプルダウンで選択できます。

⑤ピボットテーブルを使った年間収支表の作成（動画付き）

A)　最後に、年間収支表を作成します。DATAシートを全選択→【挿入】→【ピボットテーブル】→OK

a. ピボットテーブルの上で、右クリック→【ピボットテーブル オプション】を選択

b.【表示】→【従来のピボットテーブル...】に✔→OK

c. 科目を、行のフィールドにドラッグ＆ペースト

d. 同様に、科目2、科目3をドラック＆ペースト

e. 年、月を列のフィールドへ、ドラッグ＆ペースト

f. 税込み金額を、値にドラッグ＆ペースト

g. あとは、見た目をきれいにして完成。そして、データベースに入力したら、毎回、ピボットテーブルの上で、右クリック→更新をすると、フレッシュなデータに更新されます。

我が家は、毎月2回くらいレシートを見ながらデータベースにざっくりと入力して、年間収支で毎月のキャッシュフローを把握しています。

ここまで、お付き合いいただき、ありがとうございました。