使用 Excel 分析Analyze in Excel

有時候您可能會想要使用 Excel 來檢視和與您有 Power BI 的資料集互動。There are times when you may want to use Excel to view and interact with a dataset that you have Power BI. 有了 [使用 EXCEL 分析],您可以做到,並以存在於 Power BI 中的資料集為基礎來存取 Excel 的樞紐分析表、 圖表和交叉分析篩選器的功能。With Analyze in Excel, you can do just that, and access PivotTable, chart, and slicer features in Excel based on the dataset that exists in Power BI.


使用 [使用 EXCEL 分析] 有幾項要求:There are a few requirements for using Analyze in Excel:

  • Microsoft Excel 2010 SP1 和更新版本支援 [使用 EXCEL 分析]。Analyze in Excel is supported for Microsoft Excel 2010 SP1 and later.
  • Excel 樞紐分析表不支援數值欄位的拖放功能彙總。Excel PivotTables do not support drag-and-drop aggregation of numeric fields. 您在 Power BI 中的資料集 必須有預先定義的量值Your dataset in Power BI must have pre-defined measures.
  • 某些組織可能會有防止安裝 [使用 EXCEL 分析] 必要更新的群組原則規則。Some organizations may have Group Policy rules that prevent installing the required Analyze in Excel updates to Excel. 如果您無法安裝更新,請洽詢您的系統管理員。If you’re unable to install the updates, check with your administrator.
  • [在 Excel 中進行分析] 需要 Pro 授權。Analyze in Excel requires a Pro license. 若要深入了解免費和 Pro 授權之間的差異,請參閱 Power BI 免費與 ProTo learn more about the differences in functionality between Free and Pro licenses, take a look at Power BI Free versus Pro.

運作方式How does it work?

當您從省略符號功能表 (...) 相關聯的資料集或報表中的 Power BI 選取 [使用 EXCEL 分析],Power BI 會建立 .ODC 檔案,並從瀏覽器下載到您的電腦。When you select Analyze in Excel from the ellipses menu (the ...) associated with a dataset or report in Power BI, Power BI creates an .ODC file and downloads it from the browser to your computer.

當您在 Excel 中開啟檔案時,您會看到一個空白樞紐分析表,以及內含 Power BI 資料集中所有資料表、欄位和量值的欄位清單。When you open the file in Excel, an empty PivotTable and Fields list appears with the tables, fields, and measures from the Power BI dataset. 如同您可以在 Excel 中使用本機資料集工作一樣,您可以建立樞紐分析表、 圖表和分析該資料集。You can create PivotTables, charts, and analyze that dataset just as you would work with a local dataset in Excel.

.ODC 檔案具有 MSOLAP 連接字串,會連接到您在 Power BI 中的資料集。The .ODC file has an MSOLAP connection string that connects to your dataset in Power BI. 當您分析或處理資料時,Excel 會查詢 Power BI 中的該資料集,並將結果傳回 Excel。When you analyze or work with the data, Excel queries that dataset in Power BI and returns the results to Excel. 如果該資料集使用 DirectQuery 連接到即時資料來源,Power BI 會查詢資料來源,並將結果傳回 Excel。If that dataset connects to a live data source using DirectQuery, Power BI queries the data source and returns the result to Excel.

[使用 Excel 分析] 對於連接到「Analysis Services 表格式」或「多維度」資料庫的資料集和報表非常有用,或是從 Power BI Desktop 檔案或 Excel 活頁簿連接的資料集和報表,其中的資料模型具有使用資料分析運算式 (DAX) 建立的模型量值。Analyze in Excel is very useful for datasets and reports that connect to Analysis Services Tabular or Multidimensional databases, or from Power BI Desktop files or Excel workbooks with data models that have model measures created using Data Analysis Expressions (DAX).

開始使用 [使用 EXCEL 分析]Get started with Analyze in Excel

在 Power BI 中,選取報表或資料集旁邊的省略符號功能表 (報表或資料集名稱旁邊的 …),然後從出現的功能表,選取 [使用 EXCEL 分析]。In Power BI, select the ellipses menu beside a report or dataset (the ... beside the report or dataset name), and from the menu that appears, select Analyze in Excel.

安裝 Excel 更新Install Excel updates

當您第一次使用 [使用 EXCEL 分析],您需要將更新安裝至 Excel 文件庫。When you first use Analyze in Excel, you need to install updates to the Excel libraries. 系統會提示您下載並執行 Excel 更新 (這會啟動安裝 SQL_AS_OLEDDB.msi Windows 安裝程式套件)。You’ll be prompted to download and run Excel updates (this initiates installation of the SQL_AS_OLEDDB.msi Windows installer package). 此套件會安裝 Microsoft AS OLE DB Provider for SQL Server 2016 RC0 (Preview)This package installs Microsoft AS OLE DB Provider for SQL Server 2016 RC0 (Preview).


請務必選取 [安裝 Excel 更新] 對話方塊中的 [不要再顯示]。Be sure to check Don’t show this again in the Install Excel updates dialog. 您只需要安裝更新一次。You only need to install the update once.

如果您需要為 [使用 EXCEL 分析] 再次安裝 Excel 更新,您可以從 Power BI 中的下載圖示下載更新,如下圖所示。If you do need to install the Excel updates for Analyze in Excel again, you can download the update from the Download icon in Power BI, as shown in the following image.

登入 Power BISign in to Power BI

即使您已經在瀏覽器中登入 Power BI,第一次在 Excel 中開啟新的 .ODC 檔案時,可能會要求您使用 Power BI 帳戶登入 Power BI。Although you’re signed in to Power BI in your browser, the first time you open a new .ODC file in Excel you may be asked to sign in to Power BI with your Power BI account. 這會驗證從 Excel 到 Power BI 的連線。This authenticates the connection from Excel to Power BI.

使用多個 Power BI 帳戶的使用者Users with multiple Power BI accounts

某些使用者有多個 Power BI 帳戶,這些使用者可能會遇到的情況是:他們用某個帳戶登入 Power BI,但該帳戶與使用於 [使用 EXCEL 分析] 所存取的資料集的帳戶是不同的。Some users have multiple Power BI accounts, and those users may encounter a situation where they're logged into Power BI with one account, but the account that has access to the dataset being used in Analyze in Excel is a different account. 在這些情況下,若您嘗試存取用於 [使用 EXCEL 分析] 活頁簿中的資料集,可能會出現禁止錯誤或登入失敗。In those situations, you may get a Forbidden error or a sign-in failure when attempting to access a dataset that's being used in an Analyze in Excel workbook.

您將有機會再次登入,屆時您可以使用用於 [使用 EXCEL 分析] 所存取之資料集的 Power BI 帳戶登入。You'll be provided an opportunity to sign in again, at which time you can sign in with the Power BI account that has access to the dataset being accessed by Analyze in Excel. 您也可以從 Excel 中的 Power BI 功能區索引標籤選取設定檔,其會識別您目前登入的帳戶,並提供一個連結讓您登出 (接著即可使用不同的帳戶登入)。You can also select Profile from the Power BI ribbon tab in Excel, which identifies which account you're currently logged in with, and provides a link that lets you sign out (and subsequently, sign in with a different account).

啟用資料連線Enable data connections

若要在 Excel 中分析您的 Power BI 資料,系統會提示您確認 .odc 檔案的路徑與檔案名稱,然後選取 [啟用]。In order to analyze your Power BI data in Excel, you are prompted to verify the file name and path for the .odc file, and then select Enable.


Power BI 租用戶的管理員可以使用 Power BI 管理入口網站,禁止位於 Analysis Services (AS) 資料庫中的內部部署資料集使用 [在 Excel 中進行分析]。Administrators for Power BI tenants can use the Power BI Admin Portal to disable the use of Analyze in Excel with on-premises datasets housed in Analysis Services (AS) databases. 停用該選項時,AS 資料庫即無法使用 [在 Excel 中進行分析],但其他資料集仍可使用者該功能。When that option is disabled, Analyze in Excel is disabled for AS databases, but continues to be available for use with other datasets.

分析為離開Analyze away

已開啟 Excel 且您有空白的樞紐分析表,則您已準備好使用您的 Power BI 資料集進行各種分析。Now that Excel has opened and you have an empty PivotTable, you're ready to do all sorts of analysis with your Power BI dataset. 如同其他本機活頁簿,您可以使用 [使用 EXCEL 分析] 來建立樞紐分析表、圖表、從其他來源新增資料等等。Just as with other local workbooks, with Analyze with Excel you can create PivotTables, charts, add data from other sources, and so on. 當然,您可以使用資料的各種檢視來建立不同的工作表。And of course, you can create different worksheets with all sorts of views into your data.


請務必了解使用在 Excel 中進行分析會將所有詳細等級的資料公開給任何具有資料集權限的使用者。It's important to know that using Analyze in Excel exposes all detail-level data to any users with permission to the dataset.


如同任何其他活頁簿,您可以儲存此 Power BI 資料集來連接活頁簿。You can save this Power BI dataset connected workbook just like any other workbook. 但是,由於您只能將活頁簿發佈或匯入至資料表中有資料或有資料模型的 Power BI,您無法將活頁簿發佈或匯入至 Power BI。However, you cannot publish or import the workbook back into Power BI because you can only publish or import workbooks into Power BI that have data in tables, or that have a data model. 因為新的活頁簿只要在 Power BI 中有連接至資料集,發佈或匯入至 Power BI 將會陷入循環!Since the new workbook simply has a connection to the dataset in Power BI, publishing or importing it into Power BI would be going in circles!


儲存活頁簿之後,您可以與組織中的其他 Power BI 使用者共用。Once your workbook is saved, you can share it with other Power BI users in your organization.

當與您共用活頁簿的使用者開啟活頁簿時,他們會看到樞紐分析表和資料最後儲存時的狀態,而不一定是最新版資料。When a user with whom you’ve shared your workbook opens the workbook, they’ll see your PivotTables and data as they appeared when the workbook was last saved, which may not be the latest version of the data. 若要取得最新資料,使用者必須先按下 [資料] 功能區中的 [重新整理] 按鈕。To get the latest data, users must use the Refresh button on the Data ribbon. 而且因為活頁簿連接到 Power BI 中的資料集,嘗試重新整理活頁簿的使用者必須登入 Power BI 和安裝首次嘗試使用此方法更新的 Excel 更新。And since the workbook is connecting to a dataset in Power BI, users attempting to refresh the workbook must sign into Power BI and install the Excel updates the first time they attempt to update using this method.

由於使用者必須重新整理資料集,但 Excel Online 不支援對外部連接重新整理,因此建議這些使用者在自己的電腦上用桌面版 Excel 來開啟活頁簿。Since users will need to refresh the dataset, and refresh for external connections is not supported in Excel Online, it’s recommended that users open the workbook in the desktop version of Excel on their computer.