關於 Excel 中的 Power Query

使用 Power Query (稱為取得 & 轉換資料在先前的 Excel 版本) 中,您可以匯入或連結至外部資料,然後以符合您需求的方式來圖形化該資料,例如移除欄、變更資料類型或合併資料表。 接著,您可以將查詢載入至 Excel 以建立圖表和報表。 您可以定期重新更新資料,使其為最新狀態。

使用 Power Query 有四個階段。

Power Query 常見步驟

  1. 連接    在雲端、服務或本地建立資料連線

  2. 變換    圖形資料符合您的需求,而原始來源保持不變

  3. 結合    整合來自多個來源的資料,以取得資料的唯一視圖

  4. 負荷   完成查詢並載入至工作表或資料模型,並定期重新進行重新檢查。

讓我們深入瞭解每個階段。 最後一節包含 Excel 中的 Power Query 歷程記錄。 有關所有 Power Query 說明主題的摘要,請參閱Power Query for Excel 説明。

您可以使用 Power Query 將單一資料來源 ,例如 Excel 活頁簿,或跨雲端的多個資料庫、摘要或服務進行導入。 資料來源包含目前活頁簿中來自 Web、檔案、資料庫、Azure 或   甚至 Excel 資料表的資料。 使用 Power Query,您可以使用自己獨特的轉換和組合,將所有這些資料來源合併在一起,以發掘您原本不會看到的深入見解。 

一旦輸入之後,您可以重新將資料重新進行,以從外部資料源新增、變更和刪除。 詳細資訊,請參閱在 Excel 中重新建立外部資料連接。  

在使用者介面中輸入命令

轉換資料表示以某種方式修改資料,以滿足資料分析需求。 例如,您可以移除欄、變更資料類型或篩選列。 這些作業都是資料轉換。 將轉換套用至一 (,) 合併至一或多個資料集的此程式也稱為轉換資料。

用這種方式思考。 一個花盆會以黏土形式開始,將其中一個圖案轉換成實用且美觀的圖案。 資料相同。 它需要轉換成適合您需求的表格,並啟用具吸引力的報告和儀表板。

Power Query 使用稱為 Power Query 編輯器的專用視窗來簡化和顯示資料轉換。 您可以從取得 & 轉換資料群組中的取得資料命令選取啟動查詢編輯器來開啟Power Query 編輯器,但當您連接到資料來源、建立新查詢或載入查詢時,也會開啟該編輯器。

查詢設定使用者介面

Power Query 編輯器會記錄並標記您用於資料的每個轉換或步驟,以追蹤您處理資料的所有專案。 無論轉換是資料連線、資料行移除、合併或資料類型變更,都可以在查詢設定窗格的 APPLIED STEPS區段查看及修改每個轉換。

您可以從使用者介面進行許多轉換。 每個轉換會記錄為背景中的步驟。 您甚至可以使用進一步編輯器中的 Power Query M 語言修改及撰寫自己的步驟。

所有您適用于資料連線的轉換,共同構成查詢,這是原始資料與資料來源 (不變) 的新表示。 當您重新更新查詢時,每個步驟都會自動執行。 查詢會取代在 Excel 中手動連接及圖形資料。

您可以附加或合併 Excel 活頁簿中的多個查詢。 追加和合併作業會針對任何具有表格式圖形的查詢執行,而且與資料的來源無關。

附加   追加作業會建立一個新查詢,其中包含第一個查詢的所有列,後面接著第二個查詢的所有資料列。 您可以執行兩種類型的追加作業:

  • 中間追加    為每個追加作業建立新查詢。

  • 內嵌追加    將資料附加到現有的查詢,直到您到達最終結果。

附加查詢概念

合併    合併作業會從兩個現有的查詢建立新查詢。 此查詢包含主資料表的所有資料行,其中一欄可做為相關資料表的流覽連結。 相關資料表包含與主資料表中一般欄值之每一列相符的所有資料列。 此外,您也可以從關聯資料表展開或新增資料行至主資料表。

合併查詢概念

將查詢載入至您的活頁簿的方法有兩種:

  • 在 Power Query 編輯器中,您可以使用位於首頁索引鍵的關閉群組中的關閉和載入命令。

  • 從 [Excel 活頁簿查詢 (選取 [查詢&連接) ,您可以以滑鼠右鍵按一下查詢,然後選取 [載入至

您也可以使用 [查詢選項 (選取檔案>選項和設定>查詢選項) 來微調載入選項,以選取您想要如何查看資料,以及要在何處載入資料,無論是位於工作表或資料模型(,這是位於活頁簿) 中多個資料表的關係資料來源。 

取得資料&轉換 (現在稱為 Power Query) ,與 Excel 的整合,經過數年來的一些變更。 

Windows 版 Excel 2010 和 2013

在 Windows 的 Excel 2010 中,我們第一次推出 Power Query,而且可從此處下載免費附加元件:下載Power Query 附加元件。 啟用後,Power Query 功能可從功能區的 Power Query 索引鍵中提供。

Excel 2013 Power Query 功能區

Microsoft 365

我們已更新 Power Query,成為 Excel 中用於輸入及清理資料的主要體驗。 您可以存取 Excel 功能區中資料索引&資料轉換群組中的 Power Query 資料匯出精靈和工具。

Excel 2016 Power Query 功能區

此體驗包括增強的資料輸入功能、重新排列資料索引鍵上的命令、新的查詢&連接側邊窗格,以及持續以強大的方式排序、變更資料類型、分割欄、匯總資料等方式,以強大的方式塑造資料。

這項新體驗也會取代在取得外部資料群組中的資料命令下的舊版資料導入精靈。 不過,仍然可以從[Excel選項」 對話方塊 (選取 [檔案>選項>資料>顯示舊版資料) 。

Windows 版 Excel 2016 和 2019

我們新增了與 Microsoft 365 & Power Query 技術相同的取得轉換資料體驗。

Mac 版 Microsoft 365 Excel

在 2019 年,我們開始了在 Mac Excel 中支援 Power Query 的歷程。 自此之後,我們新增了從 TXT、CSV、XLSX、JSON 和 XML 檔案重新更新 Power Query 查詢的能力。 我們也新增了從 SQL Server 和資料表重新&目前活頁簿範圍的資料。

我們在 2019 年 10 月新增了重新更新現有 Power Query 查詢,以及使用 VBA 建立和編輯新查詢的能力。

在 2021 年 1 月,我們新增了 OData 和 SharePoint 來源的 Power Query 查詢重新更新支援。

詳細資訊,請參閱在Mac Excel 中使用 Power Query。

注意事項    Mac 版 Excel 2016 和 Excel 2019 不支援 Power Query。

資料目錄已棄用

您可以使用資料目錄來查看您的共用查詢,然後選取這些查詢以載入、編輯或以其他方式用於目前的活頁簿。 此功能已逐漸被棄用:

  • 我們在 2018 年 8 月 1 日停止將新客戶加入資料目錄。

  • 在 2018 年 12 月 3 日,使用者無法共用資料目錄中的新查詢或更新查詢。

  • 2019 年 3 月 4 日,資料目錄停止工作。 在此日期之後,我們建議您下載您的共用查詢,以便您可以使用來自資料目錄查詢工作窗格的開啟選項,在資料目錄之外繼續使用。

Power Query 附加元件已棄用

在 2019 年夏季初,我們正式將 Windows 版 Excel 2010 和 2013 所需的 Power Query 附加元件取消。 為了禮貌,您仍可使用該附加元件,但日後可能會變更。

Facebook 資料連線器已停用

Excel 中的 Facebook 資料于 2020 年 4 月停止使用。 該日期之前建立的任何 Facebook 連結都無法再使用。 我們建議您儘快修改或移除任何使用 Facebook 連接器的現有 Power Query 查詢,以避免意外的結果。

需要更多協助?

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

這項資訊有幫助嗎?

×