こんにちは、はな夫（@logicalifer）です。
今回は、前回Googleスプレッドシートで作成した「家計簿2021」という家計簿ファイルの続きを設定していきます。
もし、前回の記事をご覧になっていない方は、下記の記事をご参照ください。
費目の集計をする
各月の各費目で使用した金額を合算していきます。
2021年01月の家計_食費は下記の関数で集計しています。
初稿では、下記のfilter関数を使用していましたが、query関数を使用することで、もっと簡潔に記述することができました。query関数を使用した場合の記載は下記の記事を参照いただければ幸いです。
以降は古い記載ですが、そのまま残しておきます。
=iferror(sum(filter(indirect(Calc!$E$10),indirect(Calc!$D$10)=$E3,indirect(Calc!$B$10)>=date(Calc!$A$3,G$1,"01"),indirect(Calc!$B$10)<EOMONTH(date(Calc!$A$3,G$1,"01"),0))),0)長くてよくわからないですね。
分解して一つずつご説明いたします。
内部計算用シートの作成
まず、どのセルが何を示しているのかよくわからなくなってしまうため、内部計算用のシートを用意しました。
下記の感じです。
- A3セル：年を表しています。集計シートの年月もここの値を参照しています。来年以降もこの値を変えるだけで済みます。
- A6セル：フォームから入力されたデータが記録されたデートシートの名前です。
- A9～H9セル：データシートの各項目を集計するときに使用する文字列です。
- A10～H10：A6セルとA9~H10と組み合わせた値です。例えばA10セルは「=$A$6&A9」として、Form!A$:$Aを表しています。
これらの多くは関数の引数として使用するため、indirect関数と組み合わせて使用することになります。
クエリを作成する
もとのシートに戻り、クエリを作成していきます。この関数でコアになる部分はfilter関数になりますので、まずはそこを作ります。(ここから先はG3セルの値を例にして記載します)
filter(indirect(Calc!$E$10),indirect(Calc!$D$10)=$E3,indirect(Calc!$B$10)>=date(Calc!$A$3,G$1,"01"),indirect(Calc!$B$10)<EOMONTH(date(Calc!$A$3,G$1,"01"),0))filter関数
filter関数の仕様は下記に紹介されています。
。。。何を言っているのか難しいですが、フィルタリングして出力するデータ群を1つ目に、2つ目以降にそのデータ群のフィルタの条件を指定する関するです。
この関数を利用するために、抽出条件を定義すると、下記の4つになります。
- 支払金額を抽出する
- 費目がE列の名前と一致する
- 支払日が該当月の初日以降
- 支払日が該当月の最終日以前
以降でそれぞれを考えていきます。
支払金額を抽出する
今回取り出したいのは支払金額のため、1つ目の引数にはCalcシートに記載したCalc!E10セルの値を使用します。
indirect(Calc!$E$10)費目がE列の名前と一致する条件を設定する
フィルタリングする条件を並べていきます。
費目でフィルタリングするために、Calcシートに記載したCalc!D10セルと、E3セルの値が一致していることが条件となります。
indirect(Calc!$D$10)=$E3支払日が該当月の初日以降を設定する
データシートの日付列はCalc!B10を参照します。
初日を集計シートにそのまま記載しても良いのですが、表としてサマリを見たときに違和感のある日付ですので、計算で算出して表に見えないようにします。
そこで、「G2セルの月の初日」を計算するために、date関数を使用します。
初日は年、月、01とすればよいので、下記で初日の日付が計算できます。
date(Calc!$A$3,G$1,"01")この日が支払日よりも前である必要があるので、下記を条件とします。
indirect(Calc!$B$10)>=date(Calc!$A$3,G$1,"01")支払日が該当月の最終日以前を設定する
最終日は月によって異なるため、emonth関数を使用します。
初日として算出した日を代入すればその月の最終日が得られますので、下記を指定します。
EOMONTH(date(Calc!$A$3,G$1,"01"),0)条件としては下記になります。
indirect(Calc!$B$10)<EOMONTH(date(Calc!$A$3,G$1,"01"),0)以上で、フィルタリングの条件が揃いクエリである下記が算出できました。
filter(indirect(Calc!$E$10),indirect(Calc!$D$10)=$E3,indirect(Calc!$B$10)>=date(Calc!$A$3,G$1,"01"),indirect(Calc!$B$10)<EOMONTH(date(Calc!$A$3,G$1,"01"),0))クエリで抽出したデータを合計する
上記のクエリにより、該当する費目の支払金額の配列が得られます。
この配列の合計を出せばよいので、SUM関数を使用します。おそらくほとんどの方には説明不要だと思います。
なお、クエリの応答が一つもないとエラーになってしまうため、iferror関数を使用し、何も支払いが無かった月や未来の月は0になるようにします。
これらを組み合わせた下記がG3セルの内容になります。必要な箇所には$を入れているので、この関数を全費目、全月にコピーすれば、1年分の値を計算することができます。
=iferror(sum(filter(indirect(Calc!$E$10),indirect(Calc!$D$10)=$E3,indirect(Calc!$B$10)>=date(Calc!$A$3,G$1,"01"),indirect(Calc!$B$10)<EOMONTH(date(Calc!$A$3,G$1,"01"),0))),0)以上で、毎月の支払いを集計するための作り込みができました。
テスト
フォームからテスト用に支払いを入力して、期待通りの動きをしているか確認します。
例えば、下記のように費目や月、支払い方法をバラバラにして入力します。
集計シートで期待どおり分類されていればOKです。
振替データを作成する
これまでで、家計としての登録部分はできたので、次に立て替えた人に家計口座から振替えるための計算を行います。この部分は家計専用のお財布が完全に分かれていれば不要かもしれません。
我が家の支出は基本的にお互いの個人の財布から出したお金を精算して家計口座から振り込むという、「会社で交通費を立て替えて払った後、精算処理をして後日振り込んでもらう」ことにヒントを得て、同じことをしています。
そのため我が家では、「この家計簿に入力しないとお金が返ってこないのでちゃんと入力する」→家計簿がしっかり機能するという運用になっています。
ガワは下記のような感じです。
それぞれの列は下記としています（上にある費目の集計と月の列を合わせるために、D列から始めています）
- D列：振替を行う口座です。空欄に見えるセルも、白色文字で口座の名前を入れています
- E列：支払元の人を記載しています。
- F列：D列以降の見出しです。なお、1行目については各口座の「口座」を消した名前にしています。
- G列1行目：合計値を検索するデータです。下記で説明します。
- G列2,3行目：銀行口座の振替を行ったときのエビデンスとして金額と日付を手打ちします。もし、金額がずれていたら、振り込んだ金額と家計簿が一致していないことが簡単に分かります。
合計値の算出
支払いデータを算出したのとほぼ同じ関数を利用して、データを集計します。
=iferror(sum(filter(indirect(Calc!$E$10),left(indirect(Calc!$D$10),2)=$F41,indirect(Calc!$C$10)=$E41,indirect(Calc!$B$10)>=date(Calc!$A$3,G$1,"01"),indirect(Calc!$B$10)<EOMONTH(date(Calc!$A$3,G$1,"01"),0))),0)クエリとしてフィルタリングする条件は、下記になります。
- 支払金額を抽出する
- 費目の名前が該当する口座の名前で始まる
- 支払元の名前が一致する
- 支払日が該当月の初日以降
- 支払日が該当月の最終日以前
支払金額を抽出する
ここは、費目で抽出した支払金額と同じです
indirect(Calc!$E$10)費目の名前が該当する口座の名前で始まる
費目名は内部計算用のシートのCalc!$D$10を使用します。
ここで、入力データ名をわざわざ”口座種類”_”費目”と結合していたことが活きてきます。
費目に該当する口座名の2文字を費目名から抽出するためにleft関数を使用します。
費目の先頭2文字をF列の1行目の値と一致しているかを条件にすればよいので、下記となります。
left(indirect(Calc!$D$10),2)=$F41支払元の名前が一致する
支払元は内部計算用のシートのCalc!C10を使用します。
支払元はE列にあるので、下記を条件とします。
indirect(Calc!$C$10)=$E41支払日が該当月の初日以降
費目のときと同じ条件です。
indirect(Calc!$B$10)>=date(Calc!$A$3,G$1,"01")支払日が該当月の最終日以前
費目のときと同じ条件です。
indirect(Calc!$B$10)<EOMONTH(date(Calc!$A$3,G$1,"01"),0)以上で条件が揃い、クエリは下記となります。
=iferror(sum(filter(indirect(Calc!$E$10),left(indirect(Calc!$D$10),2)=$F41,indirect(Calc!$C$10)=$E41,indirect(Calc!$B$10)>=date(Calc!$A$3,G$1,"01"),indirect(Calc!$B$10)<EOMONTH(date(Calc!$A$3,G$1,"01"),0))),0)まとめ
これで、家計簿としての支出のデータ化と、支払った金額を家計から個人の口座へ振り込むためのデータ作りができました。
家計を運用していくには、ここまでのデータで十分だと思います。
我が家の家計簿には、他のデータをみる機能もあるので、3回目以降の記事でご紹介したいと思います。
次の記事では、クレジットカードの明細をこの家計簿に自動で取り込む方法をご紹介します。
コメント