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上的文档

世人只知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函数。

由于三组函数用法基本相同，本节以求和函数为示例来介绍。

快捷键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）

▴ 排序函数常用应用

4、统计

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

▴ 统计函数

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

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

本篇文章字数不多，但信息密度较高，别看每类函数都只是小小一张图，但其实内容是相对比较难的。建议先收藏下来，慢慢啃。

有任何问题或者还有啥想了解的，欢迎私信我。

- END -

往期推荐

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

-如果喜欢这篇文章，请关注我