衣带渐宽终不悔,为表消得人憔悴:
* 正在求职的你,发现Excel操作被加入了笔试题;
* 身为老鸟的你,Excel竟然成为了你升职加薪的瓶颈
Excel的存在是为了更高效地工作,何时竟成为了掣肘?
其实,我们只要掌握20%的Excel技巧和操作,就足以应对80%的工作需求和学习需求了。今天特意整理了这些操作技巧,拯救正在“表海”中挣扎的你,让你从0到HERO,熟练运用Excel,工效提高200%。
提高桌面生产力第一篇章 数据整理与保护篇
CTRL+E,快速截取和填充技巧1:CTRL+E,截取与填充有妙招
还在为究竟使用哪个函数来对文本进行截取而发愁?身份证号?电话号码?家庭地址?一个快捷操作,截取、填充同时搞定。
操作步骤:在数据源当中的第一行,输入:出生年月日,然后选中整列区域,按键盘CTRL+E,完成快速填充。也可以在第一行右下角单元格处,双击十字句柄,点击右下角的填充选项,选择【快速填充】。
数据分列技巧2:分列一下,日期数据规范起来
在表格中,我们经常会发现这样的情况:同一维度的信息,数据格式却千奇百怪,比如日期,有的写2017年5月1日,有的写20170501,有的写201.05.01,等等。虽然表达同一意思,但数据类型却完全不一样,分析起来就频频出现错误。面对大量格式不准确的日期,分列一下,马上规范起来。
使用分列,不只是可以规范日期格式呀
操作步骤:选中数据源,在【数据】选项卡下找到【分列】,按照分隔符号,下一步到设置数据类型为日期,点击完成
数据格式技巧3:数据管理的小闹钟:条件格式
在工作中,我们经常需要对数据进行格式标识,以期更加直观地显示数据,比如员工工资的数额高低、培训成绩的分布、工作进度的控制、异常数据的监测等。没错,这个功能就是条件格式,它就像管理数据的小闹钟,让你更加直观清晰地看清数据,提示你进行相关的工作或发现数据的规律。
操作步骤:选中数据单元格,点击【开始】选项卡--【条件格式】--【数据条】,让单元格直接嵌上了类似于“柱状图”的效果。
除了数据条,这个功能还有很多种显示方式,见下图
条件格式技巧4:数据录入的加速器:数据有效性
在工作中,我们经常会碰到这样的工作场景:发给各个部门填写的表格,回收回来却发现,要么数据格式不统一,要么填写不一致。比如性别,有的会填男,有的会填男性,还有诸如出生日期等信息,填写起来五花八门。
如何从源头解决这个问题呢?数据有效性就是解决这个问题的有效办法。今天教大家数据有效性的高阶应用,双重数据有效性,比如限定省份下选择该省份包括的城市。
步骤1:选中需要设置条件格式的单元格区域,点击【数据】选项卡--【数据验证】--设置允许为:【序列】--选定数据来源为【A1:H1】即可为Excel单元格设置下拉的效果,即数据有效性。
数据有效性-1步骤2:如果要根据已经选定的省份,关联选择对应的城市,则需要为单元格进行【二级联动式数据有效性的设置】
【1】选择省市区域的数据源单元格区域,点击【公式】选项卡--【定义的名称】--【根据所选内容创建】--【首行】
数据有效性-2步骤3:【2】选中K列黄色区域,点击【数据选项卡】--【数据验证】--设置允许为:【序列】--来源为:=Indirect($J2)
数据有效性-3是不是很强大呢~
第二篇章 常见函数应用
技巧1:VLOOKUP函数,查找匹配,随心随遇
查找引用数据是Excel中使用最频繁的一项操作,其重要性不言而喻。比如根据姓名匹配职位、身份证号等信息,总不能一个一个查找复制,耗时耗力,还经常容易出错。
而提到查找引用,首先必须掌握的就是VLOOKUP函数,不会使用,那等着你的就是加班了。
VLOOKUP函数结构:
VLOOKUP函数结构VLOOKUP精确匹配:
VLOOKUP精确匹配VLOOKUP模糊匹配:不再为等级匹配发愁,取代if多层嵌套
VLOOKUP模糊匹配技巧2:INDEX+MATCH函数,查找界的王者
从适用性上讲,INDEX+MATCH的组合函数更具威力!
原理:在数据区域中,确定了数据所在的行和列,数据也就能唯一确定了。
Index+Match中,Match用以确定数据所在的行值和列值(查找姓名所在的行,查找身份证号所在的列,行列交汇的数据就是要匹配出来的数据),Index负责调出由Match确定的行值和列值交叉位置确定的唯一数据,于是查找匹配就自然实现了。
Match:查找到你的位置
Match:查找到你的位置Index:提取出相应位置的数据
Index:提取出相应位置的数据Index+match:完成查找匹配
Index+match:完成查找匹配Index+match+表单控件,一个小型的查询系统成型了
小型查询系统第三篇章 数据汇总与分析
ALT+=,快速求和技巧1:ALT+=,快速求和
不用再写那么多的SUM了,一个快捷操作统统搞定
合并计算技巧2:多表汇总,就用合并计算
对于表结构一致的多表汇总,不用再想着要用到VBA才能解决,合并计算功能就足够了
技巧3:数据透视表:超强统计,多数值计算,创建组,切片器中自由筛选
数据透视表,是一种交互式的图表。它允许用户根据需要对各类数据维度进行划分,通过快捷地拖动各类数据维度,将他们进行不同的重组,实现我们想要的结果。数据透视表堪称Excel数据分析汇总的神器,助你轻松发现隐藏在数据背后的本质。
超强统计:根据你想要呈现的数据报表,透视一下,让你轻松“拖”出来。
超强统计多数值计算:求和?计数?平均值?最大值/最小值?标准差?方差?你想要的数值计算方式,应有尽有。
多数值计算创建组:按季度?分年龄段?数据标签由你定义
操作:选中字段下的数据—右键单击选择【创建组】-创建“起始于”和“终止于”对应的数值,也可采用自带的年、季度、月等组合方式。
创建组切片器:一枚切片器,轻松控制多个数据透视表,数据展现随心而动
操作:选中数据透视表中任一数据——【分析】选项卡—插入切片器—右键单击切片器—报表连接—勾选需要控制的多个表格
切片器综合运用上面的功能,一张人员基本情况分析的看板就实现了
看板第四篇章 数据呈现与可视化
为了让要表达的信息更直观,更具说服力,通常我们遵循的原则为:能用数据显示的,绝不用文字说明;能用图形显示的,绝不用数据说明。相比较枯燥乏味的文字和数据信息,人们更愿意也更容易接受各种图形信息,也就是可视化呈现。
图表是Excel中非常强大的功能。所谓图表,就是将工作中的数值以图形的方式展示出来,更直观地帮助我们分析和比较数据。
组合图如何制作上面的组合图呢?我们一起来操作一下:
步骤11、选中数据源,插入一张柱形图,并修改图表类型为组合图。设置:产值:图表类型为-带数据标记的折线图
环比增长:图表类型为-簇状柱形图,勾选次坐标
步骤2通过调整分类间距的大小,改变柱形图两柱形之间的间距距离
步骤33、设置折线图的标记点显示方式
①设置折线图,线条填充样式为:无线条
②设置标记点:数据标记选项为原型,大小为35
填充颜色为:白色
标记表框为蓝色:5磅,线条类型为粗细结合式
步骤44、设置数据标签及轴坐标
点击选中次坐标,在设置坐标轴格式中,更改坐标轴的最大值为1,即100%;点击环形图的数据点以后,单击鼠标右键,选:添加数据标签;选中数据标签后,在设置数据标签格式中,更改标签位置为:居中
步骤55、设置图表标题,删除冗余刻度线条
如果觉得坐标轴的数值比较多的话,可以通过设置坐标轴格式,更改主单位的大小进行调整。
我们还可以制作更高阶的图表比如仪表图、动态图、看板等
仪表盘图 动态图表 人员情况看板这些操作技能感觉如何呢?有没有目瞪口呆,赞不绝口!马上练习吧!
如果想一起学习Excel,欢迎评论留言,让我看到!
欢迎大家关注文集Excel常用技巧,与大家一起共同成长学习。
如果你觉得不错或者有用,希望大家能在文章最下面点个喜欢!
欢迎大家关注下作者北大小笨,更不要忘记打赏哟~~
网友评论