- ホーム >
- 基本操作 >
- エクセル(Excel)編 >
レポートの作成 ~ ピボットテーブルとクロス集計 ~
これまでの学習でExcelの基礎はほぼ習得できていて、表計算や簡単な関数などはもう使いこなすことができると思います。
ここからは、もう少し応用的なExcelの利用について学習していきます。本項では集積したデータをもとに、いろいろな角度から集計・分析を行う機能について解説します。
Excelの利用は、関数やグラフだけでなく、例えば家計簿の作成など、日々少しずつデータを入力していき、月単位等のまとまった時点でデータを加工・分析する、といった使い方も一般的です。
こうした機能のなかでもっとも代表的な機能が、
ピボットテーブル
と呼ばれる機能です。ピボットテーブル機能をつかうと、データの集計のみならず、自動的にレポート形式に整理してくれます。
といっても、具体的な例がなければピンとこないと思いますので、ますは単純な例題を使って練習してみましょう。
例題10 | ピボットテーブルの学習用 |
---|
例題10の「データ」セルは、ある会社の売上データとなっています。「いついつに誰が何を売ったか」というデータを日付ごとに単純に積み重ねたものです。
こういったデータを月ごとや週ごとに集計して、担当者等の区分ごとにまとめたり、グラフ等に可視化したりすることはよくあることです。
では、このように積み上げられたデータをどのように集計したらよいでしょうか?
例題のように50にも満たない件数であれば、オートフィルタ機能(簡単なデータベースの作成(2) で後述します)で、区分ごとに抽出して加工することもできますが、もっと膨大なデータであればちょっと処理に困ります。
このように、ある程度まとまったデータを集計するときには、ピボットテーブル機能が役立つのです。マウス操作で複雑なクロス集計、グラフ化まで簡単に行うことができます。
さて、ではここで機能の説明に入るまえに、このようなデータ集計の基礎について簡単に触れておきたいと思います。
そもそもExcelでは、のちのデータ処理・加工のことを考慮してデータを入力していく、または入力フォーマット(形式)を作成しておく、というのはとても大事です。
つまり、規則正しくデータを入力していくこと、言い換えれば、
規則正しくデータが入力できるフォーマット(形式)を最初に作成すること
が重要になります。具体的には、例題のように横方向(列)に「日付」「商品名」などの区分を分け、縦方向(行)に個別のデータを積み重ねていくように入力様式をつくります。あくまで例ですが、このようにしっかりと区分を分けると、その列には異なる書式の値が入ること(数値の列にテキストが入るなど)はありません。
つまり、データ処理の基本は「しっかりと区分に分けて、その区分でデータの書式を統一しておく」ということです。
しかし、データがどんどん溜まっていった後になって、例えば「地域別の区分をもっと細かく分けておけばよかった」などと気づくことが往々にしてあるものです。
Excelは、簡単な(とはいっても数万単位までの)データベース機能を持っています。これからの時代、膨大に収集されるデータをどう扱うかが重要なテーマとなっています。データ処理の基礎についてもあわせて習得していただければ幸いです。
さて、それでは例題のデータをピボットテーブル機能をつかって加工してみましょう。(別シートはサンプルですので、例題作成後に参考にしていただければ幸いです)
操作はとても簡単です。表をマウスで範囲指定(表のタイトル部分も含めて)し、「挿入」タブの「ピボットテーブル」より「ピボットテーブル」を選択します。
すると、「ピボットテーブルの作成」ウィンドウのが表示されますので、選択した表の範囲を確認し、ピボットテーブルを作成する場所(配置する場所)を選択します。
今回は新規のワークシートに作成しますので、なにも変更することなく「OK」ボタンをクリックします。すると新しいシートが作成され、下図のようにテーブルのフォーマットが作成されます。
この行や列、値のフィールドエリアに任意の項目(データ区分)をマウスでドラッグするだけです。非常に直観的でわかりやすいと思います。
一方、画面右側に下図のツールメニューが表示されます。このメニューから項目(フィールドリスト)を選んでマウスでドラッグするだけです。
つまり、マウスによるドラッグ&ドロップだけで、各フィールドごと(「日付」や「商品名」など)のクロス集計を簡単に行うことができるのです。
まず、蓄積されたデータから、どのような集計をするのかを考えます。いろいろな切り口から集計することができますが、ここではスタンダードに、「月次の売上」を担当者別に集計して比較してみましょう。
日付データを「月」にまとめて、時系列的に集計しますので、列(横方向)を「日付」とし、行(縦方向)に「担当者」を並べる表になります。
ピボットテーブルは、日付を自動的に「月」ごとに集計してまとめてくれます。下図のように、「ピボットテーブルのフィールドリスト」から「日付」をドラッグして「列」の位置にドロップします。
すると、下図のように列が「月」ごとの見出しに変化します。
または、「ピボットテーブルのフィールドリスト」内の「列ラベル」位置に、下図のようにドラッグ&ドロップしても同様になります。
同様に、「担当者」を「行」の位置にドラッグ&ドロップします。
このかたちになると集計表っぽくなってきます。あとは、「値」をクロス部分(値フィールド)に表示すれば、月ごとの担当者別売上表になります。同様にフィールドリストの「売上高」を「値」の位置にドラッグ&ドロップしましょう。
値フィールドにはそれぞれの合計値が自動計算されて表示されます。このように、ピボットテーブル機能を使うと、非常に簡単にクロス集計表を作成することができます。
また、複数のフィールドを追加することも可能です。行位置に「商品名」、レポートフィルター位置に「カテゴリ」をドラッグして、さらに細かく分化した表を作成してみましょう。
上図のように、「担当者」のなかの「商品名」別といった複数フィールドによる集計を行うことができます。また、レポートフィルター位置にドラッグした「カテゴリ」は、カテゴリ別のフィルターとして使うことができます。
下図のようにリストボタンをクリックすると、商品のカテゴリが表示され、任意のカテゴリを選択すると、そのカテゴリのみが集計されます。
例えば、「道具」でフィルタリングすると、下図のように「道具」のみでのクロス集計表が再作成されて表示されます。
では、次に集計された表からグラフを作成してみましょう。
上記と同様に、それぞれ任意のフィールドをドラッグ&ドロップしてグラフ化する「ピボットグラフ」機能もありますが、基本的にはまず集計表を作成してから、そのデータを使ってグラフ化するというのが多いと思います。
したがって、初めに集計した担当者ごとの合計値(下図)をグラフ化してみましょう。
上図のグラフに戻すには、下図のとおりピボットテーブルのフィールドリストから、不要なフィールドのチェックマークを外します。この場合は、「商品名」のチェックを外して、フィルター位置の「カテゴリ」を「すべて」に変更するか、チェックを外せばOKです。
それから、通常どおりグラフ化すればよいのですが(グラフの作成については、グラフの作成(1) を参照してください)、ピボットテーブルの場合は、マウスで範囲指定するときにフィールドが選択されてしまい、ドラッグのかたちになってしまうので、うまく範囲指定できません。
そこで、ピボットテーブルでは表内の任意のセルをカレント状態にして、グラフの作成ボタンをクリックすると、ピボットテーブルのグラフが作成できます。つまり、あるひとつのセルを選択してグラフボタンを押すだけで、正しい範囲のグラフが作成されます。
ピボットテーブルから作成したグラフには、フィールドごとにフィルターが自動作成され、グラフでもフィルタリングすることができます。
このように、蓄積されたデータを効率的に活用したいときにはピボットテーブル機能が有効です。
最後に、ある注意点をしっかり覚えておきましょう。ピボットテーブルでは、
値が変更したときには、「更新」処理をしないと変更が適用されない
ので注意が必要です。
入力ミスなどで元データの値を変更したときは、自動で再計算されない ので、下図のとおり、ピボットテーブル内をカレント状態にし、「ピボットテーブルツール」メニューを表示させた状態で、「オプション」タブの「更新」ボタンをクリックします。この処理をしないと更新が適用されないので注意が必要です。適当に値を変更して実験してみてください。
さて、では下記の練習課題に取り組んでみましょう。この練習課題は、よくあるアンケートの集計に便利な例題です。蓄積されたアンケート結果を、任意のフィールドごとにいろいろな角度から集計してみましょう。
課題9 | ピボットテーブルの練習課題 |
---|
これといっての解説はありませんが、課題9のような場合は、例題とは異なり、「売上高」のような合計値となる値がありません。(下図のように、値となるフィールドがない)
値としては、〇〇という回答が「〇件」という件数があるのみです。したがって、値フィールドには、行や列に使用したものと同じフィールドをドロップします。
例えば、上図のように「デザイン」を「居住区」別に集計する場合、値フィールドには「デザイン」をもう一度ドラッグすれば、その件数の合計を計算してくれます。
ピボットテーブルには様々な活用方法があると思われますので、ぜひ練習して活用の場を広げていただけたらと思います。
更新履歴
- 2012年4月20日
- ページを公開。
- 2017年12月29日
- ページをSSL化によりHTTPSに対応。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- ウェブサイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします!