我上周写过一篇用数据透视表的方式求各班级平均分,即带条件求平均值,具体可参见 Excel – 公式3小时,数据透视表1分钟,计算各班平均分。
更早的时候,在讲解 sumproduct 函数的时候,也写过用这个函数怎么解决类似问题,可参见 Excel函数(四) – sumproduct函数计数、排名、求和等等
今天我教大家两个新函数 averageif 和 averageifs,分别用来解决带单一条件和多条件求平均值,操作也非常简单,因为不需要跟其他函数组合使用。
案例:
下图 1 是各班学生的高考成绩,需要如下图 2 所示,求以下两种平均分:
1) 每个班级的高考平均分
2) 去除 600 分以上的成绩后,各班的平均分
![9e15676b53b2fbe2a889fa40749ae890.png](https://img-blog.csdnimg.cn/img_convert/9e15676b53b2fbe2a889fa40749ae890.png)
![c21350523342e97473ee7c6939de56d7.png](https://img-blog.csdnimg.cn/img_convert/c21350523342e97473ee7c6939de56d7.png)
解决方案 1:求每个班级的高考平均分
本需求是比较典型的带一个条件求平均值,可以用 AverageIf 函数来解决。
AverageIf 函数语法:
AVERAGEIF(Range,Criteria,[Average_Range])
- Range:需要求平均值的区域范围;如果有 Average_Range,则 Range 为条件区域范围
- Criteria:条件
- Average_Range:求平均值的实际区域范围
- 如果 Range 或 Average_Range 选定的单元格中有逻辑值 True 或 False,它们将被忽略
- 如果 Average_Range 选定的单元格为空单元格,将被忽略
- Criteria 可以是文字,也可以用大小于和等于号
- 如果条件 Criteria 包含空单元格,将被视为 0 值;如果选定的区域没有满足条件的单元格,将返回错误值
1. 了解完语法,现在可以开始设置公式了:在 F2 单元格输入以下公式:
=AVERAGEIF(A:A,E2,C:C)
公式释义:
- A:A:条件范围
- E2:条件值,即“一班”
- C:C:求平均值的实际区域范围
![3c61968f04cb279f48bc43877e1ebdb4.png](https://img-blog.csdnimg.cn/img_convert/3c61968f04cb279f48bc43877e1ebdb4.png)
2. 这就是得到的结果
![eef249b63a74c1c4735f45b104cf4426.png](https://img-blog.csdnimg.cn/img_convert/eef249b63a74c1c4735f45b104cf4426.png)
3. 把单元格格式设置为保留一位小数,向下拖动公式,即可得到各班平均分
![55a6f304f22710c1017cbf18a580b728.png](https://img-blog.csdnimg.cn/img_convert/55a6f304f22710c1017cbf18a580b728.png)
解决方案 2:求去除 600 分以上的成绩后,各班的平均分
本需求有两个条件:一个是按班级求平均值,另一个是去除 600 分以上的成绩,所以需要用 AverageIfs 函数。
AverageIfs 函数语法:
AVERAGEIFS(Average_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria2,...)
- Average_Range:求平均值的区域范围
- Criteria_Range1,Criteria_Range2...:条件区域
- Criteria1,Criteria2...:条件
1. 现在开始设置公式:在 G2 单元格输入以下公式:
=AVERAGEIFS(C:C,A:A,E2,C:C,"<600")
公式释义:
- C:C:求平均值的实际区域范围
- A:A:条件范围 1
- E2:条件 1,即“一班”
- C:C:条件范围 2
- "<600":条件 2
![a8293168e02ea1c99ccd577089b8fc08.png](https://img-blog.csdnimg.cn/img_convert/a8293168e02ea1c99ccd577089b8fc08.png)
2. 把单元格格式设置为保留一位小数,向下拖动公式,即可得到所需结果
![95e8a1042e3bbe5150c18e2c568b63ef.png](https://img-blog.csdnimg.cn/img_convert/95e8a1042e3bbe5150c18e2c568b63ef.png)