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

發(fā)送【7】 領(lǐng) 1000 篇 Excel 精選教程！

作者：小爽

編輯：竺蘭

大家好，我是研究數(shù)據(jù)處理的小爽~

每隔一個(gè)季度，公司就會(huì)對(duì)員工業(yè)績(jī)排名，相同業(yè)績(jī)的姓名需要合并在一起，如下圖所示：

從左表的數(shù)據(jù)，匯總成右表，應(yīng)該怎么做？

常規(guī)方法

仔細(xì)看下案例，這不就是中國(guó)式排名+合并同類項(xiàng)嘛？

PS：中國(guó)式排名是指重復(fù)數(shù)不占用名次，比它小的最大數(shù)的排名只低一級(jí)。

我們先制作一個(gè)輔助列，用來(lái)計(jì)算中國(guó)式排名：

=SUM(--(UNIQUE($B$2:$B$20)>=B2))

公式也很容易理解。先對(duì)業(yè)績(jī)數(shù)據(jù)進(jìn)行去重 （Unique 函數(shù)） ，判斷大于等于當(dāng)前業(yè)績(jī)有多少個(gè)，即可求出中國(guó)式排名。

接著，合并同類項(xiàng)，即匯總姓名：

=TEXTJOIN(",",,FILTER($A$2:$A$20,$C$2:$C$20=E2))

業(yè)績(jī)用 Index+Match 函數(shù)匹配過(guò)來(lái)即可：

=INDEX($B$2:$B$20,MATCH(E2,$C$2:$C$20,0))

上面這樣做，是比較常規(guī)的思路。

我們還可以換種角度思考：將業(yè)績(jī)分組，分組后的姓名進(jìn)行合并，分組的業(yè)績(jī)進(jìn)行降序排序，添加名次索引數(shù)據(jù)。

不懂？沒關(guān)系，一起來(lái)看案例 ↓

Groupby

那在 Excel 中，有沒有分組函數(shù)？

當(dāng)然！Office 365 現(xiàn)在已經(jīng)有 Groupby 函數(shù)了，WPS 目前也已經(jīng)更新。

Groupby，顧名思義，就是按照某些字段對(duì)數(shù)據(jù)進(jìn)行分組。

第一種方法，我們用了好幾個(gè)函數(shù)對(duì)吧？

使用 Groupby 函數(shù)，我們只需要一個(gè)公式：

=GROUPBY(B2:B20,A2:A20,ARRAYTOTEXT,0,0,-1)

我們先來(lái)看看 Groupby 函數(shù)的基礎(chǔ)語(yǔ)法：

=GROUPBY (row_fields，values，function，[field_headers]，[total_depth]，[sort_order]，[filter_array])
=GROUPBY(行字段，值，匯總方式，[是否包含標(biāo)題]，[是否顯示總計(jì)小計(jì)]，[排序依據(jù)]，[篩選依據(jù)])

我們來(lái)看看公式：

=GROUPBY(B2:B20,A2:A20,ARRAYTOTEXT,0,0,-1)

行字段：B2:B20，就是業(yè)績(jī)列。

值：A2:A20，就是姓名列。

匯總方式：Arraytotext，我們需要將姓名列進(jìn)行合并。 Arraytotext 函數(shù)可以將數(shù)組區(qū)域轉(zhuǎn)化為字符串格式，默認(rèn)用英文逗號(hào)進(jìn)行合并。

[是否包含標(biāo)題]：0 ，不包含標(biāo)題。

[是否顯示總計(jì)小計(jì)]：0 ，無(wú)總計(jì)。

[排序依據(jù)]：按照值字段所在的列進(jìn)行排序，數(shù)字代表結(jié)果表中的列號(hào)，正數(shù)表示升序，負(fù)數(shù)表示降序。這里參數(shù)為-1，按照降序排序。

我們也可以對(duì)照著數(shù)據(jù)透視表來(lái)理解。

Groupby 函數(shù)有三個(gè)必選參數(shù)。

=GROUPBY (行字段，值，匯總方式)

行字段：業(yè)績(jī)列--對(duì)應(yīng)數(shù)據(jù)透視表的行字段。

值：姓名列--對(duì)應(yīng)數(shù)據(jù)透視表的值。

匯總方式：Counta 函數(shù)--對(duì)應(yīng)數(shù)據(jù)透視表的值匯總方式計(jì)數(shù)項(xiàng)。

到這里，大家應(yīng)該懂了 Groupby 函數(shù)的作用了吧？

如果我們想要將姓名用頓號(hào)合并，可以直接改第三參數(shù)，使用匿名函數(shù) Lambda。

=GROUPBY(B2:B20,A2:A20,LAMBDA(s,TEXTJOIN("、",,s)),0,0,-1)

最后，增加一個(gè)名次索引即可。

看到這里，可能有人會(huì)問(wèn)，有沒有不用函數(shù)的方法呢？

問(wèn)得好！我們還可以用PowerQuery，只需要?jiǎng)觿?dòng)鼠標(biāo)改改參數(shù)，就可以搞定。

PowerQuery

先將數(shù)據(jù)導(dǎo)入到 PQ 編輯器中。

全選數(shù)據(jù)區(qū)域，在【數(shù)據(jù)】選項(xiàng)卡下，單擊【來(lái)自表格/區(qū)域】-【確定】。

進(jìn)入 PQ 編輯器中，選擇業(yè)績(jī)列，點(diǎn)擊【主頁(yè)】選項(xiàng)卡-【分組依據(jù)】，出現(xiàn)分組依據(jù)對(duì)話框。

新列名：姓名合并

操作：所有行

出現(xiàn)分組后的數(shù)據(jù)。

然后針對(duì) each _進(jìn)行更改。

如下圖公式，改成對(duì)姓名列進(jìn)行合并：

each Text.Combine([姓名],"、")

Text.Combine 函數(shù)能將姓名進(jìn)行合并，類似于 Excel 中的 Textjoin 函數(shù)。

選擇業(yè)績(jī)列下三角-【降序排序】。

最后添加名次索引。

在【添加列】選項(xiàng)卡，單擊【索引列】，【從 1】。

這時(shí)，就會(huì)出現(xiàn)一個(gè)索引列。

更改公式中的索引為「名次」。

到這里，PQ 做法就介紹完了。

本文主要講解的是根據(jù)業(yè)績(jī)進(jìn)行排名，且合并相同排名姓名的表格處理問(wèn)題。

我一共分享了三種方法。

?常規(guī)方法：

中國(guó)式排名中，重復(fù)數(shù)不占用名次，所以我們用 Unique 去重，再來(lái)判斷大于等于值的個(gè)數(shù)，即可得到中國(guó)式排名。

合并同類項(xiàng)中，F(xiàn)ilter 函數(shù)+Textjoin 函數(shù)的組合搭配。

?新函數(shù) Groupby：

基于業(yè)績(jī)字段進(jìn)行分組，值數(shù)據(jù)為姓名，匯總方式將姓名合并。可以類比數(shù)據(jù)透視表進(jìn)行理解。

其中有個(gè)參數(shù)可以指定排序依據(jù)，降序排序后，增加索引，就是對(duì)應(yīng)的名次。

?PowerQuery：

思路也是分組，排序，加索引

• 分組依據(jù)

• 更改匯總方式 Text.Combine

• 將業(yè)績(jī)進(jìn)行降序

• 添加索引列，從 1 開始

今天我們又解鎖了一個(gè)新函數(shù)，如果你還想學(xué)更多 Excel 干貨，高效辦公早下班，那就來(lái)《秋葉 Excel 3 天集訓(xùn)營(yíng)》吧~

專為職場(chǎng)人準(zhǔn)備，全部基于職場(chǎng)真實(shí)表格案例設(shè)計(jì)，超實(shí)用 Excel 技巧集合教學(xué)。

從此不愿再為表格而加班，那就行動(dòng)起來(lái)！3 天搞定表格難題！

只需 3 天，你就能獲取：

? Excel 高效辦公技巧

? 視頻+直播+圖文筆記+課后練習(xí)

? 35 個(gè)常用函數(shù)說(shuō)明手冊(cè)

? 超多 Excel 學(xué)習(xí)資源……

今天免費(fèi)領(lǐng)取

還等什么??

快掃描下圖中的二維碼搶課吧！

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

免費(fèi)獲取 Excel 干貨、模板