Excel ピボットテーブルで中央値を計算（の代替方法。アドインなし）
ピボットテーブルは中央値に対応していません。
この記事ではアドインやマクロを使わず数式のみで同様のことを実現する方法を紹介します。
手順
ピボットテーブルは多くの場合、年月のように1つ以上の基準を軸に集計したい時です。
下のようなシートで、A列の年とB列の月を軸にする例で紹介します。
集計用のシートの一番上に項目行を作ります（項目行は必須ではありません）
A2セルにUNIQUE関数を設定します。
引数は軸となる年月の列、「データ」シートのA:B列です。
=UNIQUE(データ!A:B)
UNIQUE関数の結果の一番上が項目行、一番下が0だけの行になるため、DROP関数を重ねて削除します。
もし「データ」シートに項目行がない場合は、末尾を削除する-1のDROP関数だけが必要です。
=DROP(DROP(UNIQUE(データ!A:B),1),-1)
数式はA2セルだけですが、スピルになっており「データ」シートに2023年2月のような新しい値が追加されると自動的に反映されます。
SUMやAVERAGEであればIFS系の関数を使えば構いませんが、MEDIANには存在しません。
替わりにC2セルに次のBYROW関数、LAMBDA関数、MEDIAN関数、CHOOSECOLS関数、FILTER関数を利用した次の数式を設定します。
=BYROW(A2#,LAMBDA(r,
MEDIAN(CHOOSECOLS(FILTER(データ!A:C,
(データ!A:A=CHOOSECOLS(r,1)) *
(データ!B:B=CHOOSECOLS(r,2)))
,3))))
この数式により各年月の中央値が算出され、「データ」シートに2023年2月のような新しい値が追加されると自動的に反映される状態になります。
変更例
中央値以外を算出
他の集計関数をすれば中央値以外になります。IFSが存在する集計関数やピボットテーブルに存在しないものではメリットがありません。メリットがあるものは最頻値のMODE.SNGLなどが該当します。
=BYROW(A2#,LAMBDA(r, MODE.SNGL(CHOOSECOLS(FILTER(データ!A:C, (データ!A:A=CHOOSECOLS(r,1)) * (データ!B:B=CHOOSECOLS(r,2))) ,3))))
軸の数が1つ
関連する列が減る分、数式が減ります。
=BYROW(A2#,LAMBDA(r,
MEDIAN(CHOOSECOLS(FILTER(データ!A:C,
(データ!A:A=CHOOSECOLS(r,1)))
,3))))
下は軸が2つの数式です。（変わった箇所を赤字）
=BYROW(A2#,LAMBDA(r,
MEDIAN(CHOOSECOLS(FILTER(データ!A:C,
(データ!A:A=CHOOSECOLS(r,1)) *
(データ!B:B=CHOOSECOLS(r,2)))
,3))))
データシートに年しかない場合は数式が替わります。
=BYROW(A2#,LAMBDA(r, MEDIAN(CHOOSECOLS(FILTER(データ!A:B, (データ!A:A=CHOOSECOLS(r,1))) ,2))))
最後の数値はFILTER関数で指定したセル範囲の中で中央値を集計する列です。（一番左が1の連番）
軸の数が3つ
「データ」シートに列を増やしています。
数式はこうなります。最後の数字が3から4に変わっていますが、これは「データ」シートの中で集計対象が何列目にあるかの指定です。種類列を増やしたために個数が1つ右に行ったため、1つ増やしています。
=BYROW(A2#,LAMBDA(r,
MEDIAN(
CHOOSECOLS(FILTER(データ!A:D,
(データ!A:A=CHOOSECOLS(r,1))*
(データ!B:B=CHOOSECOLS(r,2))*
(データ!C:C=CHOOSECOLS(r,3))),
4))))
Excelを効率的に習得したい方へ
当サイトの情報を電子書籍用に読み易く整理したコンテンツを
買い切り950円またはKindle Unlimited (読み放題) で提供中です。
Word-A4サイズ：1,400ページの情報量で
（実際のページ数はデバイスで変わります）
基本的な使い方、関数の解説(140種類)、
頻出テクニックと実用例(109種類)、
XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。
体系的に学びたい方は是非ご検討ください。
アップデートなどの更新事項があれば随時反映しています。
なお購入後に最新版をダウンロードするには
Amazonへの問い合わせが必要です。