パワークエリを使ってExcelを自動化するときは、その処理手順を「Power Query エディター」で指定しておく必要がある。そこで今回は、「どのように処理手順を指定していくのか？」を実際の操作例を示しながら解説していこう。これを参考に、パワークエリを使用するときの“大まかな作業の流れ"を把握して頂ければ幸いだ。

取得元のデータについて

パワークエリで自動化を実現するには、その処理手順を「Power Query エディター」で指定しておく必要がある。つまり、「Power Query エディター」を自由自在に扱えるようになることが最初の課題といえる。ということで、今回は「Power Query エディター」の具体的な操作例を紹介していこう。

  • 処理手順の指定(1)

今回も「ファルダー内にあるExcelファイルを結合する場合」を例に操作手順を解説していく。使用するデータは、前回の連載で紹介したものと同じだ。念のため、データの状況を再確認しておこう。

以下の図は、あるハンバーガー店の売上データをExcelファイルに記録したものだ。売上データは1日ごとに個別のExcelファイルに記録されており、現時点では3日分のデータが保存されている。

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

各ファイルの内容についても紹介しておこう。日々の売上データは、以下の図に示した形式で記録されている。いずれも同じ形式のデータ表で、「提供方法」や「分類」で仕訳した状態で「数量」と「売上」の数値が記録されている。

  • 4月1日の売上データ

  • 4月2日の売上データ

  • 4月3日の売上データ

このままでは「3日間の売上」についてデータを分析できないので、複数のファイルに分割されている売上データを「1つの表」に結合していこう。この作業をパワークエリで自動化してみる。

パワークエリを使って処理を自動化するときは、以下のような流れで処理手順を指定するのが基本となる。

1. データの取得方法を指定する
2. 取得したデータを適切な形に加工する
3. 加工したデータ表をExcelに出力する

フォルダー内にあるデータを取得する処理

それでは、具体的な操作手順を紹介していこう。最初に「データの取得方法」を指定する。新しいExcelブックを開き、「データ」タブにある「データの取得」をクリックする。今回はフォルダー内あるExcelファイルからデータを一括取得したいので、「ファイルから」→「フォルダーから」を選択する。

  • データの取得方法の指定

データの取得元フォルダーを指定する画面が表示される。売上データのExcelファイルが保存されているフォルダーを選択し、「開く」ボタンをクリックする。

  • データの取得元フォルダーの指定

すると、指定したフォルダー内に保存されているファイルの情報が表示される。これらのデータを結合して取得するときは、「結合」ボタンをクリックして「データの結合と変換」を選択する。

  • 「データの結合と変換」を指定

続いて、「どのファイルを基準にデータを結合するか？」を指定する。今回の例は、すべてのデータ表が同じ形式で作成されているため、どれをサンプルファイルに指定しても構わない。よって、この指定は「最初のファイル」のままでよい。

  • 結合時に基準とするExcelファイルの選択

サンプルファイルを指定できたら、データが記録されている「シート名」を選択する。すると、そのプレビューが右側に表示される。これを確認してから「OK」ボタンをクリックする。

  • ワークシートの指定

以上で「データの取得方法」の指定は完了だ。以下の図に示したようなウィンドウが新たに表示される。これが「Power Query エディター」となる。

  • Power Query エディター

「Power Query エディター」を見ると、3つのExcelファイルを結合したデータ表が作成されているのを確認できる。また、このデータ表の左端には「取得元のExcelファイル名」を記録した列が追加されている。

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

  • 画面をスクロールしてデータを確認

不要な行を削除する処理

作成されたデータ表に問題がなければ、「そのままデータ表をExcelに出力して……」となるが、たいていの場合、データ表に何らかの加工を施す必要があるのが一般的だ。この処理手順を指定するツールが「Power Query エディター」となる。

作成されたデータ表をよく見ると、各日の「売上の合計」を算出した行が含まれていることに気付くと思う。

  • 各日の「売上の合計」を算出した行

要するに、3つのExcelファイルを“単純に縦につなげただけのデータ表"が作成されている訳だ。このまままではデータを分析しにくいので、「合計の行」を削除しておこう。

通常のExcel操作の場合、「行番号」ボタンを右クリックして「削除」を選択すると、不要な行を削除できる。ただし、「Power Query エディター」では、この操作を実行できない。試しに「8」のボタンを右クリックしてみると、右クリックメニューが表示されないことに気付くだろう。

このため、別の方法で行を削除する必要がある。「Power Query エディター」で行を削除するときは、“特定の条件"に基づいて行を削除しなければならい。よって、“特定の条件"を見つけ出すことが最初の一歩になる。

今回の例の場合、「数量」の列に「合計」の文字列データが入力されている行が“不要な行"になると考えられる。これを条件に行を削除していこう。ここでは「フィルター」を使って行を削除してみる。「数量」の列にある「▼」ボタンをクリックする。

  • 「合計」の文字列データとフィルターの起動

「数量」の列に入力されているデータが一覧表示されるので、「合計」のチェックボックスをOFFにして「OK」ボタンをクリックする。

  • 「合計」の行を除外する操作

これで「数量」の列が「合計」の行をデータ表から除外できる。つまり、「合計の行」だけを削除できたことになる。

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

このように、「Power Query エディター」の操作はExcelに“似ている部分"と“そうでない部分"が存在する。フィルターは通常のExcelにも装備されている機能であり、その使い方は「Power Query エディター」の場合も基本的に同じだ。一方、単純に行を削除する操作は、「Power Query エディター」には用意されていない。

加工したデータ表をExcelに出力する操作

ほかにも、「日付」のデータを追加するなど、データ表を加工しておきたい箇所がいくつか見受けられるが、話が長くなってしまうので、先に「データ表をExcelに出力する操作」を紹介しておこう。

加工したデータ表をExcelに出力するときは、「閉じて読み込む」のアイコンをクリックすればよい。

  • データ表をExcelに出力する操作

「Power Query エディター」が終了し、通常のExcel画面に戻る。Excel画面を見ると、「4月の売上」(データを取得したフォルダー名)というワークシートに作成されていることに気付くと思う。ここに先ほど加工したデータ表が出力されている。

  • パワークエリで作成したデータ表(テーブル)

このデータ表は「テーブル」として作成されている。「テーブル」はExcelに用意されている機能の一つで、データを集計したり、分析したりするときに便利な表形式となる。これについては、後の連載で詳しい使い方を解説していこう。

また、画面に右端には「クエリと接続」というウィンドウが表示されている。このウィンドウは、「Power Query エディター」で作成したクエリを操作するときに使用するものとなる。

  • 「クエリと接続」ウィンドウ

以上が、パワークエリを使ってデータを処理するときの“大まかな流れ"となる。今回、示した例の場合、

1. フォルダー内にあるExcelファイルを結合したデータ表を作成する
2. 作成したデータ表から「合計の行」を削除する(データ表の加工)
3. 加工したデータ表をExcelに出力する

という流れになる。

ちなみに、「Power Query エディター」で作成したクエリ(処理手順)は、“Excel本体"ではなく、“現在のExcelファイル"に保存される仕組みになっている。このため、ファイルを保存せずにExcelを終了すると、「Power Query エディター」で作成したクエリも破棄されてしまう。クエリを保持しておきたい場合は、ファイルの保存(上書き保存)を実行しておく必要がある。忘れないように注意しておこう。

ということで、次回は、今回の連載で未処理になっていた「日付データの追加」について詳しく解説していこう。