Excelのピボットテーブルで中央値計算をしたかったメモです。

概要

ピボットテーブルを強化できるExcelのアドインを利用します。
なかなか日本語記事見当たらなかったので以下の記事を参考にしています。

Calculating the median in Excel PivotTables

結果として今回調べた方法はWindows版のExcelでは可能ですが、Mac版ではまだできなさそうなので注意が必要です。悲しい。
詳しい対応バージョンは以下をどうぞ。

Power Pivot が含まれている Office のバージョン

手順としては

PowerPivotアドインを有効にする
対象範囲をデータモデルとして読み込む
中央値計算のメジャー作成
ピボットテーブルで作成したメジャーを選択

設定手順

Power Pivotの導入

Power PivotはMicroSoftから提供されているExcelを強化できる分析ツールの一つです。アドインの形で提供されています。

Power Pivot は、データモデルを作成し、関係を確立して計算を作成できるデータモデリングテクノロジです。

Power Pivot - 概要と学習

今回は中央値計算（計算式の拡張）をやりたかっただけですが、テーブルのリレーション表示などDBチックにも色々と使えそうです。

まず、アドインを以下のように有効化します。
[ファイルタブ] → [オプション] → [アドイン] → 「管理：COMアドイン」の設定

データモデル読み込み

計算対象のデータを読み込みます。

方法１

通常のピボットテーブル作成と同様に範囲を指定して、[挿入タブ] → [ピボットテーブル] を押します。
設定ダイアログで「このデータをデータモデルに追加する」にチェックをつけると、PowerPivotで利用するデータモデルが作成されます。

方法２

新しく表示されれうようになっている[PowerPivotのタブ]からデータモデルに追加を選ぶと、現在選択されている部分を読み込めます。また、管理を押すとデータモデル一覧やそれに対する各種処理の設定が行えるウィンドウが表示されます。

中央値計算のメジャー作成

PowerPivotが使用できるなりましたが、ピボットテーブル作成時に中央値がすぐに使えるようになった訳ではないようです。
ピボットテーブルで利用する計算を拡張して、使えるようにします。
[PowerPivotタブ]→[メジャー] を選ぶと計算方法を定義できるウィンドウが出てきます。
ここで中央値を設定します。
この時各種入力項目はピボットテーブルのこの部分に相当するので、わかりやすい命名にしましょう。

以下のようなテーブルがあったとして、金額列の中央値を出したい場合、 =MEDIAN([金額]) のように設定します。

使ってみる

通常の項目と同じように選択し、値の枠に持っていくとこんな感じで表示されます。
他の「平均」や「合計」みたいにどこのピボットテーブルでも汎用的に使えるという形にはならないものの、ひとまずピボットテーブルで中央値を計算するという目標は達成できました。

まとめ

PowerPivot
- 計算を拡張することで中央値計算もできる
- ただし使用できるExcelバージョンが限られている(2020/8現在)
- テーブルモデル・項目ごとに設定しないといけないのはつらいところ