點(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ì)看下案例，這不就是中國式排名+合并同類項(xiàng)嘛？
PS：中國式排名是指重復(fù)數(shù)不占用名次，比它小的最大數(shù)的排名只低一級(jí)。
我們先制作一個(gè)輔助列，用來計(jì)算中國式排名：
=SUM(--(UNIQUE($B$2:$B$20)>=B2))公式也很容易理解。先對(duì)業(yè)績(jī)數(shù)據(jù)進(jìn)行去重 （Unique 函數(shù)） ，判斷大于等于當(dāng)前業(yè)績(jī)有多少個(gè)，即可求出中國式排名。
接著，合并同類項(xiàng)，即匯總姓名：
=TEXTJOIN(",",,FILTER($A$2:$A$20,$C$2:$C$20=E2))業(yè)績(jī)用 Index+Match 函數(shù)匹配過來即可：
=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)系，一起來看案例 ↓
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)我們先來看看 Groupby 函數(shù)的基礎(chǔ)語法：
=GROUPBY (row_fields，values，function，[field_headers]，[total_depth]，[sort_order]，[filter_array])
=GROUPBY(行字段，值，匯總方式，[是否包含標(biāo)題]，[是否顯示總計(jì)小計(jì)]，[排序依據(jù)]，[篩選依據(jù)])我們來看看公式：
=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 ，無總計(jì)。
[排序依據(jù)]：按照值字段所在的列進(jìn)行排序，數(shù)字代表結(jié)果表中的列號(hào)，正數(shù)表示升序，負(fù)數(shù)表示降序。這里參數(shù)為-1，按照降序排序。
我們也可以對(duì)照著數(shù)據(jù)透視表來理解。
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ì)問，有沒有不用函數(shù)的方法呢？
問得好！我們還可以用PowerQuery，只需要?jiǎng)觿?dòng)鼠標(biāo)改改參數(shù)，就可以搞定。
PowerQuery
先將數(shù)據(jù)導(dǎo)入到 PQ 編輯器中。
全選數(shù)據(jù)區(qū)域，在【數(shù)據(jù)】選項(xiàng)卡下，單擊【來自表格/區(qū)域】-【確定】。
進(jìn)入 PQ 編輯器中，選擇業(yè)績(jī)列，點(diǎn)擊【主頁】選項(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)行排名，且合并相同排名姓名的表格處理問題。
我一共分享了三種方法。
?常規(guī)方法：
中國式排名中，重復(fù)數(shù)不占用名次，所以我們用 Unique 去重，再來判斷大于等于值的個(gè)數(shù)，即可得到中國式排名。
合并同類項(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 干貨，高效辦公早下班，那就來《秋葉 Excel 3 天集訓(xùn)營》吧~
專為職場(chǎng)人準(zhǔn)備，全部基于職場(chǎng)真實(shí)表格案例設(shè)計(jì)，超實(shí)用 Excel 技巧集合教學(xué)。
從此不愿再為表格而加班，那就行動(dòng)起來！3 天搞定表格難題！
只需 3 天，你就能獲?。?/strong>
? Excel 高效辦公技巧
? 視頻+直播+圖文筆記+課后練習(xí)
? 35 個(gè)常用函數(shù)說明手冊(cè)
? 超多 Excel 學(xué)習(xí)資源……
今天免費(fèi)領(lǐng)取
還等什么??
快掃描下圖中的二維碼搶課吧！
點(diǎn)擊關(guān)注【秋葉 Excel】
免費(fèi)獲取 Excel 干貨、模板
熱門跟貼