等級(jí):初學(xué)者
-
積分:0
-
財(cái)富值:0.00
-
身份:普通用戶
用數(shù)組公式提取一列中的重復(fù)項(xiàng)
如果Excel工作表的某列中包含有重復(fù)的數(shù)據(jù)，要提取該列中所有重復(fù)的數(shù)據(jù)，可以用下面的數(shù)組公式。假如數(shù)據(jù)在A2:A30區(qū)域中，現(xiàn)在要在B列中提取該區(qū)域中有重復(fù)的數(shù)據(jù)，在B2單元格中輸入下列數(shù)組公式：
=INDEX($A$2:$A$30,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$30)+IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,0,1),0))
公式輸入完畢后按Ctrl+Shift+Enter結(jié)束，然后拖動(dòng)填充柄向下填充，直到出現(xiàn)“#N/A”錯(cuò)誤為止。
說明：
用INDEX函數(shù)返回A2:A30區(qū)域中的重復(fù)項(xiàng)，該函數(shù)的行號(hào)參數(shù)“row_num”為MATCH函數(shù)。MATCH函數(shù)可在區(qū)域或數(shù)組中搜索指定項(xiàng)，然后返回該項(xiàng)在區(qū)域或數(shù)組中的相對(duì)位置。由于本例中包含重復(fù)數(shù)據(jù)的區(qū)域?yàn)锳2:A30，所以可以用最簡(jiǎn)單的“1”和“0”來(lái)構(gòu)造一個(gè)數(shù)組，用MATCH函數(shù)返回“0”在數(shù)組中的位置。
由于要返回重復(fù)項(xiàng)，首先對(duì)于A2:A30區(qū)域（包含29個(gè)數(shù)據(jù)）中的重復(fù)項(xiàng)，在數(shù)組中的對(duì)應(yīng)位置用“0”來(lái)表示，非重復(fù)項(xiàng)就用“1”來(lái)表示，用COUNTIF函數(shù)和IF函數(shù)組合后就可以實(shí)現(xiàn)：
IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,0,1)
其中COUNTIF函數(shù)在本例中的說明見后。由于上述部分公式處于MATCH函數(shù)的“l(fā)ookup_array”參數(shù)中，它返回一個(gè)包含29個(gè)元素的一維垂直數(shù)組：
{1;0;0;1;1;0;0;0;1;0;0;0;1;1;1;1;0;1;1;1;1;0;1;1;1;1;1;0;1}
這個(gè)數(shù)組可以通過選擇公式中的這部分后按F9鍵來(lái)查看。
其次，對(duì)于在B列中已出現(xiàn)的數(shù)據(jù)，也應(yīng)該在數(shù)組中用“1”來(lái)表示將其排除掉。以B6單元格中的公式（返回結(jié)果為“歐陽(yáng)鋒”）為例，對(duì)于B2:B5區(qū)域中已返回的數(shù)據(jù)，數(shù)組中對(duì)應(yīng)的位置要用“1”來(lái)表示，未出現(xiàn)的數(shù)據(jù)用“0”來(lái)表示，這用COUNTIF函數(shù)來(lái)實(shí)現(xiàn)：
COUNTIF($B$1:B5,$A$2:$A$30)
上述COUNTIF函數(shù)對(duì)區(qū)域中滿足單個(gè)指定條件的單元格進(jìn)行計(jì)數(shù)，語(yǔ)法為：
COUNTIF(range, criteria)
本例中的“range”參數(shù)為$B$1:B5，“criteria”參數(shù)為$A$2:$A$30。因需要對(duì)其上的區(qū)域進(jìn)行判斷，還要能向下填充，所以“range”參數(shù)從B1單元格開始，并且采用絕對(duì)引用與相對(duì)引用混合的方式，填充到B6單元格，就是$B$1:B5。該函數(shù)將對(duì)$A$2:$A$30區(qū)域中的每個(gè)數(shù)據(jù)（29個(gè)），都統(tǒng)計(jì)其在區(qū)域$B$1:B5中出現(xiàn)的次數(shù)，由于$B$1:B5區(qū)域中沒有重復(fù)項(xiàng)，所以它返回另一個(gè)由“0”和“1”組成的、包含29個(gè)元素的一維垂直數(shù)組：
{0;1;1;0;0;1;1;1;0;1;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;1;0}
將上述兩個(gè)數(shù)組合并，即：
COUNTIF($B$1:B5,$A$2:$A$30)+IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,0,1)
返回?cái)?shù)組：
{1;1;1;1;1;1;1;1;1;1;1;0;1;1;1;1;1;1;1;1;1;0;1;1;1;1;1;1;1}
然后用MATCH函數(shù)判斷數(shù)組中第一個(gè)“0”出現(xiàn)的位置，此時(shí)公式可理解為：
=INDEX($A$2:$A$30,MATCH(0,{1;1;1;1;1;1;1;1;1;1;1;0;1;1;1;1;1;1;1;1;1;0;1;1;1;1;1;1;1},0))
即：
=INDEX($A$2:$A$30,12)
最終B6單元格中的公式返回“歐陽(yáng)鋒”。
在B7單元格中，由于合并后的數(shù)組中元素都為“1”，MATCH函數(shù)找不到“0”，故返回“#N/A”錯(cuò)誤，表示重復(fù)項(xiàng)已全部列出。
另外，如果要在其他列中顯示所提取的重復(fù)項(xiàng)，例如要在F列中從F7單元格開始提取，需將公式中用紅色標(biāo)識(shí)的“$B$1:B1”改為“$F$6:F6”，數(shù)組公式輸入完畢后再向下填充。