Excel Power Queryでリアルタイム動的ダッシュボードを作成する方法

ExcelのPower Queryは、データ接続・変換・リアルタイム更新を実現できる最も強力なツールの一つです。さまざまなデータソースからデータを取り込み、元データに変更が生じた際にリアルタイムで反映できます。本記事では、Power Queryを活用してリアルタイムデータダッシュボードを構築する具体的な手順を解説します。

ここでは、拡張された売上データセットに対してPower Queryで追加操作を行い、リアルタイムの売上ダッシュボードを作成します。売上データセットのヘッダーには「注文日」「地域」「製品」「販売担当者」「販売数量」「売上高($)」「利益($)」が含まれています。

ステップ1:Power Queryでデータに接続する

外部ソースからデータを取得するには、以下の手順を実行します。

• データタブ >> データの取得 >> ファイルから >> ブックからを選択します。
• データセットファイル(例:SalesData.xlsx)を選択し、インポートをクリックします。
• ナビゲーターで売上データが含まれるシートを選択し、読み込みをクリックします。

既存のブックからデータに接続する場合は、以下のように操作します。

• データタブ >> テーブル/範囲からを選択します。

これにより、Power QueryがデータをPower Queryエディターに読み込みます。

ステップ2:高度な操作でデータを変換する

データがPower Queryエディターに読み込まれたら、データ変換と計算を行いましょう。

1. 列のデータ型を変更する

Power Queryは自動的に既定の形式へ変換することがあります。データの種類に応じて、列全体のデータ型を適切に設定してください。

• 注文日:日付形式になっていることを確認します。
  • 日付列を選択します。
  • 変換タブ >> データ型から日付のみを選択します。
• 販売数量:整数形式になっていることを確認します。
  • 販売数量列を選択します。
  • 変換タブ >> データ型から整数のみを選択します。
• 売上高($)と利益($):通貨形式になっていることを確認します。
  • 売上高($)と利益($)の列を選択します。
  • 変換タブ >> データ型から通貨のみを選択します。

2. 「月」列を追加する

• 注文日列を選択します。
• 列の追加タブ >> 日付 >> 月 >> 月の名前を選択します。
• これにより、注文日から月名(1月、2月など)を抽出する新しい列が作成されます。

3. 単位あたり売上高を計算する

販売された1単位あたりの売上高を計算してみましょう。

• 列の追加タブ >> カスタム列を選択します。
• カスタム列ダイアログボックスで以下を設定します。
  • 列名を単位あたり売上高とし、数式ボックスに次の式を入力します。

[#"Revenue ($)"]/[Units Sold]

• OKをクリックします。

この新しいカスタム列には、販売された各単位が生み出した売上が表示されます。

4. 利益率を計算する

次に、利益率を計算します。利益率は「利益 ÷ 売上高 × 100」で求められます。

• 列の追加タブ >> カスタム列を選択します。
• カスタム列ダイアログボックスで以下を設定します。
  • 列名を利益率(%)とし、数式ボックスに次の式を入力します。

([#"Profit ($)"]/[#"Revenue ($)"])*100)

• OKをクリックします。

このカスタム列には、各販売の利益率が表示されます。

5. 地域と製品別にグループ化して集計する

地域別・製品別の合計売上高、販売数量、利益を把握するために、データをグループ化します。

• ホームタブ >> グループ化を選択します。
• グループ化ダイアログボックスで以下を設定します。
  • 詳細を選択して複数の集計を追加できるようにします。
  • グループ化:Region(地域)、Product(製品)。
  • 有効な新しい列名を挿入します。
  • 操作:合計
  • 列:
    • Units Sold(販売数量)
    • Revenue ($)(売上高)
    • Profit ($)(利益)
  • OKをクリックします。

グループ化機能により、地域別・製品別にデータが要約され、売上パフォーマンスの全体像を一目で把握できるようになります。

6. データを並べ替える

売上高($)の降順で並べ替えることで、パフォーマンスの高い製品や地域を簡単に確認できます。

• 売上高($)列ヘッダーのドロップダウン矢印をクリックします。
• 降順の並べ替えを選択 >> OKをクリックします。

これにより、売上金額の高い順から低い順にデータが並べ替えられます。

ステップ3:変換後のデータをExcelに読み込む

クリーンアップして変換したデータを、Excelワークシートに読み込みます。

• ホームタブ >> 閉じて読み込む >> 閉じて読み込むを選択します。

ステップ4:ダッシュボードを作成する

データの変換とExcelへの読み込みが完了したら、視覚化要素を組み合わせてダッシュボードを作成できます。

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

• 変換済みのデータテーブルを選択します。
• 挿入タブ >> ピボットテーブルを選択します。

• ピボットテーブルのフィールドリストで以下を設定します。
  • 行:Region(地域)、Product(製品)。
  • 値:Units Sold(販売数量)の合計、Revenue(売上高)の合計、Profit(利益)の合計。

2. ピボットグラフを作成する

地域別売上高の棒グラフ:

• 地域と売上高を含むピボットテーブルを選択します。
• ピボットテーブル分析タブ >> 棒グラフ >> 集合横棒を選択します。

製品別売上分布の円グラフ:

• 製品と売上高を含むピボットテーブルを選択します。
• ピボットテーブル分析タブ >> 円グラフを選択します。

ここで、ダッシュボードのインタラクティブ性を確認してみましょう。円グラフから地域「East」を選択します。

さらに、販売担当者用のスライサーや注文日用のタイムラインを追加すると、ダッシュボードの操作性が一段と向上します。

ステップ5:リアルタイム更新を設定する

ダッシュボードのデータをリアルタイムに更新するには、以下の方法があります。

• ソースデータに新しいデータを追加した際は、データタブ >> すべて更新をクリックします。

または、ブックを数分ごとに自動更新するよう設定することも可能です。

• データタブ >> クエリと接続を選択 >> クエリと接続ウィンドウ内で右クリック >> プロパティを選択します。

• クエリのプロパティダイアログボックスで以下を設定します。
  • 5分ごとに更新を有効にします。
  • OKをクリックします。

まとめ

以上の手順に従うことで、Power Queryを使用した動的なリアルタイム売上ダッシュボードを構築できます。Power Queryエディターでは、グループ化、カスタム列による計算、並べ替え、フィルターなどの高度な操作が可能で、変換したデータをExcelに読み込むだけで実用的なダッシュボードが完成します。Power Queryのデータ接続と変換機能を活用すれば、最新データをリアルタイムで素早くダッシュボードに反映でき、常に鮮度の高い情報に基づいた意思決定が行えるようになります。