Excel でみんながやってる家計簿作りに挑戦!手軽に集計、家計を点検

家計簿を作る Excel
家計簿を作る

きょうは Excel で家計簿作りを学びます。

極めようと思えば、どこまでも複雑になるのが家計簿の世界ですが、なるべく簡単に、実用上困らない程度に絞ります。一家の支出を見る場合、最もシンプルににすると「収入」「支出」「残高」だけ記述すれば済みます。

しかし、せっかく作るのですから、家計を点検できるようにしたいですよね?

“Excel は人を作る” といいます。言わないか?

きょうはあなたと、家計簿のつけかたを学習します。

1.家計簿をエクセルで毎日つくる方法

家計簿
家計簿

家計簿は、収入や支出を記録してお金の管理を行うのに役立ちます。

ステップ1: 新しいエクセルファイルを作成する エクセルを開いて新しいブランクのワークブックを作成します。

ステップ2: シートの作成 シートの下に「家計簿」という名前で新しいシートを作成します。これにより、家計簿用の新しいシートが作成されます。

ステップ3: 列の作成 家計簿の記録を行うために、次のような列を作成します。

  1. 日付
  2. カテゴリ(食費、交通費、光熱費など)
  3. 内容(支出の詳細や収入源など)
  4. 収入
  5. 支出

ステップ4: データの入力 家計簿に収入や支出のデータを入力していきます。日付、カテゴリ、内容、収入、支出の各列に対応する情報を入力します。収入の場合は、収入列に金額を入力し、支出の場合は支出列に金額を入力します。

ステップ5: 合計の計算 収入と支出の合計を計算するために、合計を表示するセルに合計関数を使用します。例えば、収入の合計を計算したい場合は、収入の列の下に合計のセルを作成し、「=SUM(セル範囲)」と入力します。セル範囲には収入の列のデータが含まれる範囲を指定します。同様に、支出の合計も計算します。

ステップ6: データのグラフ化(オプション) 必要に応じて、家計簿のデータをグラフにして可視化することもできます。収入と支出の比較など、データの傾向を把握するのに役立ちます。

ステップ7: 継続的な更新 家計簿は継続的に更新して、収入や支出の変動に対応しましょう。定期的に情報を入力し、合計を更新して、家計管理を行います。

以上が、エクセルで家計簿を作成する基本的な手順です。項目やデザインは自分のニーズに合わせてカスタマイズすることができます。

2.Excel家計簿で最も重要かつ煩雑な「変動支出」シートを作る

家計簿で最も重要かつ煩雑な「変動支出」シート
家計簿で最も重要かつ煩雑な「変動支出」シート

昔から、お金を貯めるには「入るを計りて出づるを制す」といいます。家計簿をつけたからといって収入は増えませんが、支出を削るヒントは見つかるかも知れません。

支出を管理するには、毎月必ず払うことが決まっている「固定支出」と、やりくり次第で額が変わる「変動支出」とに分けて考えます。

家計簿としては「変動支出」「固定支出」、収支を見る「総計」の3シートを作ることになります。

変動支出シート
変動支出シート

1.家計簿の「変動支出」シートを作成する

  1. カテゴリの設定: 変動支出を分類するために、カテゴリを設定します。例えば、食費、交通費、娯楽費、衣服、医療費などが考えられます。可能な限り具体的で細かいカテゴリを作成しましょう。
  2. 収入と支出の月次予算: 変動支出をコントロールするために、収入と支出の月次予算を設定します。収入と支出のバランスが取れるようにし、無駄遣いを防ぎます。
  3. 支出項目の追加: 各カテゴリごとに、変動支出がどのように発生するかを考慮して、具体的な支出項目を追加します。例えば、食費カテゴリでは、食料品、外食、カフェなどを考慮します。
  4. 支出の記録方法: 支出を追跡する方法を決定します。手書きで記録するか、スプレッドシートを使用するか、家計簿アプリを活用するかなど、効率的な方法を選びましょう。
  5. 支出の記録: 毎日または毎週、支出を記録していきます。領収書や銀行取引明細を確認し、正確な金額を入力します。適切なタイミングで記録しないと、後で記憶が曖昧になり、漏れが生じる可能性があります。
  6. 集計と分析: 定期的に支出の合計を集計し、カテゴリごとにどれだけ使ったかを確認します。支出のパターンを分析して、節約できるポイントを見つけましょう。
  7. 貯蓄と投資への活用: 支出の記録と分析を通じて、貯蓄や投資に回す余裕が生まれた場合は、それを計画的に行いましょう。
  8. 予算の再評価: 実際の支出が予算を超えた場合は、予算を再評価し、必要な調整を行いましょう。
  9. 家族の協力: 家計簿を運用するのは個人だけでなく、家族全体で協力することが大切です。家族の誰かが支出を把握していないと、正確な情報が得られないため、家族全員で情報共有を行いましょう。
  10. 継続的な管理: 家計簿は一時的な取り組みではなく、継続的な管理が必要です。毎月、定期的に記録・集計を行い、家計を健全な状態に保ちましょう。

これらのステップを踏むことで、重要な情報が得られ、家計簿の変動支出シートがより効果的に役立つものになるでしょう。

まず A1 に「8月1日」と入力し、枠のマスの右下にカーソルを合わせて、十字マークが出たら、マウスの右クリックしたまま、A31 マスまで下に引っ張ります。

同じように B・C・D・E に、それぞれ 9・10・11・12月の日付を入力します。

日付と一緒に曜日を表示する場合、改めて入力する必要はなく、書式設定を変更すればできます。日付を入力したセルを選択、「書式」→「セル」→「表示形式」タブから「分類」内の「ユーザー定義」を選びます。

セルに「2021/12/24」とあるとき、ユーザー定義で「aaa」とすれば「金」と表示され、「aaaa」なら「金曜日」になります。(aaa)とカッコをつければ(金)と表示されます。

日付から曜日を表示する
日付から曜日を表示する

1 マスを右クリック「挿入」で、最上部に1行入力できる場所を作ります。F 列から順番に、「食費」「衣服費」「雑貨費」などを入れていきます。

N 2 のマスを左クリックした後、上部ツールバーにある「Σ」マークをクリックします。

F2 のマスを左クリックしたまま「M2」のマスまでカーソルを移動、「=SUM(F2:M2)」の表示を確認します。

Enter キーを押します。「0」が表示されれば成功です。ここで日付入力の時使った方法で、「N2」マスを左クリックして右下に十文字がでたところで枠を下に31 個分引っ張ります。

0が並んだら OK です。

コピーした列を挿入
コピーした列を挿入

今度は「F」列から「N」列までを反転させ右クリック、「コピー」し、「B」マス上で右クリック、「コピーした列を挿入」します。くれぐれも「貼り付け」ではないところにご注意ください。

はい、8月分の変動支出シートの完成です。毎日使った金額を入力していきます。

つぎに固定支出シート総計シートを作れば、家計簿になります。ひと目でどのシートかわかるように名前をつけます。

変動支出/固定支出/総計
変動支出/固定支出/総計

固定費支出のタブをクリックして、白紙のシートに必要項目を入力していきます。1カ月単位で作ります。

固定費シート
固定費シート

「特別費」とは、大きな買い物をするときに備えた積立です。日付入力の方法は、「年月」でも使えます。「固定費支出計」の入力方法は、「Σ」を使います。

3.家計簿をExcelの自動計算で楽々

家計簿を自動計算で楽々
家計簿を自動計算で楽々

1.家計簿を自動計算するいくつかの方法

  1. 家計簿アプリ: スマホやコンピュータ向けに開発された家計簿アプリは、収入と支出を自動的にトラッキングし、合計やカテゴリごとの金額を計算してくれる場合があります。一部のアプリは、銀行口座やクレジットカードと連携して自動的に取引情報を取得できる機能もあります。
  2. 電子取引明細の利用: オンラインバンキングを利用している場合、銀行やクレジットカード会社が提供する電子取引明細をダウンロードして家計簿に取り込むことができます。一部のアプリやスプレッドシートでは、これらの明細を自動的に解析して支出を計算する機能があります。
  3. 自動家計簿システム: 一部のウェブサイトやプログラムでは、自動家計簿システムを利用できます。これらのシステムは、指定した条件に基づいて支出を自動的に計算し、グラフやレポートを生成することができます。
  4. API連携: 銀行やクレジットカード会社が提供するAPI(Application Programming Interface)を使用して、取引情報を自動的に取得し、家計簿システムに統合することも可能です。ただし、API連携を許可している金融機関が限られます。

自動計算を活用することで、手動での入力や計算作業を省略し、より効率的に家計簿を管理できます。ただし、セキュリティやプライバシーの観点から、家計の情報を取り扱うアプリやシステムを選ぶ際には注意が必要です。信頼性の高いソフトウェアを選び、個人情報を適切に保護することが重要です。

「収入」「支出」など複数のマスを使うばあいの設定方法は、「セルを結合して中央揃え」にします。

「収入計」の「「F3」マスは再び「Σ」です。

総計シート
総計シート

さあ、ここからがヤマ場です。「固定支出」の「G3」マスに、固定支出シートの合計額が自動的に呼び込まれるようにします。

総計シートの「G3」マスを左クリック、「=」を入力した後で、左下の「変動支出タブ」を左クリック。さらに「L2」マスを左クリック。表示が「=固定支出!L2」となっていることを確認して Enter 。

総計と固定支出の連携
総計と固定支出の連携

「固定支出!~」とは「固定支出シートの L2 というマスの数値を参照しなさい」という意味です。

あと、もうひと息です。「変動支出」の「N3」のマスを左クリック、「=」を入力した後で、左下の「変動支出タブ」をを左クリック。さらに「I」マスを左クリック。表示が「=変動支出!I:I」となっていることを確認して Enter 。

「残高」は「収入」マイナス「支出」なので、このばあいは固定と変動の両方ですから、「J3」マスには「=G3-H3-I3」と入力、Enter をクリックします。この「残高」が、翌月の「繰り越し」になるので、「B4」マスは「J3」と入力します。

「繰り越し」「残高」「収入計」は、入力した数式を他の月にも使えるので、日付入力と同じ方法でコピーします。「固定支出」「変動支出」は、参照するマスや列を毎月指定します。

なにはともあれ節約のコツは、変動支出を項目ごとに予算立てして管理することです。なので予算対策シートも作ります。シートを増やすには、左下のタブを右クリック「挿入」です。

残高を表示するマスをクリック、「=」→「予算を入力するマス」を左クリック→当該項目別のアルファベットのマス、例えば食費なら「B」を左クリックして Enter 。項目の左マスに予算を入力すると、右マスに予算の残高が表示されます。

変動支出シートに、どこで何を買ったかの備考を書き込みたいときは、項目の間に列を挿入します。この欄に文字を入力しても計算結果に関係ありません。

2.条件付きで合計・平均・件数を算出

食費、交際費などの費目別に、支出を合計したいとき。

「自動集計機能」が便利です。ピボットテーブルを使うのです。集計したいメニューを選んでいくだけで、あっという間に「費目別の集計」などを求めることができます。計算式は必要なし、マウス操作だけです。

このように、日付、費目、金額などの項目名が一行目に並び、以下1件づつとういう形で入力された表なら、「ピボットテーブル」という自動集計機能が利用できます。

「挿入」から「ピボットテーブル」を選び、表または範囲の選択を確認しOKボタンを押します。

すると、「ピボットテーブルのフィールド」が用意されます。右側の「元表」の1行目にあった項目名が取り込まれているので、ここから集計したい項目を選らび、枠の中に配置していきます。

項目名に☑を入れると自動的に左側に配置されます。左側に「交際費」などの項目が並び、その右側に金額が自動表示されました。項目の順番を変えるには、項目名が入ったセルの部分をドラッグすればいい。

すると集計結果も一緒に移動します。「¥」表示や罫線といった書式の設定も、通常のセルと同様にできます。

3.家計をつけるって、新しい生活を歩むことですよね?

家計簿のデータって、日々増え続けるものじゃないですか。追加したデータも自動的に集計に含めるようにするには「関数」が便利です。

「関数?」「関数は難しくてなかなか使えない・・・」という人でも尻込みする必要はありません。エクセルをうまく活用している人は、「関数」と「メニュー」を適材適所で使い分けています。必要に応じて両者を組み合わせ、”やりたいこと”をしっかりやっています。

=SUMIF(B3:B13,F4,D3:D13)

B3:B13 範囲「費目」列の13行目まで。
F4 検索条件「食費」
D3:D13 合計範囲「金額」列の13行目まで

SUMIF 関数を使えば、「費目」列が「食費」のデータだけを合計するといった項目別集計ができます。引数の「範囲」、「合計範囲」に、データを入力する欄の最下行(ここでは13行目)までを含めておけば、その灰内に追加したデータを随時、自動集計できます。

G4セルのSUMIF 関数をコピーすれば、他の項目も同時に合計できます。その際、引数の「範囲」「合計範囲」は共通なので、それぞれ範囲選択して「F4」キーを押して、「$」をつけて参照を固定し、コピーします。

そういえば、誰かがいった言葉を思い出します。

「あなたはいま、自由に生きるといいました。いろいろの生き方があると思います。もし、そのなかに再婚の可能性がふくまれていたとしたら、その中の一人に私も入れていただきたいと思うのです。私には、年齢的にも健康上からいってもそんな資格はないのですが」

「私は、いつも、そのことで悩んできました。なんどお話しょうかと思いました。でも、今日は言ってしまいました。それでもし、あなたの気持ちが決まったならば、来春、いずれの結論でも結構です。何らかの意思表示をしていただければと思います」

「わかりました。〇〇さんのおっしゃった通りいたします。わたくしの気持ちや立場を考えていただいたこと、嬉しく思いました。この花可愛いでしょ。帽子に差してくださらない?」

4.Excel 家計簿の経費の中には

経費の中
経費の中

家賃、食費、公共料金、トイレットペーパーなどの日用品といった月々出ていくのが決まっているものは生活費です。それ以外が小遣いです。同じ「食費」でもつきあいの飲食は小遣いからというふうに決めておきます。

クレジットでの買い物は、必ず買った月の支払いとして記録します。残ったら貯蓄では計画的に貯められません。収入の10%ぐらいを天引きします。

仕事に必要な書籍や雑誌、事務所を借りていればその借家料、暖房代や電気代、取材に出かけたら旅費、交通費、宿泊代、通信のための切手代、電話代などすべて含まれます。仕事が特殊なものであれば、気が付かないようなものがまだあると思います。

普通、税務署主催の税務相談会は青色申告普及を目的にしているので、例えば来年から青色申告をすることにした納税者には、推定の経費で白色申告を認めることになっています。金銭出納帳のようなものに、毎日入った金と出た金を記録し、出た金の方はできるだけ領収書を貰っておきます。旅費交通費は領収書がでないこともありますが。

1.税理士は立場から言って厳正中立でなければなりませんが

税務署の立場で納税者に対応するから、納税者は税理士を税務署の一味と思ってしまいます。顧問料は納税者からもらって、その納税者に税務署の立場に立って、口を利くんですから。

税理士の多くは、税務署が税金を集めるからこそ、自分たちの商売がなりたっていると思っているのです。長いものには巻かれろってことですね。つまり顧客の方から見れば、税理士とは計数整理屋、そして税務署との税務交渉屋でしかないのです。

もっとも税理士も見かけ程いい商売ではありません。納税者からも徴税者からも独立した第三者的立場のはずなのに、税務署の意向にいつも従わせられますからね。税務署長や、関係部署の部長や課長の移動のたびに、歓送迎会に出席させられる。年末調整や確定申告のときは、税務署員の仕事を肩代わりさせられますし。

経費の記入

10円でも1000円でも、合わなかったら同じことです。おそらく経費の入力ミスだと思います。そうなると最初から経費明細帳の点検しなおしです。

2.使った家計簿の作り方まとめ

  1. 新しいExcelブックを開き、1つ目のシートに「収支表」という名前を付けます。
  2. A列には、日付、B列には項目、C列には金額、D列には残高を入力します。
  3. 収入と支出の両方を記録するため、B列の先頭に「収入」という項目を追加し、次の行には「支出」という項目を追加します。
  4. 収支表の最初の残高を入力するために、D2セルに初期残高を入力します。
  5. 収支表の各行で、支出がある場合は金額セルに負の数を入力し、収入がある場合は正の数を入力します。金額を入力した後、D列の残高を計算するために、「=D2+C3」(C3は金額のセル)のように式を入力します。
  6. 必要に応じて、グラフを作成して、収支を可視化することもできます。グラフを作成するには、グラフを作成したい範囲を選択し、「挿入」タブの「グラフ」をクリックしてください。
  7. 月次や年次の収支表を作成する場合は、別のシートを追加して、各月または年の収支表を作成してください。それらのシートを収支表と一緒にグラフに追加することができます。

以上が、Excelを使った家計簿の作り方です。必要に応じて、自分に合ったカスタマイズを行い、毎日の収支を追跡して、よりよい家計管理を行ってください。

あとがき

家計簿をつけた経験のある人は多いものの、つけ続けている人となると少ないものです。

家計簿ではとくに、食費など日常的に使う「やりくり費」の管理が大変です。毎日、レシートとにらめっこするのは確かに手間ががかります。そこで友人が実践している家計簿をつけずにやりくりする管理する方法を紹介します。

家賃などの固定費や貯蓄とは別に、やりくり費として月9万円使える場合、9万円÷30日=3000円が1日の平均予算です。1週間では3000円×7日=2万1000円。この予算を頭に入れておきます。

もしも月曜日に2000円使えば1000円、火曜日に1000円使えば2000円、それぞれ予算が余ります。金曜日が終わって予算が1万円余れば、土、日曜日の予算は2日で1万6千円となります。平日はがんばって節約し、週末は家族で楽しむ、といったメリハリのきいた家計管理をされています。

エクセルについて悩みや疑問がありましたらご自由にコメント欄に投稿してください。(コメント欄はこの記事の最下部です)。いただいたコメントはすべて拝見し、真剣に回答させていただきます。

コメント

何なりとお申し付けください。