Excel

摘要

必要條件

若要連線到舊版活頁簿（例如 .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 活頁簿在在線，請使用 Web 連接器 連線到活頁簿。

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

   

從 Power Query Online 連線到 Excel 活頁簿

若要從 Power Query Online 進行連線：

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

   

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

   

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

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

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

   

建議的數據表

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

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

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

如果您選取其中一個建議的資料表，Power Query 能夠從活頁簿的版面配置判斷的每個個別資料表都會顯示在導覽器中。 例如，如果您選取 [表格 3]，則會顯示原本出現在單元格 C13 到 F16 中的數據。

疑難排解

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

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

答案有點複雜，而且與 Excel 如何使用稱為 二進位浮點表示法來儲存數位的方式有所關係。 底線是 Excel 無法以 100% 精確度表示的特定數位。 如果您打開 .xlsx 檔案並查看正在儲存的實際值，您會注意到在 .xlsx 檔案中，0.049 實際上儲存 為 0.04900000000000000002。 此數字是 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 只會顯示 Excel 中顯示的值至 Power Query，而不是基礎數字。

非預期的 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 驅動程式時，就會發生此錯誤。 只有 ACE 可以讀取以「嚴格開放 XML 試算表」格式儲存的工作簿。 不過，由於這類活頁簿使用與一般 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 檔案。
• Excel.Workbook useHeaders 選項會使用目前的文化特性，將數位和日期轉換成文字，因此在設定不同操作系統文化特性的環境中執行時的行為會有所不同。 建議您改用 Table.PromoteHeaders 。