パラメーター クエリを作成する (Power Query)

適用先
Excel for Microsoft 365 Excel for Microsoft 365 for Mac

SQL または Microsoft Query でのパラメーター クエリの使用については、よく理解しているかもしれません。 ただし、Power Query パラメーターには重要な違いがあります。

  • パラメーターは、任意のクエリ ステップで使用できます。 パラメータは、データフィルタとして機能するだけでなく、ファイルパスやサーバ名などを指定するために使用することもできます。
  • パラメーターでは入力を求められません。 代わりに、Power Query を使用して値をすばやく変更できます。 Excel のセルから値を格納および取得することもできます。
  • パラメーターは単純なパラメーター クエリに保存されますが、使用されるデータ クエリとは別です。 作成したら、必要に応じてクエリにパラメーターを追加できます。

注釈 別の方法でパラメーター クエリを作成する場合は、「 Microsoft Query でパラメーター クエリを作成する」を参照してください。

パラメーターの作成

パラメータを使用すると、クエリ内の値を自動的に変更し、値を変更するたびにクエリを編集しないようにすることができます。 パラメータ値を変更するだけです。 パラメーターを作成すると、特別なパラメーター クエリに保存され、Excel から直接変更することができます。

  1. [データ]>データの取得>その他のソース>起動 Power Query エディター] を選択します。

  2. Power Query エディターで、[ホーム>パラメーターの管理] > [新しいパラメーター] を選択します。

  3. [ パラメーターの管理 ] ダイアログ ボックスで、[ 新規] を選択します。

  4. 必要に応じて、以下を設定します。

    名前 これにはパラメーターの関数が反映されている必要がありますが、できるだけ短くしてください。
    説明 これには、ユーザーがパラメータを正しく使用するのに役立つ詳細を含めることができます。
    必須 次のいずれかの操作を行います。

    任意の値 パラメーター クエリには、任意のデータ型の任意の値を入力できます。

    値のリスト 値を小さいグリッドに入力することで、値を特定のリストに制限できます。 また、以下で [既定値] と [ 現在値 ] を選択する必要があります。

    クエリ コンマで区切られ、中かっこで囲まれた リスト 構造化列に似たリスト クエリを選択します。

    たとえば、"問題の状態" フィールドには、{"New"、"Ongoing"、"Closed"} という 3 つの値を含めることができます。 リスト クエリは事前に作成するには、詳細エディターを開き ([ホーム]>詳細エディター を選択し)、コード テンプレートを削除し、クエリ リスト形式で値のリストを入力して、[完了] を選択する必要があります。

    パラメーターの作成が完了すると、パラメーター値にリスト クエリが表示されます。
    型 これは、パラメーターのデータ型を指定します。
    提案された値 必要に応じて、値のリストを追加するか、入力候補を提供するクエリを指定します。
    既定値 これは、[ 候補] が [ 値のリスト] に設定され、どのリスト項目が既定であるかを指定する場合にのみ表示されます。 この場合は、既定値を選択する必要があります。
    現在値 パラメーターを使用する場所によっては、これが空白の場合、クエリは結果を返さない可能性があります。 [ 必須] が選択されている場合、[ 現在の値] を空にすることはできません。

  5. パラメーターを作成するには、[ OK] を選択します。

パラメーターを使用してデータ ソースを変更する

データ ソースの場所に対する変更を管理し、更新エラーを防ぐ方法を次に示します。 たとえば、スキーマとデータ ソースが似ていると仮定し、データ ソースを簡単に変更できるパラメーターを作成し、データ更新エラーを回避できるようにします。 サーバー、データベース、フォルダー、ファイル名、または場所が変更される場合があります。 データベース・マネージャーがサーバーを交換することがある、毎月の CSV ファイルのドロップが別のフォルダーに入っている、開発/テスト/実稼働環境を簡単に切り替える必要があるなどです。

手順 1: パラメーター クエリを作成する

次の例では、フォルダー C:\DataFilesCSV1 のインポート フォルダー操作 (データの選択>データの取得>フォルダーからFilesフォルダーから>フォルダーから) を使用してインポートする CSV ファイルがいくつかあります。 ただし、ファイルをドロップする場所として別のフォルダー (C:\DataFilesCSV2) が使用される場合があります。 クエリ内のパラメーターを、別のフォルダーの代替値として使用できます。

  1. [ホーム]>[パラメーターの管理]>[新しいパラメーター] を選択します。

  2. [ パラメーターの管理 ] ダイアログ ボックスに次の情報を入力します。

    名前 CSVFileDrop
    説明 代替ファイルの格納場所
    必須 ○
    型 テキスト
    提案された値 任意の値
    現在値 C:\DataFilesCSV1

  3. [OK] を選択します。

手順 2: データ クエリにパラメーターを追加する

  1. フォルダー名をパラメーターとして設定するには、[ クエリの設定] の [クエリ ステップ] で、[ ソース] を選択し、[ 設定の編集] を選択します。
  2. [ ファイル パス ] オプションが [パラメーター] に設定されていることを確認し、先ほど作成したパラメーターをドロップダウン リストから選択します。
  3. [OK] を選択します。

手順 3: パラメーター値を更新する

フォルダーの場所を変更したので、これでパラメーター クエリを更新するだけです。

  1. [データ>接続] & [クエリ]>[クエリ] タブを選択し、パラメーター クエリを右クリックして、[編集] を選択します。
  2. [ 現在の値 ] ボックスに新しい場所 ( C:\DataFilesCSV2 など) を入力します。
  3. [ホーム] >[読み込み & 閉じる] を選択します。
  4. 結果を確認するには、データ ソースに新しいデータを追加してから、更新されたパラメーター (データの選択>すべて更新) を使用してデータ クエリを更新します。

パラメーターを使用してデータをフィルター処理する

クエリのフィルターを簡単に変更して、クエリを編集したり、同じクエリの少し異なるコピーを作成したりせずに、異なる結果を得ることができます。 この例では、データ フィルターを変更するために日付を変更します。

  1. クエリを開くには、以前にPower Query エディターから読み込まれたクエリを見つけ、データ内のセルを選択して、[クエリ>編集] を選択します。 詳細については、「 Excel でクエリを作成、読み込み、または編集する」を参照してください。

  2. 列見出しのフィルター矢印を選択してデータをフィルター処理し、[ 日付/時刻フィルター>後] などのフィルター コマンドを選択します。 [ 行のフィルター ] ダイアログ ボックスが表示されます。

    [フィルター] ダイアログ ボックスでのパラメーターの入力

  3. [ 値] ボックスの左側にあるボタンを選択して、次のいずれかの操作を行います。

    • 既存のパラメータを使用するには、[ パラメータ] を選択し、右側に表示される一覧から目的のパラメータを選択します。
    • 新しいパラメーターを使用するには、[ 新しいパラメーター] を選択し、パラメーターを作成します。

  4. [現在の値] ボックスに新しい日付を入力し、[ホーム] >[閉じる] & [読み込み] を選択します。

  5. 結果を確認するには、データ ソースに新しいデータを追加してから、更新されたパラメーター (データの選択>すべて更新) を使用してデータ クエリを更新します。 たとえば、フィルター値を別の日付に変更すると、新しい結果が表示されます。

  6. [ 現在の値 ] ボックスに新しい日付を入力します。

  7. [ホーム] >[読み込み & 閉じる] を選択します。

  8. 結果を確認するには、データ ソースに新しいデータを追加してから、更新されたパラメーター (データの選択>すべて更新) を使用してデータ クエリを更新します。

セル値を使用してデータをフィルター処理する

この例では、query パラメーターの値はブックのセルから読み取られます。 パラメーター クエリを変更する必要はなく、セルの値を更新するだけです。 たとえば、最初の文字で列をフィルター処理し、値を A から Z までの任意の文字に簡単に変更したい場合があります。

  1. フィルター処理するクエリが読み込まれているブックのワークシート上で、ヘッダーと値の 2 つのセルを持つ Excel テーブルを作成します。

    MyFilter
    G

  2. Excel テーブルのセルを選択し、[データ>データの取り込み]>テーブル/範囲から を選択します。Power Query エディターが表示されます。

  3. 右側の [クエリ設定] ウィンドウの [名前] ボックスで、クエリ名をわかりやすい名前 (FilterCellValue など) に変更します。

  4. テーブル自体ではなくテーブルの値を渡すには、[データのプレビュー] で値を右クリックし、[ ドリルダウン] を選択します。
    数式が = #"Changed Type"{0}[MyFilter]
    手順 10 で Excel テーブルをフィルターとして使用すると、Power Query はフィルター条件としてテーブル値を参照します。 Excel テーブルを直接参照すると、エラーが発生します。

  5. [ホーム]>[読み込み & 閉じる]>[読み込み先 & 閉じる] を選択します。 これで、手順 12 で使用する "FilterCellValue" という名前のクエリ パラメーターが作成されました。

  6. [ データのインポート ] ダイアログ ボックスで、[ 接続の作成のみ] を選択し、[ OK] を選択します。

  7. データ内のセルを選択し、[クエリ>編集] を選択して、Power Query エディターから以前に読み込まれた FilterCellValue テーブルの値でフィルター処理するクエリを開きます。 詳細については、「 Excel でクエリを作成、読み込み、または編集する」を参照してください。

  8. 列見出しのフィルター矢印を選択してデータをフィルター処理し、[ テキスト フィルター>指定の値で始まる] などのフィルター コマンドを選びます。 [ 行のフィルター ] ダイアログ ボックスが表示されます。

  9. [ 値 ] ボックスに "G" などの任意の値を入力し、[ OK] を選択します。 この場合、値は、次の手順で入力する FilterCellValue テーブルの値の一時的なプレースホルダーです。

  10. 数式バーの右側にある矢印を選択して、数式全体を表示します。 数式内のフィルター条件の例を次に示します。

    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

  11. フィルターの値を選択します。 数式で "G" を選択します。

  12. M Intellisense を使用して、作成した FilterCellValue テーブルの最初の数文字を入力し、表示される一覧から選択します。

  13. [ホーム] >[閉じる]、[閉じる] > [読み込み &] を選択します。

結果

これで、クエリでは、作成した Excel テーブルの値を使用して、クエリ結果をフィルター処理します。 新しい値を使用するには、手順 1 で元の Excel テーブルのセルの内容を編集し、"G" を "V" に変更してからクエリを更新します。

パラメーター クエリの使用を制御する

パラメーター クエリを許可するかどうかを制御できます。

  1. Power Query エディターで、[ファイル] >[オプション] と [設定]>[クエリ オプション]>Power Query エディター を選択します。
  2. 左側のウィンドウの [グローバル] で、[Power Query エディター] を選択します。
  3. 右側のウィンドウの [パラメーター] で、[ データ ソースと変換のダイアログで常にパラメーター化を許可する] をオンまたはオフにします。

関連項目

Power Query for Excel のヘルプ

クエリ パラメーターの使用 (docs.com)