こんにちは、はな夫（@logicalifer）です。
今回は、前回Googleスプレッドシートで作成した「家計簿2021」という家計簿ファイルの続きを設定していきます。
また、当初の第2回としてアップした下記の記事の家計簿に改良の余地があったため、その部分を修正した内容をご紹介しています。
元記事は下記です。
元記事では、filter関数を利用して、支払いの集計をしていましたが、query関数を利用することで、シンプルに記載できて、内部計算用のシートも不要になりました。
また、本記事は第1回からの続きになっているため、第1回の記事をご覧になっていない方は、下記の記事をご参照ください。
費目の集計をする
各月の各費目で使用した金額を合算していきます。
=iferror(query(Form!$A:$H,"select sum(E) where D like '"&$E3&"' and MONTH(B)+1 ="&text(date($A$1,G$1,"01"),"M")&" LABEL SUM(E) ''",1),0)長くてよくわからないですね。
分解して一つずつご説明いたします。
query関数
query関数の仕様は下記に紹介されています。
。。。何を言っているのか難しいですが、集計するデータ群を1つ目に、2つ目にそのデータ群の抽出の仕方を指定する関数です。
この関数を利用するために、抽出条件を定義すると、下記の4つになります。
- 対象となるデータ群を指定する
- 支払金額の合計を算出する
- 費目がE列の名前と一致する
- 支払日が該当月と一致する
以降でそれぞれを考えていきます。
対象となるデータ郡を指定する
今回対象となるデータ郡は入力フォームから入力されたデータのため、下記になります。
Form!$A:$Hここはそんなに難しくないですね。
支払金額の合計を算出する
query関数では、sumと指定することで、条件に該当する値の合計値を出してくれます。
支払金額はFormシートのE列にあるため、これを合計します。
select sum(E)費目がE列の名前と一致する
上記だけではFormシートの全てが合計されてしまうため、条件を追加します。
最初に費目の名前が各費目名と一致しているということを条件にします。
where D like '"&$E3&"'FormシートのD列（費目名として入力された値）が、集計シートのE列の費目名と一致していることを条件にしています。
query文の中身は””で文字列として記述しているため、クエリとして文字列を表したいときは”で囲む必要があります。
さらにセルを参照する必要があるため、'”&セル&”‘という囲み方になります。
支払日が該当月と一致する
上記までで費目の分類ができたので、次に各月に該当するという条件を追加します。
下記の部分になります。
MONTH(B)+1 ="&text(date($A$1,G$1,"01"),"M")&"FormシートのB列（日付として入力された値）の「月」の部分が、集計シートの「月」の部分と一致しているかを確認しています。
MONTH関数の仕様は下記に記載されています。
試してみると分かるのですが、1月＝0、2月＝1となっていくようなので、MONTH(B)+1という記述をしています。
比較対象となっているのは、text関数とdate関数を組み合わせることで、家計簿で該当する月を選んでいます。
この2つが＝となったときの値を抽出します。
見出しを消す
query関数の出力結果は、見出し列を含めたものが表示されます。
今回の家計簿の集計では不要なので、ラベルを消すような指定を追加します。
それが下記の部分になります。
LABEL SUM(E) ''"selectの後に指定しているSUM(E)について、ラベルを表示しないことを意味しています。
テスト
フォームからテスト用に支払いを入力して、期待通りの動きをしているか確認します。
例えば、下記のように費目や月、支払い方法をバラバラにして入力します。
集計シートで期待どおり分類されていればOKです。
振替データを作成する
これまでで、家計としての登録部分はできたので、次に立て替えた人に家計口座から振替えるための計算を行います。この部分は家計専用のお財布が完全に分かれていれば不要かもしれません。
我が家の支出は基本的にお互いの個人の財布から出したお金を精算して家計口座から振り込むという、「会社で交通費を立て替えて払った後、精算処理をして後日振り込んでもらう」ことにヒントを得て、同じことをしています。
そのため我が家では、「この家計簿に入力しないとお金が返ってこないのでちゃんと入力する」→家計簿がしっかり機能するという運用になっています。
ガワは下記のような感じです。
それぞれの列は下記としています（上にある費目の集計と月の列を合わせるために、D列から始めています）
- E列：振替を行う口座です。%はquery関数のワイルドカードになります。
- F列：支払元の人を記載しています。
- G列1行目：合計値を検索するデータです。下記で説明します。
- G列2,3行目：銀行口座の振替を行ったときのエビデンスとして金額と日付を手打ちします。もし、金額がずれていたら、振り込んだ金額と家計簿が一致していないことが簡単に分かります。
合計値の算出
支払いデータを算出したのとほぼ同じ関数を利用して、データを集計します。
- 対象となるデータ群を指定する
- 支払金額の合計を算出する
- 費目がE列の名前と一致する
- 支払った人が振替先と一致する
- 支払日が該当月と一致する
=iferror(query(Form!$A:$H,"select sum(E) where D like '"&$E40&"' and C like '"&$F40&"' and MONTH(B)+1 ="&text(date($A$1,G$1,"01"),"M")&" LABEL SUM(E) ''",1),0)対象となるデータ郡を指定する
ここは、費目で抽出した支払金額と同じです。
Form!$A:$H支払金額の合計を算出する
ここは、費目で抽出した支払金額と同じです。
select sum(E)費目がE列の名前と一致する
ここは、費目で抽出した支払金額と同じ指定をします。
E列が口座名_%というワイルドカードを使った表記にしているため、該当する銀行口座の支払いが全て抽出されるようになります。
where D like '"&$E40&"'支払った人が振替先と一致する
ここで、FormシートのC列（支払元）が、該当する振替先に一致するかを確認します。
下記のように指定します。
C like '"&$F40&"'支払日が該当月と一致する
ここは、費目で抽出した支払金額と同じです。
見出しを消す
ここは、費目で抽出した支払金額と同じです。
まとめ
これで、第2回でご紹介した家計簿と同じことを実現できました。第2回でご紹介した方法と比べると、内容がシンプルで何を目指しているのか分かりやすい記述になったと思います。
ただ、query関数自体はスプレッドシートやエクセルのお作法と比べるとかなり異質な存在なので、使いこなすのが大変です。これを実現するためにかなりハマりながらできたので、まだまだ勉強が必要そうです。。。
次の記事では、クレジットカードの明細をこの家計簿に自動で取り込む方法をご紹介します。
コメント