３つの関数を使って表をまとめる
テスト用スプレッドシートのリンク
UNIQUE関数テスト
UNIQUE,FILTER,SORT ①売上表,②品名まとめ,UNIQUE関数で品名の重複するものをまとめる。 さらにSUMIFで売上個数、売上金額の合計を出す。 それを売上金額（合計）でソートする。 SEQUENCEで並び順を追加。,③合...
docs.google.com
UNIQUE関数,FILTER関数,SORT関数で表のまとめを作れます
この売上表のまとめを作ります
完成図
1. ユニークテーブルを作る
UNIQUE関数で品名の重複するものをまとめる。
さらにSUMIFで売上個数、売上金額の合計を出す。
それを売上金額（合計）でソートする。
SEQUENCEで並び順を追加。
=LET( uniq, UNIQUE(D4:D11),
sums,ARRAYFORMULA(SUMIF(D4:D11,uniq,E4:E11)),
sums2, ARRAYFORMULA(SUMIF(D4:D11,uniq,F4:F11)),
row_cnt, ROWS(uniq),
labels, MAKEARRAY(row_cnt,1,LAMBDA(row,col,"合計")),
uniq_tbl,{uniq,sums,sums2, labels},
{SORT(uniq_tbl,3,FALSE ),SEQUENCE(row_cnt,1,10,10)})
2. 元のテーブルとユニークテーブルをまとめてソートする
元のテーブルとユニークテーブルを合体する。{}
ユニークテーブルに対しては品名がISBLANKのものは無視する。(FILTER関数)
元のテーブルに対してはVLOOKUPでユニークテーブルから順位を取得する。
順位と名前でソートする。
=LET(arr, {D4:G11,ARRAYFORMULA(VLOOKUP(D4:D11,I4:M11,5,FALSE)+$B$3);I4:M11},
sorted,SORT(FILTER(arr,INDEX(arr,0,1)<>""), 5, TRUE, 4,TRUE),sorted)
意外と簡単でしょう？
3. もう少し整形してみる
品名は合計のみに表示
ソート用の列を無くしておく
=LET(arr, {MAKEARRAY(ROWS(D4:D11),1,LAMBDA(row,col,"")),E4:G11,ARRAYFORMULA(VLOOKUP(D4:D11,I4:M11,5,FALSE)+$B$3);I4:M11},
sorted,SORT(FILTER(arr,NOT(ISBLANK(INDEX(arr,0,1)))), 5, TRUE, 4,TRUE),
cols, {1,2,3,4},
res_2, CHOOSECOLS(sorted,cols),
res_2
)おまけ(CHOOSECOLS関数)
ある範囲から、複数の列を抜き出したいときにCHOOSECOLS関数を使うと便利
{INDEX(TBL,0,1),INDEX(TBL,0,2)}みたいなことをしてました <- やめましょう
CHOOSECOLS(TBL,{1,2}) <- これでOK
LETでTBLを変数としていても、何回もINDEXを書くと読みづらい（２列ならまあいいですが、10列とかになるとしんどい）
OFFSET関数もありますが、OFFSET関数だと行数を指定するのが面倒
FILTERとかSORTも{1,2}みたいに渡せると楽だと思いました
参考までに違う書き方との比較
ARRAYFORMULAだとできない
MAPだとできる
どう見てもCHOOSECOLSが簡単です
まとめ
表のまとめを作成するためのコードとその実装結果を載せてみました。
実際の業務だとピボットテーブルとかにする方が多いのでしょうか？
コメント