ピボットテーブルとスライサーでExcelにリアルタイム対話型ダッシュボードを作る方法

Excelで動的なダッシュボードを構築すれば、データとリアルタイムに対話できるようになります。Excelはデータ分析において非常に強力なツールであり、ピボットテーブルとスライサーを組み合わせることで、自動的に更新される動的でインタラクティブなダッシュボードを作成できます。

この記事では、Excelのピボットテーブルとスライサーを使って動的ダッシュボードを構築する手順を詳しく解説します。

ステップ1:データを準備する

ダッシュボードを作成する前に、データを適切に構造化しておくことが重要です。

データセットの要件

• 表形式であること(結合セルは使用しない)。
• 各列にわかりやすい一意の見出しを付ける。
• 空白の行や列を入れない。

すべての列に適切な書式を設定しておきましょう。

• 日付列は短い日付形式にする。
• 単価と売上合計は通貨形式にする。
• 販売数量は小数点以下なしの数値形式にする。

Excelテーブルへの変換

• データ範囲内の任意のセルを選択するか、Ctrl+Aキーを押してすべて選択します。
• Ctrl+Tキーを押すか、挿入タブからテーブルを選択します。
• 「先頭行をテーブルの見出しとして使用する」にチェックを入れます。
• OKをクリックします。

テーブルに名前を付ける

• テーブル内の任意のセルを選択した状態で、テーブルデザインタブを開きます。
• テーブル名を「SalesData」に変更します。

ステップ2:最初のピボットテーブルを作成する

まず、地域別・製品カテゴリ別の売上を表示するピボットテーブルを作成しましょう。

• SalesDataテーブル内の任意のセルをクリックします。
• 挿入タブからピボットテーブルを選択します。
• テーブル範囲または名前が正しいことを確認し、「新しいワークシート」を選択します。
• OKをクリックします。

次に、ピボットテーブルのフィールドペインで以下のように配置します。

• 地域フィールドを行エリアへドラッグします。
• 製品カテゴリフィールドを列エリアへドラッグします。
• 売上合計フィールドを値エリアへドラッグします。

これで、各地域における各製品カテゴリの売上がひと目で確認できる最初のピボットテーブルが完成しました。

ステップ3:スライサーとタイムラインを追加する

次に、スライサーとタイムラインを追加して、データをインタラクティブに絞り込めるようにします。

スライサーの挿入

• ピボットテーブル内の任意のセルを選択します。
• ピボットテーブル分析タブからスライサーの挿入を選択します。
• 「販売担当者」と「日付」にチェックを入れます。
• OKをクリックします。

タイムラインの挿入

• ピボットテーブル内の任意のセルを選択します。
• ピボットテーブル分析タブからタイムラインの挿入を選択します。
• 「日付」にチェックを入れます。
• OKをクリックします。

スライサーとタイムラインをピボットテーブルの横に配置しましょう。

実際に販売担当者をクリックしてみて、ピボットテーブルが自動的に更新される様子を確認してください!

ステップ4:追加のピボットテーブルを作成する

ダッシュボードにさらに2つのピボットテーブルを追加します。

ピボットテーブル2:月次売上トレンド

• SalesDataテーブル内の任意のセルをクリックします。
• 挿入タブからピボットテーブルを選択します。
• テーブル範囲が正しいことを確認し、「新しいワークシート」を選択します。
• OKをクリックします。

• ピボットテーブルのフィールドペインで以下を設定します。
  • 日付フィールドを行エリアへドラッグすると、「月」「日」「日付」などが表示されます。ここでは月を選択します。
  • 売上合計フィールドを値エリアへドラッグします。

ピボットテーブル3:販売数量の多い上位製品

• 同じワークシート上にもう1つピボットテーブルを作成します。
• ピボットテーブルのフィールドペインで以下を設定します。
  • 製品名フィールドを行エリアへドラッグします。
  • 販売数量フィールドを値エリアへドラッグします。

ステップ5:複数のピボットテーブルを同じスライサーに接続する

既存のスライサーをすべてのピボットテーブルに接続しましょう。

• 販売担当者のスライサーを右クリックします。
• レポート接続を選択します。

• リスト内のすべてのピボットテーブルにチェックを入れます。
• OKをクリックします。

同様の手順を日付スライサーについても繰り返します。

これで、特定の販売担当者や日付範囲をクリックすると、3つのピボットテーブルすべてが同時に更新されるようになります!

ステップ6:ピボットグラフを作成する

ピボットテーブルを見やすいグラフに変換しましょう。

地域別/カテゴリ別ピボットテーブルの場合

• ピボットテーブル内の任意の場所をクリックします。
• ピボットテーブル分析タブからピボットグラフを選択します。
• 集合縦棒グラフを選択します。
• OKをクリックします。

グラフの書式設定を行います。

• グラフタイトルを追加します。
• 軸をカスタマイズします。
• 不要な要素(目盛線や凡例など)は削除します。

月次売上トレンドの場合

• ピボットテーブル内の任意の場所をクリックします。
• ピボットテーブル分析タブからピボットグラフを選択します。
• マーカー付き折れ線グラフを選択します。
• OKをクリックします。

上位製品の場合

• ピボットテーブル内の任意の場所をクリックします。
• ピボットテーブル分析タブからピボットグラフを選択します。
• 横棒グラフを選択します。
• OKをクリックします。

ピボットテーブルに加えた変更は、ピボットグラフにも自動的に反映されます。

ステップ7:ダッシュボードのレイアウトを設計する

ここまでの要素を整理して、一体感のあるダッシュボードに仕上げましょう。

• ダッシュボード用ワークシート名を「Sales Dashboard」に変更します。
• グラフを配置します。
  • 地域別/カテゴリ別グラフを左側に配置。
  • 月次トレンドグラフを中央に配置。
  • 上位製品グラフを右側に配置。
• スライサーは操作しやすいよう上部に配置します。
• テキストボックスを使って「Sales Performance Dashboard」というタイトルを追加します。

ステップ8:書式設定で見た目を強化する

ダッシュボードをより視覚的に魅力的にしていきます。

条件付き書式の適用

• 地域別/カテゴリ別ピボットテーブルに条件付き書式を適用します。
  • データセルを選択します。
  • ホームタブから条件付き書式>>カラースケール>>「緑-黄-赤」を選択します。

月次トレンドグラフの書式設定

• グラフをクリックします。
• グラフデザインタブからスタイル5(または好みのスタイル)を選択します。
• グラフタイトルに「Monthly Sales Trend」を設定します。

上位製品グラフの書式設定

• データラベルを追加します。グラフデザインタブからグラフ要素の追加>>データラベルを選択します。
• 降順で並べ替えます。
• グラフタイトルに「Top Products」を設定します。

インタラクティブ性の確認

最後に、スライサーやタイムラインを操作して、すべてのグラフとテーブルが連動して更新されることを確認しましょう。

トラブルシューティングのヒント

• 日付が正しくグループ化されない場合は、元データで日付が日付形式として認識されているか確認してください。
• スライサーがすべてのピボットテーブルを更新しない場合は、レポート接続の設定を再確認してください。
• 大規模なデータセットでパフォーマンスの問題が発生する場合は、ピボットテーブルオプションの「レイアウト変更を保留する」の活用を検討してください。

高度なテクニック

利益率を表示する集計フィールドの作成

• ピボットテーブルをクリックします。
• ピボットテーブル分析タブからフィールド、アイテム、セット>>集計フィールドを選択します。
• 名前を「Profit」とし、以下の数式を入力します。
• 追加をクリックし、OKで確定します。

選択内容に応じて変化する動的なタイトルの追加

GETPIVOTDATA関数を使えば、現在フィルターされている合計値を取り出すことができます。以下の数式を挿入してみましょう。

="Sales Dashboard: "&TEXT(GETPIVOTDATA("Total Sales",$A$3),"$#,##0")

ワークブックのダウンロード

この記事で紹介したサンプルファイルをダウンロードして、実際に手を動かしながら学習することをおすすめします。

まとめ

以上の手順に従うことで、ユーザーが売上データを簡単に分析できる、プロフェッショナルでインタラクティブなダッシュボードを作成できます。ピボットテーブルでデータを集約・分析し、ピボットグラフで可視化できます。スライサーとタイムラインを使えば、データを直感的に絞り込むことが可能です。また、動的なExcelダッシュボードは、新しいデータを追加しても簡単に更新できます。このようなダッシュボードは、売上レポート、KPIトラッキング、在庫分析など、さまざまな業務シーンで活用できるでしょう。