Power Queryの関数化実践テクニック|複数シートを一括で編集・結合する方法

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度作成したクエリから可変の箇所を見つけました

そうはいっても、なかなか最初は慣れないかと思いますが、次第にツボが見えてくると思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

**************************

【一度作成したクエリを関数化して複数シートに使いまわし、結果的に一括処理する】

エクセルファイル内で複数シートに分かれていると、一括処理ができなくて困ることがあります

一番、困るのは各シートにヘッダーデータがあるようなケースです

このような場合はまずは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度作成したクエリから可変の箇所を見つけました

そうはいっても、なかなか最初は慣れないかと思いますが、次第にツボが見えてくると思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です