PIVOTBY関数の結果を並べ替える

  1. Home
  2. Excel
  3. Excel Tips

PIVOTBY関数は、簡単に言うと"ピボットテーブルを作ってくれる関数"です。

PIVOTBY関数には、とても多くの(なんと11個！)引数が用意されているのですが、その中に「row_sort_order」と「col_sort_order」というのがあります。前者は「行を並べ替える位置」で、後者は「列を並べ替える位置」を数値で指定します。「1」など正の数値を指定すると昇順で、「-1」のように負の数値を指定すると降順に並べ替わります。ちょっと、やってみましょう。

上図のように、行フィールドも列フィールドも、標準では昇順で並べ替えられます。たとえば「row_sort_order」に「-1」を指定すると

「col_sort_order」に「-1」を指定すると、次のようになります。

まぁ、意味は分かるのですが、結局これって"項目名"を並べ替えるんですよね。「A-101→A-102→A-103」や「A-103→A-102→A-101」みたいに。う～ん、イマイチ便利さを感じません。それより、並べ替えだったら何と言っても"数値"を並べ替えたいですよね。たとえば、冒頭の結果を「合計の値を昇順」で並べ替えるとか。PIVOTBY関数の引数に、そんな便利なのはありませんし、これ以上引数が増えるのは嫌です。なのでSORT関数を使ってみましょう。

ああ、やっぱりね。そんなことだろうとは予想していましたとも。何とか、データ部分だけを並べ替えてみましょう。いや、意外と簡単です。まず、PIVOTBY関数の結果を、3つのパーツに分解します。

並べ替えたいのは「Value」部分です。並べ替えても「Header」と「Total」は変化しません。まずは、そこまでやってみます。

ちゃんと、4列目が昇順で並べ替わっていますね。並べ替えた結果の上下に、先の「Header」と「Total」を結合します。

もし、列フィールド(現在は"A"と"B")が増減しても、常に右端列で並べ替えるのなら、次のような感じですかね。

=LET(A,PIVOTBY(Data[コード],Data[記号],Data[数値],SUM),
     Header,TAKE(A,1),
     Value,DROP(DROP(A,1),-1),
     Total,TAKE(A,-1),
     VSTACK(Header,SORT(Value,COLUMNS(A)),Total))