中 Excel数据分析——函数与公式_excel

本文共计约3000字，建议阅读时间3分钟

函数学习必备知识

单元格引用
函数小贴士

基础函数

文本转化
日期和时间处理
数值计算

进阶函数

查找与引用

XLOOKUP，比VLOOKUP更强
较为复杂的引用函数

计数和求和

SUBTOTAL与SUM的区别
条件求和
加权求和、加权平均

排序

常见应用场景

统计

高级函数

数组函数
VBA自定义函数
其他场景下的函数应用
关于函数使用的建议

本篇开始讲Excel函数与公式的中篇——进阶函数，上篇基础函数请移步Excel数据分析——函数与公式（上）。
二、进阶函数
1、查找与引用

查找与引用类型函数是相对比较复杂的一类函数，我们先将比较重要的这类函数的基本用法示例说明，然后再针对比较常用的函数组合用法详细展开。

? 查找与引用函数

CHOOSE：从值的列表中选择值
IFS：检查是否满足一个或多个条件，且是否返回与第一个 TRUE 条件对应的值。
SWITCH：根据值列表计算表达式，并返回与第一个匹配值对应的结果。如果不匹配，则可能返回可选默认值。
ROW：返回引用的行号
ROWS：返回引用中的行数
COLUMN：返回引用的列号
COLUMNS：返回引用中包含的列数
ADDRESS：以文本形式将引用值返回到工作表的单个单元格
INDIRECT：返回由文本值指定的引用
INDEX：使用索引从引用或数组中选择值
OFFSET：从给定引用中返回引用偏移量
MATCH：在引用或数组中查找值
XMATCH：返回项目在数组或单元格区域中的相对位置。match_mode缺省为精确匹配， match_mode为1 ，为返回精确匹配或下一个最大项
VLOOKUP：在数组第一列中查找，然后在行之间移动以返回单元格的值
XLOOKUP（VLOOKUP的升级版）：搜索区域或数组，并返回与之找到的第一个匹配项对应的项。如果不存在匹配项，则 XLOOKUP 可返回最接近（近似值）的匹配项。
HYPERLINK：创建一个快捷方式，用于打开硬盘、服务器或Internet上的文档

XLOOKUP，比VLOOKUP更强

世人只知VLOOKUP，而不是XLOOKUP，殊不知XLOOKUP比VLOOKUP其实更好用，弥补了VLOOKUP的很多缺陷。刚刚结果的VLOOKUP的复杂用法，其中不乏一些巧思，但用XLOOKUP来解决，就变得异常简单了。下边的几个例子中，每个例子都用了多种解法，整体对比下来，XLOOKUP函数比VLOOKUP使用上更简约、理解上更容易。

单列查找，结果多列（VLOOKUP MATCH、VLOOKUP COLUMN、XLOOKUP数组）
在左边的列中查找值（VLOOKUP IF数组、INDEX MATCH、XLOOKUP）
双列查找（VLOOKUP IF数组、INDEX MATCH数组、XLOOKUP）
在多个表格中查找（VLOOKUP IF、XLOOKUP IF）

? 查找与引用函数示例1——XLOOKUP vs VLOOKUP

较为复杂的引用函数

确定一个值在区域内的单元格地址（ADDRESS）
多行多列转换为一列（OFFSET）
引用区域文本转化为参数化的引用（INDIRECT）
ROW与ROWS的区别，COLUMN与COLUMNS同理

? 查找与引用函数示例2

2、计数和求和

前面提到的函数是放任何领域都比较通用的函数，而从这里开始之后的函数，可能在数据分析领域更为常见一些。我们先介绍最最最常用的计数和求和函数。

? 计数和求和函数

COUNT：计算参数列表中数字的个数
COUNTIF：计算区域内符合给定条件的单元格的数量
COUNTIFS：计算区域内符合多个条件的单元格的数量
SUBTOTAL：返回列表或数据库中的分类汇总
SUM：求参数的和
SUMIF：按给定条件对指定单元格求和
SUMIFS：在区域中添加满足多个条件的单元格
SUMPRODUCT：返回对应的数组元素的乘积和
AVERAGE：返回其参数的平均值
AVERAGEIF：返回区域中满足给定条件的所有单元格的平均值（算术平均值）
AVERAGEIFS：返回满足多个条件的所有单元格的平均值（算术平均值）

计数和求和函数主要分三组，计数、求和和平均（即求和除以计数），每组中均包含XX、XXIF和XXIFS的函数，另外求和函数还多了一个SUBTOTAL和SUMPRODUCT函数。
由于三组函数用法基本相同，本节以求和函数为示例来介绍。

SUBTOTAL与SUM的区别

快捷键ALT =可以快速填充求和公式，与手写SUM公式的好处在于不用挨个选择求和区域，即可以自动选择（连续）求和区域。有两种用法。
第一种，Ctrl多选求和公式所在单元格后ALT =，这时公式是SUM(X:X)

? Ctrl多选求和公式所在单元格后ALT =

第二种，筛选可见单元格后ALT =，这时对应的公式是=SUBTOTAL(9,X:X)，SUBTOTAL函数有个特性是可以仅对可见单元格求和（需要把=SUBTOTAL(9,X:X)改成=SUBTOTAL(109,X:X)），这也是SUM函数做不到的。

? 筛选可见单元格后ALT =

条件求和
说到求和函数，必须重点讲一下SUMIF，SUMIF仅针对满足条件的数据求和，也就是筛选后求和。这个筛选可以是数据本身，也可以是数据所在行的其他字段，筛选方式可以是数值比较，也可以是匹配通配符。
下面这张图可以讲清楚常用的条件求和函数。一题多解，主要是为了拓展函数思维。如有疑问，可在评论中提出来，我看到会解答的。

单条件求和（SUMIF）
多条件求和（SUMIFS、SUMPRODUCT、SUM数组公式、SUM SUMIF）

? 条件求和示例

加权求和、加权平均

? 加权求和、加权平均示例

3、排序

? 排序函数

MAX：返回参数列表中的最大值
MAXIFS：返回一组给定条件或标准指定的单元格之间的最大值
MIN：返回参数列表中的最小值
MINIFS：返回一组给定条件或标准指定的单元格之间的最小值。
LARGE：返回数据集中第K个最大值
SMALL：返回数据集中第K个最小值
MEDIAN：返回给定数值集合的中值
TRIMMEAN：返回数据集的内部平均值
QUARTILE.EXC：基于从0到1之间（不含0与1）的百分点值，返回一组数据的四分位点
QUARTILE.INC：基于从0到1之间（含0与1）的百分点值，返回一组数据的四分位点
RANK.AVG：返回某个数字在一列数字中相对于其他数字的大小排位，如果多个数值排名相同则返回平均排名
RANK.EQ：返回某个数字在一列数字中相对于其他数字的大小排位，如果多个数值排名相同则返回最佳排名
PERCENTILE.EXC：返回区域中数值的第k个百分点的值，此处的k的范围为0到1（不含0和1）
PERCENTILE.INC：返回区域中数值的第k个百分点的值，此处的k的范围为0到1（含0和1）
PERCENTRANK.EXC：返回特定数值在一组数中的百分比排名（介于0与1之间，不含0与1）
PERCENTRANK.INC：返回特定数值在一组数中的百分比排名（介于0与1之间，含0与1）

排序函数的常见应用

最大值、最小值（MAX、MIN）
次大值、次小值（LARGE、SMALL）
指定数字的排名（RANK、COUNTIF）
不计重复的次大值（LARGE COUNTIF MAX）
大于平均值的最小值（LARGE/SMALL COUNTIF AVERAGE、MIN IF AVERAGE）
前/后N名平均分（AVERAGE LARGE/SMALL ROW/COLUMN）
去掉前后N名平均分（TRIMMEAN）

【中 Excel数据分析——函数与公式】

? 排序函数常用应用

4、统计

较为简单，这里只简单示例一下。

? 统计函数

STDEVA：估算样本（包括数字、文本和逻辑值）标准偏差
STDEVPA：计算样本总体（包括数字、文本和逻辑值）标准偏差
VARA：估算样本（包括数字、文本和逻辑值）方差
VARPA：计算样本总体（包括数字、文本和逻辑值）方差
RAND：返回 0 和 1 之间的一个随机数
RANDBETWEEN：返回位于两个指定数之间的一个随机数
CORREL：返回两个数据集之间的相关系数

统计类型函数，还有各类分布的对应函数，如正态分布的NORM.DIST函数和γ分布的GAMMA.DIST函数。我实际工作中几乎没用到过，感兴趣的可以自己再深入学习了解。

本篇文章字数不多，但信息密度较高，别看每类函数都只是小小一张图，但其实内容是相对比较难的。建议先收藏下来，慢慢啃。
有任何问题或者还有啥想了解的，欢迎私信我。

- END -

往期推荐

Excel数据分析——数据输入
Excel数据分析——数据处理
Excel数据分析——数据透视表
Excel数据分析——函数与公式（上）
数据分析必备五大思维（一）——结构化思维
数据分析必备五大思维（二）——分类思维
数据分析必备五大思维（三）——对比思维
数据分析必备五大思维（四）——统计思维
数据分析必备五大思维（五）——归因思维

中 Excel数据分析——函数与公式