表姐说函数：VLOOKUP

论坛 › 期权论坛 › 期权

 E维课堂  2019-6-8 21:06  2877  0

[/url]

△用VLOOKUP函数制作的订单查询表

△用VLOOKUP函数制作的生产日报

函数的学习，大家一直以来都觉得特别复杂、难懂。然而，Excel的高效办公中，肯定是离不开函数的熟练应用。在函数的学习中，表姐推荐大家，“由难到易”，先从复杂的学起，这样大家学习其他简单的函数，也便变得容易起来。得其一，而知百，比如：先学习复杂的4参数函数。

今天我们就来聊一聊，函数界“人见人爱、花见花开”的查找神器：VLOOKUP函数。

基本语法

VLookup查找和引用函数
公式的写法是：
=VLOOKUP（Lookup_value,Table_array,Col_index_number,Range_lookup）
参数①Lookup_value：要查找的值

参数②Table_array：要在其中查找值的区域
参数③Col_index_number：区域中包含返回值的列号
参数④Range_lookup：精确匹配或近似匹配 – 指定为 0/FALSE or 1/TRUE

【函数功能】：根据被查找值，在查找的数据源区域，按列查询，并返回指定列数下，所对应的值。
要求：函数的第2参数(在选定数据源时)，将被查找的值必须位于选定数据源区域的最左侧。

在B2单元格中，需要根据A2的值（联系人：表姐），在数据源区域（B5：E11）中，查找其对应的第3列的值（身份证号码），是多少，并且要求精确查找。
将上述汉语的逻辑转化为公式：
=VLOOKUP(A2,B5:E11,3,0)
注意：在选定数据源的时候，要求“联系人”列，必须位于最左侧为起始列，因此所选择的区域并非从A5开始选择，而应当从B5开始，往后往下开始选定数据源区域。

小技巧：公式不用背
在单元格内，输入=VL
然后按住【Tab】键，公式自动填充

效果图：

单元格自动补齐了函数，并贴心的带上了左括号（。

对于初学者，可以在输入函数以后，按fx调取函数参数对话框，进行学习。

实例应用

有小伙伴留言提问：
我要核对两张几千行的Excel表中，哪些数据是已经出现过的？哪些是没有出现过的值？现在我是用“CTRL+F”去查找，但特别慢。

答：只需要分别针对两列数据，用VLOOKUP函数去对方的区域中查找，是否包含该值。如果查找的结果是错误值，则证明该数据没有在对方区域中出现过。

B2的公式=VLOOKUP(A2,D:D,1,0)

根据A2的值，在D：D的数据源区域中进行查找，并且返回该数据源区域中的第1列的值，进行精确匹配。
E2的公式=VLOOKUP(D2,A:A,1,0)
根据D2的值，在A：A列中精确查找第1列的值，并将查找结果，返回在E2单元格中。

此时，如果单元格计算的结果，显示为#N/A，则表示：查无此值。

错误分析

①错误值：#N/A（查无此值）
1.空白符
当单元格中包含空白符时，我们往往无法通过肉眼判断，两个单元格的值一致，但在Excel的语言当中，表姐≠表姐

解决方案：
a.用查找替换的方式，将所有空格符，替换为空：

b.用SUBSTITUTE函数，将单元格中的空白，替换为空值""（即连续输入两个英文状态下的双引号）

c.用TRIM函数，删除字符串中多余的空格。

但如上图所示，在C4单元格中，表姐中间包含2个空格时，函数的计算结果，并不能完全有效的清除所有的空白符。

2.文本型数字

很多后台导出的数据中，产品id、编号等，看似是数字格式的，但其单元格格式还是文本。造成VLOOKUP的查询结果，显示错误。

A列，系统导出编号：文本格式

D列，产品编号：常规格式（数值）
解决方案：
a.文本转化为数字：选择性粘贴*1

b.数字转化为文本：分列为文本

3.制表符char(9)
从很多信息化管理系统中导出的表格数据，会包含制表符，它会把数字前，空白单元格中，默认输入制表符。
比如在下图中的G3单元格，看似空白的单元格中，实际上并非完全空白。
如果将G3单元格，复制粘贴到查找对话框中，您会发现有一个" "
并且它不能够通过查找替换来消除。

解决方案：
用分列的方法，去除制表符
注意：在分列向导第三步中，选择【常规】

②错误值：#REF!（区域有误）
当VLOOKUP函数的第二函数选定区域错误时，或第三参数要求返回的列数（3）＞选定区域（2列）时，会出现查询结果为#REF！的错误提示：

解决方案：
修订函数的参数。

③屏蔽错误值：IFERROR
在公式编写时，有时为了版面的整洁，通常会用错误值判定函数，将单元格内的错误值屏蔽，显示为空白等。

公式：=IFERROR(VLOOKUP(A2,$D$2:$E$9,3,0),"")
含义：
如果VLOOKUP(A2,$D$2:$E$9,3,0)查找的结果为错误值，则显示为""
否则显示为VLOOKUP(A2,$D$2:$E$9,3,0)的查找结果

进阶应用
【1】逆向查询
在工作实践中，往往我们的数据表格格式是固定的，被查询的值，不一定会放在最左侧列，这就要求我们通过VLOOKUP函数实现逆向查询的功能。

如上图所示，根据物料编码，查询产品类别、名称等信息。

要打破数据源布局上的约束，我们需要在Excel当中用数组，构建一个从左向右的列表区域，供VLOOKUP函数来调用：

公式：=VLOOKUP(I2,IF({1,0},F:F,B:B),2,0)
重点解析：
通过IF({1,0},F:F,B:B)在Excel中，构建虚拟数组列表，且F列在第1列，B列在第2列。

【2】一对多查询
众所周知，Excel只能针对被查找数据，一对一的进行查询，当数据源中出现多个相同的被查找值时，默认按照从上到下，第一个被查询到的结果，进行返回。

如果需要根据所选部门，将对应该部门的所有报销明细，进行查找呈现，则需要VLOOKUP函数，能够实现一对多的查询。

解决方案：让被查找的值加上序号后，变成唯一值
为每个部门的出现次数，进行编号，即：从上至下，将销售部，分别标识为：
销售部1，销售部2，销售部3，销售部4......
然后再分别查找销售部1~4对应的报金额、报销日期

构建辅助列A列，A6的公式

=C6&COUNTIFS($C$1:C6,C6)
A列辅助列，表示：
C列的每一行，都从$C$1单元格开始，向下至当前行，判断当前行C列（C6）的值，出现的次数。
再用&连接符，将C列（C6）的值和出现的次数，进行连接。
这样就在原来的数据表格基础上，构建了一个唯一值序列。
然后只需要将新构建的辅助列，用作VLOOKUP函数中，进行查询就好：

F5单元格的公式：

=IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$11,2,0),"")
其中row(A1)，表示返回A1单元格的行数，即为1。
这样方便公式向下填充时，序号可以随行数的增加而自动递增。

企业级应用

【数据基础】建立全面的完工日报流水记录

【报表查询】根据完工流水记录，根据所选的日期或月份，实时生成产品完工日报、月报；
使用统计求和函数，可以在月报表的顶部，构建统计汇总表

最重要的是，这份查询表，可以根据明细数据源的增减变化而自动更新。

是的，您只需要把明细表，更换成您公司的产品、业绩、人员招聘、成本支出、费用预算、工程结算等等具体的内容，就可以像本文开头的动图一样，实时查询具体的数据明细了。

如果您认为@表姐凌祯的分享
对您有所启发
欢迎帮助我们转发朋友圈
[个人微信：BJlingzhen QQ:348902122]

标「E维课堂」一秒发现表姐^^

-END-

零基础趣学Excel
如果您期待系统学习Excel技能，与小伙伴们一同：获得表姐私房服务、系统化提高个人office技巧、实现职场晋升，那么欢迎您加入我们：

欢迎点击下方”阅读原文“选择Excel体系化课程，一起成长进步。
[url=http://mp.weixin.qq.com/s?__biz=MzIzMTQxMzg0NA==&mid=2247487475&idx=1&sn=7a0958129ce07820e648a87be3b1d6bb&chksm=e8a5cec8dfd247de660168c395d69e69170dacda6b5696705d3e51a990eb3cfa442a7e002dd8&scene=21#wechat_redirect]

【表姐新书】《数据呈现之美：Excel商务图表实战大全》（电子工业出版社）京东、天猫、当当、亚马逊等各大电商平台均有售。

请您点击“阅读原文”查看课程详细介绍

回复

分享到 :

0 人收藏

2级吧友

积分:15

帖子:3

精华:0

+ 关注私信

期权论坛

期权论坛

期权网络科技版权所有: 关于我们; 联系我们; 加入我们; 反馈问题; 免责声明; 积分充值

统一社会信用代码: 积分规则; 网站地图; 爱文库; 下属网站

官方: 新浪微博; 微信公众号
◆

下载: 表情包; App下载

期权论坛

期权论坛

QQ咨询|关于我们|Archiver|手机版|小黑屋|( 辽ICP备15012455号-4 ) Powered by 期权论坛 X3.2 © 2001-2016 期权工具网&期权论坛 Inc.