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