chrisfang的Excel大全

#微函数系列#之VLOOKUP函数&HLOOKUP函数

2011-11-17
作者: chrisfang | 分类: Excel函数公式 | 阅读: 3,451 次浏览 | Tags:
声明: 本站文章均属原创,转载时请标明出处

VLOOKUP函数和HLOOKUP是一对非常著名的查询匹配函数。它们让Excel的数据匹配工作变得简单,同时也让许多的初级用户认识到了Excel函数公式的强大。许多人会把它们作为Excel的入门函数,不认识它们就好像不能算会用Excel一般。

这两个函数的功能与LOOKUP很相近,但用法相对更简单,应用场景也十分常见,因此是使用频度最高的函数之一。

VLOOKUP函数和HLOOKUP函数的区别在于组织查询匹配的方向有所不同,V代表了Vertical表示纵向查询(在目标数据表的首列中从上往下依次查询);H代表了Horizontal表示横向查询(在目标数据表的首行从左向右依次查询)。

函数的第二参数(目标数据表)可以是数组也可以是单元格区域引用,由于函数只在其首列或首行进行查询,因此在构造这个参数时,必须把查找的目标数据选定在此数据表的首行或首列位置。

与此同时,与LOOKUP的二维数组查询方式有所不同的是,VLOOKUP函数和HLOOKUP函数的返回值并不默认是目标数据表的最末列或最末行,而是需要通过第三参数来进行指定。第三参数的数值分别代表了数据表中的相对列序号和行序号。这样的方式意味着这两个函数的返回值必须位于匹配值的右侧(VLOOKUP)或下方(Hlookup),而没有LOOKUP函数在组建查询时那么自由。当然,也可以通过数组的方式改变这种默认情况,具体方法见下文。

这两个函数有精确匹配和近似匹配两种方式,通过第四参数的逻辑值设置来切换。

当第四参数的参数值为False时(False可以用数值0来替代),表示精确匹配,目标数据表中不需要排序,只有与查找值完全匹配时才能返回值,否则返回错误值;

当第四参数的参数值为True时(True可以用非0数值来替代),表示近似匹配,目标数据表中的首列或首行数据需要事先按升序排列(包括数值和文本),然后函数会依照LOOKUP函数的方式进行近似匹配查询,返回小于等于查询值的最大数据。因此,这种近似匹配的查询方式通常会由Lookup函数来取代。

这两个函数可以使用通配符进行模糊匹配,但是区分英文字母大小写,如果需要进行区分大小写的查询匹配,可以使用Lookup函数与Exact函数的嵌套组合来实现。

在实际使用中,这两个函数只能单方向查询的限制条件让许多问题的解决变得困难,但还是可以通过构造数组的方式来间接地解决这个问题,使VLOOKUP函数和Hlookup函数也能够反向进行匹配查询。

例如,假定查询目标的匹配值位于C列,而需要返回A列的对应值,可以这样书写公式:

=VLOOKUP(查找值,IF({1,0},C1:C10,A1:A10),2,0)

这个公式的关键部分在于第二参数使用IF({1,0},匹配数组,返回数组)的形式来构建了一个内存中的数据表,这个数据表包含了两列,左边这列就是C1:C10即匹配数组,右边这列就是A1:A10即返回数组。

有些人可能不太理解IF({1,0}的作用,打一个近似的比方,就好比书架上从左到右依次排列着数学课本、语文课本、英语课本、物理课本等等,现在希望将语文课本和物理课本单独拿出来放到另一个书架中,并且要求物理课本在语文课本的左边,就可以通过这样一个公式来实现:IF({1,0},物理课本,语文课本)。

与此类似的,如果要进行横向匹配查询,匹配值位于第5行而需要返回第3行的数据,可以这样构建公式:

=HLOOKUP(查找值,IF({1;0},A5:Z5,A3:Z3),2,0)

 

VLOOKUP函数和Hlookup函数的常见应用场景包括:

1,精确匹配查询。只返回第一条符合条件的数据。

2,包含通配符的模糊匹配查询。同样只返回第一条符合条件的数据。

3,类似Lookup函数的升序序列的近似匹配。例如分数段的划分,可以替代多个IF条件的嵌套。

4,通过构造第二参数的数据表,进行反向匹配查询。

 

点击查看大图

1 2 3 4 5 6 7 8 9 10

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>