欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  Office

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果

程序员文章站 2022-06-18 16:58:48
今天和大家分享的主题是Excel函数常用的各种条件查询套路总结,内容涵盖了单条件数据查询、多条件数据查询、精确查询、模糊条件查询、多结果数据查询以及动态表格查询、多...

今天和大家分享的主题是Excel函数常用的各种条件查询套路总结,内容涵盖了单条件数据查询、多条件数据查询、精确查询、模糊条件查询、多结果数据查询以及动态表格查询、多表数据查询等。建议收藏哦~

1,单条件单结果查询

酱紫的问题,我们最常用VLOOKUP函数。

VLOOKUP函数的语法非常简单。

=VLOOKUP(找谁,在哪里找,查找值在查找范围中的第几列?0失误找还是随便找?)

但该函数有3个新手容易犯错的注意点:

1,  查找值必须在查找范围的首列。

2,  第3参数是返回查找值在查找范围中的第几列,而不是表格的第几列。

3,  第4个参数是匹配方式,当使用精确匹配方式时,通常输入0。

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果

当查找值不在查找范围首列时,与其选择VLOOKUP函数,不如使用INDEX+MATCH函数组合。

公式:

=INDEX(A:A,MATCH(D2,B:B,0))

MATCH函数先取得D2单元格的值在B列中的序列号,然后通过INDEX函数按图索骥取得结果。

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果

有一部分表亲此时依然喜欢使用VLOOKUP函数:

{=VLOOKUP(D2,IF({1,0},B:B,A:A),2,0)}

坦白说,该数组公式计算效率和编写体验都奇差无比,是2B青年糊弄小白新手的常用伎俩,少用为宜。

2,多条件单结果查询

这样的问题我们通常使用LOOKUP函数。该套路可以总结为:

=LOOKUP(1,0/(条件1*条件2……),结果区域)

如下图所示,C10单元格公式:

=LOOKUP(1,0/((A$2:A$7=E2)*(B$2:B$7=F2)),C$2:C$7)

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果

3,单条件模糊查询

Excel内常用的通配符有两个。一个星号(*),一个问号(?)。

星号可以代替任意个字符,既可以是0个,也可以是多个。

问号只能代替任意一个字符。

MATCH函数和VLOOKUP函数都支持通配符的使用。

如下图所示,两个函数都可以解决问题。

公式1:=VLOOKUP("*"&D2&"*",A:B,2,0)

公式2:=INDEX(B:B,MATCH("*"&D2&"*",A:A,0))

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果

4,多条件模糊查询

多条件模糊查询依然可以使用LOOKUP(1,0/(条件),结果)的经典套路。

下面的公式利用SEARCH函数支持使用通配符的特性(FIND函数不支持通配符),进行模糊查询匹配。

公式:

=LOOKUP(1,0/SEARCH("*"&A10&"*"&B10&"*",A$2:A$7&B$2:B$7),C$2:C$7)

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果

5,多结果数据查询:

该问题依然有一个经典的万金油函数查询套路,INDEX+SMALL+IF。

公式:

=INDEX($B:$B,SMALL(IF($A$1:$A$7=$D$2,ROW($1:$7)),ROW(A1)))

解说:

公式先通过IF函数判断A1:A7的数据是否等于D2单元格的查询值,如果相等,则返回对应值的行号,否则返回逻辑值FALSE;然后通过SMALL函数对行号从小到大取值,最后通过INDEX函数根据行号按图索骥取得最后的查询结果。

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果

 6,动态表格数据查询

当根据单元格所输入或选择的表名,动态性的对相应的表格进行数据查询时,我们通常嵌套INDIRECT函数构建动态查询区域。

如下图所示,根据B1单元格输入的表名,查询B2单元格在该表所对应的成绩结果。

公式:

=VLOOKUP(B2,INDIRECT(B1&"!A:B"),2,0)

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果

7,多表格数据查询

一个工作簿有多个表格,当你并不知道该查询值可能存在哪个表格时,可以使用以下多表数据查询套路。

假设表格名称是1~12月。

公式:

=VLOOKUP(B1,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT(ROW($1:$12)&"月!A:A"),B1),ROW($1:$12)&"月!a:b")),2,0)

解说:

该公式先通过INDIRECT(ROW($1:$12)&"月!A:A")构建多表数据区域,通过COUNTIF判断查询值在多表数据区域(A列)是否存在,然后通过LOOKUP的条件查询套路返回相应的表格名称,最后再使用INDIRECT函数对LOOKUP的表名查询结果构建VLOOKUP函数的查询区域,进而取得最后查询结果

Excel 各种条件查询函数 单条件 多条件 精确 模糊 多结果