GROUPBY関数で複数の集計をする
GROUPBY関数って、メチャクチャ便利です。
集計の方法も、合計(SUM)だけではありません。
ほかにも、下図のような集計が可能です。
LAMBDA関数も使えるので、がんばれば何でもできそうです。GROUPBY関数の詳しい解説は、下記のページをご覧ください。
さまざまな集計ができるのですが、では複数の集計を行うには、どうしたらいいでしょう。たとえば、合計(SUM)と平均(AVERAGE)とか。まずは、次のようにしてみました。
計算方法を指定する引数に、"SUM"と"AVERAGE"を配列で指定しました。結果は、数式として認識されません。スピル系の関数を試していると、よくこのエラーが表示されるのですが、地味に凹みます。だったら！ということで、HSTACK関数を使ってみました。
お、おぉ...ちょっと、ツッコミどころはありますけど、とりあえずできました。なぜか1行目に関数名が表示されていますね。とりあえずDROP関数で削除しましょうか。
できたっちゃ、できたんですけど、なんかこう、納得できないです。
SUMってなんだ？
そもそも、初めてGROUPBY関数を触ったときから違和感がありました。複数の計算を行える関数は以前から存在します。たとえばSUBTOTAL関数やAGGREGATE関数です。
両者はいずれも、使用する関数を番号で指定します。SUM関数で合計を求めたいなら「9」です。しかし、GROUPBY関数では「SUM」のように関数名を記述します。しかも文字列形式の「"SUM"」ではなく、ただの「SUM」です。なぜでしょう？理由はおそらくLAMBDA関数です。SUM関数やAVERAGE関数は引数を渡すだけで計算できます。でも、LAMBDA関数は"数式"も指定しなければ動作しません。
だから、集計方法の指定に「9」などの番号や「"SUM"」などの文字列形式は指定できないんです。だって「"LAMBDA"」では数式の記述ができませんから。そして、GROUPBY関数はLAMBDA関数に引数を渡します。そうでなければLAMBDA関数が機能しません。もし新しくGROUPBY関数を作るなら、LAMBDA関数と他のSUM関数などで、異なる動作を別々にプログラミングするのは非効率的です。天下のMicrosoftが、そんな発想をするとは考えにくいです。であるなら、GROUPBY関数の集計方法に「SUM」を指定したときって、同じように引数を渡しているだけなのでは？同じ仕組みで動いているのでは？そう考える方が自然です。
GROUPBY関数の集計方法に、従来のような番号による指定や「"SUM"」みたいな文字列形式での指定ではなく、ただ「SUM」とだけ指定するのって、もしかしてこの「SUM」は"SUM"という"値"ではなく、SUM関数そのものへの参照ではないでしょうか。ちょっと、文字で表現するのは難しいのですが、そんなイメージを抱きましたので、次のような実験をしてみました。
驚きです。こんな動作は初めて見ました。もちろん、古いExcelでは動きません。
いつから仕様が変更されたかは分かりません。たぶんですけど、GROUPBY関数が追加されたタイミングではないでしょうか。いずれにしても、こう考えると、GROUPBY関数の集計方法に「SUM」みたく指定するのも、上述したように、なぜHSTACK関数で結合したとき、1行目に変なゴミ(関数名)が表示されたのかも納得できます。驚きの仕様変更ですが、正直言って、あまり便利な用例は思いつきません。たとえば、下のような使い方くらいかな。
全部「合計」ってのは変じゃね？
さて、話を戻しましょう。いずれにしても、GROUPBY関数で複数の集計を行うには、HSTACK関数を使えば可能です。でも、そうなると「合計」は、どうなるのでしょう。やってみます。
セルE5はいいです。E列で集計された 167+171+157=495 ですから、まさに"合計"です。しかし、F列の平均はどうでしょう。セルF5の 82.5 というのは、(83.5+85.5+78.5)/3=82.5 つまり、平均の平均です。これを"合計"と言ってよいのかと。あ、でも 495/6=82.5 だから、合計の平均ってことで"合計"でもいいのか。う～ん、セルD5の"合計"という表記は、ちょっと美しくないような。手作業で同じ表を作るとしたら、ここに"合計"とは書かないような。意味としては合ってるけど、でも…。
まぁ、たいした話ではありませんので、気にならない方は、ここまでで終えてください。ただ、細かいことが気になる私は、つい「この"合計"っての、別の表記にできないかな？何なら空欄にできないかな？何か手はないかな？」って思ったので、そのやり方を考えてみました。ここからは、式も長くなりますし、難しくなりますので、あくまでオマケということで。
GROUPBY関数の結果のうち、ひとつの値(ここでは左下の"合計")だけを、別の文字列に置換してみます。深い意味はありませんけど、今回は空欄("")に置換します。文字列の中で、任意の文字を何かの文字に置換するにはSUBSTITUTE関数を使います。「SUBSTITUTE(文字列,"A","B")」で、"A"が"B"に置換されます。さて、GROUPBY関数の結果から、左下セル(というか、正しくは値)だけを抽出するには、いくつかの方法がありますけど、今回はTAKE関数でやってみましょう。
これを空欄("")に置換すると、
ああ、やっぱそうなりますよね。欲しいのは、GROUPBY関数の結果全部です。しかたありません。ピンポイントで左下の値だけ置換するのは諦めます。できるかもしれませんけど、これ以上の無理はしたくないです。大雑把ですけど「GROUPBY関数の結果全部から、"合計"を空欄("")に置換する」方針で行きます。元データの中に"合計さん"という名字は存在しないことを祈りましょう。
とりあえずできましたけど、2列目と3列目の数値が文字列になってしまいました。SUBSTITUTE関数は文字列を返すからです。文字列形式の"167"などを数値に変換するには、＊1をしてやります。
まぁ、そうなりますよね。文字列は＊1できませんから。こうなったら最後の手段です。GROUPBY関数の結果全部から、値をひとつずつ取り出して処理します。もし＊1してエラーになったら、その値は文字列なのでそのままとします。GROUPBY関数の結果は配列ですが、こんなときもMAP関数を使えます。
上図は確認です。LAMBDA関数では、何の処理もしていません。ここまでくれば、ゴールは目前ですね。次のようにすれば完成です。
上図の「D2#」を、最初の
DROP(GROUPBY(Data[名前],Data[数値],HSTACK(SUM,AVERAGE)),1)
とすれば、1つの式だけで実現できます。