用數(shù)組公式提取一列中的重復(fù)項(xiàng)

來源：excel格子社區(qū)

如果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ù)的行號參數(shù)“row_num”為MATCH函數(shù)。MATCH函數(shù)可在區(qū)域或數(shù)組中搜索指定項(xiàng)，然后返回該項(xiàng)在區(qū)域或數(shù)組中的相對位置。由于本例中包含重復(fù)數(shù)據(jù)的區(qū)域?yàn)?span lang="EN-US">A2:A30，所以可以用最簡單的“1”和“0”來構(gòu)造一個(gè)數(shù)組，用MATCH函數(shù)返回“0”在數(shù)組中的位置。

    由于要返回重復(fù)項(xiàng)，首先對于A2:A30區(qū)域（包含29個(gè)數(shù)據(jù)）中的重復(fù)項(xiàng)，在數(shù)組中的對應(yīng)位置用“0”來表示，非重復(fù)項(xiàng)就用“1”來表示，用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鍵來查看。

    其次，對于在B列中已出現(xiàn)的數(shù)據(jù)，也應(yīng)該在數(shù)組中用“1”來表示將其排除掉。以B6單元格中的公式（返回結(jié)果為“歐陽鋒”）為例，對于B2:B5區(qū)域中已返回的數(shù)據(jù)，數(shù)組中對應(yīng)的位置要用“1”來表示，未出現(xiàn)的數(shù)據(jù)用“0”來表示，這用COUNTIF函數(shù)來實(shí)現(xiàn)：

    COUNTIF($B$1:B5,$A$2:$A$30)

    上述COUNTIF函數(shù)對區(qū)域中滿足單個(gè)指定條件的單元格進(jìn)行計(jì)數(shù)，語法為：

    COUNTIF(range, criteria)

    本例中的“range”參數(shù)為$B$1:B5，“criteria”參數(shù)為$A$2:$A$30。因需要對其上的區(qū)域進(jìn)行判斷，還要能向下填充，所以“range”參數(shù)從B1單元格開始，并且采用絕對引用與相對引用混合的方式，填充到B6單元格，就是$B$1:B5。該函數(shù)將對$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單元格中的公式返回“歐陽鋒”。

    在B7單元格中，由于合并后的數(shù)組中元素都為“1”，MATCH函數(shù)找不到“0”，故返回“#N/A”錯(cuò)誤，表示重復(fù)項(xiàng)已全部列出。

    另外，如果要在其他列中顯示所提取的重復(fù)項(xiàng)，例如要在F列中從F7單元格開始提取，需將公式中用紅色標(biāo)識的“$B$1:B1”改為“$F$6:F6”，數(shù)組公式輸入完畢后再向下填充。