從 Excel 活頁簿檔案取得資料Get data from Excel workbook files

Microsoft Excel 是極為普遍使用的商務應用程式,Microsoft Excel is one of the most widely used business applications around. 也是將資料帶入 Power BI 極為常見的方式。It’s also one of the most common ways to get your data into Power BI.

Power BI 支援哪些活頁簿類型?What types of workbooks does Power BI support?

Power BI 支援匯入或連接到以 Excel 2007 及更新版本建立的活頁簿。Power BI supports importing or connecting to workbooks created in Excel 2007 and later. 活頁簿必須儲存為 .xlsx 或 .xlsm 檔案類型,並小於 1 GB。Workbooks must be saved as .xlsx or .xlsm file type and be under 1 GB. 本文所述的部分功能僅適用於較新版的 Excel。Some features described in this article are only available in later versions of Excel.

使用資料範圍或資料表的活頁簿Workbooks with ranges or tables of data

如果您的活頁簿包含具有資料範圍的簡單工作表,為了在 Power BI 中充分利用您的資料,請務必將這些範圍格式化為資料表。If your workbook has simple worksheets with ranges of data, to get the most out of your data in Power BI, be sure to format those ranges as tables. 如此一來,當您在 Power BI 中建立報表時,就會在 [欄位] 窗格中看到具名資料表和資料行,因此可以更容易將資料視覺化。This way, when creating reports in Power BI, you’ll see named tables and columns in the Fields pane, making it much easier to visualize your data.

使用資料模型的活頁簿Workbooks with data models

活頁簿可以包含具有一個或多個資料表的資料模型,這些資料表是透過連結資料表、Power Query (Excel 2016 的 [取得和轉換]) 或 Power Pivot 載入資料模型。Workbooks can contain a data model with one or more tables of data loaded into it by using linked tables, Power Query (Get & Transform in Excel 2016), or Power Pivot. Power BI 支援所有資料模型屬性,例如關聯性、量值、階層和 KPI。Power BI supports all data model properties such as relationships, measures, hierarchies, and KPIs.

注意

Power BI 租用戶之間無法共用使用資料模型的活頁簿。Workbooks with data models cannot be shared across Power BI tenants. 例如,使用 contoso.com 帳戶登入 Power BI 的使用者,無法與從 woodgrovebank.com 使用 Power BI 登入帳戶登入的使用者共用 Excel 活頁簿。For example, a user who logs in to Power BI using a contoso.com account cannot share an Excel workbook with a user who logs in using a Power BI login account from woodgrovebank.com.

連接到外部資料來源的活頁簿Workbooks with connections to external data sources

如果您使用 Excel 連接到外部資料來源,在 Power BI 中開啟活頁簿後,您就可以根據已連接資料來源中的資料建立報表和儀表板。If you use Excel to connect to an external data source, once your workbook is in Power BI, you can create reports and dashboards based on data from that connected data source. 您也可以設定「排定的重新整理」,以自動連接到資料來源並取得更新。You can also setup Scheduled Refresh to automatically connect right to the data source and get updates. 您將不再需要從 Excel 的 [資料] 功能區手動重新整理。You’ll no longer need to refresh manually from the Data ribbon in Excel. 報表或儀表板磚中以該資料來源的資料為基礎的所有視覺效果都會自動更新。Any visualizations in reports and tiles in dashboards based on data from that data source are updated automatically. 如需深入了解,請參閱 Power BI 的資料重新整理To learn more, see Data refresh in Power BI.

使用 Power View 工作表、樞紐分析表和圖表的活頁簿Workbooks with Power View sheets, PivotTables and charts

您的 PowerView 工作表、樞紐分析表和圖表在 Power BI 中是否顯示及顯示方式,取決於活頁簿檔案的儲存位置,以及您選擇將其放入 Power BI 的方式。How your PowerView sheets and PivotTables and charts appear, or not appear, in Power BI depends on where your workbook file is saved and how you choose to get it into Power BI. 底下將深入探討。We'll go into this more below.

資料類型Data types

Power BI 支援下列資料類型:整數、十進位數字、貨幣、日期、True/False、文字。Power BI supports the following data types: Whole Number, Decimal Number, Currency, Date, True/False, Text. 在 Excel 中將資料標示為特定資料類型會改善 Power BI 的體驗。Marking data as specific data types in Excel will improve the Power BI experience.

準備活頁簿以供 Power BI 使用Prepare your workbook for Power BI

請觀賞這段實用的影片,以深入了解如何確保 Excel 活頁簿已準備好供 Power BI 使用。Watch this helpful video to learn more about how to make sure your Excel workbooks are ready for Power BI.

活頁簿檔案的儲存位置會形成差異Where your workbook file is saved makes a difference

本機 - 如果您將活頁簿檔案儲存到電腦上的本機磁碟或組織中的其他位置,您可以從 Power BI 將檔案載入 Power BI。Local - If you save your workbook file to a local drive on your computer or another location in your organization, from Power BI you can load your file into Power BI. 您的檔案實際上會保留在本機磁碟,因此不是真的將整個檔案匯入 Power BI。Your file will actually remain on your local drive, so the whole file isn’t really imported into Power BI. 真正發生的情況是在 Power BI 中建立新的資料集,並將資料和資料模型 (如果有) 從活頁簿載入資料集。What really happens is a new dataset is created in Power BI and data and the data model (if any) from the workbook are loaded into the dataset. 如果您的活頁簿有任何 Power View 工作表,這些工作表會顯示在 Power BI 網站的 [報表] 下。If your workbook has any Power View sheets, those will appear in your Power BI site under Reports. Excel 2016 還提供 [發佈] 功能 (在 [檔案] 功能表下)。Excel 2016 also has the Publish feature (under the File menu). 使用 [發佈] 實際上就是使用 Power BI 的 [取得資料] > [檔案] > [本機檔案],但如果您定期變更活頁簿,這樣做通常會更容易在 Power BI 更新您的資料集。Using Publish is effectively the same as using Get Data > Files > Local File from Power BI, but is often easier to update your dataset in Power BI if you’re regularly making changes to the workbook.

OneDrive - 商務 - 如果您有商務用 OneDrive,並使用與用來登入 Power BI 相同的帳戶進行登入,這樣做可以有效地讓 Excel 中的工作,與 Power BI 中的資料集、報表和儀表板保持同步。由於 Power BI 和 OneDrive 都在雲端,因此 Power BI 每隔約一小時就會「連接」到您在 OneDrive 上的活頁簿檔案。OneDrive - Business – If you have OneDrive for Business and you sign into it with the same account you sign into Power BI with, this is by-far the most effective way to keep your work in Excel and your dataset, reports, and dashboards in Power BI in-sync. Because both Power BI and OneDrive are in the cloud, Power BI connects to your workbook file on OneDrive about every hour. 如果發現任何變更,便會自動更新 Power BI 中的資料集、報表和儀表板。If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI. 就像是將活頁簿儲存到本機磁碟,您也可以使用 [發佈] 立即更新 Power BI 中的資料集和報表;如果未這麼做,Power BI 通常會在一小時內自動同步處理。Just like if you saved your workbook to a local drive, you can also use Publish to update your dataset and reports in Power BI immediately; otherwise Power BI will automatically synchronize, usually within an hour.

OneDrive - 個人 - 如果您將活頁簿檔案儲存到自己的 OneDrive 帳戶,則可以利用許多與使用商務用 OneDrive 相同的優點。OneDrive - Personal – If you save your workbook files to your own OneDrive account, you’ll get many of the same benefits as you would with OneDrive for Business. 最大的差異是當您第一次連接到檔案時 (使用 [取得資料] > [檔案] > [OneDrive - 個人]),需要使用 Microsoft 帳戶登入 OneDrive,這通常與用來登入 Power BI 的帳戶不同。The biggest difference is when you first connect to your file (using Get Data > Files > OneDrive – Personal) you’ll need to sign in to your OneDrive with your Microsoft account, which is usually different from what you use to sign in to Power BI. 當您使用 Microsoft 帳戶登入 OneDrive 時,請務必選取 [讓我保持登入] 選項。When signing in with your OneDrive with your Microsoft account, be sure to select the Keep me signed in option. 如此一來,Power BI 每隔約一小時就會連接到您的活頁簿檔案,以確保您在 Power BI 中的資料集和報表保持同步。This way, Power BI will be able to connect to your workbook file about every hour and make sure your dataset and reports in Power BI are in-sync.

SharePoint 小組網站 - 將 Power BI Desktop 檔案儲存到 SharePoint - 小組網站與儲存到商務用 OneDrive 大致相同。SharePoint Team-Sites – Saving your Power BI Desktop files to SharePoint – Team Sites is much the same as saving to OneDrive for Business. 最大的差異是從 Power BI 連接到檔案的方式。The biggest difference is how you connect to the file from Power BI. 您可以指定 URL 或連接到根資料夾。You can specify a URL or connect to the root folder.

一個 Excel 活頁簿 - 兩種使用方式One Excel workbook – two ways to use it

如果您將活頁簿檔案儲存到 OneDrive,有幾個方式可以瀏覽您在 Power BI 中的資料。If you save your workbook files to OneDrive, you'll have a couple of ways you can explore your data in Power BI

將 Excel 資料匯入 Power BIImport Excel data into Power BI

當您選擇 [匯入] 時,資料表及/或資料模型中任何支援的資料都會匯入 Power BI 中的新資料集。When you choose Import, any supported data in tables and/or a data model are imported into a new dataset in Power BI. 如果您有任何 Power View 工作表,則會在 Power BI 中重新建立為報表。If you have any Power View sheets, those will be re-created in Power BI as reports.

您可以繼續編輯活頁簿。You can continue editing your workbook. 當您儲存變更後,這些變更通常會在一小時內與 Power BI 中的資料集同步處理。When your changes are saved, they’ll be synchronized with the dataset in Power BI, usually within about an hour. 如果您需要立即同步處理,您可以直接再按一次 [發佈] 立即匯出變更。If you need more immediate gratification, you can just click Publish again, and your changes are exported right then and there. 您在報表和儀表板中的任何視覺效果也會更新。Any visualizations you have in reports and dashboards will be updated, too.

如果您使用 [取得和轉換資料] 或 Power Pivot 將資料載入資料模型,或是您的活頁簿具有想要在 Power BI 中查看的 Power View 工作表及視覺效果,請選擇此選項。Choose this option if you’ve used Get & Transform data or Power Pivot to load data into a data model, or if your workbook has Power View sheets with visualizations you want to see in Power BI.

在 Excel 2016 中,您也可以使用 [發佈] > [匯出]。In Excel 2016, you can also use Publish > Export. 其功能幾乎相同。It's pretty much the same thing. 如需深入了解,請參閱從 Excel 2016 發佈至 Power BITo learn more, see Publish to Power BI from Excel 2016.

在 Power BI 中連接、管理及檢視 ExcelConnect, manage and view Excel in 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 click Edit, 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.

選擇這種方式時,不會在 Power BI 中建立任何資料集。When choosing this way, no dataset is created in Power BI. 您的活頁簿會顯示在 Power BI 工作區功能窗格的 [報表] 下。Your workbook will appear in your Power BI workspace navigation pane under Reports. 已連接的活頁簿會有特殊 Excel 圖示。Connected workbooks have a special Excel icon.

如果您只有工作表中的資料,或是有需要釘選到儀表板的範圍、樞紐分析表和圖表,請選擇此選項。Choose this option if you only have data in worksheets, or you have ranges, PivotTables and charts you want to pin to dashboards.

在 Excel 2016 中,您也可以使用 [發佈] > [上傳]。In Excel 2016, you can also use Publish > Upload. 其功能幾乎相同。It's pretty much the same thing. 如需深入了解,請參閱從 Excel 2016 發佈至 Power BITo learn more, see Publish to Power BI from Excel 2016.

從 Power BI 匯入或連接到 Excel 活頁簿Import or connect to an Excel workbook from Power BI

  1. 在 Power BI 的功能窗格中,按一下 [取得資料]。In Power BI, in the navigation pane, click Get Data.

  2. 在 [檔案] 中,按一下 [取得]。In Files, click Get.

  3. 尋找您的檔案。Find your file.

  4. 如果您的活頁簿檔案位於 OneDrive 或 SharePoint - 小組網站上,請選擇 [匯入] 或 [連接]。If your workbook file is on OneDrive or SharePoint - Team Sites, choose Import or Connect.

本機 Excel 活頁簿Local Excel workbooks

您也可以使用本機 Excel 檔案並將其上傳到 Power BI。You can also use a local Excel file and upload it into Power BI. 只要從上一個功能表選取 [本機檔案],然後導覽至您儲存 Excel 活頁簿的位置即可。Simply select Local File from the previous menu, then navigate to where you have your Excel workbooks saved.

選取之後,您可以選擇將檔案上傳到 Power BI。Once selected, choose to Upload your file into Power BI.

上傳活頁簿之後,您會收到活頁簿準備就緒的通知。Once your workbook is uploaded, you get a notification that the workbook is ready.

活頁簿準備就緒之後,即可在 Power BI 的 [報表] 區段中找到此活頁簿。Once the workbook is ready, you can find it in the Reports section of Power BI.

從 Excel 2016 發佈至您的 Power BI 網站Publish from Excel 2016 to your Power BI site

使用 Excel 2016 的 [發佈至 Power BI] 功能,實際上就是使用 Power BI 的 [取得資料] 匯入或連接到您的檔案。Using the Publish to Power BI feature in Excel 2016 is effectively the same as using Get Data in Power BI to import or connect to your file. 此處不會詳細說明,不過您可以參閱從 Excel 2016 發佈至 Power BI 以深入了解。We won't go into the details here, but you can see Publish to Power BI from Excel 2016 to learn more.

疑難排解Troubleshooting

活頁簿檔案太大?Workbook file too big? 請參閱減少 Excel 活頁簿的大小以在 Power BI 中檢視Check out Reduce the size of an Excel workbook to view it in Power BI.

目前,當您選擇 [匯入] 時,Power BI 只會匯入具名資料表或資料模型中的資料。Currently, when you choose Import, Power BI only imports data that is part of a named table or a data model. 因此,如果活頁簿未包含任何具名資料表、Power View 工作表或 Excel 資料模型,您可能會看到此錯誤:「在此 Excel 活頁簿中找不到任何資料」As a result, if the workbook contains no named tables, Power View sheets, or Excel data models, you might see this error: "We couldn't find any data in your Excel workbook". 這篇文章說明如何修正您的活頁簿並重新匯入。This article explains how to fix your workbook and re-import it.

後續步驟Next steps

瀏覽您的資料 - 將檔案中的資料和報表匯入 Power BI 後,您就可以開始瀏覽。Explore your data - Once you get data and reports from your file into Power BI, it's time to explore. 只要以滑鼠右鍵按一下新的資料集,然後按一下 [瀏覽]。Just right-click the new dataset and then click Explore. 如果您在步驟 4 中,選擇連接到 OneDrive 上的活頁簿檔案,您的活頁簿會顯示在 [報表] 中。If you chose to connect to a workbook file on OneDrive in step 4, your workbook will appear in Reports. 當您按一下活頁簿時,活頁簿會在 Power BI 中開啟,就像是在 Excel Online 中一樣。When you click on it, it will open in Power BI, just as it would if it were in Excel Online.

排程重新整理 - 如果您的 Excel 活頁簿檔案連接到外部資料來源,或是從本機磁碟匯入,您可以設定排定的重新整理,確保資料集或報表一律為最新狀態。Schedule refresh - If your Excel workbook file connects to external data sources, or you imported from a local drive, you can setup scheduled refresh to make sure your dataset or report is always up-to-date. 在大多數情況下,設定排定的重新整理相當容易,但細節部分則不在本文的討論範圍內。In most cases, setting up scheduled refresh is quite easy to do, but going into the details is outside the scope of this article. 如需深入了解,請參閱 Power BI 的資料重新整理See Data refresh in Power BI to learn more.

從 Excel 2016 發佈至 Power BIPublish to Power BI from Excel 2016

Power BI publisher for ExcelPower BI publisher for Excel

Power BI 的資料重新整理Data refresh in Power BI