编按：如何根据一份名单从总名单中筛选出其余的数据？譬如，需要从总名单（清单）中把不合格名单（清单）中的数据剔除得到一份合格名单（清单），该如何操作呢？文章针对4种场景给出了不同的解决公式。手里有一份总名单（清单），领导突然拿了一份不合格名单过来，要求制作出合格名单。由于事先总名单中并没有合格或者不合格的标记项目，所以很多人都会手动筛选制作合格名单。其实，完全可以用函数公式快速、准确完成筛选。场景1：总名单和不合格名单均在一个单元格如图，现需要在D列筛选出合格产品的数据。在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个空格。形成类似“产品1+50个空格产品1+50个空格……产品10+50个空格”的数据。2.使用MID函数“MID(SUBSTITUTE($B2,"、",REPT(" ",50)),ROW($B$1:$B$10)*50-49,50)”，依次提取上述含空格的数据，得到如“{"产品1";"产品2";……}”的产品加空格的数组。注意这里的“$B$1:$B$10”，表示的是总名单数据，这里假设只有10个产品数据需要处理。大家具体使用时，请根据实际产品数量名称修改。3.使用TRIM（MID）函数，将原来数组里的空格删除，形成类似“{"产品A1 ";" 产品A2 ";" 产品A3 ";" 产品A4 ";" 产品A5 ";" 产品A6 ";" 产品A7 ";" 产品A8 ";" 产品A9 ";" 产品A10"}”的数组。4. FIND(TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",50)),ROW($B$1:$B$10)*50-49,50)),$C2)：将每个产品在不合格名单中查找一次，得到数组{#VALUE!;#VALUE!;1;#VALUE!;5;#VALUE!;#VALUE!;#VALUE!;9;#VALUE!}。错误表示产品不在合格名单中，是合格的。--ISNUMBER函数将数组转化成“{0;0;1;0;1;0;0;0;1;0 }”，0对应合格产品，1对应不合格产品。再用是否=0的判断将数组变成{1;1;0;1;0;1;1;1;0;1 }，1对应合格产品，0对应不合格产品。最后使用Filter筛选和TEXTJOIN连接。场景2：总名单和不合格名单均在不同单元格方法1：FILTER函数筛选如下：在B6单元格输入公式“=TEXTJOIN("、",,FILTER(B2:K2,COUNTIF(L2:N2,B2:K2)=0))”。方法2：SUBSTITUTE函数替换在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函数提取数据，如下图所示。1.如果版本支持，优先使用FILTER函数筛选。如果数据在不同的单元格，使用COUNTIF函数计数，对同一类型数据进行统计标记，然后再以这个作为条件进行筛选。如果数据不是在一个单元格中，使用FIND函数进行查找并通过ISNUMBER函数转化为0和1，然后再进行筛选。2.如果不支持FILTER函数，可以使用SUBSTITUTE函数替换。如果数据在不同的单元格，直接依次替换；如果数据在一个单元格，可以使用MID嵌套其他函数依次提取后替换。这里注意的是对最后一个数据的处理，可以使用IFS函数添加条件，添加不同的替换条件即可。做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！学习交流请加微信hclhclsc进群领取资料强大的筛选函数FILTER用法集没有Textjoin函数，如何解决提取数据的问题？Excel教程：如何制作带有层次和透视感的图表？八大查找函数公式，轻松搞定数据中的多条件查找版权申明：本文作者ITFANS；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。