使用公式對數(shù)字進(jìn)行排序
下圖12展示了兩個對數(shù)字進(jìn)行排序的公式。使用SMALL/ROWS函數(shù)從小到大排序，使用LARGE/ROWS函數(shù)從大到小排序。
圖12
使用輔助列公式對基于數(shù)字列的記錄進(jìn)行排序
如果目的是基于數(shù)字排序記錄，可以使用輔助列來完成。下圖13展示了如何在輔助列中使用RANK和COUNTIF函數(shù)。注意，COUNTIF函數(shù)用來統(tǒng)計(jì)公式所在單元格之前的區(qū)域中該單元格值出現(xiàn)的次數(shù)，這使得區(qū)域中相同的數(shù)字根據(jù)出現(xiàn)的順序給出順序值，而不是RANK函數(shù)給出的相同的順序值。這使得后面使用INDEX/MATCH/ROWS函數(shù)提取記錄更容易。
圖13
使用數(shù)組公式對基于數(shù)字列的記錄進(jìn)行排序
下面的公式?jīng)]有借助輔助列，而是使用數(shù)組公式對基于數(shù)字列的記錄排序，如下圖14所示。注意，第2個公式中COUNTIF函數(shù)解決了重復(fù)數(shù)字問題。
圖14
使用公式提取前3名的成績以及與這些成績相關(guān)的名字
在商業(yè)和運(yùn)動中經(jīng)常要提取排在前n位的值及相關(guān)的名字。與前面的示例不同，不是排序并顯示所有值，而是前幾個值。如下圖15所示，在單元格A11中的公式確定要顯示的記錄數(shù)。在Excel2010及以上版本中，可使用AGGREGATE函數(shù)來提取記錄，如果是之前的版本，可以使用SMALL函數(shù)。
圖15
使用輔助列對基于文本列的記錄進(jìn)行排序
如果想基于文本列排序，可以借助輔助列，如下圖16所示。
圖16
使用數(shù)組公式提取唯一值列表并排序混合數(shù)據(jù)
下圖17展示了從混合數(shù)據(jù)中提取唯一值并排序的公式。
圖17
一個超級長的公式！下面簡要講解該公式。
首先，排序結(jié)果基于Excel的排序順序和ASCII字符，其中對于升序排序來說，Excel排序順序?yàn)閿?shù)字、文本（包括空文本字符串）、FALSE、TRUE、錯誤值、空單元格。有255個ASCII字符，相應(yīng)使用數(shù)字1-255表示。
如下圖18所示，對于單元格區(qū)域A2:A5中的數(shù)據(jù)，升序排列后的結(jié)果顯示在單元格區(qū)域G2:G5。而單元格區(qū)域C2:C5中的數(shù)字表示，如果排序該列表，有幾個數(shù)據(jù)在你的前面。例如，對于單元格A2中的數(shù)據(jù)（54678）來說，處于排序后的列表頂部，沒有數(shù)據(jù)在其前面，因此為0；而數(shù)據(jù)（SD-987-56）在排序后有3個數(shù)據(jù)在其前面，因此為3。在公式中需要能夠創(chuàng)建出這些數(shù)字。
圖18
要創(chuàng)建單元格區(qū)域C2:C5中的數(shù)字，我們先來進(jìn)行一些運(yùn)算操作。
選擇單元格區(qū)域E1:H1，輸入數(shù)組公式：
=TRANSPOSE(A2:A5)
如下圖19所示。
圖19
接下來，選擇單元格區(qū)域E2:H5，輸入數(shù)組公式：
=A2:A5>E1:H1
如下圖20所示。
圖20
結(jié)果如下圖21所示，為由TRUE和FALSE組成的矩形數(shù)組，對應(yīng)著A2:A5中的值與E1:H1中相應(yīng)的值比較后的結(jié)果值。注意到單元格區(qū)域E3:H3，有3個TRUE值和1個FALSE值，將3個TRUE值相加，結(jié)果為3，與上圖18中的數(shù)字3相對應(yīng)，表明該值前面有3個數(shù)據(jù)。
圖21
下面，通過在公式前面加上雙減號將TRUE和FALSE值轉(zhuǎn)換為1和0，然后將結(jié)果相加，得到想要的數(shù)值組成的數(shù)組：{0;3;0;2}。如下圖22、23、24和25所示。
圖22
圖23
圖24
圖25
使用--(A2:A5>TRANSPOSE(A2:A5))代替公式中的E2:H5，得到公式
=MMULT(--(A2:A5>TRANSPOSE(A2:A5)),{1;1;1;1})
使用ROW(A2:A5)^0代替公式中的數(shù)組常量：
=MMULT(--(A2:A5>TRANSPOSE(A2:A5)),ROW(A2:A5)^0)
如果不希望空單元格導(dǎo)致公式失敗，使用IF(A2:A5<>””,A2:A5)代替公式中的A2:A5：
=MMULT(--(IF(A2:A5<>””,A2:A5)>TRANSPOSE(IF(A2:A5<>””,A2:A5))),ROW(A2:A5)^0)
由于公式要復(fù)制到其他單元格，需要將單元格修改為絕對引用：
=MMULT(--(IF($A$2:$A$5<>””,$A$2:$A$5)>TRANSPOSE(IF($A$2:$A$5<>””,$A$2:$A$5))),ROW($A$2:$A$5)^0)
因?yàn)樯厦娴墓讲糠衷谧罱K的公式中出現(xiàn)了兩次且過長，可以將其定義為名稱，然后在公式中使用。如下圖26所示，定義名稱HMA。
圖26
下面，創(chuàng)建公式中的提取數(shù)據(jù)部分。如下圖27所示，在單元格A11中的公式有如下元素：
1.INDEX函數(shù)的參數(shù)array包含需要查找的數(shù)據(jù)所在的單元格區(qū)域。
2.第一個MATCH函數(shù)告訴INDEX要查找的數(shù)據(jù)項(xiàng)的相對位置。
3.暫時將MATCH函數(shù)的參數(shù)lookup_value的值留為空。
4.指定MATCH函數(shù)的參數(shù)lookup_array的值為定義的名稱HMA。
5.指定MATCH函數(shù)的參數(shù)match_type為0，進(jìn)行精確匹配查找，因?yàn)橛兄貜?fù)值。
圖27
在為MATCH函數(shù)指定參數(shù)lookup_value之前，必須考慮應(yīng)該指定什么。有3個唯一值要排序，需要為lookup_value指定3個數(shù)字，隨著公式向下復(fù)制時傳遞正確的相對位置給INDEX函數(shù)：
1.在單元格A11中，MATCH函數(shù)需要查找數(shù)字0，從定義的名稱HMA中報(bào)告相對位置1。
2.當(dāng)公式向下復(fù)制到單元格A12中，MATCH函數(shù)需要查找數(shù)字2，從定義的名稱HMA中報(bào)告相對位置4.
3.當(dāng)公式復(fù)制到單元格A13中時，MATCH函數(shù)需要查找數(shù)字3，從定義的名稱HMA中報(bào)告相對位置2。
如下圖28所示，公式中的元素：
MIN(IF(ISNA(MATCH($A$2:$A$5,A$10:A10,0)),HMA))
當(dāng)公式向下復(fù)制時傳遞合適的最小數(shù)值。這是公式中的關(guān)鍵點(diǎn)（排除已經(jīng)提取的值，取未提取且排名靠前的值），值得細(xì)細(xì)研究。
圖28
添加更多的元素到MIN函數(shù)中，避免空單元格影響公式運(yùn)行，如下圖29所示。
圖29
下圖30展示了最終的公式。
圖30
注：本文為電子書《精通Excel數(shù)組公式（學(xué)習(xí)筆記版）》中的一部分內(nèi)容節(jié)選。你可以到知識星球App的完美Excel社群下載這本電子書的完整中文版。
聯(lián)系客服
