共用方式為

Excel

  • 發行項

摘要

項目 說明
發行狀態 正式發行
產品 Excel
Power BI （語意模型）
Power BI （數據流）
網狀架構 （資料流 Gen2）
Power Apps （數據流）
Dynamics 365 Customer Insights
Analysis Services
支援的驗證類型 匿名 （線上）
基本 （線上）
組織帳戶（線上）
函式參考檔 Excel.Workbook
Excel.CurrentWorkbook

注意

某些功能可能會存在於一個產品中，但由於部署排程和主機特定功能，而不存在其他功能。

必要條件

若要連線到舊版活頁簿（例如 .xls 或 .xlsb），需要Access 資料庫引擎OLEDB （或 ACE） 提供者。 若要安裝此提供者，請移至 下載頁面 並安裝相關的 （32 位或 64 位） 版本。 如果您沒有安裝，連線到舊版活頁簿時，會看到下列錯誤：

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE 無法安裝在雲端服務環境中。 因此，如果您在雲端主機中看到此錯誤（例如 Power Query Online），則必須使用已安裝 ACE 的閘道來連線到舊版 Excel 檔案。

支援的功能

  • Import

從 Power Query Desktop 連線 至 Excel 活頁簿

若要從 Power Query Desktop 進行連線：

  1. 選取 [取得數據體驗] 中的 [Excel 活頁簿 ]。 Power Query Desktop 中的數據體驗會因應用程式而異。 如需 Power Query Desktop 取得應用程式數據體驗的詳細資訊，請移至 取得數據的位置。

  2. 瀏覽並選取您要載入的 Excel 活頁簿。 然後選取 [開啟]。

    從 檔案總管 選取 Excel 活頁簿。

    如果 Excel 活頁簿在在線，請使用 Web 連接器 連線到活頁簿。

  3. 在 [導覽器] 中，選取您想要的活頁簿信息，然後選取 [載入] 以載入資料或 [轉換數據] 繼續轉換 Power Query 編輯器 中的數據。

    匯入 Power Query Desktop Navigator 的 Excel 活頁簿。

從 Power Query Online 連線 至 Excel 活頁簿

若要從 Power Query Online 進行連線：

  1. 選取 [取得數據體驗] 中的 [ Excel 活頁簿] 選項。 不同的應用程式有不同的方式可取得Power Query Online取得資料體驗。 如需如何取得 Power Query Online 取得應用程式數據體驗的詳細資訊，請移至 取得數據的位置。

    [取得數據] 視窗的螢幕快照，其中強調 Excel 活頁簿。

  2. 在出現的Excel對話框中，提供Excel活頁簿的路徑。

    用來存取 Excel 活頁簿之連線資訊的螢幕快照。

  3. 如有必要，請選取內部部署數據閘道以存取 Excel 活頁簿。

  4. 如果這是您第一次存取此 Excel 活頁簿，請選取驗證種類並登入您的帳戶（如有需要）。

  5. 在 [導覽器] 中，選取您想要的活頁簿信息，然後轉換數據以繼續轉換 Power Query 編輯器 中的數據。

    匯入 Power Query Online Navigator 之 Excel 活頁簿的螢幕快照。

建議的數據表

如果您連線到未特別包含單一數據表的 Excel 活頁簿，Power Query 導覽器會嘗試建立您可以選擇的建議數據表清單。 例如，請考慮下列包含 A1 到 C5 數據的活頁簿範例、D8 到 E10 的數據，以及從 C13 到 F16 的更多數據。

Excel 活頁簿的螢幕快照，其中包含三組數據。

當您連線到 Power Query 中的數據時，Power Query 導覽器會建立兩個清單。 第一個清單包含整個活頁簿工作表，而第二個清單包含三個建議的數據表。

如果您在導覽器中選取整個工作表，則活頁簿會顯示在 Excel 中，並填滿 Null 的所有空白儲存格。

導覽器的螢幕快照，其中單一工作表在空白儲存格中以 Null 顯示。 如果您選取其中一個建議的數據表，Power Query 能夠從活頁簿的配置判斷的每個個別數據表都會顯示在導覽器中。 例如，如果您選取 [表格 3]，則會顯示原本出現在單元格 C13 到 F16 中的數據。

導覽器的螢幕快照，其中已選取 [建議的數據表] 底下的數據表 3，並顯示表格 3 的內容。

注意

如果工作表變更足夠，數據表可能無法正確重新整理。 您可以再次匯入數據並選取新的建議數據表，以修正重新整理。

疑難排解

數值有效位數（或「為什麼我的數位有所變更？」

匯入 Excel 數據時，您可能會注意到匯入 Power Query 時，某些數位值似乎稍有變更。 例如，如果您在 Excel 中選取包含 0.049 的儲存格，此數位會顯示在公式列中為 0.049。 但是，如果您將相同的單元格匯入 Power Query 並加以選取，預覽詳細數據會顯示為 0.04900000000000002（即使在預覽數據表中，其格式為 0.049）。 這其中發生了什麼狀況？

答案有點複雜，而且與 Excel 如何使用稱為 二進位浮點表示法來儲存數位的方式有所關係。 底線是 Excel 無法以 100% 精確度表示的特定數位。 如果您破解開啟.xlsx檔案並查看儲存的實際值，您會看到.xlsx檔案中，0.049 實際上儲存為 0.0490000000000000002。 這是 Power Query 從.xlsx讀取的值，因此當您在 Power Query 中選取單元格時出現的值。 （如需 Power Query 中數值精確度的詳細資訊，請移至 的 [十進制數] 和 [固定十進位數] 區段 Power Query 中的數據類型。

連線 至在線 Excel 活頁簿

如果您想要連線到 Sharepoint 中裝載的 Excel 檔，您可以透過 Power BI Desktop、Excel 和數據流中的 Web 連接器，以及數據流中的 Excel 連接器來執行此動作。 若要取得檔案的連結：

  1. 在 Excel Desktop 中開啟檔。
  2. 開啟 [ 檔案] 功能表，選取 [ 資訊] 索引標籤，然後選取 [ 複製路徑]。
  3. 將位址 複製到 [檔案路徑或 URL] 欄位，並從地址結尾移除 ？web=1 。

舊版 ACE 連接器

Power Query 會使用 Access 資料庫引擎 （或 ACE） OLEDB 提供者讀取舊版活頁簿（例如 .xls 或 .xlsb）。 因此，匯入匯入 OpenXML 活頁簿時，您可能會遇到非預期的行為（例如.xlsx）。 以下是一些常見的範例。

非預期的值格式設定

由於 ACE，舊版 Excel 活頁簿中的值可能會比您預期的精確度或逼真度低。 例如，假設您的 Excel 檔案包含數位 1024.231，您已將它格式化為 “1,024.23”。 匯入 Power Query 時，此值會以文字值 “1,024.23” 表示，而不是做為基礎的完整精確度數位 （1024.231）。 這是因為，在此情況下，ACE 不會將基礎值呈現至 Power Query，而只會顯示 Excel 中的值。

非預期的 Null 值

當 ACE 載入工作表時，它會查看前八個數據列，以判斷資料行的數據類型。 如果前八個數據列不代表較新的數據列，ACE 可能會將不正確的類型套用至該數據行，並針對不符合該類型的任何值傳回 Null。 例如，如果數據行包含前八個數據列的數位（例如 1000、1001 等等），但在稍後的數據列中有非數值數據（例如 “100Y” 和 “100Z”），ACE 會得出結論，數據行包含數位，而且任何非數值都傳回為 null。

不一致的值格式設定

在某些情況下，ACE 會在重新整理時傳回完全不同的結果。 使用格式化一節中所述的範例，您可能會突然看到值 1024.231，而不是 “1,024.23”。 在將舊版活頁簿匯入 Power Query 時，在 Excel 中開啟此差異可能是造成。 若要解決此問題，請關閉活頁簿。

遺漏或不完整的 Excel 數據

有時候 Power Query 無法從 Excel 工作表擷取所有數據。 此失敗通常是因為工作表的 維度 不正確（例如，當實際數據佔用三個以上的數據行或 200 個數據列時，具有的維度 A1:C200 ）。

如何診斷不正確的維度

若要檢視工作表的維度：

  1. 使用.zip擴展名重新命名 xlsx 檔案。
  2. 在 檔案總管 中開啟檔案。
  3. 流覽至 xl\worksheets。
  4. 將有問題的工作表的 xml 檔案（例如，Sheet1.xml）從 zip 檔案複製到另一個位置。
  5. 檢查檔案的前幾行。 如果檔案夠小，請在文本編輯器中開啟它。 如果檔案太大而無法以文本編輯器開啟，請從命令提示字元執行下列命令： Sheet1.xml。
  6. 尋找標籤 <dimension .../> （例如 ， <dimension ref="A1:C200" />）。

如果您的檔案具有指向單一單元格的維度屬性（例如 <dimension ref="A1" />），Power Query 會使用此屬性來尋找工作表上數據的起始數據列和數據行。

不過，如果您的檔案具有指向多個單元格的維度屬性（例如 <dimension ref="A1:AJ45000"/>），Power Query 會使用此範圍來尋找起始數據列和數據 行，以及結束數據列和數據行。 如果此範圍未包含工作表上的所有數據，則不會載入部分數據。

如何修正不正確的維度

您可以執行下列其中一個動作來修正維度不正確所造成的問題：

  • 在 Excel 中開啟並重新儲存檔。 此動作會以正確的值覆寫儲存在檔案中的不正確維度。

  • 確定產生 Excel 檔案的工具已修正，以正確輸出維度。

  • 更新 M 查詢以忽略不正確的維度。 自 2020 年 12 月版本的 Power Query 起， Excel.Workbook 現在支持選項 InferSheetDimensions 。 若為 true，此選項會導致函式忽略活頁簿中儲存的維度，而是藉由檢查數據來判斷它們。

    以下是如何提供此選項的範例：

    Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

載入 Excel 資料時效能緩慢或變慢

Excel 數據的載入速度緩慢也可能是因為維度不正確所造成。 不過，在此情況下，速度緩慢是由維度比需要大得多，而不是太小所造成。 超大型維度會導致 Power Query 從活頁簿讀取比實際所需的數據量大得多。

若要修正此問題，您可以參閱 尋找並重設工作表 上的最後一個儲存格，以取得詳細指示。

從 SharePoint 載入資料時效能不佳

從電腦上的 Excel 或 SharePoint 擷取數據時，請考慮所涉及的數據量，以及活頁簿的複雜性。

從 SharePoint 擷取非常大的檔案時，您會注意到效能降低。 不過，這隻是問題的一部分。 如果您在從 SharePoint 擷取的 Excel 檔案中具有重要的商業規則，當您重新整理數據時，此商業規則可能必須執行，這可能會導致複雜的計算。 請考慮匯總和預先計算數據，或將更多商業規則從 Excel 層移至 Power Query 層。

使用 Excel 連接器匯入 CSV 檔案時發生錯誤

雖然 CSV 檔案可以在 Excel 中開啟，但它們不是 Excel 檔案。 請改用 Text/CSV 連接器 。

匯入「嚴格開啟 XML 電子錶格」活頁簿時發生錯誤

匯入以 Excel 的「嚴格 Open XML 電子表格」格式儲存的活頁簿時，您可能會看到下列錯誤：

DataFormat.Error: The specified package is invalid. The main part is missing.

當主機電腦上未安裝 ACE 驅動程式時，就會發生此錯誤。 儲存在「嚴格開啟 XML 電子錶格」格式的活頁簿只能由 ACE 讀取。 不過，由於這類活頁簿使用與一般 Open XML 活頁簿相同的擴展名（.xlsx），因此我們無法使用擴展名來顯示一般 the Access Database Engine OLEDB provider may be required to read this type of file 錯誤訊息。

若要解決錯誤，請安裝 ACE 驅動程式。 如果錯誤發生在雲端服務中，您必須使用在已安裝 ACE 驅動程式的電腦上執行的閘道。

「檔案包含損毀的數據」錯誤

匯入特定 Excel 活頁簿時，您可能會看到下列錯誤。

DataFormat.Error: File contains corrupted data.

此錯誤通常表示檔格式有問題。

不過，有時候當檔案似乎是 Open XML 檔案（例如 .xlsx），但實際需要 ACE 驅動程式來處理檔案時，就會發生此錯誤。 如需如何處理需要 ACE 驅動程式之檔案的詳細資訊，請移至 舊版 ACE 連接器 一節。

已知問題與限制

  • Power Query Online 無法存取加密的 Excel 檔案。 由於以「公用」或「非商務」以外的敏感度類型標示的 Excel 檔案已加密，因此無法透過 Power Query Online 存取。
  • Power Query Online 不支援受密碼保護的 Excel 檔案。