前回、エクセルでクロス集計表を使った多角分析機能を提供する「ピボットテーブル」について、どのようなデータから「ピボットテーブル」が作られるのかについて触れてみました。
今回は、「ピボットテーブル」の作成の仕方から、分析したいデータ項目の入れ替えなど、「ピボットテーブル」を色々と操作してみたいと思います。
目次
ピボットテーブルを作成する
エクセルファイルはこちらからダウンロードできます。
元になるデータから、新たに「ピボットテーブル」を作成すると、
「ピボットテーブルのフィールド」
というウィンドウが表示されます。
前回の最後に表示させましたね。
以下の簡単な手順で、前回最後に完成したあのクロス集計表を作成することができます。
「ピボットテーブル」は昔の97や2000など古いバージョンのエクセルから存在していましたが、昔から知っている方にしてみると、このビジュアルはとても見やすくなった、と思っている方も多いかもしれませんね。
上の画面が「ピボットテーブル」を挿入した直後になりますので、ここからクロス集計となる「縦の項目」である「行フィールド」と「横の項目」である「列フィールド」を設定していきます。
まず、「行フィールド」に「担当者」を設定してみます。
フィールドの一覧から「担当者」を「行」の部分へドラッグアンドドロップします。
同じように、「列フィールド」に「商品名」をドラッグアンドドロップで設定してみましょう。
最後に、集計する「値」に「販売金額」を設定してみます。
集計の方法はデフォルトで「合計」となります。
たったこれだけの作業で、クロス集計表が完成してしまいました。
そして、元の表では分からなかった「各営業担当者の売上」や「商品ごとの売上」がはっきりと視覚的に認識できるようになったわけです。
フィールドの追加・入れ替え
「クロス集計表」の「行フィールド」と「列フィールド」の項目は、簡単に追加したり入れ替えたりすることができます。
「行フィールド」に「担当者」だけでなく、「日付」もドラッグアンドドロップで追加してみました。
そうすると、日付ごとに各担当者が売り上げた金額が集計されるようになりました。
一旦、追加した「日付」フィールドを「クロス集計表」から外します。
追加した「日付」フィールドの横にある「▼」をクリックし、一覧から「フィールドの削除」をクリックします。
「クロス集計表」から「日付」が削除されます。
同じ要領で、単に「行フィールド」と「列フィールド」をそれぞれ「商品名」と「担当者」となるようにフィールドを入れ替えてみました。
集計の向きが変化しただけで、合計された各金額に違いはないことが分かります。
値フィールドの集計方法を変更する
これまで見てきた「クロス集計表」は、「各営業担当の売上金額の合計」、「各商品の販売金額の合計」を集計してきました。
「値フィールド」には、「合計／販売金額」と表示されています。
では、各営業担当が売り上げた金額の中で「一番高い金額を集計したい」場合にはどうすればよいでしょうか。
実は、「値フィールド」の集計方法は簡単に変更することができます。
まず、ここでは「大島卓也」の売上金額に注目してみます。
彼は、「A-1」を2000円、「A-2」を4000円、「C-1」を20000円販売しています。
そこで、元のデータを確認してみましょう。
元のデータを確認すると、集計期間で、4回販売している内「A-2」は「10月5日」に2000円、「10月15日」に2000円で計4000円を販売しているわけです。
つまり、「クロス集計表」では、「販売金額の合計」として「大島卓也はA-2を合計4000円売り上げている」ことが簡単に分かるわけですね。
さて、それでは集計方法を「一番高い金額」に変更してみます。
「大島卓也」のデータに注目してみましょう。
「値フィールド」の「合計／販売金額」の横の「▼」をクリックして、「値フィールドの設定」をクリックします。
「値フィールドの設定」ウィンドウが表示されます。
「選択したフィールドのデータ」の一覧から「最大」をクリックして、「OK」ボタンをクリックします。
「クロス集計表」の「値フィールド」の集計方法が「最大」に変更されました。
それでは、もう一度「大島卓也」のデータを確認してみます。
「大島卓也」が売り上げた「A-2」の商品は、先程元のデータを確認した通り、「10月5日」と「10月15日」の2回販売されていますが、どちらも販売金額は「2000円」です。
したがって、「合計」を集計した際には「A-2は4000円」と集計されましたが、
「最大」を集計した際には、「A-2は2000円」と集計されます。
そう、「大島卓也」が販売した「A-2」の最大金額は「2000円」だからです。
では、商品「A-2」の総計(縦の総計)を確認してみましょう。
「合計」を集計していた時は、各営業担当が販売した「A-2」の売上金額の合計を表示していましたが、
「最大」の場合は、各営業担当が販売した「A-2」の売上金額で最大金額を集計します。
「A-2」は「大島卓也が2000円」、「田中聡が1200円」販売しています。
したがって、総計として最大値である「2000円」が表示されているのです。
同様に、担当者「大島卓也」の総計(横の総計)を見てみると、商品「C-1」の「20000円」が最大値であることが分かりますので、「総計」には最大値である「20000円」が表示されていますね。
表示形式を修正する
これまで、エクセルでの実際の画面を見ながら進めてきましたが、
「ピボットテーブル」に少々見づらい部分があったことにお気づきでしょうか。
そう、値の数値に「カンマ区切りがなっていない」、ということです。
早速、設定を変えてみたいと思います。
ピボットテーブル内の任意の場所で右クリックしてショートカットメニューを表示します。
一覧から「値フィールドの設定」をクリックします。
「値フィールドの設定」ウィンドウが表示されます。
左下の「表示形式」をクリックします。
「セルの書式設定」ウィンドウが表示されます。
一覧から「数値」を選択し、「桁区切りを使用する」にチェックを入れて「OK」ボタンをクリックします。
ピボットテーブルの値フィールドの金額に「桁区切り」が設定されました。
設定したフィールドを一瞬にしてクリアする
さて、各フィールドを設定してきたピボットテーブルですが、
一旦すべてリセットしたい、と考えました。
一瞬にしてピボットテーブル内をクリアするにはどのようにしたらよいでしょうか。
ピボットテーブル内の任意の場所をクリックすると、リボン内に「分析」タブが表示されます。
一覧に「クリア」があるのでこれをクリックし、「すべてクリア」をクリックします。
各フィールドを設定する前のスタート時点へと戻りました。
ピボットテーブルを削除する
ピボットテーブルそのものを削除するには、同様にリボン内の「分析」タブから設定します。
ピボットテーブル内の任意の場所をクリックして、「選択」から「ピボットテーブル全体」をクリックします。
キーボードから「Delete」キーを押します。
ピボットテーブルが削除されました。
続きは次回
さて、ピボットテーブルへのフィールド配置からちょっとした設定変更など簡単な動作を見てきました。
次回は、フィルターを使ったピボットテーブルの処理を詳しく見ていきたいと思います。
お楽しみに！
