2.2.1 常用的统计分析函数
统计分析函数是数据分析中最常见的函数，常见的统计分析函数包括COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS、SUM、SUMIF、SUMIFS、AVERAGE、AVERAGEIF、AVERAGEIFS、MAX、DMAX、MIN、DMIN、LARGE、SMALL、RANK、SUMPRODUCT等。统计分析函数可以用来实现某一组数据最常见的几个统计指标计算，包括最大值、最小值、求和、平均值、计数、数值计数等。此外，还可以实现单个或者多个条件筛选下的统计，包括条件求最大值、条件求最小值、条件求和、条件求平均值、条件计数等。下面以某企业的客户投资表为例对这些统计分析函数的使用分别进行说明，数据如表2-1所示，字段包括客户姓名、城市、性别、年龄、投资时间、投资产品、投资金额（备注：数据范围位于A1:G9）。
表2-1 客户投资表
1.COUNT函数
功能说明：计算区域中包含数字的单元格的个数。
语法：COUNT(value1, [value2], …)
参数：
● value1必需。要计算其中数字的个数的第一项、单元格引用或区域。
● value2,…可选。要计算其中数字的个数的其他项、单元格引用或区域。
示例：统计表2-1所示的客户投资表中所有客户的累计投资次数。
公式与步骤：单元格I2内输入公式“=COUNT(G2:G9)”，结果如图2-11所示。
图2-11 所有客户累计投资次数
提示：利用COUNT函数对区域G2:G9内的数字进行计数。
2.COUNTA函数
功能说明：计算区域中非空单元格的个数。
语法：COUNTA(value1, [value2], …)
参数：
● value1必需。要计算其中数字的个数的第一项、单元格引用或区域。
● value2, … 可选。要计算其中数字的个数的其他项、单元格引用或区域。
示例：统计表2-1所示的客户投资表中年龄非空的客户数。
公式与步骤：单元格I2内输入公式“=COUNTA(D2:D9)”，结果如图2-12所示。
图2-12 年龄非空的客户数
提示：利用COUNTA函数对区域D2:D9内的非空单元格进行计数。
3.COUNTBLANK函数
功能说明：计算某个区域中空单元格的数目。
语法：COUNTBLANK(range)
参数：range必需。要计算其中空白单元格个数的区域。
示例：统计表2-1所示的客户投资表中年龄为空的客户数。
公式与步骤：单元格I2内输入公式“=COUNTBLANK(D2:D9)”，结果如图2-13所示。
图2-13 年龄空值的客户数
提示：利用COUNTBLANK函数对区域D2:D9内的空值单元格进行计数。
4.COUNTIF函数
功能说明：统计满足某个条件的单元格的数量。
语法：COUNTIF(range,criteria)
参数：
● range必需。在其中计算关联条件的唯一区域。
● criteria必需。条件的形式为数字、表达式、单元格引用或文本。
示例：统计表2-1所示的客户投资表中不同性别的客户数。
公式与步骤：单元格J2内输入公式“=COUNTIF(C:C,I2)”，然后向下拖拽复制公式，结果如图2-14所示。
图2-14 不同性别的客户数
提示：这里可以用COUNTIF函数来统计，因为是单条件计数。
5.COUNTIFS函数
功能说明：将条件应用于跨多个区域的单元格，然后统计满足所有条件的单元格的数量。
语法：COUNTIFS(criteria_range1,criteria1, criteria_range2,criteria2, …)
参数：
● criteria_range1必需。在其中计算关联条件的第一个区域。
● criteria1必需。条件的形式为数字、表达式、单元格引用或文本。例如，条件可以表示为30、"＞38"、B4、"上海"或 "A"。
● criteria_range2, criteria2, … 可选。附加的区域及其关联条件。
示例：统计表2-1所示的客户投资表中城市为“上海”且性别为“F”的客户人数。
公式与步骤：单元格I2内输入公式“=COUNTIFS(B:B,"上海",C:C,"F")”，结果如图2-15所示。
图2-15 上海的女性客户数
提示：这里不能用COUNTIF函数，需要用COUNTIFS函数来统计，因为是多条件计数。
6.SUM函数
功能说明：计算单元格区域中所有数值的和。
语法：SUM(number1,[number2], …)
参数：
● number1必需。要相加的第一个数字或范围。
● number2, …可选。要相加的其他数字或单元格区域。
示例：统计表2-1所示的客户投资表中所有客户的累计投资金额。
公式与步骤：单元格I2内输入公式“=SUM(G2:G9)”，结果如图2-16所示。
图2-16 所有客户的累计投资金额
提示：当区域G2:G9出现#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!这些错误类型，不能用直接SUM函数来进行求和，可以用SUMIF或者SUMIFS来计算。例如，当区域G2:G9出现#N/A错误时，统计累计投资金额的公式为“=SUMIF (G2:G9,"＜9e307")”或数组公式“{=SUM(IFERROR(G2:G9,0)*1)}”。
7.SUMIF函数
功能说明：对满足条件的单元格求和（单条件求和）。
语法：SUMIF(range,criteria,[sum_range])
参数：
● range必需。根据条件进行计算的单元格的区域。每个区域中的单元格必须是数字或名称、数组或包含数字的引用。
● criteria必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。
● sum_range可选。要求和的单元格区域。
示例：统计表2-1所示的客户投资表中性别字段为“M”的客户投资金额之和。
公式与步骤：单元格I2内输入公式“=SUMIF(C:C,"M",G:G)”，结果如图2-17所示。
图2-17 男性客户的投资金额之和
提示：这里可以用SUMIF函数来统计，因为是单条件求和。
8.SUMIFS函数
功能说明：对一组给定条件指定的单元格求和（多条件求和）。
语法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2],[criteria2], …)
参数：
● sum_range可选。要求和的单元格区域。
● criteria_range1必需。根据条件进行计算的单元格的区域1。
● criteria1必需。用于确定对哪些单元格求和的条件1。
● criteria_range2, criteria2, …可选。附加的区域及其关联条件。
示例：统计表2-1所示的客户投资表中城市为“广州”且性别为“F”的客户投资金额之和。
公式与步骤：单元格I2内输入公式“=SUMIFS(G:G,B:B,"广州",C:C,"F")”，结果如图2-18所示。
图2-18 广州女性客户的投资金额之和
提示：这里只能用SUMIFS函数来统计，因为是多条件求和。
9.AVERAGE函数
功能说明：返回一组值中的平均值。
语法：AVERAGE(number1,[number2], …)
参数：
● number1必需。要计算平均值的第一个数字、单元格引用或单元格区域。
● number2, …可选。要计算平均值的其他数字、单元格引用或单元格区域。
示例：统计表2-1所示的客户投资表中所有客户的平均投资金额。
公式与步骤：单元格I2内输入公式“=AVERAGE(G2:G9)”，结果如图2-19所示。
图2-19 所有客户的平均投资金额
10.AVERAGEIF函数
功能说明：返回满足单个条件的所有单元格的平均值（算术平均值）。
语法：AVERAGEIF(range,criteria,[average_range])
参数：
● range必需。根据条件进行计算的单元格的区域。每个区域中的单元格必须是数字或名称、数组或包含数字的引用。
● criteria必需。用于确定对哪些单元格求平均的条件，其形式可以为数字、表达式、单元格引用、文本或函数。
● average_range可选。要求平均的单元格区域。
示例：统计表2-1所示的客户投资表中性别字段为“F”的用户平均投资金额。
公式与步骤：单元格I2内输入公式“=AVERAGEIF(C:C,"F",G:G)”，结果如图2-20所示。
图2-20 女性用户的平均投资金额
提示：这里可以用AVERAGEIF函数来统计，因为是单条件求平均值。
11.AVERAGEIFS函数
功能说明：返回满足多个条件的所有单元格的平均值（算术平均值）。
语法：AVERAGEIFS(average_range,criteria_range,criteria, …)
参数：
● average_range可选。要求平均的单元格区域。
● criteria_range1必需。根据条件进行计算的单元格的区域1。
● criteria1必需。用于确定对哪些单元格求平均的条件1。
● criteria_range2, criteria2, …可选。附加的区域及其关联条件。
示例：统计表2-1所示的客户投资表中城市为“上海”且性别为“M”的用户平均投资金额。
公式与步骤：单元格I2内输入公式“=AVERAGEIFS(G:G,B:B,"上海",C:C,"M")”，结果如图2-21所示。
图2-21 上海男性客户的平均投资金额
提示：这里只能用AVERAGEIFS函数来统计，因为是多条件求平均值。
12.MAX函数
功能说明：返回一组值中的最大值。
语法：MAX(number1,[number2], …)
参数：
● number1必需。求最大值的第一个数字或范围。
● number2, …可选。求最大值的其他数字或单元格区域。
示例：统计表2-1所示的客户投资表中所有客户的最大投资金额。
公式与步骤：单元格I2内输入公式“=MAX(G2:G9)”，结果如图2-22所示。
图2-22 所有客户的最大投资金额
13.DMAX函数
功能说明：返回列表或数据库中满足指定条件的记录字段（列）中的最大数字。
语法：DMAX(database,field,criteria)
参数：
● database必需。构成列表或数据库的单元格区域。
● field必需。指定函数所使用的列，输入两端带双引号的列标签。
● criteria可选。包含所指定条件的单元格区域。可以为参数criteria指定任意区域，只要此区域包含至少一个列标签，并且列标签下至少有一个在其中为列指定条件的单元格。
示例：统计表2-1所示的客户投资表中城市为“上海”且性别为“F”的女性客户最大投资金额。
公式与步骤：单元格K2内输入公式“=DMAX($A$1:$G$9,$G$1,I1:J2)”，结果如图2-23所示。
图2-23 上海女性客户的最大投资金额
14.MIN函数
功能说明：返回一组值中的最小值。
语法：MIN(number1,[number2], …)
参数：
● number1必需。求最小值的第一个数字或范围。
● number2, … 可选。求最小值的其他数字或单元格区域。
示例：统计表2-1所示的客户投资表中所有客户的最小投资金额。
公式与步骤：单元格I2内输入公式“=MIN(G2:G9)”，结果如图2-24所示。
图2-24 所有客户的最小投资金额
15.DMIN函数
功能说明：返回列表或数据库中满足指定条件的记录字段（列）中的最小数字。
语法：DMIN(database,field,criteria)
参数：
● database必需。构成列表或数据库的单元格区域。
● field必需。指定函数所使用的列，输入两端带双引号的列标签。
● criteria可选。包含所指定条件的单元格区域。可以为参数criteria指定任意区域，只要此区域包含至少一个列标签，并且列标签下至少有一个在其中为列指定条件的单元格。
示例：统计表2-1所示的客户投资表中城市为“上海”且性别为“F”的女性客户的最小投资金额。
公式与步骤：单元格K2内输入公式“=DMIN($A$1:$G$9,$G$1,I1:J2)”，结果如图2-25所示。
图2-25 上海女性客户的最小投资金额
16.LARGE函数
功能说明：返回数据集中第k个最大值。
语法：LARGE(array,k)
参数：
● array必需。需要确定第k个最大值的数组或数据区域。
● k必需。返回值在数组或数据单元格区域中的位置（从大到小）。
示例：统计表2-1所示的客户投资表中单次投资排名第二的投资金额。
公式与步骤：单元格I2内输入公式“=LARGE(G2:G9,2)”，结果如图2-26所示。
图2-26 单次投资排名第二的投资金额
17.SMALL函数
功能说明：返回数据集中第k个最小值。
语法：SMALL(array,k)
参数：
● array必需。需要确定第k个最小值的数组或数据区域。
● k必需。返回值在数组或数据单元格区域中的位置（从小到大）。
示例：统计表2-1所示的客户投资表中单次投资排名倒数第二的投资金额。
公式与步骤：单元格I2内输入公式“=SMALL(G2:G9,2)”，结果如图2-27所示。
图2-27 单次投资排名倒数第二的投资金额
18.RANK函数
功能说明：返回一组数字中的某个数字的排序位置。
语法：RANK(number,ref,[order])
参数：
● number必需。要找到其排序位置的数字。
● ref必需。数字列表的数组，对数字列表的引用。
● order可选。指定数字排序位置方式的数字。如果order为0或省略，默认按照降序排列。
示例：统计表2-1所示的客户投资表中投资金额的降序排名和升序排名。
公式与步骤：
单元格H2内输入公式“=RANK(G2,$G$2:$G$9,0)”。
单元格I2内输入公式“=RANK(G2,$G$2:$G$9,1)”，结果如图2-28所示。
图2-28 投资金额的降序排名和升序排名
19.SUMPRODUCT函数
功能说明：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
语法：SUMPRODUCT(array1,[array2],[array3], …)
参数：
● array1必需。其相应元素需要进行相乘并求和的第一个数组参数。
● array2,array3, … 可选。2到255个数组参数，其相应元素需要进行相乘并求和。
示例：以某超市的产品销售表为例，统计产品销量总额，数据如表2-2所示。
表2-2 某超市的产品销售表
公式与步骤：单元格F2内输入公式“=SUMPRODUCT(C2:C8,D2:D8)”，结果如图2-29所示。
图2-29 某超市的产品销量总额
以上是对统计分析函数的介绍，并通过示例对函数进行了功能讲解，下面的统计分析函数案例一～案例五是统计分析函数的应用扩展。对于同一个案例，采用了多种方法来解决。
20.统计分析函数案例一
仍然以表2-1为例，统计城市为“上海”且性别为“M”的数据行数。
公式与步骤：
● 方法一：单元格J2内输入公式“=COUNTIFS(B:B,"上海",C:C,"M")”。
● 方法二：单元格J3内输入公式“=SUMPRODUCT((B2:B9="上海")*(C2:C9="M"))”。
● 方法三：单元格J4内输入公式“=SUMPRODUCT((B2:B9="上海")+0,(C2:C9="M")+0)”。
● 方法四：单元格J5内输入数组公式“{=SUM((B2:B9="上海")*(C2:C9="M"))}”，结果如图2-30所示。
图2-30 上海男性客户的投资人数
提示：
● 方法一是用COUNTIFS函数在B列筛选“上海”，C列筛选“M”，然后对筛选后的数据统计行数。
● 方法二和方法三是将区域B2:B9的数据与字符“上海”判断是否相等，相等返回TRUE，不相等返回FALSE，从而生成布尔值数组，然后将数组加0或乘以1转换成数值类型的数组。同理，区域C2:C9通过判断转换也会生成一个数值类型的数组，最后用SUMPRODUCT函数对这两个数组进行交叉乘积求和。
● 方法四与方法二相似，二组数据进行交叉乘积后用SUM数组公式求和。SUM数组公式两端的花括号{}是在公式输入完毕之后一起按〈Ctrl+Shift+Enter〉组合键创建的，手动输入无效。
21.统计分析函数案例二
以表2-1为例，统计性别字段为“M”的客户投资金额之和。
公式与步骤：
● 方法一：单元格J2内输入公式“=SUMIF(C:C,"M",G:G)”。
● 方法二：单元格J3内输入公式“=SUMIFS(G:G,C:C,"M")”。
● 方法三：单元格J4内输入公式“=SUMPRODUCT((C2:C9="M")*(G2:G9))”。
● 方法四：单元格J5内输入公式“=SUMPRODUCT((C2:C9="M")+0,(G2:G9))”。
● 方法五：单元格J6内输入数组公式“{=SUM((C2:C9="M")*(G2:G9))}”，结果如图2-31所示。
图2-31 男性客户的投资金额
提示：
● 本案例是满足单条件进行求和，因此方法一的SUMIF函数和方法二的SUMIFS函数都可以使用。不过需要注意的是SUMIF函数的第三个参数是sum_range，而SUMIFS函数的第一个参数是sum_range。另外，SUMIF函数中criteria_range与sum_range如果相同，则sum_range参数可以省略。
● 方法三、方法四与方法五是将区域C2:C9的数据与字符"M"判断是否相等，相等返回TRUE，不相等返回FALSE，从而生成布尔值数组，然后用SUM数组公式或SUMPRODUCT函数公式对布尔值数组与投资金额数组进行交叉乘积求和。
● SUMIF和SUMIFS函数中，criteria_range参数与sum_range参数必须包含相同的行数和列数。
22.统计分析函数案例三
以表2-1为例，统计客户王飞和陈生的投资金额之和。
公式与步骤：
● 方法一：单元格J2内输入公式“=SUMIFS(G2:G9,A2:A9,"王飞")+SUMIFS(G2:G9, A2:A9,"陈生")”。
● 方法二：单元格J3内输入公式“=SUM(SUMIFS(G2:G9,A2:A9,{"王飞","陈生"}))”，结果如图2-32所示。
图2-32 王飞和陈生的投资金额之和
提示：
● 方法一分别对王飞和陈生进行条件求和，相当于根据名字匹配查找其他字段数值，最后将查找出来的数值进行求和。
● 方法二将客户王飞和陈生组成一个数组{"王飞","陈生"}，然后将此数组作为SUMIFS函数的criteria参数来分别进行条件求和，最后用SUM函数进行求和。
23.统计分析函数案例四
以表2-1为例，统计投资金额前两名的客户的投资金额之和。
公式与步骤：
● 方法一：单元格J2内输入公式“=SUM(LARGE(G2:G9,{1,2}))”。
● 方法二：单元格J3内输入公式“=SUM(SUMIF(G2:G9,LARGE(G2:G9,{1,2})))”。
● 方法三：单元格J4内输入公式“=SUMPRODUCT((G2:G9＞LARGE(G2:G9,3))*(G2:G9))”，结果如图2-33所示。
图2-33 投资金额前两名的客户投资金额总和
提示：
● 方法一是在LARGE函数外层嵌套SUM函数进行求和计算。LARGE函数的第二个参数k对应的值是数组{1,2}，可以分别将G2:G9范围内第一大值和第二大值分别取出来，最后用SUM函数进行求和。
● 方法二是用LARGE函数将G2:G9范围内的第一大值和第二大值分别取出来，然后用SUMIF函数在G2:G9范围内对这两个数值进行求和。由于求和范围G2:G9和条件范围G2:G9是一致的，因此SUMIF函数的第三个参数sum_range可以省略。
● 方法三是用LARGE函数取出投资第三名的客户，然后判断区域G2:G9内哪些是大于投资第三名的，大于返回TRUE，否则返回FALSE，从而生成一个布尔值数组，最后用SUMPRODUCT函数将区域G2:G9组成的数组与布尔值数组进行交叉乘积求和。
24.统计分析函数案例五
以表2-1为例，统计姓张的投资客户数、姓李的客户的投资金额之和。
公式与步骤：
● 姓张的投资客户数：单元格J2内输入公式“=COUNTIFS(A2:A9,"张*")”。
● 姓李的客户的投资金额之和：单元格J3内输入公式“=SUMIFS(G2:G9,A2:A9,"李*")”，结果如图2-34所示。
图2-34 姓张的投资用户数、姓李的投资金额之和
提示：利用通配符"*"对客户名称进行模糊匹配，然后再进行条件计数或条件求和。Excel里面的通配符有"*"、"?"。这里的"*"代表任意字符，"?"代表1个字符。例如，查找客户名字包含张，应该用"*张*"进行模糊匹配；查找姓张且名字总长度为两位的客户，应该用"张?"进行模糊匹配。