Analyze in Excel 故障排除Troubleshooting Analyze in Excel

有时,在使用 Analyze in 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. 此页提供针对 Analyze in 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

要使用 Analyze in 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 服务右上角的向下箭头,然后选择 Analyze in 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 并确认你可以查看和访问正尝试在 Excel 中访问的 Power BI 中数据集。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 多维数据集模型”错误,则表明你正尝试访问的数据集中不包含数据模型,因此不能在 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

如果你收到令牌过期错误,表示你最近未在计算机上使用 Analyze in 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