GROUPBY関数で月別の集計をする
GROUPBY関数って、超便利ですよね。特に、スピル系関数の結果に対して「○○の合計」や「○○の個数」を計算するようなとき、SUMIF関数やCOUNTIF関数を使うと、上手くいかないことが多いです。この件、詳しくは下記のページをご覧ください。
さて、GROUPBY関数は文字列だけでなく、日付をグループ化することも可能です。
では、GROUPBY関数を使って、"月別の合計"を求めるには、どうしたらいいでしょう。
最も重要なこと
まず、第一に考えなければならないのは、各集計結果の"項目名"ですね。まずは、"項目名"を確定してください。話はそれからです。
入力されている(グループ化の対象となる日付)のが、1月～3月など少ない日付だったら、上図のような形式でもいいでしょう。しかし、もし複数年の日付だったら、年も含めて表示したいですよね。それに、もし入力されている日付が、"単年"の、たとえば2025年だけの1月～12月だった場合、次のようになっちゃいます。
GROUPBY関数で集計した結果の"項目名(左端列)"は、自動的に並べ替えられます。"田中"や"東京"などの文字列だったら、並び順も気になりませんが、今回のような"日付"系では、ちゃんと並んでくれないと困ります。なので、まずは"項目名"を決めます。それによって、数式の内容や、そもそもの方針が変わりますし、ケースによっては難易度も上がります。
「m月」パターン(mは1～9)
簡単なケースからいきましょう。日付は単年であり、月も1月から9月までしかありません。これなら確実に並べ替えられます。
まず、日付を「m月」に変換します。これにはTEXT関数を使います。
GROUPBY関数の第一引数「行データ」には、このTEXT関数の結果を指定します。もちろん、図のように、一度TEXT関数をセルに入れて、そのセル範囲を指定してもいいのですが、GROUPBY関数内に直接TEXT関数を記述してもOKです。引数「値」には[数値]列を指定し、計算は"SUM"、合計は表示しない、としました。
「m月」パターン(mは1～12)
まず、上記の方法でやってみましょう。
10月、11月、12月が先頭にきちゃいます。これは、並べ替えの仕様なので仕方ありません。本当を言うと、最も簡単な方法のひとつは、月の表記を「1月・2月・3月…」ではなく「01月・02月・03月…」にすることです。
まぁ、今回は、あえて月の表記を「1月・2月・3月…」にして、でも並び順は「1月・2月・3月…10月・11月・12月」にしてみましょう。この発想ができれば、応用しだいで好きな順に並べ替えられます。
なぜ「10月・11月・12月」が先頭に来るかといえば、理由は"月"という文字が含まれているからです。たとえば「1月」と「12月」を並べ替えると、"1"は同じでも、次の文字が"月"と"2"ですから、並べ替えたら"2"が上にきます。そして、この「1月・2月・3月…」が、一番左の列に存在しています。GROUPBY関数は、1列目が昇順に並べ替えられます。だから、もし下図のように、一番左の列に月を表す「1・2・3…」があったら、「1・2・3…」の順で並べ替えられるはずです。
こうしたHSTACK関数の使い方は、下記のページで解説していますのでご覧ください。
関数の結果に連番の列を追加する
関数の結果に同一値の列を追加する
なお、自分で書いておきながらナニですが、上図の数式は「う～ん、ちょっと正確性に欠けるかなぁ～」って感じています。より正確に処理するのなら、GROUPBY関数の結果から左1列を抽出し、そこから並べ替え基準となる数値などを生成し、その基準で並べ替えた方がいいんだろうなと。そうは思いましたけど、数式全体がメッチャ長くなるので、今回はこのままいきます。「1月・2月・3月…」から「1・2・3…」生成するのでしたら、上図の数式で問題ないかなって。今回のケース限定ってことでお願いします。
さて、まだ処理は終わっていません。結果の左端列「1・2・3…」は、並べ替えのために作ったので、並べ替えが終わったら不要です。なので、DROP関数で削除します。
最近、調子に乗ってコンテンツが長くなる傾向にあります。(ほんのちょっとだけ)反省しています。今回は、これくらいにしておきます。