获取VIP特权
Home > 模板攻略 > Excel教程 > 怎样才算精通 Excel?

2019-10-11 15:29:04

0已点赞

怎样才算精通 Excel?



作为一个Excel的深度使用者,表示Excel的功能实在是太多了,函数都有471个,彻底精通Excel的方方面面可以说几乎是不可能,但是能熟练运用Excel处理问题还有一个另外的标准。

其实在工作中所说的精通Excel,至少要学会4+1,4个核心函数为基础篇:SUM、IF、VLOOKUP、SUMIF1个数据透视表为进阶篇。借着这个话题,给大家分享一下4+1的教程,由于篇幅原因,更多整理好的资料都上传到下方的课程链接中了~

课程传送门(学完点个5星即可)
Excel就该这样用,数据透视表篇 - 网易云课堂
【训练营】职场Excel零基础入门 - 网易云课堂

01 SUM函数(数学)

SUM函数是Excel中非常常用的函数,求和也是数学函数的一个基础,先来简单看下SUM函数的语法:

SUM函数语法

SUM函数的参数是不定的,意思是可以有一个、多个参数,并且每个参数可以是单个单元格、数字或者是引用的区域,虽然SUM函数看起来很简单,但是可以组合其他实现非常多的小技巧,例如:累计求和、数组公式等等。

累计求和案例:=SUM($B$3:B3),虽然看起来非常简单,但是效率却很高。

SUM函数进行累计求和

SUM函数数组公式案例:=SUM(B2:B9*C2:C9),利用数组公式,可以减少辅助列的创建,更加利于表格美化,在使用数组公式的时候,需要按Ctrl+Shift+Enter键,不能直接按Enter键,这样就可以在一个单元格实现“销量*单价”总和。

SUM函数使用数组公式

学习传送门:【训练营】职场Excel零基础入门 - 网易云课堂

02 IF函数(逻辑)

IF函数是Excel逻辑家族的扛把子,只要是逻辑判断就可以说离不开IF函数,先来初窥下IF函数的语法:

IF函数语法

IF函数很简单,几乎接触过Excel的人看到都能理解,基础的用法这里因为篇幅的原因就不做分享了,可以看下底下的学习传送门。

在操作中经常是需要嵌套IF或者各种函数进行判断,单一条件的IF出现的情况比较少,那么IF是如何进行嵌套的呢?

假如:平均分超过90的为优秀,超过60的为合格,低于60的为不合格

先来看一下如何将逻辑拆分:

  1. 首先判断平均分是否大于等于80,是的话,为优秀,结束,否则进入2;
  2. 判断平均分是否大于等于60,是的话,为合格,结束,否则进入3;
  3. 不合格,结束。

那么怎么在Excel里表示呢?我们先写第一层:

=IF(D2>=80,"优秀",待判断)

上面这个公式只判断了大于等于80分的情况,小于80分的情况还有两种:合格、不合格,在待判断那里可以在嵌套多一层IF:

待判断=IF(D2>=60,"合格","不合格")

嵌套进入得到完整的公式:

=IF(D2>=80,"优秀",IF(D2>=60,"合格","不合格"))

结果如图:

嵌套逻辑

如果逻辑足够复杂,那么IF嵌套的层数会越来越多,阅读起来极为不方便,那么怎样可以使得代码更加容易阅读呢?

我们可以在嵌套的时候强制换行,这样就可以美化公式,快速阅读。

在单元格中怎么换行呢?使用Alt+Enter键即可,我们对比下阅读效果吧:

Alt+Enter

可以看到使用Alt+Enter进行回车使得函数的阅读性更高,能够非常清楚看到每一层公式的判断逻辑。

当然如果要评出ABCDEF这么多等级,使用IF嵌套就太繁琐了,这个使用就可以使用vlookup函数来快速筛选组合。

学习传送门:【训练营】职场Excel零基础入门 - 网易云课堂

03 VLOOKUP函数(筛选)

VLOOKUP被称为Excel中的效率之王,但是95%的Excel使用者都不能很好使用VLOOKUP,先来简单浏览个视频看下VLOOKUP究竟是怎么用的。

VLOOKUP基础用法浏览

先来看下VLOOKUP的最基础用法,为了方便大家理解,做成了图片。

总共只有4个参数,分别是:用谁去找、匹配对象范围、返回第几列、匹配方式(0表示精确匹配,1表示模糊匹配)。

然而学会了基础语法,就会VLOOKUP了么?NO,对大部分同学来说,VLOOKUP只会最基础的查找方式,碰上了返回多列、多条件、反向位置的就一脸懵逼了,可以继续往下面看VLOOKUP的4中查询方式。

3.1 基础单条件查找

VLOOKUP的基础单条件用法是简单的一种用法,使用单个检索关键字,并且检索关键字在选择区域的第1列,直接使用普通公示就可以解决。

VLOOKUP单条件查找

总结一下,基础查询公式的用法:=VLOOKUP(用谁找,去哪里找,找到了返回什么,怎么着)

3.2 反向查找

反向查找跟普通的VLOOKUP查找存在什么差异,我们都知道检索关键字必须在查找区域的第1列,反向查找的检索关键字不在查找区域的第1列,可以使用虚拟数组公式IF来做一个调换。

VLOOKUP反向查找

总结一下,反向查找的固定公式用法:=VLOOKUP(检索关键字,IF({1,0},检索关键字所在列,查找值所在列),2,0),注意事项,所有使用了数组的公式,不能直接回车,需要使用Ctrl+Shift+Enter,否则会出错。

3.3 多条件查询

在使用VLOOKUP匹配数据的时候,往往条件不是单一的,是由多个一起组成的,那么也可以利用&将字段拼接起来,并且利用IF数组公式构建出一个虚拟的区域。

VLOOKUP多条件查询

总结一下,多条件查找的固定公式用法:=VLOOKUP(关键字1&关键字2,IF({1,0},序列1&序列2,查找值所在列),2,0),注意事项,所有使用了数组的公式,不能直接回车,需要使用Ctrl+Shift+Enter,否则会出错。

3.4 查询返回多列

查询返回一列的情况很容易就能完成,如果是返回多列呢?这个时候就要借助另外一个辅助函数——column函数,有关column函数的简介可以看下:

COLUMN返回的结果为单元格引用的列数,例如:column(B1)返回值为2,因为B1为第2列。

VLOOKUP返回多列

总结一下,返回多列的固定公式用法:=VLOOKUP(混合引用关键字,查找范围,COLUMN(xx),0),返回第几列就开始引用第几列的单元格即可。

学习传送门:【训练营】职场Excel零基础入门 - 网易云课堂

04 SUMIF函数(条件计数)

SUMIF函数是SUM函数的条件版本,即满足相应的条件才进行计算:

SUMIF函数

比如“计算【相机销售额】超过80万元的所有销售员的总销售额”,听起来很绕口。其实很简单,对销售员的销售额进行求和,但是这部分销售员必须是【相机销售额大于80w】

配合案例来简单解释下这个函数,【条件区域】是【相机销售额】;【判断条件】是【大于80】,而【求和区域】则是【单个销售员的合计销售额】。下方演示动画公式为:

=SUMIF(B2:B10,">80",H2:H10)

SUMIF单条件求和

在上方公式中,我们可以看到计算结果为2855,而【相机销售额>80】的销售员总共也只有4个,图中使用黄色标记了出来,可以看到计算的结果也为2855。

SUMIF函数有一个强化版本,即多条件版本——SUMIFS,用法与SUMIF类似:

SUMIFS函数

这里就不多解释,SUMIF的详细资料可以前往:

学习传送门:【训练营】职场Excel零基础入门 - 网易云课堂

05 数据透视表

可能大部分人都不知道透视表是什么?简单来介绍下透视表是什么?能做什么?如何提升我们的工作效率,为什么要使用透视表?先来简单看一个视频:

透视表能做什么?

除了代替复杂函数,制作交互性图表,还能规范数据,一个GIF的时间完成一项工作:

一键规范数据

透视表(Pivot Table)是一种交互性的表,可以用来进行计算,例如:求和、筛选、排序等等,并且计算的结果跟透视表中的排列有关,之所以称为数据透视表,是因为它可以动态地改变透视表的版面布局,可以非常方便地从不同角度分析数据,并且这里还有一个词,叫“交互”,跟传统的表格不同,我们可以跟表格之间做一些人机交互,更方便地集中展示我们想要的数据。

先来看下透视表能做哪些高级的操作吧~

5.1 数据的快速分组

在Excel传统用法中,如果要对数据进行分组,要写非常复杂的混合函数,效率非常的低,使用透视表就非常的便捷,右击「创建组」,然后「月份」,点击「确定」即可。动画演示:

数据的快速分组

当然除了日期,还可以对数值、文本进行分组,也是同样的操作,非常方便。

5.2 相同标签的快速合并

合并相同标签也是一个非常使用的操作,如果使用合并单元格去合并的话,效率非常的低,并且会修改数据的结构,使用透视表就可以非常便捷地完成这个操作。

右击「数据透视表选项」,勾选「合并且居中标签」,点击「确定」即可。

相同标签快速合并

除了能快速合并相同标签,也可以选择将相同的标签快速进行重复录入,在透视表选项中也是非常便捷就能完成的一个操作。

5.3 数据一键排名

传统的表格中,使用排名要使用RANK函数,复杂一点的会使用SUM和COUNTIF函数配合数组,非常麻烦,其实在透视表中只需要重新插入一个重复字段,右击「值显示方式」-「降序排序」,然后右击「排序」-「升序」即可完成快速排名,不用函数繁琐计算,非常方便。

数据一键排名

5.4 一键批量创建工作表

在使用Excel的时候,经常需要对某些数据进行拆分单元格,例如:不同地区的数据放到不同的工作表。手动筛选然后复制粘贴的效率实在是太低了,这个时候就可以利用透视表快速拆分数据。

将需要拆分的「字段」拖动的「筛选区域」,然后点击「分析」选项卡,选择「显示报表筛选页」,点击「确定」即可。

一键批量创建工作簿

5.5 一键合并所有文档

透视表除了能拆分文档,还能合并文档,对于相同结构的多份文档,如果使用手动合并,效率非常低下,使用透视表30秒就可以搞定。

依次按「Alt+D+P」,选择「多重合并计算」,然后将「每一张表的数据一一添加」,点击「确定」即可。

合并前的12份文档:

12份文档

合并结果:

合并后结果

5.6 一键计算所占百分比

在Excel中需要统计每个数据所占的百分比,利用SUM函数也可以完成,不过在透视表中只需要通过修改值显示方式即可搞定。

右击「值显示方式」,选择「总计的百分比」即可完成。

一键计算所占百分比

5.7 一键规范数据

都说「数字不规范,亲人两行泪」,在办公中会经常碰到各种不规范的数据,例如将一月、二月、三月或者销售员的名字做成字段名。

这种表格对我们的统计分析来说会非常麻烦,如果需要手动去处理统计,非常吃力,然而利用透视表就可以大大提升我们的一个效率,一分钟做出一份规范的数据。

依次按「Alt+D+P」,选择「多重合并计算区域」,然后「创建单页字段」,点击「确定」,在新建的透视表中,拖动到「右下角的汇总单元格」,双击,就可以看到规范的数据了。

例如下图:

不规范数据

动画演示:

快速规范数据

5.8 60秒制作高级交互图表

传统的图表要想制作出交互功能,只有通过插入组件或者使用数据验证制作下拉菜单进行交互,而利用数据透视表,可以便捷的插入切片器、动态透视图、日程等等,可以制作出非常强大的交互效果,如下图:

交互效果1
交互效果2

5.9 一键修改数据格式

修改数据的显示格式也是一个常用的技巧,如果使用手动去修改会非常麻烦,例如将10000修改成10,000这种千分位的展示形式,利用「数字格式」可以一键完成修改。

右击「数字格式」,选择「数值」,勾选「千分位」,小数点后位数为「0」,点击「确定」即可。

一键修改数据格式

5.10 快速分析数据

作为Excel里数一数二的数据分析工具,透视表用得最多的就是数据分析,可以快速进行交叉分析、对比分析、结构分析、汇总分析等等。

例如按照年度、季度、月份、天数对数据进行分组统计,利用数据透视表内置的数据分组,可以快速实现:

快速按照日期分析数据

快速对比两列数据,例如快速对比其他月份与1月的销售额差异百分比,利用透视表鼠标拖拽也非常方便:

快速进行数据对比

快速创建数据上下级,实现数据按照不同分类进行汇总:

按照不同分类进行汇总

学习资料:如何学习数据透视表?

将透视表划分成10个部分来系统学习,如:软件基础操作(基本术语、重整布局、数据源、字段重命名、延迟布局更新等)、数据刷新、值字段的单元格格式、排序与筛选、透视表分组计算、透视表虚拟字段、透视表动态查询、透视表布局与样式、动态透视图

对于透视表的学习,最重要的是多动手,多实践,才能快速学会数据统计分析的技巧,找到数据中隐藏的规律,碰到问题才能快速解决。

先来简单看下学习大纲(点击查看大图):

透视表学习路线大纲

01 透视表基础知识

熟悉数据透视表的基本术语、4大区域、常用操作对于后期深入学习数据透视表是非常重要的,这些基础知识看起来会比较枯燥,但是确实整个学习中最重要的部分。

1.1 数据源的基本要求

数据源是创建透视表的基础,并非所有的数据都适合创建透视表,它是具有一定要求的,判断数据是否规范,总共有5个方面:

数据源的基本要求

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

1.2 创建数据透视表

数据透视表的创建方法有多种,并且数据源也有不同的来源,并且每种创建方法的表现形式都存在差异,学会利用数据缓存来创建数据透视表,能让所有的透视表都共享缓存,文档会变得更小,运行速度会变得更快。

创建数据透视表

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

1.3 基本术语和4大区域

了解透视表中的项、字段、行区域、列区域、值区域、报表筛选区域:

基本术语和4大区域

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

1.4 透视表的基本操作

选择透视表的单元格、字段、项、整张表,重命名透视表,复制/移动/删除透视表是透视表的基本操作,这些是使用透视表的一个前提:

数据透视表的基本操作

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

1.5 透视表字段布局重命名

字段布局是调整字段在4大区域中的位置,通过鼠标拖拽的方式来进行快速数据分析,重命名会帮我们更直观观测透视表。

字段布局重命名

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

1.6 延迟布局更新

延迟布局更新在处理大量数据的时候效率会非常的高,使用延迟布局更新,可以在调整好字段布局之后才统一更新数据透视表,会大大减少我们等待的时间。

布局延迟更新

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

02 数据源的刷新

2.1 手动刷新数据源

透视表默认使用的都是缓存数据,当数据源更新的时候,透视表是不会自动更新的,需要我们提供过手动来刷新数据,通常数据源的改动有:数据改动、数据区域改动。

数据源的手动刷新

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

2.2 自动刷新数据源

除了手动刷新之外,可以设置文件打开的时候自动刷新数据,也可以通过vba,插入一个按钮,通过点击按钮就可以刷新数据。

数据源的自动刷新

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

2.3 刷新时的注意事项

在数据刷新后,往往会出现很多垃圾条目或者刷新后,单元格的样式、列宽全部改变了,这个时候可以通过数据透视表选项窗口来设置,知识点也不多,但是非常实用。

数据源刷新注意事项

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

03 值字段数字格式

3.1 数字格式设置

在透视表中也可以修改单元格的格式,在不更改数据的情况下可以改变透视表的显示方式。例如添加前缀、后缀、千分位等,让计算类型变成:求和、计数、平均、最大、最小等等。

数字格式设置

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

3.2 空值和错误值处理方式

透视表中难免会碰上错误值或者空值,如果不处理的话,会让报表看起来比较怪异。

空值和错误值处理方式

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

04 透视表筛选和排序

4.1 自动和手动排序

在透视表中,如果对显示的顺序不满意的话,可以通过透视表的排序功能来对数据进行排序,例如:手动排序、自动排、自定义排序、依据其他字段排序等。

手动和自动排序

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

4.2 筛选字段布局与排序

除了通过列筛选、行筛选来筛选数据,透视表还提供了报表筛选区域,更加简洁专业地让我们来筛选数据,在筛选区域中,数值也是可以进行排序的,并且可以改变分布的方式。

筛选字段布局与排序

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

05 透视表数据分组

5.1 数值和文本分组

在透视表中,我们可以将某类具有相同规律的数值分成一组,例如20~30岁的可以分成一组,同个姓氏的可以分成一组,这里就涉及到数值和文本分组了。

数字和文本快速分组

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

5.2 日期数据快速分组

日期数据也可以进行快速分组,不过需要满足是规范日期的条件,日期数据可以划分为:秒、分、小时、天、月、季度、年份等。

日期数据分组

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

06 透视表中的虚拟字段

6.1 常用的值显示方式

在透视表中,除了常规的无计算选项之外,还可以设置值的计算方式,例如显示成百分比、差异、差异百分比、父级百分比等,每种百分比都有不同的展示形式。

自定义值显示方式

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

6.2 计算字段的使用

在透视表中,如果当前的字段没办法满足我们的需求,这个字段又可以通过其他字段计算得到,这个时候我们就可以利用透视表提供的计算字段功能。

计算字段使用基础

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

6.3 计算项使用基础

计算项和计算字段的区别是,一个仅仅是一条数据,另外一个是一个字段,如果我们想在透视表中新增一条数据的话,就可以使用计算项。

计算项使用基础

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

07 动态查询数据

7.1 切片器实现交互式筛选

切片器是Excel2010提供的一个新功能,它提供了一个图形化交互筛选的方式,比筛选报表使用起来更加流畅舒服。

切片器使用

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

7.2 多表联动筛选小技巧

在透视表中,一个切片器是可以关联多个透视表的,这样的话,我们就可以实现,一次筛选,多次展示的效果。

多表联动筛选小技巧

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

7.3 切片器样式修改

在Excel中,系统内置了几十种切片器的样式,我们可以快速来修改切片器的样式,也可以通过自定义的方式来修改样式。

切片器样式与编辑

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

08 透视表布局与样式

8.1 透视表的3种布局

默认的透视表使用压缩布局,我们可以更改为大纲布局或者表格布局,让显示看起来更加宽松一些,同时还有项目标签的处理方式,可以通过合并单元格的形式来合并项目标签。

3种布局方式

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

8.2 透视表布局样式的快速修改

在Excel中,总共有85种内置的透视表样式,分为浅色、中浅深色、深色。除了使用内置的方式来修改,也可以自定义透视表的样式。

布局与样式快修改

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

09 动态透视图

9.1 创建/删除/移动/修改数据透视图

在Excel中,透视图是另外一个神器,我们可以通过3种方式来创建透视图,并且删除我们不想要的透视图,或者修改透视图的图表类型等。

透视图的创建/移动/删除

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

9.2 切片器联动透视图&迷你图的使用

在Excel中,如何制作这种具有动态交互功能的图表呢?包含:迷你图、动态图、展示数据、切片器等。

动态交互

这就要用到多个组件之间的相互联动了。

多个组件的联动和迷你图

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

9.3 数据透视图的使用限制

当然透视图并不是万能的,还是存在着一些限制的,例如透视图有3种不能创建的图表类型。

透视图使用限制

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

10 实战案例

10.1 在财务数据中的应用

可以利用透视表的多重合并区域来将多份表单进行汇总,从而达到按照月份、季度、上下半年、年度的方式快速汇总。

财务数据中的使用

学习传送门:Excel就该这样用,数据透视表篇 - 网易云课堂

10.2 在销售数据中的使用

如何利用透视表去挖掘销售数据中隐藏的信息呢?通过鼠标简单的拖一拖拽一拽就可以完成非常高级的仪表盘数据,并且还具备交互功能。

在销售数据中的使用

如果帮到您!点个赞吧!

相关模板

3秒登陆,即可下载

注册即同意《我拉网服务使用协议》&《我拉网隐私政策》

QQ

QQ

邮箱

电话

提交

手机绑定

绑定手机号,账户更安全

中国 +86

手机号格式错误

按住滑块,拖拽到最右边
>>

验证码错误

温馨提示:微信绑定手机号,手机端也可以登录,我拉网将对用户隐私信息给予严格保密。

取消