エクセルで家計簿をつけていて、こんな悩みはありませんか？
・支出入力はしているけど、見返すのが大変
そんなときに便利なのが、
入力するだけで自動更新されるグラフです。
この記事では「支出表に入力すると、そのままグラフに反映される方法」を解説します。
一度作ってしまえば、あとは入力するだけ。
家計簿の管理がぐっとラクになりますよ！
日別の支出をグラフで見える化する（完成イメージ）
毎日の支出を入力するだけで、グラフが自動更新される家計簿を作ってみましょう。
下のように、表に入力すると金額が自動集計され、グラフにも自動で反映されます。
このグラフは、支出管理表（家計簿）をもとに作成しています。
家計簿の基本から作りたい方は、まずこちらの記事をご覧ください。
日別の支出を自動でグラフ化する方法（全体の流れ）
今回の手順は、大きく分けて次の3ステップです。
① 同じ日付を1つまとめて一覧にする（UNIQUE）
② 同じ日付の支出を合計する（SUMIF）
③ グラフが自動更新されるように設定する（OFFSET）
この流れで作成することで、入力するだけでグラフが自動更新される仕組みを作ることができます。
同じ日付を1つにまとめる（UNIQUE）
まず最初に「重複している日付」を1つにまとめた一覧を作成します。
この一覧は、あとでグラフの元データとして使用します。
❶ 最初に、新規シートを作成します。
現在、シートはこのような形になっています。
4月：基本支出表
4月グラフ：グラフ作成用
❷ グラフ作成用のB2セル「日付」、C2セル「金額」と入力します。
枠線は作っても作らなくても大丈夫です。
❸ 基本表をみると、4/3、4/7、4/10にそれぞれ２行ずつ入力があります。
今回は「同じ日付を1つにまとめる」ために、UNIQUE関数を使用します。
❹ グラフ作成用シートの「B3」セルをクリックし、数式バーに以下の数式を入力（コピペ）します。
※’4月’!はシート名になります。ご自分のシート名に合わせて下さい。
❺ B3セルに入力しただけで、自動的にすべての日付が反映されます。
ただし表示がおかしいので、B列を選択します。
❻ B列を選択したまま、表示形式を日付に変更します。
❼ 下記のような表示になれば、OKです。
※一番下に「1月0日」と表示される場合がありますが、これは空白セルが含まれているためです。
問題ありませんので、安心してください。
日別の支出を合計する（SUMIF）
ここでは、同じ日付の支出を合計するために、SUMIF関数を使用します。
このパートで、グラフの元データが完成します。
❶ 「C3」セルをクリックし、数式バーに以下の数式を入力（コピペ）します。
この数式は、B列の日付に一致するデータを「4月」シートから探して、金額を合計します。
※’4月’!はシート名になります。ご自分のシート名に合わせて下さい。
❷ C3セルの右下にカーソルを置くと、表示が「+」に変わります。
この「＋」を、そのまま下方向にドラッグします。
少し多めに、50行目くらいまでコピーしておくと安心です。
（あとからデータを追加しても対応できるため）
❸ 金額をカンマで区切りたい場合は、C列を選択 →「桁区切りスタイル」をクリック。
❹ これで、グラフ作成に必要な元データが完成しました。
グラフを自動更新させる設定（OFFSET）
グラフの元となる集計表は出来上がりました。
しかしこのままグラフを作成すると、「1月0日」などの不要なデータまで反映され、
グラフの表示がおかしくなる場合があります。
そこで、データが入っている範囲だけを自動で認識できるように、
OFFSET関数を設定します。
❶ 上メニュー「数式」から、「名前の管理」をクリック。
❷「新規作成」をクリック。
❸「新しい名前」のダイアログボックスに、次のように入力します。
● 名前：日付
● 範囲：4月グラフ
※集計表を作成したシート（プルダウンから選択できます）
● 参照範囲：=OFFSET(‘4月グラフ’!$B$2,1,0,COUNT(‘4月グラフ’!$C:$C),1)
この3点を入力したら、OKを押します。
❹ もう一度「新規作成」をクリック。
❺「新しい名前」のダイアログボックスに、次のように入力します。
● 名前：金額
● 範囲：4月グラフ
※集計表を作成したシート（プルダウンから選択できます）
● 参照範囲：=OFFSET(‘4月グラフ’!$C$2,1,0,COUNT(‘4月グラフ’!$C:$C)-1,1)
❻ 以下の2つが設定できればOKです。
自動で更新される棒グラフの作り方
ここでは、先ほど設定した「日付」「金額」を使ってグラフを作成します。
完成まであともう少し、一緒にがんばりましょう！
棒グラフを作成する手順
❶ まずはグラフを作成します。
範囲（B2:C11）をドラッグで選択し、上メニュー「挿入」→「棒グラフ」をクリック。
※日付表示、横幅の関係で〇/〇スタイルに変更しています。
❷ 下図のようなグラフができましたか？
一見おかしな表示ですが、まだ名前（OFFSET）が反映されていない状態なので問題ありません。ここから、正しく表示されるように調整していきましょう。
❸ グラフをクリックし、上メニュー「グラフのデザイン」→「データの選択」。
❹ 左側の凡例項目にある「金額」をクリックし、「編集」を選択。
❺ 系列値（下線部）を、「$C$3:$C$11」→「金額」に修正。
※「！」を消すとエラー表示が出ますので、削除しないよう注意してください。
❻ つづいて、横（項目）軸ラベルです。
どの項目を選択してもOKですが、今回は一番上の「4/1」を選択し、「編集」をクリックします。
❼ 軸ラベルの範囲（下線部）を、「$B$3:$B$11」→「日付」に修正。
※「！」を消すとエラー表示が出ますので、削除しないよう注意してください。
❽ ２つとも直し終えたら、OKを押します。
❾ グラフが以下のように表示されれば完成です！
この時点で、入力したデータがグラフとして正しく反映される状態になっています。
グラフが自動で追加されるか確認しよう
せっかく作成しても、正しく動かなければ活用できません。
仕上げに、グラフの動作を確認してみましょう。
❶ 集計表の横にできたグラフを切り取り（Ctrl+X）し、内訳表の横に貼り付け（Ctrl+V）します。
❷ 基本表の「日付」と「金額」欄に、実際に入力してみましょう。
同じ日付でいくつか入力してみることをお勧めします。
“日付” と “集計された金額” の内容が、自動的にグラフに反映されていればOKです。
これで、入力するだけで自動更新されるグラフの完成です。
お疲れさまでした！
まとめ
今回は、日別の支出を自動でグラフに反映させる方法を解説しました。
一度設定してしまえば、あとはデータを入力するだけで、グラフが自動で更新されるようになります。
手動で範囲を選び直す必要がなくなるため、日々の支出管理がぐっとラクになりますよ。
ぜひ、ご自身の家計簿や支出管理に活用してみてください。
【関連記事】
◎このグラフは、下記の支出管理表をもとに作成しています。
まだ家計簿を作成していない方は、こちらから作ってみてください。
【関連用品】
より支出管理がしやすくなる、こういったアイテムもおすすめです。
コメント