原文標(biāo)題：《夸爆！哪位 Excel 高人琢磨出的這個(gè)數(shù)據(jù)整理技巧，太有用了！》
大家好，這里是秋葉編輯部~
今天的分享，是來自一位地產(chǎn)營銷人的提問。
「我想根據(jù)來訪登記表，自動晾曬實(shí)時(shí)到訪人次榜單，能做到嗎？」
案例說明：
上圖中，需對 C 列進(jìn)行條件計(jì)數(shù)，根據(jù)結(jié)果從大到小依次獲取對應(yīng)置業(yè)顧問姓名。
我們姑且稱這一問題為「分組統(tǒng)計(jì)并排序問題」。
該問題十分復(fù)雜，其難點(diǎn)至少包括以下三點(diǎn)：
? 必須進(jìn)行條件計(jì)數(shù)，但計(jì)數(shù)的條件值需要從數(shù)據(jù)列表中獲取，除非使用輔助列，否則沒有現(xiàn)成的人員清單可供引用；
? 人員是多次重復(fù)的，不同置業(yè)顧問的到訪次數(shù)也可能是重復(fù)，但求值結(jié)果中的人名都必須是唯一的，公式需有去重功能；
? 必須完成按到訪次數(shù)大小排序，再索引計(jì)數(shù)值對應(yīng)的置業(yè)顧問姓名文本，實(shí)現(xiàn)數(shù)值到文本的轉(zhuǎn)換。
分組統(tǒng)計(jì)并排序問題在不同 Excel 版本中有不同的解題公式，其難易也不盡相同，接下來，小花就為大家逐一講解。
1、INDEX+MOD+LARGE 法
此方法適用于 Excel 2019 及以下版本，僅使用常見的幾個(gè)「老函數(shù)」組合，即可實(shí)現(xiàn)對復(fù)雜去重排序問題的求解。
但其理解難度頗大，需要小伙伴們沉心靜氣，跟隨小花的拆解，慢慢消化其中的知識點(diǎn)。
公式：
=INDEX($C$C,MOD(LARGE(COUNTIF($C$2$C$300$C$2$C$300)*(COUNTIF(INDIRECT("C2:C"&ROW($C$2$C$300))$C$2$C$300)=1)+ROW($C$2$C$300)%ROW()-2)1)*10000)
公式說明：
? COUNTIF($C$2:$C$300,$C$2:$C$300)
該片段執(zhí)行一組條件計(jì)數(shù) COUNTIF 運(yùn)算，分別以 C2:C300 的每一個(gè)單元格為條件值，以其本身為計(jì)數(shù)范圍，統(tǒng)計(jì) C2:C300 中每一個(gè)值出現(xiàn)的次數(shù)，即每個(gè)置業(yè)顧問的客戶到訪次數(shù)。
不言而喻，此處的到訪次數(shù)數(shù)組中的每個(gè)值都是多次重復(fù)的，出現(xiàn)頻數(shù)即為重復(fù)次數(shù)。
? COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)),$C$2:$C$300)=1
同樣執(zhí)行一組條件計(jì)數(shù) COUNTIF 運(yùn)算，遍歷 C2:C300，通過 INDIRECT 函數(shù)構(gòu)建一個(gè)從 C2 到當(dāng)前單元格的引用區(qū)域作為計(jì)數(shù)范圍，再使用 COUNTIF 函數(shù)統(tǒng)計(jì)當(dāng)前值在單元格范圍中出現(xiàn)的次數(shù)。
由于計(jì)數(shù)范圍總是包含當(dāng)前單元格，其結(jié)果必然≥1；
如果 COUNTIF 函數(shù)的返回值為 1，則說明，當(dāng)前單元格是首次出現(xiàn)該值的位置；
如果大于 1，則說明在該單元格之上，已經(jīng)出現(xiàn)過該值了。
最后將 COUNTIF 函數(shù)的返回值與 1 進(jìn)行對比，將數(shù)值轉(zhuǎn)化為邏輯值，所有的 TRUE 值剛好標(biāo)記出每個(gè)唯一值首次出現(xiàn)的位置。
? ①*(②)
由于①頻數(shù)數(shù)組會多次重復(fù)，無法直接通過 LARGE 函數(shù)取排位值；
而②為邏輯數(shù)組，僅首次出現(xiàn)位置處為 TRUE（計(jì)算時(shí)為 1），其余為 FALSE（計(jì)算時(shí)為 0）；
于是①*②剛好實(shí)現(xiàn)對①的去重，實(shí)現(xiàn)僅首次出現(xiàn)位置保留有效頻數(shù)，其余均為 0。
至此，LARGE 函數(shù)已經(jīng)具備了發(fā)揮作用的條件，但如何將唯一的頻數(shù)值與所在行號掛鉤，實(shí)現(xiàn)第 k 大數(shù)值中包含其位置值信息呢？
? ③+ROW($C$2:$C$300)%%
ROW (C2:C300) 返回一組行號值，兩個(gè) %% 等同于除以 10000，將它轉(zhuǎn)化為小數(shù)，再與③相加，既不影響頻數(shù)值之間的大小排序，又能指示當(dāng)前值位置信息。
? LARGE(④，ROW()-2)
ROW ()-2 返回一個(gè) k 值，F(xiàn)3 單元格的 k 為 1，F(xiàn)4 單元格的 k 為 2，逐次增大，而 LARGE 函數(shù)依次取④中第 k 大的值。
? MOD(⑤,1)*10000
此處是對片段④的反運(yùn)算，通過對 1 取余再乘以 10000，換算出被兩個(gè) %% 轉(zhuǎn)化為小數(shù)的 ROW (C2:C300) 的行號值。
? INDEX(C:C,⑥)
INDEX 函數(shù)根據(jù)片段⑥返回的行號值索引 C 列對應(yīng)位置，即可得到出現(xiàn)頻數(shù)第 k 高的置業(yè)顧問姓名，問題得解。
2、寫在最后
以上，就是 Excel 2019 及以下版本用戶解決分組統(tǒng)計(jì)并排序問題的正解，思路大致如下：
? 以計(jì)數(shù)范圍為計(jì)數(shù)條件，使用 COUNTIF 統(tǒng)計(jì)出一組重復(fù)的頻數(shù)數(shù)組；
? 用 INDIRECT 函數(shù)構(gòu)建動態(tài)擴(kuò)展的計(jì)數(shù)范圍，判斷當(dāng)前值是否為首次出現(xiàn)；
? ①和②相乘，實(shí)現(xiàn)去重，加上代表行號的小數(shù)，以標(biāo)識文本位置；
? 使用 LARGE 獲取第 k 大值，再用 MOD 取余獲取文本位置行號，最后用 INDEX 進(jìn)行索引。
本文分享的公式在 Excel 屬于高難度級別，一時(shí)難以理解也無需焦慮，只要多看幾次，用心理解，相信每個(gè)小伙伴都能最終將其中的原理和思路內(nèi)化為自己的修行和能力。
當(dāng)然了，更高級版本 Excel 中還有其他更簡潔的解題公式，小花將在下一篇文章中繼續(xù)分享，敬請期待吧！
本文來自微信公眾號：秋葉 Excel（ID：excel100），作者：小花
廣告聲明：文內(nèi)含有的對外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家含有外鏈的文章均包含本聲明。