今までピボットテーブルを使ったことがない方向けに基本操作をご紹介します。
ここでは、Excel 2007 で操作しますので、使用しているバージョンが異なる場合は、
少し操作画面が違いますが、どういうものかを知りたい方はご覧になってみてくださいね。
ピボットテーブルを作る前に、準備しておかなければならないことがあります。
ピボットテーブルは、どこかに用意されているデータを「ピボットテーブル」という場所を
使って集計する機能です。
そのため、ピボットテーブルを作ることができるように、なおかつ、作ったときに
正しい結果が得られるように集計したい元データをちゃんと整えておく必要があります。
たとえば、下図のような表は NG です。
1 行目の項目名のセルが結合されていたり、同じデータのはずなのに同じ形式で
入力されていなかったりするからです。
この表を元にピボットテーブルを作ろうとしても、まず、1 行目の項目名が結合されている時点で
エラーになって先に進めません。
また、1 行目を修正しても、「商品A」と「商品 A」は違うデータとして扱われるため、
きっと思うような集計結果が得られません。
ピボットテーブルを使って集計をするには、まず、その元データがきちんと、
いわゆるリスト形式で整えられているかがとても大切です。
■表の 1 行目に項目名はありますか？
■表の項目名が 2 行になっていたり、セルが結合されていたりしませんか？
■列内の同じデータを表わすデータは同じ形式で入力されていますか？
(NG 例：「商品A」と「商品 A」などが混在している)
集計したい元データが整ったら、ピボットテーブルを作成します。
1.ピボットテーブルで集計をしたいデータが入力されているリスト (表、テーブル) の中の
任意のセルをクリックして、[挿入] タブの [ピボットテーブルの挿入] をクリックします。
下図では、表の中の「No」と入力されているセル B3 を選択して操作しています。
2.[ピボットテーブルの作成] ダイアログ ボックスが表示されます。
このダイアログ ボックスでは、
「集計や分析をしたいデータはどこにあるか」
「ピボットテーブルをどこに作りたいか」
を指定します。
3.[ピボットテーブルの挿入] をクリックする前に選択していたセル (B3) を基準に
自動的にデータの入力されているセル範囲が認識され、[テーブル/範囲] ボックスに
セル範囲が指定されています。
集計や分析をしたいデータが入力されているセル範囲が間違っている場合や、
変更したい場合は、セル範囲を指定しなおします。
ボックスの中に入力されているセル範囲を消して、ドラッグしなおせば指定されますよ。
元データが入力されているセル範囲がテーブルの場合は、セル範囲の代わりに
テーブル名が表示されます。
ここでは、「出荷データ」シートのセル B3 から E23 を指定しているので、
「出荷データ!$B$3:$E$23」と表示されています。
4.新しいシートを追加して、そこにピボットテーブルを作りたい場合は、
[新規ワークシート] を選んで [OK] をクリックします。
あらかじめブック内に用意してあるシートにピボットテーブルを作りたいのであれば、
[既存のワークシート] を選んでシート名を選択してください。
ここでは、新しいシートにピボットテーブルを作成します。
5.新しいシートが追加され、空の (何を集計するかが決まっていない) ピボットテーブルが
作成されます。
ピボットテーブルの中をクリックすると、リボンに [ピボットテーブル ツール] が
表示され、[オプション] と [デザイン] という 2 つのタブが表示されます。
集計をする準備ができた、というところでしょうか。
ピボットテーブルの作成直後など、ピボットテーブルの中をクリックして選択すると、
画面の右側に [ピボットテーブルのフィールド リスト] というウィンドウ (領域) が
表示されます。
(表示されていない場合は、リボンの [オプション] タブの [フィールド リスト] をクリックして
表示してください)
このフィールド リストに並んでいるのは、ピボットテーブルを作成するときに選択した
元データ (セル範囲) の 1 行目にある列の項目名です。
こんな風に利用するために、最初にちゃんと元データの表を整える必要があったのです。
「フィールド」というのは表の「列」のことです。
ピボットテーブルでは、「どの列のデータを集計するか」をこのリストから選びます。
ピボットテーブルの中に、
「レポートを作成するには、[ピボットテーブルのフィールド リスト] からフィールを
選択してください」
と書いてあります。
"フィールドを選択する" ＝ "列を選ぶ" には、フィールド リストで
選択するフィールド名のチェック ボックスをオンにします。
ここでは、[商品名] を選びます。
6.フィールド リストの下部にあるエリア セクションの [行ラベル] と書かれている領域に
[商品名] と表示され、ピボットテーブルの中に商品名が表示されます。
[行ラベル] に配置されているので、1 つの行 (横方向) に 1 つの商品名、というように
表示されています。
イメージとしては下図のような感じでしょうか。
元データの表の [商品名] の列のデータがすべて表示されるのではなく、
種類別にまとめられて、どんな種類があるかが表示されているという感じです。
「集計や分析なんかしてないじゃない」と思うかもしれませんが、
これも立派なピボットテーブルです。
たとえば、表の列の中にどんなデータがあるかの一覧が欲しいというような場合、
このピボットテーブルの中身をコピーすれば一覧を作れますね。
そんな使い方もありだと思います。
元データのリストの商品名の 1 つを「商品C」から「商品D」に変更します。
ここでは、データの量を増やすのではなく内容を変更しているということです。
元データを編集したあと、ピボットテーブルを確認しても「商品D」が追加されていません。
ピボットテーブルは自動的には更新されないのです。
1.ピボットテーブルの中をクリックして、リボンの [オプション] タブの [更新] を
クリックします。
ピボットテーブルが更新されて「商品D」が表示されます。
[更新] がクリックされたら、ピボットテーブルの元データが入力されているセル範囲を
確認しにいって、そのとき元データの範囲にある「商品名」を表示するということです。
元データの表を変更しただけでは、自動的にはピボットテーブルの内容は変わらないのです。
フィールド リストの下部にあるエリア セクションには、「レポート フィルタ」、「列ラベル」、
「行ラベル」、「値」の 4 つのセクションがあります。
全部のエリアを利用しなければいけないということはなく、
どこにどんな情報を表示したいのか、どんな集計をしたいのかによって、
適切なエリアにフィールドを配置することで使い分けます。
いま「商品名」は「行ラベル」に配置されていますが、これを別のエリアに移動すると
ピボットテーブルはどのように変わるのかを確認し、どんなときにどこにフィールドを
配置すればよいのかをイメージしてみましょう。
1.[行ラベル] エリアに配置されている「商品名」フィールドを [列ラベル] エリアへ
ドラッグします。
[列ラベル] エリアに [商品名] と表示され、1 つの列 (縦方向) に 1 つの商品名、と
いうように表示されています。
2.[列ラベル] エリアに配置されている「商品名」フィールドを [値] エリアへ
ドラッグします。
[値] エリアに [データの個数/商品名] と表示され、[商品名] 列に何個のデータが
含まれているかが表示されます。
このエリアには、個数を数えたり、数値を合計したりしたいフィールドを配置します。
[商品名] 列には「商品A」などの文字列が入っているので合計はとれませんから、
自動的に個数が表示されます。
3.[値] エリアに配置されている「商品名」フィールドを [レポート フィルタ] エリアへ
ドラッグします。
[レポート フィルタ] エリアに [商品名] と表示され、ピボットテーブルの左上に
[商品名] のドロップダウン リストが配置されます。
しかし、今の状態だとこのドロップダウン リストは意味がありません。
このエリアは特別で、ピボットテーブルに表示されているデータをフィルタするときに
使用します。ほかのデータが何も表示されていないのだから、現状ではフィルタも何もありません。
最初にピボットテーブルに [商品名] フィールドを追加したとき、フィールド リストの
チェック ボックスを使ってフィールドを選びました。
操作に慣れるまではチェック ボックスで選んでから、目的のエリアに移動すればよいのです。
どこに配置するとどこにどのような結果が表示されるのかがわかるようになったら、
フィールド リストから直接、ドラッグ アンド ドロップでエリアへフィールドを
配置してもよいです。後から移動しなくてよい分、少しだけ操作の効率があがりますね。
操作の効率があがるだけではなくて、ドラッグ操作でなければできないこともあります。
これは後で出てきます。
間違ったフィールドを選んでしまったときや、不要なフィールドがピボットテーブルにあるときは、
そのフィールドをピボットテーブルの外へ出して除外します。
除外するには、フィールド リストのチェック ボックスをオフにするか、
エリア セクションに配置されているフィールドを外へ向かってドラッグします。
前の手順で、フィールドをどのエリアに配置すると、ピボットテーブルに
どのように表示されるのかを確認しました。
たとえば、[行ラベル] エリアに [商品名] を配置すると、商品名が行の先頭に表示され、
[値] エリアに [商品名] を配置すると商品の個数が表示されましたね。
ということは、、、この 2 つを組み合わせると商品ごとの個数を表示できるということです。
フィールドは、どこか1 か所のエリアにしか配置できないというわけではないのです。
[行ラベル] エリアに [商品名] を配置して、[値] エリアにも [商品名] を配置してみましょう。
こんなときに、ドラッグ操作でエリアにフィールドを配置します。
1 つ目はチェック ボックスで指定できるけれど、2 つ目はドラッグしないと
配置できませんからね。
商品ごとのデータの個数が表示されます。
元データの表に、どの商品がいくつ含まれているかがわかるピボットテーブルになりました。
長くなったので今回はここまで。
ピボットテーブルは操作が難しい機能ではありませんが、データのある場所と集計結果を
表示する場所が異なり、少しだけ言葉が難しいだけです。
まずは、元のデータをしっかり整えることと、どこに何を置いたらどうなるかといった
基本的な動きを知っていただければよいと思います。
ピボットテーブルに関しては、何回かに分けて書いていきますね。
石田かのこ
