在我们日常工作中,有关查找的问题会非常多,比如根据姓名查找身份证,根据工号查找职务,根据学号查找成绩等等。 说到查找函数,大部分人都会想到使用VLOOKUP函数,但是VLOOKUP函数在使用过程却存在许多问题。那么究竟如何才能使用好VLOOKUP函数?今天我们来探讨一下关于查找函数的使用。 Vlookup语法: Vlookup(根据什么找,到哪里找,找哪个,怎么找) =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) lookup_value:要查找的值,也被称为查阅值。 table_array:查阅值所在的区域。请记住,查阅值应该始终位于所在区域的第一列,这样 VLOOKUP 才能正常工作。例如,如果查阅值位于单元格 C2 内,那么您的区域应该以 C 开头。 col_index_num区域中包含返回值的列号。例如,如果指定 B2:D11 作为区域,那么应该将 B 算作第一列,C 作为第二列,以此类推。 [range_lookup](可选)如果需要返回值的近似匹配,可以指定TRUE;如果需要返回值的精确匹配,则指定 FALSE。如果没有指定任何内容,默认值将始终为 TRUE 或近似匹配。 注意: 1、“根据什么找”中的“什么”一定要位于“到哪里找”区域的第1列! 2、若从“到哪里找”区域中找到多个“什么”,则仅返回第1个找到的“什么”对应的东西; 3、“找哪个”不是实际列号,而是“到哪里找”区域中的第几列,其中,“什么”位于第1列,以此类推; 4、“怎么找”包含0(精确查找)、1或省略(模糊查找),其中,模糊查找时,首列必须升序排列; 可以在I、J、K、L列分别输入VLOOKUP公式,但是数据列较多,就比较麻烦了,现在使用一个公式就能搞定: 公式分析: =VLOOKUP($H$3,$B$3:$F$12,COLUMN(B1),0) 首先,需要在“客户名称”列返回查找区域第2列的值,在“付款金额”列返回查找区域第3列的值……,以此类推,为了实现一个公式就能在不同的列返回对应的数据,结合COLUMN函数,让VLookup的第3参数,即“找哪个”变成动态的,在I3单元格第3参数为2,在J3单元格第3参数为3,就可以解决: 其次,COLUMN函数可以返回指定单元格的列号,COLUMN(B1)返回B1单元格的列号2,由于使用的是单元格相对引用,随着公式向右复制,J3单元格会变成COLUMN(C1),即返回C1单元格的列号3; 最后,再以COLUMN函数的结果作为VLookup函数的第3参数,就能实现让“找哪个”变成动态的了,刚好满足了我们的要求。 同样,使用VLookup函数,根据条件找到多个符合的数据,如:一个订单号记录了订购的多款产品,想根据订单号查找该订单下的所有产品,可以按如下步骤: 第1步:首先构造一个辅助序号列,在A3单元格输入公式,并下拉复制到A12单元格: =(B3=$G$3)+A2 公式分析: B3=$G$3:判断B3单元格的销售订单号是否等于G3单元格的查找订单号,若相同,则返回true,否则返回false; 逻辑值再与A2相加,true相当于1,false和空相当于0,得到截止当前行,查询订单号出现的总次数; 第2步:在H3单元格输入公式: =VLOOKUP(ROW(A1),$A$3:$C$12,3,0) 公式分析: 首先,从下图可以看出,只要查找到1~10(10为查询数据总行数,为某订单可能包含的最多产品数),因此为了查找订单号对应的多个产品,根据在A列中出现的行位置,再找到相应的第3列即C列的订单产品,就搞定了。 其次需要将查找到的第1个产品放入H3列,第2个产品放入H4列,依次向下,直至填完查找订单号包含的所有订单产品; 接着在H3单元格查找A列的序号1,即查询订单号第1次出现的位置,并返回该订单下的第1个产品,H4单元格查找序号2…… 最后,使用ROW函数可以解决以上的问题,在H3单元格使用ROW(A1)作为VLookup的查找条件,ROW(A1)可以返回指定单元格A1对应的行号1,随着公式向下复制,由于A1为相对引用,到H4单元格将变为以ROW(A2)即2作为查询条件; 第3步:为H列处理错误值,修改H3单元格的公式,并下拉复制到H12: =IFERROR(VLOOKUP(ROW(A1),$A$3:$C$12,3,0),"") 公式分析: 首先,因不确定每个查询订单号下到底有多少个产品,因此将上一步的公式从H3单元格一直复制填充到H12,共10格,这样可以查询数据区域的总行数,也就是在某个订单号下最多可能包含的产品个数; 其次某个查询订单号下,一般不会有这么多个产品的,于是上一步的公式就出现了下面的情况: 接着若VLookup的结果出现错误值,则显示空值””,但这些“#N/A”就是没找到第n个产品时出现的错误值,使用IFERROR函数的作用就是屏蔽掉它们。 |
|