Applies ToMicrosoft 365 Excel Mac 版 Microsoft 365 Excel Excel 網頁版

您可能相當熟悉參數查詢在 SQL 或 Microsoft Query 中的使用方式。 不過Power Query參數有主要差異:

  • 參數可用於任何查詢步驟。 除了做為資料篩選功能之外,參數還可以用來指定檔路徑或伺服器名稱等專案。 

  • 參數不會提示輸入。 您可以改用Power Query快速變更其值。 您甚至可以儲存和擷取 Excel 儲存格中的值。

  • 參數會儲存在簡單的參數查詢中,但與使用的資料查詢是分開的。  建立之後,您可以視需要將參數新增至查詢。

附註    如果您想要用另一種方式建立參數查詢,請參閱在 Microsoft Query 中建立參數查詢。

您可以使用參數來自動變更查詢中的值,避免每次都編輯查詢來變更值。 您只要變更參數值即可。 建立參數後,參數會儲存在特殊參數查詢中,方便您直接從 Excel 進行變更。

  1. 選> [啟動Power Query 編輯器] > [取得資料>其他來源]

  2. 在Power Query 編輯器中,選取 [首頁>管理參數>新參數]

  3. 在 [ 管理參數]對話方塊中,選取 [ 新增]

  4. 視需要設定下列專案:

    姓名    

    這應該會反映參數的函數,但請盡可能保持簡短。

    描述    

    這可以包含任何可協助使用者正確使用參數的詳細資料。

    必要    

    執行下列其中一項: 任何值您可以在參數查詢中輸入任何資料類型的任何值。值清單    您可以在小格線中輸入值,將值限制在特定清單中。 您也必須選取下方的 預設值目前值查詢 選取清單查詢,其類似以逗號分隔並以大括弧括住的 清單 結構化欄。例如,[問題] 狀態欄位可能會有三個值:{「New」, 「Ongoing」, 「Closed」}。 您必須事先建立清單查詢,方法是開啟進階編輯器 (選取 [常用]>進階編輯器) 、移除程式碼範本、輸入查詢清單格式的值清單,然後選取 [完成]當您完成建立參數後,清單查詢會顯示在參數值中。

    類型    

    這會指定參數的資料類型。

    建議的值    

    如有需要,請新增值清單或指定查詢以提供輸入建議。

    預設值

    只有在 [建議的 ] 設為 [ 值清單],並指定哪個清單專案為預設值時,才會出現此設定。 在此情況下,您必須選擇預設值。

    目前值    

    視您使用參數的位置而定,如果這是空白,查詢可能不會傳回任何結果。 如果已選取 [必要 ], 則 [目前值 ] 不能是空值。

  5. 若要建立參數,請選取[確定]

以下是管理資料來源位置變更並防止重新整理錯誤的方法。 例如,假設架構和資料來源相似,請建立參數以輕鬆變更資料來源,並協助避免資料重新整理錯誤。 伺服器、資料庫、資料夾、檔案名或位置有時會變更。 也許資料庫管理員偶爾會調換伺服器、每月一下 CSV 檔案會進入不同的資料夾,或者您需要在開發/測試/生產環境之間輕鬆切換。

步驟 1:建立參數查詢

在下列範例中,您有數個使用匯入資料夾作業匯入的 CSV 檔案 (選取資料>從[檔案] 取得資料>>從資料夾) 取資料夾 C:\DataFilesCSV1。 但有時會使用不同的資料夾做為放置檔案的位置,例如 C:\DataFilesCSV2。 您可以使用查詢中的參數做為不同資料夾的替代值。

  1. 取 [首頁>新參數>管理參數]

  2. 在 [ 管理參數 ] 對話方塊中輸入下列資訊:

    姓名

    CSVFileDrop

    描述

    替代檔案下拉位置

    必要

    類型

    文字

    建議的值

    任何值

    目前值

    C:\DataFilesCSV1

  3. 選取 [確定]

步驟 2:將參數新增至資料查詢

  1. 若要將資料夾名稱設定為參數,請在 [ 查詢設定] 的[ 查詢步驟] 底下,選取 [ 來源],然後選取 [編輯設定]

  2. 請確定 [檔案路徑] 選項已設定為 [參數],然後從下拉式清單中選取您剛建立的參數。

  3. 選取 [確定]

步驟 3:更新參數值

資料夾位置剛剛變更,所以現在您可以直接更新參數查詢。

  1. 選取 [資料 > 連線&查詢] > [ 查詢] 索引標籤,以滑鼠右鍵按一下參數查詢,然後選取 [ 編輯]

  2. 在 [ 目前值] 方 塊中輸入新的位置,例如 C:\DataFilesCSV2

  3. 取 [首頁 > 關閉&載入]

  4. 若要確認結果,請將新資料新增至資料來源,然後使用更新的參數重新整理資料查詢 (選取 資料 > 重新整理所有) 。

有時候,您會想要一種簡單的方法來變更查詢的篩選,以取得不同的結果,而不需要編輯查詢或對相同查詢進行稍微不同的複本。 在此範例中,我們變更日期以方便地變更資料篩選。

  1. 若要開啟查詢,請找出先前從Power Query 編輯器載入的查詢,選取資料中的儲存格,然後選取[查詢>編輯]。 如需詳細資訊,請參閱 在 Excel 中建立、載入或編輯查詢

  2. 選取任何欄標題中的篩選箭號來篩選資料,然後選取篩選命令,例如 [日期/時間篩選 ]> 之後]。 [篩選列] 對話方塊隨即出現。 在 [篩選] 對話方塊中輸入參數

  3. 選取 [ ] 方塊左側的按鈕,然後執行下列其中一項操作:

    • 若要使用現有的參數,請選取 [ 參數],然後從右側顯示的清單中選取您要的參數。

    • 若要使用新的參數,請選取 [新增參數],然後建立參數。

  4. 在 [ 目前值 ] 方塊中輸入新日期,然後選取 [ 首頁 ]> [關閉] & [載入]

  5. 若要確認結果,請將新資料新增至資料來源,然後使用更新的參數重新整理資料查詢 (選取 資料 > 重新整理所有) 。 例如,將篩選值變更為不同的日期以查看新結果。

  6. 在 [ 目前值] 方 塊中輸入新日期。

  7. 取 [首頁 > 關閉&載入]

  8. 若要確認結果,請將新資料新增至資料來源,然後使用更新的參數重新整理資料查詢 (選取 資料 > 重新整理所有) 。

在此範例中,查詢參數中的值是從活頁簿中的儲存格讀取。 您不需要變更參數查詢,只要更新儲存格值即可。 例如,您想要依第一個字母篩選欄,但輕鬆地將值變更為從 A 到 Z 的任何字母。

  1. 在載入您要篩選查詢的活頁簿工作表上,建立一個包含兩個儲存格的 Excel 表格:標題和值。  

    MyFilter

    G

  2. 選取 Excel 表格中的儲存格,然後選取[資料>從表格/範圍>取得資料]。隨即出現Power Query 編輯器。

  3. 在右側 [查詢設定] 窗格的 [名稱] 方塊中,將查詢名稱變更為更有意義的名稱,例如 FilterCellValue。 

  4. 若要傳遞資料表中的值,而非資料表本身,請以滑鼠右鍵按一下 [資料預覽] 中的值,然後選取 [ 向下切入]

    請注意,公式已變更為 = #"Changed Type"{0}[MyFilter]

    當您在步驟 10 中使用 Excel 表格做為篩選時,Power Query參照表格值做為篩選準則。 直接參照 Excel 表格會造成錯誤。

  5. 取 [首頁 > 關閉&載入] > [關閉] & [載入至]。 您現在有一個名為「FilterCellValue」的查詢參數,可在步驟 12 中使用。

  6. 在 [ 匯入資料 ] 對話方塊中,選取 [只建立連線],然後選取 [ 確定]

  7. 開啟您要使用 FilterCellValue 資料表中的值進行篩選的查詢,該資料表先前是從Power Query 編輯器載入的值,方法是選取資料中的儲存格,然後選取[查詢>編輯]。 如需詳細資訊,請參閱 在 Excel 中建立、載入或編輯查詢

  8. 選取任何欄標題中的篩選箭號來篩選資料,然後選取篩選命令,例如 [文字篩選 ]> [開始]。 [篩選列] 對話方塊隨即出現。 

  9. 在 [] 方塊中輸入任何值,例如 「G」,然後選取 [ 確定]。 在此情況下,該值是您在下一個步驟中輸入之 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. 在右側窗格中的 [ 參數] 底下,選取或清除 [ 一律允許資料來源和轉換對話方塊中的參數化]

另請參閱

Excel 說明Power Query

使用查詢參數 (docs.com)

需要更多協助嗎?

想要其他選項嗎?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。