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函数进⾏统计，其参数中每两个参数形成⼀组关联条件区域和条件表达式（最⼤可以包含127组条件），因此在K15单元格输⼊公式： =COUNTIFS(L2:L10,">=1000",L2:L10,"<1300") *得到结果。
扩展：上述需求除了可以⽤COUNTIFS函数直接统计外，还可以通过两个COUNTIF函数分别统计再算差额，在K16单元格输⼊公式：
=COUNTIF(L2:L10,">=1000")-COUNTIF(L2:L10,">=1300") 得到结果；也可以在COUNTIF函数中运⽤数组参数作为计数条件，然后与数组相乘取得求和运算中的正负符号，最后⽤SUM函数求和得到差额。在K17单元格输⼊公式：=SUM(COUNTIF(L2:L10,">="& {1000,1300})*{1,-1}) 得到结果。
2.3 多字段多条件计数
需求2：沿⽤2.2的数据，统计⼯号以A或E开头的员⼯的汽油销售笔数。
思路：该需求属于多字段（⼯号和商品）多条件的类型，可直接使⽤COUNTIFS函数实现，分别统计⼯号以 “A”开头的员⼯的“汽
油”销售笔数以及⼯号以 “E”开头的员⼯的“汽油”销售笔数，相加即可得到结果。在K22单元格输⼊公式：
=COUNTIFS(J2:J10,“A*”,K2:K10,"*汽油")+COUNTIFS(J2:J10,“E*”,K2:K10,"*汽油") 得到结果。
扩展：上述需求也可以运⽤COUNTIFS函数对“⼯号”和“商品”两个字段进⾏多字段多条件计数，条件为模糊条件，需要运⽤通配
符“*”代表任意字符，对于“⼯号”字段条件，运⽤数组解决逻辑“或”的关系，计算结果为数组{2,2}，最后⽤SUM函数求和。在K23单元格输⼊公式：=SUM(COUNTIFS(J2:J10,{“A*”,“E*”},K2:K10,"*汽油")) 得到结果。
3 认识COUNTA与COUNTBLANK函数
3.1 检查数据填写的完整性
COUNTA函数可以返回单元格区域⾮空单元格个数；COUNTBLANK函数可以统计指定单元格区域中空⽩单元格的个数。
实例：如上表所⽰，固定资产清单中存在⼀些缺项，运⽤COUNTA和COUNTBLANK函数确认固定资产清单是否填写完毕。countifs函数怎么输入条件
要求：运⽤COUNTA和COUNTBLANK函数，从“内容是否完整”和“是否存在空值”两个不同的⾓度实现数据检查的功能。
思路：
（1）⽤COUNTA函数统计当前⾏中的字段填写个数，如果⾮空单元格个数与第⼆⾏中的标题个数相
同，则返回空值（表⽰已填写完整），如果有缺项，则返回“缺项”。在K3单元格输⼊公式：=IF(COUNTA(A3:I3)=COUNTA($A$2:$I$2),"",“缺项”) ，然后向下填充得到结果。
（2）⽤COUNTBLANK函数统计当前⾏中是否存在空值，如果计算结果⼤于0，即有空值，表⽰填写未完成，公式返回“缺项”，否则返回空⽂本。在L3单元格输⼊公式：=IF(COUNTBLANK($A3:$I3),“缺项”,"")，然后向下填充得到结果。
3.2 空与⾮空的判断
运⽤COUNTA和COUNTBLANK函数进⾏数据统计时，需要注意数据表中空值和⾮空值的判断。
实例：如图所⽰，从表⾯上看，员⼯信息表中的性别字段均⽆内容，但实际上已被设置了6种真假空的情况。
核查思路：
（1）⽤COUNTA函数统计当前⾏中的字段填写个数，如果⾮空单元格的个数为4，则返回空值（表⽰已填写完整），如果有缺项，则返
回“缺项”。在U3单元格输⼊公式：=IF(COUNTA($O3:$R3)=4,"",“缺项”) ，然后向下填充得到结果。
（2）⽤COUNTBLANK函数统计当前⾏中是否存在空值，如果计算结果⼤于0，即有空值，表⽰填写未完成，公式返回“缺项”，否则返回空⽂本。在V3单元格输⼊公式：=IF(COUNTBLANK($O3:$R3),“缺项”,"")，然后向下填充得到结果。
注意：
（1）COUNTA函数返回包含⽂本、假空单元格、逻辑值或错误值的结果，只有真空单元格不被计数，其参数可以是引⽤，也可以内存数组（指按Ctrl+shift+enter得到的结果）。
（2）⽽COUNTBLANK函数则返回单元格区域中单元格为空单元格或公式计算结果为空⽂本的个数，其参数只能是单元格引⽤，不能是内存数组。
4 应⽤SUMPRODUCT函数计算
4.1 应⽤SUMPRODUCT函数进⾏多条件求和计算
SUMPRODUCT函数将给定的⼏组数组中数组间对应的元素相乘，并返回乘积之和。利⽤这⼀特性，可以⽤该函数进⾏多条件求和、计数以及其他相关的数值计算。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。