在Excel中，使用Power Pivot搭建的模型通常用透視表展現(xiàn)結(jié)果，如下圖所示。

除了透視表，還可以是表格樣式：

在《將透視表偽裝成表格的兩種方式》這篇文章中，我講解了如何制作表格樣式的透視表。

無(wú)論是表格還是透視表展現(xiàn)，都不夠靈活。本文介紹一個(gè)更加天馬行空的方式：CUBEVALUE函數(shù)輸出模型結(jié)果，取值到單元格中。感謝微博楓雨2020網(wǎng)友的分享。

該函數(shù)語(yǔ)法如下：

CUBEVALUE(connection,[member_expression1],[member_expression2], …)

Connection ?必需。一個(gè)表示多維數(shù)據(jù)集的連接名稱(chēng)的文本字符串。

Member_expression 可選。多維表達(dá)式 (MDX) 的文本字符串，用來(lái)計(jì)算出多維數(shù)據(jù)集內(nèi)的成員或元組。另外，member_expression 可以是由 CUBESET 函數(shù)定義的集合。使用 member_expression 作為切片器來(lái)定義要返回其匯總值的多維數(shù)據(jù)集部分。如果 member_expression 中未指定度量值，則使用該多維數(shù)據(jù)集的默認(rèn)度量值。

微軟官網(wǎng)

那么如何使用呢？我們以一個(gè)有三個(gè)數(shù)據(jù)源的銷(xiāo)售模型為例。

1.取單個(gè)模型中的數(shù)值

假設(shè)需要獲取姓名為胡大花的銷(xiāo)售額，任意單元格輸入以下公式：

=CUBEVALUE("ThisWorkbookDataModel","[銷(xiāo)售員].[銷(xiāo)售員].[胡大花]","[Measures].[M_銷(xiāo)售額]")

分步對(duì)該公式進(jìn)行講解。

輸入CUBEVALUE函數(shù)后，第一個(gè)參數(shù)先輸入雙引號(hào)，會(huì)自動(dòng)彈出“本工作簿模型”，選擇該模型。

第二個(gè)參數(shù)再次輸入雙引號(hào)，會(huì)提示選擇表格或者度量值，此處我們需要提取銷(xiāo)售員表中的姓名，因此選擇銷(xiāo)售員表。

選擇銷(xiāo)售員表后，輸入一個(gè)"."，彈出該表中的所有列，選擇銷(xiāo)售員列。

再次輸入一個(gè)"."，彈出ALL，即默認(rèn)數(shù)據(jù)是所有銷(xiāo)售員，此處我們將ALL手工更改為胡大花。

最后一個(gè)參數(shù)選擇度量值中的銷(xiāo)售額。

這樣，胡大花的業(yè)績(jī)體現(xiàn)在了單元格中。這個(gè)公式的內(nèi)容全部來(lái)自模型，那么如何與單元格內(nèi)容互動(dòng)？比方如下B30單元格是胡大花，B31是完顏朵。

2.與單元格互動(dòng)取值

與單元格互動(dòng)，需要將上述公式中的[胡大花]更改為單元格位置。如C30單元格公式為：

=CUBEVALUE("ThisWorkbookDataModel","[銷(xiāo)售員].[銷(xiāo)售員].["&B30&"]","[Measures].[M_銷(xiāo)售目標(biāo)]")

本案例只使用了一個(gè)條件對(duì)值進(jìn)行計(jì)算，即銷(xiāo)售業(yè)員姓名，如果有多個(gè)條件也可逗號(hào)隔開(kāi)并列書(shū)寫(xiě)公式。這種方法特別適合復(fù)雜報(bào)表格式的自動(dòng)化，例如各種合并單元格，各種表頭層級(jí)：

除了CUBEVALUE，CUBE家族還有其他函數(shù)，讀者可以自行體驗(yàn)。