売上や来店客数などのデータを多角的に分析し、現状の把握や問題点の解明につなげるための手法が「クロス集計」です。エクセルの「ピボットテーブル」を利用すれば、数式や関数を使わずにクロス集計を実践でき、簡単に高度なデータ分析ができます。
ピボットテーブルとはどういうものか知っておく
まずは目的やメリットを確認しよう!
売上などのデータを集計する際、日付や店舗、商品名、金額などを表にまとめるのが一般的な方法です。しかし、それだけでは「どの店舗で、いつ、何がよく売れているのか」といった詳細な情報を得ることはできません。そこで試したいのが、ピボットテーブルを活用したクロス集計です。行と列に複数の項目を配置することで、多様な角度からデータを分析できるのがメリットです。
![](https://tokusengai.com/assets/images/2022/05/24/9ee202f766cfed965c5cf13f3c5718542fc7f587.jpg)
ピボットテーブルの作成例。店舗、月、商品ジャンルという三つの視点から売上金額を分析でき、単純な集計ではわからない売上の傾向を知ることが可能だ。
ピボットテーブルを新規作成する
元の表は適切な形式にしておこう!
ピボットテーブルを作成するには、まず元になるデータを表またはテーブルにまとめておく必要があります。表の途中に空行や小計などの行が含まれていたり、セルが結合されていたりすると、正常に作成できないので注意しましょう。1行目に各列の項目名を入力し、2行目以降にはすべて同じ形式でデータを入力しておくのが鉄則です。
![](https://tokusengai.com/assets/images/2022/05/24/eca4523a370a2344effc664eb3f9c1fa59e7dd9d.jpg)
表またはテーブル内のセルを選択し(❶)、「挿入」タブにある「ピボットテーブル」のアイコン部分をクリックする(❷)。
![](https://tokusengai.com/assets/images/2022/05/24/4c7ce7995a68e715e2d6dac7e74af6a8a9deaecc.jpg)
「テーブル/範囲」で、ピボットテーブルにしたいデータのあるセル範囲が正しく選択されていることを確認する(❸)。もし誤っていれば修正しよう。配置する場所として「新規ワークシート」を選択し(❹)、「OK」をクリック(❺)。
![](https://tokusengai.com/assets/images/2022/05/24/b88e045e3fdbb1ad9ba19ae1c3b5aa9561bef560.jpg)
新しいワークシートが追加され、ピボットテーブルが作成される。右側の「ピボットテーブルのフィールド」エリアには、上部に「フィールドセクション」(❻)、下部に「エリアセクション」が表示される(❼)。初期状態では左側に説明が表示されるので(❽)、確認しておこう。
ピボットテーブルにフィールドを追加する
行や列に表示したい項目を追加しよう
ピボットテーブルでは、行や列に表示する項目を「フィールド」と呼びます。元の表から読み込まれた項目が「フィールドリスト」に候補として表示されるので、ここから画面右下の「行」エリアや「列」エリアにドラッグして追加しましょう。
![](https://tokusengai.com/assets/images/2022/05/24/ef1f26a2574432c99941fcc436160c756e89f9ed.jpg)
まず、「店舗名」という項目を行として追加してみよう。「フィールドリスト」で「店舗名」を選択し、画面下部の「行」エリアへドラッグする(❶)。するとピボットテーブルに追加され、「行ラベル」が表示される。
![](https://tokusengai.com/assets/images/2022/05/24/dc8d2988eeaccce904451e77b65e4946d969f883.jpg)
次に、「フィールドリスト」で「ジャンル」を選択し、「列」エリアへドラッグする(❷)。すると、ピボットテーブルに「列ラベル」が追加され、ジャンルの一覧が列として表示される。
![](https://tokusengai.com/assets/images/2022/05/24/9568d54d087fcd10ce11453027d7ad2f5f53ee2e.jpg)
「フィールドリスト」で「金額」を選択し、「値」エリアへドラッグする(❸)。すると、行ラベルと列ラベルが示す項目に従って、値(ここでは金額)が表示される。
追加したフィールドを移動・削除する
フィールドはドラッグで動かせる!
ピボットテーブルに追加したフィールドは、「列」や「行」などのエリア間でドラッグして移動させることが可能です。また、誤って追加したフィールドを削除する方法も覚えておきましょう。
![](https://tokusengai.com/assets/images/2022/05/24/7525faed05122dca38f4b4df17ee3c053d2a3728.jpg)
フィールドを選択し、別のエリアへドラッグすれば移動できる。エリアの外部へドラッグすれば、削除が可能。
メニューからも削除できる
![](https://tokusengai.com/assets/images/2022/05/24/e68c214b309b7ba8d99c20fc1fce5f915c24e084.jpg)
メニューを使って削除したい場合は、フィールドの右にある「▼」をクリックし(❶)、「フィールドの削除」を選択する(❷)。
ピボットテーブルをもっと簡単に作成する
サンプルから選ぶだけでOK!
ピボットテーブルは非常に便利なのですが、慣れないうちは作成方法が難しいと感じるかもしれません。もっと手軽に作成したいなら「おすすめピボットテーブル」を使ってみましょう。サンプルからレイアウトを選ぶだけで、簡単に作成できます。
![](https://tokusengai.com/assets/images/2022/05/24/0b6c6fb17015890d1cc556db9c625e4f31c5a9f4.jpg)
表またはテーブル内のセルを選択し(❶)、「挿入」タブの「おすすめピボットテーブル」をクリック(❷)。表示された画面で、サンプルを確認しながら使用したいものを選んでクリックする(❸)。
日付を「日」「月」「四半期」などの単位で表示する
グループ化の設定を変更できる!
フィールドに追加したデータに日付がある場合、自動的に月ごとなどの単位でグループ化されます。この単位は変更することも可能です。もっと細かく売上の推移を見たいなら「日」、長期的な傾向を知りたいなら「四半期」というように、目的に合わせて設定しましょう。
![](https://tokusengai.com/assets/images/2022/05/24/fa9deabd01296347687f3b49c425029393b4d189.jpg)
まず、日付のフィールドが表示されるようにしよう。「フィールドリスト」から「日付」を選択し、「行」へドラッグして追加する(❶)。
![](https://tokusengai.com/assets/images/2022/05/24/02f593dcaf7487376ca1084c10b16d3b89ac3531.jpg)
日付が自動的に月単位でグループ化された。変更したい場合は、日付のいずれかを選択し(❷)、「ピボットテーブル分析」タブの「グループ」をクリックして(❸)、「グループの選択」を選ぶ(❹)。
![](https://tokusengai.com/assets/images/2022/05/24/021c05cb7e12f3a2a216ced663c7bc8182c1c498.jpg)
「グループ化」画面の「単位」で、使用したいものを選択する(❺)。「日」を選択した場合、「日数」を変更すれば「10日ごと」などで表示することも可能だ(❻)。設定できたら「OK」をクリックする(❼)。
条件に一致するデータを抽出して集計する
レポートフィルターで絞り込む
ピボットテーブルには、データを抽出するための方法がいくつか用意されています。特に便利なのが「レポートフィルター」です。この機能を使って条件を絞り込むと、該当するデータの値だけを集計できます。
![](https://tokusengai.com/assets/images/2022/05/24/1990240cbb2562552c5ba26db0a8a366f2d7e6a2.jpg)
ここでは、品名を条件としてデータを抽出してみよう。まず「フィールドリスト」で「品名」を選択し(❶)、「フィルター」へドラッグして追加する(❷)。
![](https://tokusengai.com/assets/images/2022/05/24/7438f87738bcd71ba4a7e37174884311fd497f02.jpg)
ピボットテーブルの上部にレポートフィルターが表示されるので、右側の「▼」をクリックして(❸)、条件を選択する(❹)。「複数のアイテムを選択」にチェックを付ければ(❺)、複数の条件を選択可能になる。設定できたら「OK」をクリック。
![](https://tokusengai.com/assets/images/2022/05/24/a7a68be2bd2f7683e0fe673fe369438a241cc968.jpg)
ここでは「ショートケーキ」を選択したので、データの中からショートケーキの売上だけが抽出され、金額が集計される。
スライサーでデータを絞り込む
抽出条件をいろいろと切り替えながらデータを分析したいときは、テーブルと同様に「スライサー」を使うと便利です。利用するには、「ピボットテーブル分析」タブの「スライサーの挿入」をクリックします。あとは、テーブルのスライサーと同じように操作できます。
期間を指定してデータを抽出する
タイムラインを使って絞り込む!
ピボットテーブルに日付を示すフィールドが含まれている場合は、「タイムライン」で期間を指定してデータを絞り込むことができます。わかりやすい操作で、簡単に抽出できるのでおすすめです。
![](https://tokusengai.com/assets/images/2022/05/24/73553f6b244f3d09e5da1b667556c386ff72a4b2.jpg)
「ピボットテーブル分析」タブの「タイムラインの挿入」をクリック(❶)。「タイムラインの挿入」画面が表示されたら、使用するフィールド(ここでは「日付」)にチェックを付け(❷)、「OK」をクリックする(❸)。
![](https://tokusengai.com/assets/images/2022/05/24/fbe085143901c1a339749fa1bfbbc05e9708a07e.jpg)
タイムラインが表示される。青いバーの両端をドラッグして期間を指定すると(❹)、該当するデータだけが表示される。
ピボットテーブルからグラフを作成する
データを視覚的にわかりやすく!
ピボットテーブルのデータをグラフで表示したいときは、「ピボットグラフ」を使いましょう。グラフの種類を選ぶだけで簡単に作成でき、フィルターを使ってデータを絞り込むことも可能です。
![](https://tokusengai.com/assets/images/2022/05/24/fada3580590d3fb5555622a81f8ad9ef3bf43c7f.jpg)
「ピボットテーブル分析」タブの「ピボットグラフ」をクリックし、グラフの種類を選択する。グラフの右や左下に表示されるフィルターをクリックして条件を指定すれば、データの絞り込みが可能。
ピボットテーブルからデータを取り出す
データを別の表で活用する
ピボットテーブルで得た値を別の表で利用したいときは、GETPIVOTDATA関数を使ってデータを取り出します。引数に文字列を使う場合は、「”」(ダブルクォーテーション)で囲む必要があるので注意しましょう。
GETPIVOTDATA | ピボットテーブルから指定したフィールドのデータを返す |
---|---|
=GETPIVOTDATA(データフィールド,ピボットテーブル,[フィールド1],[アイテム1],[フィールド2],[アイテム2]…) |
![](https://tokusengai.com/assets/images/2022/05/24/6afe7b32383e42581a6744d5da86b63522343a40.jpg)
セルB11に「=GETPIVOTDATA(“金額”,$A$3,”店舗名”,”銀座店”)」と入力した。すると銀座店の総計、つまりセルF5の値を取り出すことができる。
自動入力機能を使えば簡単!
GETPIVOTDATA関数は引数が複雑で難しそうに見えますが、実は簡単に入力する方法があります。データを表示したいセルに「=」と入力し、ピボットテーブル内のセルをクリックすると、自動的に入力が可能です。
![](https://tokusengai.com/assets/images/2022/05/24/a2c41446b5e0f68def5fb3c598dd901a351a0482.jpg)
まず、セルB12に「=」と入力する(❶)。次に、取り出したい値があるセル(ここではセルF6)をクリックする(❷)。
![](https://tokusengai.com/assets/images/2022/05/24/addacf6d24aff87db30e5f8b7b379d16332365a5.jpg)
セルB12にGETPIVOTDATA関数が入力され、引数も自動的に記述される。あとは[Enter]キーを押すだけでデータを取得できる。
※この記事は『エクセルが2週間で身につく(楽)上達講座』(マキノ出版)に掲載されています。