[使用 EXCEL 分析] 的疑難排解Troubleshooting Analyze in Excel

有時候使用 [使用 EXCEL 分析] 可能會取得非預期的結果,或功能無法如您預期般運作。There may be times when using Analyze in Excel that you get an unexpected result, or the feature doesn't work as you expected. 此頁面可提供使用 [使用 EXCEL 分析] 時的常見問題解決方案。This page provides solutions for common issues when using Analyze in Excel.

注意

沒有專門用來描述及啟用使用 EXCEL 分析的個別頁面。There's a separate page dedicated to describing and enabling Analyze in Excel.

如果您遇到下面沒有列出的案例且發生問題,您可以在社群網站上要求進一步協助,您也可以建立支援票證If you encounter a scenario that is not listed below, and it is causing you issues, you can ask for further assistance on the community site, or you can create a support ticket.

本文包含下列疑難排解章節︰This article contains the following troubleshooting sections:

  • 更新 OLE DB 提供者的 Excel 文件庫Update Excel libraries for the OLE DB provider
  • 判斷是否需要更新您的 Excel 文件庫Determining whether you need to update your Excel libraries
  • 「無法建立連線」錯誤Connection cannot be made error
  • 「禁止」錯誤Forbidden error
  • 沒有資料模型No data models
  • 「權杖過期」錯誤Token expired error
  • 無法存取內部部署 Analysis servicesUnable to access on-premises Analysis services
  • 無法將任何項目拖曳到樞紐分析表值區域 (沒有任何量值)Can't drag anything to the PivotTable Values area (no measures)

更新 OLE DB 提供者的 Excel 文件庫Update Excel libraries for the OLE DB provider

若要使用 [使用 EXCEL 分析],您的電腦必須安裝目前的 AS OLE DB 提供者。To use Analyze in Excel, your computer must have a current AS OLE DB provider installed. 社群文章是確認 OLE DB 提供者安裝或下載最新版本的絕佳來源。This community post is a great source to verify your installation of the OLE DB provider, or to download a recent version.

Excel 文件庫必須在位元層級方面符合您的 Windows 版本。The Excel libraries need to match your version of Windows in terms of its bit-level. 如果您已安裝 64 位元 Windows,則必須安裝 64 位元 OLE DB 提供者。If you have 64-bit Windows installed, you need to install the 64-bit OLE DB provider.

若要下載最新的 Excel 文件庫,請瀏覽 Power BI,然後選取 Power BI 服務右上角的向下鍵,然後選取 [在 Excel 更新中分析]。To download the latest Excel libraries, visit Power BI and select the down arrow in the upper right corner of the Power BI service, then select Analyze in Excel updates.

在出現的對話方塊中,選取 [下載 (預覽)]。In the dialog that appears, select Download (preview).

判斷是否需要更新您的 Excel 文件庫Determining whether you need to update your Excel libraries

您可以從上一節中的連結下載最新版本的 Excel OLE DB 提供者文件庫。You can download the most recent version of the Excel OLE DB provider libraries from the links in the previous section. 下載適當的 OLD DB 提供者文件庫並開始安裝後,系統會針對目前安裝的版本執行檢查。Once you download the appropriate OLD DB provider library and begin installation, checks are performed against your current installed version.

如果 Excel OLE DB 提供者用戶端文件庫處於最新狀態,則會看到下列對話方塊︰If your Excel OLE DB provider client libraries are up to date, you'll be presented with a dialog that looks like the following:

C:\Users\davidi\Desktop\powerbi-content-pr\articles\media\powerbi-desktop-troubleshooting-analyze-in-excelC:\Users\davidi\Desktop\powerbi-content-pr\articles\media\powerbi-desktop-troubleshooting-analyze-in-excel

或者,如果您要安裝的新版本比電腦上的版本更新,則會出現下列對話方塊︰Alternatively, if the new version you are installing is newer than the version on your computer, the following dialog appears:

如果看到提示您升級的對話方塊,您應該繼續進行安裝,以取得在電腦中安裝之 OLE DB 提供者的最新版本。If you see the dialog prompting you to upgrade, you should continue with the installation to get the most recent version of the OLE DB provider installed in your computer.

「無法建立連線」錯誤Connection cannot be made error

「無法建立連線」錯誤的主要原因是電腦的 OLE DB 提供者用戶端文件庫並非最新版本。The primary cause for a connection cannot be made error is that your computer's OLE DB provider client libraries are not current. 如需有關如何判斷正確更新和下載連結的資訊,請參閱本文上述的更新 OLE DB 提供者的 Excel 文件庫For information about how to determine the correct update, and for download links, see Update Excel libraries for the OLE DB provider earlier in this article.

「禁止」錯誤Forbidden error

某些使用者擁有多個 Power BI 帳戶,且在 Excel 嘗試使用現有認證連線至 Power BI 時,其可能會使用沒有要存取之資料集或報表存取權的認證。Some users have more than one Power BI account, and when Excel attempts to connect to Power BI using existing credentials, it may use credentials that do not have access to the dataset or report you want to access.

發生這種情況時,您可能會收到標題禁止的錯誤,這表示您可能透過沒有資料集權限的認證登入 Power BI。When this occurs, you may receive an error titled Forbidden, which means you may be signed into Power BI with credentials that do not have permissions to the dataset. 發生禁止錯誤後,系統提示您輸入認證時,請使用具有要嘗試使用之資料集存取權限的認證。After encountering the forbidden error, when prompted to enter your credentials, use the credentials that have permission to access the dataset you're trying to use.

如果您仍然遇到錯誤,透過具有權限的帳戶登入 Power BI,並確認您可以在 Power BI 中檢視並存取您嘗試在 Excel 中存取的資料集。If you still run into errors, log into Power BI with the account that has permission, and verify that you can view and access the dataset in Power BI that you're attempting to access in Excel.

沒有資料模型No data models

如果您遇到表示找不到 OLAP Cube 模型的錯誤,則您正在嘗試存取的資料集沒有資料模型,因此無法於 Excel 中進行分析。If you encounter an error that states Can't find OLAP cube model, then the dataset you're trying to access has no data model, and therefore cannot be analyzed in Excel.

「權杖過期」錯誤Token expired error

如果您收到權杖過期錯誤,其表示您最近未在您使用中的電腦上使用 [使用 EXCEL 分析] 功能。If you receive a token expired error, it means you haven't recently used the Analyze in Excel feature on the computer you're using. 只需重新輸入您的認證或重新開啟檔案,該錯誤應該便會消失。Simply re-enter your credentials, or reopen the file, and the error should go away.

無法存取內部部署 Analysis servicesUnable to access on-premises Analysis Services

如果您正在嘗試存取已連接至內部部署 Analysis Services 資料的資料集,您可能會收到錯誤訊息。If you're trying to access a dataset that has connections to on-premises Analysis Services data, you may receive an error message. 使用 Excel 分析支援使用連接字串連接至內部部署 Analysis Services 上的資料集和報表,只要您的電腦與 Analysis Services 伺服器位於相同的網域,且您的帳戶可存取該 Analysis Services 伺服器即可。Analyze in Excel does support connecting to datasets and reports on on-premises Analysis Services with a connection string, as long as your computer is on the same domain as the Analysis Services server, and your account has access to that Analysis Services server.

無法將任何項目拖曳到樞紐分析表值區域 (沒有任何量值)Can't drag anything to the PivotTable Values area (no measures)

當 [使用 Excel 分析] 連接到外部 OLAP 模型 (這是 Excel 連接到 Power BI 的方式) 時,樞紐分析表需要在外部模型中定義量值,因為所有計算都在伺服器上執行。When Analyze in Excel connects to an external OLAP model (which is how Excel connects to Power BI), the PivotTable requires measures to be defined in the external model, since all calculations are performed on the server. 這不同於您使用本機資料來源時 (例如 Excel 中的資料表,或是您在使用 Power BI DesktopPower BI 服務中的資料集時),在此情況下表格式模型可以在本機使用,且您可以使用隱含量值,這是動態產生的量值,並不會儲存在資料模型中。This is different than when you work with a local data source (such as tables in Excel, or when you're working with datasets in Power BI Desktop or the Power BI service), in which case the tabular model is available locally, and you can use implicit measures, which are measures that are generated dynamically and are not stored in the data model. 在這些情況下,在 Excel 中的行為與 Power BI DesktopPower BI 服務中的行為不同:資料中可能有資料行在 Power BI 中視為量值,但無法在 Excel 中作為值 (量值) 來使用。In these cases, the behavior in Excel is different from the behavior in Power BI Desktop or the Power BI service: there may be columns in the data that can be treated as measures in Power BI, but can't be used as values (measures) in Excel.

若要解決此問題,您有幾個選項︰To address this issue, you have a few options:

  1. Power BI Desktop建立資料模型的量值,然後將資料模型發行到 Power BI 服務 並從 Excel 存取該資料集。Create measures in your data model in Power BI Desktop, then publish the data model to the Power BI service and access that published dataset from Excel.
  2. 從 Excel PowerPivot 建立資料模型的量值Create measures in your data model from Excel PowerPivot.
  3. 如果您從只有資料表 (且沒有任何資料模型) 的 Excel 活頁簿匯入資料,那麼您可以將資料表加入資料模型,然後依照上述選項 2 中的步驟,建立資料模型中的量值。If you imported data from an Excel workbook that had only tables (and no data model), then you can add the tables to the data model, then follow the steps in option 2, directly above, to create measures in your data model.

一旦您的量值定義在 Power BI 服務的模型中,您就能夠在 Excel 樞紐分析表的 [值] 區域中使用它們。Once your measures are defined in the model in the Power BI service, you'll be able to use them in the Values area in Excel PivotTables.

後續步驟Next steps

使用 Excel 分析Analyze in Excel

教學課程:在 Power BI Desktop 中建立自己的量值Tutorial: Create your own measures in Power BI Desktop

PowerPivot 中的量值Measures in PowerPivot

在 PowerPivot 中建立量值Create a Measure in PowerPivot

使用連結的資料表將工作表資料新增至資料模型Add worksheet data to a Data Model using a linked table

Excel 中 OLAP 和非 OLAP 樞紐分析表之間的差異Differences between OLAP and non-OLAP PivotTables in Excel