今回はエクセルの”ピボットテーブル”機能の使い方です。売上表などを様々な角度から分析するために、かんたんにデータをまとめることができます。基本的な使い方を覚えたら、即実務で活用できます。
ピボットテーブルとは?
かんたんに言えば、見出しの項目をドラッグ&ドロップするだけでテーブルを作成してデータ分析できる機能です。
メリット
速い
ドラッグ&ドロップするだけで、一発で分析表が完成します。
自由な分析ができる
列、行の入れ替えや表示変更などで必要に合わせたテーブルができるので、自由なデータ分析が可能です。
ミスが少なくなる
関数を使う必要がないので、ミスを減らせます。
ピボットテーブルの基本的な使い方
例として、上の画像のような売上実績表を使って解説していきます。
テーブル化しておく
ピボットテーブルを作るための元データとして、データベース形式のテーブルを使います。テーブル化しておくと、データが追加・削除された場合も範囲に含めて集計してくれるからです。
データのあるセルの一つをアクティブにした状態で、「挿入」タブ >「テーブル」をクリックします。「テーブルの作成」ダイアログが表示されます。範囲を確認して「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」します。
これだけで、担当ごとのシートを一括作成してくれます。すごいですね。もちろん、元データを変更したら「すべてを更新」をお忘れなく。
まとめ
いかがでしたか。今回は、エクセルの便利機能の一つ”ピボットテーブル”の基本的な使い方でした。
使っていくと、どんなテーブルになるのかすぐにイメージできるようになります。かんたんにデータ分析をして業務改善につなげたいですね。
では、次回もお楽しみに。