エクセル家計簿の作り方を解説！数式なしで集計表とグラフを完成させる一連の流れ
当ブログの⇒エクセル家計簿は、おかげさまで年間20万回のアクセスをいただきました（2019/12/26～2020/12/25）
ありがとうございますm(_ _“m)
家計簿をつくる過程で、「〇〇をすれば便利！〇〇なら簡単！」という自分の考えが固まったので、この記事で共有したいと思います。
初心者にも分かるように実践形式で書くので、エクセルを開いて試してみてください。
主なポイントは、下記の通りです。
- 数式はまったく使わない
- 集計表・グラフ（連動）
- 手順に沿えば10分で完成
この記事を最後まで進めると、下記ダウンロードリンクのテンプレートが完成します。
最低限の骨組みだけなので、デザインなどは別コンテンツで解説します。
⇒テンプレートをダウンロード【当記事の信頼性について】
⇒MOS Expert 保持者のUdemy講義（英語）の情報をベースに、エクセル家計簿に使えるテクニックを抜粋・応用しました。IT関係では英語圏発の情報が豊富なので、できる限り一次情報寄りのソースを採用しました。
自分自身も、6年間ほど仕事としてシステム開発やプログラミングを行っています。簡単で効率的なやり方に関しては、信頼いただいて問題ないと思っています。
Contents
エクセル家計簿の作り方を解説！数式なしで集計表とグラフを完成させる一連の流れ
下記の順番で解説します。
- 入力用のシートを作る
- 集計表を作る
- 集計グラフを作る
それぞれを詳しく説明すると長くなるので、「こんな流れで作れますよ～」といった感じで駆け足で進めたいと思います。
詳細については、別コンテンツで解説します。
入力用のシートを作る
まずは新しいエクセルファイルを作成し、それを開きます。
最初からあるシート名を分かりやすい名前に変更します。今回は「入力」とします。
※矢印クリックで再生
次に、日々の収入や支出を記録するための入力表を作ります。
下記の3項目とします。
- 日付：日付を入力
- 費目：「収入」または「支出」を入力
- 金額：数値を入力
実際に使う家計簿ではこれだけでは足りないと思いますが、今回は「テンプレートの作り方」を紹介するのが目的であるため、最小構成で進めます。
また、何かを作るときは、①最小限の構成で骨組みを決める → ②それに肉付けして機能を増やす とした方が、最終的にキレイにまとまることが多いです。
いろいろアイデアが浮かんだとしても、最初はシンプルにすることをおすすめします。
下画像のように、項目名を入力します。
最後に、テンプレート作成中に使うニセモノのデータを入力します。
多くてもOKですが、下記のような現実的なデータをある程度そろえれば十分です。
- 日付：同年の1～12月、年月日の"日"はバラつかせる
- 費目：月ごとに収入・支出を1件ずつ
- 金額：適当にバラつかせて入力
この記事では、下記のデータを前提に進めます。
入力が面倒であれば、サンプルのテンプレートからコピーしてください（その際は、2021年のデータのみコピーしてください）
集計表を作る
「入力用のシートを作る」が終わったので、「集計表を作る」に進みます。
今回は、費目別・月別の集計を行います。
「〇月の収入は〇万、支出は〇万」といった情報が、ぱっと見で分かるものを目指します。
集計には数式（SUM関数など）を使う方法もありますが、下記の理由でピボットテーブルをおすすめします。
- 数式のタイプミスなど、凡ミスがない
- マウスで気楽に設定・変更できる
- 慣れれば数式よりも簡単（最初は取っ付きにくいですが）
ピボットテーブルが初めての方でも分かるように、順を追って解説します。
まず、下記のように操作します。
- A～C列を選択する
- 挿入タブを開く
- ピボットテーブルをクリックする
- OKをクリックする
※矢印クリックで再生
新しいシートが作成されて、“よく分からない何か"がシート上に表示されます。これがピボットテーブルです。
シート名が「Sheet2」になっているので、とりあえず「集計」など分かりやすい名前に変更してください。
ピボットテーブルの中を選択すると、「ピボットテーブルのフィールド」が右側に表示されます（以降、フィールドリストと呼びます）
下記のように操作します。
- 「金額」を「値」にドラッグする
- 「費目」を「行」にドラッグする
- 「日付」を「列」にドラッグする
※矢印クリックで再生
⇒大きい画像を開く
フィールドリストを設定していくと、ピボットテーブルの形がどんどん変わっていきます。
これだけで、費目別・月別の集計は完了です。
デザイン的に不要な部分があるので、下記を消去したいと思います。
- 「<2021/1/12」という列（下緑枠）
- 「(空白)」という行（下青枠）
- 「総計」という行（下赤枠）
「<2021/1/12」、「(空白)」といったデータが混じってしまうのは、ピボットテーブルを作ったときに「A～C」の列で指定したことが原因です。行は指定していないため、下の方にあるデータ未入力の行も集計対象となります。
このあたりの解決法については、別コンテンツにまとめます。
また、「総計」行を不要としているのは、家計簿としてあまり意味のない集計であるためです。収入と支出の合計が分かっても、活用することがありません。
下記の手順で解決します。
- 行ラベルをクリック → 「(空白)」のチェックを外す
- デザインタブを開く → 総計 → 行のみ集計を行う
※矢印クリックで再生
⇒大きい画像を開く
また、月の左にあるプラスマークをクリックすると日別の集計が表示されますが、これも不要です。
下記の手順で、日別の集計を消去します。
- 月を右クリック
- グループ化をクリック
- 日の選択を外す
- OKをクリック
※矢印クリックで再生
⇒大きい画像を開く
集計グラフを作る
「集計表を作る」が終わったので、次は「集計グラフを作る」に進みます。
今回は、先ほどの集計表と連動するグラフを作ります。
下記の手順で設定してください。
- ピボットテーブルの中をクリックする
- 分析タブを開く
- ピボットグラフをクリック
- 折れ線をクリック
- OKをクリック
※矢印クリックで再生
⇒大きい画像を開く
ピボットテーブル専用のグラフである「ピボットグラフ」が作成されました（ピボットグラフのデータ元は、ピボットテーブルです）
しかし、何やら見た目がおかしいですね。
折れ線グラフとして表示したいのは、収入と支出が1～12月でどのように推移しているか…という時系列の情報です。
つまり、データの軸が逆になっているのです。
これを解決するには、データ元であるピボットテーブルの軸を逆にしなければいけません。
実際にやってみましょう。
下記の手順です。
- ピボットテーブルの中をクリックする
- 日付を列から行にドラッグする
- 費目を行から列にドラッグする
※矢印クリックで再生
⇒大きい画像を開く
収入と支出の推移が時系列で表示される、それらしいグラフになりました。
ただし、当然ながら集計表の構造が変わってしまいます。
集計表の構造をそのままにした上で、グラフだけデータ軸を変更するにはどうすればよいのでしょうか。
上級者がよく使うテクニックは、グラフ用にピボットテーブルを複製する方法です。
まず、Ctrl + Z を3回ほど押してください。
集計表のデータ軸がもとに戻り、グラフが作成される前の状態になったら、下記のように進めてください。
- ピボットテーブルの中をクリックする
- 範囲選択する（Ctrl + A）
- コピーする（Ctrl + C）
- 下の方に貼りつける（Ctrl + V）
- 新しいピボットテーブルのフィールドリストで、データ軸を逆にする（先ほどと同じ手順）
- ピボットグラフを挿入する（先ほどと同じ手順）
※矢印クリックで再生
⇒大きい画像を開く
最初の集計表はそのままにした上で、時系列のグラフを作ることができました。
新しい集計表はグラフのデータ元として使い、私たち自身は見る必要がないので、別のシートに隠してしまいましょう。
これもよく使われるテクニックです。
- ピボットテーブルの中を選択する
- 範囲選択する（Ctrl + A）
- 切り取る（Ctrl + X）
- 新しいシートに貼りつけ（Ctrl + V）
※矢印クリックで再生
⇒大きい画像を開く
集計シートに余分なスペースができるので、上動画ではついでにグラフのサイズを調整しました。Altキーを押しながらドラッグすると、セルに合わせてにサイズ変更できます。
また、新しいシートの名前は何でも大丈夫ですが、今回は「ワーク」にします。「一時的」、「作業用」みたいなニュアンスで、プログラミング界隈でたまに使います。
【発展編】エクセル家計簿をもっと使いやすくするには…
入力シートと集計シート（表・グラフ）ができたので、骨組みはこれで完了とします。
ただし、下記のような課題が残っています。
- 入力シートのデータを変更しても、集計シートの内容が変わらない
→ピボットテーブル・グラフは手動で更新する必要があります - 複数年のデータを入力すると、同じ年月に集計されてしまう
→2020年1月と2021年1月など、年が異なっても同じ年月に集計される - 集計表・グラフのデザインがいまいち
→配色や書式設定など、デフォルトのままで見にくい
ここからは発展編として、上記について ①どんな問題なのか現状把握 → ②その問題をどう解決するか を考えてみます。
引き続き実践形式で進めます。
集計シートの内容を更新する方法
入力シートのデータを追加・変更・削除しても、集計シートの内容は自動で更新されません。
集計表とグラフ、どちらもです。
具体的な例をあげると、下記のような状態になっています。
- 集計シートでは、1月の収入が200000になっている
- 入力シートで、300000に変更する
- 集計シートの内容が変わらない！！
※矢印クリックで再生
これはピボット系機能の仕様で、数少ないデメリットのひとつです。
とは言っても、何回かクリックするだけで更新できるので、そこまで大きな問題ではないと思います。
更新するには、下記の手順を試してください。
- ピボットテーブルの中を選択する
- 分析タブを開く
- 更新の下にある「▼」をクリックする
- すべて更新をクリックする
※矢印クリックで再生
⇒大きい画像を開く
更新する前に選択するのは、ピボットグラフでも「ワーク」シートに隠したもうひとつのピボットテーブルでも問題ありません。
ピボット系の"何か"を選択して、「すべて更新」をクリックすればOKです。
集計方法を 月別 → 年月別 に変更
現時点では2021年のデータしかないので問題ないですが、2022年のデータを入力すると集計結果がおかしくなります。
実際に見てみましょう。
入力シートに1年分のデータをまるごとコピー、2021年を2022年に変更した上で、集計シートを更新します。
- 1年分のデータを選択してコピー（Ctrl + C）
- 最終行の下に貼りつける（Ctrl + V）
- 2021年 → 2022年 に変更
- 集計シートを更新
※矢印クリックで再生
⇒大きい画像を開く
2021年 → 2022年 に変更する際、全置換というテクニックを使っています。置換する範囲を選択した後、Ctrl + F で検索・置換ダイアログを表示できます。
難しければ、手入力で変更していただいても大丈夫です。
ピボットテーブルの数字が増えているので、新しいデータが集計対象になっているのは間違いなさそうです。
ただし、家計簿としては間違っています。
2021年1月と2022年1月のデータが「1月」の"月単位”（複数年の合計）で集計されても、何の役にも立ちません。「2021年1月」、「2022年1月」といった"年月単位"にする必要があります。
下記の手順で設定します。
- 月を右クリック
- グループ化をクリック
- 年の選択を追加する
- OKをクリック
※矢印クリックで再生
⇒大きい画像を開く
先ほどは2021年の集計が倍になっていましたが、2022年が右側に分かれることで集計結果が正しくなりました。
B4セル、O4セルにも、「2021年」のように年が表示されます。
しかし、ピボットテーブルが右側に広がってしまうことで、使いにくく感じるのではないでしょうか。
個人的な感覚でいうと、縦方向のスクロールであれば苦になりませんが（マウスホイールがあるため）、横方向のスクロールは面倒くさくてストレスを感じます。
また、グラフの右側にも余白ができて、フィット感が損なわれてしまいます。
この問題を解決するために、スライサーという機能を使って、選択した年の集計を1年分だけ表示されるようにします。
横スクロールする必要がなくなり、フィット感も復活します。
下記の手順で設定してください。
- ピボットテーブルの中を選択する
- 分析タブを開く
- スライサーの挿入をクリックする
- 年を選択する
- OKをクリックする
※矢印クリックで再生
⇒大きい画像を開く
スライサーが挿入されて、その中に入力したデータの「年」が選択肢として表示されます。
「2021年」を選択するとピボットテーブルが2021年のみ、「2022年」を選択すると2022年のみ表示されます（金額のデータが同じなので分かりにくいですが、B4セルの年表記が変わっています）
続いて、レイアウトを整えます。
N4～N7セル（2021年の合計）は、O4～O7セル（ピボットテーブル全体の合計）とまったく同じなので必要ありません。スライサーの絞り込みによって、どちらも集計対象のデータが2021年のみになったためです。
スライサーの位置も収まりがわるいので、移動します。
- ピボットテーブルの中を選択する
- デザインタブを開く
- 小計をクリックする
- 小計を表示しないをクリックする
- スライサーを移動する
※矢印クリックで再生
⇒大きい画像を開く
スライサーを移動するときは、Altキーを押しながらドラッグするとセルに合わせてキレイに収まります。
「これで完成！」と言いたいところですが、下画像を見ると分かるように、グラフに2021年と2022年のデータがどちらも表示されています。
スライサーで2021年だけ選択したにも関わらず…です。
こうなる原因を、図にまとめました。
登場人物は下記のとおりです。
- 左上：集計シートのピボットテーブル
- 左下：集計シートのスライサー
- 右上：ワークシートに隠したピボットテーブル
- 右下：集計シートのピボットグラフ
※矢印クリックで再生
先ほど、私たちは左上のピボットテーブルを選択した上でスライサーを挿入しました。
これにより、スライサーとピボットテーブルの紐づけ（青矢印）が自動的に設定され、スライサーを操作するとすぐにピボットテーブルに反映されます。
その一方で、図の右側にあるピボットテーブル・グラフとは何も関連性がありません。
紐づけの設定（赤矢印）を手動で行う必要があります。
気をつけないといけないのは、スライサーに紐づけるのはピボットグラフではなく、ピボットテーブルであることです（エクセルの仕様です）
というわけで、紐づけの設定をやってみましょう。
下記の手順です。
- スライサーを選択する
- オプションタブを開く
- レポートの接続をクリックする
- ピボットテーブル2を選択する
- OKをクリックする
※矢印クリックで再生
⇒大きい画像を開く
この設定をした瞬間に、ピボットグラフの表示期間が2021年のみに変わりました。
上手くいったようです。
入力シートで2022年のデータ（金額）をいくつか変更し、本当に切り替わるのか試してみます。現時点では金額がまったく同じなので、実際に変わっているのか分からないからです。
※矢印クリックで再生
⇒大きい画像を開く
2021年と2022年で表示内容が変わっているので、とくに問題なさそうですね。
すこし長くなりましたが、なんとか形になりました。
ピボットテーブル・グラフのデザイン
ここまでレイアウトやデザインは手を付けてないので、最後にこの点を改善したいと思います。
まず、ピボットテーブルの行が 1行目：支出、2行目：収入 となっていることに違和感を覚えます（個人的な感覚ですが）
順番の変え方は簡単で、下動画のようにドラッグするだけです。
※矢印クリックで再生
次に、スライサーに「<2021/1/12」「>2022/12/17」という必要ない選択肢があるため、これを消します。
見た目が薄くなっていることからデータが存在しない、つまり選択肢を消して問題ないことが分かります。
下記の手順で消します。
- スライサーを選択する
- オプションタブを開く
- スライサーの表示設定をクリックする
- 「データのないアイテムを非表示にする」をクリックする
- OKをクリックする
※矢印クリックで再生
⇒大きい画像を開く
次に、数字の書式を「200000」などから「200,000」のように三桁区切りで表示します。
ピボットテーブルとグラフについて、別々に手順をまとめます。
【ピボットテーブル】
- 数字の上で右クリックする
- 表示形式をクリックする
- 数値をクリックする
- 「桁区切りを使用する」を選択する
【ピボットグラフ】
- 目盛りの数字を右クリックする
- 軸の書式設定をクリックする
- 表示形式を開く
- 数値を選択する
※矢印クリックで再生
⇒大きい画像を開く
次に、配色を変更します。
人によって好みが分かれますが、今回はグレーを基調とした淡い色を使います。
【ピボットテーブル】
- ピボットテーブルを選択する
- デザインタブを開く
- ピボットテーブルスタイルを変更する
【スライサー】
- スライサーを選択する
- オプションタブを開く
- スライサースタイルを変更する
【ピボットグラフ】
- 折れ線グラフを右クリックする
- 枠線をクリックする
- 色を変更する
※矢印クリックで再生
⇒大きい画像を開く
最後に、ピボットテーブルとグラフの上に表示されている、不要なラベルやフィルターを消します。
見た目がすこしサッパリするはずです。
下記の手順で消します。
【ピボットテーブル】
- ピボットテーブルを選択する
- 分析タブを開く
- 「フィールドの見出し」をクリックする
- 「+/- ボタン」をクリックする
- A3セルの「合計/金額」を半角スペースに変更する
→エクセルの仕様で未入力はエラーになるため、半角スペースにする
【ピボットグラフ】
- ピボットグラフを選択する
- 分析タブを開く
- フィールドボタンをクリックする
※矢印クリックで再生
⇒大きい画像を開く
というわけで、この記事はこれで以上となります。
いかがだったでしょうか。
ピボットテーブルが初めての方にとっては難しかったかもしれませんが、慣れると数式よりも簡単でパワフルな集計ができると思います。
気が向いたらコンテンツを追加するので、気が向いたときに覗いていただけるとうれしいです(^▽^)/
