建立參數查詢

當您在 Excel 中查詢資料時,您可能會想要使用輸入值-參數-指定查詢的相關資訊。 若要這樣做,您需要建立參數查詢。 您建立參數查詢的方式,以及它們的運作方式取決於您使用的是 Microsoft Query 或 Power Query。

提示: Power Query 參數與以 SQL 為基礎的查詢中使用的參數極為不同。 此外,如果您只需要篩選資料,您就可以使用查詢(而不是實際參數)。 在 Power Query 中建立參數之前,請先閱讀 Power Query 範例區段。

Microsoft Query

Power Query

參數如何影響查詢

參數是在查詢的 WHERE 子句中使用,它們的作用中通常是檢索資料的篩選。

您可以在任何查詢步驟中使用參數。 除了作為資料篩選器之外,您還可以使用參數來指定檔案路徑或伺服器名稱等專案。

參數輸入選項

當執行或刷新查詢、使用常數作為輸入值,或使用指定儲存格的內容作為輸入值時,參數可以提示使用者輸入值。

參數不會提示輸入。 您可以改為使用 Power Query 編輯器變更其值。 或者,您可以使用查詢來參照外部位置,而不是 bona fide 參數,您可以輕鬆編輯此值。

參數範圍

參數是它所修改之查詢的一部分,而且無法在其他查詢中重複使用。

參數是獨立于查詢-建立之後,您可以視需要將參數新增至查詢。

  1. 按一下 [資料>取得 & 轉換資料] > 從Microsoft Query其他來源>取得資料>。

  2. 依照 [查詢嚮導] 的步驟進行。 在 [查詢嚮導–完成]畫面上,選取 [在 Microsoft Query 中查看資料] 或 [編輯查詢],然後按一下[完成]。 [Microsoft Query] 視窗隨即開啟,並顯示您的查詢。

  3. 按一下 [ View> SQL]。 在出現的 [SQL] 對話方塊中,尋找 WHERE 子句:以單字開頭的一行,通常是在 SQL 程式碼的結尾。 如果沒有 WHERE 子句,請在查詢結尾的新行上輸入一個位置,以新增一個。

  4. 在哪裡,輸入功能變數名稱、比較運算子(=、<、>、LIKE 等),以及下列其中一項:

    • 針對一般參數提示,請輸入問號(?)。 在執行查詢時,在出現的提示中不會顯示任何有用的片語。

      MS Query 的 SQL 視圖,強調 WHERE 子句

    • 如需可協助使用者提供有效輸入的參數提示,請輸入以方括弧括住的片語。 在執行查詢時,該片語會顯示在參數提示中。

      MS Query 的 SQL 視圖,強調 WHERE 子句

  5. 在 WHERE 子句中新增含參數的條件之後,按一下[確定]以執行查詢。 Excel 會提示您為每個參數提供一個值,然後 Microsoft Query 就會顯示結果。

  6. 當您準備好要載入資料時,請關閉 Microsoft Query 視窗,以將結果傳回 Excel。 [匯入資料] 對話方塊隨即開啟。

    Excel 中的 [匯入資料] 對話方塊

  7. 若要查看您的參數,請按一下 [屬性]。 然後在 [連接屬性] 對話方塊的 [定義] 索引標籤上,按一下 [參數]。

    [連接屬性] 對話方塊

  8. [參數] 對話方塊會顯示查詢中使用的參數。 選取 [參數名稱] 下的參數,以查看或變更參數值的取得方式。 您可以變更參數提示、輸入特定值,或指定儲存格參照。

    MS Query 參數對話方塊

  9. 按一下[確定] 儲存變更並關閉 [參數] 對話方塊,然後在 [匯入資料] 對話方塊中按一下[確定],即可在 Excel 中顯示查詢結果。

現在,您的活頁簿有參數查詢。 當您執行查詢或重新整理其資料連線時,Excel 會檢查參數以完成查詢的 WHERE 子句。 如果參數提示輸入值,Excel 會顯示 [輸入參數值] 對話方塊來收集輸入,您可以輸入值,或按一下包含值的儲存格。 您也可以指定您所提供的值或參照,而如果您使用儲存格參照,您可以指定 Excel 在指定儲存格的值變更時,自動重新整理資料連線(亦即執行查詢)。

附註: 本主題假設您知道如何使用 Power Query 建立 Access 資料庫的連線。 如需詳細資訊,請參閱連接至 Access 資料庫

您可以在較多的 Power Query 案例中使用參數,而不只是篩選資料– Power Query 查詢的任何步驟都可以有參數。 例如,您可以使用參數指定來源步驟中的連接字串部分(例如檔案名)。

Power Query 參數具有名稱。 若要使用參數,您可以在步驟的公式中以名稱來引用它。 例如,假設您想要查看您維護之網頁的相關資料,而您想要依發佈日期篩選資料。 雖然您隨時都可以在查詢預覽中使用內建篩選,但使用參數來提供篩選的日期可節省時間並提供更大的彈性。 讓我們來流覽這個範例。

在空白的活頁簿中,我們會建立包含我們所要的網路流量記錄的 Access 資料庫連線,包括指示每個頁面最初發行時間的欄位。 載入至 Power Query,看起來會像這樣:

Power Query 編輯器描述載入的資料

因為我們想要依日期篩選,所以我們會變更我們所使用之資料行的資料類型,FirstPublishDate。 它是來源中的日期/時間資料,但我們並不關心當天發佈的時間,必須指定它才能 tiresome,所以我們會將它變更為 Date 資料類型。

Power Query 編輯器顯示結果

接著,我們會建立一個參數,以根據最初發佈頁面的日期來限制結果。 按一下[常用>參數] > [管理參數] 以開啟 [參數] 對話方塊。

[Power Query 參數] 對話方塊

按一下 [新增],表單會顯示一個名為 Parameter1 的新參數,不含其他資訊。

我們會變更某些參數屬性:

  • 名稱變更為FirstPubD

  • 描述變更為第一次發佈頁面的日期。

  • 將 [類型] 變更為 [日期],讓參數只接受日期值

  • 設定目前的值,讓您在未提供輸入時(我們使用1/1/2010)不會篩選出所有資料列。

提示: 名稱和描述應該提供足夠的內容,以協助使用者瞭解如何使用參數。 即使您是唯一會使用該參數的人,您也可能需要一段時間的提醒。

按一下[確定]以建立參數,並在 Power Query 編輯器中查看。

顯示參數的 Power Query 編輯器

現在,我們在 [查詢] 面板中列出了參數,我們可以選取它以在主面板中顯示,或者,您可以用滑鼠右鍵按一下它以取得更多選項。 選取參數之後,我們可以編輯主面板中的目前值,或按一下 [管理參數] 來變更其他設定。

現在,我們可以在原始查詢中使用這個參數。 按一下 [查詢] 面板中的原始查詢以顯示它。 我們想要使用參數根據第一個出版物的日期來篩選結果,所以接下來我們將選取 [ FirstPublishDate ] 資料行,按一下欄標題右邊緣的 [篩選/排序] 箭號,指向 [日期篩選],然後按一下 [之後 ...]。

Power Query 編輯器顯示日期篩選功能表

在 [篩選列] 對話方塊中,從 [篩選] 的選項清單中選取 [參數]。

[篩選資料列] 對話方塊

輸入或選取的值會以可用參數的清單取代。 只有一個剛建立的 FirstPubD。

顯示已選取參數的 [篩選列] 對話方塊

選取它,然後按一下[確定]。 Power Query 編輯器會使用新參數做為篩選來載入查詢。

Power Query 編輯器顯示篩選結果

若要測試參數,我們會將它的值變更為1/1/2018

顯示參數的 Power Query 編輯器

我們會重新整理查詢,現在只會顯示1/1/2018 之後有 FirstPublishDate 的資料列。

Power Query 編輯器顯示篩選結果

現在,我們有使用參數依日期篩選的查詢。 若要使用 FirstPublishDate 篩選結果,不需要再尋找欄位,請按一下 [篩選/排序] 箭號,然後選擇 [之後 ...]。 [篩選類型],然後輸入日期值,就可以變更 FirstPubD 並重新整理查詢的值。 此外,我們還可以重複使用新參數,例如,如果我們決定將另一組欄位從原始資料來源拉入新的工作表,但仍要包含 FirstPubDate 並使用它來篩選結果。

參數非常有用,但我們仍必須使用 Power Query 編輯器來變更參數的值。 我們希望能夠變更篩選值,而不需開啟 Power Query 編輯器。 若要這樣做,我們會在工作表上建立一個表格,並在其中載入查詢,並將新的 Power Query 連線至資料表,然後使用新查詢來篩選我們的主要查詢。

在載入查詢的工作表上,我們會在匯入的資料上方插入一些資料列。 接著,我們會建立一個含有一個列的 Excel 表格,以保留我們的參數值。

顯示參數資料表及從 Power Query 載入之資料的 Excel 活頁簿

若要使用新資料表來篩選查詢,我們需要在 Power Query 中連線到查詢。 我們先選取資料表,然後按一下 [資料] 索引標籤上的 [從資料表/範圍],即可建立與該表的連線。 新的連線會開啟,並顯示 Power Query 編輯器中的新資料表。

Power Query 編輯器中載入的 Excel 表格資料

因為資料是以 [日期/時間] 資料類型載入,所以我們需要將它變更為 [Date] 資料類型,使其符合我們的參數,因此我們按一下 [常用 >轉換] >資料類型>日期

在 Power Query 編輯器功能區的 [常用] 索引標籤上,將滑鼠移至 [轉換] 群組中的 [資料類型] 命令。

我們也會將查詢重新命名為比資料表2更有意義的內容。 為 FirstPubDate,我們會將它命名為 []。

[Power Query 編輯器] 醒目提示 [名稱] 方塊

因為我們想要傳遞值,而不是資料表本身,所以我們必須向下切入至日期值。 若要這樣做,請以滑鼠右鍵按一下預覽資料中的值,然後按一下 [向下切入]。

欄位值的 Power Query 編輯器操作功能表

預覽現在會顯示值,而不是資料表。

Power Query 編輯器顯示單一日期值

我們不需要將新查詢的資料載入至任何位置-其資料已經在您想要的工作表上。 我們只需要連線,Power Query 才能取得參數值。 所以,按一下 [檔案]>關閉 & 載入至 ... ] 以開啟 [匯入資料] 對話方塊,然後選取 [只建立連線]。

已選取 [建立連線] 選項的 [匯入資料] 對話方塊

現在,我們有一個名為「FirstPubDate」的查詢,該查詢會從工作表上的資料表中提取單一日期值,就在主要查詢載入的位置上。 現在,我們只需要使用此查詢做為篩選主要查詢的參數。 因此,我們會開啟主要查詢,並編輯使用 [FirstPublishDate] 資料行篩選資料列的步驟。 展開資料編輯列,然後選取我們先前建立的參數(FirstPubD)。 接著,我們會在FirstPubD之後輸入「a」-因為新查詢的名稱會以與參數相同的字母開頭,Power query 會將它顯示為要挑選的選項。

Power Query 編輯器資料編輯列已展開

選取它,然後按一下資料編輯列以外的地方來套用步驟。

已載入資料的 Power Query 編輯器

一切看起來都正確,所以請退出 Power Query 編輯器並儲存我們的變更。 若要測試參數,請在報表工作表上,將表格中的儲存格值變更為5/4/2019,然後重新整理連線來查看篩選的資料。

Excel 中的篩選資料

我們的新篩選是可行的! 所以我們會儲存並關閉活頁簿。 現在,使用活頁簿的任何人都可以將第一個發佈的日期指定為查詢篩選,在載入查詢的同一張工作表上。

  1. 按一下 [資料] >取得 & 轉換資料>取得資料>啟動 Power Query 編輯器

  2. 在 Power Query 編輯器中,按一下 [常用] >參數> [管理參數]。

  3. 在 [參數] 對話方塊中,按一下 [新增]。

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

    • Name (名稱)-這應該反映參數的函數,但儘量保持簡短。

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

    • 必要-選取以讓此參數需要值。

    • 類型-這會指定參數所需的資料類型。

    • 建議的值-視需要新增值清單,或指定查詢來提供輸入建議。

    • 預設值-此選項只會在建議的值設定為值清單時出現,並指定哪個清單專案是預設值。

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

  5. 按一下[確定]以建立參數。

  1. 在 Power Query 編輯器中開啟查詢。

  2. 按一下您要用來篩選資料之欄之 [標題] 右邊緣的箭號,然後從出現的功能表中選擇篩選。

  3. 在 [篩選列] 對話方塊中,按一下篩選準則右側的按鈕,然後執行下列其中一項操作:

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

    • 若要使用新參數,請按一下 [新增參數 ...],然後建立參數

  1. 在已載入您要篩選之查詢的工作表上,建立含有兩個儲存格的資料表: [頁首] 和 [值]。

  2. 按一下值,然後按一下 [資料] > 取得 &從表格/範圍轉換資料>。

  3. 在 Power Query 編輯器中,對資料表連線進行任何調整(例如,變更資料類型或名稱),然後按一下 [常用] >關閉] >關閉 & 載入>關閉 & 載入至 ...]。

  4. 在 [匯入資料] 對話方塊中,按一下 [只建立連線],選擇 [新增至資料模型],然後按一下[確定]

  5. 在 Power Query 編輯器中開啟您要篩選的查詢。

  6. 按一下您要用來篩選資料之欄之 [標題] 右邊緣的箭號,然後從出現的功能表中選擇篩選。

  7. 執行下列其中一項操作:

    • 從值的下拉式清單中選取一個值(這些值來自查詢的資料)。

    • 使用篩選準則右邊緣的按鈕來選取值。

  8. 按一下資料編輯列右邊緣的箭號,以顯示整個查詢。

  9. 每個字的篩選準則如下:

    • 所篩選之欄的名稱會顯示在方括弧中。

    • 比較運算子會緊跟在欄名後面。

    • 篩選值會立即跟在比較運算子之後,並在右括弧結束。 選取 [整個值]。

  10. 開始輸入您剛建立的表格連線名稱,然後從出現的清單中選取它。

  11. 按一下[常用] >關閉>關閉 & 載入]。

    您的查詢現在會使用您所建立之資料表中的值來篩選查詢結果。 若要使用新的值,請編輯儲存格內容,然後重新整理查詢。

另請參閱

建立下拉式清單

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

需要更多協助?

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×