ピボットテーブルとは
Excelで家計簿を付けたり、会社の経理データの一覧表を作成することは、ごく普通に見られます。しかし、たくさんの数字が並んでいるだけではその傾向を知ることはできません。そこで並び替えや集計をしますが、明確にはなりません。そのための便利な機能がピボットテーブルです。例えば買い物のデータがあり、その内容を把握するために使います。
Excelでは簡単にピボットテーブルを作成することができますから、使い方は難しくありません。応用範囲が広いため仕事やプラベートなど、いろいろな場面で役立つことは間違いありません。具体的な使い方について、これから詳しく説明しましょう。
元データを表にして集計するExcelの機能
1行目に項目名を入れて、2行目以降にデータを入力し、最後に合計を表示させた単純な表をイメージしましょう。これはよく使うパターンです。ただ、この合計では各行の項目の違いによる数字を知ることはできません。そこでピボットテーブルを利用します。データ入力の時点からピボットテーブルを意識して項目を作成しておくことがポイントです。
この表で言えば、項目の内容で同じデータをまとめることを想定しています。もちろん、一つの項目だけではなく複数の項目でもピボットテーブルを作成することはできますが、項目が増えすぎると収拾がつかなくなってしまいます。
機能的には項目数を増やすことはできますが、作成された表を直感的に理解することが難しくなるため、現実的とは言えません。
ピボットテーブルの使い方【作成方法】
まず、ピボットテーブルの作成方法を説明します。ピボットテーブルに使用するデータは同じファイル内にあることが多いいのですが、別のファイルやデータベースから取り出すこともできます。もちろん、これはExcelの基本機能であり、ピボットテーブルだけが特別ではありませんから、すでに利用している方もおられるでしょう。
表形式になったデータを入力して、特定の項目で集計をします。作成された表内のセルにカーソルを合わせた状態でメニューから挿入をクリックし、ピボットテーブルのボタンをクリックします。
元データの範囲を設定する
画面上に「ピボットテーブルの作成」のウィンドウが開きテーブルの範囲などを変更できます、テーブルの範囲は自動的に選択されていますから、特別な処理が必要ないなら、そのままOKを押しましょう。一般的なデータは1行目に項目名が書かれており、2行目から1行1データのデータ列として表現されているとExcelは認識しています。
ピボットテーブルはこの表データを基にして作成しますが、必要に応じて一部のデータだけを利用することもできます。また、範囲は後からでも自由に変更することができます。ただ、多くの場合、その必要はないでしょう。
新しいシートにピポットテーブルが作成される
自動的に新しいシートが作成されて、ピボットテーブルの項目を選ぶウィンドウが開きます。ここから、必要なフィールドを選択していきます。選択は行と列に必要な項目をドラッグするだけです。特に難しい作業ではありませんが、作成したいピボットテーブルに合わせて項目を選ばなければなりません、Excelの場合、この作業は自動化されていません。
その理由はピボットテーブルの使用目的に合わせて適切に項目を選ぶことが重要なポイントだからです。作成された表で何を知りたいのかを意識することが大切でしょう。
ピボットテーブルの更新機能の使い方
ピボットテーブルに使用するデータは同じファイル内にあるとは限りません。特に仕事で使用するようなデータの場合はデータベースサーバーからデータを取り出すケースの方が多いでしょう。この場合、データの更新タイミングはExcelからはわからないため、自動更新は現実的ではないのです。
同じファイル上にあるデータの場合でも、一度作成したピボットテーブルの元データを修正しなければならないケースもあります。修正した場合は更新ボタンでピボットテーブルの更新を行います。メニューのオプションを選ぶと「更新」と「すべて更新」が選択できます。
ピボットテーブルの更新を自動的に行うことも可能ですが、データ量が多かったり、別のデータベースにあるデータを参照していたりする場合はと意外と時間がかかります。そのため、Excelでは初期設定は自動更新なしになっているのです。
ピボットテーブルの使い方【操作方法】
ピボットテーブルの基本的な使い方を説明しましょう。ピボットテーブルは行と列の項目に合わせて、分類した上で集計した表です。例えば、シートでは行方向に日付、列方向に支払い方法を設定すれば、金額がそれぞれのセル内で合計された表となるでしょう。どの項目を選択するかは自分で決めなければなりません。
「レポートに追加するフィールドを選択してください」で日付を選んで行ラベルの欄にドラッグします。同じように支払い方法を列ラベルに、金額をΣ値にドラッグします。どの項目でピボットテーブルを作成するかは分析しようとする内容で変わってくるのです。
日毎の支払い方法を調べているのであれば日付を行ラベルに設定しますが、日付が必要ないのであれば、行ラベルから削除すればいいだけです。そうすれば支払い方法の種類分の列を持った1行だけのピボットテーブルとなります。
値の集計
データの集計は行や列の最後に表示された合計をイメージする人が少なくありませんが、ピボットテーブルでは各セルに表示されている数字がすでに集計されていることを忘れてはいけません。行と列に該当するデータを合計した数字が表示されているのです。そして、各行と列の最後の総計は各セルの合計を表しています。
どの項目が突出しているかを知るためにはピボットテーブルは非常に有効なツールとなります。初期状態では行と列のそれぞれについて集計した数字が表示された表です。
必要ない場合はメニューのデザインから総計を選び集計したい項目を選べばいいでしょう。行だけ、列だけ、どちらも集計しないなどの4パターンが選べるようになっています。縦と横の集計が必要ない場合も少なくありませんが、一般的には両方の集計を表示させることが多いため、初期値は行と列の集計を行うようになっています。
項目の設定
行ラベルと列ラベル、Σ値にどのフィールドを設定するかによって、作成されるピボットテーブルは変わってきます。分析すべき内容に適したフィールドを設定することが重要となります。項目の設定はフィールド名をそれぞれのマス目にドラッグするだけで、使い方は非常に簡単です。ピボットテーブル以外でも同じような操作があるはずです。
それぞれのフィールドを変更するとすぐにピボットテーブルは更新されますから、結果を確認しながら表を作成することができます。注意しておかなければならないのは、データ数が多い場合には更新に時間がかかることです。
そのため「レイアウトの更新を保留する」のチェックボタンがあります。チェックしておくとリアルタイムに表の変化を確認することはできず、設定後に「更新」ボタンをクリックしなければなりません。
項目の入れ替え
ピボットテーブルの作成は行ラベルと列ラベルを設定することで完成しますが、どの項目を選んだ方がいいかわからない時もあるでしょう。このような時はまず、テーブルを完成させてみて、その表をじっくり見てみることが大切です。自分が必要な数字が表現されているかを確認します。不十分なら項目を選びなおせばいいのです。
Excelのピボットテーブルでは行ラベル、列ラベル、Σ値は自由に入れ替えることができます。入れ替えた項目に応じて、すぐにデータが更新されますから、目で確認しながら、項目を選ぶことが可能です。
それぞれをドラッグしてもいいですし、右クリックでメニューを表示させれば使い方がわかります。いずれにしても、使い方をマスターするためには自分で試してみることが一番早道です。簡単な表を作成して、いろいろと試してみることをおすすめします。
項目の削除
一度行ラベルや列ラベルに設定したフィールドを削除する場合は、右クリックで図のメニューを表示させましょう。その中からフィールドの削除をクリックします。マウスでドラッグしても削除することができます。行ラベル、列ラベルから「レポートに追加するフィールドを選択してください」へドラッグするだけです。
集計項目の並び替え・フィルターの利用
まず、項目の並べ替えを説明しましょう。行ラベルの部分にカーソルを置いて右クリックするとメニューが表示されますから、並び替えをクリックするだけです。通常のデータの並び替えと同じように昇順、逆順などを選べます。それから、ピボットテーブルのオプションメニューを表示させて「スライサーの挿入」を選ぶとフィルター処理ができます。
選択した項目のデータ種別に応じてについてフィルター処理を行うことができます。文字列であればリスト表示されますし、数字であれば範囲を指定します。
元データにフィルター処理を行ったデータによってピボットテーブルが作成されます。リスト表示された項目を複数選択するにはCtrlを押しながら左クリックで選びます。すでに選んだ項目をクリックすると非選択状態となります。スライサーを使うメリットは、選択した状態とピボットテーブルを同時に視覚化できることです。
日付ごとの集計
多くのデータは日付に紐づけられることが多いため、日付を使って並び替えることが多くなります。ピボットテーブルでも通常の票と同じように日付で並び替えることができます。
日付を行ラベルまたは列ラベルに設定しているなら、そのラベル部分を選んで、右クリックメニューから並び替えを選択します。昇順・逆順も自由に選べます。必要なら、月毎にまとめることもできます。
ピボットテーブルの使い方【クロス集計】
ピボットテーブルとは複数の項目でデータを分類した上で集計する時に使います。たくさんのデータから一つの傾向を見つける場合です。項目は二つ以上となりますから、3次元以上のデータをイメージすればいいでしょう。表形式だけではなく、グラフとして表示することできますから、使い方を知っておくと応用範囲はたくさんあります。
集計したい項目をドラックする
クロス集計に使用する項目はフィールドの一覧に表示された内容を選んでマウスでドラッグするだけです。「行ラベル」「列ラベル」「Σ値」のいずれかにドラッグしましょう。必要に応じて複数の項目を行ラベルとして設定することもできます。この場合は行ラベルの中に小計が表示された状態となります。
ただし、たくさんの項目をラベルに設定すると、データが細分化されてしまうため、わかりにくい表になることでしょう。表を作成しながら選ぶとわかりやすいでしょう。
フィルターの利用でより便利になる
データ分析において重要なのはフィルターです。フィルターはたくさんのデータから必要な部分を抜き出すために使用します。Excelのピボットテーブルでも使用することができます。ピボットテーブルにすることでデータの傾向や集計がわかりやすくなりますが、不要なデータをフィルターで取り除くことで、よりシンプルな表が完成します。
経理をやっている人ならわかりやすいでしょう。例えば、経費データから直接費をフィルターで取り除けば間接費だけのピボットテーブルがワンタッチで完成します。
ピボットテーブルの使い方【削除方法】
ピボットテーブルを削除するには、シート全体を削除してもいいですが、テーブルを選んだ状態でDelキーを押します。通常のExcelのデータ削除と同じと考えていいでしょう。もちろん、誤って削除した場合には「元に戻す」の操作で復元できますから、気軽に試してみることができます。
ピボットテーブル全体を選ぶのが重要
ピボットテーブルを削除する時に注意しなければならないことがあります。それはテーブル全体を選ぶことです。上図のように行ラベルのマス目だけを選んだ状態で削除すると行ラベルの文字が削除されます。また、列全体を選ぶと列項目が削除されます。この点に注意するのです。それぞれの違いはマウスの形でわかります。
ピボットテーブル上でマウスをゆっくり動かしてみてください。マス目の境目に来た時にマウスの形が変わるはずです。ピボットテーブル全体を選ぶにはテーブルの左上のマス目の上近くでクリックすることです。これによってテーブル全体を選ぶことができます。
Excelを使ったデータ集計のテクニック
Excelには単純な表を作成するだけではなくたくさんの機能がありますが、すべての使い方を知っている人は少ないでしょう。ただ、自分の仕事の効率化を図るために機能の概要を知っておくことをおすすめします。そして、実際に使用する時に細かい使い方を調べればいいのです。ピボットテーブルの作成も一つのテクニックに過ぎません。
ピボットテーブルの使い方は簡単でデータの集計に便利
Excelで合計を表示させた表はごく普通に見ることが多いでしょう。しかし、ピボットテーブルを使用している人は意外と少ないのです。ピボットテーブルの使い方を知っている人は、その便利さを実感できます。ほとんどの処理が自動で行われるため、専門的な知識は必要ありません。業務で多くのデータをパソコンで処理している人にはおすすめのデータ集計方法です。