家計簿アプリを、より使いやすく自分仕様に改造していきましょう！
以前の投稿で、家計簿アプリとして最低限の機能を持たせることができました。
（その６までをご確認いただいていない方は、まずは下記まとめをご確認ください。）
ただ、これは本当に最低限の機能しかなく、勘定科目も『固定費』と『変動費』しかないので、
各家庭に合った勘定科目を自由に追加できるように、
このマクロのデコレーションをしていく必要があります。
前回、『任意に追加できる詳細科目の入力と合計』の方法について説明していきましたが、今回はその続きで、追加された詳細科目についてのグラフの作成について説明していきたいと思います。
【本記事の目標】
色々なグラフを作って、より分かりやすい家計簿アプリにしよう
簡単なイメージは下記記事に記載していますので、イメージづくりや今回以降の流れの参考に。
前回の記事を見られていない方は先にこちらをご確認ください。
各詳細科目のグラフを作ろう
まずは、、、
前回までで、各勘定科目の合計値まで表示できるようになりました。
今回はそれを数値ではなくグラフにして、より分かりやすい形にしていこうと思います。
今回作るグラフに関しても、各勘定科目の合計値のデータがある場所や範囲は基本的に動かないので、以前と同じようにマクロは使わずに作っていこうと思います。
前回説明している記事のリンクも置いておきます。
今あるグラフについては少し場所を移動させて、下記の部分に色々なグラフを作っていこうと思います。
まずは基本の棒グラフ
まずは基本の棒グラフを作ります。
各勘定科目の大きさが一番わかりやすいグラフになります。
データ自体は下記領域にありますので、この領域のデータを使ってグラフを作っていきます。
棒グラフについては過去の記事でも説明していますが、
- グラフ化したいデータを選択する
- ツールバー⇒「挿入」⇒「グラフ」⇒「2-D 縦棒」を選択
という手順でグラフが作れます。
できたグラフは、各々が見やすい形でデコレーションしていっていただけたらと思います。
個人的なおすすめポイントとしては、
●グラフのタイトルは消してしまう（縦が短すぎて見にくいので）
●縦軸のラベルに『金額（円）』を入れておく
●収入は青、変動費はピンク、固定費はオレンジ、に変更しておく
●縦軸の上限を、おおよそあり得る支出の上限ぐらいに設定しておく
（収入（給料）に引っ張られて縦軸が広くなり、支出が見えにくくなるので）
このあたりを織り込んでもらったら見やすいグラフになるかと思います。
上記を織り込んだら下記のようなイメージになります。
上記のグラフでは収入の項目（給料）も入れてありますが、収入と支出の比較グラフはもうあるので、
収入の項目以外で棒グラフを作っていただくのも良いと思います。
その場合縦軸の上限を設定する必要がないので、より分かりやすいグラフになると思います。
詳細科目ごとの比率を確認する（円グラフ）
数値の大小に関しては棒グラフを見れば一目瞭然ですが、
「支出の全体の中で、各詳細科目がどのぐらいの比率になっているのか？」
というのを見やすくしようと思うと、円グラフを作るのが効果的です。
「支出の中で」という条件が付くので、円グラフを作る場合は収入のデータ以外でグラフを作る必要があります。
なので、下記領域のデータを使って円グラフを作ります。
円グラフについても過去の記事でも説明していますが、
- グラフ化したいデータを選択
- ツールバー⇒「挿入」⇒「グラフ」⇒「2-D 円」を選択
という手順でグラフが作れます。
できたグラフは、各々が見やすい形でデコレーションしていっていただけたらと思います。
個人的なおすすめポイントとしては、
●グラフのタイトルは消してしまう（縦が短すぎて見にくいので）
●判例も消してしまう
●代わりに代わりにデータラベルを追加し、ラベルオプションから「分類名」のみを表示させる
●変動費はピンク、固定費はオレンジ、に変更しておく
このあたりを織り込んでもらったら見やすいグラフになるかと思います。
上記を織り込んだら下記のようなイメージになります。
今回追加したグラフと、その上にある「支出内の割合」のグラフは、色だけを見ると同じことを示しています。（今回追加したグラフは、その中をより細分化・詳細化したものになってます）
ただ、グラフの左右が逆になっていますね。
これは詳細科目のテーブルを作る際に
収入 ⇒ 変動費 ⇒ 固定費
の順で書いてしまっているせいです。
しかし、前回のグラフの順番としては
収入合計 ⇒ （支出合計 ⇒） 固定費 ⇒ 変動費
の順になっていますので、円グラフの表示される順番が逆になってしまっているのです。
これが円グラフの左右が逆になってしまっている原因ですので、
詳細科目を追加される場合は、
収入の項目 ⇒ 固定費の項目 ⇒ 変動費の項目
の順で追加していくよう意識してもらったら、グラフが変になることはないと思います。
修正したグラフは下記の通りです。
収支合計をわかりやすくする「ウォーターフォールチャート」
今回グラフを作った際に、実は意図的に下の方の空間を空けていました。
実は、今回はここに通常の棒グラフや円グラフではない、ちょっと特殊なグラフを作っていきたいと思っています。
その名も 『ウォーターフォールチャート』
普段の生活ではほとんど見かけることは少ないと思いますし、会社員の方々の中でも見たことがある方は少ないかもしれません。
「ウォーターフォールチャート」は数値の増減やその変化、およびそれらの変化によって最終的にその数値がどうなったのか、というのが一目でわかる非常に便利なグラフです。
今回の家計簿アプリで言うと、
その月の収入に対して、どの勘定科目でどのぐらいその収入を取り崩していって、最終的にその月の収支がプラスだったのかマイナスだったのか、というのが一目でわかるグラフになっています。
完成品のイメージは下記のようなグラフになります。
このグラフを作るためには、
１．収入の項目はプラス、支出の項目はマイナスにする。
２．表の最後に『収支』の項目を追加して、そこまでの合計金額を入れる。
というのをする必要があります。
ウォーターフォールチャート用のデータ追加とグラフ作成
まずは1つ目のデータの追加
１．収入の項目はプラス、支出の項目はマイナスにする。
に関してです。
これは単純に、今表示されている各勘定科目の合計にプラスかマイナスを追加するだけです。
既に計算されている値を書き換えてしまうと棒グラフに影響が出ますので、
隣の列に『グラフ用』として追加しましょう。
追加すると下記のようになります。
これで必要なデータの1つ目が完成です。
次に2つ目の
２．表の最後に『収支』の項目を追加して、そこまでの合計金額を入れる。
に関してです。
こちらも単純に、今ある表の一番下に『収支』という項目を追加して、
上記で追加した『グラフ用』の列の合計（SUM）を計算すればOKです。
追加すると下記のようになります。
あとは、『科目』の列と『グラフ用』の列を選択した状態で、
ツールバー⇒「挿入」⇒「グラフ」⇒「ウォーターフォール図」
と選択すれば、ウォーターフォールチャートが作られます。
ただ、完成した直後のウォーターフォールチャートは、
全ての項目について、左から順にプラスなら上向きに、マイナスなら下向きに棒グラフが伸びたような状態になっています。
基本的にそれで問題ありませんが、『収支』に関しては最後の合計になってくるので、１つ左の棒グラフからスタートではなく、０の点からスタートさせる必要があります。
このような合計の項目については、グラフ上のその項目のみを選択して
右クリック ⇒ 合計として設定(T)
を選択すると、この項目が合計として認識されて、０の点から棒グラフが出てくる形に変わります。
あとは、今までのグラフと同じように、グラフの色や軸のラベル等を編集してもらったら、
下記のようなグラフを作ることができます。
「月ごとの推移」、「年ごとの推移」に反映させる
まずは各シートの準備（詳細科目の欄を追加）
毎月の収支を入力するシートについては、詳細科目を追加することができました。
あとは、「月ごとの推移」、「年ごとの推移」にその項目を反映させることができれば完成です！
追加するにあたって、まずは各シートに今回追加した詳細科目の欄を追加しましょう。
また、合わせてグラフも追加しちゃいましょう。
とりあえず作ってみた各シートの一例を置いておきます。
これについてはほとんど見栄えの問題ですので、各々が見やすい形にしてもらって良いと思います。
【月ごとの推移】
【年ごとの推移】
上記で作ったグラフは棒グラフと折れ線グラフが重なったグラフになっています。
また、収入である「給料」の項目だけ第2軸（右の縦軸）に設定しています。
これらは下記手順で作ることができます。
【棒グラフと折れ線グラフの複合】
複合グラフを作る場合は、まずはどちらか好きな方のグラフを作っておき、変更したい系列を選択した状態で、
右クリック⇒系列グラフの種類の変更(Y)
⇒ グラフの種類の変更ウィンドウで、変更したい系列のグラフを変更する。
という手順で複合グラフを作ることができます。
ただ、選択するグラフによっては複合グラフを作ることができない組み合わせもありますので、ご注意ください。（円グラフと棒グラフの組み合わせとか）
【第2軸の設定】
指定の系列だけ第2軸に設定したい場合は、上記とほぼ同じ手順で変更したい系列を選択した状態で、
右クリック⇒系列グラフの種類の変更(Y)
⇒ グラフの種類の変更ウィンドウで、
第2軸を設定したい系列で第2軸にチェックを入れる
という手順で第2軸を設定することができます。
これらはグラフの見栄えを良くするテクニックの１つですので、知っておいて損はないと思います。
『月ごとの推移』の編集（マクロの修正）
それでは、『月ごとの推移』に追加した詳細科目の欄に、自動的に毎月の収支に記載された詳細科目の値が表示されるようにしましょう。
基本的な考え方はほかの項目（収入合計・支出合計や、固定費・変動費）と同じ考え方で問題ないです。
これらの項目は、毎月の収支のシートを『シートの複製・初期化』をクリックしたタイミングで、値が入ったセルを参照する関数が記載されるようになっており、『SheetCopy1』プロシージャの中で、下記部分にそのコマンドが記載されています。
ここに記載されている内容をコピーして、参照元と貼り付け先の部分だけ修正すればOKです。
実際にこの部分の下に、今回つかした詳細項目の各項目を追加すると、下記のようになります。
Option Explicit
Sub SheetCopy1()
'シートの複製
Dim YearInput As Integer
Dim MonthInput As Integer
With ThisWorkbook.Sheets("入力")
YearInput = .Range("M4").Value
MonthInput = .Range("O4").Value
End With
ThisWorkbook.Sheets("入力").Copy After:=ThisWorkbook.Sheets("入力")
ActiveSheet.Name = YearInput & "年" & MonthInput & "月"
ThisWorkbook.Sheets("入力").Range("B8:F10000").ClearContents
ThisWorkbook.ActiveSheet.Shapes("正方形/長方形 5").Delete
'=============毎月データの集計用コード（参照関数の入力ver） ここから=================
Dim EndRow1 As Integer
With ThisWorkbook
'『月ごとの推移』シートの最終行を確認
EndRow1 = .Sheets("月ごとの推移").Cells(.Sheets("月ごとの推移").Rows.Count, 3).End(xlUp).Row
EndRow1 = EndRow1 + 1
'=============行挿入のコード ここから=================
With .Sheets("月ごとの推移")
.Range(.Cells(EndRow1, 3), .Cells(EndRow1, 18)).Insert Shift:=xlDown
End With
'=============行挿入のコード ここまで=================
'データを貼り付けていく
.Sheets("月ごとの推移").Cells(EndRow1, 3).Value = "='" & YearInput & "年" & MonthInput & "月'!M4" '年
.Sheets("月ごとの推移").Cells(EndRow1, 4).Value = "='" & YearInput & "年" & MonthInput & "月'!O4" '月
.Sheets("月ごとの推移").Cells(EndRow1, 5).Value = YearInput & "年" & MonthInput & "月" '年/月
.Sheets("月ごとの推移").Cells(EndRow1, 6).Value = "='" & YearInput & "年" & MonthInput & "月'!K3" '収入合計
.Sheets("月ごとの推移").Cells(EndRow1, 7).Value = "='" & YearInput & "年" & MonthInput & "月'!K4" '支出合計
.Sheets("月ごとの推移").Cells(EndRow1, 8).Value = "='" & YearInput & "年" & MonthInput & "月'!K6" '固定費
.Sheets("月ごとの推移").Cells(EndRow1, 9).Value = "='" & YearInput & "年" & MonthInput & "月'!K7" '変動費
.Sheets("月ごとの推移").Cells(EndRow1, 10).Value = "='" & YearInput & "年" & MonthInput & "月'!K10" '給料
.Sheets("月ごとの推移").Cells(EndRow1, 11).Value = "='" & YearInput & "年" & MonthInput & "月'!K11" '光熱費
.Sheets("月ごとの推移").Cells(EndRow1, 12).Value = "='" & YearInput & "年" & MonthInput & "月'!K12" '通信費
.Sheets("月ごとの推移").Cells(EndRow1, 13).Value = "='" & YearInput & "年" & MonthInput & "月'!K13" '家賃
.Sheets("月ごとの推移").Cells(EndRow1, 14).Value = "='" & YearInput & "年" & MonthInput & "月'!K14" '食費
.Sheets("月ごとの推移").Cells(EndRow1, 15).Value = "='" & YearInput & "年" & MonthInput & "月'!K15" '生活費
.Sheets("月ごとの推移").Cells(EndRow1, 16).Value = "='" & YearInput & "年" & MonthInput & "月'!K16" '衣服代
.Sheets("月ごとの推移").Cells(EndRow1, 17).Value = "='" & YearInput & "年" & MonthInput & "月'!K17" '教育費
.Sheets("月ごとの推移").Cells(EndRow1, 18).Value = "='" & YearInput & "年" & MonthInput & "月'!K18" '娯楽費
End With
'=============毎月データの集計用コード（参照関数の入力ver） ここまで=================
End Sub
これで毎月の収支を入力した後で『シートの複製・初期化』をクリックしたら、今回追加した詳細科目が『月ごとの推移』のシートに反映されるようになっているはずです。
実際にクリックしてみて、問題なく動いているか確認しておきましょう。
『年ごとの推移』のシートの修正
最後に『年ごとの推移』のシートも修正しましょう。
と言いたいところですが、実は『年ごとの推移』のシートではマクロもないですし、ほとんど修正する部分はないです。
強いて言うなら、今回追加した詳細科目の欄に、隣の項目（収入合計・支出合計や、固定費・変動費）に記載されている関数を、そのままコピーするだけでOKです。
これで【各家庭に合った勘定科目を自由に追加】について実装することができました。
ここまでできてくると、手作りとは言え見栄えや使い勝手についてもそれなりのものになってきていると思います。
さすがに市販のアプリほどではないかもしれませんが、それでも、使っていくうえでそんなに不便ではないように思います。
このアプリを、もっともっと良くしていって、市販されているものと遜色ないものにしていけたらと思いますので、引き続き頑張っていきましょう。
次の記事へ
コメント