Excel中实现Join数据——VLOOKUP函数的使用方法

  |   0 评论   |   4,922 浏览

概述

我们的日常开发中,经常会用到关联操作,如果在Hive或MySQL中,可以通过各种Join(left/right/inner)等来关联,但是对于小量的数据,难道要传到HDFS中,然后在Hive中再建两张表才能做Join吗?这未免有点太麻烦了。幸好,Excel中提供了VLOOKUP函数,可以实现按列查找,类似于join的功能。

语法规则

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数类型说明
lookup_value数值、引用或文本字符串需要在数据表第一列中进行查找的数值,默认为0
table_array数据表区域需要在其中查找数据的数据表。使用对区域或区域名称的引用。
col_index_num正整数

table_array 中查找数据的数据列序号。

col_index_num 为 1 时,返回 table_array 第一列的数值,col_index_num 为 2 时,返回 table_array 第二列的数值,以此类推。

如果 col_index_num 小于1,函数 VLOOKUP 返回错误值#VALUE!;

如果 col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值#REF!。

range_lookupTRUE/FALSE(或不填)

指明函数 VLOOKUP 查找时是精确匹配,还是近似匹配。

如果为FALSE或0 ,则返回精确匹配,如果找不到,则返回错误值 #N/A。

如果 range_lookup 为TRUE或1,函数 VLOOKUP 将查找近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值。

如果range_lookup 省略,则默认为近似匹配。

用法

数据准备

user.xlsx

假定我们的Excel中有两个Sheet

一个用来存放用户信息

image.png

另一个名为login的Sheet用来存放用户的登录历史

image.png

一个关联条件

我们要以条件(uid相同)作为关联条件,查询用户的基本信息及登录时间

=VLOOKUP(A2,login!$A:$C,3,)

选定一个单元格,输入

=VLOOKUP(A2,login!$A:$C,3,)

image.png

参数解释

参数解释
lookup_value由于我们当前是在行A上为该id查找值,因此这个地方是A2
table_array我们查找的是A列,为了得到C列,为了简单,我们从A到C,即login!$A:$C,注意这个地方是绝对引用
col_index_num我们期望返回登录时间列,因此是3。注意这个地方是相对于区域起始列的索引值
range_lookup我们使用默认值

回车

image.png

修改一下单元格样式,然后点击+,将同列的其它单元格补全,就得到了id所对应的登录时间

image.png

多个关联条件

如果需要关联多个条件,有几种方式

  • =VLOOKUP(A2:B2,login!$A:$C,3,0)

  • =VLOOKUP(A2&B2,IF({1,0},login!$A$2:$A$10&login!$B$2:$B$10,login!$C$2:$C$10),2,0)

然后Ctrl+Shift+Enter就OK啦

image.png

参数解释

参数解释
lookup_valueA2&B2,为我们要查找的联合键
table_array

if的用法:=IF(条件判断, 结果为真返回值, 结果为假返回值)

IF({1,0},相当于IF({True,False},用于构造查找范围的数据,相当于返回“真返回值”+“假返回值”,这样我们就有了2列数据

login!$A$2:$A$10&login!$B$2:$B$10,第一列的范围

login!$C$2:$C$10,第二列的范围

col_index_num我们期望返回登录时间列,因此是2。注意这个地方是相对于区域起始列的索引值
range_lookup0