原文標(biāo)題：《哪位 Excel 高人琢磨出的這個(gè)數(shù)據(jù)整理技巧，太有用了?。ㄏ拢?/span>

小 E 之前在處理訂單數(shù)據(jù)的時(shí)候，需要將相同的訂單數(shù)據(jù)進(jìn)行合并發(fā)貨，以節(jié)約物流成本。

如下圖所示，在此之前，我介紹了三種解決方法：函數(shù)方法，插件方法還有 PQ 方法。

小 E 在打包了上百份快遞后，已經(jīng)腰酸背痛的情景下。

巧好看到后臺(tái)有小伙伴留言，想要讓我們講講度量值。

本著滿足于讀者需求的原則，今天我們就來講講，如何利用度量值搞定上面這個(gè)問題 。

調(diào)用 PP 加載項(xiàng)

說到度量值，肯定有人會(huì)問，什么是度量值?

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

那么在 Excel 中，度量值在哪個(gè)地方呢？

就在 PowerPivot 選項(xiàng)卡下面。

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

如果有些小伙伴沒有看到 PowerPivot 選項(xiàng)卡，是因?yàn)闆]有將它調(diào)出來。

大家有開發(fā)工具選項(xiàng)卡的話，只需要在 COM 加載項(xiàng)把它調(diào)用出來就可以。

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

然后同樣勾選「powerpivot」加載上去就可以了。

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

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

具體步驟

由于存在同個(gè)信息有多筆訂單數(shù)據(jù)的情況，為了簡化問題的難度，方便大家理解，我跟之前一樣，先制作一個(gè)匯總輔助表。

然后通過這個(gè)輔助表，用數(shù)據(jù)透視表的方式，來編寫度量值。

我們先來看看具體操作步驟。

? 選中「輔助表」區(qū)域-在「插入」選項(xiàng)卡下-單擊「數(shù)據(jù)透視表」-單擊「表格與區(qū)域」。

放置在現(xiàn)有工作表中，同時(shí)勾選將此數(shù)據(jù)添加到數(shù)據(jù)模型中，單擊【確定】按鈕。

PS. 勾選數(shù)據(jù)模型后，數(shù)據(jù)會(huì)自動(dòng)加載到 PowerPivot 中，所以我們就能使用里面的度量值啦~

這時(shí)，就可以看到剛剛的輔助表名稱成為了透視表區(qū)域名稱。

現(xiàn)在，我們來新建度量值~

? 在「PP」選項(xiàng)卡下單擊「度量值」-新建度量值。

出現(xiàn)度量值對話框。

度量值名稱：發(fā)貨數(shù)量。

公式:

=CONCATENATEX('區(qū)域','區(qū)域'[產(chǎn)品名稱]&"*"&'區(qū)域'[匯總個(gè)數(shù)],",")

單擊【確定】按鈕。

① 將姓名和手機(jī)號(hào)拖拽到行區(qū)域中。

② 將發(fā)貨數(shù)量度量值拖拽到值區(qū)域中。

此時(shí)，我們想要的效果就實(shí)現(xiàn)啦！

度量值解釋

在前面我們寫了一個(gè)簡單的度量值，就立馬解決了我們的需求。

利用數(shù)據(jù)透視表的好處是，我們可以靈活的增加或者減少外部篩選字段條件。

比如說，我現(xiàn)在只想要合并同一手機(jī)號(hào)購買的產(chǎn)品以及對應(yīng)數(shù)量，那么只需要把姓名字段從行區(qū)域中取消就可以了。

數(shù)據(jù)透視表中每一個(gè)匯總的值，里面都是一個(gè)數(shù)據(jù)集。

那我們來簡單看看這個(gè)度量值。

=CONCATENATEX('區(qū)域','區(qū)域'[產(chǎn)品名稱]&"*"&'區(qū)域'[匯總個(gè)數(shù)],",")

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

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

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

我們先雙擊一下小爽發(fā)貨數(shù)量的單元格。

此時(shí)，就會(huì)出現(xiàn)一個(gè)新的工作表，里面就有篩選小爽以及她的手機(jī)號(hào)碼的數(shù)據(jù)集。

然后執(zhí)行我們的度量值。

=CONCATENATEX('區(qū)域','區(qū)域'[產(chǎn)品名稱]&"*"&'區(qū)域'[匯總個(gè)數(shù)],",")

先執(zhí)行表達(dá)式: 產(chǎn)品名稱與匯總個(gè)數(shù)合并：

'區(qū)域'[產(chǎn)品名稱]&"*"&'區(qū)域'[匯總個(gè)數(shù)],

最后再執(zhí)行 CONCATENATE 將數(shù)據(jù)利用分隔符合并。

合并后就是這個(gè)單元格的匯總結(jié)果了。

如果說不想用輔助表。

同理，我們也可以通過將數(shù)據(jù)源插入數(shù)據(jù)透視表的方式，將數(shù)據(jù)源添加到數(shù)據(jù)模型中，最后新建如下度量值，就可以搞定。

原理跟上面基本差不多，有興趣的小伙伴可以自行去研究一下。

=CONCATENATEX(VALUES('表1'[產(chǎn)品名稱]),'表1'[產(chǎn)品名稱]&"*"&calculate(sum('表1'[商品數(shù)量])),";")

最后的話

本文講解的是復(fù)雜合并同類項(xiàng)關(guān)于數(shù)據(jù)透視表的做法。

在插入數(shù)據(jù)透視表時(shí)勾選數(shù)據(jù)模型，數(shù)據(jù)會(huì)自動(dòng)加載到 PowerPivot (簡稱 PP) 中，而度量值就是存在于 PP 中。

要想調(diào)用 PP 選項(xiàng)卡，只需要在 COM 加載項(xiàng)中勾選即可。

案例中的度量值，我們主要用到了 CONCATENATEX 函數(shù)，它是一個(gè)迭代函數(shù)，能夠?qū)?shù)據(jù)集中的每一行進(jìn)行表達(dá)式運(yùn)算，最后再利用分隔符將其進(jìn)行合并成一個(gè)值。

相比于其他的做法，使用數(shù)據(jù)透視表的好處是我們可以靈活的控制篩選環(huán)境，一旦篩選字段需要改變，我們只需要進(jìn)行拖拖拽拽就可以。

而其他做法可能還需要重新更改，比較麻煩。

本文來自微信公眾號(hào)：秋葉 Excel （ID：excel100），作者：小爽

廣告聲明：文內(nèi)含有的對外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。