【EXCEL】データ分析に便利な“ピボットテーブル”の基本的な使い方とは?

今回はエクセルの”ピボットテーブル”機能の使い方です。売上表などを様々な角度から分析するために、かんたんにデータをまとめることができます。基本的な使い方を覚えたら、即実務で活用できます。

 

ピボットテーブルとは?

かんたんに言えば、見出しの項目をドラッグ&ドロップするだけでテーブルを作成してデータ分析できる機能です。

メリット

速い

ドラッグ&ドロップするだけで、一発で分析表が完成します。

自由な分析ができる

列、行の入れ替えや表示変更などで必要に合わせたテーブルができるので、自由なデータ分析が可能です。

ミスが少なくなる

関数を使う必要がないので、ミスを減らせます。

 

 

ピボットテーブルの基本的な使い方

例として、上の画像のような売上実績表を使って解説していきます。

 

テーブル化しておく

ピボットテーブルを作るための元データとして、データベース形式のテーブルを使います。テーブル化しておくと、データが追加・削除された場合も範囲に含めて集計してくれるからです。

データのあるセルの一つをアクティブにした状態で、「挿入」タブ >「テーブル」をクリックします。「テーブルの作成」ダイアログが表示されます。範囲を確認して「OK」をクリックします。ショートカットを使う場合は、Ctrl + T でできます。テーブル(Table)の”T”と覚えられますね。

 

かんたんにテーブル化できました。セルの結合があったり、空白の行があったり、1セル1データでない場合などは、ピボットテーブルでうまく集計してくれないので、事前に修正しておきましょう。

 

ピボットテーブルを表示させる

テーブル内であればどのセルでもOKなのでアクティブにして、「挿入」タブ >「ピボットテーブル」を選択してください。

 

上の画像のようにダイアログが開きます。ピボットテーブルを配置する場所が「新規ワークシート」になっていることを確認して「OK」をクリックします。

 

元データがあるシートとは別のシート(画像ではSheet2)が作成されます。そのシートの左側に「ピボットテーブル1」という枠(青囲み部分)が表示されます。これで下準備は完了です。

フィールドやタブ

ピボットテーブルのフィールド

画像のように、ピボットテーブルの枠内にアクティブセルをもってくると、右側には「ピボットテーブルのフィールド」が表示されます。このフィールドは、ドラッグ&ドロップで移動も可能です。基本的に移動しなくてもOKですが、見やすく便利な位置に移動してもいいでしょう。

ピボットテーブル分析のタブ

ピボットテーブルの枠内にアクティブセルがある時、上部のタブに追加表示されます。ピボットテーブルに関連した様々な編集ができますが、この点は後述します。

 

おもに「ピボットテーブルのフィールド」を使ってピボットテーブルを作成していきます。右上の×でダイアログを消してしまった場合、「ピボットテーブル分析」タブにある「フィールドリスト」をクリックして再表示できます。元の位置に戻したい時は、ドラッグして右端にあてると戻ります。

 

項目をドラッグしてボックス内に配置する

ピボットテーブルを作る基本動作は、ドラッグ&ドロップです。上のボックスにある項目の一つをドラッグして、4種類ある下のボックスにドロップしていきます。

 

各ボックスについて

フィルター:
集計の絞り込みができる
列:
横方向に項目を配置できる
行:
縦方向に項目を配置できる ※
※ 縦に長くなる項目を配置すると良い。多くの人が縦スクロールに慣れているからです。
値:
数値の入った項目を配置すると集計できる

 

配置してみる

実際にエクセル上で配置してみると分かりやすいと思います。

行ボックスに「店名」を入れています。まず、縦方向に店名が並びました。

 

値ボックスに「売上実績」を入れました。先程入れた「店名」別に合計が表示されます。値ボックスは、数値が入った項目を入れるということでしたね。

 

列ボックスに「日付」を入れています。月別の小計が確認できるようになりました。

 

行ボックスに「担当」を追加しました。先程入れた「店名」の下に配置すると、各店の担当者別の売上実績も加わりました。

 

このように、いつ、だれが、どれだけといった要素で分析できるテーブルが、かんたんに作成できました。とても便利ですね!

どのボックスにどの項目を入れると良いかは、実際に使っていくとすぐに分かってきます。前述の各ボックスの説明を参考にしながら、ボックスに入れたデータを入れ替えて、どんなピボットテーブルができるか試してみてください。フィルターボックスについては後述します。

 

ボックスに配置したデータをリセットする

2つの方法があります。

1.項目リストのチェックを外す

上のボックスの項目リストには、左端にチェックボックスが付いています。このチェックを外すとリセットできます。

2.何もないところへドラッグ&ドロップする

何も入っていないセルや上のボックスへドラッグ&ドロップすれば、下のボックスから消え、ピボットテーブル上のデータも消えます。

 

 

ピボットテーブルの表示変更

数値をカンマ区切りにする

値ボックスに入っている項目名の横に▼があります。ここをクリックして「値フィールドの設定」を選択します。

 

表示されたダイアログの左下にある「表示形式」をクリックします。

 

見慣れた「セルの書式設定」のダイアログが開きます。分類で「数値」を選び、「桁区切りを使用する」にチェックを入れて「OK」をクリックします。

 

3桁区切りで表示され、見やすくなりましたね。分類で「通貨」や「会計」を選択すると¥マークを付けた表示にできます。

 

最大値を表示させる

先程のように「値フィールとの設定」から、「集計方法」タブにある「最大」を選びます。「OK」をクリックしましょう。

 

集計値が変わりました。このように、各担当者の最大値、その月の最大値、店舗別の最大値が確認できるテーブルに変更できました。

何も設定していなければ「合計」が表示されますが、データを指定すれば、その他「平均」「個数」などの値も集計できます。

 

階層構造の表示・非表示

テーブル内の+や-をクリックして、表示、非表示が可能です。

 

テーブルデザインの変更

「デザイン」タブを選択すると、ピボットテーブルのスタイルの変更ができます。赤丸で囲んだ部分をクリックすると、テンプレートとなるデザインが表示されます。

 

「淡色」「中間」「濃色」に分かれて表示されますので、見やすいものを選べます。

「ピボットテーブルスタイルのオプション」の部分では、見出しや縞模様をチェックボックスでつけたり消したりできます。いろいろ試して好みのスタイルを選択できますね。

 

レポートのレイアウト

「デザイン」タブ内には「レポートのレイアウト」があります。デフォルトで「コンパクト形式で表示」になっていますが、その他のデザインも選べます。

例えば、店舗別の合計を担当者の下に表示したいなら、「表形式で表示」を選択します。

 

こちらのほうが、見慣れていて見やすいかもしれませんね。

 

使い方の注意点

更新が必要

元データである「Sheet1」に変更を加えます。実務でもよくあることですね。上の画像では、大阪の担当者「木村」の売上が、150,000,000に修正されています。

ピボットテーブルのほうを見てみると、どうでしょうか。

 

先程の数値のままです。元データに変更(追加や削除)があっても自動で反映されないということです。ここが注意点で、ピボットテーブルにも反映させるには「更新」が必要です。

テーブル上で、右クリックしてください。

 

表示されるメニューから「更新」をクリックします。

 

ピボットテーブル上でも、元データの変更が反映されました。この更新を忘れないようにしましょう。

 

元データから、たくさんのピボットテーブルを作っている場合は、一括更新ができます。

「ピボットテーブル分析」タブの「更新」から、「すべてを更新」をクリックします。これで一括更新できます。こちらをしておけば確実ですね。

 

 

その他の便利な使い方

フィルター

手動フィルター

担当者でフィルターをかけたい場合です。項目の右にある▼をクリックしてください。通常のフィルター機能と同様に表示させたい項目だけを表示させることができます。

「佐藤」だけにチェックを入れて「OK」してみます。

 

「佐藤」だけのデータが抽出されました。フィルターがかかったので、▼マークが変化しています。ちなみに、日付の▼(青囲み部分)をクリックすれば、さらに月別でフィルターをかけられます。

 

スライサー

「ピボットテーブル分析」タブの「スライサーの挿入」も便利です。「データのフィルター処理を視覚的に実行」できます。クリックしてみてください。

 

「スライサーの挿入」ダイアログが表示されます。例えば、「日付」と「担当」でフィルターをかけるとします。チェックを入れて選択し「OK」します。

 

チェックした項目に応じたスライサーが、右側に表示されます。

 

画像のように、「2月」と「佐藤」を選択してみましょう。すぐにピボットテーブル上に反映されます。こんなふうに、マウス操作だけでかんたんにフィルターがかけられます。

スライサーは複数選択できます。Ctrlキーを押しながら選択すれば、奇数月の「1月」と「3月」を選択したり、「佐藤」と「渡辺」と「鈴木」といった連続していない担当も選択して、テーブル作成ができますよ。

 

ちなみに、フィルターを解除したい場合は、フィルターマーク(赤丸囲み部分)をクリックして「”○○”からフィルターをクリア」を選択するだけです。

 

フィルターボックス

行ボックスに入っていた「担当」を、ドラッグ&ドロップでフィルターボックスに移動させてみましょう。上の画像のように、ピボットテーブルの左上にセクションが設けられ、「担当」でフィルターをかけられるようになります。

 

例えば、担当者「太田」でフィルターをかけてみましょう。▼をクリックして「太田」を選択し「OK」してください。

 

「太田」だけのデータがテーブルで表示されます。当然ですが、別の担当者を選択すれば、その人のデータがすぐに反映されます。便利ですね。

 

担当者ごとのシートを一括作成する

担当者が多い場合、担当ごとにフィルターをかけるのは面倒ですね。かんたんに担当ごとのシートを作成できます。

「ピボットテーブル分析」>「ピボットテーブル」>「オプション」内「レポートフィルターページの表示」をクリックしてみてください。

 

表示されるダイアログで「担当」を確認して「OK」します。

 

これだけで、担当ごとのシートを一括作成してくれます。すごいですね。もちろん、元データを変更したら「すべてを更新」をお忘れなく。

 

 

 

まとめ

いかがでしたか。今回は、エクセルの便利機能の一つ”ピボットテーブル”の基本的な使い方でした。

使っていくと、どんなテーブルになるのかすぐにイメージできるようになります。かんたんにデータ分析をして業務改善につなげたいですね。

では、次回もお楽しみに。

 

 

>コアース株式会社

コアース株式会社

業務システム開発一筋32年

コアースは、オーダーメイドでシステム開発を行う大阪の会社です。 特に中小企業様のニーズにぴったりなシステムをご提案いたします。

CTR IMG