わえなび ワード&エクセル問題集

ワードとエクセルの練習問題の動画を「Youtube」で無料公開しています

【Excel】ピボットテーブルのクロス集計の基本は、行集計である

ピボットテーブルとは、簡単に言えば、縦横2方向に項目を並べて集計する表のことです。

f:id:waenavi:20181031055358j:plain

 

しかし、Excelでピボットテーブルを練習するときには、いきなり2方向を考えてはいけません。適当に縦横2方向に項目を並べて集計できればいいという、中途半端な理解の仕方をしてしまうと応用力が身につきません。

まずは、縦方向だけの集計(行集計、単純集計ともいいます)をしっかりと練習した後で、1方向と2方向では何が違うのかを意識しながら縦横2方向の集計表を作るべきです。

f:id:waenavi:20181031060510g:plain

そこで、今回は、1方向のみの集計から、縦横2方向の集計に発展するまでの流れを解説します。

 

目次

0.ピボットテーブルの基本

ピボットテーブルを練習するときは、集約→行集計→クロス集計の順で練習します。集計をする前に、まずは「集約」の仕方を理解しましょう。

 

1.データの個数

問題

ピボットテーブルを用いて、各設問の操作をしなさい。

f:id:waenavi:20181030183346j:plain

 

(1)次のような受験番号のリストを表示しなさい。また、受験番号がそれぞれ何個ずつあるか数えなさい。

f:id:waenavi:20181030183417j:plain

 

(2)次のような科目のリストを表示しなさい。また、各科目の受験者数(のべ人数)を求めなさい。

f:id:waenavi:20181030183425j:plain

 

(3)科目別で、それぞれの受験生が何回受験したかを求め、次のような表を作成しなさい。

f:id:waenavi:20181030183432j:plain

 

解説

(1)ピボットテーブルによるカウント

はじめにピボットテーブルを作ります。リストを全部選択します。ピボットテーブルを挿入します。

f:id:waenavi:20181030183629j:plain

 

このまま完了します。

f:id:waenavi:20181030183640j:plain

 

受験番号を「行」のフィールドにドラッグします。

f:id:waenavi:20181030183652j:plain

f:id:waenavi:20181030183702j:plain


これで受験番号のリストが表示されます。受験生は全部で10人です。

f:id:waenavi:20181030183715j:plain

 

さらに、受験番号を「値」のフィールドにもドラッグします。

f:id:waenavi:20181031054415j:plain


それぞれの個数が表示されます。例えば、1A001の受験生は7回テストを受けています。

f:id:waenavi:20181030183801j:plain

 

このように、(原則として)同じ項目を、行のフィールドと値のフィールドの両方にドラッグをするとそれぞれの個数をかぞえることができます。

f:id:waenavi:20181030183811j:plain

 

(2)データの個数とは何か

受験番号を「行」からなくします。(左にドラッグすれば消えます)

f:id:waenavi:20181030185447j:plain


55というのは、元のリストが全部で55行あるということです。「行」のフィールドが無い場合、元のリストのレコードの件数(行数)を数えます。

f:id:waenavi:20181030185450j:plain

 

科目を「行」のフィールドにドラッグします。

f:id:waenavi:20181031054455j:plain


今度は科目ごとの行数になります。

f:id:waenavi:20181030185500j:plain

 

全部で6科目の試験が行われ、国語の試験はのべ13人が受験しています。

f:id:waenavi:20181030185505j:plain


しかし、さきほどの問題(1)で、受験生は10人しかいないことがわかっています。つまり、同じ受験生が国語の試験を2回以上受けているということがわかります。

f:id:waenavi:20181030185508j:plain

 

「値」のフィールドにドラッグした受験番号を削除します。

f:id:waenavi:20181031054534j:plain

 

その代わりに科目を「値」のフィールドにドラッグします。

f:id:waenavi:20181031054558j:plain

 

結果は同じです。

f:id:waenavi:20181030185517j:plain

 

「値」のフィールドに入れるとデータの個数と表示されます。このデータの個数とは、レコードの数のことであり、行数のことです。

f:id:waenavi:20181030185520j:plain


A列の受験番号であっても、B列の科目であっても、科目ごとの行数を数えていますから結果は同じです。原則として、「値」のフィールドに何を入れてもデータの個数は変わりません。ただし、通常は、コード番号(今回の場合は受験番号)を使ってカウントします。

f:id:waenavi:20181030185526j:plain

 

(3)重複を見つける

受験生が10人しかいないのに、国語の受験者が13人いることから、誰が2回受験したかを探します。

f:id:waenavi:20181030185529j:plain

 

受験番号を科目の下にドラッグします。

f:id:waenavi:20181031054647j:plain

f:id:waenavi:20181030185536j:plain

 

科目別の受験番号のリストになります。同じ科目の試験を2回受けている受験生がいることが分かります。このように、重複があるものを見つけることができます。

f:id:waenavi:20181030185539j:plain

 

完成イメージは「表形式」のレイアウトです。また、小計と総計がない形です。

f:id:waenavi:20181030185542j:plain

 

それぞれ設定を変えます。

f:id:waenavi:20181030185545j:plain

f:id:waenavi:20181030185551j:plain

f:id:waenavi:20181030185554j:plain

 

2.値フィールドの設定

問題

次のリストについて、各設問の操作をしなさい。

f:id:waenavi:20181031042922j:plain

(1)受験番号のリストを作り、受験した回数(行数)を求めようとしたところ、次の図のようになった。このようになった理由と直す方法を述べなさい。

f:id:waenavi:20181031042925j:plain

(2)同じ受験者が同じ科目を2回以上受験している。次のように受験回数と合計点を求めなさい。

f:id:waenavi:20181031042927j:plain

(3)同じ受験者が同じ科目を2回以上受験した場合には、得点の高いほうを採用することにした。受験回数と得点を求めなさい。

f:id:waenavi:20181031042933j:plain

また、平均点を採用する場合はどうすればよいか。

f:id:waenavi:20181031042939j:plain

 

解説

(1)値フィールド

ピボットテーブルを挿入します。

f:id:waenavi:20181031045515j:plain

 

受験番号を、「行」のフィールドにドラッグします。

f:id:waenavi:20181031045521j:plain

 

これで受験番号のリストが表示されます。受験生は全部で10人です。

f:id:waenavi:20181031045524j:plain

 

受験番号を「値」のフィールドにドラッグします。

f:id:waenavi:20181031045527j:plain

 

これで受験した回数が求められます。これは正しい操作です。

f:id:waenavi:20181031045532j:plain

 

今度は、得点を「値」のフィールドにドラッグします。

f:id:waenavi:20181031045538j:plain

 

得点の合計になります。

f:id:waenavi:20181031045541j:plain

f:id:waenavi:20181031045544j:plain

 

得点の、「値フィールドの設定」を開きます。

f:id:waenavi:20181031045550j:plain

 

合計になっています。

f:id:waenavi:20181031045552j:plain

 

これをデータの個数に変えます

f:id:waenavi:20181031045555j:plain

 

受験回数になります。

f:id:waenavi:20181031045558j:plain

 

ピボットテーブルで、文字列のフィールドを「値」にドラッグすると、データの個数になります。

f:id:waenavi:20181031045601j:plain

 

数値のフィールドを「値」にドラッグすると、合計になります。得点は数値なので合計になります。

f:id:waenavi:20181031045604j:plain

 

これを直すには、「値フィールドの設定」を変えます。

f:id:waenavi:20181031045607j:plain

 

(2)値のフィールドを2つにする=集計方法を2つにする

「行」のフィールドに科目を追加します。

f:id:waenavi:20181031045610j:plain

 

これで受験番号別・科目別の回数になります。2回以上受験している受験生がいます。

f:id:waenavi:20181031045616j:plain

 

さらに、得点を「値」のフィールドに入れます。

f:id:waenavi:20181031045620j:plain

 

これで回数と合計の両方が表示されます。

f:id:waenavi:20181031045623j:plain

 

「値」のフィールドを2つにすることによって、カウントと合計を2つ表示することができます。

f:id:waenavi:20181031045626j:plain

 

(3)最大値と平均

2回の合計だと200点満点になり比較することができません。2回以上受験した場合に得点の高いほうを表示するときは、最高点を求めます。

f:id:waenavi:20181031045629j:plain

 

合計の「値」のフィールドの設定を変えます。

f:id:waenavi:20181031045632j:plain

 

最大値に変えます。

f:id:waenavi:20181031045635j:plain

 

これで最高点を採用したことになります。

f:id:waenavi:20181031045638j:plain

 

得点の「値」のフィールドの設定を変えます。

f:id:waenavi:20181031045644j:plain

 

今度は平均にしてみます。

f:id:waenavi:20181031045647j:plain

 

2回受験した場合はその平均点になります。

f:id:waenavi:20181031045650j:plain

 

3.行のフィールドと値のフィールドに分ける

問題

次のリストから、ピボットテーブルを用いて完成イメージのような表を作りなさい。
なお、数量は売上数量、売上は売上金額のことである。

f:id:waenavi:20181031051129j:plain

<完成イメージ>

f:id:waenavi:20181031051132j:plain

 

解説

売上数量はF列を集計すればよいですが、売上金額は単価*数量を求める必要があります。

f:id:waenavi:20181031051135j:plain

 

ピボットテーブルを作るときには、「行」のフィールドと「値」のフィールドに分けて考えます。

f:id:waenavi:20181031051138j:plain

 

合計や個数など集計をするものが「値」のフィールドです。

f:id:waenavi:20181031051141j:plain

 

この場合は、分類、コード、商品名が、「行」のフィールドで、数量と売上が、「値」のフィールドです。

f:id:waenavi:20181031051144j:plain

 

ピボットテーブルを挿入します。分類、コード、商品名を「行」のフィールドに、数量と売上を「値」のフィールドに、それぞれドラッグします。

f:id:waenavi:20181031051147j:plain

f:id:waenavi:20181031051150j:plain

 

数量と売り上げはもう一度ドラッグして4列にします。

f:id:waenavi:20181031051156j:plain

 

次に、「行」のフィールドについて3つのレイアウト(コンパクト・アウトライン・表形式)から適切なものを選びます。また、集計行が必要かを決めます。

f:id:waenavi:20181031051159j:plain

 

「値」のフィールドについては、集計方法を考えます。

f:id:waenavi:20181031051205j:plain

  

レイアウトは表形式です。

f:id:waenavi:20181031051208j:plain

 

そして、小計だけ消します。

f:id:waenavi:20181031052014j:plain

 

数量と売り上げの合計になっているのを、平均に変えます。

f:id:waenavi:20181031051220j:plain

f:id:waenavi:20181031051222j:plain

 

これで完成です。

f:id:waenavi:20181031051226j:plain

 

4.クロス集計とクリア

問題

次のリストで、同じ受験者が同じ科目を2回以上受験した場合、得点の高いほうを採用する。各設問の操作をしなさい。

f:id:waenavi:20181031052132j:plain


(1)次のような表を作りなさい。

f:id:waenavi:20181031052320j:plain

(2)ピボットテーブルをクリアしなさい。

f:id:waenavi:20181031052324j:plain

(3)次のような表を作りなさい。

f:id:waenavi:20181031052327j:plain

(4)さらに、次のような表にしなさい。

f:id:waenavi:20181031052329j:plain

 

解説

(1)列のフィールド=内訳をみる

はじめにピボットテーブルを作ります。ピボットテーブルを挿入します。このまま完了します。

f:id:waenavi:20181031053410j:plain

 

受験番号を、「行」のフィールドと「値」のフィールドにそれぞれドラッグします。

f:id:waenavi:20181031053413j:plain

 

これで受験番号ごとの受験回数が出ます。

f:id:waenavi:20181031045558j:plain

 

さらに、科目を、「列」のフィールドにドラッグします。

f:id:waenavi:20181031053416j:plain

 

科目ごとに何回受験したかがわかります。

f:id:waenavi:20181031053419j:plain

 

「列」のフィールドに何も入れなければ、各受験生の受験回数になります。例えば、1A001の受験生は7回テストを受けています。

f:id:waenavi:20181031053422j:plain

 

「列」のフィールドに科目を入れることで、ピボットテーブルが横に広がり、科目ごとの内訳がわかります。このように、「列」のフィールドを使うと、内訳をみることができます

f:id:waenavi:20181031053426j:plain

 

例えば、1A001の受験生が受験したのは5教科で、合わせて7回テストを受けていることが分かります(1+2+2+1+1=7)。

f:id:waenavi:20181031053431j:plain

 

 

縦に受験番号、横に科目を並べて、それぞれ分けて集計をすることができます。

f:id:waenavi:20181031053434j:plain

 

いっぱんに、複数の項目をグループとして集計することを「クロス集計」といい、2つの項目の場合は縦と横に並べて、2次元の集計表を作ります。

f:id:waenavi:20181031053437j:plain

 

(2)ピボットテーブルのクリア

ここでピボットテーブルツールの中から、クリア、すべてのクリアを選んで、ピボットテーブルをクリアします。

f:id:waenavi:20181031053441j:plain

f:id:waenavi:20181031053444j:plain

 

フィールドをすべてなくしてリセットするには、「すべてクリア」をします。画面の幅によってはボタンが「アクション」と表示されることがあります。

f:id:waenavi:20181031053447j:plain

 

(3)クロス集計の練習

受験番号を、「行」のフィールドにドラッグします。

f:id:waenavi:20181031053450j:plain

 

得点を、「値」のフィールドにドラッグします。

f:id:waenavi:20181031053454j:plain

 

これで受験番号ごとの得点の総合計が出ます。

f:id:waenavi:20181031053457j:plain

 

科目を、「列」のフィールドにドラッグします。

f:id:waenavi:20181031053500j:plain

 

これで科目ごとに分かれます。

f:id:waenavi:20181031053503j:plain

 

ここで、レイアウトを表形式にして、総計を非表示にします。

f:id:waenavi:20181031053507j:plain

 

得点が3桁になっているのは、受験したテストの合計になっているからです。

f:id:waenavi:20181031053510j:plain

 

今回は、得点の高いほうを採用するので、値フィールドの設定を変えて、最大値にします。

f:id:waenavi:20181031053513j:plain

 

これで完成です。

f:id:waenavi:20181031053516j:plain

 

(4)値のフィールドの順番

「値」のフィールドに受験番号をドラッグします。

f:id:waenavi:20181031053519j:plain

 

このとき「値」のフィールドの順番を、受験番号、得点の順にします。項目の順番はドラッグで変えることができます。

f:id:waenavi:20181031053522j:plain

 

それぞれの列が2列になってそれぞれ受験回数と得点が求められます。

f:id:waenavi:20181031053528j:plain

 

(5)まとめ:列が増えるパターン

ピボットテーブルの列が増える(横方向に伸びる)パターンは2つあります。

  • 集計方法(カウント・合計・平均など)を増やす場合は、集計したい列を「値」フィールドに入れます。
  • 項目ごとに内訳を見たい場合は、その項目を「列」のフィールドに入れます。

 


解説は以上です。

*補足*
日本の企業では、列集計(横方向に項目を並べて集計する方法)を基本とする場合が多いですが、Excelは、行方向(縦方向)が第1次元を表すので、行集計を基本として練習をします。


付録1 サンプルのファイルはこちら

わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお使いください。7-23 ~ 7-25のサンプルファイル(ZIP)です。

 

付録2 動画版はこちら(無料)

この記事は、わえなびファンダメンタルExcel Program7-23 ~ 7-25 の動画の内容を書き起こし、加筆修正したものです。

動画版(完全版)は、Youtubeにすべて無料で公開しております。ぜひ、ご覧ください。  

ファンダメンタルExcel 7 データベース(全28回)【わえなび】 - YouTube
https://www.youtube.com/playlist?list=PLRaY8kd5CoxOnpijKZLSbMRv37GQXv1Ko

 


Youtube わえなび公式チャンネル
http://www.youtube.com/c/waenavijp

チャンネル登録(無料)をすると最新の動画をチェックできます。
初心者向けの動画が大量にあります。

 

 

 

 

 

Copyright(C)2018 waenavi, All rights reserved. [www.waenavi.com 定礎 平成30年8月]