Excel2013 ピボットテーブルを使った分析
ピボットグラフと条件付き書式
ピボットグラフの作成
ピボットテーブルの集計結果を元に作成されるグラフを「ピボットグラフ」といいます。ピボットテーブルの変更は 即座に「ピボットグラフ」に反映されるので、分析作業をスムーズに実行できます。 ※ピボットテーブル基礎で作成したピボットテーブルを元に引き続き操作を解説しています。操作 月別支店別売上表を元にグラフを作成します。
ピボットテーブル内の任意のセルを選択します。 ①[ピボットテーブルツール][分析]タブの[ツール]グループの[ピボットグラフ]ボタンをクリックします。 [グラフの挿入]ダイアログボックスが表示されます。 ②[集合縦棒]グラフが選択された状態になっていることを確認し、[OK]ボタンをクリックします。 月別の支店別売上を表すグラフが作成されます。 POINT! ピボットグラフを削除するには、 選択された状態で[Delete] キーを押します。ピボットグラフ選択時には、エリアセクションの「行ラベル」が「軸フィールド」に「列ラベル」が「凡例フィールド」に 変更されます。リボンには、ピボットグラフの編集のための「ピボットグラフツール」の各タブが表示されます。
行と列のフィールドの入れ替え
行と列のフィールドを入れ替えることで視点を変えた集計表を作成できます。 ピボットグラフの[ピボットグラフツール][デザイン]タブの「行列の切り替え」ボタンでは、簡単に行と列の フィールドの入れ替えができます。 行エリアに、支店名が配置され、支店別の売上を分析の軸とした集計表となります。 「凡例(系列) フィールド」 「軸(項目)フィールド」条件付き書式の利用
条件付き書式機能では、ピボットグラフと同様にデータの傾向を視覚化することができます。 また、条件を満たすデータに書式を設定して、特定のデータを強調して注目させることもできます。操作 データバーを設定しましょう。
4 月～6 月の売上データに、数値の大小をバーの長さで表すデータバーを設定します。 4 月～6 月の売上データ範囲を選択します。 ①【B5～D10】セルをドラッグします。 ②[ホーム]タブの[スタイル]グループの[条件付き書式]をクリックし、[データバー]の[緑のデータバー]を選択 します。 選択した範囲にデータバーが設定され数値の大小を一目で確認できます。 POINT！ 上位/下位ルールでは「上位(下位)10 項目」 「上位(下位)20％」「平均より上(した)」などの ルールに基づき書式を設定できます。 POINT！ 設定を解除するには、[スタイル]グループの [条件付き書式]、[ルールのクリア]から指定します。詳細データの表示
詳細データの表示
集計表から特徴的なデータを掘り下げて分析する方法を「ドリルダウン分析」といいます。 ピボットテーブルでは分析したいデータを掘り下げて詳細にデータを表示させることができます。操作 東京支店の詳細フィールドを表示しましょう。
売上貢献度の高い東京支店の商品名フィールドのデータの詳細を表示してその要因を分析します。 ①[行ラベル]の[東京支店]のセル【A8】セルをダブルクリックします。 詳細データを表示させたいフィールドを指定します。 ②[商品名]を選択し、[OK]ボタンをクリックします。 東京支店の商品名ごとの売上の詳細データが表示され、階層状の集計表となります。《STEP UP！》
フィールドの展開と折りたたみ フィールドの詳細データを表示すると自動的に 展開ボタンや 折りたたみボタンが表示 されます。ボタンをクリックすることで詳細データ 表示/非表示を切り替えることができます。
フィールド全体の折りたたみ、展開は [アクティブなフィールド]グループの [フィールドの折りたたみ]や、 [フィールドの展開]をクリックします。集計値の明細行の抽出
特徴的な集計値は詳細データを別シートに表示させ、さらに詳細な分析を進めることができます。操作 詳細データを別シートに抽出します。
①東京支店 6 月の紳士スーツのデータをダブルクリックします。 新しいシート(Sheet2)が挿入され紳士スーツの 6 月の売上データだけが別テーブルとして表示されます。レイアウトの変更
階層構造の集計表はレイアウトを変更することで見やすくなります。ピボットテーブルには「コンパクト形式」 「アウトライン形式」「表形式」3 つのレイアウトスタイルが用意されています。 変更するには、[ピボットテーブル]ツール[デザイン]タブの[レイアウト]グループの[レポートのレイアウト]ボタン から選択します。データの絞り込み
様々な切り口でデータを絞り込み分析する方法を確認します。レポートフィルターの利用
レポートフィルターエリアにフィールドを追加すると、フィールドのアイテム（項目）ごとに集計表を切り替えて 分析することができます。操作 レポートフィルターエリアにフィールドを追加します。
①[レポートフィルター]エリアに[エリア]フィールドをドラッグします。操作 「西日本」だけの集計表に絞り込んで表示してみましょう。
①レポートフィルターエリアの の▼をクリックします。 エリアフィールドのアイテム一覧が表示されます。 ②「西日本」を選択し[OK]ボタンをクリックします。 「西日本」だけの集計表が表示されます。 POINT！ 複数のアイテムがある場合[複数のアイテムを選択] をオンにすると複数の項目を選択可能です。
操作 レポートフィルターの項目ごとにシートを表示します。
レポートフィルターエリアのフィールドは、項目ごとにシートを分けてデータを表示できます。 ① の をクリックし、(すべて)を選択します。[OK]ボタンをクリックします。 ②[ピボットテーブルツール][分析]タブ[ピボットテーブル]グループの[オプション]の▼をクリックします。 続けて[レポートフィルターページの表示]をクリックします。[レポートフィルター ページの表示]ダイアログボックスが表示されます。 ③「エリア」が選択されているのを確認し、[OK]ボタンをクリックします。
フィルター機能の利用
列または行ラベルに表示されるフィルターボタンから、条件を指定してデータを絞り込むことができます。
操作 行ラベルのボタンから値フィルターを実行します。
合計売上金額が「10000000」以上の支店のデータだけに絞り込んで、売上集計を行ってみましょう。 値フィルターで抽出条件を指定します。 ①「行ラベル」の ボタンをクリックして[値フィルター]をクリックし、[指定の値以上]をクリックします。 [値フィルター(支店名)]ダイアログボックスが表示されます。 「合計/売上金額」が「10000000」以上という条件を指定します。 ②[次の条件に一致する項目を表示]の「合計/売上金額」となっているのを確認し、次のボックスに 「10000000」と入力します。[OK]ボタンをクリックします。 「合計/売上金額」が「10000000」以上の支店のデータだけの売上集計表に変更されました。フィルターを解除します。
③「行ラベル」の ボタンをクリックして[“支店名“からフィルターをクリア]をクリックします。
《STEP UP！》
ピボットテーブルでのフィルターの種類 ピボットテーブルでは「値フィルター」「日付フィルター」「ラベルフィルター」が用意されており、様々な切り口 でデータを抽出して分析することができます。 事例）「日付フィルター」「指定の範囲内」 売上日フィールドから売上日が 20XX/4/1～4/10 の間の売上を集計 事例）「ラベルフィルター」「指定の値で始まる」 商品名フィールドから商品名が「NX」で始まる商品の売上を集計スライサーの利用
スライサー機能を使用する、様々な条件でフィールドのアイテム（項目）を絞り込むことができます。
操作 スライサーを表示します。
「支店名」「売上日」各フィールドのスライサーを表示します。 ①[ピボットテーブルツール][分析]タブの[フィルター]グループの [スライサー]ボタンをクリックします。 ②[スライサーの挿入]ダイアログボックスで「売上日」「支店名」を選択し、[OK]ボタンをクリックします。 「支店名」「売上日」各フィールドのスライサーが表示されます。③「売上日」のスライサーから「4 月」を、「支店名」のスライサーから「札幌支店」をクリックします。 4 月の札幌支店の売上データに絞り込まれます。 スライザーでは、どのフィールドのどのアイテムに絞り込まれているか一目で確認できます。
POINT！
スライサーのフィルターを解除するに は、 [フィルターのクリア]をクリッ クします。 スライサーを削除するには、スライ サ ー の 外 枠 線 上 を ク リ ッ ク し [Delete]キーを押します。《STEP UP！》
時系列のデータを絞り込むタイムライン機能 タイムラインでは、ドラッグ操作で、簡単に任意の期間のデータに絞り込むことができます。 タイムラインを表示するには、[ピボットテーブルツール] [分析]タブの[フィルター]グループの[タイムライン]ボタ ンをクリックします。絞りたい月をクリックすると、対象の期間のデータに絞り込まれます。集計方法を変え分析する
集計方法の変更 1(総計に対する比率・データの個数)
ピボットテーブルでは 11 種類の集計方法と 14 の計算の種類を指定でき、さらに多角的なデータ分析が 行えます。
操作 「総計に対する比率」を指定して商品別売上構成比を集計します。
①値エリアの任意のセルを選択します。 ②[ピボットテーブルツール][分析]タブ[アクティブなフィールド]の[フィールドの設定]をクリックします。 ③[値フィールドの設定]ダイアログボックスで集計方法の「合計」が選択されているのを確認し、[計算の 種類]タブをクリックします。 ④[計算の種類]で[総計に対する比率]を選択します。[OK]ボタンをクリックします。総計を 100％とした、商品別、売上構成比が表示されました。 値フィールドの見出し名を、わかりやすい名前に変更します。 ⑤【B4】セルをクリックして「売上構成比」と入力します。
操作 売上金額の合計値と構成比を同時に表示します。
さらに、[売上金額]フィールドを[値]エリアに配置することで、売上金額の合計値と構成比を同時に表示 することができます。(下図参照) ①[値]エリアに[売上金額]フィールドをドラッグします。
操作 数量フィールドを値エリアに追加しましょう。
数量フィールドを値エリアに追加して商品別売上数を集計しましょう。 ①[値]エリアに[数量]フィールドをドラッグします。
操作 データの個数を集計します。
売上金額の集計方法をデータの個数に変更することで、商品別、売上回数を集計することができます。 ①[値]エリアに[売上金額]フィールドをもう一度ドラッグします。 集計方法を今度は、ショートカットメニューから変更します。 ②【E4】セル上で右クリックして、表示されるショートカットメニューの[値の集計方法]から[データの 個数]を選択します。 商品別のデータの個数(売上回数)が集計表に追加されました。集計方法の変更 2(基準値に対する比率)
「基準値に対する比率」では「基準フィールド」の「基準アイテム」の値に対する比率や差分を表示できます。
操作 レイアウトを変更します。
①[値]エリアの[合計/売上金額]を残し、それ以外をすべてエリアセクション外にドラッグします。 ②[列ラベル]エリアに[売上日]フィールドを配置します。
操作 4 月の売上を基準とした 5 月、6 月の売上の増減を比率で表示します。
計算方法で「基準値に対する比率」を指定すると、例えば、4 月の売上を基準とした売上比や、前月比を 表示させることができます。 ①「合計/売上金額」の任意のセル（【D5】セル）上で右クリックして、表示されるショートカットメニュ ーの[計算の種類]から[基準値に対する比率]を選択します。[計算の種類]ダイアログボックスが表示されます。 ②[基準フィールド]の▼をクリックして、「売上日」を選択します。[基準アイテム]の▼をクリックして、 「4 月」を選択します。[OK]ボタンをクリックします。 POINT！ 基準アイテムに「前の値」を指定すると、前月比を 表示させることができます。 4 月の売上を 100％とした 5 月、6 月の売上を比率で 表示することができます。
