微信号:cntongji

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

Vlookup函数判断你的Excel水平处于几段

2018-09-03 17:00 中国统计网


毫不夸张地说,99%天天和Excel打交道的人,他们所掌握的Excel知识量不到总体的5%,也就是说还有95%的知识点并没有掌握。这不是危言耸听,这是我这几年数据分析培训中观察的结果。


大部分的Excel使用者,每天在用最低级的知识处理着各种复杂的数据分析问题,分析要有效率只是一种传说。


不信我们就来测试一下,用一个最大众化的函数Vlookup来做测试,别瞧不起这个初阶函数,国外有个小哥还专门给这个函数写了一本书,可见这个函数简约而不简单。



于是我就琢磨了个题考考大家函数水平,看你在几段:


一段:会简单的vlookup函数的使用

二段:会vlookup+column函数的嵌套使用

三段:会vlookup+match函数的嵌套使用

四段:会vlookup的模糊匹配使用


相信大部分人在一段或者段外徘徊,vlookup函数基本上是使用频率最高的一个函数,这个函数不会使用的话,基本上就算是不会函数了。


只会sum或count这种函数的朋友自动面壁去,下面的描述你基本看不懂哈。


很多表哥表妹常说这些函数都会,但是组合在一起就不会了。确实,函数的嵌套是最难的,不光难在技术,最关键是逻辑,很多时候是我们自己想不到这样取巧的使用而自己打败了自己。


别慌,今天我给大家上堂干货课程,分享给你办公室的每个表哥表姐表弟表妹们,让他们都学会。谦虚的说,这样你们的办公效率至少会提高一倍吧。


一段:vlookup的基本用法


vlookup是一个纵向查找函数(从左往右查),官方的语法规则是这样的:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。翻译成中文就是:查找(一个值,这个值所在的区间,它位于第几列,精准匹配还是模糊匹配)




lookup_value:可以是一个值、日期或文本等。如你查询上图中的“城市”


table_array:查询值所处的区域,对于上图就是A1:H11这个范围,强烈推荐区域改成A:H这种写法,好处是当添加新数据源时不用更改公式。


col_index_num:查询的数据处于第几列,比如要查“完成率”这个值就是4,查“销售数量”就是6。


range_lookup:0为精准匹配,就是查询对象必须长得一模一样,少根汗毛都不行。一般情况都要求精准匹配,如果这个值省略这是模糊匹配(见vlookup四段的用法)


举例说明:

公式=VLOOKUP(“上海”,A:H,5,0)


查找“上海”所在的第五列数据,要求精准匹配。这个公式生成的结果是718。


注:“上海”可以是查询值所处的单元格,如果“上海”在K2单元格,则公式可以改成:

公式=VLOOKUP(K2,A:H,5,0)


K2中如果是“成都”,结果则是659,如果是“雄安”,结果则是668。


Vlookup是非常好的数据查找函数,很方便的把处于不同地方的数据匹配到指定的地方,其中关键点就是数据查询的区域,这个区域可以是不同的区域,不同的工作簿,不同的工作表。


拓展知识点:

Vlookup家族还有Hlookup,Lookup


二段:Vlookup+Column


当我们需要用Vlookup匹配多列数据的时候,往往需要手动去更改公式中的第3个值(就是col_index_num),但是匹配对象太多的情况下,手动修改其实是非常没有效率并且非常苦逼的一件事,这个时候column函数可以解放你们。


相信大部分会vlookup的人,现在还是傻傻的手动在改这个参数,说的就是你。


COLUMN(reference)


返回reference所在单元格所处的列号,如果A1就是1(第1列),B25就是2(第2列),H2就是8(第8列),这三个公式分别为COLUMN(A1),COLUMN(B25),COLUMN(H2)。如果reference为空则返回当前单元格的列号。




上图就是在L2单元格写好公式后直接往后拉这个公式就可以直接匹配出其它6个值,不用手动将第3个参数分别改成3,4,5……,因为第三个值自动复制成COLUMN(C1),COLUMN(D1),COLUMN(E1)……


高效不?就是这么简单,小函数有大用途。


拓展知识点:

与column(reference)函数对应的是row(reference),试试看。


三段:Vlookup+match


Vlookup和match函数组合是V函数的标准用法,与column函数一样的功效,match函数的作用也是用来改变第三个参数值。


MATCH(lookup_value, lookup_array, match_type)


M函数是返回指定数值在指定数组区域中的位置,生成的是位置而不是V函数中位置所处的值,这是二者的区别。


match_type如果是0则为精准匹配,省略则为模糊匹配,一般都是用0进行精准匹配。


例如我们使用上面图1中的数据源,公司如下:


公司=MATCH(“完成率”,B1:H1,0)


返回值为3,因为“完成率”这个指标是在B1:H1这个区域的第3个值,如果查询“进店顾客数”则返回7。


所以M函数可以用来查询指定对象所处的位置,和V函数组合威力巨大,基本上可以两个查询值的无死角匹配。




图3中嵌套公式写在了V2单元格,U2和V1单元格是可以修改“城市”和“查询指标”的地方,V2单元格将生成对应的查询值,修改U2和V1的值即可以查到对应的数据。


V+M函数组合是非常灵活的查询函数,是E界必备之效率嵌套用法。


四段:Vlookup的模糊匹配


从技术层面来讲,这个V函数的用法大概处于二段水平,但是从数据分析业务场景来说,我更愿意把它放在四段,因为这种应用解决了好几个业务场景的实际使用。


比如将商品价格分成低中高三段,将员工年龄分成青年、中年、老年等,将员工工龄分成4段等等场景。


如下图,通过每个商品的价格,自动匹配出来它处于的”价格段”和”价格描述”两个字段,有了这两个字段后,再用数据透视表做分析就so easy了。




要实现这样的功能,首先需要建立一个自定义的分段标准,没有标准鬼才知道你应该归位到哪儿。知识点来了:




这里的价格节点可以自定义修改,修改后在图4的对应位置就可以自动生成对应的价格段。自定义的知识点其实比较简单,真正的知识点是图4、图5的数据该如何关联在一起?




单元格C2和D2中的公式就是答案,它利用了vlookup函数的模糊匹配功能,你可以看到公式中第四个参数是缺失的。


END.

来源:数据分析网


近期文章回看(点击查看)

转行数据分析真的适合吗?

3组技能决定数据分析师是否skr

Mysql常见的13个错误码讲解

 
中国统计网 更多文章 3组技能决定数据分析师是否skr 转行数据分析真的适合吗,你准备好牺牲了? 女性用户占比52%,哪款有声APP深得你心? Mysql常见的13个错误码讲解 985、211毕业生薪酬大幅度领先普通高校,北京、上海毕业生薪酬上明显占据优势!
猜您喜欢 发展领导力而不仅仅是培训领导力 【招聘】京东\/蚂蚁金服\/滴滴\/VR\/餐饮等年前面试,年后入职美滋滋 为何测试时性能吊炸天而上线却惨不忍睹? 浪曦齐鲁工业大学合作签约仪式隆重举行