ピボットテーブルとは、簡単に言えば、縦横2方向に項目を並べて集計する表のことです。
しかし、Excelでピボットテーブルの集計方法を練習するときには、いきなり2方向を考えてはいけません。適当に縦横2方向に項目を並べて集計できればいいという、中途半端な理解の仕方をしてしまうと応用力が身につきません。
まずは、縦方向だけの集計(行集計、単純集計ともいいます)をしっかりと練習した後で、1方向と2方向では何が違うのかを意識しながら縦横2方向の集計表を作るべきです。そこで、今回は、1方向のみの集計から、縦横2方向の集計に発展するまでの流れを解説します。
目次
- 0.補足説明:ピボットテーブルとクロス集計の違い
- 1.データの個数
- 2.値フィールドの設定
- 3.行のフィールドと値のフィールドに分ける
- 4.クロス集計とクリア
- 5.まとめ:列が増えるパターン
- 6.ピボットテーブル練習問題・動画解説
- 付録1 サンプルのファイルはこちら
- 付録2 動画版はこちら(無料)
0.補足説明:ピボットテーブルとクロス集計の違い
前述の通り、ピボットテーブルとはExcelの集計機能の一つであり、縦方向だけの行集計や、縦横2方向のクロス集計ができます(※横方向だけの列集計も可能です)。
ピボットテーブル=クロス集計のイメージがありますが、そうではありません。まず、ピボットテーブルで行集計の練習をしてから、クロス集計の練習をするようにしましょう。
1.データの個数
(1)ピボットテーブルによるカウント
問題
複数の受験者が、複数の科目の試験を受けたので、その得点を入力した。
ピボットテーブルを用いて、次のような受験番号のリストを表示しなさい。また、受験番号がそれぞれ何個ずつあるか数えなさい。
解説
はじめにピボットテーブルを作ります。リストを全部選択します。ピボットテーブルを挿入します。
このまま完了します。
受験番号を「行」のフィールドにドラッグします。
これで受験番号のリストが表示されます。受験生は全部で10人です。
さらに、受験番号を「値」のフィールドにもドラッグします。
それぞれの個数が表示されます。例えば、1A001の受験生は7回テストを受けています。
このように、(原則として)同じ項目を、行のフィールドと値のフィールドの両方にドラッグをするとそれぞれの個数をかぞえることができます。
(2)データの個数とは何か
問題
次のような科目のリストを表示しなさい。また、各科目の受験者数(のべ人数)を求めなさい。
解説
受験番号を「行」からなくします。(左にドラッグすれば消えます)
55というのは、元のリストが全部で55行あるということです。「行」のフィールドが無い場合、元のリストのレコードの件数(行数)を数えます。
科目を「行」のフィールドにドラッグします。
今度は科目ごとの行数になります。
全部で6科目の試験が行われ、国語の試験はのべ13人が受験しています。
しかし、さきほどの問題で、受験生は10人しかいないことがわかっています。つまり、同じ受験生が国語の試験を2回以上受けているということがわかります。
「値」のフィールドにドラッグした受験番号を削除します。
その代わりに科目を「値」のフィールドにドラッグします。
結果は同じです。
「値」のフィールドに入れるとデータの個数と表示されます。このデータの個数とは、レコードの数のことであり、行数のことです。
A列の受験番号であっても、B列の科目であっても、科目ごとの行数を数えていますから結果は同じです。原則として、「値」のフィールドに何を入れてもデータの個数は変わりません。ただし、通常は、コード番号(今回の場合は受験番号)を使ってカウントします。
(3)重複を見つける
問題
科目別で、それぞれの受験生が何回受験したかを求め、次のような表を作成しなさい。
解説
受験生が10人しかいないのに、国語の受験者が13人いることから、誰が2回受験したかを探します。
受験番号を科目の下にドラッグします。
科目別の受験番号のリストになります。同じ科目の試験を2回受けている受験生がいることが分かります。このように、重複があるものを見つけることができます。
完成イメージは「表形式」のレイアウトです。また、小計と総計がない形です。
それぞれ設定を変えます。
2.値フィールドの設定
(1)値フィールド
問題
複数の受験者が、複数の科目の試験を受けたので、その得点を入力した。
受験番号のリストを作り、受験した回数(行数)を求めようとしたところ、次の図のようになった。このようになった理由と直す方法を述べなさい。
解説
ピボットテーブルを挿入します。
受験番号を、「行」のフィールドにドラッグします。
これで受験番号のリストが表示されます。受験生は全部で10人です。
受験番号を「値」のフィールドにドラッグします。
これで受験した回数が求められます。これは正しい操作です。
今度は、得点を「値」のフィールドにドラッグします。
得点の合計になります。
得点の、「値フィールドの設定」を開きます。
合計になっています。
これをデータの個数に変えます
受験回数になります。
ピボットテーブルで、文字列のフィールドを「値」にドラッグすると、データの個数になります。
数値のフィールドを「値」にドラッグすると、合計になります。得点は数値なので合計になります。
これを直すには、「値フィールドの設定」を変えます。
(2)値のフィールドを2つにする=集計方法を2つにする
問題
同じ受験者が同じ科目を2回以上受験している。次のように受験回数と合計点を求めなさい。
解説
「行」のフィールドに科目を追加します。
これで受験番号別・科目別の回数になります。2回以上受験している受験生がいます。
さらに、得点を「値」のフィールドに入れます。
これで回数と合計の両方が表示されます。
「値」のフィールドを2つにすることによって、カウントと合計を2つ表示することができます。
(3)最大値と平均
問題
同じ受験者が同じ科目を2回以上受験した場合には、得点の高いほうを採用することにした。受験回数と得点を求めなさい。
また、平均点を採用する場合はどうすればよいか。
解説
2回の合計だと200点満点になり比較することができません。2回以上受験した場合に得点の高いほうを表示するときは、最高点を求めます。
合計の「値」のフィールドの設定を変えます。
最大値に変えます。
これで最高点を採用したことになります。
得点の「値」のフィールドの設定を変えます。
今度は平均にしてみます。
2回受験した場合はその平均点になります。
3.行のフィールドと値のフィールドに分ける
問題
次のリストから、ピボットテーブルを用いて完成イメージのような表を作りなさい。なお、数量は売上数量、売上は売上金額のことである。
<完成イメージ>
解説
売上数量はF列を集計すればよいですが、売上金額は単価*数量を求める必要があります。
ピボットテーブルを作るときには、「行」のフィールドと「値」のフィールドに分けて考えます。
合計や個数など集計をするものが「値」のフィールドです。
この場合は、分類、コード、商品名が、「行」のフィールドで、数量と売上が、「値」のフィールドです。
ピボットテーブルを挿入します。分類、コード、商品名を「行」のフィールドに、数量と売上を「値」のフィールドに、それぞれドラッグします。
数量と売り上げはもう一度ドラッグして4列にします。
次に、「行」のフィールドについて3つのレイアウト(コンパクト・アウトライン・表形式)から適切なものを選びます。また、集計行が必要かを決めます。
「値」のフィールドについては、集計方法を考えます。
レイアウトは表形式です。
そして、小計だけ消します。
数量と売り上げの合計になっているのを、平均に変えます。
これで完成です。
4.クロス集計とクリア
(1)列のフィールド=内訳をみる
問題
次のリストで、同じ受験者が同じ科目を2回以上受験した場合、得点の高いほうを採用する。各設問の操作をしなさい。
次のような表を作りなさい。
解説
はじめにピボットテーブルを作ります。ピボットテーブルを挿入します。このまま完了します。
受験番号を、「行」のフィールドと「値」のフィールドにそれぞれドラッグします。
これで受験番号ごとの受験回数が出ます。
さらに、科目を、「列」のフィールドにドラッグします。
科目ごとに何回受験したかがわかります。
「列」のフィールドに何も入れなければ、各受験生の受験回数になります。例えば、1A001の受験生は7回テストを受けています。
「列」のフィールドに科目を入れることで、ピボットテーブルが横に広がり、科目ごとの内訳がわかります。このように、「列」のフィールドを使うと、内訳をみることができます。
例えば、1A001の受験生が受験したのは5教科で、合わせて7回テストを受けていることが分かります(1+2+2+1+1=7)。
縦に受験番号、横に科目を並べて、それぞれ分けて集計をすることができます。
いっぱんに、複数の項目をグループとして集計することを「クロス集計」といい、2つの項目の場合は縦と横に並べて、2次元の集計表を作ります。
(2)ピボットテーブルのクリア
問題
ピボットテーブルをクリアしなさい。
解説
ここでピボットテーブルツールの中から、クリア、すべてのクリアを選んで、ピボットテーブルをクリアします。
フィールドをすべてなくしてリセットするには、「すべてクリア」をします。画面の幅によってはボタンが「アクション」と表示されることがあります。
(3)クロス集計の練習
問題
次のような表を作りなさい。
解説
受験番号を、「行」のフィールドにドラッグします。
得点を、「値」のフィールドにドラッグします。
これで受験番号ごとの得点の総合計が出ます。
科目を、「列」のフィールドにドラッグします。
これで科目ごとに分かれます。
ここで、レイアウトを表形式にして、総計を非表示にします。
得点が3桁になっているのは、受験したテストの合計になっているからです。
今回は、得点の高いほうを採用するので、値フィールドの設定を変えて、最大値にします。
これで完成です。
(4)値のフィールドの順番
問題
さらに、次のような表にしなさい。
解説
「値」のフィールドに受験番号をドラッグします。
このとき「値」のフィールドの順番を、受験番号、得点の順にします。項目の順番はドラッグで変えることができます。
それぞれの列が2列になってそれぞれ受験回数と得点が求められます。
5.まとめ:列が増えるパターン
ピボットテーブルの列が増える(横方向に伸びる)パターンは2つあります。
- 集計方法(カウント・合計・平均など)を増やす場合は、集計したい列を「値」フィールドに入れます。
- 項目ごとに内訳を見たい場合は、その項目を「列」のフィールドに入れます。
6.ピボットテーブル練習問題・動画解説
さらに、こちらの記事でピボットテーブル(クロス集計)の練習ができます。
解説は以上です。
*補足*
日本の企業では、列集計(横方向に項目を並べて集計する方法)を基本とする場合が多いですが、Excelは、行方向(縦方向)が第1次元を表すので、行集計を基本として練習をします。
付録1 サンプルのファイルはこちら
わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお使いください。7-23 ~ 7-25のサンプルファイル(ZIP)です。
ファンダメンタルExcel_Program 7. データベース | わえなび [waenavi] the Theory of Word-Excel
付録2 動画版はこちら(無料)
この記事は、わえなびファンダメンタルExcel Program7-23 ~ 7-25 の動画の内容を書き起こし、加筆修正したものです。
- ファンダメンタルExcel 7-23 ピボットテーブルの基本3(データの個数)
- ファンダメンタルExcel 7-24 ピボットテーブルの基本4(値フィールドの設定)
- ファンダメンタルExcel 7-25 ピボットテーブルの基本5(クロス集計とクリア)
動画版(完全版)は、Youtubeにすべて無料で公開しております。ぜひ、ご覧ください。
ファンダメンタルExcel 7 データベース(全28回)【わえなび】 - YouTube
https://www.youtube.com/playlist?list=PLRaY8kd5CoxOnpijKZLSbMRv37GQXv1Ko