基礎からわかる!パソコン入門・再入門 / 無料パソコン教室・基礎知識・Word・Excel・PowerPoint

レポートの作成 ~ ピボットテーブルとクロス集計 ~

れまでの学習でExcelの基礎はほぼ習得できていて、表計算や簡単な関数などはもう使いこなすことができると思います。

ここからは、もう少し応用的なExcelの利用について学習していきます。本項では集積したデータをもとに、いろいろな角度から集計・分析を行う機能について解説します。

Excelの利用は、関数やグラフだけでなく、例えば家計簿の作成など、日々少しずつデータを入力していき、月単位等のまとまった時点でデータを加工・分析する、といった使い方も一般的です。

こうした機能のなかでもっとも代表的な機能が、

ピボットテーブル

と呼ばれる機能です。ピボットテーブル機能をつかうと、データの集計のみならず、自動的にレポート形式に整理してくれます。

といっても、具体的な例がなければピンとこないと思いますので、ますは単純な例題を使って練習してみましょう。

例題
例題10 ピボットテーブルの学習用

例題10の「データ」セルは、ある会社の売上データとなっています。「いついつに誰が何を売ったか」というデータを日付ごとに単純に積み重ねたものです。

「例題10」の「データ」セルのイメージ

こういったデータを月ごとや週ごとに集計して、担当者等の区分ごとにまとめたり、グラフ等に可視化したりすることはよくあることです。

では、このように積み上げられたデータをどのように集計したらよいでしょうか?

例題のように50にも満たない件数であれば、オートフィルタ機能(簡単なデータベースの作成(2) で後述します)で、区分ごとに抽出して加工することもできますが、もっと膨大なデータであればちょっと処理に困ります。

このように、ある程度まとまったデータを集計するときには、ピボットテーブル機能が役立つのです。マウス操作で複雑なクロス集計、グラフ化まで簡単に行うことができます。

さて、ではここで機能の説明に入るまえに、このようなデータ集計の基礎について簡単に触れておきたいと思います。

そもそもExcelでは、のちのデータ処理・加工のことを考慮してデータを入力していく、または入力フォーマット(形式)を作成しておく、というのはとても大事です。

つまり、規則正しくデータを入力していくこと、言い換えれば、

規則正しくデータが入力できるフォーマット(形式)を最初に作成すること

が重要になります。具体的には、例題のように横方向(列)に「日付」「商品名」などの区分を分け、縦方向(行)に個別のデータを積み重ねていくように入力様式をつくります。あくまで例ですが、このようにしっかりと区分を分けると、その列には異なる書式の値が入ること(数値の列にテキストが入るなど)はありません。

つまり、データ処理の基本は「しっかりと区分に分けて、その区分でデータの書式を統一しておく」ということです。

しかし、データがどんどん溜まっていった後になって、例えば「地域別の区分をもっと細かく分けておけばよかった」などと気づくことが往々にしてあるものです。

Excelは、簡単な(とはいっても数万単位までの)データベース機能を持っています。これからの時代、膨大に収集されるデータをどう扱うかが重要なテーマとなっています。データ処理の基礎についてもあわせて習得していただければ幸いです。

さて、それでは例題のデータをピボットテーブル機能をつかって加工してみましょう。(別シートはサンプルですので、例題作成後に参考にしていただければ幸いです)

操作はとても簡単です。表をマウスで範囲指定(表のタイトル部分も含めて)し、「挿入」タブの「ピボットテーブル」より「ピボットテーブル」を選択します。

「挿入」タブの「ピボットテーブル」ボタンのイメージ

すると、「ピボットテーブルの作成」ウィンドウのが表示されますので、選択した表の範囲を確認し、ピボットテーブルを作成する場所(配置する場所)を選択します。

「ピボットテーブルの作成」ウィンドウのイメージ

今回は新規のワークシートに作成しますので、なにも変更することなく「OK」ボタンをクリックします。すると新しいシートが作成され、下図のようにテーブルのフォーマットが作成されます。

ピボットテーブルの作成ウィザードで作成されたシートのイメージ

この行や列、値のフィールドエリアに任意の項目(データ区分)をマウスでドラッグするだけです。非常に直観的でわかりやすいと思います。

一方、画面右側に下図のツールメニューが表示されます。このメニューから項目(フィールドリスト)を選んでマウスでドラッグするだけです。

ピボットテーブルのフィールドリストのイメージ

つまり、マウスによるドラッグ&ドロップだけで、各フィールドごと(「日付」や「商品名」など)のクロス集計を簡単に行うことができるのです。

まず、蓄積されたデータから、どのような集計をするのかを考えます。いろいろな切り口から集計することができますが、ここではスタンダードに、「月次の売上」を担当者別に集計して比較してみましょう。

日付データを「月」にまとめて、時系列的に集計しますので、列(横方向)を「日付」とし、行(縦方向)に「担当者」を並べる表になります。

ピボットテーブルは、日付を自動的に「月」ごとに集計してまとめてくれます。下図のように、「ピボットテーブルのフィールドリスト」から「日付」をドラッグして「列」の位置にドロップします。

ピボットテーブルのフィールドをドラッグしているイメージ

すると、下図のように列が「月」ごとの見出しに変化します。

ピボットテーブルの列タイトルが変化したイメージ

または、「ピボットテーブルのフィールドリスト」内の「列ラベル」位置に、下図のようにドラッグ&ドロップしても同様になります。

ピボットテーブルのフィールドをドラッグしているイメージ

同様に、「担当者」を「行」の位置にドラッグ&ドロップします。

ピボットテーブルの行タイトルが変化したイメージ

このかたちになると集計表っぽくなってきます。あとは、「値」をクロス部分(値フィールド)に表示すれば、月ごとの担当者別売上表になります。同様にフィールドリストの「売上高」を「値」の位置にドラッグ&ドロップしましょう。

ピボットテーブルの値が変化したイメージ

値フィールドにはそれぞれの合計値が自動計算されて表示されます。このように、ピボットテーブル機能を使うと、非常に簡単にクロス集計表を作成することができます。

また、複数のフィールドを追加することも可能です。行位置に「商品名」、レポートフィルター位置に「カテゴリ」をドラッグして、さらに細かく分化した表を作成してみましょう。

ピボットテーブルが細分化したイメージ

上図のように、「担当者」のなかの「商品名」別といった複数フィールドによる集計を行うことができます。また、レポートフィルター位置にドラッグした「カテゴリ」は、カテゴリ別のフィルターとして使うことができます。

下図のようにリストボタンをクリックすると、商品のカテゴリが表示され、任意のカテゴリを選択すると、そのカテゴリのみが集計されます。

ピボットテーブルの「レポートフィルター」部分が変化したイメージ

例えば、「道具」でフィルタリングすると、下図のように「道具」のみでのクロス集計表が再作成されて表示されます。

ピボットテーブルを任意にフィルタリングして再集計したイメージ

では、次に集計された表からグラフを作成してみましょう。

上記と同様に、それぞれ任意のフィールドをドラッグ&ドロップしてグラフ化する「ピボットグラフ」機能もありますが、基本的にはまず集計表を作成してから、そのデータを使ってグラフ化するというのが多いと思います。

したがって、初めに集計した担当者ごとの合計値(下図)をグラフ化してみましょう。

担当者ごとの合計値が集計されたピボットテーブルのイメージ

上図のグラフに戻すには、下図のとおりピボットテーブルのフィールドリストから、不要なフィールドのチェックマークを外します。この場合は、「商品名」のチェックを外して、フィルター位置の「カテゴリ」を「すべて」に変更するか、チェックを外せばOKです。

ピボットテーブルのフィールドリストのイメージ

それから、通常どおりグラフ化すればよいのですが(グラフの作成については、グラフの作成(1) を参照してください)、ピボットテーブルの場合は、マウスで範囲指定するときにフィールドが選択されてしまい、ドラッグのかたちになってしまうので、うまく範囲指定できません。

そこで、ピボットテーブルでは表内の任意のセルをカレント状態にして、グラフの作成ボタンをクリックすると、ピボットテーブルのグラフが作成できます。つまり、あるひとつのセルを選択してグラフボタンを押すだけで、正しい範囲のグラフが作成されます。

ピボットテーブルのグラフを作成しているイメージ

ピボットテーブルから作成したグラフには、フィールドごとにフィルターが自動作成され、グラフでもフィルタリングすることができます。

ピボットテーブルからグラフを作成したイメージ

このように、蓄積されたデータを効率的に活用したいときにはピボットテーブル機能が有効です。

最後に、ある注意点をしっかり覚えておきましょう。ピボットテーブルでは、

値が変更したときには、「更新」処理をしないと変更が適用されない

ので注意が必要です。

入力ミスなどで元データの値を変更したときは、自動で再計算されない ので、下図のとおり、ピボットテーブル内をカレント状態にし、「ピボットテーブルツール」メニューを表示させた状態で、「オプション」タブの「更新」ボタンをクリックします。この処理をしないと更新が適用されないので注意が必要です。適当に値を変更して実験してみてください。

「ピボットテーブル」メニューの「オプション」タブの「更新」ボタンのイメージ

さて、では下記の練習課題に取り組んでみましょう。この練習課題は、よくあるアンケートの集計に便利な例題です。蓄積されたアンケート結果を、任意のフィールドごとにいろいろな角度から集計してみましょう。

練習課題
課題9 ピボットテーブルの練習課題

これといっての解説はありませんが、課題9のような場合は、例題とは異なり、「売上高」のような合計値となる値がありません。(下図のように、値となるフィールドがない)

ピボットテーブルの値フィールドのイメージ

値としては、〇〇という回答が「〇件」という件数があるのみです。したがって、値フィールドには、行や列に使用したものと同じフィールドをドロップします。

例えば、上図のように「デザイン」を「居住区」別に集計する場合、値フィールドには「デザイン」をもう一度ドラッグすれば、その件数の合計を計算してくれます。

ピボットテーブルの値フィールドに値が表示されているイメージ

ピボットテーブルには様々な活用方法があると思われますので、ぜひ練習して活用の場を広げていただけたらと思います。

ページトップへ戻る


関連情報&オススメ

JALカードはJALマイレージバンク機能に、各提携会社のクレジット機能が備わったクレジットカードです。フライトでもショッピングでも効率よくマイルをためることができます。

マイルをためようとている方、旅行好きな方には最適なクレジットカードです。JAL派とANA派に分かれますが、JAL便を多く利用するなら、はっきり言ってJALカードは「必須」です。

JAL便の場合、チェックインも搭乗手続きもJALカードをかざすだけで可能になりますし、カード付帯保険や各地の特約ホテルで割引などもあります。

たまったマイルは特典航空券と交換でき、マイルをためることで無料で旅行を楽しむことができたりもします。その他にも様々な商品や各地で使えるクーポン、電子マネーやAmazonギフト券などとも交換することができます。

私はクレジットカードはすべてJALカードにまとめ、電話代等の引落しもJALカードを使っています。すると、月々の支払いによってマイルが自動的たまり、数年に一度は無料で飛行機に乗ったり、Amazonギフト券に交換したりと重宝しています。

ページトップへ戻る

更新履歴

2012年4月20日
ページを公開。

ページトップへ戻る

参考文献・ウェブサイト

当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。

文献
なし
ウェブサイト
なし

ページトップへ戻る