Excel常用统计分析函数分享

1 平均值计算

1.1 无条件平均值计算：AVERAGE

通常我们习惯直接使用AVERAGE函数进行数据的平均值计算，其实该函数可以与其他函数组合运用，来满足更复杂的平均值计算。

实例：如图数据所示，要求在不进行排序的情况下计算出销量前5名销售的平均销量。

思路解析：在B16单元格输入数组公式：{=AVERAGE(LARGE(B3:B13,ROW(1:5)))}，公式先利用LARGE函数求得销量前5名销售的销量数组，再用AVERAGE函数求出平均值。

涉及的函数：ROW、LARGE、AVERAGE

思考：如果需求改为“要求在不进行排序的情况下计算出销量末尾5名销售的平均销量”，该如何实现？
答案：在B19单元格输入数组公式：{=AVERAGE(SMALL(B3:B13,ROW(1:5)))}，该公式和上述公式类似，只是将large函数换成了small函数。

注意：需按Ctrl+Shift+Enter三键输入数组公式，才能得到想要的结果。

1.2 单一条件下的平均值计算：AVERAGEIF

AVERAGEIF函数：AVERAGEIF(条件区域，条件，[求平均值区域])

下面数据是一份销售报表，由于星期六、星期日为休息日，无销售额发生，因此金额栏中的值为0（用“-”代替）。

需求1：分别计算星期一至星期五的平均销售额，用于分析星期一至星期五的平均销售额变动情况。

思路：在N4单元格输入公式：=AVERAGEIF($H$3:$H$30,M4,$I$3:$I$30)，然后向下填充公式，公式以具体星期作为条件，使用AVERAGEIF函数计算平均值。

思考：如果上述问题使用AVERAGE函数实现，要怎么做？
答案：在O4单元格输入数组公式：{=AVERAGE(IF($H$3:$H$30=M4,$I$3:$I$30))}，然后向下填充公式，公式结合IF函数用逻辑判断H3到H30单元格区域的内容是否与M4单元格的内容相等，然后返回I3到I30单元格区域中属于M4单元格的值，最后用AVERAGE函数求平均值。

需求2：计算2014年5月1日至28日之间有效工作日的日均销售额。

思路：在N12单元格输入公式：=AVERAGEIF(I3:I30,">0")，这里的有效工作日是指非周六周日，因此只需要计算金额列非0单元格的平均值即可。

注意：当AVERAGEIF函数省略第三参数时，将使用第一参数同时作为条件判断与数值计算的区域。

1.3 多条件下的平均值计算：AVERAGEIFS

AVERAGEIF函数：AVERAGEIFS(求平均值区域，条件区域1，条件1，[条件区域2，条件2]，[条件区域3，条件3]，……)

需求3：沿用1.2的数据，要求计算中旬有效工作日的平均销售额。

思路：该需求有两个条件，一个是中旬（即11日至20日），一个是有效工作日（即金额列非0的单元格），因此在N16单元格输入公式：=AVERAGEIFS(I3:I30,G3:G30,">=2014年5月11日",G3:G30,"<=2014年5月20日",I3:I30,">0")。

注意：AVERAGEIFS函数的条件区域和条件值组成一组条件参数，最多允许设置127组条件参数（一般不会设置这么多条件）。

2 按指定条件计数

2.1 条件判断介绍

COUNTIF函数和COUNTIFS函数可以统计满足一定条件的单元格个数，条件参数中可以使用比较运算符和通配符。上面展示了COUNTIF函数常用的公式用法，这些公式同样适用于COUNTIFS函数，在仅有一个条件参数的情况下，两者的运算结果完全相同。
设置COUNTIF函数或COUNTIFS函数的条件参数时，需要注意以下一些情况：

• 判断条件 “<>”：这个条件参数表示不等于 ”真空”，“真空”表示单元格内没有任何数据，是真正的空单元格，设置这个判断条件可统计非真空单元格的个数；
• 判断条件 “><”：这个条件参数仅表示统计大于 “<”符号的文本，注意区分 “<>”和 “><”的区别；
• 判断条件 “=”：这个条件参数表示等于 “真空”，可用于统计真正空单元格的个数；
• 判断条件“”：这个条件参数表示包含真空单元格及空文本，其中的 “空文本”一般是指由公式计算得到的结果；
• 判断条件 “*”：这个条件参数代表所有文本，包括空格以及空文本，但不包含真空单元格，也不包含数值、逻辑值、错误值等数据单元格；
• 判断条件 “<>”””：这个条件参数的含义并不代表“不等于空文本”，而仅仅只表示不等于单个双引号 (“ )；
• 判断条件 “?*”：这个条件参数表示统计所有单元格长度不为0的文本单元

2.2 单字段多条件计数

数据：

需求1：统计销售量大于等于1000且小于1300的记录个数。

思路：该需求属于单个字段列多个条件的类型，可使用COUNTIFS函数进行