[分享] 重複人名表

本帖最後由 Bodhidharma 於 2013-4-7 16:13 編輯

剛剛應用新學到的方法寫了一個東西：
有一堆人名，希望由重複次數的多到少，列出重複的姓名以及次數

原理：
B欄輔助欄公式：B2=IF(COUNTIF(OFFSET($A$2,,,ROW()-1,),A2)=1,COUNTIF(OFFSET($A$2,,,COUNTA(A:A)-1,),A2)+1/ROW(),)下拉
COUNTIF(OFFSET($A$2,,,ROW()-1,),A2)：到目前的列，該姓名出現的次數，如果出現超過兩次的話就回傳零
COUNTIF(OFFSET($A$2,,,COUNTA(A:A)-1,),A2)+1/ROW()：如果只出現一次，就回傳該姓名出現的總次數，後面再加一個1/row()以標示該姓名出現的列數

C欄公式：C2=IFERROR(INDEX(A:A,INT(1%+1/MOD(LARGE(OFFSET($A$2,,1,COUNTA(A:A)-1,),ROW(A1)),1))),"")下拉
LARGE(OFFSET($A$2,,1,COUNTA(A:A)-1,),ROW(A1))：動態範圍選取整個輔助欄，並將其數值由大到小排序
INT(1%+1/MOD(B欄對應的值,1))：上面公式會將B欄由大到小排序，並回傳其值，B欄的值有兩個資訊：重複次數(整數部分)以及位置(小數部分)，用mod把整數部分刪掉，然後再用倒數讀取其位置。因為倒數再倒數有可能計算上會稍微有一點誤差，因此前面再加個1%後無條件捨去，即可讀取B欄對應的值其所在的列數。最後再用index即可讀取其姓名

D欄公式：D2=IFERROR(IF(INT(LARGE(OFFSET($A$2,,1,COUNTA(A:A)-1,),ROW(A1)))>0,INT(LARGE(OFFSET($A$2,,1,COUNTA(A:A)-1,),ROW(A1))),""),"")下拉
INT(LARGE(OFFSET($A$2,,1,COUNTA(A:A)-1,)：跟C欄差不多，不過是用int函數讀取B欄對應的值的重複次數，然後再加一些基本換除錯功能

整體來說只用了一個補助欄即可達到效果，而且沒有使用陣列公式
不過就效率來說，當姓名到10000筆時差不多要跑個一分鐘，不知道有沒有辦法再快一點？