适用于 Excel 的 Power BI 发布服务器Power BI publisher for Excel

使用 Microsoft Power BI Publisher for Excel,可以在 Excel 中获取最重要的数据洞察快照,如数据透视表、图表和区域,并将它们固定到 Power BI 中的仪表板。With Microsoft Power BI publisher for Excel, you can take snapshots of your most important insights in Excel, like PivotTables, charts, and ranges and pin them to dashboards in Power BI.

你可以固定什么?What can you pin? Excel 工作表中几乎所有项目。Just about anything in an Excel worksheet. 你可以从一个简单的工作表或表、数据透视表或数据透视图、图例和图像、文本中选择单元格区域。You can select a range of cells from a simple sheet or table, a PivotTable or PivotChart, illustrations and images, text.

你不能固定:不能在 Power View 工作表中固定 3D 地图或可视化效果。What you can't pin: you cannot pin 3D Maps or visualizations in Power View sheets. 你还可以固定一些元素,但是固定这些元素意义不大,如切片器或时间线筛选器。There are also some elements you can pin, but it wouldn't make much sense to, like a Slicer or Timeline filter.

当固定 Excel 中的元素时,将在 Power BI 中的新仪表板或现有的仪表板中添加新的磁贴。When you pin an element from Excel, a new tile is added to a new or existing dashboard in Power BI. 新的磁贴是快照,因此它不是动态的,但是仍可以更新它。The new tile is a snapshot, so it's not dynamic but you can still update it. 例如,如果更改已固定的数据透视表或图表,那么 Power BI 中的仪表板磁贴不会自动更新,但是仍可以使用固定管理器来更新已固定的元素。For example, if you make a change to a PivotTable or chart you've already pinned, the dashboard tile in Power BI isn't updated automatically, but you can still update your pinned elements by using Pin Manager. 你将在以下章节中了解有关固定管理器的详细信息。You'll learn more about Pin Manager in the following sections.

下载和安装Download and install

Power BI Publisher for Excel 是一个可以下载并在 Microsoft Excel 2007 和更高版本的桌面版上安装的加载项。Power BI publisher for Excel is an add-in you can download and install on desktop versions of Microsoft Excel 2007 and later.

下载 Power BI Publisher for ExcelDownload Power BI publisher for Excel

安装此发布服务器后,你会在 Excel 中看到一个新的 Power BI 功能区,可以在其中登录(或注销)Power BI、将元素固定到仪表板,以及管理已固定的元素。Once you have the publisher installed, you'll see a new Power BI ribbon in Excel, where you can sign in (or sign out) of Power BI, pin elements to dashboards, and manage elements you've already pinned.

默认情况下已启用 Power BI Publisher for Excel 外接程序,但是如果因为某些原因未在 Excel 中看见 Power BI 功能区选项卡,则需要启用它。The Power BI publisher for Excel add-in is enabled by default, but if for some reason you don't see the Power BI ribbon tab in Excel, you'll need to enable it. 单击文件 > 选项 > 加载项 > COM 加载项。选择“Microsoft Power BI Publisher for Excel”。Click File > Options > Add-ins > COM Add-ins. Select Microsoft Power BI Publisher for Excel.

将区域固定到仪表板Pin a range to a dashboard

你可以从工作表中选择任何单元格区域,然后将该区域的快照固定到 Power BI 中的现有或新的仪表板。You can select any range of cells from your worksheet, and pin a snapshot of that range to an existing or a new dashboard in Power BI. 也可以将同一个快照固定到多个仪表板。You can pin the same snapshot to multiple dashboards, too.

若要开始操作,需确保已登录到 Power BI。To begin, you need to make sure you're signed in to Power BI.

  1. 从 Excel 中的“Power BI”功能区选项卡中选择“配置文件”。Select Profile from the Power BI ribbon tab in Excel. 如果已登录到 Power BI,你将看到一个对话框,其中显示了当前的登录帐户。If you're already signed in to Power BI, you'll see a dialog that shows which account you're currently signed in with. 如果这正是你想使用的帐户,那太好了 — 请转到下一组步骤,以固定你的区域。If that's the account you want to use, great - go to the next set of steps to pin your range. 如果想要使用其他 Power BI 帐户,请选择“注销”。Select Sign out if you want to use a different Power BI account. 如果尚未登录,请转到下一步(步骤 2)。If you're not signed in, go to the next step (Step 2).

  2. 如果你尚未登录,请选择在从 Excel 中的“Power BI”功能区选项卡中选择“配置文件”时所显示的“登录”链接,接着在“连接到 Power BI”对话框中键入要使用的 Power BI 帐户的电子邮件地址,然后选择“登录”。If you're not signed in, select the Sign In link that appears when you select Profile from the Power BI ribbon tab in Excel, in the Connect to Power BI dialog type in the email address of the Power BI account you want to use, then select Sign In.

登录后,按照下列步骤将区域固定到仪表板:Once you're signed in, follow these steps to pin a range to a dashboard:

  1. 在 Excel 中,选择“Power BI”功能区选项卡即可看到“固定”功能区按钮。In Excel, select the Power BI ribbon tab to see the Pin ribbon button.
  2. 从 Excel 工作簿中选择一个区域。Select a range from your Excel workbook.
  3. 从“Power BI”功能区中单击“固定”按钮,以显示“固定到仪表板”对话框。Click the Pin button from the Power BI ribbon to show the Pin to dashboard dialog. 如果你尚未登录 Power BI,系统将提示你登录。If you're not already signed into Power BI, you'll be prompted to do so. 从“工作区”下拉列表中选择一个工作区。Select a workspace from the Workspace dropdown list. 如果想要固定到自己的仪表板,请确认选择的是我的工作区If you want to pin to your own dashboard, verify My Workspace is selected. 如果想要固定到组工作区中的仪表板,请从下拉列表中选择组。If you want to pin to a dashboard in a group workspace, select the group from the drop-down list.
  4. 选择是想固定到现有仪表板还是创建新仪表板。Choose whether you want to pin to an existing dashboard or create a new dashboard.
  5. 单击“确定”将所选内容固定到仪表板。Click Okay to pin your selection to the dashboard.
  6. 在“固定到仪表板”中,选择工作区中的现有仪表板或创建一个新的仪表板,然后单击“确定”按钮。In Pin to dashboard, select an existing dashboard in the workspace or create a new one, and then click the Ok button.

将图表固定到仪表板Pin a Chart to a dashboard

只需单击图表,然后单击“固定”图标 Just click on the chart, and then click Pin .

管理固定的元素Manage pinned elements

使用固定管理器,可以更新(刷新)Power BI 中已固定元素的关联磁贴。With Pin Manager, you can update (refresh) a pinned element's associated tile in Power BI. 你还可以对已固定到 Power BI 中的仪表板的元素取消固定。You can also remove the pin between an element you've already pinned to dashboards in Power BI.

若要更新仪表板中的磁帖,请在固定管理器中选择一个或多个元素,然后选择“更新”。To update tiles in your dashboard, in Pin Manager select one or more elements and then select Update.

若要删除 Excel 中已固定的元素和仪表板中相关联的磁贴之间的映射,请单击“删除”。To remove the mapping between a pinned element in Excel and the associated tile in a dashboard, remove Remove. 选择“删除”不会从 Excel 的工作表中删除元素,也不会从仪表板中删除相关联的磁贴。When you select Remove, you're not removing the element from your worksheet in Excel or deleting the associated tile in the dashboard. 你删除的是固定或两者之间的映射。You are removing the pin, or mapping, between them. 已删除的元素将不再显示在固定管理器中。The removed element will no longer appear in Pin Manager. 如果再次固定该元素,它将显示为新的磁贴。If you pin the element again, it will appear as a new tile.

若要从仪表板中删除固定的元素(磁贴),需要在 Power BI 中执行此操作。To remove a pinned element (a tile) from a dashboard, you'll need to do that in Power BI. 在你想要删除的磁贴中,选择“打开菜单”图标 ,然后选择“删除磁贴 ”In the tile you want to delete, select the Open menu icon and then select Delete tile .

连接到 Power BI 中的数据Connect to data in Power BI

从 2016 年 7 月发行的 Power BI Publisher for Excel 起(包括前面链接到的最新版本),你可以直接连接到 Power BI 服务中的数据,并在 Excel 中使用数据透视表和数据透视图分析该数据。Beginning with the July 2016 release of Power BI publisher for Excel (including the current release, linked to above), you can connect directly to data in the Power BI service and analyze that data in Excel using PivotTables and PivotCharts. 借助此功能,你可以轻松地将 Power BI 数据和 Excel 搭配使用,以分析对你而言最重要的数据。This features makes it easy to use Power BI data and Excel together to analyze data that's most important to you.

改进功能包括:Improvements include the following:

  • 将在每个版本中自动更新连接到 Power BI 中的数据所需的任何驱动程序 — 无需自行安装或管理这些驱动程序。Any drivers required to connect to data in Power BI are automatically updated with each release - no need to install or manage those drivers yourself.
  • 不再需要下载 .odc 文件来创建连接 — 在你选择要使用的报表或数据集后,Power BI Publisher for Excel 将自动创建连接。You no longer need to download .odc files to create the connections - Power BI publisher for Excel creates the connections automatically when you select which report or dataset you want to use.
  • 现在可以在同一工作簿中创建多个连接和数据透视表Now you can create multiple connections and PivotTables in the same workbook
  • 对错误进行了改进并且特定于 Power BI Publisher for Excel,而不是使用默认的 Excel 消息Errors are improved and specific to Power BI publisher for Excel, rather than using default Excel messages

如何连接到 Excel 中的 Power BI 数据How to connect to Power BI data in Excel

若要使用 Power BI Publisher for Excel 连接到 Power BI 数据,请执行下面这些简单的步骤:To connect to Power BI data using Power BI publisher for Excel, follow these easy steps:

  1. 确保你已登录到 Power BI。Make sure you're signed in to Power BI. 本文前面部分提供了描述如何登录(或使用其他帐户登录)的步骤。The steps describing how to sign in (or to sign in with a different account) are provided earlier in this article.
  2. 通过要使用的帐户登录到 Power BI 之后,从 Excel 中的“Power BI”功能区选项卡中选择“连接到数据”。Once you're signed in to Power BI with the account you want to use, select Connect to Data from the Power BI ribbon tab in Excel.

  3. Excel 将使用 HTTPS 连接连接到 Power BI,并显示“连接 Power BI 中的数据”对话框,你可以在其中选择要从中选择数据的工作区(下图中的 1),并选择要连接到的数据类型(“报表”或“数据集”)(2),该对话框中有一个下拉列表 (3),你可以通过下拉列表选择要连接到的可用报表或数据集。Excel connects to Power BI using an HTTPS connection and presents the Connect to data in Power BI dialog, where you can select the workspace from which you want to select your data (1, in the image below), which type of data you want to connect to, either a report or a dataset (2), and a drop down (3) that allows you to select which available report or dataset to which to connect.

  4. 在完成选择后,从“连接 Power BI 中的数据”对话框中选择“连接”,Excel 将准备一个数据透视表,并显示“数据透视表字段”窗格,你可以从该窗格中已连接的 Power BI 数据中选择字段,并创建表或图表来帮助你分析数据。When you make your choices and select Connect from the Connect to data in Power BI dialog, Excel prepares a PivotTable and displays the PivotTable Fields pane, where you can select fields from your connected Power BI data, and create tables or charts that help you analyze the data.

如果 Power BI 中没有任何数据,Excel 将进行检测并创建示例数据,以便你连接并尝试执行操作。If you don't have any data in Power BI, Excel detects that and offers to create sample data for you to connect to and try.

在此版 Power BI Publisher for Excel 中,需要注意以下几个事项:There are a few things to consider in this release of Power BI publisher for Excel:

  • 共享数据 — 已共享给你、但不会直接显示在 Power BI 中且在“连接到数据”中不可用的数据。Shared data - Data that has been shared with you, but isn't directly visible to you in Power BI, is not available in Connect to Data.
  • 本地 SSAS — 如果所选数据集来自本地 SQL Server Analysis Services (SSAS),并且 Power BI 中的数据集使用 DirectQuery 来访问数据,Power BI Publisher for Excel 将通过本地网络连接连接到该数据,而不会通过 Power BI 连接到该数据。SSAS on-premises - If the dataset you select originates from an on-premises SQL Server Analysis Services (SSAS) and the dataset in Power BI uses DirectQuery to access the data, Power BI publisher for Excel connects to that data through the on-premises network connection, and does not go through Power BI to connect to that data. 同样地,尝试连接到此类数据集的任何用户都必须连接到本地网络,并且在经过用于存储数据的 Analysis Services 实例所使用的身份验证方法的身份验证后,才能访问该数据。As such, any user trying to connect to such datasets must be connected to the on-premises network, and is authenticated for access to that data using the authentication method employed by the Analysis Services instance where the data is stored.
  • 所需的驱动程序 - Power BI Publisher for Excel 会自动安装运行此功能所需的所有驱动程序。Required drivers - Power BI publisher for Excel installs all the necessary drivers for this feature to work, and does so automatically. 在这些自动安装的驱动程序之中,Excel OLE DB 驱动程序用于 Analysis Services;如果该驱动程序被用户删除(或由于其他任何原因而被删除),到 Power BI 数据的连接将不起作用。Among those automatically installed drivers is the Excel OLE DB driver for Analysis Services; if that driver is removed by the user (or for any other reason), the connection to Power BI data will not work.
  • 数据集必须有度量值 - 数据集必须定义模型度量值,这样 Excel 才能在数据透视表中将度量值作为值来处理,并正确分析数据。Dataset must have measures - The dataset must have model measures defined in order for Excel to treat the measures as values in PivotTables, and to correctly analyze the data. 详细了解度量值Learn more about measures.
  • 支持组 - 与指定组以外的人共享的数据集不受支持,并且不能连接。Support for Groups - Datasets shared with people outside the specified group are not supported, and cannot be connected to.
  • 免费订阅与专业订阅 - 与组相关的活动对于 Power BI 免费用户不受支持,因此他们将不会其工作区中看到任何与组共享的数据集和报表。Free versus Pro subscriptions - Activities associated with groups are not supported for free users of Power BI, and thus won't see any datasets or reports shared with a group in their own workspace.
  • 共享的报表或数据集 - 不能连接与你共享的报表或数据集。Shared reports or datasets - Reports or datasets that were shared with you cannot be connected to.
  • 使用表而不是数据模型 - 通过从 Excel 仅导入表(不带数据模型)创建的数据集和报表当前不受支持,并且不能连接。Using Tables instead of Data models - Datasets and reports that are created by importing only tables from Excel (without a data model) are not supported at this time, and cannot be connected to.

在创建了引人注目的图表或其他视觉效果(例如一系列数据)后,可以轻松地将它们固定到 Power BI 中的仪表板,如本文前面部分中所述。Once you've created compelling charts or other visuals such as a range of data, you can easily pin those to a dashboard in Power BI, as described earlier in this article.

有多种方法可以将 Excel 和 Power BI 一起使用,并获取两者最大的价值。There are many ways to use Excel and Power BI together, and get the best out of both. 有关详细信息,请参阅以下文章。Take a look at the following articles for more information.