Power Query、Power Pivot、VBA を使用して高度な Excel ダッシュボードを構築 – データからインサイトまで

最新の Excel ダッシュボードは、単純なグラフや表をはるかに超えて進化しています。データ変換のための Power Query、高度なデータ モデリングと分析のための Power Pivot、自動化と強化された対話性のための VBA を組み合わせることで、プロフェッショナルなダッシュボードを作成できます。

このチュートリアルでは、Power Query、Power Pivot、VBA を使用して高度な Excel ダッシュボードを構築する方法を示します。

複数の地域で製品を販売する架空の小売会社の販売ダッシュボードを作成してみましょう。次のテーブルを含むデータセットを使用します。

• 販売 – トランザクション データが含まれます。
• 製品 – 製品の詳細とカテゴリ
• 顧客 – 顧客情報
• 地域 – 地理情報

ステップ 1:データ インポートに Power Query を使用する

データのインポート:

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

• Power Query エディターが開いたら、データを確認し、次の変換を行います。
• データ型を変更します。
• 任意の列を右クリック>> [データ型の変更] を選択します。>> データタイプを選択します .
  • 注文日 日付まで .
  • 数量 整数へ .
  • 単価 と割引 10 進数へ .

• 重複の削除を使用して重複した行を削除します。 .
• Products.csv、Customers.csv、Dates.csv のインポート プロセスを繰り返し、適切なデータ型変換を適用します。

Power Query を使用してデータを変換する:

Power Query を使用してタスクを実行して、販売データを強化しましょう。

計算列の追加:

• Power Query エディターで、[売上データ] を選択します。
• 列の追加に移動します。 タブ>> カスタム列を選択します .
• 名前を付けます:収益 .
• 次の数式を挿入します。

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

• [OK] をクリックします。 .

• 別のカスタム列を追加します。
• 名前を付けます:利益 .
• 次の数式を挿入します。

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

• [OK] をクリックします。 .
• コストを取得するには、Products テーブルとマージする必要があります。

テーブルを結合して追加の洞察を得る:

• Power Query エディターで売上データを開いた状態。
• ホームに移動します タブ>> [クエリの結合] をクリックします。 リボンから。
  • プロダクト ID を選択します Sales テーブルから。
  • 製品 を選択します。 テーブルを作成し、ProductID で結合します。 .
  • [OK] をクリックします。 .

• テーブルを展開をクリックします。 オプション>> 単価 のみを選択します 取り込む列
• [OK] をクリックします。 .

• 次に、利益 をドラッグします。 拡張された製品の下の列 ステップ
• 利益額が得られます。

• データ変換が完了したら。
• [閉じてロード…] をクリック

• データのインポート ボックス;
  • 接続の作成のみを選択します。 .
  • [このデータをデータ モデルに追加する] を選択します。 4 つのテーブルすべてに適用されます。
  • [OK] をクリックします。 .

ステップ 2:Power Pivot を使用してデータ モデルを構築する

Power Pivot を開く:

Power Pivot がリボンで使用できない場合は、有効にしてください。

• ファイルに移動します タブ>> オプションを選択します>> アドインを選択します .
• 管理内 ボックス>> [COM アドイン] を選択します。>> [移動] を選択します。 .

• Microsoft Power Pivot for Excel を選択します。 .
• [OK] をクリックします。 .

• Power Pivot に移動します。 タブ>> 管理を選択します .
• Power Query からインポートされたデータが開きます。

人間関係を築く :

• ホームに移動します タブ>> ダイアグラムビューを選択します .
  • 売上[ProductID] をドラッグします。 製品[ProductID]へ .
  • 売上[顧客ID]をドラッグします 顧客[CustomerID] 宛 .
  • 顧客[地域ID]をドラッグします 地域[地域ID]へ .

• または、デザイン に移動します。 タブ>> 関係の作成を選択します .
• 次に、一致する列を選択します。

計算メジャーの作成:

• Power Pivot で、Sales テーブルをクリックします。
• ホーム に移動します タブ>> メジャー を選択します>> [新しいメジャー] をクリックします。 .
• または、ホームに移動します。 タブ>> 計算領域を選択します .
• 計算されたメジャーをそこに挿入します。

• 総収益:

Total Revenue := SUM(Sales[Revenue])

• 総利益:

Total Profit := SUM(Sales[Profit])

• 利益率:

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

• 注文合計:

Total Orders:=COUNTA(Sales[OrderID])

• 平均注文金額:

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

• YTD 収益:

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

• 前年度の収益:

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

• 前年比成長率:

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

ステップ 3:ピボット テーブルを使用してダッシュボード コンポーネントを作成する

ピボットテーブルを作成する

• 挿入 に移動します。 タブ>> ピボットテーブル を選択します>> [データ モデルから] を選択します。 .
• Power Pivot で、ホームに移動します。 タブ>> ピボット テーブルを選択します .

• [ピボットテーブルの作成] で ボックス;
  • 新しいワークシートを選択します。
  • [OK] をクリックします。 .

• 総収益、総利益、利益率、成長率について個別のピボットテーブルを作成する
• これらをダッシュボードの構造に合わせてセルに配置します。
• 必要に応じて通貨またはパーセントの形式でフォーマットします。

チャートとビジュアライゼーション

収益傾向グラフ:

• データ モデルからピボットテーブルを作成します。
  • 行:売上[注文日[月]]
  • 値: 総収益
• ピボットテーブル分析に移動します。 タブ>> 集合縦棒グラフを選択します。 .

トップ製品チャート:

• データ モデルからピボットテーブルを作成します。
  • 行:製品[製品名]
  • 値:総収益
• 総収益で並べ替えます 降順、上位 10 位までフィルタリングします。

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

カテゴリのパフォーマンス :

• データ モデルからピボットテーブルを作成します。
  • 行:製品[カテゴリ]
  • 値:総収益、総利益、利益率 .
• ピボットテーブル分析に移動します。 タブ>> コンボを選択します>> 集合列 – 第 2 軸上の線 を選択します。 .

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

スライサーとタイムラインを挿入:

製品と地域のスライサーを作成する:

• ピボット分析に移動します。 タブ>> スライサーの挿入を選択します。 .
• 製品[カテゴリ]を選択します。 および顧客[地域] .
• [OK] をクリックします。 .

• ダッシュボードのデザインに合わせてフォーマットします。

日付フィルタリング用のタイムラインを追加します:

• ピボット分析に移動します。 タブ>> スライサーの挿入を選択します。 .
• 販売[注文日]を選択します .
• [OK] をクリックします。 .

• チャートの上に配置します。
• ダッシュボードのスタイルに合わせてフォーマットします。

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

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

• すべてのピボットテーブルをチェックします フィルタがグローバルに適用されるようにするため。
• [OK] をクリックします。 .

ステップ 5:VBA を使用して対話性を自動化および強化する

VBA を使用して、ダッシュボードを動的にし、ナビゲートしやすくします。

例 1:データの更新ボタン

• 開発者 に移動します。 タブ>> 挿入 を選択します>> ボタンを選択します .
• ボタンの名前を「データの更新」に変更します。 .
• ボタンを右クリックし、マクロの割り当て>> を選択します。 新規を選択します .

• 次のコードをコピーして貼り付けます。

VBA コード:

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

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

• 開発者 に移動します。 タブ>> 挿入 を選択します>> ボタンを選択します .
• ボタンの名前を「ダッシュボードをリセット」に変更します。 .
• ボタンを右クリックし、マクロの割り当て>> を選択します。 新規を選択します .
• 次のコードをコピーして貼り付けます。

VBA コード:

Sub ResetDashboardFilter()
 Dim ws As Worksheet
 Dim slicer As slicerCache
 Dim pivotTable As pivotTable
 
 ' Clear all slicer caches
 For Each slicer In ActiveWorkbook.SlicerCaches
 slicer.ClearAllFilters
 Next slicer
 
 ' Reset any timelines (already using SlicerCaches)
 For Each slicer In ActiveWorkbook.SlicerCaches
 If slicer.SourceType = xlTimeline Then
 slicer.ClearAllFilters
 End If
 Next slicer
 
 ' Refresh pivot tables
 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 アイコンを追加して、目標に対するパフォーマンスを表示します。
• 説明書を作成する:
  • 「手順」という名前の新しいワークシートを作成します。
  • ダッシュボードの使用方法を説明するテキストを追加します。
  • データの更新、インタラクティブ性、利用可能な機能に関する情報を含めます。

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

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

• スライサーが関連するすべてのビジュアライゼーションを確実にフィルタリングする
• ボタンがマクロを正しく実行していることを確認します。
• タイムライン コントロールをチェックして、データ範囲が適切であることを確認します。
  • 美しさを選択します カテゴリから スライサー。
  • 2024 年 1 月～4 月を選択します タイムラインから。
• これにより、フィルタの選択に基づいてダッシュボード全体が更新されます。

テスト リセット データ フィルター:

• [ダッシュボードをリセット] をクリックします。 .
• メッセージがポップアップ表示されます。「ダッシュボード フィルターがリセットされました」 .
• [OK] をクリックします。 .
• すべてのフィルタが削除され、新しいダッシュボードが表示されます。

テスト データの更新 :

• ソース データを変更します。
• [データを更新] をクリックします。 .
• すべての計算が正しく更新されていることを確認します。
• 壊れた接続や数式がないか確認します。

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

結論

すべての手順を実行すると、強力なビジネス インテリジェンス ツールとして機能する高度な Excel ダッシュボードを構築できます。このダッシュボードは、データの準備には Power Query、モデリングと分析には Power Pivot、対話性の強化には VBA の力を活用しています。このダッシュボードは、製品、地域、期間にわたる販売実績を分析するための使いやすいインターフェイスを提供します。ダッシュボードを試して、より高度な機能を追加してください。

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