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

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

【Excel】ピボットテーブルは集計より「集約」の仕方を理解せよ

初心者にピボットテーブルの使い方を説明するとしたら、最初に何を語るべきでしょうか?

Microsoftの説明(下の図)によると、複雑なデータをピボットテーブルに簡単に配置し、集計することができるのだそうです。

f:id:waenavi:20181031180130j:plain

しかし、合計や平均を集計するだけであれば関数を使えばよく、複雑な集計は複雑な関数を使えばいいだけです。何も分からない初心者に、いきなり「集計表ができること」を説明するのは論外です。

f:id:waenavi:20181031193518g:plain

Microsoftはあまり強調していませんが、ピボットテーブルの最大の強みは大量のデータを集約できることです。簡単に言えば「まとめ」機能です。ピボットテーブルの機能を覚えるときには、合計や平均を意識する前に、まず「集約こそが主役である」ということを根本的に理解するべきです。

そこで、今回は、リスト形式の表をどのようにまとめたらよいかについて解説します。

 

目次

1.リスト形式の表について

大量のデータはリスト形式の表にしなければ、集約も集計もできません。リスト形式の表でなければピボットテーブルは作れません。詳しくはこちらの記事をご覧ください。

 

2.何種類あるか

問題

ピボットテーブルを用いて、受験番号をすべて抽出しなさい。また、抽出を取り消して、この操作を繰り返し練習しなさい

f:id:waenavi:20181031101833j:plain

 

解説

A列には受験番号がたくさん入力されていますが、同じ番号もたくさんあります。結局、何人の受験生がいるのかが全く分かりません。

このように、リスト形式の表(上述)の場合、同じ種類のデータを縦向きに入力します。重複をなくして全部をもれなく抽出するには「ピボットテーブル PivotTable 」を使います。

f:id:waenavi:20181031102020j:plain

 

リストを全部選択します。(リストを選択しなくてもできますが、ここでは、念のため全部選択します)

f:id:waenavi:20181031112716j:plain


ピボットテーブルを選びます。

f:id:waenavi:20181031112719j:plain

 

このまま完了(OK)します。

f:id:waenavi:20181031112722j:plain

 

新しいシートが追加され、ピボットテーブルの枠だけ表示されます。

f:id:waenavi:20181031112725j:plain

 

枠の中をクリックすると、画面の右側にフィールドリストが表示されます。

f:id:waenavi:20181031112729j:plain

 

枠の外をクリックすると消えます。

f:id:waenavi:20181031112736j:plain

 

「フィールド」とは元のリストの列のことです。(リストの1行目の項目名が表示されます)

f:id:waenavi:20181031112739j:plain

 

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

f:id:waenavi:20181031112742j:plain

 

受験番号のリストが昇順で並びます。全部で10人の受験生がいることがわかります。

f:id:waenavi:20181031112746j:plain

 

受験番号を左にドラッグします。

f:id:waenavi:20181031112750j:plain

 

受験番号の下に小さいバツの記号がついていることに注意します。

f:id:waenavi:20181031112757j:plain

 

手を放すと受験番号が消えます。

f:id:waenavi:20181031112800j:plain

 

それと同時にリストもなくなります。

f:id:waenavi:20181031112803j:plain

 

ピボットテーブルのフィールドは、枠の外にドラッグすることによって削除することができます。(右クリックで削除することも可能)

f:id:waenavi:20181031112807j:plain

 

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

f:id:waenavi:20181031112810j:plain

 

リストが復活します。シート自体を削除します。

 f:id:waenavi:20181031112814j:plain

 

これで元の状態に戻ります。それでは、もう一度繰り返し練習しましょう。

f:id:waenavi:20181031112817j:plain

 

リストを全部選択します。ピボットテーブルを選びます。

f:id:waenavi:20181031112820j:plain

 

このとき、テーブル範囲にも、選択したリストの範囲が表示されていることを確認します。

f:id:waenavi:20181031112823j:plain

 

また、新規のワークシートが選ばれていることを確認します。

f:id:waenavi:20181031112830j:plain

 

新しいシートができます。

f:id:waenavi:20181031112834j:plain

 

受験番号を行のフィールドにドラッグします。受験番号の前に自動でチェックが入ります。受験番号の列で使われている全ての番号が表示されます。

f:id:waenavi:20181031112839j:plain

f:id:waenavi:20181031113945j:plain

 

3.種類が抽出できるということは・・・

(1)データの点検ができる

ピボットテーブルの「行」を使うことによって、重複することなくすべてのデータを抽出することができます。「何種類あるか」を把握することができます。

f:id:waenavi:20181031112842j:plain

 

例えば、実際の受験生が11人だったとします。しかし、ピボットテーブルで集約した結果、受験番号が10個しか出てこなければ入力漏れの疑いがあることになります。また、実際の受験番号が「1A」から始まる番号しかなかったとすると、「1B009」は入力ミスの疑いがあるということになります。

このように、データの過不足や不正なデータを見つけることができます。

(2)別表が作れる

大量のデータから種類だけを抽出して、コピーすることが可能です(後述)。これを用いて別の「まとめ」の資料を作成することができます。

 

4.フィルタ

問題

さきほど作成したピボットテーブルを利用して、「9」で終わる番号だけ抽出しなさい。

 

解説

ピボットテーブルを作成すると、項目名にオートフィルタがつきます。(オートフィルタについて、詳しくはわえなびファンダメンタルExcel Program7-16の動画参照)

f:id:waenavi:20181031114503j:plain


ここで9で終わるものだけチェックを入れます。

f:id:waenavi:20181031115054j:plain

 

(別解)ラベルフィルタによって、9で終わるものを指定することができます。

f:id:waenavi:20181031115300j:plain

f:id:waenavi:20181031115304j:plain

f:id:waenavi:20181031115307j:plain


受験番号からフィルタをクリアします。

f:id:waenavi:20181031115613j:plain

 

元に戻ります。

f:id:waenavi:20181031115604j:plain

 

5.抽出結果を再利用する

問題

さきほど作成したピボットテーブルを利用して、小論文以外の科目名を抽出しなさい。
また、抽出したリストを別のシートに値のみ貼り付けたあとで、フィルタをクリアしなさい。

 

解説

今度は、科目名です。B列には科目名がたくさん入力されていますが、小論文以外の科目が全部で何科目あるのかが全く分かりません。

f:id:waenavi:20181031101833j:plain

 

まず、受験番号はいらないので、左にドラッグすることで削除します。

f:id:waenavi:20181031112750j:plain

 

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

f:id:waenavi:20181031130025j:plain

 

フィルタで、小論文をなくします。

f:id:waenavi:20181031130040j:plain

 

5教科になります。

f:id:waenavi:20181031130351j:plain

 

新しいシートを挿入します。

f:id:waenavi:20181031131210j:plain

 

コピーします。

f:id:waenavi:20181031131213j:plain

 

新しいシートに、値を貼り付けます。

f:id:waenavi:20181031131216j:plain

f:id:waenavi:20181031131219j:plain

 

最後に、ピボットテーブルで、科目からフィルタをクリアします。

f:id:waenavi:20181031131225j:plain

 

クリアしたので小論文が復活します。

f:id:waenavi:20181031131228j:plain

 

しかし、貼り付けたほうは5教科のままです。

f:id:waenavi:20181031131231j:plain

 

ピボットテーブルは自動で作られるものであり、フィールドを変えると形が大きく変わります。したがって、ピボットテーブル自体を直接編集してはいけません。

f:id:waenavi:20181031131234j:plain

 

このリストを再利用するときは、別のシートに貼り付けるのが一般的です。

f:id:waenavi:20181031131238j:plain

 

6.グループとレイアウト

問題

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

(1)次のリストを作りなさい。 

f:id:waenavi:20181031142014j:plain

(2)設問(1)のリストを次のようなレイアウトにしなさい。

 f:id:waenavi:20181031142017j:plain

(3)設問(2)のリストを次のようなレイアウトにしなさい。

f:id:waenavi:20181031142020j:plain

 

解説

(1)グループ別にする

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

f:id:waenavi:20181031143909j:plain

 

このまま完了します。新しいシートが追加され、ピボットテーブルの枠だけ表示されます。

f:id:waenavi:20181031143915j:plain

 

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

f:id:waenavi:20181031143921j:plain

 

受験番号ごとに、受験した科目のリストが表示されます。

f:id:waenavi:20181031143925j:plain

 

例えば、1A008の受験生は4教科、1A009の受験生は2教科受験していることがわかります。

f:id:waenavi:20181031143928j:plain

 

グループ別にリストを作る場合は、大きいグループから順に、「行」のところにドラッグします。

f:id:waenavi:20181031143934j:plain

 

(2)コンパクト形式とアウトライン形式

ピボットテーブルをクリックすると、ピボットテーブルツールが表示されます。

f:id:waenavi:20181031143941j:plain

 

外側をクリックするとツールが消えます。

f:id:waenavi:20181031143948j:plain

 

ピボットテーブルツールとフィールドリストは、ピボットテーブルの中にカーソルがあるときだけ表示されます。

f:id:waenavi:20181031143951j:plain

 

ピボットテーブルには3つのレイアウトがあります。

f:id:waenavi:20181031143954j:plain

 

レポートのレイアウトの中に3つあります。

f:id:waenavi:20181031143958j:plain

 

コンパクト形式は1列で表示されます。これをアウトライン形式に変えると2列になります。

f:id:waenavi:20181031144003j:plain

 

コンパクト形式は1列で表示され、アウトライン形式は複数列になります。アウトライン形式は、受験番号と科目でそれぞれフィルタが使えます。

f:id:waenavi:20181031144006j:plain

 

(3)表形式、小計と総計

行の中に、受験番号と科目があります。この順番は、ドラッグで変えることができます。ドラッグをして順序を変えます(科目、受験番号の順)。

f:id:waenavi:20181031144009j:plain

 

科目ごとに、受験番号のリストが出ます。

f:id:waenavi:20181031144013j:plain

 

小論文は1人だけです。

f:id:waenavi:20181031144016j:plain

 

2列で表示されていますので、アウトライン形式です。このように項目を変えてもレイアウトは引き継がれます。

f:id:waenavi:20181031144019j:plain

 

アウトライン形式は、空白があります。

f:id:waenavi:20181031144022j:plain

 

この空白を詰めます。まず、レイアウトを、表形式にします。

f:id:waenavi:20181031144025j:plain

 

さらに、集計行をなくします。

f:id:waenavi:20181031144029j:plain

 

「小計を表示しない」を選びます。小計がなくなります。

f:id:waenavi:20181031144032j:plain

 

最後に総計が表示されています。さらに総計も「行と列の集計を行わない」を選ぶと総計も消えます。

f:id:waenavi:20181031144035j:plain

 

アウトライン形式では2列目に空白があります。この空白を埋めてリストのような形にするには、レイアウトを表形式にします。

f:id:waenavi:20181031144038j:plain

 

表形式にしてから、小計と総計を非表示にします。

f:id:waenavi:20181031144046j:plain

 

ちなみにExcelのバージョンによっては、「アイテムのラベルをすべて繰り返す」を選ぶことによって、全部の空白を埋めることができます。

f:id:waenavi:20181031144052j:plain

f:id:waenavi:20181031144056j:plain

 

7.コード表を作る

問題

次のリストで使われている商品は全部で何種類あるか。すべて抽出して、完成イメージのようなコード表を作りなさい。ただし、作成した表は別のシートに値のみ貼り付けるものとする。

f:id:waenavi:20181031144103j:plain

<完成イメージ>

f:id:waenavi:20181031144106j:plain

 

解説

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

f:id:waenavi:20181031144112j:plain


このまま完了します。

f:id:waenavi:20181031144114j:plain

 

コード、商品名、分類、単価の順に、行のフィールドにドラッグします。

f:id:waenavi:20181031144118j:plain

 

表形式にします。

f:id:waenavi:20181031144125j:plain

 

「小計を表示しない」を選びます。

f:id:waenavi:20181031144128j:plain

 

総計も不要です。

f:id:waenavi:20181031144135j:plain

 

商品は全部で5種類であることがわかります。

f:id:waenavi:20181031144138j:plain

 

このようなコード表を作るときには、コードから順に、行のフィールドにドラッグします。

f:id:waenavi:20181031144141j:plain

 

レイアウトは先ほどの問題と同じで、表形式で、小計と総計を非表示にします。

f:id:waenavi:20181031144144j:plain

 

できあがったピボットテーブルをさらに利用するときは、新しいシートを用意して、貼り付けます。コピーします。

f:id:waenavi:20181031144147j:plain

 

新しいシートに、値を貼り付けます。

f:id:waenavi:20181031144153j:plain

 

これで完成です。

f:id:waenavi:20181031144159j:plain

 

7.集計

項目が集約できたら、それぞれの項目について集計をします。続きはこちらの記事をご覧ください。

  


解説は以上です。

*補足*
この記事では初心者が分かりやすいように「集約」「抽出」という言葉を使っていますが、正確には「グループ化(GROUP BY句)」といいます。


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

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

 

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

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

www.youtube.com

www.youtube.com

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