ピボットテーブルとスライサーを使用して Excel でリアルタイムの対話型ダッシュボードを作成する

画像提供：Freepik
Excel の動的なダッシュボードを使用すると、データをリアルタイムで操作できます。 Excel はデータ分析に非常に強力であり、ピボットテーブルとスライサーを組み合わせると、自動的に更新される動的で対話型のダッシュボードが作成されます。

この記事では、Excel でピボットテーブルとスライサーを使用して動的なダッシュボードを構築する方法を説明します。

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

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

データセットの要件:

• 表形式である必要があります（結合されたセルは不可）。
• 各列には明確で固有のヘッダーが必要です。
• 空白の行や列はありません。
• すべての列が適切な書式設定になっていることを確認します。
  • 日付 列を短い日付として表示 .
  • 単価 と総売上高 通貨として .
  • 販売数 数値として 小数点なし .
• Excel テーブルに変換:
  • すべてのデータを選択するか、Ctrl+A を押します。 .
  • Ctrl+T を押します。 または [挿入 ] に移動します。 タブ>> テーブルを選択します .
  • テーブルにヘッダーがあるにチェックを入れます .
  • [OK] をクリックします。 .

• テーブルに名前を付けます:
  • テーブル内の任意のセルを選択した状態で
  • テーブル デザインに移動します。 タブ>> テーブル名を変更します 販売データへ .

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

地域と製品カテゴリごとの売上を示すピボットテーブルを作成しましょう。

• SalesData 内の任意の場所をクリックします。 テーブル。
• 挿入 に移動します。 タブ>> ピボットテーブルを選択します .
• テーブル範囲または名前が正しいことを確認し、新しいワークシートを選択します。 .
• [OK] をクリックします。 .

• ピボットテーブル フィールド ペイン:
  • 領域をドラッグします フィールドを行に追加します。
  • 製品カテゴリをドラッグします。 フィールドを列に追加します。
  • 総売上高をドラッグします。 フィールドを値に設定します。
• 最初のピボットテーブルには、各地域で各商品カテゴリが生み出す収益が表示されます。

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

次に、スライサーとタイムライナーを追加して、このデータを対話的にフィルタリングします。

スライサーを挿入:

• ピボットテーブルの任意のセルを選択します。
• ピボットテーブル分析に移動します。 タブ>> スライサーの挿入を選択します。 .
• 営業担当者を選択します と日付 .
• [OK] をクリックします。 .

タイムラインを挿入:

• ピボットテーブルの任意のセルを選択します。
• ピボットテーブル分析に移動します。 タブ>> タイムラインの挿入を選択します .
• 日付を選択します .
• [OK] をクリックします。 .

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

さまざまな営業担当者をクリックし、ピボットテーブルがどのように自動的に更新されるかを観察して、スライサーとタイムライナーをテストしてください。

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

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

ピボットテーブル 2:月次売上傾向

• SalesData 内の任意の場所をクリックします。 テーブル。
• 挿入 に移動します。 タブ>> ピボットテーブルを選択します .
• テーブル範囲が正しいことを確認し、新しいワークシートを選択します。 .
• [OK] をクリックします。 .

• ピボットテーブル フィールド ペイン:
  • 日付をドラッグします 行 へ エリア。表示されます。
    • 月（日付）
    • 日数 (日付)
    • 日付
  • 総売上高をドラッグします 価値観へ

ピボットテーブル 3:販売単位別の上位商品

• ピボットテーブル 3 と同じワークシートに別のピボットテーブルを作成します。
• ピボットテーブル フィールド ペイン:
  • 製品名をドラッグします 行へ
  • 販売数をドラッグします 価値観へ

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

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

• 営業担当者を右クリックします。 スライサー。
• [接続のレポート] を選択します。 .

• リスト内のすべてのピボットテーブルを確認します。
• [OK] をクリックします。 .

• 日付 について繰り返します スライサー。

これで、特定の営業担当者または日付範囲をクリックすると、3 つのピボットテーブルすべてが同時に更新されます。

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

ピボットテーブルを視覚的なグラフに変換します。

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

• ピボットテーブル内の任意の場所をクリックします。
• ピボットテーブル分析に移動します。 タブ>> ピボットグラフを選択します .
• クラスタ化された列を選択します チャート。
• [OK] をクリックします。 .
• グラフの書式を設定します:
  • グラフのタイトルを追加します。
  • 軸をカスタマイズします。
  • 不要な要素（不要な場合はグリッド線や凡例など）を削除します。

月次売上傾向の場合:

• ピボットテーブル内の任意の場所をクリックします。
• ピボットテーブル分析に移動します。 タブ>> ピボットグラフを選択します .
• マーカーのある線を選択します。 チャート。
• [OK] をクリックします。 .

トップ製品の場合:

• ピボットテーブル内の任意の場所をクリックします。
• ピボットテーブル分析に移動します。 タブ>> ピボットグラフを選択します .
• バーを選択します チャート。
• [OK] をクリックします。 .

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

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

次に、すべてをまとめて、まとまったダッシュボードにまとめましょう。

• ダッシュボード ワークシートの名前を Sales Dashboard に変更します。 .
• チャートを配置します:
  • 地域/カテゴリ 左側のグラフ。
  • 月ごとの傾向 真ん中のグラフ。
  • 人気の製品 右側のグラフ。
• アクセスしやすいようにスライサーを上部に配置します。
• テキスト ボックスを使用してタイトルを追加します:販売実績ダッシュボード .

ステップ 8:書式設定による強化

ダッシュボードをより視覚的に魅力的なものにしてみましょう:

• 地域/カテゴリ ピボットテーブルに条件付き書式を適用する :
  • データ セルを選択します。
  • ホーム に移動します 条件付き書式のタブ>>>>カラー スケールから>>緑、黄、赤を選択します .
• 月次傾向の書式設定 チャート:
  • グラフをクリックします。
  • グラフのデザインに移動します。>> スタイル 5 を選択します。 (または好みのスタイル)。
  • グラフのタイトルを追加:月次売上傾向 .
• トップ商品のフォーマット チャート:
  • データラベルを追加します。
  • グラフのデザインに移動します。>> [グラフ要素の追加] を選択します。>> データラベルを選択します .
  • 降順に並べ替えます。
  • グラフのタイトルを追加:トップ商品 .

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

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

• 日付が適切にグループ化されていない場合は、ソース データ内で日付の形式になっていることを確認してください。
• スライサーがすべてのピボットテーブルを更新していない場合は、レポート接続を再確認してください。
• 大規模なデータセットでのパフォーマンスの問題については、ピボットテーブル オプションの「レイアウトの更新を延期」を使用することを検討してください。

高度なテクニック

利益率を示す計算フィールドを作成します。

• ピボットテーブルをクリックします。
• ピボットテーブル分析に移動します。 フィールド、項目、 セットのタブ>>>> 計算フィールドを選択します .
• 名前を「利益 」にします 次の数式を入力します。

• [追加 ] をクリックします。 そしてOK .

選択に応じて変化する動的なタイトルを追加します。

• GETPIVOTDATA() を使用して、現在フィルタリングされている合計を取得します。
• 次の数式を挿入します。

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

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

結論

上記の手順に従うことで、ユーザーが販売データを簡単に分析できる、プロフェッショナルでインタラクティブなダッシュボードを作成できます。ピボットテーブルを使用してデータを要約および分析し、ピボットグラフを使用してデータを視覚化できます。インタラクティブなスライサーとタイムラインは、データを自動的にフィルタリングするのに役立ちます。動的 Excel ダッシュボードは、データが追加されると簡単に更新されます。この種のダッシュボードは、販売レポート、KPI 追跡、在庫分析などに役立ちます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!