Excel Power Query を使用して動的なリアルタイム ダッシュボードを作成する

Excel の Power Query は、データ接続、変換、リアルタイム更新のための最も強力なツールの 1 つです。 Power Query は、さまざまなソースからデータをインポートし、ソース データに変更があるたびにリアルタイムでデータを更新できます。この記事では、Power Query を使用してリアルタイム データ ダッシュボードを構築する方法を説明します。

拡張されたデータセットに対して Power Query で追加の操作を実行して、リアルタイム販売ダッシュボードを作成してみましょう。販売データセットのヘッダーには、注文日、地域、製品、販売員、販売数、収益 ($)、利益 ($) が含まれます。

ステップ 1:Power Query を使用してデータを接続する

外部ソースからデータを取得するには:

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

既存のワークブックからデータを接続するには:

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

Power Query はデータをPower Query エディタに読み込みます。 .

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

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

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

Power Query では、データ形式がデフォルトの形式に変更されることがあります。データ型に基づいて、列全体を変更します。

• 注文日 :日付になっていることを確認してください。 形式。
  • そうでない場合は、「日付」列を選択します。
  • 変換に移動します。 タブ>> DataType から>>日付のみを選択してください .
• 販売単位: それが整数であるかどうかを確認します。 形式。
  • そうでない場合は、[販売単位] 列を選択します。
  • 変換に移動します。 タブ>> DataType から>> 整数のみを選択してください .
• 収益 ($) と利益 ($) :これらの列が通貨であることを確認してください。 形式。
  • そうでない場合は、「収益 ($)」列と「利益 ($)」列を選択します。
  • 変換に移動します。 タブ>> DataType から>>通貨のみを選択してください .

2. 「月」列を追加します

• 「注文日」列を選択します。
• 列の追加に移動します。 タブ>> 日付をクリック>>月を選択します>> 月の名前を選択します .
• これにより、注文日から月の名前を抽出する列が作成されます。 (例:1 月、2 月)。

3.ユニットあたりの収益を計算する

販売されたユニットごとに生じる収益を計算してみましょう。

• 列の追加に移動します。 タブ>> カスタム列を選択します .
• カスタム列内 ダイアログボックス;
  • 列に「ユニットあたりの収益」という名前を付けます。 式ボックスで次の式を使用します。

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

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

この新しいカスタム列には、販売されたユニットごとにどれだけの収益が発生したかが表示されます。

4.利益率の計算

次に、利益率を計算してみましょう。利益率は次のように計算されます。

• 列の追加に移動します。 タブ>> カスタム列を選択します .
• カスタム列内 ダイアログボックス;
  • 列に 利益率 (%) という名前を付けます。 式ボックスで次の式を使用します。

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

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

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

5.地域および製品ごとにデータをグループ化し、分析情報を集約

収益の合計を確認するには 、販売数 、 そして利益も得られます。 地域別 と製品 、データをグループ化します。

• ホームに移動します タブ>> グループ化を選択します .
• グループ化内 ダイアログ ボックスで、次のように設定します:
  • [詳細設定] を選択します。 複数の集計を追加します。
  • グループ化 :地域、製品
  • 有効な新しい列名を挿入します。 .
  • 操作 :合計
  • 列:
    • 販売数
    • 収益 ($)
    • 利益 ($)
  • [OK] をクリックします。 .

グループ化 この機能は、地域および製品ごとにデータを要約し、販売実績の概要を提供します。

6.データを並べ替える

データを収益 ($) の降順に並べ替えて、最もパフォーマンスの高い製品と地域を表示します。

• 収益 ($) のドロップダウンをクリックします。 列ヘッダー。
• 降順で並べ替えを選択します。>> [OK] をクリックします。 .

これにより、データが並べ替えられ、収益に基づいてデータが最高から最低まで表示されます。

ステップ 3:変換されたデータを Excel にロードする

クリーンアップおよび変換されたデータを Excel ワークシートにロードします。

• ホームに移動します タブ>> 閉じてロード から>> [閉じてロード] を選択します。 .

ステップ 4:ダッシュボードの作成

データが変換されて Excel に読み込まれると、視覚化されたダッシュボードを作成できます。

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

• 変換されたデータ テーブルを選択します。
• 挿入に移動します タブ>> ピボットテーブルを選択します .

• ピボットテーブル フィールド内 リスト:
  • 行 :地域、製品
  • 価値観 :販売個数の合計、収益の合計、利益の合計

2.ピボット チャートを作成する

地域別の収益の棒グラフ :

• 地域と収益を含むピボット テーブルを強調表示します。
• ピボットテーブル分析 に移動します。 タブ>> 棒グラフを選択します>> 集合棒を選択します .

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

• 製品と収益を含むピボット テーブルを選択します。
• ピボットテーブル分析 に移動します。 タブ>> 円グラフを選択します .

ダッシュボードの対話性を見てみましょう。円グラフから東の地域を選択します。

インタラクティブ性のために、営業担当者用のスライサーと注文日用のタイムラインを追加できます。

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

ダッシュボードのリアルタイム データを更新するには:

• データに移動します。 タブ>> [すべて更新] をクリックします。 ソース データに新しいデータを追加するたびに。

または、数分ごとに自動的に更新されるようにワークブックを設定することもできます。

• データに移動します。 タブ>> クエリと接続を選択します>> [クエリと接続] を右クリックします。>> プロパティを選択します .

• クエリのプロパティ内 ダイアログボックス;
  • 5 分ごとに更新を有効にします。 .
  • [OK] をクリックします。 .

結論

これらの手順に従うと、Power Query を使用して動的なリアルタイム販売データ ダッシュボードを構築できます。 Power Query エディターで、グループ化、カスタム列の計算、並べ替え、フィルターなどの高度な操作を実行し、変換されたデータを Excel に読み込んでリアルタイム ダッシュボードを構築できます。 Power Query のデータ接続と変換機能により、ダッシュボードがリアルタイムで新しいデータで迅速に更新されます。

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