前回の記事では、エクセル家計簿を自作するというテーマで毎月の家計簿をシンプルに作成する方法を紹介しました。

今回はそのエクセルファイルをそのまま使用して、年間の集計を可能にしたバージョンをご紹介したいと思います。

前回の記事についてはこちらから確認してください。

エクセルでシンプルな家計簿を簡単に自作する方法~PART①

以下の記事で作成方法を紹介しますが、いちから作成するのが面倒だという方は下のリンクをクリックすると作成済のエクセルファイルが開きますので、名前を付けて保存などでご自身のパソコンにダウンロードしてご活用ください。

シンプル家計簿(年間集計用)ダウンロード

では始めます。

まず、前回の家計簿ファイルを開いていただきシート名を「シンプル家計簿」から「1月」(1は半角英数)に変更してください。

次に変更したシートを「シートのコピー」で、2月~12月分まで作成してください。

そして、新しいシートを追加して、名前を「年間集計」としておきます。(名前はご自身のお好きな名前にしていただいて結構です。)

シートの構成としてはこんな感じになります。

シートの作成が終わったら年間集計シートの土台づくりに入りましょう。まず下の画像を参考に「タイトル」「項目名」「集計月(1月~12月)」「合計欄」を入力します。

項目名は前回作成したファイルに対応させて「収入」「固定支出」「やりくり費」について正確に入力しておきましょう。ちなみに項目ごとに色分けしていますが、これも好みの問題なので、必ず色分けしなければならないわけではありません。

また、画像では数字が入力されていますが、この段階では何も入力しなくて大丈夫です。

表の土台が完成したら関数を設定していきます。

C4セルに「=SUMIF(‘1月’!$A$5:$A$12,$B4,’1月’!$B$6:$B$12)」と入力します。

※ ちなみにセルに「=su」まで入力すると予測変換でエクセルが関数の一覧を表示してくれます。

使用しているのは「=SUMIF(サムイフ)」という関数です。複雑に見えるかもしれませんがやっていることは単純なので解説します。

言葉で説明すると、「ある範囲の中で検索条件に合致したとセルと同じ行にあるセルの値をすべて合計する。ただし、合計する値は合計範囲の中にある値だけとする。」といった感じです。

ややこしいですね。(笑)

関数の構成としては「範囲,検索条件,合計範囲」の3つを設定しています。

  • 範囲 = ’1月’!$A$5:$A$11
  • 検索条件 = $B4
  • 合計範囲 = ’1月’!$B$5:$B$11

検索条件はB4セルの「前月からのくりこし」を表しています。

範囲は1月シートのA5セルからA11セルの範囲を意味しています。ここですね。

合計範囲は1月シートのA5セルからA11セルの範囲を意味しています。

ここです↓

関数の意味をまとめると「1月シートの収入内容の列から「前月からのくりこし」を検索して該当するものがあればその該当セルと同じ行にある数字を合計する。ただし、合計するのは1月シートの収入金額を入力した範囲に限る」となります。

検索条件と検索範囲にある文字列が完全に一致していない場合は正しく集計で行きませんので、年間集計シートの項目名と毎月の家計簿の項目名を一致させておく必要がある点に注意してください。

集計シートのA4セルに関数を設定したら、固定支出(C12セル)とやりくり費(C29セル)にも以下のように関数を設定してみましょう。

  • C12:「=SUMIF(‘1月’!$D$5:$D$20,$B12,’1月’!$E$5:$E$20)」
  • C29:「=SUMIF(‘1月’!$G$5:$G$20,年間集計!$B29,’1月’!$H$5:$H$20)」

設定が終わったら「オートフィル機能」を使って計算式をコピーしましょう。

オートフィル機能とは選択したセルの情報を自動で他のセルにコピーしてくれるもので、すべてのセルに関数を手入力する手間を省いてくれる便利機能です。

先ほど設定したセルの右下にマウスのポイントを持っていくと「+」印が表示されますので、その表示が出た状態のままクリックしてマウスを下のセルまで動かしてみると計算式がコピーされたのがわかると思います。

確認したら、先ほど関数を設定したセルから合計欄までそれぞれセルをコピーしておきましょう。

実は先ほど説明しませんでしたが、関数の範囲指定する際に「’1月’!$A$5:$A$11」のように「」記号が出てきたと思います。この記号は「絶対参照」を表しているもので、セルをコピーしたときにもとの範囲を変更しないという意味です。

この記号を書かないでオートフィル機能を使うと、一行隣のセルにコピーしたときには検索範囲や合計範囲も一行ずれてしまいますので、正しく集計できなくなります。

よくわからない方も多いかもしれませんが、この記事はエクセル講座ではありませんので、ここではへぇ~そんなもんなんだ、くらいに思っていただければいいと思います。

では本筋に戻って

オートフィル機能で計算式をコピーした場合、すべてのせるの内容は「1月シート」のから検索している状態ですのでこれを各月のシートから検索する状態に変更しましょう。

方法としてはひとつづつ計算式を直してもできますが、ここでは「置換」を利用した方法を紹介します。

年間集計シートの変更したい列を選択した状態で「Ctrl」を押しながら「H」を押すと以下のようなメニューが開きますので、検索する文字列に「1月」、置換後の文字列に「〇月(変更する月)」と入力して「すべて置換」をクリックすると入力した月から検索することができます。

2月~12月までそれぞれの列で同じ作業をしておきましょう。

最後に以下を参考にして、各項目の合計欄に合計値を集計していきます。

各月の項目合計(1月の収入項目):「=SUM(C4:C10)」

1月の収支:「=C11-C28-C45」

項目の年間合計(1月):「=SUM(C4:N4)」

以上で年間集計用の家計簿が完成しました。お疲れさまでした。

エクセルに慣れていない方にとっては大変な作業だったかもしれませんね。でも一度作っておけばあとは毎月の家計簿を付けるだけで自動集計されますので、作成に多少時間をかけても十分もとは取れると思いますよ。

作成が面倒な方はページ上部のリンクからダウンロードもできますのでご活用くださいね。