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