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

ピボットテーブルとはどういうものか知っておく

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

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

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

ピボットテーブルを新規作成する

元の表は適切な形式にしておこう!

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

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

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

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

ピボットテーブルにフィールドを追加する

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

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

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

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

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

追加したフィールドを移動・削除する

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

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

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

メニューからも削除できる

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

ピボットテーブルをもっと簡単に作成する

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

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

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

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

グループ化の設定を変更できる!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ピボットテーブルからグラフを作成する

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

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

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

ピボットテーブルからデータを取り出す

データを別の表で活用する

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

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

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

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

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

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

※この記事は『エクセルが2週間で身につく(楽)上達講座』(マキノ出版)に掲載されています。