「月ごと」には出せた
過去に書いた QUERY関数で「月ごと」に集計したい の記事で下記のように「月ごと」に出すことに成功しました。
今回の課題
それはそれとして、新たに下記のような元データ(左の表)の場合、「月ごと」で集計すると右の表のようになります。
D1セルに下記の式が入っています。
=query(A:B,"select month(A)+1, sum(B) where A is not null group by month(A)+1 label month(A)+1 '月', sum(B) '合計'",1)
2020年10月と2021年10月は、どちらも「10月」なので合算されちゃいます。
年を超えた月ごとの合計や平均を調べたいときはこれで問題ないのですが、「年月ごとの推移」を見たいときはこれじゃダメです。
これでどうだろう。
↑ これだと「年月ごと」に集計することができました。やったね。
元データ(A,B列)はそのままで、C列に「年月」を表示する列を追加しました。
(X) C2セルには下記の式が入っています。
TEXT(A2:A,"yyyymm") がポイントで、TEXT関数で年月の情報を表示しています。
=ARRAYFORMULA(If(A2:A<>"",TEXT(A2:A,"yyyymm"),""))
↑ ここまでできちゃえばA,B,C列でやりたいことができます。
(Y) E1セルには下記の式が入っています。
=query(A:C,"select C, sum(B) where A is not null group by C label C '年月', sum(B) '合計'",1)
とはいえ
なんかこう、「C列に追加する」っていうのがイケてない気がするんですよね。
これってQuery関数一発で表示させることができるのでしょうか。
できるよ！という方、ぜひコメントで教えてください！！
一発でできたよ！
投稿直後に データ範囲を読み込むときに年月を連結しちゃう 案をいただきました。
ありがとうございます！！
ARRAY_CONSTRAIN なんて使ったことなかったよ！！
=query(
ARRAY_CONSTRAIN(
ARRAYFORMULA({YEAR(A:A) & RIGHT("0" & MONTH(A:A), 2), B:B}),
COUNTA(A:A),
2
),
"select Col1, sum(Col2)
group by Col1
label Col1 '年月', sum(Col2) '合計'",
1
)
=query(範囲, query文) の構文において、「範囲」の時点でほしいテーブルを生成しておけばいい、ってことか。。。天才かよ。
追記(2022-12-23)
コメント欄にとてもいいアイディアを頂いたので、そのアイディアを使ったやり方を このコメント に投稿しました。