Excel大全 - 迅易阁资讯

Excel大全

2024-12-26 20:00

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零基础入门 - 网易云课堂

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函数来快速筛选组合。

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

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个部分来系统学习，如：软件基础操作（基本术语、重整布局、数据源、字段重命名、延迟布局更新等）、数据刷新、值字段的单元格格式、排序与筛选、透视表分组计算、透视表虚拟字段、透视表动态查询、透视表布局与样式、动态透视图。

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

先来简单看下学习大纲（点击查看大图）：

透视表学习路线大纲

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