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

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

【Excel】ピボットテーブル集計の練習問題サンプルデータ動画解説付き

ピボットテーブル(Pivot Table)については別の記事で集計の練習をするよりも、まず、集約の練習をしたほうがいいということを述べました。表をリスト形式にして、フィールドを集約することが最も重要な作業です。

「ピボットテーブルは簡単だ」という人がいますが、決して簡単だと思ってはいけません。クロス集計のやり方だけ理解しても全く意味がありませんし、ピボットテーブル単体で理解しても無意味です。ピボットテーブルは、並べ替えや小計、オートフィルタといったデータベース機能をひと通り理解していなければ使いこなすことができません。

f:id:waenavi:20181113163851j:plain

今回は、ピボットテーブルを総合的に理解できているかを問う練習問題をご用意いたしました。はっきり言って難問です。これがすべて理解できたらピボットテーブルの基礎は完璧です。

目次

1.準備(サンプルファイルはこちら)

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

 

2.小計とピボットテーブルの違い

問題

行集計(小計)の機能と比較して、ピボットテーブルを用いるメリットを述べなさい。 

f:id:waenavi:20181113110924j:plain

 

解説

Excelの[データ]タブに「小計」のボタンがあります。

f:id:waenavi:20181113111628j:plain

 

行集計(小計)について、詳しくは、わえなびファンダメンタルExcel Program7-19~7-20の動画をご覧ください。

行集計をするには、グループ化のため事前に並べ替えをする必要があります(例えば、受験番号ごとに集計するのであれば受験番号の昇順または降順にする)。

f:id:waenavi:20181113111312j:plain

 

さらに元のリストに集計行が入り、アウトラインが設定されます。行集計は、リストの途中に小計を挿入するときに使います。このように簡単な集計をするには行集計(小計)の機能を使い、ピボットテーブルを使ってはいけません。

f:id:waenavi:20181113130522j:plain

 

また、縦向きに集計することはできても、縦横に集計をすることはできません。

f:id:waenavi:20181113130527j:plain

 

これに対して、ピボットテーブルは元のリストを並べ替える必要はありません。

f:id:waenavi:20181113130533j:plain

 

元のリストはそのままにして、別のシートにピボットテーブルを作ります。また、2つの項目を縦横に並べて、クロス集計をすることができます。

f:id:waenavi:20181113130537j:plain

 

フィールドの入れ替えが簡単にできるので、いろいろな集計を試すことができるというメリットもあります。

f:id:waenavi:20181113130541j:plain

 

ちなみに、ピボットテーブルも縦方向だけの行集計が基本です。縦方向の集計ができなければクロス集計はできません。

 

3.クロス集計による比較

問題

次のリストで、右よりも左打者のほうがホームラン(HR)を多く打っているチームはあるか、ピボットテーブルを用いて調べなさい。

f:id:waenavi:20181113134450j:plain

 

解説

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

f:id:waenavi:20181113134826j:plain

 

ピボットテーブルで考えるときには、何を基準に分けて、何を集計するのかを考えます。

f:id:waenavi:20181113135318j:plain

 

今回はホームランの本数が問題となっています。

f:id:waenavi:20181113135324j:plain

 

そして、チーム別・左右別で集計をします。

f:id:waenavi:20181113135327j:plain

 

比較する基準となる左右とチームを縦横に並べます(行と列は逆でも良い)。そして集計するホームランを値のフィールドに入れます。

f:id:waenavi:20181113135358j:plain

f:id:waenavi:20181113145850j:plain

 

4つのチームのうち、チームGは、右打者よりも左打者のほうがホームランの合計本数が多く、ホームランを多く打っていることが分かります。

f:id:waenavi:20181113145855j:plain 

 

ここで、ホームランの数ではなく人数を調べます。HRの、値フィールドの設定を、データの個数にすればよいです。

f:id:waenavi:20181113145901j:plain

 

4つのチームは8人ずつで、同じ人数です。チームGは他のチームに比べて左打者の人数が多いので、左打者のホームランが多くなるのは当たり前のことです。

また、チームBは左打者が2人しかいないので、ホームランの合計が少なくなるのは当たり前です。

f:id:waenavi:20181113145904j:plain

 

そこで、HRの、値フィールドの設定を、平均にします。

f:id:waenavi:20181113151402j:plain

 

これで、チームGに所属する左打者は、右打者に比べてHRを多く打っていると言えます。

f:id:waenavi:20181113151405j:plain

 

4.数値を項目にしてカウントする

問題

さきほどのリストで、オートフィルタを使ってHRを32本打っている選手を抽出したところ、3チームにそれぞれ1人ずつ所属していることが分かった。このように「同じ本数のHRを打っている3人の選手が、3チームにそれぞれ所属している」ということが「32本」以外にあるか、ピボットテーブルを用いて調べなさい。

f:id:waenavi:20181113151408j:plain

 

解説

それぞれのチームに同じHRの本数の選手がいるかどうかは、縦にHR、横にチームとして、選手の人数をカウントすればよいです。例えば、選手名を「値」のフィールドに入れます。

f:id:waenavi:20181113153425j:plain 

 

これで、HRを32本打っている選手は3チームにそれぞれ1人ずついることがわかります。

f:id:waenavi:20181113153429j:plain

 

このほか、6本、16本についても同じことが言えます。

f:id:waenavi:20181113153436j:plain

 

このように、数値を縦にしてカウントすると度数分布表も作れます。

 

5.ピボットテーブルの更新

問題

このリストからピボットテーブルを作って、科目別の平均点を求めた。

f:id:waenavi:20181113131708j:plain

 

採点ミスにより元のリストの98点を100点に訂正した。ピボットテーブルを修正しなさい。

f:id:waenavi:20181113131746j:plain

 

解説

元のリストで98点のところ、100点に修正すると英語の平均点が上がるはずです。

f:id:waenavi:20181113132036j:plain

 

しかし、英語の平均点は変わりません。

f:id:waenavi:20181113132843j:plain

 

更新のボタンを押します。英語の平均点が上がりました。

f:id:waenavi:20181113133158j:plain

 

元のリストに変更があっても、ピボットテーブルは自動で更新されません。

f:id:waenavi:20181113132851j:plain

 

元のリストに変更があったら、かならず、ピボットテーブルの更新をします。

f:id:waenavi:20181113132857j:plain

 

6.データソースの変更

問題

先ほどのリストで、小論文のテストを実施したのでデータを追加した。ピボットテーブルを修正しなさい。

f:id:waenavi:20181113132900j:plain

 

解説

小論文のデータを追加しましたが、ピボットテーブルは自動で更新されないので、変わりません。

f:id:waenavi:20181113133644j:plain

f:id:waenavi:20181113133647j:plain

 

そこで、更新のボタンを押します。更新のボタンを押しても何も変わりません。

f:id:waenavi:20181113133650j:plain

 

ピボットテーブルを挿入するときには、テーブル範囲を指定します。

f:id:waenavi:20181113133653j:plain

 

このとき、ピボットテーブルの集計の対象となる範囲は点線で囲まれます。この範囲のことを「データソース」といいます。

f:id:waenavi:20181113133656j:plain

 

この外側にデータを追加しても、データソースが勝手に広がることはありません。また、更新ボタンはデータソースの内部でデータが変わったときに更新できるボタンであって、データソースの範囲を変えるボタンではありません。

f:id:waenavi:20181113133659j:plain

 

そこで更新ボタンの隣にあるデータソースの変更のボタンを押します。

f:id:waenavi:20181113133702j:plain

 

現在のデータソースの範囲が点線で表示されます。

f:id:waenavi:20181113133709j:plain

 

改めて、元のリストをすべて選択しなおします。

f:id:waenavi:20181113133712j:plain

 

小論文が表示されました。

f:id:waenavi:20181113133716j:plain

 

データソースの内部のデータに変更があった場合は「更新」、データソースの範囲を変更する場合は「データソースの変更」です。

f:id:waenavi:20181113133718j:plain

 

7.フィルタ

問題

次のリストで、あとの設問(1)、(2)の集計をしなさい。

f:id:waenavi:20181113154114j:plain


(1)それぞれの商品で、月ごとの販売数量を求めなさい。ただし、ハヤシライスを非表示にしなさい。

f:id:waenavi:20181113154845j:plain

 

(2)分類がカレーの行だけを集計の対象として次の表を作りなさい。さらに、数量が2以上の注文のみを集計しなさい。

f:id:waenavi:20181113154858j:plain

 

解説

(1)行・列のフィルタ

はじめにピボットテーブルを作ります。

f:id:waenavi:20181113155638j:plain 

 

商品名を、「行」のフィールドにドラッグします。月を、「列」のフィールドにドラッグします。そして数量を「値」のフィールドにドラッグします。

f:id:waenavi:20181113155645j:plain

 

ハヤシライスを非表示にするには、フィルタでハヤシライスのチェックを外します。

f:id:waenavi:20181113155649j:plain

 

これで完成です。

f:id:waenavi:20181113155653j:plain

 

(2)レポートフィルタ

今度は、行のフィールドを、商品名ではなく商品コードにします。

f:id:waenavi:20181113160556j:plain

 

このうち、元のリストで、C列がカレーになっている行だけを集計をします。

f:id:waenavi:20181113160559j:plain

 

しかし、行と列には、分類の項目がありません。

f:id:waenavi:20181113160602j:plain

 

そこで、分類を、フィルタのフィールドにドラッグします。

f:id:waenavi:20181113160606j:plain

 

分類のフィルタを使います。カレーを選択します。

f:id:waenavi:20181113161247j:plain 

 

これで完成です。

f:id:waenavi:20181113161250j:plain

 

ピボットテーブルは、行と列に項目を並べて表を作ります。ここにドラッグしたものであれば、それぞれのラベルのフィルタを使えば抽出ができます。

f:id:waenavi:20181113161257j:plain

 

しかし、ここにない項目で抽出する場合は、別の欄にフィルタを用意する必要があります。これを、レポートフィルタ(フィルターのフィールド)といいます。

f:id:waenavi:20181113161300j:plain 

 

さらに、F列の数量が1になっている行を除いて、2以上のものだけ集計をします。

f:id:waenavi:20181113161303j:plain

 

数量をフィルタのフィールドに追加します。

f:id:waenavi:20181113161310j:plain

 

数量のフィルタで抽出をします。

f:id:waenavi:20181113161314j:plain

 

複数選択するには、複数のアイテムを選択します。1だけチェックをはずします。

f:id:waenavi:20181113161317j:plain

 

これで完成です。

f:id:waenavi:20181113161323j:plain

 


解説は以上です。


8.動画版はこちら(無料)

この記事は、わえなびファンダメンタルExcel Program7-25(特訓問題のみ)、7-25-2、7-26 の動画の内容を書き起こし、加筆修正したものです。

動画版(完全版)は、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月]