当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。Table)については別の記事で集計の練習をするよりも、まず、集約の練習をしたほうが良いということを述べました(【Excel】ピボットテーブルは集計より「集約」の仕方を理解せよ)。表をリスト形式にして、フィールドを集約することが最も重要な作業です。「Excelのピボットテーブルは簡単だ」という人がいますが、決して簡単だと思ってはいけません。クロス集計のやり方だけ理解しても全く意味がありません。ピボットテーブルは、並べ替えや小計、オートフィルタといったデータベース機能をひと通り理解していなければ使いこなすことができません。今回は、ピボットテーブルの集計を総合的に理解できているかを問う練習問題をご用意いたしました。これがすべて理解できたらピボットテーブルの基礎は完璧です(※解けない人は上記の記事に戻って練習しましょう)。「ピボットテーブル」シリーズ(この記事は第3回です)ピボットテーブルは集計より「集約」の仕方を理解せよピボットテーブルのクロス集計の基本は、行集計であるピボットテーブル集計の練習問題サンプルデータ動画解説付きピボットテーブルで度数分布表(ヒストグラム)を作成する方法ピボットテーブルにはミスを発見するという使い方があるピボットテーブルの中のセルを数式で参照してもよいか【まとめ】ピボットテーブルは集計の前の基礎トレーニングが大事!ピボットテーブル総まとめ【応用事例】ルー大柴さんが過去11年間にブログで使用した頻出ルー語TOP100を発表!カッ!(ピボットテーブル)目次1.準備(サンプルファイルはこちら)2.小計とピボットテーブルの違い3.クロス集計による比較4.数値を項目にしてカウントする5.ピボットテーブルの更新6.データソースの変更7.フィルタ(1)行・列のフィルタ(2)レポートフィルタ8.動画版はこちら(無料)1.準備(サンプルファイルはこちら)わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお取りください。7-25,7-25-2,7-26の「サンプルファイル(ZIP)」です。行集計(小計)の機能と比較して、ピボットテーブルを用いるメリットを述べなさい。解説Excelの[データ]タブに「小計」のボタンがあります。Program7-19~7-20の動画をご覧ください。行集計2(小計のグループを考える)行集計をするには、グループ化のため事前に並べ替えをする必要があります(例えば、受験番号ごとに集計するのであれば受験番号の昇順または降順にする)。元のリストに集計行が入り、アウトラインが設定されます。行集計は、リストの途中に小計を挿入するときに使います。このように簡単な集計をするには行集計(小計)の機能を使い、ピボットテーブルを使ってはいけません。縦向きに集計することはできても、縦横に集計をすることはできません。これに対して、ピボットテーブルは元のリストを並べ替える必要はありません。元のリストはそのままにして、別のシートにピボットテーブルを作ります。2つの項目を縦横に並べて、クロス集計をすることができます。フィールドの入れ替えが簡単にできるので、いろいろな集計を試すことができるというメリットもあります。ちなみに、ピボットテーブルも縦方向だけの行集計が基本です。縦方向の集計ができなければクロス集計はできません。次のリストで、右よりも左打者のほうがホームラン(HR)を多く打っているチームはあるか、ピボットテーブルを用いて調べなさい。解説ピボットテーブルを挿入します。ピボットテーブルで考えるときには、何を基準に分けて、何を集計するのかを考えます。今回はホームランの本数が問題となっています。チーム別・左右別で集計をします。比較する基準となる左右とチームを縦横に並べます(行と列は逆でも良い)。集計するホームランを値のフィールドに入れます。4つのチームのうち、チームGは、右打者よりも左打者のほうがホームランの合計本数が多く、ホームランを多く打っていることが分かります。ここで、ホームランの数ではなく人数を調べます。HRの、値フィールドの設定を、データの個数にすればよいです。4つのチームは8人ずつで、同じ人数です。チームGは他のチームに比べて左打者の人数が多いので、左打者のホームランが多くなるのは当たり前のことです。チームBは左打者が2人しかいないので、ホームランの合計が少なくなるのは当たり前です。そこで、HRの、値フィールドの設定を、平均にします。これで、チームGに所属する左打者は、右打者に比べてHRを多く打っていると言えます。4.数値を項目にしてカウントする問題さきほどのリストで、オートフィルタを使ってHRを32本打っている選手を抽出したところ、3チームにそれぞれ1人ずつ所属していることが分かった。このように「同じ本数のHRを打っている3人の選手が、3チームにそれぞれ所属している」ということが「32本」以外にあるか、ピボットテーブルを用いて調べなさい。解説それぞれのチームに同じHRの本数の選手がいるかどうかは、縦にHR、横にチームとして、選手の人数をカウントすればよいです。選手名を「値」のフィールドに入れます。これで、HRを32本打っている選手は3チームにそれぞれ1人ずついることがわかります。このほか、6本、16本についても同じことが言えます。このように、数値を縦にしてカウントすると度数分布表も作れます。問題このリストからピボットテーブルを作って、科目別の平均点を求めた。採点ミスにより元のリストの98点を100点に訂正した。ピボットテーブルを修正しなさい。元のリストで98点のところ、100点に修正すると英語の平均点が上がるはずです。英語の平均点は変わりません。更新のボタンを押します。英語の平均点が上がりました。元のリストに変更があっても、ピボットテーブルは自動で更新されません。元のリストに変更があったら、かならず、ピボットテーブルの更新をします。先ほどのリストで、小論文のテストを実施したのでデータを追加した。ピボットテーブルを修正しなさい。小論文のデータを追加しましたが、ピボットテーブルは自動で更新されないので、変わりません。そこで、更新のボタンを押します。更新のボタンを押しても何も変わりません。ピボットテーブルを挿入するときには、テーブル範囲を指定します。このとき、ピボットテーブルの集計の対象となる範囲は点線で囲まれます。この範囲のことを「データソース」といいます。この外側にデータを追加しても、データソースが勝手に広がることはありません。更新ボタンはデータソースの内部でデータが変わったときに更新できるボタンであって、データソースの範囲を変えるボタンではありません。そこで更新ボタンの隣にあるデータソースの変更のボタンを押します。現在のデータソースの範囲が点線で表示されます。元のリストをすべて選択しなおします。小論文が表示されました。データソースの内部のデータに変更があった場合は「更新」、データソースの範囲を変更する場合は「データソースの変更」です。次のリストで、あとの設問(1)、(2)の集計をしなさい。(1)それぞれの商品で、月ごとの販売数量を求めなさい。ハヤシライスを非表示にしなさい。(2)分類がカレーの行だけを集計の対象として次の表を作りなさい。数量が2以上の注文のみを集計しなさい。解説(1)行・列のフィルタはじめにピボットテーブルを作ります。商品名を、「行」のフィールドにドラッグします。月を、「列」のフィールドにドラッグします。数量を「値」のフィールドにドラッグします。ハヤシライスを非表示にするには、フィルタでハヤシライスのチェックを外します。(2)レポートフィルタ今度は、行のフィールドを、商品名ではなく商品コードにします。このうち、元のリストで、C列がカレーになっている行だけを集計をします。行と列には、分類の項目がありません。そこで、分類を、フィルタのフィールドにドラッグします。分類のフィルタを使います。