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

新城市猎人

hiqingle：Excel数据分析——函数与公式（上）

查找与引用

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

• 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）
  

较为复杂的引用函数

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

引用函数

计数和求和

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

• 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)



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



条件求和

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

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

条件求和函数
加权求和、加权平均





排序

• 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）
  

统计

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

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

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