GROUPBY関数の内訳を表示する
GROUPBY関数で下図のような集計をしたとします。
なるほど、田中の合計が最も多く、次が佐倉で、最も少ないのが小原です。これはこれで良いのですけど、じゃぁもし、元データが次のようになっていたらどうでしょう。
小原は1件のデータで168なのに対して、佐倉は5件のデータを合計して174です。こういう状態を、単純に合計だけで判断しても良いのでしょうか。まぁ、良いかもしれませんけどｗ でも、各合計に対する"内訳(詳細)"を見たいときもありますよね。ないかな？今回は、そういうネタなので"ある"ってことにします。
まずは、GROUPBY関数で"合計"と"データの件数"の2つを集計します。GROUPBY関数で複数の結果を表示する方法は、下記のページで詳しく解説していますので、ぜひご覧ください。
式が長くなるので、途中で改行しました。式をコピーする方は、下記をどうぞ。
=DROP(GROUPBY(Data[名前],Data[数値],HSTACK(SUM,COUNTA),,0),1)
これを見ると「むむ、小原は1件なのに、佐倉はずいぶん多いな」と分かります。分かったら、その内訳を知りたいです。そんなときは、GROUPBY関数で指定できるARRAYTOTEXTが便利です。
=DROP(GROUPBY(Data[名前],Data[数値],HSTACK(SUM,COUNTA,ARRAYTOTEXT),,0),1)
今回は、集計した結果が3人と少ないので、パッと見て誰が大きいのか小さいのか、すぐに判断できるでしょう。でもこれが、10人とか20人とかだったら、判断にも困ります。そんなときのために、"合計"や"件数"で並べ替えられるようにしましょう。GROUPBY関数には、指定した列で並べ替えられる引数があります。今さらですけど、GROUPBY関数の詳しいことは下記のページをご覧ください。
実務ではこういうとき、みなさんは、どうしたいんでしょうね。2つ別々の結果を表示したいですか？
上図は画面の都合上、2つの結果を縦に並べましたが、実際にやるときは横に置いてください。項目名が増えたとき、上の結果がスピれなくなります。まさに、ケースバイケースですかね。この結果から何を判断したいのかにもよります。今回は、1つの結果(表)で、並べ替えの基準を切り替えます。
どこか別のセルで、並べ替え基準の位置(2または3)と、昇順/降順(プラスまたはマイナス)を指定します。ここでは、セルD1とセルD2としましょう。簡単に考えたら、セルに"2"とか"-3"などと入力すればいいのですが、それでは分かりにくいです。ここでは、入力規則のリストを使ったやり方をご紹介します。
まず、セルD1に"合計"と"件数"を選択できるリストを設定します。続いて、セルD2には"昇順"と"降順"のリストです。
まず「列」を指定するところを考えます。セルD1が"合計"だったら"2"とし、"件数"だったら"3"にしたいんです。IF関数で強引にやる手もありますが、ここはMATCH関数を使うのがスマートで応用も利きます。
検索する範囲は「E1:G1」でもいいですけど、とりあえずH1までとしました。特に深い意味はありません。次に「(並べ替えの)順序」ですが、もしセルD2が"昇順"だったら正の数にし、"降順"のときは負の数にします。MATCH関数の結果が"2"だったとき、"昇順"だったら"2"のまま、"降順"だったら"-2"にしたいと。これも、いくつかの方法があります。でも、プラスかマイナスの2択ですから、オーソドックスにIF関数を使いましょう。"昇順"だったら、そのまま"2"です。"昇順"ではなかったら、MATCH関数の結果に"-1"を掛け算します。
=IF(D2="昇順",MATCH(D1,E1:H1,0),MATCH(D1,E1:H1,0)*-1)
同じ「MATCH(D1,E1:H1,0)」が2回登場するので、LET関数で「MATCH関数の結果をAとする」にします。
これを、GROUPBY関数の引数「並べ替え」に指定します。
=DROP(GROUPBY(Data[名前],Data[数値],HSTACK(SUM,COUNTA,ARRAYTOTEXT),,0,LET(A,MATCH(D1,E1:H1,0),IF(D2="昇順",A,A*-1))),1)
こんなもんかな。この発想を応用すると、次のような"人員配置"にも使えますね。
