excel高阶小技巧(Excel高级筛选技巧)

请用语音读文章

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

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

01 SUM函数（数学）SUM函数是Excel中非常常用的函数。求和也是数学函数的一个基本。先来简单看下SUM函数的语法：

SUM函数语法

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

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

SUM函数进行累计求和

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

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

IF函数语法

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

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

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

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

首先判断平均分是否大于等于80。是的话。为优秀。结束。否则进入2；判断平均分是否大于等于60。是的话。为合格。结束。否则进入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函数来超快筛选组合。

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)。返回第几列就开始引用第几列的单元格即可。

04 SUMIF函数（条件计数）SUMIF函数是SUM函数的条件版本。即满足相应的条件才进行计算：

SUMIF函数

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

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

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

SUMIF单条件求和

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

SUMIF函数有一个强化版本。即多条件版本——SUMIFS。用法与SUMIF差不多：

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个方面：

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

1.3 基本术语和4大位置知道透视表中的项、字段、行位置、列位置、值位置、报表筛选位置：

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

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

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

02 数据源的刷新2.1 手动刷新数据源透视表默认使用的都是缓存数据。当数据源更新的时候。透视表是不会自动更新的。需要咱们提供过手动来刷新数据。通常数据源的改动有：数据改动、数据位置改动。

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

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

03 值字段数字格式3.1 数字格式设置在透视表中也完全可以改写单元格的格式。在不更改数据的情况下可以改变透视表的显示方式。例如添加前缀、后缀、千分位等。让计算类型变成：求和、计数、平均、最大、最小等等。

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

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

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

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

5.2 日期数据超快分组日期数据也完全可以进行超快分组。不过需要满足是规范日期的条件。日期数据可以划分为：秒、分、小时、天、月、季度、日期等。

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

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

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

07 动态盘查数据7.1 切片器实现交互式筛选切片器是Excel2010提供的一个新功能。它提供了一个图形化交互筛选的方式。比筛选报表使用起来更加流畅舒服。

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

7.3 切片器样式改写在Excel中。系统内置了几十种切片器的样式。咱们可以超快来改写切片器的样式。也完全可以通过自定义的方式来改写样式。

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

8.2 透视表布局样式的超快改写在Excel中。总共有85种内置的透视表样式。分为浅色、中浅深色、深色。除了使用内置的方式来改写。也完全可以自定义透视表的样式。

09 动态透视图9.1 创建/删除/移动/改写数据透视图在Excel中。透视图是另外一个神器。咱们可以通过3种方式来创建透视图。并且删除咱们不想要的透视图。或者改写透视图的图表类型等。

9.2 切片器联动透视图&迷你图的使用在Excel中。如何制作这种具有动态交互功能的图表呢？包含：迷你图、动态图、展示数据、切片器等。

动态交互

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

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

10 实战案例10.1 在财务数据中的应用可以利用透视表的多重合并位置来将多份表单进行汇总。从而达到遵从月份、季度、上下半年、年度的方式超快汇总。

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

以上就是由优质生活领域创作者 生活常识网 整理编辑的，如果觉得有帮助欢迎收藏转发~