Excel新関数GROUPBYでテキストを並べる

2024年10月12日 13:40

9月に一般公開されたExcelの新関数 GROUPBY を試してみました。その感想を共有します。（対象は、Microsoft 365 enterprise/bussinessの最新チャネル、バージョン2409です。）

・・・引数が8つもありまして、複雑にしようと思えばいくらでも出来ますが、まずは一番シンプルなものから。

練習用にダミーのデータテーブル TestTableを用意しました。部門、地域、評価額という列があります。

この評価額合計を、部門でグループ化します。

=GROUPBY(TestTable[部門],TestTable[評価額],SUM)

必須の引数は行、値、集計方法の3つ

この1セルで、スピルしてくれて完成です。ついでに合計行も付けてくれます。

手軽で良いですね。グループ化集計のために、わざわざピボットを組んで、それを値貼りつけして、、、みたいなことをしてるのをたまに見かけます。それを考えると、結構使えるかもしれません。

範囲指定をテーブルでやっているので、テーブル範囲が伸縮すれば、計算結果に即座に反映されてくれるのも良い点です。

ただし、古いバージョンと互換性が無いので、しばらくの間は内輪でしか使えないですけど。

さらに、ここから一歩進んで、オプションの引数等を駆使すると、見出し行を付けたり、フィルターしたり、合計行の表示位置を変えたり、複数列でグループ化したり、複数の集計値を算出したり・・・といろいろ出来ることがわかったんですが・・・、かなりわかりにくいです。ちょっと期待してたのと違ったな、と感じました。

それはともかく、第3引数の集計方法（関数）には、 ARRAYTOTEXT という選択肢があったので、試してみました。式はこちら👇。

=GROUPBY(TestTable[部門],TestTable[地域],ARRAYTOTEXT)

地域列の配列をテキスト文字列にする

その結果はこちら👇。。。単純に値をカンマ区切りでくっつけただけですね。これでも使える局面はありそうですが、今回は重複を省きたいです。

重複排除はどうにもならんのか、、、と悩んでいたところ、Microsoft MVPのWyn Hopkinsさんが、やり方をYouTubeで紹介していました。（いつもありがとうございます。）

式は下にある通りです。このように、集計方法にLAMBDAを使います。P_Textは任意のテキストパラメータです。

俄然難しくなりましたが、簡単に言うと、地域列のユニーク値をソートして、カンマで結合しています。

=GROUPBY(TestTable[部門],TestTable[地域],LAMBDA(P_Text, TEXTJOIN(" ,",TRUE,SORT(UNIQUE(P_Text)))))

いきなり難しくなるが第3引数にLAMBDAが使える

結果は、この通りです👇。重複が排除されていますね。

これと同じことを、既存の他のやり方でやろうとすると、Power QueryかPower PivotのDAXあたりになるでしょうか。いずれにしても、やや難解なので、とっつきにくさは同程度かもしれません。

最後に、これに評価額の合計も加え、見出しも表示させてみます。
式はこうなります👇。CHOOSECOLとHSTACKが追加されています。

=GROUPBY(CHOOSECOLS(TestTable[#すべて],2),CHOOSECOLS(TestTable[#すべて],4,9),HSTACK(LAMBDA(P_Text,TEXTJOIN(" ,",TRUE,SORT(UNIQUE(P_Text)))),SUM),3)

もう何が何やらわかりません・・・AIに聞くと良いと思います。

で、結果はこうなります👇。

・・・ここまで行くとやり過ぎなので、避けた方が良い気もしてきました。
（今回の内容は、詳細な解説を載せているこちら👇も参考にさせていただきました。）
Excel GROUPBY function to group rows and aggregate values (ablebits.com)

以上、いろいろ試して分かったことは、シンプルなGROUPBYは使えそうだが、欲張ると途端に複雑化する、ということでした。

ちなみにセットで登場した新関数 PIVOTBY は、とりあえずピボットテーブルで良い気がしていて、紹介を省略します。

今回の記事は以上です。
気に入った方は、スキ・フォローお願いします！