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