Excel ピボットテーブルで中央値を計算（の代替方法。アドインなし）

2023年2月3日2026年1月31日

1. はじめに
- 1.1. 各手順の比較表
- 1.2. 概要解説動画
2. 手順(GROUPBY関数やPIVOTBY関数で対応)
- 2.1. 縦方向に集計する（GROUPBY関数）
- 2.2. クロス集計を行う（PIVOTBY関数）
3. 手順(GROUPBY関数やPIVOTBY関数が利用できない場合)
- 3.1. 変更例
4. Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド

はじめに

Excelのピボットテーブルは、便利な集計ツールですが、標準機能では「中央値」の算出に対応していません。

しかし、最新のExcel（Microsoft 365やExcel 2024以降）であれば、新しく追加された集計関数の活用により、ピボットテーブル以上に素早く、かつ正確に中央値を算出できるようになりました。

この記事では、最新関数を使った「最短の解決策」から、旧バージョンのExcelでも動作する「汎用的な数式」まで、アドインやマクロを一切使わずに実現する手順を具体的に解説します。ご自身の環境や用途に合わせた最適な方法を見つける一助となれば幸いです。

各手順の比較表

項目	ピボットテーブル	GROUPBY / PIVOTBY	代替数式 (BYROWなど）
中央値の集計	非対応	標準対応	対応可能
列指定の柔軟さ	可能だが空白行の除外が困難	TRIMRANGEで解決可能	制御可能だが数式が複雑化
推奨用途	手作業での詳細・多角的な分析	定型フォームでの自動集計	旧環境での自動集計
難易度	低（マウス操作）	中（数式1つ）	高（複雑）
データの追加	再読み込みが必要	自動反映	自動反映

概要解説動画

手順(GROUPBY関数やPIVOTBY関数で対応)

2024年に追加されたGROUPBY関数やPIVOTBY関数はピボットテーブルと同様の働きをする関数ですが、集計方法に中央値が存在します。

集計の軸が1つの場合はGROUPBY関数、2つの場合はやPIVOTBY関数を使用します。

GROUPBY関数やPIVOTBY関数が使用できる環境で、頻繁に集計軸を変更しない用途の場合、ピボットテーブルよりもこちらを推奨します。

縦方向に集計する（GROUPBY関数）

特定の軸（例：年と月）ごとに中央値を算出したい場合は、GROUPBY関数を利用します。

第1引数（軸）: 集計の基準となる列を指定します。
第2引数（値）: 中央値を計算したい数値列を指定します。
第3引数（関数）: MEDIAN（中央値）を指定します。
第4引数（見出し）:3 を指定すると、元の表の見出しを自動で表示します。

数式の例（年集計）：

=GROUPBY(A1:A8, C1:C8, MEDIAN, 3)

数式の例（年月集計）：

=GROUPBY(A1:B8, C1:C8, MEDIAN, 3)

一つのセルに数式を入力するとスピルで自動拡張されます。

もしデータの追加が予想される場合、列指定をしたいですが、そのまま行うと集計結果に空白の行が出来てしまいます。計算速度にも影響が出てしまう場合もあります。

列指定の柔軟さと集計範囲を有効なものだけに制限を両立したい場合、Microsoft 365のTRIMRANGE 関数が有効です。利用するにはデータの列指定をTRIMRANGE 関数で加工します。

=GROUPBY(TRIMRANGE(A:B), TRIMRANGE(C:C), MEDIAN,3)

これにより列指定をしたまま、無効な行を集計範囲に加えることがなくなります。

クロス集計を行う（PIVOTBY関数）

例えば「年」を横軸、「月」を縦軸にクロス集計を、PIVOTBY関数を利用します。

数式の例（年月クロス集計）：

=PIVOTBY(B1:B8, A1:A8, C1:C8, MEDIAN)

列指定の柔軟さと集計範囲を有効なものだけに制限を両立したい場合、GROUPBY関数と同様に、Microsoft 365のTRIMRANGE 関数が有効です。

=PIVOTBY(TRIMRANGE(B:B), TRIMRANGE(A:A), TRIMRANGE(C:C), MEDIAN)

手順(GROUPBY関数やPIVOTBY関数が利用できない場合)

ピボットテーブルは多くの場合、年月のように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 .S N G Lなどが該当します。

=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))))