独学MOS

ピボットテーブルの利用

ピボットテーブルとは

表に入力されたデータを集計して集計結果の表を作成する機能です。集計する項目を自由に入れ替えたり複数の項目でデータを集計したりすることができます。

ピボットテーブルの作成

例として以下のような表があったとします。(「受注日」がセル「A1」にあるものとします)

受注日 顧客名 商品名 単価 数量 受注金額
2012/1/1 山田一郎 ミカン 100 10 1000
2012/1/5 山田三郎 ブドウ 120 1 120
2012/1/12 山田二郎 ミカン 100 90 9000
2012/1/20 山田二郎 リンゴ 150 50 7500
2012/2/1 山田一郎 リンゴ 150 5 750
2012/3/1 山田一郎 ブドウ 120 8 960
2012/3/3 山田三郎 ミカン 100 10 1000
2012/3/15 山田二郎 メロン 200 10 2000
2012/4/1 山田一郎 バナナ 90 9 810
2012/4/2 山田三郎 バナナ 90 80 7200
2012/4/4 山田二郎 バナナ 90 1 90
2012/5/1 山田一郎 メロン 200 3 600
2012/5/6 山田三郎 バナナ 90 100 9000
2012/5/10 山田三郎 メロン 200 1 200
2012/5/28 山田二郎 リンゴ 150 100 15000

この表から顧客ごとの受注金額を商品別に表示するピボットテーブルを作成するには以下の手順を行います。

  1. 「挿入」タブをクリック
  2. テーブルグループの「ピボットテーブル」をクリック
  3. ピボットテーブルの作成ダイアログで列名を含めた表全体を選択してテーブルの配置場所を新規ワークシートに設定した後「OK」ボタンをクリック
  4. 新規ワークシートに空のピボットテーブルが作成される
  5. ピボットテーブルのフィールドリストから「顧客名」を行ラベルボックスにドラッグ&ドロップする
  6. ピボットテーブルのフィールドリストから「受注金額」を値ボックスにドラッグ&ドロップする
  7. ピボットテーブルのフィールドリストから「商品名」を列ラベルボックスにドラッグ&ドロップする

フィールドの追加、変更、削除

ピボットテーブルへのフィールドの追加と変更はいずれもドラッグ&ドロップでできます。フィールドの削除は追加したフィールドをクリックし、「フィールドの削除」を選択することで可能です。

フィールドのグループ化

ピボットテーブルのフィールドをグループ化するには以下の手順を行います。

  1. 列ラベルに「商品名」、行ラベルに「受注日」、値に「受注金額」を設定したピボットテーブルを作成
  2. グループ化したいフィールドにカーソルを移動
  3. グループグループの「グループの選択」をクリック
  4. グループ化ダイアログで「OK」ボタンをクリック
  5. フィールドがグループ化される

これでフィールドをグループ化することができました。グループ化の解除は解除したいフィールドにカーソルを合わせて、グループグループの「グループ解除」をクリックすることでできます。

フィールドの表示形式の設定

ピボットテーブルのフィールドの表示形式を設定するには以下の手順を行います。

  1. 列ラベルに「商品名」、行ラベルに「受注日」、値に「受注金額」を設定したピボットテーブルを作成
  2. ピボットテーブルの値エリアを右クリックして「表示形式」をクリック
  3. セルの書式設定ダイアログで書式を設定して「OK」ボタンをクリック
  4. 値エリアの書式が変更される

ピボットテーブルのオプション設定

ピボットテーブルのオプションを設定するには以下の手順を行います。

  1. 列ラベルに「商品名」、行ラベルに「受注日」、値に「受注金額」を設定したピボットテーブルを作成
  2. ピボットテーブルを右クリックして「ピボットテーブルオプション」をクリック
  3. ピボットテーブルオプションダイアログで各種設定をして「OK」ボタンをクリック
  4. ピボットテーブルのオプションが変更される

値フィールドの集計方法の変更

値フィールドの集計方法を変更するには以下の手順を行います。

  1. 列ラベルに「商品名」、行ラベルに「受注日」、値に「受注金額」を設定したピボットテーブルを作成
  2. ピボットテーブルの値エリアを右クリックして「値フィールドの設定」をクリック
  3. 値フィールドの設定ダイアログで値フィールド集計方法を変更して「OK」ボタンをクリック
  4. 値エリアの集計方法がが変更される

集計元データの表示

ピボットテーブルの値エリアに表示されている集計値をダブルクリックすると集計元の一覧が新規ワークシートに作成されます。

レポートフィルターページの表示

レポートフィルターが設定されている場合、以下の手順でフィールドごとのピボットテーブルを新規ワークシートに作成することができます。

  1. レポートフィルターに「顧客名」、列ラベルに「商品名」、行ラベルに「受注日」、値に「受注金額」を設定したピボットテーブルを作成
  2. ピボットテーブルの内のセルにカーソルを移動
  3. 「オプション」タブの「ピボットテーブル」をクリック
  4. 「オプション」の横にある三角をクリック
  5. 「レポートフィルターページの表示」をクリック
  6. レポートフィルターページの表示ダイアログで「OK」ボタンをクリック
  7. レポートフィルターの項目ごとに新規ワークシートが作成される

スライサーの利用

スライサーを利用することで複数の項目を使ってピボットテーブルを絞り込むことができます。スライサーを使った絞り込みは以下の手順で行います。

  1. 列ラベルに「商品名」、行ラベルに「顧客名」、値に「受注金額」を設定したピボットテーブルを作成
  2. 「挿入」タブをクリック
  3. ピボットテーブル内のセルにカーソルを移動
  4. フィルターグループの「スライサー」をクリック
  5. スライサーの挿入ダイアログで絞り込みたい項目にチェックを入れて「OK」ボタンをクリック
  6. スライサーが作成される

スライサーに表示されている項目をクリックすることで絞り込みを行うことができます。「Ctrl」キーを押しながらクリックすることで複数の項目を同時に選択することができます。また、スライサーは複数のピボットテーブルを同時に絞り込みすることが可能です。上記の手順に続けて以下の手順を行うと二つのピボットテーブルを一つのスライサーで絞り込みできます。

  1. 二つ目のピボットテーブルを作成
  2. スライサーを選択して「オプション」タブをクリック
  3. スライサーグループの「ピボットテーブルの接続」をクリック
  4. ピボットテーブルの接続ダイアログで二つ目のピボットテーブルにチェックを入れて「OK」ボタンをクリック
  5. スライサーが接続される

ここまでの手順を実行してスライサーの絞り込みを行うと二つのピボットテーブルが同時に絞り込まれます。