分享

VLOOKUP函数教程 & 简易实例讲解

 老来乐64 2018-12-08

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

序言

操作描述尽量详细,但需要一点点基础,excel纯新人可能会看不明白。

本系列既是写给自己,也是送给大家的小福利,避免遗忘知识点后到处百度。本系列大部分参考都会是Excel帮助文档,例子都是自己编写,结合实际,尽量简化。

常见需求

有时候,会有 需要汇总的数据,分散在两张表格 的情况。

如果两张表,都有某种列,比如都是唯一性数据(比如工号,身份证号),用 VLOOKUP 函数,来合并表格是个方便的选择。

VLOOKUP函数能把关键词作为索引来查找数据。它是一个查找和引用函数。

语法

VLOOKUP ( lookup_value , table_array , col_index_num , [range_lookup])▼

参数名称 说明

lookup_value(必需) 要查找的值。要查找的值必须位于 table-array 中指定的单元格区域的第一列中。

Table_array(必需) VLOOKUP 在其中搜索 lookup_value 和返回值的单元格区域。

col_index_num(必需) 其中包含返回值的单元格的编号(table-array 最左侧单元格为 1 开始编号)。

range_lookup(可选) 选填 TRUE(近似匹配) 或者 FALSE(精确匹配)。

公式举例

= VLOOKUP('王炸',B2:C7,2,FALSE)

说明:在 B2:C7 这个区域内,查找 “王炸” 这个数据,如果有,就定位 “王炸” 所在行,从 B 列 开始往右数,第二列的数据。FALSE 代表可以精确匹配,比如“王炸”可以,但是“王炸之狗”就算另一个数据。

场景图例▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

说明:

1,输入公式时,不要忘记输入 等于号“=”

2,注意,在单元格输入公式,必须是英文输入法,特别注意标点符号一定是半角字符,输入全角或者中文标点可能会出错。只有引号内的数据可以是中文,比如“王炸”、“狗腿”之类。

3,请一定要使用 FALSE 精确匹配模式,使用 TRUE 近似匹配会有意想不到的错误。哪怕是纯数字数据。

4,如果匹配不到数据,会显示 #N/A 。不要怕,这是正常现象,工作助力小集锦 篇一:Excel:用好VLOOKUP函数 。为了显示的更直观,可以人为改进,比如: = IFERROR( VLOOKUP ( '王炸', B2:C7, 2, FALSE), '未找到王炸')。这时候,如果选区内找不到“王炸”,单元格就会显示'未找到王炸'。

实际操作(简化场景)▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

题目:因为业务需要,组织宣布重大任务,需要根据表1和表2,得到 表三——颜值销量关系表(表头如下)。▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

就是说要把颜值和销量放在一张表里,才方便分析颜值和销量的关系。

我们假设有个员工叫小李,由他来操作。

小李发现,表三需要的信息,分散在表1表2里面。表1缺少了销量,多了地址,表2 缺的比较多。所以机智的小李选择改造表1,来获得表三。

步骤:

1,表1和表2,都在sheet1中。小李复制表1,粘贴到sheet2中,去掉了不需要的地址列,添加了销量列。▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

2,小李观察到,工号是两张表共有的数据,且和身份证一样,拥有唯一性,于是就使用工号作为索引。小李在sheet2的 E3 单元格中输入

= VLOOKUP ( C3 , Sheet1!B11:C14 , 2 , FALSE)▼

1,首参数 C3:

表三 的工号。用处是,C3这个单元格内的代表工号的数据(值是“1001”),作为关键词,到表2的工号这个列中查找(找“1001”)。

2,第二个参数,查询区域 Sheet1!B11:C14:

表2中的序号这个列,是不需要的,所以不选用。选取范围是 Sheet1!B11:C14 区域。

3,第三个参数 2:

就是选区中的第二列。这里是从B列开始数第二列,就是C列——销量列。

4,FALSE 参数:

推荐精确查找,就是 FALSE 这个参数(大拿除外,他们精确了解excel查询排序机制的bugs,哦不,是features)。

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

3,小李拉了下,获得了全部数据。▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

4,这个 #N/A 有点丑,没销量就是0喽,小李改进了下公式

=IFERROR(VLOOKUP(C3,Sheet1!B11:C14,2,FALSE),0)▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

5,小李使用了数据透视▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

6,小李得意的将报告交给了领导,然而被领导甩了一脸:“小李,人越丑越努力,他们的努力取得了成功,获得了销量。你的颜值是0.1,为什么销量是0呢?!”

好了,小李比较委屈,那我们换个场景,顺便初步了解一下宏和VBA的用法。

VBA——更自由的操作

小李感到委屈,换工作到了大企业,人比较多,大概一百万人吧。

可天有不测风云,领导提出了类似的任务(连表格格式都一样),数据量大,数万行,写好公式后,用拖拽产生数据,也要累死人的,手工输入几乎就是不可能了。

正在烦恼的小李睡着了,被成龙托梦,想起了小霸王,哦不,是VBA(小霸王学习机内置QBASIC编辑器)。工作助力小集锦 篇一:Excel:用好VLOOKUP函数

于是小李开始了操作。

什么是VBA:

VBA(Visual Basic for Applications)是VB(Visual Basic)的一个子集,是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。VBA可以称作EXCEL的“遥控器”,VBA开发的程序必须依赖于它的父应用程序,例如EXCEL。VBA不需要安装,OFFICE内置了VBA的开发环境。

Excel 2016VBA功能开启步骤:▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

表格操作步骤:

模拟表格,5000行(要十万行也是可以的,但是作为例子也没啥意义)。所要做的工作是合并两张表:▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

好了我们通过操作(略),有了5000行“颜值表”、“销量表”的原始数据表格了(数据生成见附录部分)

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

下面开始VBA吧

1,新建一页,做个表头▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

2,打开VBA编辑界面▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

3,右键模块,选择插入模块▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

4,修改模块名称▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

5,输入代码▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

6,回到当前“汇总表”sheet,执行宏“整理合并”▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

小李按下了回车键,得到结果,舒适的摆了个姿势,看小电影去了:工作助力小集锦 篇一:Excel:用好VLOOKUP函数

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

【正文完】

附录,5000行数据随机生成的VBA

预备工作,新建两张表,写好列▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

一、对于颜值表,我们采用录制宏,并编辑宏的方法简便生成5000行数据,步骤如下

——作为VBA入门,这里简化VBA的编程,对不熟悉VBA的人比较友好。工作助力小集锦 篇一:Excel:用好VLOOKUP函数

1,点击录制宏。▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

2,填写工号初始数字▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

3,颜值用随机函数确定▼

B3单元格输入 = RANDBETWEEN(1,10)

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

4,拖拽一下,形成复制。不用多,拖个两三行就行了▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

注意:本案例仅仅是示范宏和VBA的关系,以及录制宏之后的VBA编辑。用RANDBETWEEN之类的随机函数,产生的数据会随着操作不断变化。实际应用要注意!

5,点击关闭录制宏。开始编辑宏。▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数


工作助力小集锦 篇一:Excel:用好VLOOKUP函数

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

6,找到录制的宏,VBA代码,修改一下▼

1),把 Selection.AutoFill Destination:=Range('A3:A7'), Type:=xlFillDefault。其中的'A3:A7',改成'A3:A5002'

2)把 Selection.AutoFill Destination:=Range('B3:B7'), Type:=xlFillDefault。其中的'B3:B7',改成'B3:B5002'

然后,ctrl+s保存。▼

可能会提示要保存成可以执行宏的格式,看自己情况吧。如果不保存VBA,可以直接点是,如果想保存这个宏,以后用于别的文件,可以点击否,然后在excel里另存为宏格式的文件。

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

7,回到当前VBA颜值表sheets执行▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

8,查看结果,工号已到105000,正好5000人。▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

9,注意,上面生成的随机数,会随着修改文件而不断变化的缺陷(特性、features),之所以这么操作,是为了让大家看一看,最简单的VBA,就是通过录制宏,然后按需简单修改而成的。如果想要更好一点的方式,可以改成下面这种,就不会“数据闪来闪去”了。▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

二、对于销量表,我们来直接用VBA编写

方法1

代码输入▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

执行结果,也得到了5000组数据▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

会发现,照抄宏录制的代码,大脑放空,随意编写,执行速度会很慢,有种窒息的感觉,但是也算能用吧。工作助力小集锦 篇一:Excel:用好VLOOKUP函数

方法2

于是我们可以稍微改进下。速度快一点,主要是没那么卡了,代码如下:▼

工作助力小集锦 篇一:Excel:用好VLOOKUP函数

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多