vlookup函数的使用方法,VLOOKUP函数使用八大基本方法

xiaobian xiaobian

vlookup函数的使用方法(VLOOKUP函数使用八大基本方法)VLOOKUP是最常用的查找和引用函数,依据给定的查阅值,在一定的数据区域中,返回与查阅值对应的想要查找的值。 [“本文来自”:www.666z.com]

 

[“转载出处”:www.666z.com]

vlookup函数的使用方法(VLOOKUP函数使用八大基本方法)

函数讲解

语法:

=VLOOKUP(查阅值,包含查阅值和返回值的查找区域,查找区域中返回值的列号,精确查找或近似查找)
参数:

查阅值,也就是你指定的查找关键值
如本示例中,查阅值是F3单元格“林三”,我们要在“姓名”一列中查找“林三”得分,“林三”就是查找的关键值。
包含查阅值和返回值的查找区域。一定记住,查阅值应该始终位于查找区域的第一列,这样 VLOOKUP 才能正常工作。
例如,本示例中,查找区域是$B$2:$D$15,查阅值“林三”所在的“姓名”B列,就是该区域的首列,而且该区域还包括返回值“得分”所在的D列。
查找区域中返回值的列号。
例如,本示例,查找区域$B$2:$D$15中,首列“姓名”是第一列,返回值“得分”是第三列,所以列号是“3”。
精确查找或近似查找。
如果需要精确查找返回值,则指定 FALSE或者0;如果近似查找返回值,则指定TRUE或者1;如果该参数省略,则默认为近似匹配 TRUE 或近似匹配。
本示例中是“0”,为精确查找。
一、需基本查找
在G3单元格输入公式:

=VLOOKUP(F3,$B$2:$D$15,3,0)

确定,即可查找到“林三”的得分;

公式向下填充,即可查找到“陆七”的得分。


如本示例中,查阅值是F3单元格“林三”,我们要在“姓名”一列中查找“林

本示例公式解释:


注意:查找区域的绝对引用:

在公式中,第二个参数“查找区域”,使用的是绝对引用$B$2:$D$15。

绝对引用的作用是:公式填充到其他行列时,该区域不变。

本示例,查找完“林三”的得分,公式向下填充,再去查找“陆七”得分,查找区域始终不应改变,应该是包含所有姓名与得分的B2:D15区域,所以,该区域绝对引用。

二、多行多列查找
比如,以下数据:


要求查找多人多条信息,这种情况,就需要灵活改动VLOOKUP函数参数,实现用一个公式返回多行多列数据。

公式实现过程如下:


我们可以看出,几行几列数据,是用一个公式完成的,该公式是:

=VLOOKUP($B18,$C$2:$G$15,COLUMN(B1),0)

公式向下,向右填充,记得到所有要求查找的返回值。

三、区间查找
如下图:


采购数量不同,所得折扣也不同,如右侧的折扣表。

公式“=VLOOKUP(B2,$E$3:$F$6,2)”,省略了第四个参数,即查找方式,省略就代表把第四个参数设置成TRUE或1,即是近似查找。

近似查找返回值是:比查阅值小且最接近的查询区域首列中的区间值所对应的返回值。

本示例中

比“20”小的值且最接近20的是0,所以返回0对应的区间值“0%”;
比“225”小的值且最接近225的是200,所以返回200对应的区间值“8%”。
区间查找有一最最重要的注意事项:

查找区域的区间值必须是从小到大排列,否则查找不到正确结果。

本示例,区间值0、100、200、300是从小到大依次排列的。

四、等级评定
如下图:


公式“=VLOOKUP(B2,{0,"不合格";60,"合格";70,"良好";85,"优秀"},2)”,省略了第四个参数,即是近似查找。

其中,{0,"不合格";60,"合格";70,"良好";85,"优秀"}是下图数组的变相写法:


等级查找是区间查找的特殊方式,也可以写成区间查找的公式:


五、模糊查找
如下图:


查找G型号系列产品的销量,可以把查找值用通配符表示。此种方法可以查找字符串中含有某个关键值的对应返回值。

六、多条件查找
如下图:


查找仓库二键盘的销量,查找条件必须符合仓库是“仓库二”、商品是“键盘”两个条件。

公式:

=VLOOKUP(E2&F2,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)

按“CTRL+SHIFT+ENTER”键确认,即得结果。

第一个参数:

E2&F2,用文本连接符,将E2单元格“仓库二”与F2单元格“键盘”,连接在一起,形成新的查询条件:仓库二键盘。

第二个参数:

IF({1,0},A2:A13&B2:B13,C2:C13),生成一个新的查询区域:

VLOOKUP函数使用八大基本方法,你一定要掌握!
第三个参数:2,新的查找区域里,返回值在第二列。

第四个参数:0,精确查找。

七、逆向查找
VLOOKUP函数要求查询值必须位于查询区域的首列。比如,下图中的数据:


原数据区,“部门”位于“姓名”的左侧,而要求按照姓名去查询部门,那直接用VLOOKUP函数进行查找,是查不到结果的。

我们需要构建一个新的查询数据区,将“姓名”置于“部门”的左侧。这种新的查询数据区,可以通过IF和CHOOSE两个函数来实现。

IF帮助VLOOKUP实现逆向查询

在E2输入公式:

=VLOOKUP(D2,IF({1,0},B1:B10,A1:A10),2,0),

结果如下图:


其中:

IF({1,0},B1:B10,A1:A10),构造出姓名在前,部门在后的新的查询区域,如下图:


CHOOSE帮助VLOOKUP实现逆向查询

也可在E2输入公式:

=VLOOKUP(D2,CHOOSE({1,2},B1:B10,A1:A10),2,0),

结果如下图:


八、跨表引用数据
如下图,将表1中的消费记录,按名称,引用到表2中:


其实,跨工作表的引用,和同一工作表数据引用方法是一样的,只不过多了一步点击工作表的名称,即选择工作表而已。

引用过程如下:


切记:

在选了引用位置“sheet1!A1:B13”以后,不要再去点回sheet2,除非公式后面需要sheet2中的数据。在公式编辑过程中,鼠标的点击位置会随时记录。

xiaobian微信号:xiaobian扫描二维码关注公众号
爱八卦,爱爆料。
小编推荐
  1. NO.1 片场拍驾马车戏份全靠人动手摇 演员不笑场也太强了

    近日网上曝光了一段驾马车戏的拍摄花絮,视频中演员驾着没有马的“马车”,几个工作人员在旁使劲摇晃车子营造出马车颠簸的感觉。 网友们调侃

  2. NO.2 2019腾讯音乐娱乐盛典什么时候开始?12月8日腾讯音乐娱乐盛典直播入口

    2019腾讯音乐娱乐盛典什么时候开始?哪里可以看直播?哪里可以投票? 2019腾讯音乐娱乐盛典将会在12月8日于澳门开始哦~投票结果是怎样?哪些明星会做到

  3. NO.3 商店火腿肠一夜失踪是怎么回事?为什么商店火腿肠一夜失踪

    商店火腿肠一夜失踪 从老鼠洞里掏出近一箱 12月29日,兰州一商店店主发现刚进的很多火腿肠不见了。大家将仓库的货架搬开后发现一个老鼠洞,竟

  4. NO.4 2020年彩票市场什么时候开市?附公告

    2020年彩票市场什么时候开市?附最新公告 2020年彩票市场什么时候开市? 经国家福彩中心、国家体彩中心研究决定,全国联销彩种开奖将于2020年3月

  5. NO.5 快递价格行为规则是怎么回事?快递价格为什么发行为规则

    禁止快递价格垄断是怎么回事?快递价格行为规则详情 近几年来快递行业发展越来越快,其中各种问题都有,但是相关的制度规定等还不够完善。而

  6. NO.6 谢彬彬为什么不火 起底其详细资料背景及不红原因

    谢彬彬出道多年也带来了多部作品,但不知道为什么就是不火,让人诧异!谢彬彬为什么不火?扒其中缘由,其实与话题度不高也是有关系的,总的

  7. NO.7 三一重工收问询什么情况?三一重工收问询原因

    三一重工收问询什么情况?三一重工收问询原因 三一重工今晚公告,公司拟以自有资金收购控股股东三一集团有限公司持有的三一汽车金融有限公司

  8. NO.8 深南电路预计2019年盈利超11.50亿元

    深南电路(002916.SZ)发布2019年度业绩预告。 预告显示,2019年1月1日至2019年12月31日,深南电路实现归属于上市公司股东的净利润为115,046.65万元128,991.6

Copyright2018.天天资讯网资讯站,让大家及时掌握各行各业第一手热门资讯!