「クエリの複製」を利用した処理手順の指定の簡略化

パワークエリを使って自動処理を行うときに、似たような処理手順を何回も指定しなければならない状況に陥ることもある。こういった場合に活用できるのが「クエリの複製」だ。この機能を使うと、「既存のクエリ」を「新しいクエリ」として複製することが可能となる。今回は「クエリの複製」の活用方法を紹介していこう。

• 

  処理手順の指定を簡略化、クエリの複製

クエリの複製とは?

「クエリの複製」は、クエリに指定した処理内容(ステップ)をそのままコピーして「新しいクエリ」を作成してくれる機能となる。似たような処理を何回も指定しなければならない場合に「クエリの複製」を活用すると、処理内容を指定する手間を簡略化できるようになる。便利な機能なので、この機会に使い方を覚えておくとよいだろう。

毎日の売上を自動集計するクエリ

今回は、あるハンバーガー店の売上データを集計する場合を例に、具体的な活用例を紹介していこう。このハンバーガー店では、売上データを1日ずつ個別のExcelファイルに記録している。現時点では3日分のデータ(4月1日～4月3日)が同じフォルダー内に保存されている。

• 

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

それぞれのExcelファイルには、以下の図のような形式で「各日の売上データ」が記録されている。以下の図は「4月1日の売上」のExcelファイルを開いた例だ。もちろん、他の日付のExcelファイルにも同じ形式でデータが記録されている。

• 

  4月1日の売上データ

このように同じ形式のデータ表が「複数のファイル」に分割して保存されている場合は、フォルダーを対象にデータを取得するとよい。すると、それぞれのExcelファイルを結合した形でデータ表を取得できる。

• 

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

連載第1回、第2回、第3回で紹介した内容と同じなので、おぼろげに覚えている方もいるだろう(といっても、もう1年以上前の話になるが……)。

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

• 

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

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

• 

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

ここまでの手順は、以前に紹介した内容と同じである。よく分からない方は先に第1回から第3回に目を通してから、以下を読み進めて頂きたい。

続いて、「グループ化」コマンドを使って「日付別の売上の合計」を集計すると、以下の図のような結果が得られる。

• 

  「グループ化」の設定

• 

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

なお、「グループ化」の使い方については第32回と第33回で詳しく解説しているので、よく分からない方はあわせて一読いただきたい。

最後に、このクエリの名前を「日付別の売上」に変更し、データ表をExcelに出力する。今回は、出力場所を指定できるように「閉じて次に読み込む」を選択した。

• 

  クエリ名の変更、データ表の出力

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

• 

  データ表の出力先の指定

データ表の出力結果は以下の図の通り。これで「各日の売上」を一目で確認できるようになる。

• 

  出力されたデータ表

もちろん、パワークエリならではの利点といえる自動処理も行える。たとえば、データの取得元となるフォルダーに「4月4日」と「4月5日」のExcelファイルを追加し、その後「すべて更新」を実行すると、計5日分の「各日の売上金額」が自動集計される。

• 

  フォルダーに「4月4日」と「4月5日」の売上データを追加

• 

  クエリを更新した結果

かなり駆け足で説明してきたが、パワークエリの使い方を知っている方なら、ここまでの内容は理解できるだろう。

今回はここからが本題だ。

たとえば、先ほど求めた「日付別の売上金額」のほかに、バーガー類/サイドメニュー/ソフトドリンクなどで区分した「分類別の売上金額」も集計したい、となった場合、どうすればよいだろうか?

普通に考えると、以下の手順で「新しいクエリ」を作成することになる。

1. フォルダー内のExcelファイルを結合してデータを取得する
2. 「合計の行」を削除する
3. データ取得元のExcelファイル名を「日付」のデータに変換する
4. 「グループ化」コマンドを使って「分類別の売上の合計」を求める

このうち(1)～(3)は先ほどのクエリとまったく同じ処理手順になる。異なるのは(4)の処理だけだ。にもかかわらず「新しいクエリ」をゼロから作成しなおす、というのは少し効率の悪い話といえないだろうか?

このような場合は、先ほど作成したクエリを複製し、処理内容の一部を変更したほうが効率よく作業を進められる。

「クエリの複製」を使った分類別の売上の自動集計

それでは、具体的な操作手順を紹介していこう。といっても、それほど難しい話ではない。先ほど作成した「日付別の売上」のクエリを開き、「管理」コマンドから「複製」を選択する。

• 

  クエリを複製する操作

すると、「日付別の売上(2)」という名前のクエリが新たに作成される。このクエリには、コピー元のクエリと同じ処理内容(ステップ)が記録されている。

• 

  複製されたクエリ

「元のクエリ名(2)」という名前のままでは少し紛らわしいので、この時点でクエリ名を変更しておこう。今回は「分類別の売上」という名前に変更した。さらに、今回のクエリでは不要な処理となる「グループ化された行」のステップを削除する。

• 

  クエリ名の変更、ステップの削除

「グループ化」の処理(日付別の売上を集計する処理)が削除され、データ表が集計前の状態に戻る。この状態にしてから、現クエリの目的となる「分類別の売上金額」を求めていく。「グループ化」コマンドをクリックし、「分類」で区分した「売上」の合計を集計するように指定する。

• 

  「グループ化」の設定

これで「分類別の売上金額」にデータ表を加工することができた。このように「クエリの複製」を利用すると、元のクエリとは異なる処理(ステップ)を書き換えるだけで目的のクエリを作成できるようになる。

• 

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

あとは、データ表をExcelに出力するだけ。今回も出力場所を指定できるように「閉じて次に読み込む」を選択した。

• 

  データ表の出力

データ表の出力先には「既存のワークシート」のB3セルを指定した。これでB3セルを先頭にデータ表が出力されるようになる。

• 

  データ表の出力先の指定

データ表の出力結果は以下の図の通り。現時点では、4月1日～4月5日のデータがフォルダーに保存されているので、5日分のデータを分類別に集計した結果が表示される。

• 

  出力されたデータ表

以上で、クエリの作成は完了となる。以降は、4月6日、4月7日、4月8日、……のExcelファイルをフォルダーに順次追加していき、「すべて更新」をクリックするだけ。これで最新の集計結果を即座に確認できるようになる。

このように「クエリの複製」を上手に活用すると、似たような処理を何回も指定する手間を省くことが可能となる。今回の例のように「事前に整形が必要なデータ表」を扱う場合などに重宝する。

さらに、各クエリで処理した結果を同じワークシート(既存のワークシート)に出力すると、それぞれの分析結果を一目で確認できる「ダッシュボード」のようなワークシートを作成できる。

パワークエリを、もっと便利に、もっと快適に、活用するテクニックのひとつとして、参考にして頂ければ幸いだ。