【改訂版】クレジットカード対応のエクセル家計簿を作る

クレジットカード対応のエクセル家計簿のアイキャッチ 家計簿

家計簿管理で一癖あるのがクレジットカード支払いの記帳方法。

これには二大流派、

・引き落とし日に精算派
・使ったその日に精算派

があり、エクセルで家計簿を付けているような細かい人間には大きな問題だったりするわけです。

私は使ったその日に精算派なので、それに対応したエクセル家計簿を作りました。

一ヶ月単位での家計簿記帳をしている人は参考にして見てください。

今回作成するエクセルシートの完成形

クレジットカード対応のエクセル家計簿のアイキャッチ
以前作った家計簿を元にして、クレジットカード対応にした形です。

カードごとの精算と現金枠の精算を行います。

支払い方法を「現金、amazon,楽天」から選び、仕分けは「食事、衣服、娯楽、投資、日用品」の5種類から選びます。

所持しているカード、仕分けしたい項目は各自で調整してください。

実際の作業手順

大枠作成

まずは大きな枠組みの作成をします。
クレジットカード対応のエクセル家計簿の大枠
支払いの分割は自分の経験上、3個にしました。

入力規則の設定

用途列に「食事、衣服、娯楽、投資、日用品」、

クレジットカード対応のエクセル家計簿の用途欄リスト

支払列に「現金、amazon,楽天」の入力規則を設定します。

クレジットカード対応のエクセル家計簿の支払欄リスト

用途列のセルを選択しておき、「データ」タブの「入力規則」をクリックします。
クレジットカード対応のエクセル家計簿の入力規則
「条件設定」の許可から「リスト」を選択し、
データの入力規則からリストを選択

「元の値」欄に「食事,衣服,娯楽,投資,日用品」と入力します。

データの入力規則欄

※各項目は「,」(=コンマ)で区切ります。

同様に、支払列のセルを選択しておき、「データ」タブの「入力規則」をクリックします。

「条件設定」の許可から「リスト」を選択し、「元の値」欄に「現金,amazon,楽天」と入力します。
データの入力規則欄

※ 各項目は「,」(=コンマ)で区切ります。
※ 1つのセルに設定した場合は、セル右下をクリックしながらコピーしましょう。

大枠の計算式の設定

支払欄の「現金、amazonカード、楽天カード」の選択肢で計算する欄を変える式を設定していきます。
クレジットカード対応のエクセル家計簿の合計欄
実際の計算式は以下の通りです。

=IF($I3="現金",$J3,0)+IF($L3="現金",$M3,0)+IF($O3="現金",$P3,0)

ざっくり日本語に直すと「入力欄」に「現金」が入力されたら足算に追加してください。

という感じです。

※ amazonを選択した場合は「”現金”」のところを「”amazon”」とします。

小枠の計算式の設定

生活費などをカード引き落としにしている場合は固定費として算出します。
カード欄の設定
固定費はsum関数を使って以下の式を書きます。

=SUM(C2:C4)

お小遣いの範囲内でクレジットカードを使用した場合は変動費として算出します。

変動費は大枠のsum関数式をそのまま代入します。

=S34

変動費と固定費の合計を算出し、その月の利用料金を見えやすくします。

=SUM(C6:C8)

私は毎月の予算を決めているので使用可能な残額が分かるようにしています。

あとはSuicaだったり、現金欄を設けていますが、ここら辺はお好みで。
Suicaと現金欄の設定
特に難しいことはないですが、わかんない方はコメント欄で教えてください。

色ぬり

支払い区分でセルの色が塗り分けられるようにします。
支払い区分で色分け
「ホーム」タブの「条件付き書式」を選択します。
強調セルの設定
「セルの強調表示ルール」から「その他のルール」を選択します。
強調ルールの条件設定
新しい書式ルールの中から「特定の文字列」を選び、現金を指定します。
特定の文字列

※ 「amazon」や「楽天」ごとに設定しましょう。

セルの色は「書式」欄の「ユーザー指定の書式」から設定できます。

完成

はいさ!終わりです。

クレジットカード対応の方法はいろいろあると思うので是非お試しあれ!!
クレジットカード対応のエクセル家計簿のアイキャッチ

コメント

タイトルとURLをコピーしました