首页 学习资料网络教程正文

韩老师讲过查询函数的使用【问题来源】【】

老师讲过查询函数的使用【问题来源】【】

[问题来源]

原来韩老师讲了很多VLOOKUP查询功能的使用。

VLOOKUP 查询有两个不便之处:

1、查找值(第一个参数)始终在查找区的第一列(第二个参数);

2、从左到右的查找很容易,但是从右到左的查找需要 IF 或 choose 来构造一个新的查找区域。

今天韩老师要讲另一个更厉害的组合:INDEX+match,这个组合不受以上两个条件的限制。

匹配功能】

MATCH函数查找指定项在单元格范围内的相对位置,即哪一行哪一列。

语法:MATCH(lookup_value,lookup_array, [match_type])

中文语法:MATCH(指定项,单元格范围,[匹配方法])

match_type,匹配方法,有三个参数:

MATCH函数是搜索功能的最佳“伙伴”,在与INDEX、VLOOKUP、HLOOKUP的配合中发挥着重要作用

例子

1、检查线路:

在B13中输入公式:=MATCH(A13,A2:A10,0),可以找到不同名字所在的行。

2、查看栏目:

在B6中输入公式:=MATCH(A6,A1:E1,0),可以找到产品所在的行。

【索引函数】

功能:查找单元格区域或数组常量中的行、列或行与列的交集的值

语法:INDEX(array,row_num, [column_num])

中文语法:INDEX(单元格区域或数组常量,数组中的一行,[数组中的列])

例子

1、询问不同销售业绩的销售人员姓名:

公式:=INDEX(A2:A10,MATCH(D2,B2:B10,0))

其中:MATCH(D2,B2:B10,0),为所有销售业绩中D2单元格销售业绩所在行。

2、查询不同销量的产品名称:

公式:=INDEX(A1:E1,MATCH(A6,A2:E2,0))

其中:MATCH(A6,A2:E2,0),即A6电芯销量列。

【INDEX+MATCH组合使用示例

1、寻找表现最好的名字

在单元格 D2 中输入公式:

=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0))

你可以获得最高性能对应的名称。

公式解释:

2、查找性能的一、二、三名

在E2中输入公式:

=INDEX($A$2:$A$10,MATCH(大($B$2:$B$10,ROW(A1)),$B$2:$B$10,0))

公式往下填,得到前三个名字:

公式解释:

该公式中使用LARGE函数求ROW(A1)在单元格B2:B10中的最大值。当公式在单元格E2中时excel提取当前工作表名称,ROW(A1)@ > 为 1 ,往下填的时候会自动变成 ROW(A2), ROW(A3),也就是第二大第三大的值,从而找出前三名。

特别注意:

INDEX+MATCH一起使用时,INDEX的第一个参数区必须和MATCH的第二个参数区的起始行相同,否则会出现错位。

3、求行列交集的值

C13输入公式:=INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0))

您可以实现指定销售人员对指定产品的销售,如下动画所示

公式分析

MATCH(A13,A1:A10,0): 单元格 A13 的名称,在 A1:A10 范围内的行中。

MATCH(B13,A1:E1,0): B13 单元格中的乘积,A1:E1 范围内的列。

INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0)):在A1:E10区域,名字所在的行A13所在列的值与B13相同 产品所在列的交点处的值。

4、提取整行整列

(1),查找指定名称的所有产品的销售额

选择B13:E13区域,输入公式:=INDEX(B2:E10,MATCH(A13,A2:A10,0),0)

ctrl+Shift+Enter结束,如下图:

搜索完成。

(2), 按名称查找指定产品的所有销售额

选择H2:H10区域excel提取当前工作表名称,输入公式:=INDEX(B2:E10,0,MATCH(H1,B1:E1,0))

以Ctrl+Shift+Enter结束,如下图:

搜索完成。