表格中录入简称查找包含全称的所有数据 No.274

古哥计划

优秀创作者

某张表格中,源数据B列有上万行,数据源中有不同的公司,现在需要在表格中另外一个区域录入查找关键字,自动查找出包含公司全称的所有数量并去重重复项目,

模拟转换数据结果如下图1所示:

图1

需求分析

这个需求是一个模糊查找并返回对应精准数据的经典案例,很多时候都是用操作来完成的,最简单直接就是筛选,录入关键字,筛选出对应的结果,复制出来,去除重复项目。

现在需要用公式来实现,就相当于一个表格“建模”,公式写好后,就可以在查询单元格录入任意查找关键字,就可以返回对应包含查找值关键字的公司全称了。

要实现这个需求,可以配合FIND查找函数和ISNUMBER函数以及筛选函数FILTER,得到的结果最后用UNIUQE去重函数来实现

FIND查找关键词

先提前在G2录入关键字“木”,再利用FIND函数查询这个木,得到数字和错误值。配合ISNUMBER判断,如果是数字就返回TRUE,不是数字就返回FLASE。分别录入

公式1:C3=FIND(G2,B3:B30)

公式2:D3=ISNUMBER(C3#)

C3#为公式1的动态数组写法,代表FIND查询出来的动态数组结果,公式合并后可以写成:

=ISNUMBER(FIND(G2,B3:B30))

效果如下图2所示:

图2

筛选返回TRUE的结果

上面ISNUMBER返回了逻辑值TRUE和FALSE,这个结果恰好是筛选函数FLTER的第二参数的结果,所以只需要直接嵌套到筛选函数FILTER中即可,录入函数:

=FILTER(B3:B30,D3#)

函数释义:

筛选包含TRUE的结果,并返回对应B3:B30的数据,也就是公司名称数据。

效果如下图3所示:

图3

对结果去重

通过筛选后的结果显示,数据存在重复值,所以需要嵌套去重函数UNIUQE,录入公式:

=UNIQUE(E3#)

函数释义:

对筛选后的结果(E列),进行去重。

效果如下图4所示:

图4

公式合并

通过辅助列分别计算,可以得到上面的结果。已经初步实现了录入关键字(简称),返回包含全称的公司名称了,现在只需要把公式合并,并加把一些“异常”的结果也考虑上去,就可以了;

源数据的范围不可能只有B3:B30,所以这里把个范围定义一个相对大的范围如B3:B3000,当然数据也是可以更改为更大。

第二就是筛选函数中录入的关键词如果在源数据中没有的话,就会返回错误值,所以需要在筛选函数中的第三参数中加入一个判断。

整体公式合并后,录入以下公式:

=UNIQUE(FILTER(B3:B3000,ISNUMBER(FIND(E2,B3:B3000)),"无结果"))

公式释义:

把筛选的显示范围调整为B3:B3000

把筛选不到,返加“无结果”

效果如下图5所示:

图5

和古哥一起学习PMC生产计划运营,一辈子够不够?

关注古哥计划

如何快速提取不同单元格的内容,并汇总到一列 No 273

如何统计指定年和月后,汇总对应产品的销售数据 No272

表格中在一个单元格内有多个条件,如何快速求和 No271

WPS新函数:巧妙利用DROP 去重UNIQUE函数留下的“0” NO 270

快速计算出每名员工参与了几个项目No.269

统计每个城市的唯一商品明细No.268

WPS更新后的TAKE函数轻松实现动态求和 No 267

WPS 新函数 EXPAND 实现工单快速分拆 No 266

WPS 新函数 CHOOSECOLS 快速对齐错乱列 No 265

WPS新函数LET让公式的长度大大的简化了. No.264

自动分配客户对应业务的奖金No.263

快速查询出销售前2的销售员和销售金额并排序 No.262

WPS 新函数 WRAPROWS 把错乱的数据整理成标准二维数据 NO 261

多表指定日期与时间的生产数据查询No.260

WPS 新函数 TOCOL将二维数组转化成一行【No.259】

WPS 新函数 VSTACK 多表查询合并【No.258】

257 一招搞定请假时间的区间转换及人数统计

256 快速匹配不同的采购量对应的结算量

255 WPS新函数案例:复杂的产品欠料运算

254 WPS新函数案例:灵活多变的万年日历

253 WPS新函数案例:指定工号快速筛选

252 WPS新函数案例:员工姓名与工号快速分离

广东省
浏览 805
2
12
分享
12 +1
9
2 +1
全部评论 9
 
牛,太专业了
· 安徽省
回复
 
· 广东省
回复
 
111
· 广东省
回复
 
666
· 广东省
回复
 
发现关键词为空时会筛选出所有数据, 试了一下 =FIND("","字符串") 返回1, 所以FIND字符串时应屏蔽一下空串, 比如把第一个参数E2换成 IF(LEN(E2), E2) 或者 IF(E2<>"", E2)就没问题了.
· 河南省
回复
古哥计划

优秀创作者

是的,这点没考虑到,感谢您的支持
· 广东省
回复
 
Mr Chen

创作者俱乐部成员

学习
· 甘肃省
回复
 
刘航

创作者俱乐部成员

古老师分享的案例都很有代表性,学习了!
· 黑龙江省
回复
古哥计划

优秀创作者

感谢您的支持!
· 广东省
回复