點擊關注 【秋葉 Excel】

發送【7】領 1000 篇 Excel 精選教程！

作者：小花

編輯：衛星醬

大家好，這里是秋葉編輯部~

今天的分享，是來自一位地產營銷人的提問。

「我想根據來訪登記表，自動晾曬實時到訪人次榜單，能做到嗎？」

案例說明：

上圖中，需對 C 列進行條件計數，根據結果從大到小依次獲取對應置業顧問姓名。

我們姑且稱這一問題為「分組統計并排序問題」。

該問題十分復雜，其難點至少包括以下三點：

? 必須進行條件計數，但計數的條件值需要從數據列表中獲取，除非使用輔助列，否則沒有現成的人員清單可供引用； ? 人員是多次重復的，不同置業顧問的到訪次數也可能是重復，但求值結果中的人名都必須是唯一的，公式需有去重功能； ? 必須完成按到訪次數大小排序，再索引計數值對應的置業顧問姓名文本，實現數值到文本的轉換。

分組統計并排序問題在不同 Excel 版本中有不同的解題公式，其難易也不盡相同，接下來，小花就為大家逐一講解。

如果你被數據統計逼瘋，想學點技能提高工作效率？

那你一定要加入《秋葉 Excel 3 天集訓營》，不僅有名師教你表格排版+數據處理+工作匯報，還有助教隨時答疑，助你更快掌握 Excel~

《秋葉 Excel 3 天集訓營》

原價 99 元

今天限時免費

搶占學習名額

開啟你的高效辦公之旅吧！

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)

該片段執行一組條件計數 COUNTIF 運算，分別以 C2:C300 的每一個單元格為條件值，以其本身為計數范圍，統計 C2:C300 中每一個值出現的次數，即每個置業顧問的客戶到訪次數。

不言而喻，此處的到訪次數數組中的每個值都是多次重復的，出現頻數即為重復次數。

? COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)),$C$2:$C$300)=1

同樣執行一組條件計數 COUNTIF 運算，遍歷 C2:C300，通過 INDIRECT 函數構建一個從 C2 到當前單元格的引用區域作為計數范圍，再使用 COUNTIF 函數統計當前值在單元格范圍中出現的次數。

由于計數范圍總是包含當前單元格，其結果必然≥1；

如果 COUNTIF 函數的返回值為 1，則說明，當前單元格是首次出現該值的位置；

如果大于 1，則說明在該單元格之上，已經出現過該值了。

最后將 COUNTIF 函數的返回值與 1 進行對比，將數值轉化為邏輯值，所有的 TRUE 值剛好標記出每個唯一值首次出現的位置。

? ①*(②)

由于①頻數數組會多次重復，無法直接通過 LARGE 函數取排位值；

而②為邏輯數組，僅首次出現位置處為 TRUE （計算時為 1） ，其余為 FALSE （計算時為 0） ；

于是①*②剛好實現對①的去重，實現僅首次出現位置保留有效頻數，其余均為 0。

至此，LARGE 函數已經具備了發揮作用的條件，但如何將唯一的頻數值與所在行號掛鉤，實現第 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 高的置業顧問姓名，問題得解。

以上，就是 Excel 2019 及以下版本用戶解決分組統計并排序問題的正解，思路大致如下：

? 以計數范圍為計數條件，使用 COUNTIF 統計出一組重復的頻數數組；

? 用 INDIRECT 函數構建動態擴展的計數范圍，判斷當前值是否為首次出現；

? ①和②相乘，實現去重，加上代表行號的小數，以標識文本位置；

? 使用 LARGE 獲取第 k 大值，再用 MOD 取余獲取文本位置行號，最后用 INDEX 進行索引。

本文分享的公式在 Excel 屬于高難度級別，一時難以理解也無需焦慮，只要多看幾次，用心理解，相信每個小伙伴都能最終將其中的原理和思路內化為自己的修行和能力。

當然了，更高級版本 Excel 中還有其他更簡潔的解題公式，小花將在下一篇文章中繼續分享，敬請期待吧！

真正的 Excel 高手，不是加班最晚的人，而是用對方法的人！

如果你也想告別熬夜做表，升職加薪快人一步，那么一定要參加秋葉 Excel 3 天集訓營！

只需 3 天時間，每天 30 分鐘，你也能掌握更多 Excel 技巧，提高效率、減輕工作壓力~

《秋葉 Excel 3 天集訓營》

原價 99 元

現在限時免費

搶占學習名額

開啟你的高效辦公之旅吧！

▲ 現在報名，還能免費獲得更多福利：

點擊關注【秋葉 Excel】

免費獲取 Excel 干貨、模板