はじめに

今回はExcel Power Queryを使った相対的な期間のデータ集計と集計結果の自動更新についてご紹介します。一般的なデータ収集・整形・出力の流れの解説に加えて、Power Query M関数、データの読み込み先についても触れていきたいと思いますので、Excel Power Queryがどんなものなのか、ある程度把握されていることを前提に読み進めていただけると幸いです。

Excel Power Queryとは

Excel Power QueryはExcelに搭載されている機能の一つで、Excel 2016以降のバージョンで標準機能として使用することができます。データ収集・整形ルールを設定することで一連の定型作業を自動化することができます。日ごとに作成されるエクセルファイルのデータを集計・分析できるよう同じ形へ加工したり、一つのエクセルファイルにまとめるといった作業もPower Queryで一度ルールを作成してしまえばそれ以降はルールに従ってワンクリック（もしくは自動）で集計結果の更新を行うことができます。
Power Queryについての詳細はこちらでご確認ください。

Power Query とは - Power Query | Microsoft Learn

Excel Power Queryの使い方

Excel Power Queryの作業ステップは主に1.データの接続2.データの整形3.データの出力4.クエリの更新に分かれています。今回は例として次のような日別売上データの集計を行います。

• 売上データフォルダーに日ごとの売上データファイルが格納される
• 売上データファイル内の商品情報は商品マスタファイルに記載

直近1週間分の売上データの集計、ファイルへの出力の自動化をExcel Power Queryで実現しようと思います。
早速各ステップを見ていきましょう。

データの接続

Excelファイルを新規作成します。データの取得はファイル上部のリボン「データ」タブ（①）の「データの取得」（②）から可能です。「ファイルから」（③）→「フォルダーから」（④）を順に選択します。

集計したいファイルが配置されているフォルダーを選択します。

指定したフォルダー内のファイルがそろっていることを確認し、「データの結合と変換」を選択します。

使用するテーブルを選択し、OKをクリックするとPower Queryエディターが起動します。

データの整形

次にPower Queryエディターでデータの整形を行います。

データの抽出

現在売上データフォルダー内のすべてのデータが結合されているため、この内直近1週間の売上データのみを抽出します。今回の例ではファイル名の先頭がデータ日付になっているので

• 「Source.Name」列から日付部分を抽出
• 列名を「日付」に変更
• データ型を「日付」に変更

の処理を行い「Source.Name」列から「日付」列を作成します。

次に日付のフィルタリングを行います。

画面右のクエリの設定→適応したステップの最新のステップを右クリック→後にステップの挿入から空のステップを作成できます。

作成したステップを選択し、画面上部の数式バーに以下の式を設定します。

= Table.SelectRows(変更された型1, each [日付] > =Date.AddDays(DateTime.Date(DateTime.LocalNow()),-6) and [日付] <= DateTime.Date(DateTime.LocalNow()))

Power Query内で使用する関数は「Power Query M関数」と呼ばれ、Excel関数と似た関数を使用します。「Power Query M関数」の詳細はこちらをご確認ください。

Power Query M 関数参照 - PowerQuery M | Microsoft Learn

数式バーで使用している関数について説明します。

• DateTime.LocalNow
  現在の日時を日時型で取得します。
• DateTime.Date
  日時型の値を日付型に変換します。
• Date.AddDays
  一つ目の引数に計算元の日付、二つ目の引数に追加したい日数を指定して日付に指定の日数を加算または減算します。

今回の場合、今日の日付-6日以降は

[日付] >= AddDays(DateTime.Date(DateTime.LocalNow()),-6)

今日の日付以前は

[日付] <= DateTime.Date(DateTime.LocalNow())

となります。

日付列を確認すると、直近1週間分の売上データを抽出できていることがわかります。

データのマージ

各商品の価格は商品マスタファイルに記載してあるので、売上データテーブルと商品マスタテーブルをマージします。一度現在開いているPower Query エディターを閉じます。「ホーム」タブ左上の「閉じて読み込む」で作成したクエリを新規のシートに読み込むことができます。

続いて商品マスタの取り込みを行います。ファイル上部のリボン「データ」タブ（①）の「データの取得」（②）、「ファイルから」（③）→「Excelブックから」（④）を順に選択します。

商品マスタを選択し、インポートをクリックします。ナビゲーターが出てくるので該当のテーブルを選択し、「データの変換」を選択してPower Queryエディターを再度開きます。

その他のクエリに商品マスタが追加されました。
売上データと商品マスタのマージを行います。その他のクエリで売上データを選択した状態で、「クエリのマージ」を選択（①）、マージするテーブルと照合列（②）、結合の種類（③）の順で選択します。今回は売上データテーブルと商品マスタテーブルの商品番号を照合列として左外部結合を行います。

次にマージされた商品マスタの展開を行います。
商品マスタ列の展開で展開する列を選択します。今回は「価格」列のみにチェックを入れて展開します。

売上データと商品マスタのマージが完了しました。

データの集計

日別の売上データのグルーピングを行います。今回は日別の売上合計額とキャンセル数を計算します。「受注数量」列の内、備考がキャンセルになっている注文は売上としてカウントしたくないので、条件列から「売上数量」列を作成し、「備考」列がキャンセルの場合は0、そうでない場合は「受注数量」列の値とします。

次に注文ごとの「売上」列を作成し、「価格」列*「売上数量」列の値とします。列の作成方法は「売上数量」列と同様です。
次に日別のキャンセル数を集計するための「キャンセル」列を作成し、「備考」列がキャンセルの場合1、それ以外の場合0とします。こちらも列の作成方法は「売上数量」列と同様です。
列作成後のプレビューがこちらです。

ここまでで集計を行うのに必要なデータがそろいました。データ型は適宜適切な型に変更してください。
最後に集計を行います。「日付」列でグループ化を行い、「日別売上」列を「売上」列の合計、「日別キャンセル数」列を「キャンセル」列の合計の値とします。

これでデータの整形は完了です。

データの出力

「閉じて読み込む」をクリックすることで、先程読み込んだプレビューが更新されます。また、Power Queryではクエリ作成後に読み込み先を変更することができます。
ファイル上部「データ」タブの「クエリと接続」を選択します。

読み込み先を変更したいクエリで右クリックし、「読み込み先…」を選択し読み込み先の変更を行います。

データの読み込み先には4つの選択肢があります。

1. テーブル
   データをテーブルとして出力します。「既存のワークシート」を選択するとデータを出力するセルを選択できます。「新規ワークシート」を選択すると新規のワークシートにデータが読み込まれます。
2. ピボットテーブルレポート
   データをシートに出力することなくピボットテーブルを作成することができます。
3. ピボットブラフ
   ②と同様データをシートに出力することなくピボットテーブル、ピボットグラフの作成を行うことができます。
4. 接続のみ
   データをシートに読み込みたくない場合に使用します。

今回の例で作成した「売上データ」、「商品マスタ」データのうち「売上データ」のみをシートに読み込みたいので、商品マスタの読み込み先を「接続のみ」に変更します。
シートからデータの表示が消え、クエリと接続の表示も「接続専用」に変更されました。

クエリの更新

今回の例では日ごとに売上データフォルダーに新しいファイルが格納されます。ファイルを開いたタイミングで直近1週間の売上集計にクエリが更新されるように設定を行います。
ファイル上部「データ」タブ（①）、「すべて更新」（②）の「接続のプロパティ」（③）を選択します。

クエリプロパティで「ファイルを開くときにデータを更新する」にチェックを入れることでファイルを開くタイミンクでクエリの更新が自動的に行われます。

実際に更新が自動で行われるか試してみます。
2023/4/18時点の出力がこちらです。4/12~4/18の1週間の集計データが出力されています。

1日後に当日分の売上データファイルを格納し、再度ファイルを開いてみます。

クエリが更新され、4/13~4/19の集計データに書き換わりました。

さいごに

いかがでしたでしょうか。今回はExcel Power Queryを使用した相対的な期間のデータ集計とデータの自動更新について紹介しました。クエリの作成はプログラミングの知識がほとんど不要で慣れれば数十分～数時間でできますし、一度作成してしまえばあとはデータを追加するだけで集計結果の更新が行えますので日々の繰り返し業務を自動化するのにおすすめです。
弊社では、RPAツールやその他幅広いソリューションでお客様の業務効率化に向けてサービスを提供しております。RPA全般に関する質問やご相談があればこちらからお問い合わせください。