閱： 3764 | 回： 0
等級(jí):初學(xué)者
-
積分:0
-
財(cái)富值:0.00
-
身份:普通用戶
按降序或升序依次提取某列中的文本
在Excel中要按降序或升序排列某列數(shù)據(jù)，通常只需對(duì)其排序即可。在不允許改變表格結(jié)構(gòu)的情況下，對(duì)于數(shù)值類型的數(shù)據(jù)可用LARGE、SMALL等函數(shù)來(lái)提取，如公式“=LARGE($A$1:$A$20,ROW(A1))”。對(duì)于文本則無(wú)法直接使用LARGE函數(shù)，因?yàn)長(zhǎng)ARGE函數(shù)不支持文本，這時(shí)可用下面的數(shù)組公式。
假如A2:A14中包含一些文本和空單元格，要將其中的文本按字母順序降序提取到B列，如圖所示。
在B2單元格中輸入數(shù)組公式：
=OFFSET($A$1,MATCH(LARGE(COUNTIF($A$2:$A$14,"<="&$A$2:$A$14),ROW(A1)),COUNTIF($A$2:$A$14,"<="&$A$2:$A$14),),)&""
公式輸入后按Ctrl+Shift+Enter結(jié)束，然后填充公式到B14單元格，即可得到與降序排序完全相同的結(jié)果。
公式說(shuō)明：
1.“COUNTIF($A$2:$A$14,"<="&$A$2:$A$14)”比較A2:A14中每個(gè)文本，并返回一個(gè)數(shù)組，本例為“{8;5;0;1;10;6;0;2;4;9;7;0;3}”，其中的每個(gè)數(shù)字對(duì)應(yīng)各自文本，空單元格返回“0”。如“體操 Gymnastic”對(duì)應(yīng)數(shù)字“8”，表示A2:A14中小于等于該文本值的數(shù)量為8個(gè)。
2.LARGE函數(shù)按從大到小的順序（降序）依次提取上述數(shù)組中的各個(gè)數(shù)值，MATCH函數(shù)得到該數(shù)值在數(shù)組中的位置，最后用OFFSET函數(shù)提取文本。由于空單元格會(huì)返回“0”，在公式的末尾加上“&""”將其轉(zhuǎn)換為空。
3.要按字母順序升序提取文本，將公式中的“<”改為“>”，如在B2中輸入公式：
=OFFSET($A$1,MATCH(LARGE(COUNTIF($A$2:$A$14,">="&$A$2:$A$14),ROW(A1)),COUNTIF($A$2:
在Excel中要按降序或升序排列某列數(shù)據(jù)，通常只需對(duì)其排序即可。在不允許改變表格結(jié)構(gòu)的情況下，對(duì)于數(shù)值類型的數(shù)據(jù)可用LARGE、SMALL等函數(shù)來(lái)提取，如公式“=LARGE($A$1:$A$20,ROW(A1))”。對(duì)于文本則無(wú)法直接使用LARGE函數(shù)，因?yàn)長(zhǎng)ARGE函數(shù)不支持文本，這時(shí)可用下面的數(shù)組公式。
假如A2:A14中包含一些文本和空單元格，要將其中的文本按字母順序降序提取到B列，如圖所示。
在B2單元格中輸入數(shù)組公式：=OFFSET($A$1,MATCH(LARGE(COUNTIF($A$2:$A$14,"<="&$A$2:$A$14),ROW(A1)),COUNTIF($A$2:$A$14,"<="&$A$2:$A$14),),)&""
公式輸入后按Ctrl+Shift+Enter結(jié)束，然后填充公式到B14單元格，即可得到與降序排序完全相同的結(jié)果。
公式說(shuō)明：
1.“COUNTIF($A$2:$A$14,"<="&$A$2:$A$14)”比較A2:A14中每個(gè)文本，并返回一個(gè)數(shù)組，本例為“{8;5;0;1;10;6;0;2;4;9;7;0;3}”，其中的每個(gè)數(shù)字對(duì)應(yīng)各自文本，空單元格返回“0”。如“體操 Gymnastic”對(duì)應(yīng)數(shù)字“8”，表示A2:A14中小于等于該文本值的數(shù)量為8個(gè)。
2.LARGE函數(shù)按從大到小的順序（降序）依次提取上述數(shù)組中的各個(gè)數(shù)值，MATCH函數(shù)得到該數(shù)值在數(shù)組中的位置，最后用OFFSET函數(shù)提取文本。由于空單元格會(huì)返回“0”，在公式的末尾加上“&""”將其轉(zhuǎn)換為空。
3.要按字母順序升序提取文本，將公式中的“<”改為“>”，如在B2中輸入公式：
=OFFSET($A$1,MATCH(LARGE(COUNTIF($A$2:$A$14,">="&$A$2:$A$14),ROW(A1)),COUNTIF($A$2:
我的個(gè)性簽名