Excelをノーコードで自動化しよう! パワークエリの教科書 第5回 クエリの更新と取得元データの変更

パワークエリの特長は、指定した処理手順を何度でも繰り返して実行できること。これによりExcelを自動化することが可能となる。ということで、今回は「クエリの更新」について詳しく紹介していこう。また、取得元データ(フォルダー)を変更することにより、既存のクエリ(自動処理)を再利用する方法も紹介する。
クエリの更新による自動化の実現
パワークエリの一番の醍醐味は、クエリとして登録した処理手順を好きなタイミングで何回でも実行できること。この操作が「クエリの更新」となる。今回はクエリの更新によりExcel作業を自動化する方法、ならびに取得元データ(フォルダー)を変更する方法について解説していこう。
これまでの連載では、以下のような処理手順を例に“パワークエリの基本操作"について解説してきた。
＜処理手順＞
1. 「4月の売上」フォルダー内にあるファイルを結合してデータ表を作成する
2. 結合したデータ表から「合計の行」を削除する
3. 取得元のファイル名をもとに「日付」のデータを追加する
4. 加工したデータ表をExcelに出力する
この処理をもういちど最初から実行しなおす操作が「クエリの更新」となる。順番に解説していこう。
まずは、「Power Query エディター」で処理手順を指定したExcelファイルを開く。このとき、「コンテンツの有効化」というボタンが表示される場合もある。この場合は、ボタンをクリックして外部データ(取得元データ)へのアクセスを有効にする。
現在、このExcelファイルには「4月1日」〜「4月3日」の売上データを結合したテーブルが保存されている。
このテーブルに「新しいデータ」を追加するときの操作手順から解説していこう。ここで紹介しているクエリ(自動処理)は、「4月の売上」フォルダーからデータを取得するように指定されている。よって、「4月の売上」フォルダーに新しいファイルを追加するだけで、テーブルにデータを追加することが可能となる。
たとえば、4月3日から2日間が経過し、新たに「4月4日の売上」と「4月5日の売上」というExcelファイルが作成されたとしよう。これらのファイルを「4月の売上」フォルダーに追加する。
この状態で「データ」タブにある「すべて更新」のアイコンをクリックすると、クエリに登録した処理手順が再実行される。つまり、「4月の売上」フォルダー内にあるファイルを結合したデータ表を作成し、そこから「合計の行」を削除して、「日付」のデータを追加する、という処理が再び実行されることになる。その結果、「4月1日」〜「4月5日」の売上データを結合したテーブルに更新することが可能となる。
要するに、フォルダーにファイルを追加して「すべて更新」をクリックするだけで、最新のデータ表に更新できる訳だ。いちいちデータをコピー&ペーストする必要はない。もちろん、4月6日以降の売上データが追加された場合も同様である。
今度は、取得元のExcelファイルを修正したときの例を紹介していこう。たとえば、「4月5日の売上」にデータの入力ミスがあったとしよう。この場合は、取得元のExcelファイル(4月5日の売上.xslx)を開いて、データを修正すればよい。
上図では、「店内飲食」の「ソフトドリンク」の売上データを「25,760」→「25,670」に修正している。このファイルを上書き保存して閉じる。
その後、4月の売上を集計したExcelファイルを開き、「すべて更新」のアイコンをクリックすると、クエリに指定した処理手順が再実行され、修正済みのデータを反映したテーブルに更新される。もちろん、それに合わせて「売上の合計」などの数値も再計算される。
このように、データの追加や修正にクリックひとつで対応できることがパワークエリの魅力となる。念のため、“おさらい"しておこう。今回の例の場合、クエリに以下のような処理手順が登録されている。
＜処理手順＞
1. 「4月の売上」フォルダー内にあるファイルを結合してデータ表を作成する
2. 結合したデータ表から「合計の行」を削除する
3. 取得元のファイル名をもとに「日付」のデータを追加する
4. 加工したデータ表をExcelに出力する
「クエリの更新」を実行すると、上記の処理が再実行され、現時点の「4月の売上」フォルダーをもとに「データの取得と加工」が行われる。つまり、最新状況を反映したデータ表(テーブル)を即座に得られる訳だ。
取得元データの変更
続いては、同様の自動処理を、取得元データ(フォルダー)を変更して実行する方法を紹介していこう。
たとえば、4月の売上とは別に、3月の売上を記録したExcelファイルが31個あったとしよう。これらのExcelファイルは「3月の売上」フォルダーに保存されている。
これを既存のクエリで自動処理したい場合は、どうしたらよいだろうか？　これまでに紹介してきたクエリは「4月の売上」フォルダーからデータを取得するように指定されているので、そのままでは使えない。
現在、「4月の売上」フォルダーに保存されているExcelファイルを別の場所へ移動して、代わりに3月の売上を記録した31個のExcelファイルを「4月の売上」フォルダーに移動する、という方法も考えられるが、あまりスマートな解決策とはいえない。一時的とはいえ、「4月の売上」フォルダーの中に3月の売上データを保存する、というのはトラブルの原因になりかねない。
かといって、新たにクエリの処理手順を指定しなおすのも少し面倒だ。せっかく同様の処理を実行できるクエリがあるのだから、「それを再利用したい」というのが人情であろう。
このような場合は、「データの取得元フォルダー」だけを変更してしまうのが最も効率のよい対応策となる。その手順を詳しく解説していこう。
まずは、クエリが登録されているExcelファイル(4月の売上集計)を複製する。続いて、複製したExcelファイルの名前を「3月の売上集計」に変更する。
このExcelファイルを起動すると、以下の図のようなテーブルが表示される。現時点ではファイル名を変更しただけなので、「4月の売上」フォルダーからデータを取得したテーブルが表示されている。「データ」タブにある「クエリと接続」をクリックしてONにする。
「クエリと接続」ウィンドウが表示されるので、「4月の売上」のクエリをダブルクリックして「Power Query エディター」を起動する。
クエリとして登録した処理手順を編集できるようになる。データの取得元を変更したいときは、「ホーム」タブにある「データ ソース設定」をクリックすればよい。
現在の「データの取得元」(フォルダー)が表示されるので、「ソースの変更」ボタンをクリックする。
以下の図のような画面が表示され、「データの取得元」を変更できるようになる。パスを直接入力するか、もしくは「参照」ボタンをクリックして「データの取得元」となるフォルダーを指定しなおす。その後、「OK」ボタンをクリックする。
元の画面に戻るので、「データの取得元」が変更されていることを確認し、「閉じる」ボタンをクリックする。
これで「データの取得元」の変更は完了だ。この時点で「クエリ名」も変更しておこう。クエリ名は画面右側にある「プロパティ」の部分で変更できる。続いて、「プレビューの更新」のアイコンをクリックすると、「3月の売上」フォルダーからデータが取得され、それを加工したデータ表に表示が更新される。
3月の売上データが正しく処理されていることを確認できたら、「閉じて読み込む」のアイコンをクリックする。
クエリの更新が行われ、3月の売上データを1つに結合したテーブルが表示される。ただし、このデータの並び順は、なぜか「3月10日」が先頭になっている。
これはファイルを取得する順番に起因するものだ。ファイル名を文字コード順に並べた場合、「日」の文字は「0」や「1」、「2」などの数字よりも順番が後になる。よって、「10日」→「11日」→「12日」→ …… →「19日」→「1日」→「20日」→「21日」→ ……、という順番でファイルが取得されていく。
これを正しい日付順に並べ替えたいときは、「日付」の列にある「▼」ボタンをクリックし、「昇順」を選択すればよい。
これでデータを「日付」の順番に並べ替えることができる。画面をスクロールして「売上の合計」なども確認しておこう。これらの数値は「更新されたテーブル」をもとに再計算されるため、「3月の売上」のデータを合計した数値が表示されている。つまり、3月の売上状況を即座に確認できる訳だ。
ちなみに、「日付」の順番に並べ替える処理をクエリの中で指定しておくことも可能となっている。ただし、そのためには「Power Query エディター」でデータを並べ替える方法を学んでおく必要がある。これについては、後の連載で詳しく紹介していく予定だ。
今回の連載で紹介したように、既存のクエリを再利用して別のデータを自動処理することも可能である。細かく見ていくと、シート名が「4月の売上」のままになっている……など、若干の手直しが必要になる部分もあるが、面倒な繰り返し作業を行うことなく、既存のクエリで別のデータを処理できる、というのは大きなメリットといえるだろう。
まだパワークエリに慣れていない方は、よく分からない部分も多いかもしれないが、「このような活用方法もある」といことを頭に入れておくとよい。今後、パワークエリを利用していくときの“便利な活用方法"として、いずれ役に立つはずだ。