Power BI 和 ExcelPower BI and Excel

Use Excel and Power BI together and create powerful, connected reports and visuals.

你将了解操作方式...

在 Power BI 中使用 Excel 数据简介Introduction to using Excel data in Power BI

欢迎进入 Power BI 引导学习课程的 Power BI 和 Excel 部分。Welcome to the Power BI and Excel section of the Guided Learning course for Power BI.

很可能你已经使用过 ExcelChances are good that you've used Excel before. 或许你曾使用 Excel 创建或查看报表,或生成饼图或其他视觉对象。Maybe you used Excel to create or view reports, or to build pie charts or other visuals. 或者你可能曾使用 Power PivotPower Query 生成技术性稍强的解决方案。Or maybe you used Power Pivot or Power Query to build solutions that were a bit more technical. 如果是这样,你将在 Power BI 中感到得心应手,你的工作薄也同样如此。If so, you'll be right at home in Power BI, and so will your workbooks.

本节将向你介绍将 Excel 工作薄导入 Power BI 是多么简单,并演示 Power BI 和 Excel 如何展现最佳搭档气质。This section shows you just how easy it is to bring Excel workbooks into Power BI, and demonstrates that Power BI and Excel make a great partnership.

在 Power BI 中使用 Excel 数据简介Introduction to using Excel data in Power BI

观看 Excel 和 Power BI 服务协同工作方式的简介Watch an introduction to the ways Excel and the Power BI service work together

凭借 Power BI,将你的 Excel 数据导入 Power BI 是非常简单的。With Power BI, getting your Excel data into Power BI is easy.

以下主题将指导你使用简单的表格将 Excel 工作薄上传到 Power BI。The following topics guide you through uploading an Excel workbook with a simple table into Power BI. 然后你将了解如何上传使用 Excel 更高级的 BI 数据建模和报表功能(如 Power Pivot 和 Power View)创建的工作薄。Then you see how to upload workbooks created with Excel's more advanced BI data modeling and reporting features, such as Power Pivot and Power View.

你还将了解到将 Excel 工作薄文件保存到 One Drive for Business 的一些优点。You'll also learn some benefits of saving your Excel workbook files to OneDrive for Business. 从 Power BI 连接到 OneDrive 上的 Excel 文件时,你在 Power BI 中的报表和仪表板将在你作出更改并保存工作时自动更新和刷新。When you connect to Excel files on OneDrive from Power BI, your reports and dashboards in Power BI are updated and refreshed automatically when you make changes and save your work.

将 Excel 数据上传到 Power BIUpload Excel data to Power BI

在本主题中,我们将首先了解一下如何将包含简单的 Excel 工作簿文件从本地驱动器导入 Power BI。In this topic, we'll first take a look at how you can import an Excel workbook file containing a simple table from a local drive into Power BI. 然后了解如何通过在 Power BI 中创建报表开始浏览该表的数据。You'll then learn how you can begin exploring that table's data in Power BI by creating a report.

确保数据已格式化为表格Make sure your data is formatted as a table

为了使 Power BI 从工作簿导入数据,需要将这些数据格式化为表格In order for Power BI to import data from your workbook, that data needs to be formatted as a table. 这很容易。It's easy. 在 Excel 中,你可以突出显示某个范围的单元格区域,然后在 Excel 功能区的插入选项卡上,单击表格In Excel, you can highlight a range of cells, then on the Insert tab of the Excel ribbon, click Table.

你需要确保每列均具有有效的名称。You'll want to make sure each column has a good name. 它将能够使你在 Power BI 中创建报表时更轻松地找到你想要的数据。It will make it easier to find the data you want when creating your reports in Power BI.

从本地驱动器导入Import from a local drive

无论将文件保存在何处,通过 Power BI 都可以轻松导入它们。Wherever you keep your files, Power BI makes it easy to import them. 在 Power BI 中,你可以使用获取数据 > 文件 > 本地文件以查找并选择我们想要的 Excel 文件。In Power BI, you can use Get Data > Files > Local File, to find and select the Excel file we want.

一旦数据导入到 Power BI,你便可以开始创建报表。Once imported into Power BI, you can begin creating reports.

当然,你的文件无需位于本地驱动器。Your files don't have to be on a local drive, of course. 如果你将文件保存在 OneDrive 或 SharePoint 团队网站上,那就更好了。If you save your files on OneDrive or SharePoint Team Site, that's even better. 我们将在稍后的某个主题中介绍其详细信息。We'll go into more details about that in a later topic.

开始创建报表Start creating reports

导入工作簿的数据之后,就会在 Power BI 中创建一个数据集。Once your workbook's data has been imported, a dataset is created in Power BI. 它将出现在数据集中。It appears under Datasets. 现在可以通过创建报表和仪表板开始浏览数据了。Now you can begin exploring your data by creating reports and dashboards. 只需单击数据集旁边的打开菜单图标,然后单击浏览Just click on the Open menu icon next to the dataset and then click Explore. 此时会出现一个新的空白报表画布。A new blank report canvas appears. 在右上角的字段下,你将看到表格和列。Over on the right, under Fields, you'll see your tables and columns. 只需选择你想要在画布上创建新的可视化效果的字段。Just select the fields you want to create a new visualization on the canvas.

你可以更改可视化效果的类型并应用可视化效果下的筛选器和其他属性。You can change the type of visualization and apply filters and other properties under Visualizations.

将 Power View 和 Power Pivot 导入到 Power BIImport Power View and Power Pivot to Power BI

如果你使用 Excel 的任何高级 BI 功能,如使用 Power Query(在 Excel 2016 中称为获取和变换)查询和加载数据,使用 Power Pivot 创建功能强大的数据模型以及使用 Power View 创建动态报表,你也可以将这些功能导入 Power BI。If you use any of Excel's advanced BI features like Power Query (called Get & Transform in Excel 2016), to query and load data, Power Pivot to create powerful data models, and Power View to create dynamic reports, you can import those into Power BI, too.

如果你使用 Power Pivot 创建高级数据模型,如具有多个相关表格、度量值、计算列和层次结构的数据模型,Power BI 也将导入所有相关内容。If you use Power Pivot to create advanced data models, like those with multiple related tables, measures, calculated columns, and hierarchies, Power BI will import all of that as well.

如果你的工作簿具有 Power View 工作表,也没问题。If your workbook has Power View sheets, no problem. Power BI 会将它们重建为 Power BI 中的新报表Power BI will re-create them as new Reports in Power BI. 你可以立即开始将可视化效果固定到仪表板。You can start pinning visualizations to dashboards right away.

下面是 Power BI 的强大功能之一:如果你使用 Power Query 或 Power Pivot 连接、查询和加载外部数据源中的数据,将工作簿导入 Power BI 后,你便可以设置计划刷新And here's one of the great features of Power BI: If you use Power Query or Power Pivot to connect to, query, and load data from an external data source, once you've imported your workbook into Power BI, you can setup scheduled refresh. 通过使用计划刷新,Power BI 将用你的工作簿中的连接信息直接连接到数据源并查询和加载已更改的任何数据。By using scheduled refresh, Power BI will use the connection information from your workbook to connect directly to the datasource and query and load any data that has changed. 报表中的任何可视化效果也将自动更新。Any visualizations in reports will automatically be updated, too.

将 OneDrive for Business 连接到 Power BIConnect OneDrive for Business to Power BI

将 Excel 工作簿保存到 OneDrive 时,在 Power BI 和 Excel 之间实现无缝集成。Get seamless integration between Power BI and Excel when you save your Excel workbooks to OneDrive.

因为 OneDrive 和 Power BI 一样,也在云端,所以需要在 Power BI 与 OneDrive 之间进行实时连接。Because OneDrive is in the cloud, just like Power BI, a live connection is made between Power BI and OneDrive. 如果对 OneDrive 上的工作簿进行了更改,则那些更改将 自动同步 到 Power BI。If you make changes to your workbook on OneDrive, those changes are automatically synchronized with Power BI. 报表和仪表板中的可视化效果将保持为最新状态。Your visualizations in reports and dashboards are kept up-to-date. 如果工作簿连接到外部数据源(如数据库或 OData 数据源),则可以使用 Power BI 的计划刷新功能检查是否有更新。If your workbook connects to external data sources like a database or an OData feed, you can use Power BI's Schedule refresh features to check for updates. 需要对工作簿中的数据进行提问?Need to ask questions about data in your workbook? 没问题。No problem. 可以使用 Power BI 的问答功能做到这一点。You can use Power BI's Q & A features to do just that.

可以通过两种方式连接到 OneDrive for Business 上的 Excel 文件:There are two ways to connect to your Excel files on OneDrive for Business:

  1. 将 Excel 数据导入到 Power BIImport Excel data into Power BI
  2. 在 Power BI 中连接、管理和查看 ExcelConnect, manage, and view Excel in Power BI

将 Excel 数据导入到 Power BIImport Excel data into Power BI

如果选择将 Excel 数据导入到 Power BI,会将工作簿中的表数据加载到 Power BI 的新数据集中。When you choose to import Excel data into Power BI, table data from your workbook is loaded into a new dataset in Power BI. 如果工作簿中有任何 Power View 工作表,则该工作表也会被导入,并在 Power BI 中自动创建新的报表。If you have any Power View sheets in your workbook, those are imported and new reports are automatically created in Power BI, too.

Power BI 将保持自身与 OneDrive for Business 上的工作簿文件之间的连接。Power BI will maintain the connection between it and the workbook file on your OneDrive for Business. 如果对工作簿进行了任何更改,保存更改后,这些更改通常都将在一小时内 自动同步 * 到 Power BI。If you make any changes to your workbook, when you save, those changes will be automatically synchronized* with Power BI, usually within an hour. 如果工作簿连接到外部数据源,则可以设置计划刷新,以便 Power BI 中的数据集保持为最新。If your workbook connects to external data sources, you can setup scheduled refresh so the dataset in Power BI is kept up-to-date. 因为 Power BI 中的报表和仪表板中的可视化效果将使用数据集中的数据,所以浏览时你的查询将快如闪电。Because visualizations in reports and dashboard in Power BI will use the data from the dataset, as you explore, your queries are lightning fast.

在 Power BI 中连接、管理和查看 ExcelConnect, manage and view Excel in Power BI

如果选择连接到 Excel 工作簿,则可以实现处理 Excel 工作簿和 Power BI 之间的无缝体验。When you choose to connect to the Excel workbook, you'll get a seamless experience of working with your workbook in Excel and Power BI. 通过这种方式连接时,工作簿的报表旁边有一个小的 Excel 图标。When you connect this way, the workbook's report has a small Excel icon next to it.

在 Power BI 中查看 Excel 工作簿的方式与在 Excel Online 中查看工作簿的方式相同。In the report, you see your Excel workbook in Power BI just as you would in Excel Online. 你可以通过从省略号菜单中选择编辑来在 Excel Online 中浏览和编辑你的工作表。You can explore and edit your worksheets in Excel Online by selecting Edit from the ellipses menu. 进行更改时,将自动更新任何固定到仪表板的可视化效果。When you make changes, any visualizations you've pinned to dashboards are updated automatically.

未在 Power BI 中创建任何数据集。No dataset is created in Power BI. 所有数据均保留在 OneDrive 的工作簿中。All of the data remains in the workbook on OneDrive. 这种方法的优点之一是如果工作簿连接到外部数据源,则可以设置计划刷新One of the many advantages to this approach is that you can setup scheduled refresh if your workbook connects to external data sources. 你可以选择诸如数据透视表和图表等元素,并将其直接固定到 Power BI 的仪表板中。You can select elements such as PivotTables and charts and pin them right to dashboards in Power BI. 进行的任何更改都将自动反映在 Power BI 中。If you make any changes, they're automatically reflected in Power BI. 并且你还可以使用 Power BI 出色的问答功能来对工作簿中的数据进行提问。And, you can use Power BI's awesome Q & A features to ask questions about the data in your workbook.

Power BI 中的 Excel - 摘要Excel in Power BI - summary

在本主题集合中,你将了解到通过在 Power BI 中上传和浏览,利用现有的 Excel 工作簿文件是多么的简单。In this collection of topics, you learned how easy it is to leverage your existing Excel workbook files by uploading and exploring them in Power BI. 你可以上传带简单表的 Excel 工作簿,也可以上传使用 Excel 更高级的 BI 功能(例如 Power Pivot 和 Power View)创建的工作簿。You can upload Excel workbooks with simple tables or workbooks created with Excel's more advanced BI features like Power Pivot and Power View.

你还了解了如何连接到驻留在 OneDrive for Business 上的 Excel 文件,以及如何使用 Power BI 自动刷新功能,在你更改工作簿时使你的报表和仪表板保持最新。You also learned how to connect to Excel files that reside on OneDrive for Business, and use Power BI's automatic refresh features to keep your reports and dashboards up-to-date as you make changes in your workbook.

后续步骤Next steps

祝贺你!Congratulations! 你已经完成了 Power BI 引导学习课程的 Power BI 和 Excel 部分。You've completed the Power BI and Excel section of the Guided Learning course for Power BI. 这很简单,不是吗?That was easy, wasn't it? Power BI 和 Excel 组成是最佳的搭档,你可以同时轻松使用这两者。Power BI and Excel make a great team, and makes it easy for you to use them together.

掌握所有这些知识并具备在报表和仪表板中创建引人注目的视觉对象的能力之后,你便可以共享你的杰作了。With all the knowledge you've gained, and your ability to create compelling visuals in reports and dashboards, you're probably ready to share your masterpieces. 下一节,发布和共享将向你演示如何做到这一点。The next section, Publishing and Sharing, shows you how to do just that.

下一部分见!See you in the next section!

恭喜!

Nice job completing the Power BI and Excel section of Power BI Guided learning. Next, you learn about publishing and sharing.

你已了解操作方式...

下一个教程

Publishing and sharing

作者

  • Davidiseminger
  • olprod
  • Alisha-Acharya