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

2023年2月3日2024年10月14日

English version.

ピボットテーブルは中央値に対応していません。

この記事ではアドインやマクロを使わず数式のみで同様のことを実現する方法を紹介します。

GROUPBY関数やPIVOTBY関数で対応

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

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

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.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完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド

本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。

【本書の特徴】

• 情報量：Word-A4サイズ換算で1,400ページ相当
• 基本操作から高度なテクニックまで、段階的に学習可能
• 140種類の関数を詳細に解説
• 109種類の実用的なテクニックと具体例を紹介
• 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説

【対象読者】

• Excel初心者からプロフェッショナルまで
• 体系的にExcelスキルを向上させたい方
• 業務効率化を目指すビジネスパーソン
• データ分析や可視化のスキルを磨きたい方

【本書の強み】

• 実務に即した例題と解説
• 視覚的な図表やスクリーンショットで理解を促進
• 最新のExcelバージョンに対応した内容
• 著者の長年の経験に基づく、実践的なTipsを多数収録

【更新とサポート】

• 常に最新の情報を反映するため、定期的に内容を更新
• 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください

【入手方法】

• 買い切り：950円
• Kindle Unlimited：読み放題プランで利用可能

Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。