ピボットテーブル

ピボットテーブルとは

あらかじめ用意したデータをマウスのドラッグ操作で簡単に、 項目ごとに集計したり、それを見やすくレポート化したりできる機能です。
関数を使って複雑な数式を入力しなければいけないような作業でも、ピボットテーブルはマウスひとつで簡単に出来てしまいます。

どんな時に使うか

たとえば、スーパーで食料品を買うとします。
カゴにリンゴ5個とジャガイモ10個など、たくさん食料品が入っています。
りんごの総額と、ジャガイモの総額が知りたい場合、 カゴの中からりんごを出してまとめて、 じゃがいもはじゃがいもでまとめて、最後にそれぞれの金額を合計します。
この「カゴから出して種類ごとにまとめる」という作業と「それぞれを合計する」という作業をピボットテーブルがやってくるのです。
「このカゴ (テーブル、表) を調べる」という場所の指定と、  「食料品を買ったときの総額 (商品の出荷数) が知りたい」という目的を明確にし、  指定すればよいのです。

ピボットテーブルの作成　

下表のデータをピボットテーブルにします。

  B C D E F G
2 No 年月日 項目名 収入 支出 適用
3 1 2011/04/01 繰越 12,345   前年度繰越
4 2 2011/04/10 税金   5,000 軽自動車税
5 3 2011/04/10 食費   12,800 総菜費
6 4 2011/04/10 給与 200,000   3月分の給料
7 5 2011/04/11 光熱費   3,000 水道料金
8 6 2011/04/11 アルバイト代 20,000   アルバイト代
9 7 2011/04/12 光熱費   5,000 ガス料金
10 8 2011/04/12 交際費   10,000 新築祝い
11 9 2011/04/13 食費   5,000 総菜費
12 10 2011/05/10 光熱費   6,000 電気料金
13 11 2011/04/15 光熱費   5,000 電気料金
14 12 2011/04/16 アルバイト代 15,000   アルバイト代
15 13 2011/04/17 税金   29,800 自動車税
16 14 2011/04/22 交際費   30,000 結婚祝い
17 15 2011/04/03 教育・教養費   8,000 学習塾4月分
18 16 2011/04/24 食費   10,000 外食
19 17 2011/04/29 教育・教養費   8,000 学習塾5月分
20 18 2011/04/30 食費   8,200 外食
21 19 2011/05/02 食費   8,500 総菜費
22 20 2011/05/23 教育・教養費   10,000 学習塾6月分
23 21 2011/05/04 食費   12,000 外食

完成例　　　　

収入のピボットテーブル
支出のピボットテーブル
  1. データ表内のセルを選択します。
  2. [挿入]タブの[ピボットテーブルの挿入]を実行します。
  3. [ピボットテーブルの作成]ダイアログでデータ範囲およびテーブルの配置する場所を設定します。
    • テーブル/範囲が正しいか確認します。適切でないときはシート上のセル範囲をドラッグして指定します。
    • 作成する場所は、今回は「新規ワークシート」としました。

  4. シート上に作成のヒントが表示され、フィールドリストが作業ウィンドウに表示されます。

    ピボットテーブルの操作　　

    収入のピボットテーブルを作成する

    • フィールドを各ボックス内へドラッグします。
      • 列ラベル：年月日
      • 行ラベル：項目名
      • 値：収入

    • 年月でデータの絞り込みを行いたいので、「年月日」を「年」「月」にグループ化します。
      1. 列フィールドを選択します。
      2. [ピボットテーブル　ツール]リボンの[オプション]タブで[グループの選択]を実行します。
      3. 単位で、「月」と「年」を選択し［OK］ボタンをクリックします。
        • ここで「年」と「月」を合わせて選択するのはデータが複数年にわたるケースを想定しています。
          (例えば、2011年5月と2012年5月のデータが有った時区別するためです。)
          データが1年分であるのなら「月」だけでグループ化してもよいでしょう。

      4. 「列ラベル」に「年」の項目が作成されました。また「ピボットテーブルのフィールド」には「2007年」が作成されました。
      5. データが「データの個数」の集計になっているので、「合計」に変更します。
        1. A3セルの[データの個数/収入]を選択します。もしくは「値」が表示されているセルを選択します。
          アクティブなフィールドが「データの個数/収入」となっている必要があります。
        2. [ピボットテーブルツール]リボンの[オプション]タブを選択し、[フィールドの設定]を実行します。
          • フィールドリストの「データの個数/収入」をクリックして「値フィールドの設定」を選択することもできます。
        3. 「値のフィールド」ダイアログボックスで集計方法を「合計」としました。
        4. [表示形式」ボタンまたは[ホーム]タブなどを使って、値のところに[桁区切りスタイル]を設定します。
      6. 行ラベルに不要なものがあります。
        行ラベルの[▼]ボタンをクリックし、表示されてリストから不要なもののチェックを外します。
      7. これで完成です。

    支出のピボットテーブルを作成する

    • 同じ手順で、支出のピボットテーブルを作成します。
      元になるピボットテーブルをコピーして収入と支出を入れ替えるのが手っ取り早い方法です。この時は行ラベルを選択し直します。
      下図のようになりました。
    • 収入と支出を一つのピボットテーブルにすることもできます。
      • 下図のように「値」に収入と支出を入れます。
      • 下図のピボットテーブルができました。

    行ラベルを値フィルターを使って非表示にする

    • 上記の例の「6」では行ラベルの[▼]ボタンをクリックし、表示されてリストから不要なもののチェックを外しましたが、値フィルターを使う方法もあります。
    • 操作手順
      1. 行ラベルの[▼]ボタンをクリックし、[値フィルター]→[指定の値より大きい]を選択します。
      2. 中央の欄内に「0」を入力します。
        「合計/収入」が「0」「より大きい」という条件を設定します。
      3. 下図のようになります。

    ピボットテーブルの削除　　　

    • ピボットテーブル以外のデータがないときは、シート全体を削除するのが簡単です。
    • ピボットテーブルの範囲をすべて選択して、[ホーム]タブの編集グループにある[クリア]→[すべてクリア]を実行して削除します。
      または、ピボットテーブルの範囲をすべて選択して、[Delete]キーを押して削除することもできます。
      1. ピボットテーブルの範囲を選択します。
      2. [ホーム]タブの編集グループにある[クリア]→[すべてクリア]を実行します。
    • コマンドボタンからピボットテーブルを選択する方法もあります。
      1. ピボットテーブル内のセルを選択します。
      2. ピボットテーブル ツールの［オプション］タブの［選択］→［テーブル全体］を選択します。
      3. ピボットテーブル 全体が選択されました。
        [Delete]キーを押すと削除することができます。

     

     

     

テキスト