数据分析之“Excel函数VLOOKUP”(全)

文|仟樱雪

Excel函数千千万,但大众情人,独他无二。

VLOOKUP函数,传说中的神查找&匹对,高效掌握它,分分钟,帮你搞定那个她!


01

当之无愧的中央空调——VLOOKUP函数,在企业中,不仅财务、采购、商品、物流、运营、生产这些部门会出现它的身影,而且客服、IT、研发、行政、人资、后勤等等这些部门,它都游刃有余。

熟练掌握VLOOKUP函数,是大部分数据处理人的初始必经之路,但是无数的表哥、表姐在利用VLOOUP函数,进行初级入门的查找、匹配时,遇到各种报错,便累觉不爱了。

枚举各种报错处理,见招拆招的铲除VLOOUP函数查找路上的妖魔鬼怪,揭开数据分析的唯美面纱,是每一个表哥、表姐的必备法宝。


02

众所周知,VLOOKUP函数是一个查找、匹配函数,给定一个查找的目标,它就能从指定的查找范围中查找返回想要查找到的值。它的基本“家庭结构”为:

查找结果=VLOOKUP(找谁,查找范围,在查找范围里第几列,精确查找OR模糊查找)

例如:根据右侧的“产品编码”,查找产品所属于的“品类”,


正确公式=VLOOKUP($M2,$E$1:$G$20,3,0)

第一个参数:找谁?找M列的“产品编码”;

第二个参数:在哪儿找,在左侧数据源表的E列到G列,E列是要找的“谁”(产品编码)所在的列,G列是“谁”所对应的目标(品类)所在的列,即E:G;

第三个参数:在左侧数据源区域中,从要找的“谁”(产品编码)所在E列,到目标列(品类)所在G列的列数字,本例中是3;

第四个参数:查找的方式,0或者FALSE代表精确查找(查找结果是数据源表格中第一条满足条件的记录的相应值),1或者TRUE代表模糊查找(查找最接近但比它小的那个值)


03

掌握了VLOOKUP的前世今生以及家庭结构,接下来开始斩妖除魔,进行常见报错情况和处理枚举。

(一)VLOOKUP函数初级报错——结构性错误枚举

1.查找范围不包含返回目标值列

错误公式=VLOOKUP($M2,$E$1:$F$20,3,0)


说明:该错误是查找范围,根本不存在产品“品类”所在列,应该将G列纳入查找范围,即将查找范围E:F,更改为E:G。

处理方法:查找范围可以很大,但是必须包含查找目标列。

正确公式=VLOOKUP($M2,$E$1:$G$20,3,0)

1.查找范围开始列位置,不是“找谁”所在列

错误公式=VLOOKUP($M2,$F$1:$G$20,3,0)


说明:VLOOKUP函数的查找范围,开始列必须是要查找的“谁”(数据源中“产品编码”所在列),该错误是未从开始列,计量查找范围,将F:G,更改为E:G。

处理方法:查找范围可以很大,但必须从“找谁”所在列,开始计量查找范围。

正确公式=VLOOKUP($M2,$E$1:$G$20,3,0)。

1.第三参数——查找参数,数字输入错误

a.偶然因素,人为手动输入错误

错误公式=VLOOKUP($M2,$E$1:$G$20,1,0)



说明:该错误是第三参数——查找参数错误,在数据源表中从“产品编码”E列开始数,3列,到“品类”所在G列止,而不是1列,数字1,是“产品编码”所在列,应该将1,更改为3。

1.第三参数——查找参数,数字输入错误

b.偶然因素,人为一叶障目,视觉错误

错误公式=VLOOKUP($M2,$E$1:$G$20,2,0)


说明:该错误是第三参数——查找参数错误,在数据源表中“产品编码”所在E列和“品类”所在G列中间隐藏了1列,导致人为视觉计量,参数为2,导致错误。

处理方法:查找范围可以很大,但必须从“找谁”所在列开始计量查找范围,释放隐藏列,避免视觉误差。

正确公式=VLOOKUP($M2,$E$1:$G$20,3,0)

注意:数据源中列数据,有时会被隐藏很多列,在计数从开始列到目标终止列时,一定要注意。

1.漏掉第四位参数或输入为1

错误公式=VLOOKUP($M3,$E$1:$G$20,3,1)


说明:该错误是第四参数,VLOOKUP函数如果缺少第四个参数,默认为模糊查找(1或者TRUE),则进行数字的区间查找,继续使用1或者TRUE,我们就无法精确查找到结果,出现报错,将其更改为0或者FALSE。

注意:当第四位参数为1或者TRUE时,默认为模糊查找

1.第四位参数,模糊匹配时,报错

例如:根据右侧产品的单价,查找在左侧的产品的销售单价的单价等级

错误公式=VLOOKUP($w2,$S$2:$T$7,2,0)


说明:该错误本案例是第四参数设置错误,模糊查找(1或者TRUE),是进行数字的区间查找的最佳选择,继续使用0或者FALSE,我们就无法精确查找到结果,出现报错。

处理办法:右侧公式中在查找“销售单价”为15.25时,返回“单价等级”所对应的级别,原因是在左侧的销售单价区域中0和20中,VLOOKUP函数只选比查找值15.25小的那一个对应的级别,所以公式会返回0所对应的级别F,将其0改为1或者TRUE。

正确公式=VLOOKUP($w2,$S$2:$T$7,2,1)

1.第四位参数,模糊匹配时,匹配错误

公式=VLOOKUP($w2,$S$2:$T$7,2,1),公式正确时,匹配结果错误

例如:以下案例中的价位区间从0开始到99元截止


说明:VLOOKUP使用模糊查找时,数据源区域的数据必须为升序列(从小到大),本例子中销售单价为65和45的顺序上下反了,导致VLOOKUP函数,一脸懵逼不知如何查找,从而查找出模糊的错误数据。

处理方法:将数据源区域的顺序按照从小到大更正即可。

1.VLOOKUP函数不支持按区域或者数组查找

例如:根据右侧“平台”和“产品编码”,在左侧日平台销售数据,查找“产品名称”

错误公式=VLOOKUP($M$1:$N$7,$B$1:$J$20,9,0)

说明:VLOOKUP函数查找区域不支持按照数组或者区域进行查找,本例中查找“平台”和“产品编码”的“产品名称”,因同一个编码在不同的平台是不同的产品,属于需组合处理或者进行结构转化后的匹配

处理方法:将“平台”和“产品编码”进行组合


正确公式=VLOOKUP($N2,$A$1:$K$20,11,0)

1.VLOOKUP函数不支持逆序查找

错误公式=VLOOKUP($O2,$E$1:$F$20,2,0)


说明:vlookup函数不支持反向查找。当查找区域中,查找列和目标列的顺序相反时,本例中左侧数据源区域“产品编码"是查找列,“品类”是目标列,但是“品类”在“产品编码”的前一列。此时从“品类”所在列开始输入查找区域,是逆序查找,VLOOUP函数并不支持此类查找,则自动报错

处理方法:将数据源区域的“品类”所在列,“飘移”到“产品编码”的右侧,再进行从左到右的数据区域选择 ,如下所示:


正确公式=VLOOKUP($O2,$F$1:$M$20,8,0)

(一)VLOOKUP函数初级报错——内容性错误枚举

1.查找值与查找范围开始列,数据格式不一致

错误公式=VLOOKUP($O2,$E$1:$F$20,2,0)

例如:根据右侧“产品编码”,在左侧查找“产品编码”对应的“产品名称”,


说明:VLOOKUP函数还是个很“别扭”的函数,如果查找值与查找范围开始列的数据类型不匹配,则会报错,本例左侧查找范围中“项目编号”是文本型数据(单元格左上方的绿色三角,表示文本格式数据),而右侧查找值“产品编码”是数值型(无绿三角),两者数据类型不匹配,则报错。

处理方法:统一数据类型格式,统一使用文本型格式或统一使用数值型数据,将右侧的“找谁”的数值型格式,添加一个空格格式转换成文本型格式进行查找。

正确公式=VLOOKUP($O2&"",$E$1:$F$20,2,0)

1.查找范围开始列与查找值,数据格式不一致

错误公式=VLOOKUP($O2,$E$1:$F$20,2,0)


说明:同样是数据格式不统一问题,如果查找值与查找范围开始列的数据类型不匹配,则会报错,本例左侧查找范围中“项目编号”是数值型数据,而右侧查找值“产品编码”是文本型(带绿三角),两者数据类型不匹配,则报错。

处理方法:统一数据类型格式,统一使用文本型格式或统一使用数值型数据,将右侧的“找谁”的文本型格式,“*1”转换成数值型格式进行查找。

正确公式=VLOOKUP($O2*1,$E$1:$F$20,2,0)

注意:不到万不得已,尽量想办法不要更改数据源。

1.查找区域中含有“隐形”字符(例如空格)

公式正确,但是出现查找报错:


说明:常见文本型数据,在录入系统时人为误差导致“隐形空格”存在或者数据源从网页或数据库中取出来是带有格式的,观察看不出来带有空格,本例中右侧数据,“产品编码”前带有空格,左右数据格式不统一导致报错但没有空格格式的,正常查找匹配数据。

处理方法:剔除右侧数据的文本空格,在右侧“产品编码”列加入几列空列,然后对A列进行分列操作(数据-分列),即可把不可见字符分离出去,然后整合数据后进行匹配。


正确公式=VLOOKUP($O3,$E$1:$F$20,2,0)

1.查找区域中含有不规则“隐形”字符(例如空格)


说明:查找列含有不规则的空格,有时1个或者2个,甚至多个空格,这些就是类空格的不可见字符。

处理方法:直接在单元格中复制不可见字符粘贴到替换窗口,替换掉即可。


1.数据源中出现重复记录,数据查找错误

错误公式=VLOOKUP($P5,$E$1:$H$20,4,0)

例如:根据右侧“平台”、“产品编码”数据查找其对应的“收入”;


说明:本例中原始数据中同一个“产品编码”属于不同的“平台”,但是“收入”是不同的,则直接根据“产品编码”查找数据源中的“收入”,VLOOKUP会傻掉,不知道匹配哪个收入数据,VLOOKUP一般默认从上到下的查找顺序,遇到的第一个“产品编码”的收入则进行查找结果呈现,因此出现数据内容的错误。

处理方法:辅助列处理,将“产品编码”和“平台”进行组合匹配,但是数据源中也需要进行组合,进行统一查找。


正确公式=VLOOKUP($O5,$A$1:$I$20,9,0)

注意:此类错误,一般不会轻易被发现,但是会导致总金额或者汇总数据差距甚远

1.查找范围不固定,导致部分查找匹配

错误公式=VLOOKUP($N6,E5:H24,4,0)


说明:本例中根据“产品编码”查找对应“收入”,但是查找区域没固定,其中编码为“64234”的产品在左侧数据源中是第2行,但是查找区域是“E5:H24”属于“相对引用”,第2行根本不在查找范围内,因此出现报错。

处理方法:将区域进行行列绝对引用,锁定不变动 。


正确公式=VLOOKUP($N6,$E$1:$H$20,4,0)

1.文本简称模糊匹配报错

错误公式=VLOOKUP($N2,$F$1:$I$20,4,0)

例如:根据右侧的“产品名称”简称,查找对应的销售数量;


说明:右侧“产品名称”是数据源中的产品名称的简称,根据简称进行精确查找匹配,无法一一匹配,故此报错。

处理方法:利用通配符“*”进行精确匹配,查找带有简称关键字词的数据,例如查找带有“李”姓的同学,等。

正确公式=VLOOKUP($N2&"*",$F$1:$I$20,4,0)

1.通配符使用错误导致报错

错误公式=VLOOKUP($X3,$R$2:$S$6,2,0)

例如:根据右侧的单价区间,在左侧的单价区间对应的“单价等级”,查找对应的单价等级;


说明:“~”符号在文本中时,在VLOOKUP函数中,会被默认特殊函数条件,不是文本“~”。

处理方法:用“~~”就可以表示查找文本的“~”了。

正确公式=VLOOKUP(SUBSTITUTE($X5,"~","~~"),$R$2:$S$6,2,0)



03

VLOOKUP函数查找小妙招

1.VLOOKUP+IF组合逆序查找匹配

公式=VLOOKUP($N2,IF({1,0},$F$1:$F$20,$E$1:$E$20),2,0)


说明:根据右侧“产品编码”,查找其对应的“品类”,但是在左侧的数据源中,“品类”所在列E列,却在“产品编码”列的前面列,逆序而处之,故此需要进行逆序查找匹配;

(1)公式1=IF({1,0},$F$1:$F$20,$E$1:$E$20)

是将左侧的数据源区域进行了逆序的正向扭转;


(1)公式2=VLOOKUP($N2,IF({1,0},$F$1:$F$20,$E$1:$E$20),2,0)是组合函数,扭转顺序后的查找匹配。

1.VLOOKUP+CHOOSE组合逆序查找匹配

公式=VLOOKUP($P2,CHOOSE({1,2},$F$1:$F$20,$E$1:$E$20),2,0)


说明:根据右侧“产品编码”,查找其对应的“品类”,但是在左侧的数据源中,“品类”所在列E列,却在“产品编码”列的前面列,逆序而处之,故此需要进行逆序查找匹配;

(1)公式1=CHOOSE({1,2},$F$1:$F$20,$E$1:$E$20)

是将左侧的数据源区域进行了逆序的正向扭转;


(2)公式2=VLOOKUP($P2,CHOOSE({1,2},$F$1:$F$20,$E$1:$E$20),2,0)

是组合函数,扭转顺序后的查找匹配。

1.COLUMN()/ROW()函数批量查找

COLUMN(),用于同一行数据自动产生列序列号;

ROW(),用于同一列数据自动产生序行号。

公式=VLOOKUP($P2,$E$1:$G$20,COLUMN(C1),0)

等价于=VLOOKUP($P2,$E$1:$G$20,3,0)


左侧的数据源中,因此利用column()函数自动计数“品类”和“收入所在列”,其中column(C1)=3,返回“品类”所在列的内容,因此只需将公式右侧填充即可,自动填充“收入”等内容的匹配查找;

后话:VLOOKUP函数查找匹配完毕,并不是万事大吉,一定需返回原数据源筛选查看是否查找匹对正确,进行查错处理,保证查找匹配正确。


04

Excel函数千千万,但大众情人,独VLOOUP无二,熟练掌握这20个斩妖除魔利器,前途无可限量,再也不会轻易入坑了。

那些年,我们走过的查找之路,趟过的20个坑,如今坑已平,那个她,分秒可见倾城容颜。

(注:2017.09.20 工作技能积累打卡,记录点滴数据分析成长之路,纵使分析工具换过千千万万,Excel之美,难相忘,感谢老大的启蒙!不足之处,望见谅,后续更新)

推荐阅读更多精彩内容