Power Queryを使っていると、
- 同じレイアウトのシートが複数存在する
- 各シートに同じ処理を適用したい
- シートごとに同じクエリを作るのが面倒
といった場面に遭遇します。
このような場合に便利なのがクエリの関数化です。
一度作成したクエリを関数として部品化することで、シート名だけを差し替えながら同じ処理を繰り返し実行できます。
本記事では、各シートにヘッダー情報が存在するExcelファイルを例に、
- 1シート用のクエリ作成
- クエリの関数化
- 関数の一括適用
- 複数シートの一括編集
までの流れを実例付きで解説します。
Power Queryの真価は「データを取得すること」ではなく、一度作った処理を再利用することです。クエリの関数化を理解すると、同じ処理を何度も作る必要がなくなり、業務の効率化が大きく進みます。
目次
この記事でできること
- Power Queryで作成したクエリを関数化する考え方を理解できる
- 関数化する際に「可変部分」を見つけるポイントを学べる
- シート名をパラメーター化してクエリを再利用できる
- M言語の
(X)=>の基本的な意味を理解できる - 複数シートに対して同じ変換処理を一括適用できる
- ヘッダー行を持つ複数シートのデータをまとめて整形できる
- 自作関数を呼び出してクエリを展開する方法を学べる
- Power Queryで再利用性の高いクエリを作成できるようになる
概要
自動化を行う時に一番困るのは下の画像のように、各シートにヘッダーデータがあるようなケースです

このような場合はまずは1シートを処理するクエリを作成します
そして、そのクエリを関数化します
関数化するということは、Y=2X+1のような式で表せるということです
こちらのXには複数シート名を入れて使いまわせるようにします
SUM関数/SUM()では()の中身を変えれば、それぞれの合計が出力されます
今回は、()の中身が次から次へと格納されて、連続的に出力されるイメージになります
使用データ
前述のように各シートにはヘッダーデータがあります
その下に欲しい一括処理したいデータがあります

そして、上記のようなシートがファイル内に複数あります

各シートの開始行などは一致している前提です

上の画像は東京のシートですが、次の画像は大阪のシートです

関数化するクエリの作成
まずは1シートを処理するクエリを作成します
こちらのクエリを前述のように関数化します
今回はテーブル化からはじめず、ファイルからデータを取得します

指定するファイルは「今処理しているファイル」です
ファイルを指定したら、ナビゲーター画面が開きます

こちらでは1つのシートを指定し「データの変換」をクリックします
ここでクエリは複製しておきます(1つは関数を作成した後に、関数を適用するクエリになります

下の画像がPower Queryエディタを開いた状態です

必要なのは11行目から下です
こちらは「行の削除」アクションで10行削除します

ここからもう1アクションあります

変換タブで「1行目をヘッダーとして使用」アクションを使用して、見出しを整えます

これで関数化のもとになるクエリは完成です
関数化
関数化は詳細エディタで行います

具体的に関数化する箇所は「可変の箇所」、シート名の箇所です

ここでソースステップの内容を確認しておきましょう

要はファイル内の複数シートを取得しています
ここからナビゲーション画面で1つのシートを選択しています
ですので、作成した関数を適用する場合は「東京、大阪、名古屋」と関数化した箇所に連続して格納されるようにすればいいのです
では実際に記号Xを埋め込んでみましょう

(X)=>はXを記号として使用するという合図になります⇒詳細
ここまで行うとクエリが関数に変ります

関数は「myFunction」と名前を変えておきましょう

ここでパラメーターに「名古屋」と入れてみましょう

これで名古屋のシートデータが変換されて出力されます
関数の適用
ここで関数の複製元のクエリを選択し直します

その後は、適用したステップでソースデータを残して後は全て削除します

ここから列の追加タブで作成済みの関数を呼び出します

関数のXに入るのは「Item」になります

これでXに東京、大阪、名古屋と値が格納され、クエリが作成されていくことになります

結果、次の画像のようになります

ここからmyFunctionを展開すれば完成です

次の画像が展開した後のものです

<まとめ>
今回は、ヘッダー行がある複数シートを、クエリの関数化を使用して一括処理しました
クエリの関数化のポイントは可変の箇所を見つけることです
今回は、1度作成したクエリから可変の箇所を見つけました
そうはいっても、なかなか最初は慣れないかと思いますが、次第にツボが見えてくると思います


**************************
【一度作成したクエリを関数化して複数シートに使いまわし、結果的に一括処理する】
エクセルファイル内で複数シートに分かれていると、一括処理ができなくて困ることがあります
一番、困るのは各シートにヘッダーデータがあるようなケースです

このような場合はまずは1シートを処理するクエリを作成します
そして、そのクエリを関数化します
関数化するということは、Y=2X+1のような式で表せるということです
こちらのXには複数シート名を入れて使いまわせるようにします
SUM関数/SUM()では()の中身を変えれば、それぞれの合計が出力されます
今回は、()の中身が次から次へと格納されて、連続的に出力されるイメージになります
目次
使用データ
前述のように各シートにはヘッダーデータがあります
その下に欲しい一括処理したいデータがあります

そして、上記のようなシートがファイル内に複数あります

各シートの開始行などは一致している前提です

上の画像は東京のシートですが、次の画像は大阪のシートです

関数化するクエリの作成
まずは1シートを処理するクエリを作成します
こちらのクエリを前述のように関数化します
今回はテーブル化からはじめず、ファイルからデータを取得します

指定するファイルは「今処理しているファイル」です
ファイルを指定したら、ナビゲーター画面が開きます

こちらでは1つのシートを指定し「データの変換」をクリックします
ここでクエリは複製しておきます(1つは関数を作成した後に、関数を適用するクエリになります

下の画像がPower Queryエディタを開いた状態です

必要なのは11行目から下です
こちらは「行の削除」アクションで10行削除します

ここからもう1アクションあります

変換タブで「1行目をヘッダーとして使用」アクションを使用して、見出しを整えます

これで関数化のもとになるクエリは完成です
関数化
関数化は詳細エディタで行います

具体的に関数化する箇所は「可変の箇所」、シート名の箇所です

ここでソースステップの内容を確認しておきましょう

要はファイル内の複数シートを取得しています
ここからナビゲーション画面で1つのシートを選択しています
ですので、作成した関数を適用する場合は「東京、大阪、名古屋」と関数化した箇所に連続して格納されるようにすればいいのです
では実際に記号Xを埋め込んでみましょう

(X)=>はXを記号として使用するという合図になります⇒詳細
ここまで行うとクエリが関数に変ります

関数は「myFunction」と名前を変えておきましょう

ここでパラメーターに「名古屋」と入れてみましょう

これで名古屋のシートデータが変換されて出力されます
関数の適用
ここで関数の複製元のクエリを選択し直します

その後は、適用したステップでソースデータを残して後は全て削除します

ここから列の追加タブで作成済みの関数を呼び出します

関数のXに入るのは「Item」になります

これでXに東京、大阪、名古屋と値が格納され、クエリが作成されていくことになります

結果、次の画像のようになります

ここからmyFunctionを展開すれば完成です

次の画像が展開した後のものです

<まとめ>
今回は、ヘッダー行がある複数シートを、クエリの関数化を使用して一括処理しました
クエリの関数化のポイントは可変の箇所を見つけることです
今回は、1度作成したクエリから可変の箇所を見つけました
そうはいっても、なかなか最初は慣れないかと思いますが、次第にツボが見えてくると思います










コメントを残す