工作助力小集锦 篇一：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 代表可以精确匹配，比如“王炸”可以，但是“王炸之狗”就算另一个数据。

场景图例▼

说明：

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

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

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

4，如果匹配不到数据，会显示 #N/A 。不要怕，这是正常现象， 。为了显示的更直观，可以人为改进，比如： = IFERROR( VLOOKUP ( "王炸", B2:C7, 2, FALSE), "未找到王炸")。这时候，如果选区内找不到“王炸”，单元格就会显示"未找到王炸"。

实际操作（简化场景）▼

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

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

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

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

步骤：

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

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）。

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

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

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

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

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

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

VBA——更自由的操作

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

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

正在烦恼的小李睡着了，被成龙托梦，想起了小霸王，哦不，是VBA（小霸王学习机内置QBASIC编辑器）。

于是小李开始了操作。

什么是VBA：

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

Excel 2016VBA功能开启步骤：▼

表格操作步骤：

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

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

下面开始VBA吧

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

2，打开VBA编辑界面▼

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

4，修改模块名称▼

5，输入代码▼

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

小李按下了回车键，得到结果，舒适的摆了个姿势，看小电影去了： ▼

【正文完】

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

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

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

——作为VBA入门，这里简化VBA的编程，对不熟悉VBA的人比较友好。

1，点击录制宏。▼

2，填写工号初始数字▼

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

B3单元格输入 = RANDBETWEEN(1，10）

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

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

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

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里另存为宏格式的文件。

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

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

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

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

方法1

代码输入▼

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

会发现，照抄宏录制的代码，大脑放空，随意编写，执行速度会很慢，有种窒息的感觉，但是也算能用吧。

方法2

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

附录，以上例子使用的excel文件▼

链接:

密码: gfe8

祝大家食用愉快，谢谢！ up也只是初学者，一起学习共同进步吧。

注意：使用的时候，请不要变动sheets的顺序。会造成执行宏“整理合并”错误。因为宏“整理合并”中，有代码涉及到sheets的引用顺序。

ps：有不明确的地方可以提问哦，up会在评论区回答。当然，很有可能up也不懂，共同学习共同进步吧。

ps2：系列更新，看有没有闲的时间，up会努力勤快更新。

内容虽然简单，其实打打字，还是挺花时间的。。。

【完】