粉丝服务

「熟练使用Excel」指的是什么水平?


Hello~我是运营菌。


刚毕业的同学会发现,在招聘要求中「熟练使用Excel」是最常见的。那什么才是熟练Excel呢?


在专业能力过关前提下,单单是Excel来说,应届毕业生应试,那就是掌握VLOOKUP函数和数据透视表,还有时间把SUM、SUMIF、IF函数掌握。以上,这就是被誉为Excel中必学的“4+1”。


本人16年刚毕业时,面试过2份工作,分别对Word和Excel的使用熟练都有要求的。虽然Word插入页眉也不会,后面因为缺人和我态度不错,把我招进去了。


对Excel有操作要求的是台账管理员的职位,总共要统计1000多套房子的出租房租水电费等数据信息。面试上机的Excel时候,印象是用了SUMIFS和VLOOKUP做了简单的习题,但SUMIFS的结果是错误的,尴尬.gif。


后来,那位出题人直接问我平常会用哪些函数。我直接说VLOOKUP、SUMIF和COUNTIFS函数。出题人再问我,会不会数据透视表。我当然说会,但我当时是不怎么会的哈。就这样通过了Excel操作的面试,2面后还入职了,想不到吧~


正是因为入职了这一家公司,导致我做的报表越来越多,同事越夸我报表越花哨,还卷出了许多数据看板。



在这里简单给大家分享2个重要的应试技巧,VLOOKUP函数和数据透视表。


理解Excel函数构造

可能许多同学不太了解函数的构造,一起来看看Excel函数在使用时有哪些规范,首先我们看一下标点符号的使用规范:



在函数里面的标点要使用英文输入法下的标点,在Excel上,SUM的参数个数是不固定的,区域和数值的参数也能混合使用。


看回vlookup函数的构造情况:



VLOOKUP函数


首先我们看一下vlookup函数的含义,就是这么一串英文,不用急着弄明白其中的意思,大概知道它有4个参数就可以。



第一次看到的人,看不太明白其中的意思,下面我们通过具体的例子来理解





1.正向查找

例子,我们要在成绩单中找到喜洋洋、哆啦A梦、大雄的段位分别是多少?


这时候我们利用VLOOKUP正向查找即可,在段位处填入的公式为:

 =VLOOKUP(J3,$B$2:$F$94,3,0)



发现一:②查找范围,要根据①用谁找作为,查找范围的第一列。
发现二:返回列数是以②查找范围(红色区域)作为参考系,而不是整个表格作为参考系。【段位】在查找范围第3列,所以返回【3】


喜洋洋、哆啦A梦、大雄的段位分别是黄金、白金、黄金。



2.查找多列

例如现在我们要找喜洋洋的段位,数学、语文成绩三样东西


公式为:

=VLOOKUP($J3,$B$2:$F$94,COLUMN(C1),0)



对比查找单列和查找多列的公式

 查找单列 

 查找多列 


发现一:①用谁找的J3变成了$J3,固定引用J列
发现二:③返回第几列,由固定的3变成相对引用COLUMN(C1),当我们往右拖动填充时,里面的C1变成了D1,E1。


在函数中,COLUMN(C1)=3,COLUMN(D1)=4,COLUMN(E3)=5,COLUMN函数只与列有关,与行无关。






3.逆向查找

例:我们现在要查找哆啦A梦的学号


逆向查找表达式:
=vlookup(查找值,IF({1,0},查找值所在列,结果值所在列),2,0)

正常表达式:

逆向查找表达式:

逆向查找其实是构造出新的查找区域↑

通过对比上面的2个公式发现,主要是②在哪里查找发生了变化,由原来的区域,变成了IF函数嵌套。我们可以理解为红色部分的IF函数,重新构造了②查找区域。

重构的②查找区域存在代码中,不真正显示在单元格上

重构后的查找区域只有2列,所以返回列数为2,匹配类型:【0】绝对匹配。

 延伸阅读 

在工作时,我们可以直接构造出辅助列在数据最后一列,这样可以沿用原来的正向查找的方法,可以用隐藏或填充颜色把【辅助列】隐藏掉。所以我们要学会灵活多变。


VLOOKUP掌握以上三种用法足矣,尤其是第一种常规用法必须熟练,第三种用法理解就好。


数据透视表作为Excel中最强大的功能,也是招聘中问得最多内容。透视表内容虽多,只需要掌握使用数据透视表求和、计数、平均值也基本你能通过面试了。



数据透视表有四大区域,分别是筛选区,行区,列区和值区。把字段拖拽到不同区域可以呈现不一样的效果。


透视表每个区域右击所呈现功能都是有所不同的,值区域重要功能有汇总依据、值显示方式、数字格式、排序筛选、显示数据源。


数据透视表主要有5个常见汇总依据:1.求和;2.计数;3.平均值;4.最大/最小值,5.非重复计数。


(1)求和-数据透视表


选择到数据源,插入数据透视表,汇总依据选择求和。轻松求出了每个城市的销售额。


有时候会出现求和是均是0的错误,这主要是字段是文本类型,像这样看起来是数值类型,实质是文本类型。

这种情况可以直接看到数据源,改字段前面有绿色的小三角,证明是个错误类型。通过选择整列数据,转换为数字,刷新后,求和能正常显示。



(2)计数-数据透视表


把值汇总依据改为计数,这里的计数无法去重。如果需要去重的计数,需要使用到Power Pivot的功能。




(3)平均值


把值汇总依据改为平均值,求出每个城市的每个订单平均消费多少钱。




(4)最大最小值


把值汇总依据改为最大/最小值,就可以获取销售额最大的订单是多少。



(5)非重复计数

我们在使用数据透视表,右击透视表,选择到汇总依据时,非重复计数是灰色不可选状态。




这里有2个途径都能激活该隐藏功能,把数据添加到数据模型(Power pivot),再生成的数据透视表即可激活。

途径①:选择插入数据透视表是,把下方的□将此数据添加到数据模型√打上。



途径②:选择到数据源,在Power pivot选择添加到数据模型,生成出来的透视表即可激活非重复计数功能。


对比一下普通透视表与超级透视表的字段列表。


首先看一下数据源的样板,截取前30行。



问题①:统计每个城市在过去一年有销售成交的天数?

把城市放在【行】,日期放在【值】,选择汇总依据【非重复计数】。统计出每个城市在有成交的天数。



我们看一下普通的计数,与非重复计数的结果对比。


由于2021年只有365天,透视表2的数据明显不符合,它只是对日期行进行简单的计数。

问题②:统计每个城市销售出去的商品类型共有多少种?

把城市放在【行】,产品放在【值】,选择汇总依据【非重复计数】


这样就可以统计出每个城市买了多少类商品,可以看到该公司的品类是否更全面。


以上内容仅仅是临急抱佛脚用,正常还是系统学习一下Excel函数、数据透视表和图表可视化内容,通过学习,你也能利用Excel制作出这样的数据看板。

关于以上这样的数据看板,我在工作中制作了50多套,特意从中挑选了6套数据看板作为课程案例分析,看完好评还可以获得赠送的5套福利模板。





《Excel数据大屏,自动化模板》

7大行业模板+视频教程+老师答疑
限量100张立减10元优惠券


戳“阅读原文”和1000+同学学习该课程!

本栏目精选

热门推荐

频道导航