Excelをノーコードで自動化しよう! パワークエリの教科書 第3回 クエリに新しい処理手順を追加する方法

前回の連載では、フォルダー内にあるExcelファイルを結合して、そこから「合計の行」を削除するクエリ(自動処理)を作成した。今回は、このクエリに新しい処理手順を追加する方法を紹介していこう。パワークエリを使用するときの“大まかな作業の流れ"を把握できるように、実際に試してみながら学習を進めていくとよい。
「Power Query エディター」の呼び出し
今回も、パワークエリを使用するときの“大まかな作業の流れ"を確認していこう。今回は、すでに登録されているクエリ(自動処理)に「新しい処理手順」を追加する方法を解説する。
前回の連載では、
1. フォルダー内にあるExcelファイルを結合したデータ表を作成する
2. 作成したデータ表から「合計の行」を削除する
3. 加工したデータ表をExcelに出力する
という処理手順でデータ表(テーブル)を作成した。その出力結果は以下の図のようになっている。
このデータ表の左端には「取得元のExcelファイル名」を記録した列が配置されている。このままではデータを分析しにくいので、この列をもとに「日付」のデータを作成してみよう。
「データ」タブにある「クエリと接続」をクリックしてONにし、「クエリと接続」ウィンドウを表示する。
前回の連載で処理手順を指定したクエリ(自動処理)が一覧表示される。これらのうち、「4月の売上からファイルを変換」や「ヘルパークエリ」に分類されているクエリは、複数のファイルを結合するためにExcelが自動登録したクエリとなる。その下に表示されている「4月の売上」(取得元のフォルダー名)のクエリをダブルクリックする。
「Power Query エディター」が起動し、クエリの処理手順を再編集できるようになる。なお、これまでに指定した処理手順の後に「新しい処理手順」を追加するときは、“最後のステップ"を選択しておく必要がある。念のため、「適用したステップ」の領域で「一番下にあるステップ」が選択されていることを確認しておこう。
「日付」の列を追加する処理
それでは、「取得元のExcelファイル名」をもとに「日付」のデータを作成する処理を追加していこう。「Source.Name」の列名をクリックして列全体を選択する。
この列には「X月X日の売上.xlsx」という形で文字列データが記録されている。ここから「の売上.xlsx」の文字を削除すると、「日付」のデータを作成できる。つまり、「の」より前にある文字だけを抜き出せばよいことになる。
このような場合は「抽出」というコマンドを使って「新しい列」を作成する。「列の追加」タブを選択し、「抽出」コマンドから抽出方法を選択する。今回の例の場合、「区切り記号の前のテキスト」を選択すればよい。
区切り記号を指定する画面が表示されるので、「の」と入力して「OK」ボタンをクリックする。
データ表の右端に列が追加され、そこに「の」より前にある文字列だけを抽出したデータが自動入力される。
これで日付データを作成できたことになる。ただし、少しだけ手直しが必要だ。最初に「列の名前」を変更する。列名の部分をダブルクリックし、新しい「列の名前」を入力する。今回は「日付」という列名に変更した。
続いて、データ型を変更する。現時点では、データ型に「テキスト」が指定されているので、これを「日付」に変更する。
右端に「日付」が配置されているデータ表は少し見づらいので、「列の並び順」を変更しておこう。この操作は、列名を左右にドラッグすると実行できる。これで「日付」データの作成は完了となる。
このように「Power Query エディター」に用意されているコマンドを利用することで、希望する形にデータを加工していくことも可能である。とはいえ、まだパワークエリの学習を始めたばかりなので、「どんなコマンドが用意されているか？」を把握できていない方が大半を占めるだろう。
ここでは「抽出」というコマンドの使い方を紹介したが、このほかにも「Power Query エディター」には数多くのコマンドが用意されている。これらの“用途"や“操作手順"を学んでいくことがパワークエリを使いこなすための入門編となる。
今後、本連載で主要なコマンドの使い方を紹介していくので、これを参考にパワークエリの知識を少しずつ増やしていくとよいだろう。
不要な列を削除する処理
「日付」のデータを作成できたら、もう「Source.Name」の列は不要だ。速やかに削除しておこう。「Source.Name」の列名を右クリックして「削除」を選択する。
すると、「Source.Name」の列が削除され、以下の図のようなデータ表に仕上げることができる。
前回の連載で紹介したように、データ表から「行」を削除するときは、“特定の条件"に基づいて行を削除する必要があった。一方、「列」の削除は、右クリックメニューで簡単に実行することができる。こういった違いがあることも「Power Query エディター」ならではの仕様といえる。
編集後のクエリでデータ表を更新する操作
以上で、データ表の加工は完了。修正したクエリ(処理手順)でデータ表をExcelに出力してみよう。「ホーム」タブを選択し、「閉じて読み込む」のアイコンをクリックする。
「Power Query エディター」が終了し、前回と同じ「4月の売上」というワークシートに、更新後のデータ表(テーブル)が出力される。
各列の表示形式の指定
最後に、出力されたデータ表の「表示形式」を変更して見た目を整えておこう。といっても、これは普通にExcelを操作する場合と同じなので、新たに覚えるべき操作ではない。
たとえば、A列を選択して「セルの書式設定」を呼び出すと、「日付」の表示方法を自由に変更できるようになる。
同様に、D列の表示形式に「数値」、E列の表示形式に「通貨」を指定すると、データ表(テーブル)の見た目を以下の図のように変更できる。Excelの基本的な使い方を知っている方なら、これは「特に問題のない操作」といえるだろう。
これで、4月1日〜4月3日の売上データを結合した表(テーブル)を作成できたことになる。まだパワークエリに慣れていない方は、「意外と手間がかかる……」とか、「データをコピペしたほうが簡単かも……」と感じたかもしれない。しかし、それは最初のうちだけだ。パワークエリに慣れてしまえば、「いちいちコピペするなんて面倒くさい!」と考え方が変化していくだろう。
パワークエリの便利な点は、いちどクエリ(自動処理)を登録しておけば、同じ処理を瞬時に完了できること。新たに4月4日、4月5日、4月6日……とExcelファイルが追加されたときも、クリックひとつでデータ表(テーブル)を更新できる。このため、すぐにデータ分析を始めることが可能となる。
なお、パワークエリを活用するには、テーブルの扱い方についても知識を蓄えておく必要がある。ということで、次回は、テーブルの便利な使い方や注意点について紹介していこう。