你还在手动筛选吗?实在是太笨了!4种场景的筛选公式,收好不谢!
哈喽,大家好。
今天来给大家讲解一个筛选不合格名单的问题。
即手里有一份总名单(清单),领导突然拿了一份不合格名单过来,要求制作出合格名单。
由于事先总名单中并没有合格或者不合格的标记项目,所以很多人会选择手动筛选。
效率有点子低,对不对?
没关系,完全可以用函数公式快速、准确完成筛选,下面分别针对可能出现的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!}。错误表示产品不在合格名单中,是合格的。
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函数添加条件,添加不同的替换条件即可。
手机耗电快且发热严重,关闭6个开关电量节省一半,快去关闭吧
站长网2023-07-30 15:36:200000空间转录组|数据读入,标准数据形式外,还有哪些"天残地缺"可以读取
空间转录组测序可以同时获得细胞的空间位置信息和基因表达数据,虽然囿于当前单个spot的精度问题,但是在组织细胞功能,肿瘤生物学、发育过程等需要空间位置的研究领域仍然可以提供很多非常有价值的东西。本节会在spaceranger处理fastq,Seurat处理标准数据(h5spatial文件夹)外,额外提供以下几种情况如何处理站长网2023-07-29 12:31:310002烦烦烦烦!同事发来这样一张Excel表,如何快速拆分整理?
今天主要是来给大家分享一篇关于数据处理的技巧。即将单元格中的内容按照分隔符拆分为多行显示。效果如下:今天主要来教大家3种方法:PQ拆分法空格替换提取数据法OFFICE365函数法方法一:利用PQ拆分单元格内容到多行首先,将鼠标放在任意有数据的单元格中,点击【数据】-【来自表格/区域】,勾选【表格包含标题】,然后确定。站长网2023-07-29 11:11:130000顶级小软件!来3个!
hello大家好,这里是日常爆肝更新的老Y工作室。最新发现了几个不错的小软件,抽空在摸鱼的时候测试了下推荐给大家,觉得有用记得点赞支持下呀01、CAD看图一款小巧免费的CAD快速看图工具,可以进行CAD的一些基础操作,比如面积和长度测量、底图灰显、图层功能、数据导出、图数分离、版本转换、提取文字、打散分解等,内置测量报表可以导出excel。下载地址:蓝奏:/idY4X0w8mm6b站长网2023-07-28 08:42:000003【20230214】为什么微信接收的文件电脑端打开显示“只读模式”?
微信3.9版本的新功能对微信缓存文件夹设置了只读权限到的,可以根据以下方法解决。1、点击微信左下方,设置--文件管理--打开缓存文件夹。2、打开【FileStorage】文件夹,右键点击【file】选择属性,取消勾选【只读】,点保存。之后即可正常通过微信打开文件进行编辑。站长网2023-07-27 13:30:070000