「クエリの参照」は、Excelファイルなどからデータを取得するのではなく、既存のクエリの結果(データ表)を読み込んで「新しいクエリ」を作成できる機能だ。この機能を活用することにより、同じ処理手順の指定を簡略化することもできる。今回は「クエリの参照」の具体的な活用方法を紹介しよう。

  • 途中で分岐する処理、クエリの参照

クエリの参照とは?

前回は「クエリの複製」を使って、同じ処理手順の指定を簡略化する方法を紹介した。これと同様の作業を「クエリの参照」で実現することも可能だ。今回は「クエリの参照」の具体的な活用方法を見ていく。

まずは、「クエリの複製」と「クエリの参照」の違いについて説明する。「クエリの複製」は、「既存のクエリ」を複製して「新しいクエリ」を作成する機能となる。このため、それぞれのクエリで、(1)データ表の取得、(2)データ表の整形、(3)テータ表の加工、(4)データ表の出力、といった処理が行われることになる。

一方、「クエリの参照」は、既存のクエリの結果(データ表)を読み込んで「新しいクエリ」を作成する機能となる。たとえば、(1)データ表の取得、(2)データ表の整形、といった処理を行うクエリを作成したとする。続いて、このクエリの結果をもとに、さまざまな分析を行うクエリを追加していく。このようにクエリを連携させることで、途中で分岐する処理を実現することが可能となる。

  • 「クエリの複製」と「クエリの参照」のイメージ図

上図は、フォルダー内に保存されているExcelファイルをもとに「日付別の売上」と「分類別の売上」を集計する作業をイメージ化したものだ。

前回紹介した「クエリの複製」を活用する場合は、それぞれのクエリが独立した存在として扱われる。一方、これから紹介する「クエリの参照」を活用した場合は、ベースとなるクエリで「データの取得・整形」を行い、その結果に「さまざまな分析を行うクエリ」を追加していく、というイメージになる。

ベースとなるクエリの作成

それでは、先ほど示したイメージ図を例に具体的な操作手順を紹介していこう。今回も、あるハンバーガー店の売上データを使って操作手順を解説していく。現時点では4月1日〜4月5日の5日分のデータが、それぞれ個別のExcelファイルに記録されている。

  • 「各日の売上データ」を記録したExcelファイル

念のため、各Excelファイルの内容も示しておこう。以下の図は「4月1日の売上」のExcelファイルを開いた例だ。他の日付のExcelファイルにも同じ形式でデータが記録されている。

  • 4月1日の売上データ

これらのExcelファイルを結合したいときは、「データの取得」→「ファイルから」→「フォルダーから」を選択してデータを取得すればよい。すると、以下の図のように各Excelファイルを結合したデータ表を取得できる。

  • 取得したデータを結合した表

現時点では「各日の売上の合計」もデータとして取得されているため、フィルター機能を使って「合計の行」を削除しておこう。これで以下の図に示したようなデータ表に整形できる。

  • 「合計の行」を除外したデータ表

さらに、データの取得元となったExcelファイル名(Source.Name)から「X月X日」の部分だけを抽出して「日付」のデータを作成する。すると、以下の図のようになる。

  • ファイル名を「日付」に変換したデータ表

これで「データの取得」と「データ表の整形」まで作業が済んだ。続いて、データを分析する処理を指定していくが、その前に「現時点の処理結果」をクエリとして保持しておこう。クエリ名を適当な名前に変更し、「閉じて読み込む」→「閉じて次に読み込む」を選択する。

  • クエリ名の指定、データ表の出力

出力方法を指定する画面が表示される。このクエリはデータを分析するための下準備となるので、データ表をExcelに出力する必要はない。よって、「接続の作成のみ」を選択し、接続専用のクエリとして保持する。

  • 出力方法に「接続の作成のみ」を指定

以上で、ベースとなるクエリの作成は完了。以降の分析作業は、このクエリの結果をもとに、さまざまな処理を追加していくことになる。

「日付別の売上金額」を集計するクエリの作成

続いては、「日付別の売上金額」を集計するクエリの作成手順を紹介していこう。先ほど接続専用として保持した「データ取得・整形」のクエリをダブルクリックする。

  • 「Power Query エディター」の起動

「Power Query エディター」が起動するので、「データ取得・整形」のクエリが選択されていることを確認してから「管理」→「参照」を選択する。

  • クエリを参照する操作

「データ取得・整形(2)」という名前で「新しいクエリ」が作成される。このクエリに登録されている処理手順(ステップ)は「ソース」のみ。そのM言語は「= #"データの取得・整形"」と記されている。

※編注：M言語については本連載第7回を参照のこと。

  • 新しく作成されたクエリ

このM言語は、「データ取得・整形」のクエリで処理した結果(データ表)を読み込む、ということを意味している。つまり、「データ取得・整形」を参照して、その続きの処理を行うクエリとなる。

続けて、このクエリに「日付別の売上金額」を集計する処理を追加する。「グループ化」をクリックし、「日付」で区分した「売上」の合計を集計するように指定する。

  • 「グループ化」の設定

これで「日付別の売上金額」にデータ表を加工することができた。

  • 日付別に集計した売上金額

あとは、クエリ名を適当な名前に変更し、このデータ表をExcelに出力するだけ。出力先を指定できるように「閉じて次に読み込む」を選択する。

  • クエリ名の指定、データ表の出力

ここでは「既存のワークシート」のB11セルを先頭にデータ表を出力するように指定した。よって、以下の図のようにデータ表が出力されることになる。

  • 出力されたデータ表

「分類別の売上金額」を集計するクエリの作成

続いては、「分類別の売上金額」を集計するクエリの作成手順を紹介していこう。こちらも「データ取得・整形」のクエリで処理した結果をベースに、新たな処理手順を追加していけばよい。念のため、その手順を詳しく紹介しておこう。

「データ取得・整形」のクエリをダブルクリックして「Power Query エディター」を起動する。続いて、「データ取得・整形」のクエリが選択されていることを確認してから「管理」→「参照」を選択する。

  • クエリを参照する操作

「データ取得・整形(2)」という名前で「新しいクエリ」が作成される。先ほどと同様に、「データ取得・整形」のクエリで処理した結果(データ表)が読み込まれていることを確認できるだろう。

  • 新しく作成されたクエリ

ここに「分類別の売上金額」を集計する処理を追加する。「グループ化」をクリックし、今度は「分類」で区分した「売上」の合計を集計するように指定する。

  • 「グループ化」の設定

これで「分類別の売上金額」にデータ表を加工することができた。

  • 分類別に集計した売上金額

あとは、クエリ名を適当な名前に変更し、このデータ表をExcelに出力するだけ。今回も出力先を指定できるように「閉じて次に読み込む」を選択する。

  • クエリ名の指定、データ表の出力

出力先に「既存のワークシート」のB3セルを指定すると、以下の図のようにデータ表が出力される。なお、数値を読み取りやすくするために、売上金額の列には「通貨」の表示形式を指定してある。

  • 出力されたデータ表

以上で、一連の作業は完了となる。このように「ベースとなるクエリ」を作成し、その処理結果を参照するようにクエリを追加していく方法もある。処理内容を途中で分岐させたい場合などに活用できるので、その仕組みをよく理解しておくとよいだろう。

クエリの依存関係の確認

参考までに、今回紹介した例の「クエリの依存関係」を示しておこう。「表示」タブにある「クエリの依存関係」をクリックすると、「どのようにクエリが連携しているか?」を示すイメージ図を表示できる。

  • 「クエリの依存関係」を表示した様子

今回の例は「フォルダー」からデータを取得するヘルパークエリも含まれているため、少し複雑になっているが、この図を見ることで各クエリの依存関係を把握しやすくなると思われる。上図の赤線で囲んだ部分が、自分で作成したクエリだ。「データの取得・整形」を起点に、「日付別の売上」と「分類別の売上」にデータが引き継がれていることを確認できる。

さらに、これらの処理結果を参照するクエリを追加して、「親クエリ」→「子クエリ」→「孫クエリ」といった構成にすることも可能だ。それぞれの作業をクエリに分割し、それらを連携させることで、さまざまな分析も行える。こういった複雑な連携を確認するときのイメージ図として、「クエリの依存関係」という機能があることも覚えておくと役に立つだろう。