編按：

如何根據(jù)一份名單從總名單中篩選出其余的數(shù)據(jù)？譬如，需要從總名單（清單）中把不合格名單（清單）中的數(shù)據(jù)剔除得到一份合格名單（清單），該如何操作呢？文章針對4種場景給出了不同的解決公式。

手里有一份總名單（清單），領導突然拿了一份不合格名單過來，要求制作出合格名單。由于事先總名單中并沒有合格或者不合格的標記項目，所以很多人都會手動篩選制作合格名單。其實，完全可以用函數(shù)公式快速、準確完成篩選。

場景1：總名單和不合格名單均在一個單元格

如圖，現(xiàn)需要在D列篩選出合格產(chǎn)品的數(shù)據(jù)。

在D2單元格輸入公式：
=TEXTJOIN("、",,FILTER(TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",50)),ROW($B$1:$B$10)*50-49,50)),--ISNUMBER(FIND(TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",50)),ROW($B$1:$B$10)*50-49,50)),$C2))=0))

公式解釋：

1.  使用“SUBSTITUTE($B2,"、",REPT(" ",50))”，將B2的分割符號“、”替換為50個空格。形成類似“產(chǎn)品1+50個空格產(chǎn)品1+50個空格……產(chǎn)品10+50個空格”的數(shù)據(jù)。

2.  使用MID函數(shù)“MID(SUBSTITUTE($B2,"、",REPT(" ",50)),ROW($B$1:$B$10)*50-49,50)”，依次提取上述含空格的數(shù)據(jù)，得到如“{"產(chǎn)品1                                               ";"   產(chǎn)品2                                            ";……}”的產(chǎn)品加空格的數(shù)組。注意這里的“$B$1:$B$10”，表示的是總名單數(shù)據(jù)，這里假設只有10個產(chǎn)品數(shù)據(jù)需要處理。大家具體使用時，請根據(jù)實際產(chǎn)品數(shù)量名稱修改。

3.  使用TRIM（MID）函數(shù)，將原來數(shù)組里的空格刪除，形成類似“{"產(chǎn)品A1 ";" 產(chǎn)品A2 ";" 產(chǎn)品A3 ";" 產(chǎn)品A4 ";" 產(chǎn)品A5 ";" 產(chǎn)品A6 ";" 產(chǎn)品A7 ";" 產(chǎn)品A8 ";" 產(chǎn)品A9 ";" 產(chǎn)品A10"}”的數(shù)組。

4.  FIND(TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",50)),ROW($B$1:$B$10)*50-49,50)),$C2)：將每個產(chǎn)品在不合格名單中查找一次，得到數(shù)組{#VALUE!;#VALUE!;1;#VALUE!;5;#VALUE!;#VALUE!;#VALUE!;9;#VALUE!}。錯誤表示產(chǎn)品不在合格名單中，是合格的。
--ISNUMBER函數(shù)將數(shù)組轉化成“{0;0;1;0;1;0;0;0;1;0 }”，0對應合格產(chǎn)品，1對應不合格產(chǎn)品。
再用是否=0的判斷將數(shù)組變成{1;1;0;1;0;1;1;1;0;1 }，1對應合格產(chǎn)品，0對應不合格產(chǎn)品。
最后使用Filter篩選和TEXTJOIN連接。

場景2：總名單和不合格名單均在不同單元格

方法1：FILTER函數(shù)篩選

如下：

在B6單元格輸入公式“=TEXTJOIN("、",,FILTER(B2:K2,COUNTIF(L2:N2,B2:K2)=0))”。

方法2：SUBSTITUTE函數(shù)替換

在B9單元格輸入公式“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("、",,B2:K2),IFS(L2="","",L2=K2,"、"&L2,TRUE,L2&"、"),""),IFS(M2="","",M2=K2,"、"&M2,TRUE,M2&"、"),""),IFS(N2="","",N2=K2,"、"&N2,TRUE,N2&"、"),"")”，下拉即可。

場景3：總名單在不同單元格，不合格名單在一個單元格

如下：

在B6單元格輸入公式=TEXTJOIN("、",,FILTER(B2:K2,--ISNUMBER(FIND($B2:$K2,$L2))=0))。

場景4：總名單在同一單元格，不合格名單在不同單元格中

如下：

定位到F2單元格輸入公式下拉即可。
=TEXTJOIN("、",,FILTER(TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",50)),ROW($B$1:$B$10)*50-49,50)),--ISNUMBER(FIND(TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",50)),ROW($B$1:$B$10)*50-49,50)),C2&D2&E2))=0))。

同樣可以使用多層SUBSTITUTE函數(shù)提取數(shù)據(jù)，如下圖所示。

寫在最后：

1.如果版本支持，優(yōu)先使用FILTER函數(shù)篩選。

如果數(shù)據(jù)在不同的單元格，使用COUNTIF函數(shù)計數(shù)，對同一類型數(shù)據(jù)進行統(tǒng)計標記，然后再以這個作為條件進行篩選。

如果數(shù)據(jù)不是在一個單元格中，使用FIND函數(shù)進行查找并通過ISNUMBER函數(shù)轉化為0和1，然后再進行篩選。

2.如果不支持FILTER函數(shù)，可以使用SUBSTITUTE函數(shù)替換。

如果數(shù)據(jù)在不同的單元格，直接依次替換；如果數(shù)據(jù)在一個單元格，可以使用MID嵌套其他函數(shù)依次提取后替換。這里注意的是對最后一個數(shù)據(jù)的處理，可以使用IFS函數(shù)添加條件，添加不同的替換條件即可。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

強大的篩選函數(shù)FILTER用法集

沒有Textjoin函數(shù)，如何解決提取數(shù)據(jù)的問題？

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權申明：

本文作者ITFANS；同時部落窩教育享有專有使用權。若需轉載請聯(lián)系部落窩教育。