Vlookup函数的使用方法和实操案例,一文精解秒看懂(入门+进阶+高级) | 您所在的位置:网站首页 › vlookup与column减1 › Vlookup函数的使用方法和实操案例,一文精解秒看懂(入门+进阶+高级) |
写在开头:这是一篇超级大干货,全文覆盖了从入门、进阶到高级人士学习VLOOKUP函数的不同实操演示案例,希望可以快速帮你实现从Excel菜鸟到大神的阶层跨越。 在日常工作中,有时候需要找到与某个信息相匹配的数据,但人工逐条去查找既费时费力又容易出错。 比如,在庞大的员工信息表中,怎样才能快速找到某个特定员工的职务/工资/年龄等特定信息? 今天小编就来给大家介绍一个非常好用又高大上的函数——VLOOKUP函数。 【小编福利】点击领取2000+套Excel数据可视化大礼包,纯免费 一、Excel小白初识VLOOKUP函数VLOOKUP是Excel中的一个纵向查找函数,它的功能是按列查找,可以用来查找数据,在多个表格之间核对数据、传递数据。 VLOOKUP函数语法如下: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) “lookup_value”表示要查找的值,可输入数值、引用或文本字符串;“table_array”表示要查找的区域,可输入类型为“数据表区域”;“col_index_num”表示返回数据在查找区域的第几列数,一般输入正整数;“range_lookup”表示“模糊匹配/精确匹配”,可填TRUE/FALSE(或不填)下面,再分别详细解释四个变量的意义: 1.要查找的值,也被称为查阅值。例如,如果需要根据「员工姓名」查找到某位员工的「工资」,则该名员工的「姓名」,则是查阅值。 2. 查阅值所在的区域。请记住,查阅值应该始终位于所在区域的第一列,查阅区域的最后一列是返回值所在的列。这样 VLOOKUP 才能正常工作。例如,如果查阅值位于单元格 C2 内,那么您的区域应该以 C 开头。 3. 区域中包含返回值的列号。例如,如果指定 B2:D11 作为区域,那么应该将 B 算作第一列,C 作为第二列,以此类推计算出该区域共包含多少列。 4.返回近似匹配、精确匹配值。如果需要返回值的近似匹配,可以指定 TRUE(可填写“1”,或者不填写);如果需要返回值的精确匹配,则指定 FALSE(可填写“0”)。 二、实例演示:VLOOKUP函数的应用1、入门篇1.1 精确查找举个栗子 :根据姓名查找对应部门。 输入公式:=VLOOKUP(G2,A:C,3,0) G2:要查找的内容A:C:查找区域,注意查找区域的首列要包含查找的内容3:要返回的结果在查找区域的第3列0:精确查找![]() 有时候我们需要得到的并不是一个确切的值,而是等级的划分。比如,如何根据学生的分数,来确定他们的成绩等级。 这时,我们就可使用近似匹配。近似匹配的返回值是所有“小于查阅值”的数据中“最大的”那一个。 举个栗子 :根据分数查找对应等级。 输入公式:=VLOOKUP(B2,E:F,2,1) B2:要查找的内容E:F:查找区域,注意查找区域的首列要包含查找的内容2:要返回的结果在查找区域的第2列1:近似查找注意查找区域中的首列内容必须以升序排序。 ![]() 举个栗子 :将销售额按3个区间计算提成。 ![]() 使用公式:=A2*VLOOKUP(A2,{600,0.01;900,0.02;1200,0.03},2) 有很多人看到常量数组就晕,其实常量数组不过是由单元格的内容转变而来,将数据填入单元格就一目了然。VLOOKUP函数最后参数省略,就是按区间查找,在写区间的时候,只需将区间下限写出来即可。 =A2*VLOOKUP(A2,$F$2:$G$4,2) ![]() 举个栗子 :按需查找三名员工的性别、年龄、职称、得分信息。 要求查找多人多条信息,这种情况,就需要灵活改动VLOOKUP函数参数,实现用一个公式返回多行多列数据。单元格公式:=VLOOKUP($B18,$C$2:$G$15,COLUMN(B1),0) 公式向下,向右填充,记得到所有要求查找的返回值。 ![]() 举个栗子 :根据部门和职务查找对应姓名。 如下图,需要从A-D的数据表中,根据F2单元格的部门和G2单元格的职务,查询对应的姓名。 H2单元格公式为:=VLOOKUP(F2&G2,IF({1,0},C2:C13&D2:D13,B2:B13),2,) ![]() 举个栗子 :根据公司查找多个员工姓名。 A公司对应有多个姓名,如何通过vlookup直接返回A公司下面所有的姓名呢?直接用vlookup肯定不行,因为正常情况只能返回第一个姓名。 ![]() a.设置辅助列公式。 在A列插入一个辅助列,将每个公司与第几次出现连接起来即可(COUNTIF可以帮助你统计第几次出现)。 在A2单元格中输入公式:=B2&COUNTIF(B$2:B2,B2),然后向下填充即可。 注:COUNTIF的第一个参数要将区域写成B$2:B2,即从B2单元格开始向下下拉的过程,每个公司出现的属于第几次,相当于一个累计计数。&指的是连接符,可以把多个单元格连接在一起,也可以单元格与公式返回的值连接在一起。countif指的是对满足条件的单元格计数。 ![]() b.设置查询公式。 在H2单元格中输入公式:=IFERROR(VLOOKUP($G$2&ROW(A1),A:D,4,0),""),然后向下填充至空白出现即可。 注:查找的目标值即G2单元格须连接一个计数功能的函数为ROW(A1),才能生成正确的与辅助列一致的目标值。最后使用一个屏蔽错误的函数IFERROR将错误转化为空白即可。![]() iferror表示如果公式返回错误值,用一个值来代理。这里用的是""来代替错误值,这样往下拉如果查找不到,就不会显示#n/a错误了,而是空白(""表示空文本,肉眼看上去就是空白) row表示返回行数,A1往下拉分别返回是1、2、3、4、5...... 3.3 反(逆)向查找举个栗子 :要按照门店名称去找对应的门店代码。 有时候我们查找的数据并不是从左向右的,按照原来的方法写入公式后,结果会报错如图: ![]() 究其原因就是因为在数据源(AB两列)中我们的查找条件(门店名称)位于查找值(门店代码)的右侧了,相当于从右向左查找,这时候VLOOKUP就无法得到正确的结果。 这时,你还可以利用vlookup反向查找公式:=VLOOKUP(D2,IF({1,0},$B$2:$B$8,$A$2:$A$8),2,0) ![]() 此外,我们还可以借用辅助列这个工具,来个动画演示看看怎么用辅助列: ![]() 以上就是小编整理的有关Vlookup函数全部干货内容了,希望对您有所帮助。 三、不懂Excel函数,如何实现自定义查找?如果您不是Excel的狂热分子,甚至对Excel的各种函数公式也提不起任何兴致。这种情况下,如何实现想要的快速查找功能呢? 不用慌!小编还给你准备了planB计划——用伙伴云的自定义筛选功能。 全程不涉及任何的函数公式,只需设置不同字段的筛选条件,就可以一键筛选查找想要的数据了,如下图所示。 ![]() 伙伴云自定义筛选体验地址 >>> 新一代在线协作数据管理平台,团队20人以内永久免费使用 下面,我们看下具体操作。 1、添加筛选条件登陆注册伙伴云,创建伙伴云表格及数据。点击表格上方的筛选按钮,如下图所示,选择添加条件或条件组。 ![]() 你可以通过设置条件的筛选逻辑筛选出需要的数据,不同类型字段的筛选逻辑不同。 (1)文本类型字段 文本类型字段包括文本、号码、条码字段等,通过关键词筛选数据。 「=」关系表示数据中的「客户姓名」与筛选关键词完全匹配,才会筛选出数据,一次只能输入一个关键词; 「包含」关系表示数据中的「客户姓名」只要包含其中一个筛选关键词,就会筛选出数据,可以同时输入多个关键词。 ![]() (2)数值类型字段 数值类型字段包括数值、金额、计算字段等,通过指定数值筛选数据。 筛选关系如图所示,其中「~」关系表示「≤值≤」。 ![]() (3)选项类型字段 选项类型字段包括选项、下拉菜单字段,通过备选项筛选数据。 「=」关系表示数据中的「客户进度」与指定的选项完全匹配,才会筛选出数据,一次只能输入一个选项; 「包含」关系表示数据中的「客户进度」只要包含其中一个指定的选项,就会筛选出数据,可以同时输入多个选项。 ![]() (4)日期与时间字段 日期与时间字段通过限定时间范围筛选数据,筛选关系如图所示。其中「~」关系下可以选择动态时间范围。 ![]() 其中「~」关系下可以选择动态时间范围。如图所示,选择「成交日期」为当前1月即可以筛选出本月成交的客户,筛选出的数据会随着时间推移而变化。 ![]() (5)工作区成员字段 工作区成员字段通过成员账户筛选数据,筛选的逻辑关系可以参考选项类型字段。 注意:可以将某成员字段指定为当前用户作为筛选条件,即每个成员查看数据时,系统始终筛选出的是该成员字段中的成员账号等于(或包含)当前登录的成员账号的数据。登录的账号不同,筛选出的数据也不同。![]() (6)关联字段 关联字段可以直接通过指定关联数据来筛选数据,筛选的逻辑关系可以参考选项类型字段。 ![]() 也可以通过关联字段中显示的字段来筛选数据,如图所示。 ![]() 你可以设置多个条件和条件组,条件组中可以添加多个条件。 ![]() 条件之间可以是「且」和「或」的关系。 且:条件之间取交集,即同时满足多个条件的数据才会被筛选出来; 或:条件之间取并集,即只要满足其中一个条件的数据就会被筛选出来。 ![]() 怎么样,你学会了吗?赶紧上手试试去吧~ 伙伴云自定义条件筛选体验地址 >>> 新一代在线协作数据管理平台,团队20人以内永久免费使用 【小编福利】点击领取2000+套Excel数据可视化大礼包,纯免费 以上,记得关注 @伙伴云 哦~ |
CopyRight 2018-2019 实验室设备网 版权所有 |