哈喽,大家好。 今天来给大家讲解一个筛选不合格名单的问题。 即手里有一份总名单(清单),领导突然拿了一份不合格名单过来,要求制作出合格名单。 由于事先总名单中并没有合格或者不合格的标记项目,所以很多人会选择手动筛选。 效率有点子低,对不对? 没关系,完全可以用函数公式快速、准确完成筛选,下面分别针对可能出现的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)) 公式解释: 5· --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函数添加条件,添加不同的替换条件即可。 |
|