減少 Excel 活頁簿的大小以在 Power BI 中檢視Reduce the size of an Excel workbook to view it in Power BI

所有小於 1 GB 的 Excel 活頁簿都可上傳至 Power BI。You can upload any Excel workbook smaller than 1 GB to Power BI. Excel 活頁簿可以包含兩個部分:資料模型和報表的其他部分 — 核心工作表內容。An Excel workbook can have two parts: a Data Model, and the rest of the report—the core worksheet contents. 如果報表符合下列大小限制,您就可以將它儲存到商務用 OneDrive,然後從 Power BI 連接到它並於 Excel Online 中檢視:If the report meets the following size limits, you can save it to OneDrive for Business, connect to it from Power BI, and view it in Excel Online:

  • 整份活頁簿的上限是 1 GB。The workbook as a whole can be up to 1 GB.
  • 核心工作表內容的上限是 10 MB。The core worksheet contents can be up to 10 MB.

核心工作表內容大於 10 MB 的原因What makes core worksheet contents larger than 10 MB

以下是使得核心工作表內容大於 10 MB 的項目:Here are some elements that can make the core worksheet contents larger than 10 MB:

建議您盡可能移除這些項目。Consider removing these elements, if possible.

如果報表包含資料模型,您可有其他選擇:If the report has a Data Model, you have some other options:

  • 移除 Excel 工作表中的資料,將它改儲存在資料模型中。Remove data from Excel worksheets, and store it in the Data Model instead. 如需詳細資訊,請參閱下文<移除工作表的資料>。See “Remove data from worksheets” below for details.
  • 建立記憶體有效率的資料模型來減少報表的整體大小。Create a memory-efficient Data Model to reduce the overall size of the report.

若要進行前述任何變更,您必須在 Excel 中編輯活頁簿。To make any of these changes, you need to edit the workbook in Excel.

詳細資訊請參閱 SharePoint Online 的 Excel 活頁簿檔案大小限制Read more about file size limits for Excel workbooks in SharePoint Online.

移除工作表的資料Remove data from worksheets

如果從 [Power Query] 索引標籤或 [Excel 資料] 索引標籤將資料匯入 Excel,此活頁簿在 Excel 資料表和資料模型中可能會有相同的資料。If you import data into Excel from the Power Query tab or the Excel Data tab, the workbook might have the same data in an Excel table and in the Data Model. Excel 工作表中的大型資料表可能會使得核心工作表內容超過 10 MB。Large tables in Excel worksheets may make the core worksheet contents more than 10 MB. 在 Excel 中移除資料表並在資料模型中保存資料,可大幅減少報表的核心工作表內容。Removing the table in Excel and keeping the data in the Data Model can greatly reduce the core worksheet contents of the report.

將資料匯入 Excel 時,請依照下列提示作業:When you import data into Excel, follow these tips:

  • 在 [Power Query] 中:清除 [載入到工作表] 方塊。In Power Query: Clear the Load to worksheet box.

    資料會只匯入資料模型,Excel 工作表中沒有任何資料。The data is imported only into the Data Model, with no data in Excel worksheets.

  • 如果先前在 [匯入精靈] 中核取了 [資料表],從 [Excel 資料] 索引標籤:請移至 現有連線 > 按一下該連線 > 只建立連線From the Excel Data tab, if you previously checked Table in the import wizard: Go to Existing Connections > click the connection > Only create connection. 刪除於初始匯入期間建立的原始資料表。Delete the original table or tables created during the initial import.
  • 從 [Excel 資料] 索引標籤:不要核取 [匯入資料] 方塊中的 [資料表] 。From the Excel Data tab: don’t check Table in the Import Data box.

活頁簿大小最佳化工具Workbook Size Optimizer

如果您的活頁簿包含資料模型,您就可以執行活頁簿大小最佳化工具,以減少您的活頁簿大小。If your workbook contains a data model, you can run the workbook size optimizer to reduce the size of your workbook. 下載活頁簿大小最佳化工具Download Workbook Size Optimizer.

建立記憶體效率高的資料模型Create a memory-efficient Data Model

在 Power BI Desktop 中使用商務用 OneDrive 連結Use OneDrive for Business links in Power BI Desktop