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

《用數(shù)組公式提取一列中的重復(fù)項》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1、用數(shù)組公式提取一列中的重復(fù)項來源：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é)束，然后拖動填充柄向下填充，直

2、到出現(xiàn)“#N/A”錯誤為止。5格子社區(qū)-Excel互助交流平臺????????說明：???用INDEX函數(shù)返回A2:A30區(qū)域中的重復(fù)項，該函數(shù)的行號參數(shù)“row_num”為MATCH函數(shù)。MATCH函數(shù)可在區(qū)域或數(shù)組中搜索指定項，然后返回該項在區(qū)域或數(shù)組中的相對位置。由于本例中包含重復(fù)數(shù)據(jù)的區(qū)域為A2:A30，所以可以用最簡單的“1”和“0”5格子社區(qū)-Excel互助交流平臺來構(gòu)造一個數(shù)組，用MATCH函數(shù)返回“0”在數(shù)組中的位置。???由于要返回重復(fù)項，首先對于A2:A30區(qū)域（包含29個數(shù)據(jù)）中的重復(fù)項，在數(shù)組中的對應(yīng)位置用“

3、0”來表示，非重復(fù)項就用“1”來表示，用COUNTIF函數(shù)和IF函數(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ù)中，它返回一個包含29個元素的一維垂直數(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}???這個數(shù)組可以通過選擇公式中的這部分后按F9鍵來查看。???其次，對于在B列中

4、已出現(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ù)來實現(xiàn)：???COUNTIF($B$1:B5,$A$2:$A$30)5格子社區(qū)-Excel互助交流平臺???上述COUNTIF函數(shù)對區(qū)域中滿足單個指定條件的單元格進行計數(shù)，語法為：???COUNTIF(range,criteria)???本例中的“range”參數(shù)為$B$1:B5，“criteria”參數(shù)為$A$2

5、:$A$30。因需要對其上的區(qū)域進行判斷，還要能向下填充，所以“range”參數(shù)從B1單元格開始，并且采用絕對引用與相對引用混合的方式，填充到B6單元格，就是$B$1:B5。該函數(shù)將對$A$2:$A$30區(qū)域中的每個數(shù)據(jù)（29個），都統(tǒng)計其在區(qū)域$B$1:B5中出現(xiàn)的次數(shù)，由于$B$1:B5區(qū)域中沒有重復(fù)項，所以它返回另一個由“0”和“1”組成的、包含29個元素的一維垂直數(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}???將上述兩個數(shù)組合并，即：??

6、?COUNTIF($B$1:B5,$A$2:$A$30)+IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,0,1)???返回數(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ù)組中第一個“0”出現(xiàn)的位置，此時公式可理解為：5格子社區(qū)-Excel互助交流平臺???=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;

7、1;1;1;1;1;1;1},0))???即：???=INDEX($A$2:$A$30,12)???最終B6單元格中的公式返回“歐陽鋒”。???在B7單元格中，由于合并后的數(shù)組中元素都為“1”，MATCH函數(shù)找不到“0”，故返回“#N/A”錯誤，表示重復(fù)項已全部列出。???另外，如果要在其他列中顯示所提取的重復(fù)項，例如要在F列中從F7單元格開始提取，需將公式中用紅色標(biāo)識的“$B$1:B1”改為“$F$6:F6”，數(shù)組公式輸入完畢后再向下填充。5格子社區(qū)-Excel互助交流平臺