【Excel】Power Queryでパラメータクエリを作る方法!Excelのセル値で取得条件を動的に変える

ExcelのPower Queryで、特定の条件を動的に変更したい場面は多いでしょう。例えば、年度や地域などの条件を都度手入力するのは非効率です。この記事では、Excelのセルに入力した値をPower Queryの取得条件として利用する「パラメータクエリ」の作成方法を解説します。これにより、レポート作成の自動化やデータ更新の手間を大幅に削減できます。

Excelのセルに設定した値に応じて、Power Queryで取得するデータの条件を自動で変更できるようになります。これにより、レポート作成の効率が劇的に向上します。

Power Queryでパラメータクエリを活用するメリット

Power Queryでパラメータクエリを作成すると、Excelファイル内で取得条件を管理できます。これにより、外部データソースからデータを取得する際に、毎回クエリを編集する必要がなくなります。例えば、年度や商品コードなどの条件をExcelシート上で変更するだけで、Power Queryが自動的にその条件を反映したデータを取得します。これは、定期的なレポート作成や、様々な条件でのデータ分析において、作業時間を大幅に短縮する強力な手法です。

パラメータクエリの仕組みと作成準備

パラメータクエリとは、Power Queryで外部データを取り込む際の条件(フィルター条件など)を、Excelシート上のセル値で指定できるようにする仕組みです。通常、Power Queryエディターで直接フィルター条件を設定しますが、パラメータクエリを使うと、その条件をExcelシート上の値に紐づけることができます。これにより、Power Queryの知識が少ないユーザーでも、Excelシートの値を変更するだけでデータの取得条件を調整できるようになります。

作成準備として、まずパラメータとして使用したい値(例:年度、地域名、商品コードなど)をExcelシートのどこかに入力します。その入力セル範囲をテーブルに変換しておくと、Power Queryで参照しやすくなります。テーブルに変換することで、セルの追加や削除にも柔軟に対応できるようになります。

Excelセル値をPower Queryの条件として取得する手順

ここでは、Excelシート上のセル値をPower Queryのフィルター条件として使用する手順を解説します。例として、「特定の年度の売上データを取得する」というシナリオを想定します。まず、Excelシートの任意の場所に年度を入力するセル(例:A1セル)を用意し、そのセルをテーブルに変換します。その後、Power Queryで売上データテーブルを読み込み、年度を条件にフィルターをかけます。

1. パラメータ用セルをテーブルに変換する
   ExcelシートのA1セルに「年度」と入力し、B1セルに取得したい年度(例:2023)を入力します。このA1:B1の範囲を選択し、「挿入」タブの「テーブル」をクリックします。ダイアログが表示されたら、「先頭行をテーブルの見出しとして使用する」にチェックを入れ、「OK」をクリックします。テーブルに名前を付けることもできます(例:「パラメータ」)。
2. ExcelテーブルをPower Queryに読み込む
   「データ」タブの「データの取得」→「ファイルから」→「Excelブックから」を選択します。先ほど作成したExcelファイルを選択し、「インポート」をクリックします。ナビゲーターウィンドウが表示されたら、作成したテーブル(例:「パラメータ」)を選択し、「データの変換」をクリックしてPower Queryエディターを開きます。
3. 元のデータテーブルをPower Queryに読み込む
   Power Queryエディターで、「ホーム」タブの「新しいソース」→「ファイル」→「Excelブック」を選択し、売上データが格納されているExcelファイルを開きます。売上データテーブルを選択し、「OK」をクリックします。
4. パラメータクエリを定義する
   Power Queryエディターの左側にある「クエリ」ペインで、売上データテーブルのクエリを選択した状態で、「ホーム」タブの「詳細エディター」をクリックします。表示されたM言語コードの末尾に、以下のコードを追加します。これは、パラメータテーブルから年度の値を取得する処理です。

   "年度" = Table.Column(パラメータ, "Column1"){0}

   ※「パラメータ」はテーブル名、「Column1」はテーブルの列名(通常、テーブル変換時に自動で付与される)、`{0}`は最初の行(0から始まる)を指します。必要に応じてテーブル名や列名を変更してください。

5. 元のクエリにフィルターを適用する
   詳細エディターで追加したコードを確定した後、売上データテーブルの「年度」列のフィルターボタンをクリックします。「テキストフィルター」→「指定の値と等しい」を選択します。表示されるダイアログで、フィルター条件として、先ほど詳細エディターで定義した変数名(例:`"年度"`)を選択します。
6. クエリを閉じて読み込む
   「ホーム」タブの「閉じて読み込む」をクリックし、結果をExcelシートに読み込みます。

Excelシートの値変更でPower Queryが自動更新されない場合の対処法

Excelシート上のパラメータ値を変更しても、Power Queryが自動的に更新されない場合があります。これは、Power Queryの更新は手動で行われるか、特定のイベント(例:ブックの保存時)に紐づけられているためです。

Excelシートの変更をPower Queryに反映させる方法

Excelシートのパラメータ値を変更したら、Power Queryのデータを更新する必要があります。これは以下のいずれかの方法で行えます。

1. 「すべて更新」を手動で実行する
   「データ」タブの「すべて更新」ボタンをクリックします。これにより、ブック内のすべてのPower Queryが再実行され、変更されたパラメータ値が反映されます。
2. ブックの保存時に自動更新を設定する
   Power Queryエディターで、対象のクエリを選択し、「クエリのプロパティ」を開きます。「使用時にデータを更新する」のチェックボックスをオンにすると、ブックを開くたびにデータが更新されます。より細かく制御したい場合は、VBAを使用してブックの保存時やシート変更時に更新をトリガーすることも可能です。

VBAによる自動更新の実装例

VBAを使用すると、さらに高度な自動化が可能です。例えば、パラメータシートの特定の値が変更されたら、自動的にPower Queryを更新するように設定できます。以下は、ブックの保存時にPower Queryを更新するVBAコードの例です。

VBAコードの記述手順

1. VBAエディターを開く
   Excelで「Alt」+「F11」キーを押してVBAエディターを開きます。
2. ThisWorkbookモジュールにコードを貼り付ける
   左側のプロジェクトウィンドウで「ThisWorkbook」をダブルクリックし、右側のコードウィンドウに以下のコードを貼り付けます。

   Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim qt As WorkbookQuery

   ' パラメータシートを指定
   Set ws = ThisWorkbook.Sheets("パラメータ") 'シート名を適宜変更してください

   ' ブック内のすべてのPower Queryを更新
For Each qt In ThisWorkbook.Queries
qt.Refresh
Next qt
End Sub

   ※「パラメータ」の部分は、ご自身のパラメータシートの名前に変更してください。

3. VBAプロジェクトを保存する
   「ファイル」メニューから「保存」を選択し、Excelブックを「マクロ有効ブック(.xlsm)」形式で保存します。

このVBAコードを設定すると、Excelブックを保存するたびに、すべてのPower Queryが自動的に更新されます。これにより、パラメータの変更が即座に反映されるようになります。

Power Queryのパラメータクエリでよくある誤解と注意点

パラメータクエリは非常に便利ですが、いくつかの注意点があります。特に、テーブルの列名や行の指定方法、そして更新の仕組みについて誤解が生じやすいです。

テーブルの列名と行の指定について

詳細エディターでパラメータ値を参照する際、テーブル名だけでなく、列名も正確に指定する必要があります。テーブルを変換した際に自動で付与される列名(例:Column1, Column2)は、意図しないものになることがあります。もし列名が意図しないものになっている場合は、Power Queryエディターの「ホーム」タブにある「列のヘッダーの昇格」や「列名の変更」機能で修正できます。また、行の指定は `{0}` で最初の行、`{1}` で2番目の行のように、0から始まるインデックスで指定します。単一の値しかパラメータとして使用しない場合は、必ず `{0}` を指定してください。

更新の連動性についての誤解

Excelシートの値を変更しただけでは、Power Queryは自動的に更新されません。これは、Power Queryの更新は明示的な操作(「すべて更新」ボタンなど)や、VBAによるトリガーが必要だからです。この点を理解せず、値変更後にデータが更新されないと困惑するケースがあります。前述のVBAコードや、ブックを開いたときの自動更新設定を活用することで、この問題を回避できます。

複雑な条件指定の限界

パラメータクエリは、単一の値(例:年度、地域名)を条件として指定するのに適しています。しかし、複数の条件を組み合わせたり、条件のパターンが非常に複雑な場合は、パラメータクエリだけでは管理が難しくなることがあります。そのような場合は、条件を複数列持つテーブルを作成したり、VBAでより複雑なロジックを記述したり、あるいはPower Queryの関数を組み合わせて柔軟に対応する必要があります。例えば、複数の年度を一度に指定したい場合は、パラメータテーブルに複数行で年度をリストアップし、クエリ側で `Table.Contains` などの関数を使って条件に合致するかどうかを判定するなどの工夫が考えられます。

Power Queryパラメータクエリと他の機能との比較

Power Queryで条件を動的に変更する機能は、パラメータクエリ以外にもいくつか考えられます。しかし、それぞれ得意なことや使い方が異なります。

項目	パラメータクエリ	Excelの数式(例:XLOOKUP, FILTER)	VBA
主な用途	Power Queryでのデータ取得条件をExcelシートの値で制御	Excelシート上でのデータ抽出・参照	Excel操作全般の自動化、複雑なロジックの実装
学習コスト	中程度(M言語の基本理解が必要)	低〜中程度(関数の理解が必要)	高(プログラミング知識が必要)
更新の自動化	手動更新、VBAによるトリガー、ブックを開いたとき	シート変更時に自動更新(一部)	VBAで完全に制御可能
データソース連携	得意(様々なデータソースに対応)	限定的(主にExcelシート内データ)	可能(ADODBなどを使用)
柔軟性	中程度(単一またはシンプルな複数条件)	高(複雑な条件式も可能)	非常に高い(ほぼ全ての処理を実装可能)

パラメータクエリは、Power Queryでのデータ取得プロセスをExcelシートから制御したい場合に最も適しています。Excelの数式は、あくまでExcelシート内でのデータ処理に強みがあります。VBAは最も柔軟性が高いですが、学習コストが高くなります。目的に応じてこれらの機能を使い分けることが重要です。

この記事では、Excelのセル値をPower Queryの取得条件として利用するパラメータクエリの作成方法を解説しました。Excelシート上の値を変更するだけで、Power Queryが取得するデータの条件を動的に変えられるようになりました。これにより、レポート作成やデータ分析の効率が大幅に向上するでしょう。今後は、複数のパラメータを設定したり、VBAと連携してさらに高度な自動化に挑戦することをお勧めします。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】