家計簿アプリを、より使いやすく自分仕様に改造していきましょう！
以前の投稿で、家計簿アプリとして最低限の機能を持たせることができました。
（その６までをご確認いただいていない方は、まずは下記まとめをご確認ください。）
ただ、これは本当に最低限の機能しかなく、勘定科目も『固定費』と『変動費』しかないので、
各家庭に合った勘定科目を自由に追加できるように、
このマクロのデコレーションをしていく必要があります。
前回、『ドロップダウンリストを使って勘定科目を追加する』方法について説明していきましたが、今回はその続きで、追加した各勘定科目の合計の表示について説明していきたいと思います。
【本記事の目標】
Excel関数を使いこなして楽に合計を表示させる
簡単なイメージは下記記事に記載していますので、イメージづくりや今回以降の流れの参考に。
前回の記事を見られていない方は先にこちらをご確認ください。
関数を使って少しだけ入力を楽に
どうやって入力を楽にする？
前回の記事で任意の勘定科目（詳細科目）の追加ができるようになりました。
ただ、今のままだと、
１．金額を入力
２．収入か支出を選択
３．固定費か変動費を選択
４．詳細科目を選択
５．『リストに追加』ボタンをクリック
と、１つの項目を追加するだけでもかなり手間がかかってしまいますよね。
このままだと三日坊主は必至、むしろ三日も続かない可能性大です。。。
なので、少しでも入力を楽にしましょう。
今回新しく「詳細科目」を追加しましたが、これを見れば、
・収入か支出か
・固定費か変動費か
というのは分かりますよね。
なので、この２つについては関数を使って自動的に入力できるようにしておきましょう。
今、仮の詳細科目として下記を用意しました。
これを見てもらったら分かりますが、収入の詳細科目は『固定/変動』の欄を空欄にしておけば、この欄を確認するだけで、
・収入か支出か
・固定費か変動費か
というのが分かるようになります。
なので、
- 詳細科目を選んだ時、その詳細科目が右下の表のどこにあるのか。
- 右下の表の詳細科目の左に書いてあるのは何か。
- 「固定費」か「変動費」と記載があれば、その内容を”D4″セルに記載し
“C4″セルに「支出」を記入する。 - 記載がなければ、”D4″セルを空欄にして、”C4″セルに「収入」と記載する。
ということを自動でできるようになれば、詳細科目を選択する“E4″セルを選択するだけで他の内容（収入か支出か、固定費か変動費か）の記載は不要になり、入力が非常に楽になります。
では、どうやってこれを自動でできるようにするのか。。。
またマクロで作る？？
もちろんマクロを使えば作ることができます。
ただ、実はこの内容であれば、Excelシート関数だけで実現が可能です。
今回はExcelシート関数を使って、上記の自動化をしていこうと思います。
まずは完成形
先に完成形を書いておきます。
■”C4″セル （収入か支出か）
=IF(
OFFSET(テーブル2[[#見出し],[固定/変動]],
MATCH(E4,テーブル2[科目],),0)
=””,”収入”,”支出”)
■”D4″セル （固定費か変動費か）
=OFFSET(テーブル2[[#見出し],[固定/変動]],
MATCH(E4,テーブル2[科目],0),0)
このように、”C4″セルは３つ、”D4″セルは２つの関数（If、Offset、Match）を使うだけで、今実現したいと思っている自動化が可能になります。
それでは、それぞれの関数がどういったものなのか説明していきます。
Match関数について
まずはどちらのセルでも使用したMatch関数について。
この関数は、
検索したい単語を、検索したい領域の中で検索し、
見つかった場所が左上から何番目にあるか、
を教えてくれる関数です。
使用する場合は下記のように記入します。
=Match(「検索したい単語」, 「検索したい領域」, 「検索の方法」)
ここで、
「検索したい単語」は直接記入しても良いですし（”生活費”みたいな書き方）、
検索した単語が記入されているセルを指定してもOKです（E4みたいな書き方）
「検索したい領域」は縦でも横でも指定することが可能です。
縦の場合は、上から何番目にあったかを返してくれます。
横の場合は、左から何番目にあったかを返してくれます。
ただし、指定できるのは縦1列もしくは横1行のみです。
2行以上もしくは2列以上は指定できません。
「検索の方法」はどのぐらいの制度で検索してくるかを指定します。
「検索の方法」を『0：完全一致』を指定すると、「検索したい領域」の中で、「検索したい単語」と全く同じ内容が記載されている場所を返してくれます。
「検索したい単語」が数値で、「検索したい領域」が昇順もしくは降順で並んでいる場合、
「検索の方法」に『1：以上』『-1：以下』を指定することで、「検索したい単語」の値に一番近い値がある場所を返してくれます。
完成形の関数に記載されている内容ですと
MATCH(E4,テーブル2[科目],0)
なので、
E4セル（詳細科目）の中身が、右下のテーブルの「科目」の列の中で
完全一致する場所が上から何番目にあるか。
というのを返してくれます。
例えば、E4セルを「生活費」を選択すると、この計算式の結果は『３』になります。
Offset関数について
次に、どちらのセルでも使ったOffset関数について。
この関数は、
指定したセルから、指定した分だけ移動させ、指定したサイズを選択する
ということをする関数です。
使用する場合は下記のように記入します。
＝Offset(「①指定セル」, 「②縦の移動数」, 「③横の移動数」, 「④縦の選択セル数」, 「⑤横の選択セル数」)
イメージとしては下記のような動きになります。
（＝Offset(“A1”,5,3,4,2)と指定した場合）
ここで、④⑤については指定なしも可能です。
その場合、１セルだけを選択することになるので、移動後のセルに記載されている内容が表示されます。
逆に、④⑤を記入して、移動後のセルが１つのセルではなく複数セルの領域を指定した場合、セルに記載されている内容は表示できません（複数セルのデータを１つのセルに表示することになるので）。
じゃあ④⑤はどういった時に指定するのか、というと、
他の関数と組み合わせて、他の関数が計算に使いたい領域範囲をこの関数を使って指定してあげる場合に使います。
例えばAverage関数と組み合わせることで、平均を計算したい領域を、計算式自体をいじらずに操作ができるようになります。
下記はAverage関数とOffset関数の組み合わせ例です。
E1～E4セルに入力された値を変更することで、
Average関数で平均値を計算する領域を好きに変えることができます。
完成形の関数に記載されている内容ですと
OFFSET(テーブル2[[#見出し],[固定/変動]],
MATCH(E4,テーブル2[科目],0),0)
なので、
“I9″セルの「固定/変動」と記載されている、テーブルの見出し部分から、
Match関数で判明した縦の数だけ行数を移動させ、
その欄に記載されているデータ。
を返してくれます。
例えば、E4セルを「生活費」を選択すると、Match関数の結果は『３』になるので、
“I9″セルの３つ下のセルのデータである『変動費』というのが結果として表示されます。
If関数について
次に、If関数について
この関数は、
条件式に合う、合わない（正しい、誤り）を判別し、それぞれ場合に合った値を返す
ということができる関数です。
使用する場合は下記のように使います。
=If(「条件式」, 「合う場合（正しい場合）」, 「合わない場合（誤りの場合）」)
この「条件式」については、マクロでのIf文と考え方や書き方はほぼ同じです。
マクロでのIf文の書き方については、下記記事でも記載していますので、ご確認ください。
完成形の関数に記載されている内容ですと、
=IF(
OFFSET(テーブル2[[#見出し],[固定/変動]],
MATCH(E4,テーブル2[科目],),0)
=””,”収入”,”支出”)
なので、
“I9″セルの「固定/変動」と記載されている、テーブルの見出し部分から、
Match関数で判明した縦の数だけ行数を移動させ、
その欄に何も記入されておらず空欄であれば『収入』を、
そうでなければ『支出』を表示する。
という結果になります。
例えば、E4セルを「生活費」を選択すると、Match関数の結果は『３』になるので、
“I9″セルの３つ下のセルのデータには『変動費』というのが記載されており、
空欄ではないので『支出』を結果として表示されます。
If関数の条件式で、そのセルが空欄かどうか、というのを判別する場合、
下記のような書き方をします。
=If(A1=“”,”空欄”,”空欄でない”)
ここで “” というのが空欄という意味になります。
この書き方は条件式だけでなく、
正しい場合・誤りの場合に空欄を表示する、といったときにも使えるので、
覚えておくと良いと思います。
ただし、１つ注意点として、
これで空欄を表示させた場合、この値は空欄ではありますが、文字列と認識されます。
なので、これで空欄を表示させたセルを含めてグラフを作ったりすると、
意図したものと違うグラフができる場合があります。
より複雑なExcel関数やグラフを作れるようになってくると起こることがあるので、
その時に思い出していただければと思います。
この関数を入れておくことで、詳細科目を”E4″セルで選択するだけで、
収入か支出か、固定費か変動費か
というのを自動入力できるようになりました。
入力しなくてもよくなったことが分かるように、下記のように色を変えておきましょう。
各勘定科目の合計を表示させる（関数を使う）
勘定科目も入力しやすくなったことですし、次に進めましょう。
次は、ずっと空欄になっていた 各詳細科目の合計金額 について表示させていきましょう。
ここも、収入合計や支出合計等と同じようにマクロを使って合計を計算させることもできますが、
せっかくなので、こちらもExcel関数を使って入力してしまいましょう。
SUMIF関数を使って各勘定科目の合計を表示する
各勘定科目の合計を表示しようと思うと、一番使い勝手が良いのが SUMIF関数 です。
SUMIF関数については、過去にも何度か説明していますので、下記の記事をご参考下さい。
早速ですが完成形を書いてしまいます。
収入と支出によって金額が表示されている列が異なりますので、ご注意ください。
■収入系
=SUMIF($D$8:$D$1000,[@科目],$E$8:$E$1000)
■支出系
=SUMIF($D$8:$D$1000,[@科目],$F$8:$F$1000)
これで、
・任意に追加できる詳細科目のドロップダウンリスト
・追加した詳細科目をドロップダウンリストから選ぶだけで、
収入か支出か、固定費か変動費か を自動選択
・詳細科目ごとの合計計算
について出来上がりました♪
あとはこれをグラフにして、より見やすい家計簿アプリにしていきましょう！
グラフの作成については次回説明していこうと思います。
次の記事へ
コメント