操作描述尽量详细,但需要一点点基础,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(小霸王学习机内置QBASIC编辑器)。
于是小李开始了操作。
什么是VBA:
VBA(Visual Basic for Applications)是VB(Visual Basic)的一个子集,是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。VBA可以称作EXCEL的“遥控器”,VBA开发的程序必须依赖于它的父应用程序,例如EXCEL。VBA不需要安装,OFFICE内置了VBA的开发环境。
模拟表格,5000行(要十万行也是可以的,但是作为例子也没啥意义)。所要做的工作是合并两张表:▼
好了我们通过操作(略),有了5000行“颜值表”、“销量表”的原始数据表格了(数据生成见附录部分)
下面开始VBA吧
1,新建一页,做个表头▼
2,打开VBA编辑界面▼
3,右键模块,选择插入模块▼
4,修改模块名称▼
5,输入代码▼
6,回到当前“汇总表”sheet,执行宏“整理合并”▼
小李按下了回车键,得到结果,舒适的摆了个姿势,看小电影去了:
▼【正文完】
——作为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,就是通过录制宏,然后按需简单修改而成的。如果想要更好一点的方式,可以改成下面这种,就不会“数据闪来闪去”了。▼
方法1
代码输入▼
执行结果,也得到了5000组数据▼
会发现,照抄宏录制的代码,大脑放空,随意编写,执行速度会很慢,有种窒息的感觉,但是也算能用吧。
方法2
于是我们可以稍微改进下。速度快一点,主要是没那么卡了,代码如下:▼
联系客服