龙空技术网

EXCEL表格下拉菜单选项多,巧用函数公式设置关键字匹配对应数据

和我学办公软件 325

前言:

如今你们对“excel匹配任意字符”大概比较重视,你们都想要学习一些“excel匹配任意字符”的相关知识。那么小编在网上网罗了一些关于“excel匹配任意字符””的相关文章,希望咱们能喜欢,各位老铁们快快来学习一下吧!

在EXCEL表格里制作下拉菜单是常有的事,可以简便数据录入的工作量,也可以和VLOOKUP函数等搭配使用便于动态查询数据。

如果下拉的选项比较多,我们能否输入开头字符后,仅显示出以输入字符开头的选项呢?

如图所示,左侧表格是所有产品的库存表,产品编号有A、B、C、D等开头的编号,右侧我们制作一个动态查询的表格,这里E2单元格内输入A01,下拉选项里就只会显示出A01开头的产品编号,其他的不符合条件就不再显示,如果输入B01则仅显示B01开头的编号,下面我们来看看如何实现?

选中E2单元格,点击菜单栏上“数据-数据验证-数据验证”。

弹出窗口,点击验证条件,选择序列,点击来源,输入函数公式“=OFFSET($A$1,MATCH(E2&"*",A2:A54,0),,COUNTIF(A2:A54,E2&"*"))”。

我们先来看结果,后面再来详解这个公式的意思。

点击确定,顺便把公式复制到G2单元格内,方便我们更加直观的查看公式结果。

在E2单元格内输入A01,弹出错误提示,这是因为我们输入的值和单元格定义的数据验证限制不匹配。我们还需要修改下这个提示。

重新打开数据验证窗口,在出错警告标签下方,取消勾选“输入无效数据时显示出错警告”。

点击确定后,我们在E2单元格内输入产品编号的开头,如输入“A0100”,现在下方显示出的产品编号并不完全正确,为啥?因为左侧表格里的产品编号没有进行排序。

对左侧表格数据按产品编号进行升序排列后,右侧E2单元格内输入A0100,这回下方的选项列表就是正确的了,仅显示出A0100开头的编号了。

下面我们来看下前面公式的意思:

=OFFSET($A$1,MATCH(E2&"*",A2:A54,0),,COUNTIF(A2:A54,E2&"*"))

这里有3个函数:offset,match,countif.

OFFSET函数:以指定的引用为参照,通过给定偏移量得到新的引用。

这里以A1单元格为参照引用,

第二个参数是偏移的行,这里使用“MATCH(E2&"*",A2:A54,0)”函数公式,指定出符合E2单元格内数据的第一个值所在的行位置。

MATCHA函数在这也算是标准用法,只是第一个参数使用“E2&"*"”使用连接符将E2单元格内容和通配符连接起来,表示以E2单元格内容开头的数据。

第三个参数是偏移的列,这里省略。

第四个参数是返回指定的行数,这里使用“COUNTIF(A2:A54,E2&"*")”函数公式,也就是通过COUNTIF函数来计算产品编号这一列里,E2单元格内容开头的数据一共有多少行,有多少行就返回多少行,这也是为啥前面我们说要将产品编号列进行排序了,不排序的话,返回的数据就不准确。

接下来是获取库存数据。

在F2单元格内使用VLOOKUP函数,根据E2单元格的产品编号在左侧表格里查找出对应的库存。

这是VLOOKUP函数的最基础用法,根据语法结构套用这个函数公式就行。四个参数,第一个E2单元格内的编号是查找值,整个表格是查找区域,查找的是第2列数据,使用精准匹配。

如图所示,输入A01,下拉可选择A01004,F2单元格内就会显示出对应的库存数据。

怎么样,对于下拉列表比较多的数据,这样是不是更方便些呢?但要注意的是,这个函数公式,仅能输入以XX开头的关键字,否则数据就不准确。

标签: #excel匹配任意字符