Yahoo! JAPAN

売上や来店客数などのデータを多角的に分析し、現状の把握や問題点の解明につなげるための手法が「クロス集計」です。Excel（エクセル）の「ピボットテーブル」を利用すれば、数式や関数を使わずに簡単に、そうしたクロス集計を実践でき、高度なデータ分析ができるのです。

「ピボットテーブル」は数式や関数を使わずに簡単に高度なデータ分析ができる機能

まずは目的やメリットを確認しよう！

売上などのデータを集計する際、日付や店舗、商品名、金額などを表にまとめるのが一般的な方法です。しかし、それだけでは「どの店舗で、いつ、何がよく売れているのか」といった詳細な情報を得ることはできません。そこで試したいのが、ピボットテーブルを活用したクロス集計です。行と列に複数の項目を配置することで、多様な角度からデータを分析できるのがメリットです。

ピボットテーブルの作成例。店舗、月、商品ジャンルという三つの視点から売上金額を分析でき、単純な集計ではわからない売上の傾向を知ることが可能です。

ピボットテーブルを新規作成してみよう！

元の表は適切な形式に

ピボットテーブルを作成するには、まず元になるデータを表またはテーブルにまとめておく必要があります。表の途中に空行や小計などの行が含まれていたり、セルが結合されていたりすると、正常に作成できないので注意しましょう。1行目に各列の項目名を入力し、2行目以降にはすべて同じ形式でデータを入力しておくのが鉄則です。

表またはテーブル内のセルを選択し(❶)、「挿入」タブにある「ピボットテーブル」のアイコン部分をクリックします(❷)。

「テーブル/範囲」で、ピボットテーブルにしたいデータのあるセル範囲が正しく選択されていることを確認します(❸)。もし誤っていれば修正しよう。配置する場所として「新規ワークシート」を選択し(❹)、「OK」をクリック(❺)。

新しいワークシートが追加され、ピボットテーブルが作成されます。右側の「ピボットテーブルのフィールド」エリアには、上部に「フィールドセクション」(❻)、下部に「エリアセクション」が表示されます(❼)。初期状態では左側に説明が表示されるので(❽)、確認しておきましょう。

ピボットテーブルにフィールドを追加してみよう！

行や列に表示したい項目を追加しましょう

ピボットテーブルでは、行や列に表示する項目を「フィールド」と呼びます。元の表から読み込まれた項目が「フィールドリスト」に候補として表示されるので、ここから画面右下の「行」エリアや「列」エリアにドラッグして追加しましょう。

まず、「店舗名」という項目を行として追加してみましょう。「フィールドリスト」で「店舗名」を選択し、画面下部の「行」エリアへドラッグします(❶)。するとピボットテーブルに追加され、「行ラベル」が表示されます。

次に、「フィールドリスト」で「ジャンル」を選択し、「列」エリアへドラッグします(❷)。すると、ピボットテーブルに「列ラベル」が追加され、ジャンルの一覧が列として表示されます。

「フィールドリスト」で「金額」を選択し、「値」エリアへドラッグします(❸)。すると、行ラベルと列ラベルが示す項目に従って、値(ここでは金額)が表示さます。

追加したフィールドを移動・削除してみよう！

フィールドはドラッグで動かせる

ピボットテーブルに追加したフィールドは、「列」や「行」などのエリア間でドラッグして移動させることが可能です。また、誤って追加したフィールドを削除する方法も覚えておきましょう。

フィールドを選択し、別のエリアへドラッグすれば移動できます。エリアの外部へドラッグすれば、削除が可能。メニューからも削除できます。

メニューを使って削除したい場合は、フィールドの右にある「▼」をクリックし(❶)、「フィールドの削除」を選択します(❷)。

ピボットテーブルをもっと簡単に作成するとっておきの方法

サンプルから選ぶだけでOK！

ピボットテーブルは非常に便利なのですが、慣れないうちは作成方法が難しいと感じるかもしれません。もっと手軽に作成したいなら「おすすめピボットテーブル」を使ってみましょう。サンプルからレイアウトを選ぶだけで、簡単に作成できます。

表またはテーブル内のセルを選択し(❶)、「挿入」タブの「おすすめピボットテーブル」をクリック(❷)。表示された画面で、サンプルを確認しながら使用したいものを選んでクリックします(❸)。

日付を「日」「月」「四半期」などの単位で表示する

グループ化の設定も変更できる！

フィールドに追加したデータに日付がある場合、自動的に月ごとなどの単位でグループ化されます。この単位は変更することも可能です。もっと細かく売上の推移を見たいなら「日」、長期的な傾向を知りたいなら「四半期」というように、目的に合わせて設定しましょう。

まず、日付のフィールドが表示されるようにしましょう。「フィールドリスト」から「日付」を選択し、「行」へドラッグして追加します(❶)。

日付が自動的に月単位でグループ化されました。変更したい場合は、日付のいずれかを選択し(❷)、「ピボットテーブル分析」タブの「グループ」をクリックして(❸)、「グループの選択」を選びます(❹)。

「グループ化」画面の「単位」で、使用したいものを選択します(❺)。「日」を選択した場合、「日数」を変更すれば「10日ごと」などで表示することも可能です(❻)。設定できたら「OK」をクリックします(❼)。

条件に一致するデータを抽出して集計する

レポートフィルターで絞り込む

ピボットテーブルには、データを抽出するための方法がいくつか用意されています。特に便利なのが「レポートフィルター」です。この機能を使って条件を絞り込むと、該当するデータの値だけを集計できます。

ここでは、品名を条件としてデータを抽出してみよう。まず「フィールドリスト」で「品名」を選択し(❶)、「フィルター」へドラッグして追加します(❷)。

ピボットテーブルの上部にレポートフィルターが表示されるので、右側の「▼」をクリックして(❸)、条件を選択する(❹)。「複数のアイテムを選択」にチェックを付ければ(❺)、複数の条件を選択可能になる。設定できたら「OK」をクリック。

ここでは「ショートケーキ」を選択したので、データの中からショートケーキの売上だけが抽出され、金額が集計されます。

スライサーでデータを絞り込む

抽出条件をいろいろと切り替えながらデータを分析したいときは、テーブルと同様に「スライサー」を使うと便利です。利用するには、「ピボットテーブル分析」タブの「スライサーの挿入」をクリックします。あとは、テーブルのスライサーと同じように操作できます。

期間を指定してデータを抽出する

タイムラインを使って絞り込む！

ピボットテーブルに日付を示すフィールドが含まれている場合は、「タイムライン」で期間を指定してデータを絞り込むことができます。わかりやすい操作で、簡単に抽出できるのでおすすめです。

「ピボットテーブル分析」タブの「タイムラインの挿入」をクリック(❶)。「タイムラインの挿入」画面が表示されたら、使用するフィールド(ここでは「日付」)にチェックを付け(❷)、「OK」をクリックします(❸)。

タイムラインが表示される。青いバーの両端をドラッグして期間を指定すると(❹)、該当するデータだけが表示されます。

ピボットテーブルからグラフを作成してみよう！

データを視覚的にわかりやすく

ピボットテーブルのデータをグラフで表示したいときは、「ピボットグラフ」を使いましょう。グラフの種類を選ぶだけで簡単に作成でき、フィルターを使ってデータを絞り込むことも可能です。

「ピボットテーブル分析」タブの「ピボットグラフ」をクリックし、グラフの種類を選択します。グラフの右や左下に表示されるフィルターをクリックして条件を指定すれば、データの絞り込みが可能です。

ピボットテーブルからデータを取り出してみよう！

データを別の表でも活用

ピボットテーブルで得た値を別の表で利用したいときは、GETPIVOTDATA関数を使ってデータを取り出します。引数に文字列を使う場合は、「"」（ダブルクォーテーション）で囲む必要があるので注意しましょう。

GETPIVOTDATA

ピボットテーブルから指定したフィールドのデータを返す

=GETPIVOTDATA(データフィールド,ピボットテーブル,[フィールド1],[アイテム1],[フィールド2],[アイテム2]...)

セルB11に「=GETPIVOTDATA("金額",$A$3,"店舗名","銀座店")」と入力しました。すると銀座店の総計、つまりセルF5の値を取り出すことができます。

自動入力機能を使えば簡単！

GETPIVOTDATA関数は引数が複雑で難しそうに見えますが、実は簡単に入力する方法があります。データを表示したいセルに「=」と入力し、ピボットテーブル内のセルをクリックすると、自動的に入力が可能です。

まず、セルB12に「=」と入力します(❶)。次に、取り出したい値があるセル(ここではセルF6)をクリック(❷)。

セルB12にGETPIVOTDATA関数が入力され、引数も自動的に記述されます。あとは、[Enter]キーを押すだけでデータを取得できます。