Power BI 與 ExcelPower BI and Excel

一起使用 Excel 和 Power BI,並建立功能強大、相互連結的報表和視覺效果。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.

您很有可能先前已使用過 Excel。Chances 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 Pivot 或 Power 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 活頁簿檔案儲存至商務用 OneDrive 的優點。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 中稱為 [Get & Transform] (取得與轉換)) 來查詢和載入資料、[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 連接至 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 一樣位於雲端,所以可在兩者間即時連線。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 的 [Q & A] 功能發問。You can use Power BI's Q & A features to do just that.

有兩種方式可連接到您商務用 OneDrive 上的 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 上活頁簿檔案的連接。Power BI will maintain the connection between it and the workbook file on your OneDrive for Business. 如果您對活頁簿進行變更,當您儲存變更時,這些變更會與 Power BI 自動同步處理 ,通常 1 小時內即可完成。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 [Q & A] 功能,詢問有關您活頁簿中資料的問題。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

在此主題集合中,您了解到透過將現有 Excel 活頁簿上傳至 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 上的 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!

恭喜!

您已順利完成 Power BI 引導式學習的 Power BI 與 Excel一節。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