集計データの分析結果は資料を提出するだけではなく、プレゼンテーション形式で説明を求められることがありますよね。例えば、月次の報告会議などでよくあるシーンです。その際に『支店別のデータが見たい』、『カテゴリー別に絞り込んだ結果はないの？』などの要望が出ることも少なくありません。
用意していたスライドでは想定外の質問に対応しきれず、元のExcelファイルを開いて、その場でフィルターなどを操作して説明した経験のある人も多いのではないでしょうか。今回紹介するテクニックは、Excelで作る簡易的な「ダッシュボード」です。
ピボットテーブルとスライサーを使った「ダッシュボード」の例 支店や製品カテゴリーを切り替えるとグラフの表示も連動する どこかで見たことがありませんか？ 高度なテクニックを駆使して作り込んでいるように見えますが、実はピボットテーブルとスライサーの機能だけで簡単に作成可能です。
グラフ用のピボットテーブルを作成する
初めて「ピボットテーブル」を作成する場合はこちらの記事も参考にしてください。ここでは、元の集計データを「テーブル」に変換した状態から操作します。テーブルに変換していない表を操作する場合は［挿入］タブにある［ピボットテーブル］をクリックします。
テーブル内の任意のセルをクリックして、［テーブルデザイン］タブの［ピボットテーブルで集計］をクリックする。テーブルに変換していない表の場合は［挿入］タブにある［ピボットテーブル］をクリックする ［新規ワークシート］が選択されていることを確認して［OK］をクリックする 新しいシートが追加されて、ピボットテーブルが作成される。フィールドを指定していないため、結果は表示されていない状態 支店ごとの売上が集計される。ピボットテーブル名を付けておく。ここでは「支店」とした 挿入したピボットテーブルをコピーして、離れたセルに貼り付ける ［支店］のチェックを外して、［製品カテゴリー］にチェックを付ける。ここでは「製品カテゴリー」と名前を付けた 同様に作成済みのピボットテーブルをコピー＆ペーストして、［製品名］にチェックを付け直す。ここでは「製品名」と名前を付けた グラフを作成するためのピボットテーブルを用意できました。続けて、通常のグラフと似たような手順で目的ごとの「ピボットグラフ」を挿入していきます。
ピボットグラフを作成する
上記の手順で作成した3つのピボットテーブルを元に「ピボットグラフ」を作成します。ピボットテーブル内の任意のセルを選択して［ピボットテーブル分析］タブの［ピボットグラフ］をクリックするだけです。
データラベルや凡例、軸目盛、グラフタイトルなどの設定方法は、一般的なグラフと同じです。後で調整するため、この段階ではグラフのサイズや色を気にする必要はありません。
1つめのピボットテーブル内のセルを選択して［ピボットテーブル分析］タブの［ピボットグラフ］をクリックする ピボットグラフに表示されるボタンを非表示にする。［フィールドボタン］－［すべて非表示］をクリックする 凡例を選択して、［Delete］キーを押して削除する グラフタイトルを入力して、作業の邪魔にならない位置に移動しておく 2つめのピボットテーブル内のセルを選択して［ピボットテーブル分析］タブの［ピボットグラフ］をクリックする ボタンや凡例は削除し、グラフタイトルを付けておく。［＋］－［データラベル］－［その他のオプション］をクリックする ［分類名］と［パーセンテージ］にチェックを付ける。［自動調整］をクリックする 3つめのピボットテーブル内のセルを選択して［ピボットテーブル分析］タブの［ピボットグラフ］をクリックする ボタンや凡例は削除しておく。［＋］－［軸］－［第1横軸］のチェックを外す グラフタイトルを変更して、大まかにサイズを調整しておく 新しいシートを追加して、作成したグラフをすべて切り取り＆貼り付けします。スライサーを挿入する位置を意識してサイズと位置を調整します。なお、位置やサイズを調整する際に［Alt］キーを押しながらハンドルをドラッグするとセルの罫線に吸着します。
新しいシート（ここでは［ダッシュボード］）を追加しておく。［Ctrl］キーを押しながらグラフをクリックして、すべて選択して［Ctrl］＋［X］キーで切り取る 画面左側にスライサーを挿入するので、スペースを空けて大まかに配置する ［集合横棒］グラフの並び順を変更する。棒をクリックして［データ］タブの［昇順］をクリックする。新しいシートでデータラベルの追加や削除も可能 スライサーと連動させる
仕上げにスライサーを挿入して、それぞれのグラフと連動するように設定します。どのグラフを選択して操作しても構いません。例えばすべての製品を常に表示しておきたいという場合は、あえて連動しない設定としてもいいでしょう。あわせてグラフの色と位置、サイズも調整します。
いずれかのグラフを選択して［ピボットグラフ分析］タブにある［スライサーの挿入］をクリックする フィルターとして利用するフィールドを選択する。ここでは［製品カテゴリー］と［支店］にチェックを付けた スライサーが挿入された。位置とサイズを調整しておく 続けてグラフの色を設定する。［デザイン］タブの［色の変更］から色を選択する ダッシュボードの背景色を設定する。スライサーとグラフが収まるセル範囲を選択して、［ホーム］タブの［塗りつぶしの色］を設定する スライサーを選択して［スライサー］タブの［レポートの接続］をクリックする すべてのピボットテーブルにチェックを付けて［OK］をクリックする 同様に、もう1つのスライサーもすべてのピボットテーブルと連動するように設定する 以上で簡易的なダッシュボードの完成です。一度作ってしまえば、データを更新するだけで瞬時に最新の分析結果を示せます。グラフの要素は目的に応じて設定してください。色使いを工夫するとさらに凝った印象に仕上げられますよ。
