點擊關(guān)注 【秋葉 Excel】
發(fā)送【7】領(lǐng) 1000 篇 Excel 精選教程！
作者：小花
編輯：衛(wèi)星醬
大家好，這里是秋葉編輯部~
今天的分享，是來自一位地產(chǎn)營銷人的提問。
「我想根據(jù)來訪登記表，自動晾曬實時到訪人次榜單，能做到嗎？」
案例說明：
上圖中，需對 C 列進(jìn)行條件計數(shù)，根據(jù)結(jié)果從大到小依次獲取對應(yīng)置業(yè)顧問姓名。
我們姑且稱這一問題為「分組統(tǒng)計并排序問題」。
該問題十分復(fù)雜，其難點至少包括以下三點：
? 必須進(jìn)行條件計數(shù)，但計數(shù)的條件值需要從數(shù)據(jù)列表中獲取，除非使用輔助列，否則沒有現(xiàn)成的人員清單可供引用； ? 人員是多次重復(fù)的，不同置業(yè)顧問的到訪次數(shù)也可能是重復(fù)，但求值結(jié)果中的人名都必須是唯一的，公式需有去重功能； ? 必須完成按到訪次數(shù)大小排序，再索引計數(shù)值對應(yīng)的置業(yè)顧問姓名文本，實現(xiàn)數(shù)值到文本的轉(zhuǎn)換。
分組統(tǒng)計并排序問題在不同 Excel 版本中有不同的解題公式，其難易也不盡相同，接下來，小花就為大家逐一講解。
如果你被數(shù)據(jù)統(tǒng)計逼瘋，想學(xué)點技能提高工作效率？
那你一定要加入《秋葉 Excel 3 天集訓(xùn)營》，不僅有名師教你表格排版+數(shù)據(jù)處理+工作匯報，還有助教隨時答疑，助你更快掌握 Excel~
《秋葉 Excel 3 天集訓(xùn)營》
原價 99 元
今天限時免費
搶占學(xué)習(xí)名額
開啟你的高效辦公之旅吧！
INDEX+MOD+LARGE 法
此方法適用于Excel 2019 及以下版本，僅使用常見的幾個「老函數(shù)」組合，即可實現(xiàn)對復(fù)雜去重排序問題的求解。
但其理解難度頗大，需要小伙伴們沉心靜氣，跟隨小花的拆解，慢慢消化其中的知識點。
公式：
=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í)行一組條件計數(shù) COUNTIF 運算，分別以 C2:C300 的每一個單元格為條件值，以其本身為計數(shù)范圍，統(tǒng)計 C2:C300 中每一個值出現(xiàn)的次數(shù)，即每個置業(yè)顧問的客戶到訪次數(shù)。
不言而喻，此處的到訪次數(shù)數(shù)組中的每個值都是多次重復(fù)的，出現(xiàn)頻數(shù)即為重復(fù)次數(shù)。
? COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)),$C$2:$C$300)=1
同樣執(zhí)行一組條件計數(shù) COUNTIF 運算，遍歷 C2:C300，通過 INDIRECT 函數(shù)構(gòu)建一個從 C2 到當(dāng)前單元格的引用區(qū)域作為計數(shù)范圍，再使用 COUNTIF 函數(shù)統(tǒng)計當(dāng)前值在單元格范圍中出現(xiàn)的次數(shù)。
由于計數(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)記出每個唯一值首次出現(xiàn)的位置。
? ①*(②)
由于①頻數(shù)數(shù)組會多次重復(fù)，無法直接通過 LARGE 函數(shù)取排位值；
而②為邏輯數(shù)組，僅首次出現(xiàn)位置處為 TRUE （計算時為 1） ，其余為 FALSE （計算時為 0） ；
于是①*②剛好實現(xiàn)對①的去重，實現(xiàn)僅首次出現(xiàn)位置保留有效頻數(shù)，其余均為 0。
至此，LARGE 函數(shù)已經(jīng)具備了發(fā)揮作用的條件，但如何將唯一的頻數(shù)值與所在行號掛鉤，實現(xiàn)第 k 大數(shù)值中包含其位置值信息呢？
? ③+ROW($C$2:$C$300)%%
ROW(C2:C300)返回一組行號值，兩個%%等同于除以 10000，將它轉(zhuǎn)化為小數(shù)，再與③相加，既不影響頻數(shù)值之間的大小排序，又能指示當(dāng)前值位置信息。
? LARGE(④，ROW()-2)
ROW()-2 返回一個 k 值，F(xiàn)3 單元格的 k 為 1，F(xiàn)4 單元格的 k 為 2，逐次增大，而 LARGE 函數(shù)依次取④中第 k 大的值。
? MOD(⑤,1)*10000
此處是對片段④的反運算，通過對 1 取余再乘以 10000，換算出被兩個%%轉(zhuǎn)化為小數(shù)的 ROW(C2:C300)的行號值。
? INDEX(C:C,⑥)
INDEX 函數(shù)根據(jù)片段⑥返回的行號值索引 C 列對應(yīng)位置，即可得到出現(xiàn)頻數(shù)第 k 高的置業(yè)顧問姓名，問題得解。
以上，就是 Excel 2019 及以下版本用戶解決分組統(tǒng)計并排序問題的正解，思路大致如下：
? 以計數(shù)范圍為計數(shù)條件，使用 COUNTIF 統(tǒng)計出一組重復(fù)的頻數(shù)數(shù)組；
? 用 INDIRECT 函數(shù)構(gòu)建動態(tài)擴(kuò)展的計數(shù)范圍，判斷當(dāng)前值是否為首次出現(xiàn)；
? ①和②相乘，實現(xiàn)去重，加上代表行號的小數(shù)，以標(biāo)識文本位置；
? 使用 LARGE 獲取第 k 大值，再用 MOD 取余獲取文本位置行號，最后用 INDEX 進(jìn)行索引。
本文分享的公式在 Excel 屬于高難度級別，一時難以理解也無需焦慮，只要多看幾次，用心理解，相信每個小伙伴都能最終將其中的原理和思路內(nèi)化為自己的修行和能力。
當(dāng)然了，更高級版本 Excel 中還有其他更簡潔的解題公式，小花將在下一篇文章中繼續(xù)分享，敬請期待吧！
真正的 Excel 高手，不是加班最晚的人，而是用對方法的人！
如果你也想告別熬夜做表，升職加薪快人一步，那么一定要參加秋葉 Excel 3 天集訓(xùn)營！
只需 3 天時間，每天 30 分鐘，你也能掌握更多 Excel 技巧，提高效率、減輕工作壓力~
《秋葉 Excel 3 天集訓(xùn)營》
原價 99 元
現(xiàn)在限時免費
搶占學(xué)習(xí)名額
開啟你的高效辦公之旅吧！
▲ 現(xiàn)在報名，還能免費獲得更多福利：
點擊關(guān)注【秋葉 Excel】
免費獲取 Excel 干貨、模板
熱門跟貼