Excelで中央値を計算するピボットテーブルを作成する方法

マーケターにとって、Microsoft Excelはデータを並び替えたり、分析したり、共有したりするための非常に便利なツールであることは間違いありません。ただ難点なのが、非常に便利なはずの関数のなかに、数字の得意な人でさえかなり手強いと感じるようなものが含まれることです。

たとえば、ピボットテーブルの使い方については、HubSpotによるこちらの無料ガイドブックでも説明していますが、マーケティング分析などの際に便利な中央値を、ピボットテーブルでは残念ながら計算することができません。

中央値を使用すると、データの特徴について平均値よりも詳しく知ることができます。たとえば、小規模な非営利団体が、資金集めを行った際に、1,000円の寄付を10件と、50万円の寄付を1件受け取ったとします。この場合、寄付金の平均額は46,363円であるのに対し、中央値は1,000円です。

ピボットテーブルの内部でも、配列を使用すれば、計算式を作成してデータセットの中央値を算出することができます。その方法について、この記事で最初に説明したいと思います。説明には顧客の企業サイズと、セールスサイクル（日数）の入った顧客リストを使用します。

中央値と平均値の違いをわかりやすく示すため、各企業サイズに対するセールスサイクルの平均値と中央値を、2つの表で並べて表示しています。まずはこちらからサンプルファイルをダウンロードしてください。以降の説明を見ながら、このファイルを実際に操作してみましょう。

Excelのピボットテーブルで配列を使用して中央値を計算する方法

セールスサイクルの平均値（Average of Sales Cycle）を示す表は、ピボットテーブルで作成しています。セールスサイクルの中央値（Median of Sales Cycle）の表の作成方法は以下のとおりです。

１）従業員数（# of Employees）を1-5、6-10、11-15、16-20、21-30、31以上の6つに分けた各カテゴリを、F列に入力します。

２）「1-5」の右側のセルに「 =MEDIAN(IF($A$2:$A$20=D2, $B$2:$B$20))」と入力します。

３）この数式の最後の閉じ括弧を入力した後に、セルから移動せずそのままControl+Shift+Enterキー（PCの場合）、またはCommand+Shift+Enterキー（Macの場合）を押すと、中央値が計算されます。この操作によって、配列の作成をExcelに指示することができます。

この操作を行うと、数式が波括弧で囲まれますが、この波括弧をセル内の数式に自分で追加したり、コピー＆ペーストして追加した場合には、Excelは配列を作成しません。

４）先頭のセル（この例ではG2）の中央値をコピーし、それ以降のセル（この例ではG3-G7）に貼り付けてください。

上記の数式で処理されるデータは以下の3つです。

1. A列に入力された社員数の値
2. セルD2の値（1 to 5）
3. B列に入力されたセールスサイクルの値

数式の説明： まずIF文によって、社員数が「1-5」であるすべての行が検出され、それぞれのセールスサイクルの値が配列に格納されます。その後、MEDIAN関数によって、社員数が「1-5」である顧客のセールスサイクルの中央値が配列から算出されます。

平均値と中央値の違い

平均値と中央値はいずれも、データ群の特徴を数値で表すためによく利用されます。より一般的なのは平均値の方で、データ群の代表的な値を調べるのに便利ですが、場合によっては、中央値を使用した方が有益な情報が得られる場合もあります。

平均値では、データの分布にかなりの偏りがあり場合、たとえば大部分の値が近似する一方で、それとはかけ離れた値がいくつか見られるような場合に、その影響を大きく受けてしまいます。したがって、そのような場合は中央値を使用した方が、データ群の特徴をより正確に理解することができます。

たとえば、米国では世帯収入の調査報告に中央値がよく使用されますが、これは米国内の世帯ごとの所得に非常に大きな格差があるため、平均値では世帯収入の傾向を適切に示すことができないからです。

したがって、企業が収益を分析する場合などには、どのようなデータを分析するかを考えたうえで、平均値または中央値を選ぶことが重要です。

たとえば、月ごとの売上げ金額の合計を分析するのであれば、ひと月の営業日数がどの月もだいたい同じで、条件が変わらないことから、平均値を算出して1日の売上げ金額を代表する基準値を示すのがよいと思います。

しかし、販売価格が1,000円の商品もあれば250万円の商品もあるような企業の場合は、1日の売上げ金額を平均値で示したのでは、データに偏りがあった場合にその影響を大きく受けてしまいます。したがって、安価な商品と高価な商品のどちらが多く売れたかを知りたいような場合は、中央値を使用する方がよいでしょう。

結論として、平均値と中央値の両方を算出し、Excelのピボットテーブルを使用して表示すれば、そのデータ群から得られる情報を最も詳しく示すことができると思います。