エクセルピボットテーブルの使い方【エクセル上で完全図解説明】
ピボットテーブルを作りたい基データの準備の段階から作業画面であるフィールドリストの各項目の説明を分かり易く図解しています。これをテンプレートとして今日からピボットテーブル分析ができるようになります。
(動画時間:6:02)
エクセル関数を全く使わずに出来るピボットテーブル
こんにちは、リーンシグマ、ブラックベルトのマイク根上です。業務改善コンサルをしています。
以前エクセルのSUMIFS関数を使った期間集計の動画をやり、このコメントを頂きました。⇒「エクセル期間集計: 週間、月間、年間の集計の仕方【データ分析エクセルスキル】」
「なぜピボットテーブルを推奨しない?」
Kai 携帯さん、コメントありがとうございます。Kai 携帯さんのご指摘の様に今回はエクセルのピボットテーブルを推奨します。すぐに集計結果が欲しい時はこのピボットテーブルは早く結果を作れてすごく便利です。
以前やった期間集計の結果が下記で、ピボットテーブルでエクセル関数を全く使わずに同じ事が出来ます。今日はそれを通してピボットテーブルの使い方を学んでみましょう。
ピボットテーブル作成前の準備
同じ基データから出発します(下図参照)。この様なデータをリスト形式やリストデータと言います。各列をフィールドと言い、一番上にフィールド名があります。ピボットテーブルもそうですが、データ分析をする基データはいつもこの型式にする必要があります。
基データが用意出来たらその範囲をマウスで選び、この時に各列のフィールド名が一番上に来るようにして下さい。そしてメニューから「挿入」、「ピボットテーブル」を選ぶと下記の画面が出てきて色々設定を変える事が出来ます。そのまま「OK」を押すと、新しいワークシート上にピボットテーブルを作成出来ます。
僕は基データを横に並べたいので、上記画面内の「既存のワークシート」を選んで、基データの横のセルをクリックして「OK」をクリックするとそこにピボットテーブルを作れます。また画面右に作業画面が出てきます(下図参照)。それを「フィールドリスト」といいます。
ちなみにこの画面はエクセル2016です。バージョンによって多少レイアウトや機能が違うので注意して下さい。
フィールドリストの各項目の説明
まず、各項目の意味を理解しましょう。上半分が基データを表していて、データ範囲をマウスで選んだ時のフィールド名が並びます。ですから基データでフィールド名が一つでも空欄であったらエラーが出て前に進めません。
下半分が欲しい最終結果のレイアウトを表します。その二つを比べてみましょう。最終結果の一番左の部分を「行」と言い、上の部分を「列」と言って、「行」と「列」がクロスしているのでこの表をクロス集計とも言います。
その行がフィールドリストの左下のボックスに対応します。各行には「販売チャネル」を入れたいので上からそれをドラッグして「行ボックス」にドロップします。(下図参照)
前回SUMIFS関数を入れた部分がフィールドリストの右下になり、「値ボックス」です。これは「販売金額」ですので、上からそれをドラッグして「値ボックス」に入れます。(下図参照)
しかしほとんどの場合データの個数が最初に出てきます。しかしここでは合計が欲しいので、数値上で右クリックして「値の集計方法」で「合計」を選ぶと販売金額の合計に変わります。(下図参照)
最終結果は週間売上で、各列には「販売日」を週別にして入れています。フィールドリストでは右上の「列ボックス」に対応します。上から「販売日」をドラッグしてきてそこにドロップします。(下図参照)
データが多いのでエクセルが勝手に年間集計をしてくれています。僕達が欲しいのは過去6週間の集計です。ここにオートフィルターみたいなアイコンがあります(上図セルI8参照)。それをクリックしたら「日付フィルター」があり、そこから「指定の値より後」を選んで、カレンダーアイコンをクリックして、ここから最近6週間に絞れます。
まだ年間集計ですのでそれを週間にしたいです。日付の上で右クリックして「グループ化」を選びます。ここで単位を「日」にして、週別にしたいですのでここの日数を「7」にして「OK」をクリックすると書式は違いますが数値は全く同じに出来ました。
ピボットテーブルの弱点:レイアウトと書式の制限の対応の仕方
ピボットテーブルはかなり優れた機能なのですが、一つの問題が、ピボットテーブル上ではレイアウトや書式の変更するのに色々制限がある事です。そこで僕のお勧めは、ピボットテーブルでは計算までして、レイアウトと書式を別の表でやることです。
ピボットテーブルの結果を見て、表のレイアウトと書式を作ります。ピボットテーブルの結果をコピーして作った表上で右クリックし、「貼り付けのオプション」で「1,2,3」の「値」でペーストすると良いでしょう(下図参照)。
今日はやりませんでしたが、他のフィールドを残りの左上の「フィルターボックス」に入れて簡単にフィルター機能を持たす事も出来ます(下図参照)。
ピボットテーブルは慣れるとかなり複雑な集計も短時間で作れるようになれます。ぜひ習得してみて下さい。
<<データ分析の基本とエクセルスキルシリーズ>>