【原】用countif實現(xiàn)對文本排序的效果

劉卓學(xué)EXCEL 2021-04-02

展開全文

小伙伴們，大家好。今天來學(xué)習(xí)下如何用函數(shù)實現(xiàn)對文本的排序，主要用的是countif函數(shù)。還是看一個例子。

1.以姓名為關(guān)鍵字進行升序排序

如下圖所示，將左表變?yōu)橛冶?，F(xiàn)列是姓名的首字母，是我自己添加的，為了方便大家比較。如果用基礎(chǔ)操作來完成，十分簡單，點下排序就可以了。如果用函數(shù)來實現(xiàn)呢？還是有點小小的困難。我們之前的文章說過對數(shù)字的排序，今天就在那個的基礎(chǔ)上說說對文本的排序。首先要把文本變?yōu)閿?shù)字，并且數(shù)字要代表文本的排名大小，然后再按對數(shù)字排序的思路完成就可以了。

在D3單元格輸入公式=INDEX(A:A,MOD(SMALL(COUNTIF($A$3:$A$12,"<="&$A$3:$A$12)/1%+ROW($3:$12),ROW(A1)),100))，按ctrl+shift+enter三鍵結(jié)束，向右向下填充，完成。

先來看=COUNTIF($A$3:$A$12,"<="&$A$3:$A$12)這部分，返回的結(jié)果如下圖C列所示，意思就是在$A$3:$A$12這個區(qū)域中統(tǒng)計，小于等于"吳云"的有幾個；小于等于"吳力杰"的有幾個；小于等于"曾海山"的有幾個，...以此類推。因為第2參數(shù)是個數(shù)組，所以就相當(dāng)于對每個姓名計算一次。得到的結(jié)果是按最小值的順序排序的，比如8代表的是第8個最小值，1代表的就是第1個最小值。因為我們是按升序排序，所以要得到的第1個姓名是1對應(yīng)的"曾海山"。

=COUNTIF($A$3:$A$12,"<="&$A$3:$A$12)/1%+ROW($3:$12)這部分是用上一步排名的結(jié)果乘以100再加上對應(yīng)的行號，結(jié)果如下圖C列所示。這樣的話就把排名和行號結(jié)合起來了，比如"曾海山"對應(yīng)的105代表的意思是第1個最小的姓名在第5行。接下來我們就要依次提取第1個最小值，第2個最小值，。。。一直提取完。

=SMALL(COUNTIF($A$3:$A$12,"<="&$A$3:$A$12)/1%+ROW($3:$12),ROW(A1))這部分就是用small提取出上一步的第1個最小值105，公式下拉，提取第2個最小值210；再下拉提取第3個最小值307，。。。一直提完。這樣的話姓名就是按從小到大的順序排列的。接下來就是從名次和行號組合的數(shù)字中取出行號，也就是后2位數(shù)字。

=MOD(SMALL(COUNTIF($A$3:$A$12,"<="&$A$3:$A$12)/1%+ROW($3:$12),ROW(A1)),100)這部分就是用mod除以100取出行號，當(dāng)然你也可以用right從右邊取出2位。再下來就是用index返回對應(yīng)行列的內(nèi)容。

=INDEX(A:A,MOD(SMALL(COUNTIF($A$3:$A$12,"<="&$A$3:$A$12)/1%+ROW($3:$12),ROW(A1)),100))這部分就是用index返回A列對應(yīng)的內(nèi)容。如下圖C列所示，這樣就得到了升序排序的姓名。公式右拉得到了B列對應(yīng)的部門。