ピボットテーブルの使い方!クロス集計など基本的な使用方法も解説!

ピボットテーブルの使い方!クロス集計など基本的な使用方法も解説!

Excelを仕事で使っている人は少なくないですが、もっと効率よくデータの集計をしたいと思いませんか。ピボットテーブルの使い方を知っていれば、それが効率アップにつながります。Excelのピボットテーブルの使い方は難しくありません。

記事の目次

  1. 1.ピボットテーブルとは
  2. 2.ピボットテーブルの使い方【作成方法】
  3. 3.ピボットテーブルの使い方【操作方法】
  4. 4.ピボットテーブルの使い方【クロス集計】
  5. 5.ピボットテーブルの使い方【削除方法】
  6. 6.Excelを使ったデータ集計のテクニック
  7. 7.ピボットテーブルの使い方は簡単でデータの集計に便利

ピボットテーブルとは

Photo byPixaline

Excelで家計簿を付けたり、会社の経理データの一覧表を作成することは、ごく普通に見られます。しかし、たくさんの数字が並んでいるだけではその傾向を知ることはできません。そこで並び替えや集計をしますが、明確にはなりません。そのための便利な機能がピボットテーブルです。例えば買い物のデータがあり、その内容を把握するために使います。

Excelでは簡単にピボットテーブルを作成することができますから、使い方は難しくありません。応用範囲が広いため仕事やプラベートなど、いろいろな場面で役立つことは間違いありません。具体的な使い方について、これから詳しく説明しましょう。

元データを表にして集計するExcelの機能

Photo byOpenIcons

1行目に項目名を入れて、2行目以降にデータを入力し、最後に合計を表示させた単純な表をイメージしましょう。これはよく使うパターンです。ただ、この合計では各行の項目の違いによる数字を知ることはできません。そこでピボットテーブルを利用します。データ入力の時点からピボットテーブルを意識して項目を作成しておくことがポイントです。

この表で言えば、項目の内容で同じデータをまとめることを想定しています。もちろん、一つの項目だけではなく複数の項目でもピボットテーブルを作成することはできますが、項目が増えすぎると収拾がつかなくなってしまいます。

機能的には項目数を増やすことはできますが、作成された表を直感的に理解することが難しくなるため、現実的とは言えません。

ピボットテーブルの使い方【作成方法】

Photo byjanjf93

まず、ピボットテーブルの作成方法を説明します。ピボットテーブルに使用するデータは同じファイル内にあることが多いいのですが、別のファイルやデータベースから取り出すこともできます。もちろん、これはExcelの基本機能であり、ピボットテーブルだけが特別ではありませんから、すでに利用している方もおられるでしょう。

表形式になったデータを入力して、特定の項目で集計をします。作成された表内のセルにカーソルを合わせた状態でメニューから挿入をクリックし、ピボットテーブルのボタンをクリックします。

元データの範囲を設定する

Photo byOpenClipart-Vectors

画面上に「ピボットテーブルの作成」のウィンドウが開きテーブルの範囲などを変更できます、テーブルの範囲は自動的に選択されていますから、特別な処理が必要ないなら、そのままOKを押しましょう。一般的なデータは1行目に項目名が書かれており、2行目から1行1データのデータ列として表現されているとExcelは認識しています。

ピボットテーブルはこの表データを基にして作成しますが、必要に応じて一部のデータだけを利用することもできます。また、範囲は後からでも自由に変更することができます。ただ、多くの場合、その必要はないでしょう。

新しいシートにピポットテーブルが作成される

Photo byjanjf93

自動的に新しいシートが作成されて、ピボットテーブルの項目を選ぶウィンドウが開きます。ここから、必要なフィールドを選択していきます。選択は行と列に必要な項目をドラッグするだけです。特に難しい作業ではありませんが、作成したいピボットテーブルに合わせて項目を選ばなければなりません、Excelの場合、この作業は自動化されていません。

その理由はピボットテーブルの使用目的に合わせて適切に項目を選ぶことが重要なポイントだからです。作成された表で何を知りたいのかを意識することが大切でしょう。

ピボットテーブルの更新機能の使い方

Photo byEsaRiutta

ピボットテーブルに使用するデータは同じファイル内にあるとは限りません。特に仕事で使用するようなデータの場合はデータベースサーバーからデータを取り出すケースの方が多いでしょう。この場合、データの更新タイミングはExcelからはわからないため、自動更新は現実的ではないのです。

同じファイル上にあるデータの場合でも、一度作成したピボットテーブルの元データを修正しなければならないケースもあります。修正した場合は更新ボタンでピボットテーブルの更新を行います。メニューのオプションを選ぶと「更新」と「すべて更新」が選択できます。

ピボットテーブルの更新を自動的に行うことも可能ですが、データ量が多かったり、別のデータベースにあるデータを参照していたりする場合はと意外と時間がかかります。そのため、Excelでは初期設定は自動更新なしになっているのです。

ピボットテーブルの使い方【操作方法】

Photo bygeralt

ピボットテーブルの基本的な使い方を説明しましょう。ピボットテーブルは行と列の項目に合わせて、分類した上で集計した表です。例えば、シートでは行方向に日付、列方向に支払い方法を設定すれば、金額がそれぞれのセル内で合計された表となるでしょう。どの項目を選択するかは自分で決めなければなりません。

「レポートに追加するフィールドを選択してください」で日付を選んで行ラベルの欄にドラッグします。同じように支払い方法を列ラベルに、金額をΣ値にドラッグします。どの項目でピボットテーブルを作成するかは分析しようとする内容で変わってくるのです。

日毎の支払い方法を調べているのであれば日付を行ラベルに設定しますが、日付が必要ないのであれば、行ラベルから削除すればいいだけです。そうすれば支払い方法の種類分の列を持った1行だけのピボットテーブルとなります。

値の集計

Photo bymohamed_hassan

データの集計は行や列の最後に表示された合計をイメージする人が少なくありませんが、ピボットテーブルでは各セルに表示されている数字がすでに集計されていることを忘れてはいけません。行と列に該当するデータを合計した数字が表示されているのです。そして、各行と列の最後の総計は各セルの合計を表しています。

どの項目が突出しているかを知るためにはピボットテーブルは非常に有効なツールとなります。初期状態では行と列のそれぞれについて集計した数字が表示された表です。

必要ない場合はメニューのデザインから総計を選び集計したい項目を選べばいいでしょう。行だけ、列だけ、どちらも集計しないなどの4パターンが選べるようになっています。縦と横の集計が必要ない場合も少なくありませんが、一般的には両方の集計を表示させることが多いため、初期値は行と列の集計を行うようになっています。

項目の設定

行ラベルと列ラベル、Σ値にどのフィールドを設定するかによって、作成されるピボットテーブルは変わってきます。分析すべき内容に適したフィールドを設定することが重要となります。項目の設定はフィールド名をそれぞれのマス目にドラッグするだけで、使い方は非常に簡単です。ピボットテーブル以外でも同じような操作があるはずです。

それぞれのフィールドを変更するとすぐにピボットテーブルは更新されますから、結果を確認しながら表を作成することができます。注意しておかなければならないのは、データ数が多い場合には更新に時間がかかることです。

そのため「レイアウトの更新を保留する」のチェックボタンがあります。チェックしておくとリアルタイムに表の変化を確認することはできず、設定後に「更新」ボタンをクリックしなければなりません。

項目の入れ替え

Photo by Marufish

ピボットテーブルの作成は行ラベルと列ラベルを設定することで完成しますが、どの項目を選んだ方がいいかわからない時もあるでしょう。このような時はまず、テーブルを完成させてみて、その表をじっくり見てみることが大切です。自分が必要な数字が表現されているかを確認します。不十分なら項目を選びなおせばいいのです。

Excelのピボットテーブルでは行ラベル、列ラベル、Σ値は自由に入れ替えることができます。入れ替えた項目に応じて、すぐにデータが更新されますから、目で確認しながら、項目を選ぶことが可能です。

それぞれをドラッグしてもいいですし、右クリックでメニューを表示させれば使い方がわかります。いずれにしても、使い方をマスターするためには自分で試してみることが一番早道です。簡単な表を作成して、いろいろと試してみることをおすすめします。

項目の削除

Photo by3D_Maennchen

一度行ラベルや列ラベルに設定したフィールドを削除する場合は、右クリックで図のメニューを表示させましょう。その中からフィールドの削除をクリックします。マウスでドラッグしても削除することができます。行ラベル、列ラベルから「レポートに追加するフィールドを選択してください」へドラッグするだけです。

集計項目の並び替え・フィルターの利用

Photo by gaspartorriero

まず、項目の並べ替えを説明しましょう。行ラベルの部分にカーソルを置いて右クリックするとメニューが表示されますから、並び替えをクリックするだけです。通常のデータの並び替えと同じように昇順、逆順などを選べます。それから、ピボットテーブルのオプションメニューを表示させて「スライサーの挿入」を選ぶとフィルター処理ができます。

選択した項目のデータ種別に応じてについてフィルター処理を行うことができます。文字列であればリスト表示されますし、数字であれば範囲を指定します。

元データにフィルター処理を行ったデータによってピボットテーブルが作成されます。リスト表示された項目を複数選択するにはCtrlを押しながら左クリックで選びます。すでに選んだ項目をクリックすると非選択状態となります。スライサーを使うメリットは、選択した状態とピボットテーブルを同時に視覚化できることです。

日付ごとの集計

Photo bygeralt

多くのデータは日付に紐づけられることが多いため、日付を使って並び替えることが多くなります。ピボットテーブルでも通常の票と同じように日付で並び替えることができます。

日付を行ラベルまたは列ラベルに設定しているなら、そのラベル部分を選んで、右クリックメニューから並び替えを選択します。昇順・逆順も自由に選べます。必要なら、月毎にまとめることもできます。

ピボットテーブルの使い方【クロス集計】

Photo by Shinichi Haramizu

ピボットテーブルとは複数の項目でデータを分類した上で集計する時に使います。たくさんのデータから一つの傾向を見つける場合です。項目は二つ以上となりますから、3次元以上のデータをイメージすればいいでしょう。表形式だけではなく、グラフとして表示することできますから、使い方を知っておくと応用範囲はたくさんあります。

集計したい項目をドラックする

クロス集計に使用する項目はフィールドの一覧に表示された内容を選んでマウスでドラッグするだけです。「行ラベル」「列ラベル」「Σ値」のいずれかにドラッグしましょう。必要に応じて複数の項目を行ラベルとして設定することもできます。この場合は行ラベルの中に小計が表示された状態となります。

ただし、たくさんの項目をラベルに設定すると、データが細分化されてしまうため、わかりにくい表になることでしょう。表を作成しながら選ぶとわかりやすいでしょう。

フィルターの利用でより便利になる

Photo byjanjf93

データ分析において重要なのはフィルターです。フィルターはたくさんのデータから必要な部分を抜き出すために使用します。Excelのピボットテーブルでも使用することができます。ピボットテーブルにすることでデータの傾向や集計がわかりやすくなりますが、不要なデータをフィルターで取り除くことで、よりシンプルな表が完成します。

経理をやっている人ならわかりやすいでしょう。例えば、経費データから直接費をフィルターで取り除けば間接費だけのピボットテーブルがワンタッチで完成します。

ピボットテーブルの使い方【削除方法】

Photo bymohamed_hassan

ピボットテーブルを削除するには、シート全体を削除してもいいですが、テーブルを選んだ状態でDelキーを押します。通常のExcelのデータ削除と同じと考えていいでしょう。もちろん、誤って削除した場合には「元に戻す」の操作で復元できますから、気軽に試してみることができます。

ピボットテーブル全体を選ぶのが重要

Photo byniekverlaan

ピボットテーブルを削除する時に注意しなければならないことがあります。それはテーブル全体を選ぶことです。上図のように行ラベルのマス目だけを選んだ状態で削除すると行ラベルの文字が削除されます。また、列全体を選ぶと列項目が削除されます。この点に注意するのです。それぞれの違いはマウスの形でわかります。

ピボットテーブル上でマウスをゆっくり動かしてみてください。マス目の境目に来た時にマウスの形が変わるはずです。ピボットテーブル全体を選ぶにはテーブルの左上のマス目の上近くでクリックすることです。これによってテーブル全体を選ぶことができます。

Excelを使ったデータ集計のテクニック

Photo by gaspartorriero

Excelには単純な表を作成するだけではなくたくさんの機能がありますが、すべての使い方を知っている人は少ないでしょう。ただ、自分の仕事の効率化を図るために機能の概要を知っておくことをおすすめします。そして、実際に使用する時に細かい使い方を調べればいいのです。ピボットテーブルの作成も一つのテクニックに過ぎません。

ゴールシークの使い方は?エクセルで逆算する方法をご紹介! | 副業・暮らし・キャリアに関するライフスタイルメディア
エクセルの「ゴールシーク」について解説します。ゴールシークとはどのような機能なのか、ゴールシークの使い方について、数値を逆算する機能、関数を利用した場合、利用するメリットからエクセルを開いてからの操作方法など実際に目標値を設定して具体的に説明して行きます。
エクセルの中央値とは?MEDIAN関数の使用方法や平均値との違いも解説! | 副業・暮らし・キャリアに関するライフスタイルメディア
エクセル上での中央値の出し方についてご紹介します。統計処理やグラフ作成時において混同しやすい、平均値と中央値の違いなども一緒にご紹介します。こちらの記事をご覧いただければ、エクセル上で関数を使って中央値を算出できるようになるでしょう。

ピボットテーブルの使い方は簡単でデータの集計に便利

Photo byClker-Free-Vector-Images

Excelで合計を表示させた表はごく普通に見ることが多いでしょう。しかし、ピボットテーブルを使用している人は意外と少ないのです。ピボットテーブルの使い方を知っている人は、その便利さを実感できます。ほとんどの処理が自動で行われるため、専門的な知識は必要ありません。業務で多くのデータをパソコンで処理している人にはおすすめのデータ集計方法です。

noahno
ライター

noahno

よろしくです。

関連するまとめ

人気の記事