从 Excel 将磁贴固定到 Power BI 仪表板Pin a tile to a Power BI dashboard from Excel

若要从 Excel 工作簿固定磁贴,请将该工作簿连接到 Power BI 服务 (app.powerbi.com)。Before you can pin a tile from your Excel workbook, you'll connect that workbook to Power BI service (app.powerbi.com). 实际上,连接工作簿会将该工作簿链接的只读版本引入 Power BI 服务,并允许用户将区域固定到仪表板。Connecting a workbook essentially brings a linked read-only version of that workbook into Power BI service and allows you to pin ranges to dahboards. 甚至可以将整个工作表固定到仪表板。You can even pin an entire worksheet to a dashboard.
如果已与你共享工作簿,你将能够查看由所有者固定的磁贴,但无法自行创建任何仪表板磁贴。If a workbook has been shared with you, you'll have the ability to view the tiles pinned by the owner, but not create any dashboard tiles yourself.

有关 Excel 和 Power BI 如何协同工作的详细信息,请参阅从 Excel 工作簿文件获取数据For in-depth information about how Excel and Power BI work together, see Get data from Excel workbook files.

视频将演示几个从 Excel 工作簿导入数据并连接至此的方法。Watch Will demonstrate several ways to import data from, and connect to, Excel workbooks.

从 OneDrive for Business 将 Excel 工作簿连接到 Power BIConnect your Excel workbook from OneDrive for Business to Power BI

当你选择“连接”时,你的工作簿将显示在 Power BI 中,就像在 Excel Online 中那样。When you choose Connect, your workbook will appear in Power BI just like it would in Excel Online. 但与 Excel Online 不同,你会有一些出色的功能,可帮助你将各元素从你的工作表固定仪表板中。But, unlike Excel Online, you’ll have some great features to help you pin elements from your worksheets right to your dashboards.

不能在 Power BI 中编辑你的工作簿。You can’t edit your workbook in Power BI. 但如果需要进行一些更改,可以从工作区的“工作簿”选项卡中选择“铅笔”图标,然后选择是在 Excel Online 中编辑工作簿,还是在计算机上的 Excel 中打开工作簿。But if you need to make some changes, you can select the pencil icon from the Workbooks tab of your workspace, and then choose to edit your workbook in Excel Online or open it in Excel on your computer. 你所作的任何更改都会保存到 OneDrive 上的工作簿中。Any changes you make are saved to the workbook on OneDrive.

  1. 将你的工作簿上载到你的 OneDrive for Business。Upload your workbook to your OneDrive for Business.
  2. 在 Power BI 中,连接到该工作簿,方法是:选择“获取数据 > 文件 > OneDrive - Business”并导航到保存 Excel 文件的位置。From Power BI, connect to that workbook by selecting Get Data > Files > OneDrive - Business and nagivating to the location where you saved the Excel file. 选择该文件并选择“连接 > 连接”。Select the file and choose Connect > Connect.

  3. 在 Power BI 中,该工作簿添加到工作区的“工作簿”选项卡。In Power BI, the workbook is added to the Workbooks tab of your workspace. 图标指示这是 Excel 工作簿,黄色星号指示这是新工作簿。The icon indicates this is an Excel workbook and a yellow asterisk indicates it's new.

  1. 通过选择工作簿名称,在 Power BI 中打开工作簿。Open the workbook in Power BI by selecting the workbook name.

    对 Power BI 中工作簿所做的更改不会被保存,并且不会影响 OneDrive for Business 上的原始工作簿。Changes you make to the workbook in Power BI are not saved and do not affect the original workbook on OneDrive for Business. 如果在 Power BI 中排序、筛选或更改值,则无法保存或固定这些更改。If you sort, filter, or change values in Power BI, those changes cannot be saved or pinned. 如需进行更改(将被保存),请选择右上角的“编辑”将其打开,以便在 Excel Online 或 Excel 中进行编辑。If you need to make changes that will be saved, select Edit from the upper-right corner to open it for editing in Excel Online or Excel. 以这种方式进行的更改可能需要几分钟的时间来更新仪表板上的磁贴。Changes made this way may take a few minutes to update the tiles on the dashboards.

将单元格的区域固定到仪表板Pin a range of cells to a dashboard

添加新仪表板磁贴的一种方法是从 Power BI 中的 Excel 工作簿内部进行添加。One way to add a new dashboard tile is from within an Excel workbook in Power BI. 可从已保存至你的 OneDrive for Business 或另一个组共享的文档库中的 Excel 工作簿中固定范围。Ranges can be pinned from Excel workbooks that have been saved in your OneDrive for Business or another group-shared document library. 范围可包含数据、图表、表、数据透视表、数据透视图和其他 Excel 部分。The ranges can contain data, charts, tables, PivotTables, PivotCharts, and other Excel parts.

  1. 突出显示你想要固定到仪表板的单元格。Highlight the cells that you'd like to pin to a dashboard.

  2. 选择“大头针”图标 Select the pin icon.
  3. 将磁贴固定到现有仪表板或新仪表板。Pin the tile to an existing dashboard or to a new dashboard.

    • 现有仪表板:从下拉列表中选择仪表板的名称。Existing dashboard: select the name of the dashboard from the dropdown.
    • 新仪表板:键入新仪表板的名称。New dashboard: type the name of the new dashboard.

  4. 选择“固定”。Select Pin. 一条成功消息(右上角附近)会告知你已将范围以磁贴的形式添加到你的仪表板中。A Success message (near the top right corner) lets you know the range was added, as a tile, to your dashboard.

  5. 选择“转到仪表板”。Select Go to dashboard. 在此处可以对已固定的可视化效果执行重命名、重设大小、添加链接和移动操作。From here you can rename, resize, link, and move the pinned visualization. 默认情况下,选择固定的磁贴将在 Power BI 中打开工作簿。By default, selecting the pinned tile opens the workbook in Power BI.

将整个表或数据透视图固定到仪表板Pin an entire table or pivot chart to a dashboard

按照上述步骤操作,只不过不选择单元格的区域,而是选择整个表或数据透视表。Follow the steps above except instead of selecting a range of cells, select an entire table or pivot table.

要固定表,请选择表的整个区域,并确保包含标头。To pin a table, select the entire range of the table and be sure to include the headers. 要固定数据透视表,请确保包含数据透视表的每个可见部分,包括筛选器(如果使用的话)。To pin a pivot table, be sure to include every visible part of the pivot table, including filters if used.

从表或数据透视表创建的磁贴将显示整个表。A tile created from a table or pivot table will show the entire table. 如果你在原始工作簿中添加/删除/筛选行或列,则也将在磁贴中添加/删除/筛选它们。If you add/remove/filter rows or columns in the original workbook, they will also be added/removed/filtered in the tile.

查看链接到磁贴的工作簿View the workbook linked to the tile

选择工作簿磁贴将在 Power BI 中打开链接的工作簿。Selecting a workbook tile opens the linked workbook in Power BI. 由于工作簿文件位于该所有者的 OneDrive for Business 上,因此查看工作簿要求具有对该工作簿的读取权限。Since the workbook file is located on the owner’s OneDrive for Business, viewing the workbook requires you have Read permissions for the workbook. 如果没有权限,将收到一条错误消息。If you do not have permission, you will receive an error message.

注意事项和疑难解答Considerations and troubleshooting

不支持的功能:Power BI 使用 Excel Services 来检索工作簿磁贴。Unsupported features: Power BI uses Excel Services to retrieve the workbook tiles. 因此,由于 Excel 中的某些功能在 Excel Services REST API 中不受支持,因此将不会在 Power BI 中的磁贴上看到这些功能。Therefore, since some features from Excel are not supported in Excel Services REST API, they will not be seen on tiles in Power BI. 例如:迷你图、图标集条件格式设置和时间切片器。For example: Sparklines, icon set conditional formatting, and time slicers. 有关不支持的功能的完整列表,请参阅 Excel Services REST API 中不支持的功能For a full list of unsupported features see Unsupported Features in Excel Services REST API

后续步骤Next steps

共享具有 Excel 工作簿链接的仪表板Share a dashboard that has links to an Excel workbook

从 Excel 工作簿获取数据Get data from Excel workbooks

更多问题?More questions? 尝试参与 Power BI 社区Try the Power BI Community