●今までプログラミングなんかに触れたことが無くて
「今更そんなの使いこなせるわけない」と思っている方
●小学校でプログラミングが必修科目になり、
子どもに「ここ分からないから教えて～」と言われても
「無理！！」と言うしかなくて困ったお父さん・お母さん
そんなプログラミング全くの初心者に向けて、
「こうやったら自動で動くソフト（Excelマクロ）が作れるんだ！」
というのを理解していただくように、
ゼロからソフトを作っていく過程を紹介していきたいと思います。
Windowsのパソコンがあれば大抵標準装備されているExcelを使って紹介しますので、
この投稿のシリーズを最後まで読んでいただければ、
また、投稿を読みながら実際に一緒にソフトを作っていっていただければ、
「自分で実際にソフトを作ることができて、プログラミングに少し自信がついた！」
と思ってもらえるかと思いますので、是非とも一緒に手を動かしてみてください。
【本記事の目標】
少し複雑な集計関数を、マクロから入力させよう
いよいよ最後、6つ目の動きを作っていきましょう。
１．金額を入力して勘定科目を選択したら、リストに自動で記入する。
２．記入したリストを自動的に集計する。
３．集計した結果から、今月使った割合が多い項目が何かわかるようにグラフ化する。
４．翌月の内容を記入する際は、自動で新しい白紙のリストを作成して記入していく。
５．毎月の集計結果を集めて、毎月の家計の変化がわかるグラフを作る。
６．毎月の集計結果をさらに集計し、その年使った割合が多い項目がわかるようにグラフ化。
これまでの動きを確認されていない方は、過去の投稿もご確認ください。
毎年の集計表を作ろう
日々の収支を入力する表を作りました。
毎月を集計し、その推移を確認できるようになりました。
あとは、毎年の集計ができれば、
・年間の収支のバランス（年収と貯蓄）
・年収に対する固定費もしくは変動費のインパクト
といった分析につなげることができ、
例えば
「今年は節約頑張ったから年間貯蓄でこんなに増えてる！」とか、
「去年は固定費が高かったから、固定費見直しをしたら、こんなに余裕が出た！」
といった感じでモチベーションアップにつなげていくことができます。
正直、家計簿をつけていくのは数カ月続けるのも大変だと思うのですが、
出来れば1~2年、可能ならそれ以上続けていただいた方が、
家計の管理という意味でも、節約や収入アップへのモチベーションアップの意味でも、
非常にメリットが大きいので、是非ともやっていただきたい！
その時に、年単位での集計結果があると分析がはかどるので、
年単位で集計していくシートも作っておきましょう。
毎年の集計用シートの準備
毎年の集計方法についても、Excel関数のみで作った方が良いので、Excel関数で作っていきます。
ただ、いずれにしても、集計用のシートを準備しておく必要があるので、
まずはそれを準備しましょう。
基本的には『月ごとの推移』のシートで作ったものとほぼ同じでOKです。
今回は下記のような感じで『年ごとの推移』というシートを作りました。
このシートの表に、Excel関数を入力して毎年の集計結果を反映させていく方法について解説していきます。
Excel関数を使った集計方法
SUM関数について
集計、つまりは合計値を計算する場合、Excel関数では【SUM】という関数があります。
例えば、下記のようなデータがあったとき、2022年の収入の集計結果を計算するためには、
=SUM(F6:F9)
という関数を任意のセルに入力してあげればOKです。
上記の式は同じシート内で計算する場合ですので、『月ごとの推移』のシートに入力されているデータを使って、『年ごとの推移』のシートに集計結果を表示したい場合は、
＝SUM(‘月ごとの推移’!F6:F9)
という記載をする必要があります。
これで一応集計は可能なのですが、
集計するデータは、毎月シートが複製されるたびに行追加されていくと計算する領域を変える必要があるので、都度修正していく必要があります。
また、2022年→2023年→2024年・・・と時間がたつと、
そのたびに2022年の場所だけを探して選んで～、2023年の場所を探して選んで～、とかなり手間になってきます。これを解消できるのが、【SUMIF】という関数です。
SUMIF関数について
SUMIF関数というのは、指定した領域の中で条件に合う部分だけ集計する、
という条件分岐関数です。
この関数は下記のように使います。
=SUMIF([条件を検索したい領域],[条件式],[合計したい領域])
ここで、[条件を検索したい領域]というのは、[条件式]で書いた条件に当てはまっているかどうかを探したい領域を指定します。
[合計したい領域]というのは、実際に集計したい領域を指定します。
これらを指定することで、[検索したい領域]の中で[条件式]に当てはまったセルと同じ行の[合計したい領域]の値が集計されます。
という感じに説明文を書きましたが、これだけだと分かりにくいので、実際の例を見てみましょう。
例えば、『月ごとの推移』のシートが下記のような状態になっていて、この中から『2022年の収入合計を出したい』という場合、2022年の行だけを集計したいので、「年」の列が「2022」になっているところと同じ行の「収入合計」の欄を集計する、ということができればＯＫです。
ですので、「年」の列が[条件を検索したい領域]になります。
また、[条件式]は「2022」と同じになっていること、になるので、「2022」そのままでＯＫです。
最後に「収入合計」の欄を集計したいので、[合計したい領域]は「収入合計」の欄になります。
これを『年ごとの推移』のシートの2022年の部分に書くと下記のようになります。
=SUMIF(月ごとの推移!C:C,2022,月ごとの推移!F:F)
ここで、[条件式]に「2022」をそのまま入力していますが、セルを指定することもできます。
例えば、『年ごとの推移』の「年」の列に各年数を入力していき、その年数の集計をしたい場合、下記のように書くこともできます。
=SUMIF(月ごとの推移!C:C,$C6,月ごとの推移!F:F)
この式を下の行にもコピーしていき、「年」の欄に2022→2023→2024・・・と書いていけば毎年の収入合計を表示することができるようになります。
同じことを他の項目にも適用していくと、『年ごとの推移』のシートには下記のような関数が入っていくことになります。
実際の計算結果は下記のようになり、2022年と2023年の集計結果が反映されているはずです。
SUMIF関数の[条件式]について、
今回のように、そのまま値を入力したりセルを指定した場合は、
[条件式]と同じもの
という条件で検索されます。
今回は「2022」という数値でしたが、文字列でも可能です。
例えば下記のような表の場合、[条件式]を「東京」にすれば、
東京の部分の集計を出すことができます。
また、検索したいものが数値だった場合、
○○より大きな値の場合 ○○より小さな値の場合
といった感じで、全く同じ以外の条件を指定したい場合があるかもしれません。
このような条件でもSUMIFを使うことは可能です。
このような条件の場合の[条件式]の書き方は、
１．IF文の条件式を書く時と同じイメージで書いて
２．比較元のセル名を消して
３．ダブルクォーテーションで挟んで文字列にしてしまう
という流れになります。
例えば下記のような表で、年齢が30歳以上の身長の合計がいくらになるか計算する場合、IF文で条件式を書こうと思うと、
=IF(A2>=30,・・・)
と書けます。
この条件式から比較元のセル名を消すと
>=30
になって、これをダブルクォーテーションで挟むので、
“>=30”
という形になります。
この条件式を実際にSUMIF関数に入力すると、
=SUMIF(A2:A11,”>=30″,B2:B11)
といった感じになります。
このような条件式の書き方は、他の条件分岐関数でも使えるので、知っていて損はないと思います。
最後にグラフを作って完成！！
これで毎年の集計表も完成しました！
あとは確認しやすいようにグラフを作っておけば完成です！
グラフの構成については、【その５】で『月ごとの推移』のシートのグラフを作る際に解説したものとほとんど同じでOKかと思います。
どんな感じに作っていたかは、下記記事をご確認ください。
『月ごとの推移』のシートで作ったものと同じものを作ると、下記のような感じになりました。
これについてはお好みで作っていただいて大丈夫かと思います。
各々が一番見やすいグラフを作ってもらったらOKです。
完成！！
これで、
・日々の家計簿の入力
・入力した値を毎月集計し、項目別に分類
・毎月の集計結果の推移を確認できる表・グラフを作成
・さらに毎年の集計結果の推移を確認できる表・グラフも作成
ということができるようになりましたので、
これにて、家計簿アプリ 完成です！！
超入門編ということで、かなり回りくどい説明も多かったと思いますが、
ここまで読んでいただいて、ありがとうございます！
ただこの家計簿アプリは、とりあえず最低限の機能しか持たせていないので、
より使いやすくしようと思うと、これをさらに自分なりに
・デコレーション
・ブラッシュアップ
・カスタマイズ
していく必要があります。
自分が一番使いやすい形を作り上げて、
自分の自分のための自分専用の家計簿アプリを作っていってください。
いずれ、１つの参考として、この家計簿アプリの「デコレーション」「ブラッシュアップ」「カスタマイズ」の方法についての記事も書いていこうと思います。
ではまた。
コメント