GoogleAppsScript

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

 

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”振替項目がゼロであるデータを抽出します。
RELATED POST