Power Query・Power Pivot・VBAで作る本格的なExcelダッシュボード ― データからインサイトまで完全解説

現代のExcelダッシュボードは、単純なグラフや表の域をはるかに超えて進化しています。データ変換を行うPower Query、高度なデータモデリングと分析を担うPower Pivot、そして自動化とインタラクティブ性を高めるVBAを組み合わせれば、ビジネスインテリジェンスツールとして通用するプロフェッショナルなダッシュボードを作成できます。

このチュートリアルでは、Power Query、Power Pivot、VBAを使って高機能なExcelダッシュボードを構築する方法を段階的に解説します。

例として、複数の地域に製品を販売する架空の小売企業の売上ダッシュボードを作成します。使用するデータセットは次のテーブルで構成されています。

• Sales（売上） – 取引データ
• Products（製品） – 製品の詳細とカテゴリ情報
• Customers（顧客） – 顧客情報
• Regions（地域） – 地理情報

ステップ1：Power Queryでデータを取り込む

データのインポート：

• データタブ >> データの取得 >> ファイルから >> テキスト/CSVから を選択します。
• sales_data.txt ファイルを選択し、インポートをクリックします。

• Power Queryエディターが開いたら、データを確認し、以下の変換を行います。
• データ型の変更：任意の列を右クリック >> 型の変更 >> 対象のデータ型を選択します。
• • OrderDate → 日付型
  • Quantity → 整数型
  • UnitPrice と Discount → 10進数型

• 重複行の削除機能を使って、重複データを取り除きます。
• 同じ手順で Products.csv、Customers.csv、Dates.csv もインポートし、それぞれ適切なデータ型変換を適用してください。

Power Queryでデータを変換する：

Power Queryを使って売上データをさらに強化していきましょう。

集計列（カスタム列）の追加：

• Power Queryエディターで Sales テーブルを選択します。
• 列の追加タブ >> カスタム列 を選択します。
• 列名を Revenue とします。
• 以下の数式を入力します。

= [Quantity] * [UnitPrice] * (1-[DiscountRate])

• OKをクリックします。

• 同様にもう1つカスタム列を追加します。
• 列名は Profit とします。
• 以下の数式を入力します。

Profit = [Revenue] - ([Quantity] * [UnitCost])

• OKをクリックします。
• 原価（Cost）を取得するため、Productsテーブルとのマージが必要になります。

テーブルをマージしてさらなる分析につなげる：

• Power Queryエディターで Sales データを開いた状態で操作します。
• ホームタブ >> リボンの クエリのマージ をクリックします。
• • Salesテーブルから ProductID 列を選択します。
  • マージ先として Products テーブルを選び、ProductID で結合します。
  • OKをクリックします。

• テーブルの展開アイコンをクリックし、取り込む列として UnitCost のみを選択します。
• OKをクリックします。

• Profit 列のステップを、展開されたProducts ステップの下へドラッグして移動します。
• これで利益額が正しく計算されるようになります。

• データ変換が完了したら、閉じて次に読み込む… をクリックします。

• データのインポートダイアログで以下を設定します。
• • 接続の作成のみを選択します。
  • 4つすべてのテーブルに対して このデータをデータモデルに追加する にチェックを入れます。
  • OKをクリックします。

ステップ2：Power Pivotでデータモデルを構築する

Power Pivotを開く：

リボンにPower Pivotが表示されていない場合は、まずアドインを有効化しましょう。

• ファイルタブ >> オプション >> アドイン を選択します。
• 管理ボックスで COM アドイン を選択し、設定をクリックします。

• Microsoft Power Pivot for Excel にチェックを入れます。
• OKをクリックします。

• Power Pivotタブ >> 管理 を選択すると、Power Queryから取り込んだデータが表示されます。

リレーションシップ（テーブル関係）を作成する：

• ホームタブ >> ダイアグラム ビュー を選択します。
• • Sales[ProductID] を Products[ProductID] へドラッグします。
  • Sales[CustomerID] を Customers[CustomerID] へドラッグします。
  • Customers[RegionID] を Regions[RegionID] へドラッグします。

• または、デザインタブ >> リレーションシップの作成 から、対応する列を指定して作成することもできます。

計算メジャー（KPI指標）を作成する：

• Power Pivotで Sales テーブルを選択します。
• ホームタブ >> メジャー >> 新しいメジャー をクリックします。
• または、ホームタブの計算領域に直接入力することもできます。
• 以下のメジャーを定義します。

• 総売上（Total Revenue）：

Total Revenue := SUM(Sales[Revenue])

• 総利益（Total Profit）：

Total Profit := SUM(Sales[Profit])

• 利益率（Profit Margin）：

Profit Margin := DIVIDE([Total Profit], [Total Revenue], 0)

• 総注文数（Total Orders）：

Total Orders:=COUNTA(Sales[OrderID])

• 平均注文単価（Average Order Value）：

Average Order Value:=DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0)

• 年初来売上（YTD Revenue）：

YTD Revenue:=CALCULATE([Total Revenue], DATESYTD(Sales[OrderDate]))

• 前年同期売上（Previous Year Revenue）：

Previous Year Revenue:=CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Sales[OrderDate]))

• 前年比成長率（YOY Growth）：

YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0)

ステップ3：ピボットテーブルでダッシュボード部品を作る

ピボットテーブルの作成

• 挿入タブ >> ピボットテーブル >> データモデルから を選択します。
• または、Power Pivotのホームタブ >> ピボットテーブル をクリックします。

• ピボットテーブルの作成ダイアログで以下を設定します。
• • 新しいワークシートを選択します。
  • OKをクリックします。

• 総売上、総利益、利益率、成長率それぞれについて個別のピボットテーブルを作成します。
• ダッシュボードのレイアウトに合わせてセルに配置します。
• 通貨やパーセンテージなど、適切な表示形式を適用しましょう。

グラフとビジュアル化

売上推移グラフ：

• データモデルからピボットテーブルを作成します。
  • 行：Sales[OrderDate[Month]]
  • 値：Total Revenue
• ピボットテーブル分析タブ >> 集合縦棒グラフ を選択します。

上位製品グラフ：

• データモデルからピボットテーブルを作成します。
  • 行：Products[ProductName]
  • 値：Total Revenue
• Total Revenue の降順で並べ替え、上位10件のみ表示するようフィルターします。

• ピボットテーブル分析タブ >> 集合横棒グラフ を選択します。

カテゴリ別パフォーマンス：

• データモデルからピボットテーブルを作成します。
  • 行：Products[Category]
  • 値：Total Revenue、Total Profit、Profit Margin
• ピボットテーブル分析タブ >> 組み合わせ >> 集合縦棒 – 第2軸の折れ線 を選択します。

ステップ4：インタラクティブ要素を挿入する

スライサーとタイムラインの挿入：

製品スライサーと地域スライサーの作成：

• ピボットテーブル分析タブ >> スライサーの挿入 を選択します。
• Products[Category] と Customers[Region] にチェックを入れます。
• OKをクリックします。

• ダッシュボードのデザインに合わせて書式を整えます。

日付フィルター用タイムラインの追加：

• ピボットテーブル分析タブ >> スライサーの挿入 を選択します。
• Sales[OrderDate] にチェックを入れます。
• OKをクリックします。

• グラフの上部に配置します。
• ダッシュボードのスタイルに合わせて書式を調整しましょう。

すべてのスライサーをピボットテーブルに接続する：

• 各スライサーを右クリック >> レポートの接続 を選択します。

• すべてのピボットテーブルにチェックを入れ、フィルターが全体に反映されるようにします。
• OKをクリックします。

ステップ5：VBAで自動化と操作性を強化する

VBAを使えば、ダッシュボードをより動的で使いやすいものにできます。

例1：データ更新ボタン

• 開発タブ >> 挿入 >> ボタン を選択します。
• ボタン名を Refresh Data に変更します。
• ボタンを右クリック >> マクロの登録 >> 新規作成 を選択します。

• 以下のコードをコピー＆ペーストします。

VBAコード：

Sub RefreshDashboard()
 ThisWorkbook.RefreshAll
 MsgBox "Dashboard data refreshed!", vbInformation
End Sub

例2：ダッシュボードリセットボタン

• 開発タブ >> 挿入 >> ボタン を選択します。
• ボタン名を Reset Dashboard に変更します。
• ボタンを右クリック >> マクロの登録 >> 新規作成 を選択します。
• 以下のコードをコピー＆ペーストします。

VBAコード：

Sub ResetDashboardFilter()
 Dim ws As Worksheet
 Dim slicer As slicerCache
 Dim pivotTable As pivotTable
 
 ' すべてのスライサーキャッシュをクリア
 For Each slicer In ActiveWorkbook.SlicerCaches
 slicer.ClearAllFilters
 Next slicer
 
 ' タイムラインもリセット（SlicerCaches経由で処理）
 For Each slicer In ActiveWorkbook.SlicerCaches
 If slicer.SourceType = xlTimeline Then
 slicer.ClearAllFilters
 End If
 Next slicer
 
 ' ピボットテーブルを更新
 For Each ws In ActiveWorkbook.Worksheets
 For Each pivotTable In ws.PivotTables
 pivotTable.RefreshTable
 Next pivotTable
 Next ws
 
 MsgBox "Dashboard filters have been reset!", vbInformation, "Reset Filters"
End Sub

ステップ6：ダッシュボードのレイアウトを組み立てる

「Dashboard」という名前の新しいワークシートを作成します。

• ダッシュボード構造の設計：
  • ダッシュボードのタイトルと日付フィルター
  • KPIセクション（売上、利益、利益率、成長率）
  • グラフ（売上推移、上位製品、地域別パフォーマンス）

• データテーブルのサマリー表や詳細分析エリアを追加してもよいでしょう。

• 統一感のある書式を適用：
  • 全体を通して一貫したカラースキームを使用します。
  • すべての要素をきちんと整列させます。
  • 罫線を引いてダッシュボードのセクションを区切ります。

• データテーブルへの条件付き書式の適用：
  • データバーやカラースケールを使って重要な数値を強調します。
  • KPIアイコンを追加して、目標達成状況をひと目でわかるようにします。
• 説明シートの作成：
  • 「Instructions」という名前の新しいワークシートを作成します。
  • ダッシュボードの使い方を説明するテキストを記載します。
  • データ更新方法、インタラクティブ機能、利用できる機能などの情報を含めましょう。

ステップ7：テストとトラブルシューティング

すべてのインタラクティブ要素をテストする：

• スライサーが関連するすべてのビジュアルを正しくフィルタリングしているか確認します。
• ボタンがマクロを正常に実行できるか検証します。
• タイムラインコントロールでデータ範囲が正しく機能しているかチェックします。
  • Categoryスライサーから Beauty を選択します。
  • タイムラインで 2024年1月～4月 を選択します。
• これにより、選択したフィルター条件に基づいてダッシュボード全体が更新されます。

フィルターリセットのテスト：

• Reset Dashboard ボタンをクリックします。
• 「Dashboard filters have been reset」（ダッシュボードのフィルターがリセットされました）というメッセージが表示されます。
• OKをクリックします。
• すべてのフィルターが解除され、初期状態のダッシュボードに戻ります。

データ更新のテスト：

• ソースデータを変更してみます。
• Refresh Data ボタンをクリックします。
• すべての計算が正しく更新されているか確認します。
• 接続エラーや数式の不具合がないかチェックしましょう。

練習用ワークブックのダウンロード

まとめ

以上の手順に従えば、強力なビジネスインテリジェンスツールとして活用できる高機能なExcelダッシュボードを構築できます。このダッシュボードは、データ準備を担うPower Query、モデリングと分析を支えるPower Pivot、そしてインタラクティブ性を高めるVBAの3つの技術を最大限に活用しています。製品別、地域別、期間別の売上パフォーマンスを直感的に分析できるユーザーフレンドリーなインターフェースを提供します。ぜひいろいろ試しながら、さらに高度な機能を追加してみてください。

無料のExcel実践問題と解答例を受け取る！