寒い日の夜は、膝関節が成長期。

朝散歩して、パン買って、少し内職して、ゆっくり過ごす日々

マネーフォワードから折半分を計算シート [Googleスプレッドシート]

GoogleAppsScript 暮らし

f:id:airwho:20180611155438p:plain

マネーフォワードで家計集計を行っております。

共有物は折半し、使用口座は個人と家族ごちゃまぜにしております。

そんな折半シートを公開しました。 docs.google.com

月末にすることは

  • マネーフォワードから家計データをエクスポート→シートにコピペ
  • 前月から集計式をコピペして、集計月の項目を増やす 以上です

マネーフォワードのエクスポート

下記のようなデータです f:id:airwho:20180611155454p:plain - A列：対象項目なら1のため、集計対象です。 - B列：日付は集計範囲内なら集計対象です。 - C列：内容は今回の集計では見ていません。 - D列：金額（円） - E列：保有金融機関は、ぱぱ口座か、まま口座か判定に使う項目です。 - F列：大項目は、集計対象か判定に使う項目です。 - G列：中項目も、集計対象か判定に使う項目です。 - H列：メモは今回の集計では見ていません。 - I列：振替項目なら1のため、集計の対象外です。

*** 解説 E8の「ぱぱ」食費の式の解説をします。 =abs(sum(query('こぴぺ'!$A$1:$I$140,"select D Where A=1 and ( B >= date '"&TEXT($A8,"yyyy-mm-dd")&"' and B <= date '"& TEXT($B8,"yyyy-mm-dd")&"' ) and E like '%"&$E$3&"%' and ( (F='"&$E$12&"' and G='"&$F$12&"') OR ( F='"&$E$13&"' and G='"&$F$13&"')) and I=0")))

  • ABS：絶対値：マイナスの値も＋表示されます。
  • SUM：合計：データの中で当てはまった金額を合計しています。
  • QUERY：Googleスプレッドシートで使えるクエリー宣言です。 　query(対象範囲, "式")
  • "select D Where .... "：Where後ろの条件に合うデータの"D"金額値を出力
  • "A=1"：データの抽出条件です。"A"対象項目が1であるデータを抽出する。
  • and ( B >= date '"&TEXT($A8,"yyyy-mm-dd")&"' and B <= date '"& TEXT($B8,"yyyy-mm-dd")&"' ) 　："A8"集計開始日〜"B8"集計終了日範囲のデータを抽出する。
  • and E like '%"&$E$3&"%' ：見やすくすると「and E like '%ぱぱ%' 」です。 　"E"保有金融機関が「ぱぱ」文字が含まれているデータを抽出する。
  • and ( (F='"&$E$12&"' and G='"&$F$12&"') OR ( F='"&$E$13&"' and G='"&$F$13&"')) 　食費の対象を品目です。 f:id:airwho:20180611181357p:plain:h300 　OR「または」でつなげて、いずれかに当てはまる品目のデータを抽出します。
  • and I=0 　"I"振替項目がゼロであるデータを抽出します。