集計以外もできる！ピボットテーブルで分析を効率化する方法（平均・比率編）

「Excelでひととおりの関数は使える」という方、Excelにはさらに便利な機能「ピボットテーブル」があるのをご存知ですか？ピボットテーブルを使えばアンケート集計から売上や経費の集計まで、大量のデータを一瞬で集計することができます。ピボットテーブルは「データの分析」で最も真価を発揮します。例えば、

• いちばんよく売れている商品は何か？
• 客層ごとの人気商品は何か？
• いちばん売上単価の高い客層は？
• 売上が上がった（または下がった）原因は？

といったことも、ピボットテーブルを使えば大量のデータをいちいち調べることなく分析できるのです。

今回の記事では、売上の平均値や前月比・順位・主力商品と比較したときの売上倍率など、ピボットテーブルで使える様々な集計・比較のバリエーションを紹介します。ぜひ仕事の効率アップに役立ててください。
ピボットテーブルの基本的な使い方は以下の記事を参考にしてください
ピボットテーブルで何ができる？表の集計・分析を効率化しよう

ピボットテーブルで分析する前に

ピボットテーブルは集計したデータから一定の特徴や傾向をつかみ、検証するのに便利です。以下の売上データを例に考えてみましょう。

このデータは飲食店の売上をまとめたものです。このデータをもとに、商品別の売上をピボットテーブルでまとめてみます。（ピボットテーブルの基本的な作成方法は前回の記事を参考にしてください。）

このままのデータでもある程度分析は可能ですが、例えば「注文自体の数と売上金額を比較したい」という場合は、売り上げた商品の数量を売上金額と同時に一覧できる表が必要です。

【ピボットテーブルの応用:その1】集計項目を増やして集計・分析しよう

ピボットテーブルの行ラベル・列ラベルには複数の項目を指定することができます。ここでは、項目ごとに集計したいデータが複数ある場合のピボットテーブルの作り方を学んでいきましょう。

販売個数と売上総額の集計を両方とも表示する

まずは売上の数量を売上金額と同時に表示させましょう。フィールドの中から「数量」を選び、「値」の枠の中にドラッグ・ドロップします。


金額と一緒に販売した数量も表示されました。このように、ピボットテーブルの「値」には複数の項目を指定し、同時に集計することができます。「注文数を左側に、売上金額を左側に配置したい」というときは、入れ替えたい項目全体を選択してドラッグ・ドロップして好きな順番に並べ替えることができます。

【ピボットテーブルの応用:その2】合計以外の方法で集計して分析しよう

ピボットテーブルの集計では「合計」の他、平均、最大値、最小値、セルの個数などを求めることもできます。目的にあった集計方法を使いわけましょう。

ピボットテーブルの集計方法を変えて分析

実際に集計方法を変更してみましょう。ピボットテーブル内の「数量」が表示されている部分（または左側作業ウィンドウの「数量」）を右クリックし、「値フィールドの設定」をクリックします。

「ピボットテーブルフィールド」が開きました（Windows版では「値フィールドの設定」という名前のウィンドウが開きます）。ここでは「平均」を選んで「OK」をクリックします。


「数量」の集計方法が「平均」に変わりました。このままでは小数点以下が表示されたままで見た目が良くないため、表示形式を変更しましょう。再度ピボットテーブル上で右クリックし、「値フィールドの設定」から「ピボットテーブルフィールド」を開き、「表示形式」をクリックします。

初期状態の表示形式は「標準」で、何も指定されていない状態です。「数値」に変更すると小数点以下を第何位まで表示するか設定できます。整数表示したい場合は「0」にしておきます。

数量の平均値が整数で表示されました。同様にして金額も平均で集計し、整数で表示します。

さらに、列ラベルに「支店名」を表示させると、支店ごとの売上平均を求めることもできます。「列」枠の中に「支店名」をドラッグ・ドロップしてみましょう。


「商品名」「支店名」でクロス集計され、販売数量と金額の平均が集計されました。やはりこのままだと少し見にくいです。ですが今回はひとつずつ項目を選択して移動させていては時間がかかりそうですね。そんなときは画面右側の作業ウィンドウを操作しましょう。「値」を「支店名」の下にドラッグさせます。


支店ごとに販売数量と金額の平均が集計されました。このように、ピボットテーブルを使えば合計以外の集計を行うことができます。ちなみに、一つの項目に対して複数の集計方法を同時に表示することも可能です（例えば、金額の平均と合計を併記する場合）。その場合は、作業ウィンドウのフィールドエリアから再度項目を選んで「値」にドラッグ・ドロップしてピボットテーブルに項目を追加し、「値フィールドの設定」から「ピボットテーブルフィールド」で集計方法を選んでください。

【参考】ピボットテーブルで使える集計方法一覧

ピボットテーブルでは「平均」以外にも様々な方法でデータ集計ができます。目的にあった集計方法を選びましょう。

• 合計：数値を合計する（数値が入力されたフィールド集計の初期値）
• 個数：何らかのデータが入力されたセルの個数を求める（文字列が入力されたフィールド集計の初期値）
• 平均：数値の平均値を計算する
• 最大：数値の最大値を求める
• 最小：数値の最小値を求める
• 積：数値にセルの数値をかけた値（積）を求める
• 数値の個数：数値が入力されたセルの数を求める。データの種類を問わずセルの個数を求める「個数」と違い、数値が入力されたセルだけを対象にカウントする。
• 標本標準偏差：数値の標本標準偏差を求める
• 標準偏差：数値の標準偏差を求める
• 標本分散：数値の標本分散を求める
• 分散：数値の分散を求める

【ピボットテーブルの応用:その3】データの構成比・前月比を分析しよう

ピボットテーブルを使えば、それぞれの商品が売上全体に占める割合を求めることもできます。ここでは、先ほどの飲食店の売上データをもとに、商品別・支店別の売上比率を求めてみましょう。

ピボットテーブルで構成比を分析する

こちらのピボットテーブルは、先ほどと同じ飲食店の売上データを商品ごと・支店ごとの金額の合計を計算したものです。これをもとに売上比率を求めてみましょう。

構成比を計算するには、ピボットテーブル上の「値」のセルを、どこでも良いので右クリックして「値フィールドの設定」を選択します。

「計算の種類」を選択して▼をクリックすると、計算方法をプルダウンから選ぶことができます。構成比を求めるには「列集計に対する比率」をクリックします。


商品別の構成比が計算されました。支店ごとに各商品が売上の何パーセントを占めているかがわかります。計算の種類を「行集計に対する比率」に変更すると、商品ごとにどこの支店の売上が高いか分析することもできます。

特定の項目を基準にしたときの比率を求める

ピボットテーブルでは「特定の項目を基準として何倍か」という計算もできます。例えば「浦安支店と比べたとき、各支店は浦安支店の何倍の売り上げがあるか」「主力商品である『オリジナルブレンド』を基準に比べると、他の商品はどの程度の売り上げなのか」といったことも比較できます。
例えば、こちらは「商品の分類ごと」「店のあるエリアごと」に売上を集計したピボットテーブルです。

「値」エリアの中のセルを右クリックし、「値フィールドの設定」をクリックします。

「計算の種類」をクリックし、「基準値に対する比率」を選択します。

左のウィンドウで比較したい項目を、右のウィンドウで基準にしたい値を選びます。今回は「コーヒーを基準（=100%）としたときの他の商品群の売上」を求めます。左側で「分類」右側で「コーヒー」をそれぞれ選択します。

また、比率を求めるときの集計方法は自動的にパーセント表示になっており、小数点第二位まで表示されます。設定を変更したい場合は「表示形式」から変更することができます。

集計方法が変更されました。「基準値に対する比率」でデータを集計するときにピボットテーブルの中に空白または「0」の値があると、エラーメッセージ（#NULL!）が表示されることがあるので注意しましょう。

東京都内や南関東ではコーヒー類よりもフード類の売り上げが高いこと、北関東ではコーヒーの売上比率が他の商品よりもかなり高く、逆に南関東では紅茶類の方がコーヒー類よりも10%程度売上が高いことがわかります。
こうした分析方法を知っていると、例えば「新商品をどの分野で開発するか」という課題が持ち上がった場合でも、各店舗で人気のある商品がすぐわかるので企画・戦略を立てやすくなります。

ピボットテーブルで前月比を分析する

ピボットテーブルを使って、「前月から売上金額が何%増減したか」を求めることもできます。先ほどの売上を商品ごと、月ごとにまとめたピボットテーブルを例に説明します。

前月比を求めるには、「値」エリアの中のセルを右クリックし、「値フィールドの設定」を選択します。「計算の種類」を選択し、「基準値との差分の比率」を選択します。左のウィンドウで「日付」、右のウィンドウで「（前の値）」をそれぞれ選択し、「OK」をクリックします。


前月比が表示されました。ちなみに、この方法を応用して「前年比」「前四半期比較」も求めることが可能です。Excel2016以降のバージョンで複数年分の日付が入力されたフィールドをピボットテーブルの行または列ラベルに追加すると、自動的に「年」「四半期」「月」の３つの単位でグループ分けをしてくれます。

年単位や四半期単位で前期と比較する場合は、ピボットテーブルの列ラベルを年または四半期など、比較したい単位だけの表示にしておきます。この状態で前月比を求める場合と同様に「値フィールドの設定」で「計算の種類」から「基準値との差分の比率」を選択し、左側のウィンドウを「年」や「四半期」にします。あとは右側のウィンドウを「（前の値）」に置き換えるだけです。

この機能を使えば売上が伸びている商品や、逆に低迷している商品を一目で見抜けるようになり、非常に便利です。「値フィールドの設定」から「計算の種類」を選択した後で「基準値との差分」を選択すると、比率ではなく金額の増減を求めることもできます。四則演算や関数を利用しても比較はできますが、ピボットテーブルを使えば関数の知識がなくても集計ができ、少ない手順で複数通りの分析ができるといったメリットがあります。

ピボットテーブルでできる「計算の種類」一覧

以下に「計算の種類」で選べる項目をまとめました。上記で紹介したもの以外で便利なのは「降順での順位」。多数の値を比較して、数値が高い順に順位づけをします。今回行ったのは数店舗での売上比較でしたが、例えば「全国に100ある支店の中で売上上位トップ10をピックアップしたい」という場合に役立ちます。また、「親行集計に対する比率」「親列方向に対する比率」はそれぞれタテ方向、ヨコ方向の総計を100%としたときの比率を計算します。売上の推移を分析する場合などに便利です。

• 計算なし：「集計の方法」で選択した計算結果をそのまま表示する
• 総計に対する比率：総合計に対する各項目の比率を求める
• 列集計に対する比率：列の合計に対する各項目の比率を求める
• 行集計に対する比率：行の合計に対する各項目の比率を求める
• 基準値に対する比率：指定したフィールドのうち、特定の項目に対する比率を求める
• 親行集計に対する比率：「項目の数値÷行ラベルの親項目の数値」を計算
• 親列集計に対する比率：「項目の数値÷列ラベルの親項目の数値」を計算
• 親集計に対する比率：「項目の数値÷『基準フィールド』で選択したフィールドの親項目」を計算
• 基準値との差分：「基準フィールド」「基準アイテム」で指定した項目との差を計算
• 基準値との差分の比率：「『基準フィールド』『基準アイテム』で指定した項目との差÷『基準フィールド』『基準アイテム』で選択した数値」を計算
• 累計：「基準フィールド」で指定した項目の数値を累計する
• 比率の累計：「基準フィールド」の数値の比率の累計を計算する
• 昇順での順位：数値を昇順に並べたときの順位を求める
• 降順での順位：数値を降順に並べたときの順位を求める
• 指数（インデックス）：（「セルの数値」×「総計」）÷（「行の総計」×「列の総計」）を計算

まとめ

今回はピボットテーブルを使って「合計」以外の集計ができること、計算方法を変更して比率や前期との比較を実行する方法を説明しました。ピボットテーブルを右クリックして「値フィールドの設定」から「集計の方法」「計算の種類」を指定することで、様々な分析が可能になります。ピボットテーブルは奥深く、本記事だけですべての機能を紹介することはできません。一方で、ピボットテーブルをマスターすれば大量のデータにわずらわされずにすむだけでなく、「データ分析ができる」という強みへと変えることができます。日々の業務の中で使いながら一歩ずつ着実に習得していきましょう。

こちらもオススメ