點(diǎn)擊藍(lán)字關(guān)注【秋葉 Excel】

發(fā)送【7】

免費(fèi)領(lǐng) 1000+篇 Excel 精選教程！

本文作者：小爽

本文編輯：竺蘭

大家好，這里是秋葉編輯部~

之前的文章（），我們講解過基于左表的一維數(shù)據(jù)源，制作右表的數(shù)據(jù)分析匯總圖的函數(shù)做法。

關(guān)于數(shù)據(jù)分析，我們還有一個(gè)利器，那就是PowerPivot，超級(jí)透視表。

PS：Power Pivot 是 Excel 的一個(gè)加載項(xiàng)，可用于管理數(shù)以百萬計(jì)的數(shù)據(jù)行，并對(duì)這些數(shù)據(jù)執(zhí)行強(qiáng)大的數(shù)據(jù)分析。

一起來看看，如何利用它解決這個(gè)問題吧~

超級(jí)透視表

如果沒有【開發(fā)工具】選項(xiàng)卡的話，我們可以在【文件】-【選項(xiàng)】-【加載項(xiàng)】，通過【轉(zhuǎn)到】按鈕，也可以調(diào)用出 COM 加載項(xiàng)對(duì)話框。

然后勾選【Power Pivot】加載上去就可以了。

到這里，我就先默認(rèn)大家都已經(jīng)調(diào)出 PP 選項(xiàng)卡了。

那么下一步我們來看看具體做法。

第一步：將數(shù)據(jù)源添加到數(shù)據(jù)模型中。

選擇【數(shù)據(jù)區(qū)域】，在【Power Pivot】選項(xiàng)卡下，單擊【添加到數(shù)據(jù)模型】-【確定】。

此時(shí)就進(jìn)入我們的 PP 編輯器中了。

現(xiàn)在，我們來新增度量值。

度量值：顧名思義，它就是一個(gè)值，也就是它的結(jié)果只有一個(gè)，一般是用聚合函數(shù)計(jì)算出來的值。

度量值的編寫，可以在 PP 編輯器中，計(jì)算區(qū)域中直接編寫，也可以在【Power Pivot】選項(xiàng)卡中新建。

這里，我們采取第二種方式。

在【Power Pivot】選項(xiàng)卡下，單擊【度量值】-【新建度量值】。

購買總金額度量值。

表名：表1

度量值名稱：購買總金額

公式：

=SUM('表1'[購買金額])

如果不確定公式寫的對(duì)不對(duì)，可以單擊檢查公式進(jìn)行檢查，最后點(diǎn)擊【確定 】按鈕 。

同理，新建第二個(gè)度量值。

購買明細(xì)度量值。

表名：表1

度量值名稱：購買明細(xì)

輸入度量值公式：

=CONCATENATEX(VALUES('表1'[產(chǎn)品名稱]),'表1'[產(chǎn)品名稱]&[購買總金額],",")

單擊檢查公式，確定公式?jīng)]有問題后，再單擊【確定 】 按鈕。

回到 PP 編輯器中，我們?cè)谟?jì)算區(qū)域中，也可以看到我們前面編寫的度量值。

單擊【主頁】選項(xiàng)卡，單擊【數(shù)據(jù)透視表】-【數(shù)據(jù)透視表】。

購買日期字段放在行區(qū)域，度量值購買明細(xì)和購買總金額放在值區(qū)域。

此時(shí)數(shù)據(jù)透視表就已經(jīng)是我們想要的效果啦

看到這里，如果你想系統(tǒng)學(xué)習(xí)更多超級(jí)透視表和其他 Excel 技巧，提高辦公效率，那你可別錯(cuò)過秋葉的網(wǎng)課《Excel 高手速成實(shí)戰(zhàn)課》！

原價(jià) 999

現(xiàn)在超值優(yōu)惠價(jià)

包含231節(jié)實(shí)戰(zhàn)課程

名師授課+配套練習(xí)+社群答疑

長(zhǎng)期有效，可反復(fù)回看

接下來，我們來看一下這兩個(gè)度量值的計(jì)算邏輯。

度量值解析

首先看度量值：【購買總金額】的計(jì)算邏輯。

以數(shù)據(jù)透視表中的 G2 單元格為例，

?看外部篩選環(huán)境

當(dāng)前的篩選上下文為 2025/7/1，雙擊 G2 單元格，數(shù)據(jù)為篩選行字段 2025/7/1 的所有數(shù)據(jù)。

?看度量值公式

=sum('表1'[購買金額])

如下圖，對(duì)購買金額列直接求和，也就是 400。

接下來，繼續(xù)看第二個(gè)度量值。

度量值：【購買明細(xì)】的計(jì)算邏輯。

以數(shù)據(jù)透視表的 F2 單元格為例，

?先看外部篩選環(huán)境

當(dāng)前外部篩選上下文為 2025/7/1，雙擊 F2 單元格，也可以看到基于篩選后的所有數(shù)據(jù)。

?看度量值公式

   )

Concatenatex 函數(shù)的作用：

就是將多個(gè)文本合并到一起，類似于 Excel 中的 Textjoin 函數(shù)。

Concatenatex 函數(shù)是一個(gè)迭代函數(shù)。

Concatenatex 函數(shù)的結(jié)構(gòu)如下：

=CONCATENATEX(表，表達(dá)式，分隔符，...)

來看看這個(gè) Dax 函數(shù)公式的運(yùn)算邏輯 ↓

?Values 函數(shù)獲得去重后表。

當(dāng) Values 函數(shù)的參數(shù)為單列的時(shí)候，該函數(shù)能夠?qū)α羞M(jìn)行去重，返回的是一個(gè)去重的表，會(huì)受到篩選器的影響。

?對(duì)表中的每一行執(zhí)行表達(dá)式操作。

'表1'[產(chǎn)品名稱]

引用產(chǎn)品名稱列，由于行上下文的存在，相當(dāng)于引用當(dāng)前行的數(shù)據(jù)。

[購買總金額]

這是一個(gè)度量值，也就是引用我們前面編寫的度量值。

度量值會(huì)自動(dòng)在外層添加 Calculate 函數(shù)，Calculate 函數(shù)會(huì)使得行上下文轉(zhuǎn)換成篩選上下文。

=Calculate(sum('表1'[購買金額]))

'表1'[產(chǎn)品名稱]&[購買總金額]

?最后對(duì)操作后數(shù)據(jù)進(jìn)行分隔符合并聚合。

=Calculate(表，表達(dá)式，”,”)

基于表達(dá)式得出結(jié)果，按逗號(hào)進(jìn)行合并。

如果不想使用數(shù)據(jù)透視表，我們也可以使用 Dax 查詢語句。

Dax 查詢語句

在【數(shù)據(jù)】選項(xiàng)卡下，單擊【現(xiàn)有連接】，【表格】標(biāo)簽，選擇【表1】 單擊【確定】按鈕。

選擇【表】 現(xiàn)有工作表，這里我選擇 A15 單元格。

單擊【確定】按鈕。

此時(shí)就可以看到，查詢表格就顯示出來了，后面的話，就是編輯 Dax 查詢。

單擊表格，鼠標(biāo)右鍵，選擇【表格】，選擇【編輯查詢】。

命令類型選擇 Dax。

輸入如下的表達(dá)式：

 )

單擊【確定】按鈕。

我們可以看到，查詢表格就出來了。

我們來簡(jiǎn)單介紹一下這個(gè)查詢語句。

Evaluate 指令能夠?qū)o隨其后的 Dax 表達(dá)式進(jìn)行求值計(jì)算，并且將該 Dax 表達(dá)式的計(jì)算結(jié)果以普通 Excel 工作表的形式呈現(xiàn)。

Summerarize 函數(shù)用于對(duì)數(shù)據(jù)進(jìn)行分組匯總。

=SUMMERARIZE(表,表中分組的列..,列名1,匯總方式,[列名2],[匯總方式]...)

看看對(duì)應(yīng)返回表的查詢語句。

)

PP 是我們對(duì)數(shù)據(jù)一維表進(jìn)行數(shù)據(jù)分析匯總的利器。

本文介紹利用編寫的度量值，利用透視表和查詢語句返回最終的結(jié)果。

超級(jí)透視表：先對(duì)外部的篩選環(huán)境進(jìn)行篩選，然后利用度量值公式計(jì)算。

度量值是一個(gè)值，使用的是 Dax 函數(shù)。

對(duì)了，如果你想學(xué)習(xí)更多更有用的 Excel 提效技巧，建議入手《Excel 高手速成實(shí)戰(zhàn)課》，系統(tǒng)學(xué)習(xí)，提升自己！

課程包含 231節(jié)實(shí)戰(zhàn)教程，系統(tǒng)全面帶你掌握Excel 系統(tǒng)操作、商務(wù)圖表、函數(shù)公式、數(shù)據(jù)透視表、高效技巧，一站式學(xué)透 Excel 表格！

《Excel 高手速成實(shí)戰(zhàn)課》

課程原價(jià) 999 元

限時(shí)優(yōu)惠價(jià)，僅需 99 元

名師授課+系統(tǒng)教學(xué)+配套練習(xí)+社群答疑

長(zhǎng)期有效，可反復(fù)回看

別猶豫了！趕緊掃碼搶課

現(xiàn)在報(bào)名，隨課贈(zèng)送豐厚資料：

900+ 套精選 Excel 模板

307 個(gè)函數(shù)清單

100 例圖表實(shí)戰(zhàn)案例

70 個(gè)實(shí)用圖表模板

好了，本文到這里就結(jié)束啦，有疑惑的，可以在留言區(qū)提問哦~