Introduction
Ver. all 2013 2016 2019 2021 365
ピボットテーブルの使い方①です。
「ピボットテーブル」と言われると、「なんだか便利そう。でも何ができるのか良く分からない・・・」と感じている方が多いのではないでしょうか。
ピボットテーブルを使えるようになるには、細かい操作よりも機能の全体像をつかむことの方が大切です。言われた通りに手を動かしても「へーっ」とその場だけ、できたつもりで終わってしまい、自分でいざ仕事で使ってみようとすると何をして良いのか分からず・・・。
まずは、基本的な使い方をしっかり押さえましょう。
ピボットテーブルには様々な機能が用意され、最後にはグラフも作成できます。
説明が長くなるので、数回に分けて進めていきます。
基本操作が大切なので、急がずゆっくり読み進めて欲しいにゃ
ピボットテーブルでできること
ピボットテーブルの操作を学習する前に、ピボットテーブルで何ができるのかを最初に整理しましょう。
「pivot」を直訳すると、枢軸、中心、旋回などの意味があります。ここでは、回転するという意味が強そうです。
※そういえば、バスケットボールで片足を軸にした動きを「ピボット」と呼びますね。
「table」には表という意味があります。
二つの意味を合わせて考えると、１つの表内のデータを回転させて目的のデータを探す機能ということになります。
ほとんどの機能は、直訳すると内容を理解しやすくなるのですが、ピボットテーブルの場合はそうでもありません。データを回転するという感じがつかみにくいのですね。
実際の使い方に合わせて表現すると、複数の項目で自分の好きなように集計する機能といえます。
「ん？集計だったら、エクセルには他の機能もあるじゃないか・・・」
確かにそうです。集計したいデータによって、［小計］機能や関数を使用しても良いですね。
しかしピボットテーブルを使用した方が、スピーディーに複雑な集計が可能になります。
表の中の特定のフィールド（項目）を自由に取り出して、フィールド別に集計表を作成できます。
項目をドラッグするだけで設定できるので、さまざまな角度からデータの集計や分析を手軽に行うことができるのです。
その集計機能がピボットテーブルの１番大切な機能ですが、他にも様々な機能が備わっています。
ピボットテーブルで利用できる機能をざっと並べてみると
- フィールドごとに集計する
- 新たな集計フィールドを挿入する
- 集計方法を変更できる（合計・平均）など
- 集計範囲を調整できる
- 作成したピボットテーブルをグラフ化する
- フィールドごとにデータを絞り込む
- 日付でデータを絞り込む
- スタイルが適用できる
- 元データが変更された場合、更新できる
細かく分けると、もっと・・・。
この中でもピボットテーブルの中心となるのはやはり集計機能です。
この集計の仕組みを理解することが最初のステップになります。
ピボットテーブルの基本（簡単な集計）
では、下の表を元に、ピボットテーブルの基本的な使い方を説明します。
表の内容を確認してください。「受注日」を見ると6日分のデータだということが分かります。同じ日に複数の受注がありますね。また、「受注営業所」は、渋谷・新宿・東京本店の３か所。「商品区分」は「猫缶」と「かりかり」の２つです。
なんとなく表の内容を頭に入れたところで、各営業所ごとの金額を集計してみたいと思います。
「東京本店だけだと、金額の合計はいくらになるのかな？」という感じ。
ここで大切なのは、自分が作りたい、最終的な表の形をイメージする事。
例えば、今回のように各営業所ごとに金額を集計したい場合は下のようになります。
また、商品区分ごとに金額を集計したい場合、
受注日ごとに金額を集計したい場合、
もう少し細かく、受注営業所ごとに商品区分の集計をしたい場合、
このように、最終的な完成イメージを想像しましょう。紙に書いてみるのも良いですね。
ここまでは行に項目がある形ですが、列に項目を並べる下のような形も考えられます。
集計の方法も、合計だけではなく、平均など他の集計も使えますよ。（その③で説明します）
このように、最後に欲しい情報を表の形として考えることが大切です。
イメージが何となくでも浮かんだら操作開始です。
まず、ピボットテーブルを作成したい表内のどこかをクリックして、
［挿入］タブ → ［ピボットテーブル］の上部分をクリック。
［ピボットテーブルの作成］ダイアログボックスが表示されます。この画面では、ピボットテーブルの元となるデータ範囲と、作成場所を決めます。
［表または範囲を選択］の場所を見て、データ範囲にカーソルを置いていた表全体が選択されているのを確認しましょう。
選択された場所は表の外枠が点線で囲まれるので、そちらで確認した方が早いかもしれませんね。
もし正しい場所が選択されていないようであれば、最初に自分でピボットテーブルに使用する表全体を選択しておく必要があります。
下にある、［ピボットテーブルを配置する場所を選択してください］の場所で、
ピボットテーブルを作成する場所を、新規ワークシートにするか、既存の（同じ）ワークシートにするかを選ぶことができます。
ほとんどの場合は、新規ワークシートにすると思います。
長々と説明しましたが、ほとんどの場合この画面は何も考えず、そのまま［OK］をクリックするだけで良いでしょう。
※画面最下部に［このデータをデータモデルに追加する］という項目がありますが、チェックを入れると複数のデータを統合し、広範囲な分析ができるようになります。ここでは詳しい説明は割愛します。
今回も何も変更しないでそのまま［OK］をクリックします。すると、下のような画面が新規ワークシートに現れます。※元の表は消えていません。
①ピボットテーブルツール
［オプション］タブと［デザイン］タブの２つありますが、ほとんど設定では［オプション］タブしか使用しません。［デザイン］タブは見た目の色などを変更する場所です。
②ピボットテーブル作成場所
この場所にピボットテーブルが作成されます。
③フィールドリスト
項目のリストが表示され、下の場所にフィールドを移動することでピボットテーブルが完成します。
右側に表示される、③のフィールドリストは大切な場所です。
ピボットテーブル以外の場所をクリックすると非表示になり、②の場所をクリックすると表示されます。
もし作成したピボットテーブルの中をクリックしても表示されない場合は、［オプション］タブの右側の［フィールドリスト］をクリックして表示しましょう。
拡大するとこんな感じ。
では、ピボットテーブルを作成しましょう。
作成には、右側に表示された［ピボットテーブルのフィールドリスト］を使います。
上部に表示された表のフィールド（列）を下の４つの場所に引っ張ることで作成します。
色分けすると下のようになります。４つの場所が、表の全体像と一致しているのに注目です。
大切なのは、緑枠、青枠、オレンジ枠の３か所です。紫枠の［レポートフィルター］は使い方が少し異なるため今回は説明から省きます。
どこから設定しても良いのですが、おすすめは「値」の部分からです。
「値」には集計したい「数値」のフィールドを設定します。簡単に表現すると、数字が入力されている場所。
今回の場合は「数値」の場所は「金額」フィールドしかありません。
右側の画面で、その「金額」フィールドにマウスを移動すると、マウスカーソルが矢印十字になります。
動かせる合図なので、そのまま右下の「値」のフィールドまで引っ張ります。
すると、左側に「金額」フィールド全ての合計が表示されます。また、右側の画面では、使用した「金額」の場所にチェックが入ります。
元の表で考えると、下の青枠部分の集計が表示されたということ。
ちなみに、単純に「合計」の場所にチェックを入れるだけでも同じ結果になります。
- 数値が入っているフィールドにチェックを入れると、「値」の場所に入る
- 数値以外のフィールドにチェックを入れると「行ラベル」の場所に入る
操作としては、チェックを入れた方が早いのですが、ピボットテーブルの仕組みを考えながら、思った通りのものを作成するには、自分で必要な場所にフィールドを引っ張っていった方が良いと思います。
さて、金額の合計が表示されたのですが、今回は「受注営業所」ごとの合計が欲しいとします。
完成を想像しましょう。
下の図のように各営業所を行でまとめたいな・・・。
列でまとめる場合は下のようになるかな・・・。
という感じで、何となくでも構わないので完成する形を想像しましょう。
今回は上の行でまとめる形でいきます。ということは、「行ラベル」の場所に「受注営業所」が来て欲しいということですね。
「受注営業所」フィールドを、「行ラベル」の場所に引っ張っていくと、
「受注営業所」が行ごとに集計されました。
元の表で考えると、下の青枠の場所で、「受注営業所」ごとにグループ化された「金額」が集計されたということ。
ちなみに、「列レベル」の方に引っ張ると、
下のように集計されます。
変更したい場合や修正したい場合などは、右側で対象の場所のチェックを外せば設定を戻すことができます。
または、［ピボットテーブル分析］タブ → ［クリア］をクリックして、
［すべてクリア］を選べば、設定が全て初期化されます。
次に、先ほどの「受注営業所」ごとに集計したものを、「受注日」ごとの集計に修正してみましょう。
「受注営業所」のチェックを外してから、［受注日」を「行ラベル」に引っ張っていけば簡単に修正できます。
修正されました。
このように、簡単に集計場所を変更できるのがピボットテーブルの１番の特徴です。
左側の画面を見ながら、右側のフィールドを色々下に引っ張って・・・。
うーーーん。これも悪い方法ではなくピボットテーブルの一つの活用方法だと思うのですが、適当にフィールドを移動していては、思ったような表にたどりつくには時間がかかります。
やはり、最終的な表の形を先にイメージしてから作業することが大切だと思います。
色々なフィールドの組み合わせを試す
ピボットテーブルでの集計は１つのフィールドを使用するだけではなく、複数のフィールドを組み合わせることができます。
これこそピボットテーブルの真骨頂。
言葉で説明するより、画面で確認した方が分かり易いでしょう。
先ほどの集計結果が下の画面。各営業所ごとに集計されています。これを、「商品区分」の「猫缶」と「かりかり」の別々に集計してみます。
ちなみに、「商品区分」は元の表の青枠部分です。「猫缶」と「かりかり」に分かれていますね。
イメージは下の画像の感じ。それぞれの営業所の右側に追加したいと思います。つまり行ラベルが２つ横に並ぶことになります。
では、やってみましょう。やり方は簡単。「商品区分」を、［行ラベル］の「受注営業所」の下に持っていくだけです。
できました。「かりかり」と「猫缶」が各営業所ごとに集計されました。右下を見ると、［行ラベル］に２つのフィールドが並んでいますね。
２つ以上を並べた場合、上下の順序がまとめる順番になります。
その順序も簡単に変更できます。［行ラベル］の「受注営業所」か「商品区分」を引っ張って上下の順番を逆にすると・・・
このように、「商品区分」の中で営業所の集計が行われました。
上下の順番で、表の集計方法も変わるということです。
上にある方が最初のまとまりになり、左側に置かれることになります。
また、営業所ごとに商品区分を分けるには、下のような分け方も考えられます。
行を横に並べて集計していくのではなく、列に分けて集計するということ。
下の画面で考えると、［列ラベル］に「商品区分」を置けば良いことが分かります。
では、やってみましょう。
先ほどの画面から、「商品区分」を［列ラベル］に引っ張っていくだけ。このように、下のボックス画面での移動もできます。
下のようになります。
このように、各フィールドを下のボックスに自由に動かすことで、複雑な集計も簡単に行うことができます。
データの更新
元の表のデータを訂正しても、ピボットテーブルには自動で反映されません。元となる表のデータを変更した場合は更新作業をしましょう。
［ピボットテーブル分析］タブ → ［更新］をクリックするだけです。
まとめ
ここまでが、ピボットテーブルの基本的な使い方です。
「あれ？下のボックス左上の紫部分を使用していないけど」と思った方。
紫の部分は、データの抽出で使用します。他の３か所とは少し意味合いが違うので次の説明で紹介します。
まずは、青枠・緑枠・オレンジ枠の場所の意味をしっかりマスターしましょう。
ピボットテーブルを普段から使えるようになるには、細かい操作方法を覚える事よりも、どのような集計結果が欲しいのか、しっかり把握することだと思います。
そして、ピボットテーブル作成画面の下の場所が、
表の各場所に対応していることを覚えておきましょう。
次 → ピボットテーブルの使い方 ②｜並び替え、フィルター（抽出機能）やスライサーの使い方
続きも見て欲しいにゃ