Excelのピボットテーブルを使いこなす。単純集計法とクロス集計法のやり方
Excelでデータの集計や分析を行うとき、ピボットテーブルは鉄板のツールです。しかし、我流で使っている方も多いかもしれません。
ピボットテーブルで効率よく確実にデータを扱うには、基本の操作や用語から学ぶのが一番。そのための教科書としておすすめなのが、『Excelピボットテーブル データ集計・分析の「引き出し」が増える本 第2版』（翔泳社）です。
今回は本書から、ピボットテーブルの土台作りと、集計の基本となる単純集計法とクロス集計法のやり方を紹介します（第1章 集計の基本と定番パターン）。
Excelでもっと進んでデータ集計＆分析をしたい方や、ピボットテーブルの使い方を知りたい方、そしてなんとなく使えている方もよりうまく使いこなす第一歩として、ぜひ参考にしてみてください。
◆著者について
木村 幸子（きむら・さちこ）
フリーランスのテクニカルライター。電機メーカーのソフトウェア部門においてマニュアルの執筆、制作に携わる。その後、パソコンインストラクター、編集プロダクション勤務を経て独立。現在はMicrosoft Officeを中心としたIT系書籍の執筆、インストラクションで活動中。近著に『Excelデータ分析の「引き出し」が増える本』など。
◆本書の目次
序章 これだけは知っておきたいピボットテーブルの仕組み
第1章 集計の基本と定番パターン
第2章 データの不備をなくすには
第3章 集計の応用テクニックいろいろ
第4章 「階層」を使いこなして活用の幅を広げる
第5章 ピボットテーブル分析の基本
第6章 分析に役立つ視覚化テクニック
第7章 ここで差がつく！応用的な分析手法（ケーススタディ）
第8章 ピボットテーブルを高度に活用する（PowerPivot）
ピボットテーブルの土台を作成する
リストをもとにして、ピボットテーブルを作成しましょう。いきなり集計表まで一気に作るのではなく、ここではまず、集計表の土台となる部分までを作ります。
集計する前にピボットテーブルの土台を作る
ピボットテーブルを作成する手順は2段階に分かれます。第1段階では、ピボットテーブルを表示するシートを用意し、集計表作りの準備までを済ませます。そして第2段階で、行や列の見出しを指定したり、集計する数値データを選んだりして、集計表の設計を行います。
STEP 1 ピボットテーブルを新規作成する
リスト内の任意のセルをクリックし、「挿入」タブの「ピボットテーブル」をクリックします（図1）。
「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示され、同時に、リストの周囲が点滅する枠で囲まれます。
さらに、「テーブル/範囲」の欄に、リスト範囲を表すセル番地が自動的に表示されます。これは、Excelがリストの範囲を自動で認識するためです。ただし、リストの途中に空行が入っていたりすると、この範囲が正しく認識されません。
STEP 2 ピボットテーブルの配置場所を指定する
次に、ピボットテーブルを配置する場所を指定します。初期設定では、リストとは別に新規シートが挿入され、そこにピボットテーブルが作られます。「新規ワークシート」が選択されているのを確認して、「OK」をクリックします（図2）。
新規シートが追加され、シートの左にピボットテーブルが配置される領域が表示されます。シートの右には、「ピボットテーブルのフィールド」作業ウィンドウが表示されます（図3）。
集計に必要なフィールドを配置する
ピボットテーブルのベースとなる部分が作成されたら、続けてピボットテーブルの本体である集計表を作りましょう。集計表を作るには、必要なフィールドをドラッグ操作で配置します。
ピボットテーブルの完成形を確認しておこう
操作を進める前に、これから作るピボットテーブルのレイアウトをあらかじめ確認しておきましょう。
ここでは、商品名と販売エリアの2つの内容で売上金額を合計する「クロス集計表」を作ります（図4）。クロス集計表の目的や役割については、のちほど紹介します。
具体的には、行ラベルに「商品名」フィールドを配置し、列ラベルには「販売エリア」フィールドを配置します。さらに、「値」に「金額」フィールドの合計を求めます。では、実際の操作方法を見ていきましょう。
STEP 1 行ラベルに「商品名」を追加する
まず、行ラベルに商品名の一覧を表示させましょう。「ピボットテーブルのフィールド」作業ウィンドウの上部にあるフィールドセクションで、「商品名」にマウスポインターを合わせて、下部のエリアセクションにある「行」ボックスまでドラッグします（図5）。
「行」ボックスに「商品名」と表示され、シートのA列には、商品名が縦に一覧表示されます（図6）。
STEP 2 列ラベルに「販売エリア」を配置する
次に、列ラベルに「販売エリア」フィールドの内容を表示しましょう。 「ピボットテーブルのフィールド」作業ウィンドウのフィールドセクションで「販売エリア」にマウスポインターを合わせ、エリアセクションの「列」ボックスまでドラッグします。これで、列ラベルに「東京都内」、「南関東」、「北関東」と販売エリアが表示されます（図7）。
STEP 3 「値」に「金額」を配置する
最後に、「値」ボックスに「金額」フィールドを指定します。
「ピボットテーブルのフィールド」作業ウィンドウのフィールドセクションで「金額」にマウスポインターを合わせ、エリアセクションの「値」ボックスまでドラッグします。
すると、金額の合計が自動的に計算されます（図8）。これでクロス集計表ができあがりました。
なお、エリアセクションの「行」、「列」、「値」の各ボックスにフィールドをドラッグする順番はこの通りでなくても、ピボットテーブルを作成できます（フィールドを間違えて配置してしまった場合は、ボックスの外へドラッグすれば削除できます）。
「単純集計表」を手軽に活用しよう
ピボットテーブルで作成する集計表には、2種類の基本パターンがあります。まずは、見出しが縦方向、横方向のいずれか片方だけに設定された「単純集計表」についてここで理解しましょう。
単純集計表とは
ピボットテーブルで作成する集計表には、大きく分けて、「単純集計表」と「クロス集計表」の2種類があります。それぞれの違いをまず理解しましょう。
「単純集計表」とは、項目見出しが縦、横のどちらか片方だけに設定された集計表のことです。1つの項目を基準にして売上金額や数量を集計したい場合に作成します。
実例を見てみましょう。図9では、左の列に商品名の一覧を表示して、右の列にそれぞれの商品の売上金額の合計を求めています。
これを見れば、「カップ麺詰め合わせ」や「カフェオーレ」といった商品ごとの合計金額がわかります。
このように、単純集計表では、項目見出しと集計された数値が1対1で対応します。
もう1つ、単純集計表の例を見てみましょう。図10では、左の列に支社の一覧を表示して、右の列にそれぞれの支社の販売数量を合計しています。
これも、項目見出しが縦方向だけに設定されているので単純集計表になります。
項目見出しは横方向に設定しない
単純集計表では、「商品名」や「支社名」といった項目見出しを、縦か横のうちどちらか片方に設定します。ところが、横方向に項目を設定すると、図11のように、表が極端に横長になってしまいます。このような表は印刷や表示がしづらく、実用的ではありません。実際には、縦方向に項目見出しを設定することになります。
また、金額などの数値は縦1列に並んでいないと、上下に見比べて桁を比較しづらくなります。その意味でも、単純集計表では項目見出しが縦に並ぶレイアウトにするのがおすすめです。
ピボットテーブルで「単純集計表」を作るには
では、実際にピボットテーブルで単純集計表を作るには、どのように設定すればいいのでしょうか。ここでは、2つの単純集計表を作る方法を紹介します。
商品別に金額の合計を求める単純集計表
図9で紹介した集計表では、商品名を左の列に、金額を右の列に表示しています。この表をピボットテーブルで作る場合は、図12のようにフィールドを設定します。
ピボットテーブルでは、縦方向の見出しは「行ラベル」の領域に配置します。この表では、商品名を縦方向の見出しにするため、エリアセクションの「行」ボックスに「商品名」フィールドを配置します。これでピボットテーブルでは「行ラベル」に商品名が一覧表示されます。
また「値」ボックスに「金額」フィールドを配置すると、表示が「合計/金額」に変わり、ピボットテーブルでは、商品名の右隣の「値」のエリアに、金額の合計が表示されます。
支社別に数量の合計を求める単純集計表
今度は、図10で紹介した、支社ごとに数量の合計を求める集計表を作る場合を考えましょう。
支社名を縦方向の見出しにするため、エリアセクションの「行」ボックスに「支社名」フィールドを配置します。また「値」ボックスに「数量」フィールドを配置します。
これでピボットテーブルでは「行ラベル」に支社名が一覧表示され、支社名の右の「値」のエリアに、数量の合計が表示されます（図13）。
ピボットテーブルの主流「クロス集計表」を覚えよう
ピボットテーブルのもう1つの基本形である「クロス集計表」とは、縦、横の双方に見出しを持つ集計表のことです。商品と支社のように、2つの内容を基準にして、売上金額などを集計したいときに使います。
クロス集計表とは
「クロス集計表」とは、縦軸と横軸の両方に項目見出しを配置した集計表のことです。縦軸か横軸かの片方だけに項目を置く単純集計表では、基本的に1つの内容に対する集計結果を表示します。
一方、クロス集計表では、縦と横の2方向に見出しを配置するので、「商品名」と「支社名」、「顧客名」と「支社名」といったように2つの内容をもとにした集計結果を表示できます。
図14では、縦軸に商品名が、横軸に支社名が表示されています。そして、それぞれの商品や支社に対応する内容の金額が、行と列の交差する位置にあるセルに合計されます。たとえば、「横浜支社」が販売した「ミネラルウォーター」の売上金額を知りたいときは、それぞれの見出しが交差する位置のセルを見れば「7,466,100」円だとわかります。
もう1つ、クロス集計表の例を見てみましょう。図15では、縦軸に顧客名が表示され、横軸には支社名が表示されています。そして、販売数の合計が、顧客名と支社名の交差する位置に表示されています。たとえば、「横浜支社」が「若槻自動車」に対して販売した商品数量の合計は「13,550」になることがわかります。
このように、クロス集計表では、行と列の項目が交差する（クロスする）位置にあるセルに該当する集計値が表示されます。
なお、図15で空欄のセルがあるのは、集計対象となるデータが存在しないためです。顧客ごとに担当の支社が決まっている場合、このタイプの集計表では空欄が多くなります。クロス集計表を作ると、このようにフィールド同士の関係もわかりやすくなります。
ピボットテーブルで「クロス集計表」を作るには
実際にクロス集計表をピボットテーブルで作成する例を見てみましょう。ここでは、2つのクロス集計表を作る方法を紹介します。
商品名・支社名ごとに金額を合計するクロス集計表
図14で紹介した集計表では、商品名を縦方向、支社名を横方向の見出しに設定し、金額の合計をクロス集計しています。この表をピボットテーブルで作る場合は、図16のようにフィールドを設定しましょう。
縦方向の見出しは「行ラベル」、横方向の見出しは「列ラベル」にそれぞれ配置します。したがって、エリアセクションの「行」ボックスに「商品名」フィールドを、「列」ボックスには「支社名」フィールドをそれぞれ配置しましょう。
また「値」ボックスに「金額」フィールドを配置すると、商品名、支社名に該当する金額の合計が「値」のエリアの交差する位置に表示されます。
顧客名・支社名ごとに数量を合計するクロス集計表
図15で紹介した集計表では、顧客名を縦方向、支社名を横方向の見出しにそれぞれ設定し、数量の合計をクロス集計しています。この表を作る場合は、図17のようにフィールドを設定します。
エリアセクションの「行」ボックスに「顧客名」フィールドを、「列」ボックスには「支社名」フィールドをそれぞれ配置します。さらに「値」ボックスに「数量」フィールドを配置すると、顧客名、支社名に該当する数量の合計が「値」のエリアの交差する位置に表示されます。
行ラベルと列ラベルはどう決める？
ピボットテーブルでクロス集計表を作る場合、縦方向の見出しは「行ラベル」に、横方向の見出しは「列ラベル」にそれぞれ指定します。
このとき、項目の数が多いフィールドや、長い名称の項目が多いフィールドを行ラベルに指定すると、コンパクトで見やすい集計表になります。
図18は、図16のピボットテーブルの行ラベルと列ラベルを入れ替えたものです。図16と比べると、横に間延びした印象を与えてしまいます。
図16では、行ラベルに「商品名」を、列ラベルに「支社名」を指定しました。項目の数が多く、長い名前も多い「商品名」フィールドは、行ラベルに配置した方がコンパクトな集計表になることがこの例からわかります。
Excelのピボットテーブルを使いこなす方法をあまさず解説
この記事が気に入ったらサポートをしてみませんか？