微信号:cntongji

介绍:中国统计网(www.itongji.cn),国内最大的数据分析门户网站.提供数据分析行业资讯,统计百科知识、数据分析、商业智能(BI)、数据挖掘技术,Excel、SPSS、SAS、R等数据分析软件等在线学习平台.

VLOOKUP函数在统计工作中的应用

2019-06-10 15:30 中国统计网

- 点击上方“中国统计网”订阅我吧!-



VLOOKUP函数作为EXCEL中查找和引用函数中重要的一员,在批量处理数据时可以收到事半功倍的效果。


01 VLOOKUP函数介绍


1. 用途:在表格或数值数组中的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。

2. 语法:VLOOKUP(查找目标,查找范围,返回值的列数,精确或模糊查找)

3. 参数:

①查找目标为需要在数据表第一列中查找的数值,它可以是数值、引用或文字串;

②查找范围为需要在其中查找数据的数据表,可以使用对区域或区域名称的引用;

③返回值的列数为查找范围中待返回的匹配值的列序号;

④精确或模糊查找为一逻辑值,指明函数VLOOKUP返回是精确匹配还是近似匹配。若为TURE、省略或1,则返回近似匹配值;若为FALSE或0,则返回精确匹配值;若找不到,则返回错误值#N/A。


02 经济普查中运用VLOOKUP函数查找引用数据案例


1. VLOOKUP函数使用过程


全部数据,为经济普查数据库(第二张工作表名)


需要查找的数据(第一张工作表名)


待解决问题:我们需要从“图一”中查找到“图二”第一列“单位详细名称”所对应的街(村)门牌号、行政区、固定电话等信息,并引用显示在“需要查找的数据”工作表中。



B2单元格中的公式为:

=VLOOKUP($A2,全部数据!$B:$CI,2,0)

解析:$A2为“需要查找的数据”工作表中第一列“单位详细名称”;

全部数据!$B:$CI为绝对引用区域,注意和查找目标相对应的“单位详细名称列”必须是“全部数据!$B:$C”中第一列的数值;


2返回“全部数据!$B:$C”中第二列的数值,而非“全部数据”(图一)工作表中的第二列;

0表示精确匹配值。


我们可以把这个公式复制到后面的C、D等列,但是需要把数字2手动替换成3、4等列数。


为了简化工作量,可以利用COLUMN函数指定单元格的列序号。


COLUMN函数用途为返回给定引用的列标。举例:

COLUMN(A1)、COLUMN(A2)、COLUMN(A3)……返回值为1,即A列的列序号;

COLUMN(B1)、COLUMN(B2)、COLUMN(B3)……返回值为2,即B列的列序号;

COLUMN(C1)、COLUMN(C2)、COLUMN(C3)……返回值为3,即C列的列序号。



­C2单元格中的公式为:

=VLOOKUP($A2,全部数据!$B:$CI,COLUMN(C2),0)

解析:其中COLUMN(C2)返回“全部数据!$B:$C”中第三列的列序号,相当于数值3。

我们直接复制公式到其他单元格,COLUMN函数可实现自动转换列序号。


2. VLOOKUP返回值错误处理

如果在查找范围的数据表中找不到相应的目标,则VLOOKUP返回#N/A。如下图五,在“全部数据”中无法找到“金乡县公安局第一派出”这一单位详细名称,则B5单元格中返回错误值#N/A。



有时为了方便后期计算或显示美观,我们需要用空值或者“0”值来代替#N/A。这时,就可以利用错误处理函数IF(ISERROR())进行修正,让#N/A显示为空值或者“0”,如下图六中的C5单元格显示为空值。



单元格C5显示为空值,其单元格中公式为:

=IF(ISERROR(VLOOKUP($A5,全部数据!$B:$CI,2,0)),"",VLOOKUP($A5,全部数据!$B:$CI,2,0))


若把C5单元格中公式替换为:

=IF(ISERROR(VLOOKUP($A5,全部数据!$B:$CI,2,0)),"0",VLOOKUP($A5,全部数据!$B:$CI,2,0)),则C5就显示为“0”。


IF函数是一种重要的逻辑运算函数。

用途:执行逻辑判断,它可以根据逻辑表达式的真假,返回不同的结果,从而执行数值或者公式的条件检测任务,该函数广泛用于需要进行逻辑判断的场合。


ISERROR函数是IS类函数的一种,语法:ISERROR(参数),ISERROR的参数是任意错误值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!))。


上述C5中公式,若逻辑判断ISERROR(VLOOKUP($A5,全部数据!$B:$CI,2,0))为真(#N/A),则返回空值,若逻辑判断ISERROR(VLOOKUP($A5,全部数据!$B:$CI,2,0))为假(非#N/A),则返回依据查找目标在查找范围中查找到的相应数值。


3. VLOOKUP返回值错误的常见原因有两种:

①没有查找到目标,上述实例错误显示就是因为没有查找到目标数据。

②数值格式不同,查找目标和查找范围中的数值格式不同导致VLOOKUP返回值错误。在实际工作中,从两个不同程序中导出的数据库,因其数据源不同往往存在数值格式差异,导致VLOOKUP返回值错误,这时就需要对查找数据进行“分列”处理。


具体方法:选中需要转换格式的一列数值,点击菜单栏“数据”-“分列”,按照分列向导进行“下一步”操作,一般情况为默认,最后点击“完成”。数值格式转换为相同格式后,在利用VLOOKUP函数一般就能找到对应目标。



4. VLOOKUP字符的模糊查找

针对上述图六中返回值错误,我们可以进行“包含”查找,找到最有可能的目标。



全部数据中查找多的最相近的单位详细名称“金乡县公安局第一派出所”。


图八单元格D5中的公式:

=VLOOKUP("*"&$A5&"*",全部数据!$B:$CI,COLUMN(D5),0)

“金乡县公安局第一派出”在“全部数据”工作表中查找到“金乡县公安局第一派出所”,并返回相应行的数值。


解析:查找目标"*"&$A5&"*"包含通配符“*”。VLOOKUP函数第一个参数允许使用通配符“*”来表示包含的意思,把*放在字符的两边,即“*”&字符&“*”,其中&是对字符进行连接的意思。


Vlookup函数的确是一种非常强大的查找引用函数,在统计工作中熟练运用能大大提高工作效率,节约时间成本。


End.

来源:简书

在看点这里
 
中国统计网 更多文章 10个知识点,带你入坑机器学习! SQL干货: Group by 的plus版 17种经典图表总结,轻松玩转数据可视化! 精辟:数据分析就一个字“拆”! 互联网数据化运营管理—留存篇
猜您喜欢 iOS开发中可以节省50%编译等待时间的几个措施 【Go聚会专访】Go北京用户组发起人、布道师——郝林 收藏必备:快速了解Android重要机制 我这里有一支Dior口红送你,你要么? 数据报告 | 原来中国最难脱单的省份是……