average
详解：AVERAGE()、AVERAGEIF()、AVERAGEIFS()
一、AVERAGE() 函数
用途
求行求列求区域的平均值
语法
AVERAGE(number1, [number2], ...)
参数说明
- number1：必需。要计算平均值的第一个数字、单元格引用或范围
- number2, ...：可选。最多可包含255个参数
- 自动忽略：文本、逻辑值、空单元格
- 包含0值：0值会参与计算
示例数据表1：学生成绩表
| A | B | C | D | E |
|---|---|---|---|---|
| 学号 | 姓名 | 语文 | 数学 | 英语 |
| S001 | 张三 | 85 | 92 | 78 |
| S002 | 李四 | 78 | 95 | 88 |
| S003 | 王五 | 92 | 88 | 76 |
| S004 | 赵六 | 45 | 65 | 58 |
示例题目
-
计算张三的平均分
=AVERAGE(C2:E2)结果：(85+92+78)/3 = 85 -
计算所有学生的语文平均分
=AVERAGE(C2:C5)结果：(85+78+92+45)/4 = 75 -
计算全班所有科目平均分
=AVERAGE(C2:E5)结果：所有12个成绩的平均值
二、AVERAGEIF() 函数
用途
按单个条件求列求行的平均值
它的工作方式是：在“条件区域”中找符合“条件”的单元格，然后返回对应位置的“求值区域”的值进行平均。
但求值区域必须是单列或单行，不能是多列区域，否则会出错或返回意想不到的结果。
语法
AVERAGEIF(range, criteria, [average_range])
参数说明
- range：必需。条件判断的区域
- criteria：必需。筛选条件
- 数字：
90 - 文本：
"北京"（必须用引号） - 表达式：
">=60" - 通配符：
"张*"
- 数字：
- average_range：可选。要计算平均值的实际区域
- 如果省略，则对range参数指定的区域求平均
示例数据表2：员工工资表
| A | B | C | D |
|---|---|---|---|
| 部门 | 姓名 | 工资 | 奖金 |
| 销售部 | 张三 | 8000 | 2000 |
| 技术部 | 李四 | 12000 | 3000 |
| 销售部 | 王五 | 9000 | 2500 |
| 人事部 | 赵六 | 7000 | 1500 |
| 销售部 | 孙七 | 8500 | 1800 |
示例题目
-
计算销售部的平均工资
=AVERAGEIF(A2:A6, "销售部", C2:C6)结果：(8000+9000+8500)/3 = 8500 -
计算工资大于8000的平均奖金
=AVERAGEIF(C2:C6, ">8000", D2:D6)结果：(3000+2500)/2 = 2750 -
计算所有部门的平均工资（使用通配符）
=AVERAGEIF(A2:A6, "*部", C2:C6)结果：所有部门的平均工资 -
计算销售部所有数值的平均（省略第三参数）
=AVERAGEIF(A2:A6, "销售部")⚠️ 注意：这会计算A列中"销售部"对应的所有数值列的平均
示例1：双条件平均值
A列：部门 B列：职级 C列：工资
求"技术部""高级"员工的平均工资：
=AVERAGEIFS(C:C, A:A,"技术部", B:B,"高级")
示例2：多条件组合
求"销售部"在"北京"且工龄>5年的平均业绩：
=AVERAGEIFS(业绩列, 部门列,"销售部", 地区列,"北京", 工龄列,">5")
示例3：日期范围条件
A列：日期 B列：温度
求2024年夏季(6-8月)平均温度：
=AVERAGEIFS(B:B, A:A,">=2024-06-01", A:A,"<=2024-08-31")
示例4：技术部P7级平均绩效：
=AVERAGEIFS(绩效分数列, 部门列,"技术部", 职级列,"P7")
示例5:工龄3-5年且绩效>85的平均奖金：
=AVERAGEIFS(奖金列, 工龄列,">=3", 工龄列,"<=5", 绩效分数列,">85")
示例6：排除试用期员工的平均绩效：
=AVERAGEIFS(绩效分数列, 状态列,"<>试用期")
单行求平均值
月份： Jan Feb Mar Apr May Jun
销售额：100 80 120 90 150 70
目标： >90 >90 >90 >90 >90 >90
三、AVERAGEIFS() 函数
用途
按多个条件，对单个列或单行的数值区域求平均值
语法
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
参数说明
- average_range：必需。要计算平均值的实际区域
- criteria_range1：必需。第一个条件区域
- criteria1：必需。第一个条件
- 后续参数：可选。最多可添加127个条件区域/条件对
- 参数顺序不同：注意与COUNTIFS的参数顺序区别！
示例数据表3：销售业绩表
| A | B | C | D | E |
|---|---|---|---|---|
| 销售员 | 区域 | 产品 | 销量 | 金额 |
| 张三 | 北京 | 手机 | 50 | 150000 |
| 李四 | 上海 | 电脑 | 30 | 210000 |
| 王五 | 北京 | 手机 | 40 | 120000 |
| 赵六 | 广州 | 平板 | 20 | 60000 |
| 张三 | 上海 | 手机 | 35 | 105000 |
| 李四 | 北京 | 电脑 | 25 | 175000 |
示例题目
-
计算北京地区手机的平均销量
=AVERAGEIFS(D2:D7, B2:B7, "北京", C2:C7, "手机")结果：(50+40)/2 = 45 -
计算张三在上海的平均销售金额
=AVERAGEIFS(E2:E7, A2:A7, "张三", B2:B7, "上海")结果：105000 -
计算销量大于30且金额大于100000的平均金额
=AVERAGEIFS(E2:E7, D2:D7, ">30", E2:E7, ">100000")结果：(150000+210000+105000)/3 = 155000 -
三个条件：北京地区、张三、手机产品
=AVERAGEIFS(E2:E7, B2:B7, "北京", A2:A7, "张三", C2:C7, "手机")结果：无符合条件的记录 → 返回 #DIV/0! 错误
四、对比总结
| 特性 | AVERAGE | AVERAGEIF | AVERAGEIFS |
|---|---|---|---|
| 用途 | 求行求列求区域平均值 | 求单列单行平均值 | 求单列单行平均值 |
| 条件数量 | 无条件 | 单条件 | 多条件 |
| 参数顺序 | 数值区域 | 条件区域,条件,[求值区域] | 求值区域,条件区域1,条件1,... |
| 通配符 | 不支持 | 支持 | 支持 |
| 忽略内容 | 文本、逻辑值 | 不满足条件的单元格 | 不满足任一条件的单元格 |
| 错误处理 | 忽略错误值 | 条件区域含错误值返回错误 | 任一条件区域含错误值返回错误 |
| 求值区域位置 | 直接参数 | 第三个参数 | 第一个参数 |
五、特殊用法和注意事项
1. 忽略0值的平均值
=AVERAGEIF(C2:C10, "<>0") // 计算非0值的平均
2. 排除特定文本的平均
=AVERAGEIF(A2:A10, "<>N/A", B2:B10) // 排除"N/A"文本
3. 基于日期的条件平均
=AVERAGEIFS(D2:D100, C2:C100, ">=2024-1-1", C2:C100, "<=2024-1-31")
4. 多列平均的替代方案
AVERAGEIF不能直接对多列求平均，解决方案：
=AVERAGE(FILTER(D2:H100, C2:C100="1班"))
六、综合练习
练习数据表
| A | B | C | D |
|---|---|---|---|
| 班级 | 姓名 | 性别 | 总分 |
| 1班 | 张三 | 男 | 450 |
| 1班 | 李四 | 女 | 480 |
| 2班 | 王五 | 男 | 420 |
| 1班 | 赵六 | 男 | 460 |
| 2班 | 孙七 | 女 | 490 |
| 3班 | 周八 | 男 | 430 |
| 1班 | 吴九 | 女 | 470 |
| 3班 | 郑十 | 女 | 440 |
练习题
- 计算所有学生的平均总分
- 计算1班的平均总分
- 计算男生的平均总分
- 计算1班女生的平均总分
- 计算总分大于450的学生的平均总分
- 计算2班和3班的平均总分（使用两个条件）
- 计算1班男生且总分大于460的平均分
参考答案
1. =AVERAGE(D2:D9) → 455
2. =AVERAGEIF(A2:A9, "1班", D2:D9) → (450+480+460+470)/4 = 465
3. =AVERAGEIF(C2:C9, "男", D2:D9) → (450+420+460+430)/4 = 440
4. =AVERAGEIFS(D2:D9, A2:A9, "1班", C2:C9, "女") → (480+470)/2 = 475
5. =AVERAGEIF(D2:D9, ">450") → (480+460+490+470)/4 = 475
6. =AVERAGEIFS(D2:D9, A2:A9, "2班") + 或单独计算两个班
更准确：=AVERAGE(FILTER(D2:D9, (A2:A9="2班")+(A2:A9="3班")))
7. =AVERAGEIFS(D2:D9, A2:A9, "1班", C2:C9, "男", D2:D9, ">460")
→ 无符合条件的 → #DIV/0!
七、常见错误和解决方法
| 错误 | 原因 | 解决方法 |
|---|---|---|
| #DIV/0! | 没有符合条件的单元格 | 用IFERROR包装：=IFERROR(AVERAGEIF(...), "无数据") |
| #VALUE! | 条件区域与求值区域大小不一致 | 确保区域大小相同 |
| 结果不正确 | 文本数字未被识别 | 确保数值格式正确 |
| 忽略0值 | 0值参与计算 | 使用=AVERAGEIF(range, "<>0") |
八、实战技巧
技巧1：动态条件平均
=AVERAGEIFS(销售额, 日期, ">="&开始日期, 日期, "<="&结束日期)
技巧2：排除空值和错误值
=AVERAGEIFS(数据区域, 数据区域, "<>", 数据区域, "<>#N/A")
技巧3：多条件或关系（OR逻辑）
=AVERAGE(FILTER(数据区域, (条件1)+(条件2)))
九、示例:学生成绩表
表1：学生成绩统计表（20条记录）
| 学号 | 姓名 | 班级 | 语文 | 数学 | 英语 | 物理 | 化学 | 是否寄宿 | 是否班干部 |
|---|---|---|---|---|---|---|---|---|---|
| S001 | 张三 | 1班 | 85 | 92 | 78 | 88 | 90 | 是 | 是 |
| S002 | 李四 | 1班 | 78 | 95 | 88 | 75 | 82 | 是 | 否 |
| S003 | 王五 | 2班 | 92 | 88 | 76 | 90 | 85 | 否 | 是 |
| S004 | 赵六 | 2班 | 45 | 65 | 58 | 85 | 72 | 是 | 否 |
| S005 | 孙七 | 3班 | 88 | 96 | 92 | 92 | 95 | 否 | 是 |
| S006 | 周八 | 3班 | 76 | 85 | 79 | 78 | 80 | 是 | 否 |
| S007 | 吴九 | 1班 | 95 | 39 | 94 | 68 | 70 | 否 | 是 |
| S008 | 郑十 | 2班 | 82 | 78 | 85 | 88 | 84 | 是 | 否 |
| S009 | 钱一 | 3班 | 89 | 92 | 87 | 82 | 88 | 否 | 是 |
| S010 | 王一 | 1班 | 73 | 68 | 52 | 76 | 65 | 是 | 否 |
| S011 | 刘二 | 2班 | 84 | 79 | 86 | 89 | 87 | 否 | 否 |
| S012 | 陈三 | 3班 | 39 | 42 | 45 | 92 | 88 | 是 | 是 |
| S013 | 杨四 | 1班 | 91 | 87 | 93 | 77 | 81 | 否 | 否 |
| S014 | 黄五 | 2班 | 67 | 71 | 69 | 84 | 79 | 是 | 是 |
| S015 | 赵七 | 3班 | 98 | 96 | 99 | 85 | 92 | 否 | 是 |
| S016 | 周九 | 1班 | 53 | 56 | 51 | 91 | 86 | 是 | 否 |
| S017 | 吴十 | 2班 | 82 | 88 | 85 | 79 | 83 | 否 | 是 |
| S018 | 郑一 | 3班 | 89 | 92 | 87 | 88 | 90 | 是 | 否 |
| S019 | 王二 | 1班 | 74 | 69 | 72 | 83 | 77 | 否 | 是 |
| S020 | 孙三 | 2班 | 47 | 51 | 49 | 96 | 91 | 是 | 否 |
示例1：计算学生平均分
在表1的K列（个人平均分）：
=AVERAGE(D2:H2)
结果：
张三：(85+92+78+88+90)/5 = 86.6
李四：(78+95+88+75+82)/5 = 83.6
注意：忽略文本、逻辑值、空单元格
示例2：计算班级平均成绩
计算1班语文平均分：
=AVERAGEIF(C2:C20, "1班", D2:D20)
先用AVERAGE计算所有学生语文平均：
=AVERAGE(D2:D20)
结果：计算D列所有数值的平均值
示例3：计算多列平均值
计算所有理科平均分（物理+化学）：
=AVERAGE(G2:H20) // G列物理，H列化学
或者分列计算：
=(AVERAGE(G2:G20) + AVERAGE(H2:H20))/2
示例5：计算班级平均分
计算1班语文平均分：
=AVERAGEIF(C2:C20, "1班", D2:D20)
解释：
range: C2:C20（班级列）
criteria: "1班"
average_range: D2:D20（语文成绩）
结果：1班所有学生语文成绩的平均值
示例6：计算及格学生平均分
计算数学及格（≥60）学生的平均分：
=AVERAGEIF(E2:E20, ">=60", E2:E20)
结果：数学成绩≥60的学生的数学平均分
示例7：计算寄宿生平均成绩
计算寄宿生语文平均分：
=AVERAGEIF(I2:I20, "是", D2:D20)
解释：
range: I2:I20（是否寄宿）
criteria: "是"
average_range: D2:D20（语文成绩）
示例8：使用通配符计算
计算姓"张"学生的数学平均分：
=AVERAGEIF(B2:B20, "张*", E2:E20)
结果：姓名以"张"开头的学生的数学平均分
示例12：多条件班级平均分
计算1班且寄宿的学生语文平均分：
=AVERAGEIFS(D2:D20, C2:C20, "1班", I2:I20, "是")
解释：
average_range: D2:D20（语文成绩）
criteria_range1: C2:C20（班级），criteria1: "1班"
criteria_range2: I2:I20（是否寄宿），criteria2: "是"
示例13：计算优秀学生平均分
计算数学≥90且英语≥90的学生平均分：
=AVERAGEIFS(D2:D20, E2:E20, ">=90", F2:F20, ">=90")
结果：数学和英语都≥90的学生的语文平均分
示例14：计算寄宿班干部平均成绩
计算寄宿且是班干部的学生数学平均分：
=AVERAGEIFS(E2:E20, I2:I20, "是", J2:J20, "是")
- 计算表1中所有学生的数学平均分
- 计算表1中1班寄宿生的语文平均分
十、示例：员工工资表
完整数据表示例
| A | B | C | D | E |
|---|---|---|---|---|
| 部门 | 姓名 | 职级 | 入职年份 | 月薪 |
| 技术部 | 张三 | P7 | 2018 | 25000 |
| 销售部 | 李四 | P6 | 2019 | 18000 |
| 技术部 | 王五 | P8 | 2017 | 30000 |
| 人事部 | 赵六 | P5 | 2020 | 15000 |
| 技术部 | 孙七 | P7 | 2019 | 23000 |
| 销售部 | 周八 | P6 | 2021 | 17000 |
表2：员工销售业绩表（15条记录）
| 员工ID | 姓名 | 部门 | 1月业绩 | 2月业绩 | 3月业绩 | 季度总额 | 季度平均 | 职级 | 入职年限 |
|---|---|---|---|---|---|---|---|---|---|
| SA001 | 张明 | 销售部 | 158000 | 142000 | 165000 | 465000 | 155000 | A | 3 |
| SA002 | 李华 | 销售部 | 145000 | 138000 | 152000 | 435000 | 145000 | B | 5 |
| SA003 | 王芳 | 技术部 | 128000 | 135000 | 142000 | 405000 | 135000 | A | 2 |
| SA004 | 赵强 | 财务部 | 165000 | 158000 | 172000 | 495000 | 165000 | C | 8 |
| SA005 | 孙丽 | 市场部 | 142000 | 148000 | 155000 | 445000 | 148333 | B | 1 |
| SA006 | 周伟 | 销售部 | 135000 | 128000 | 140000 | 403000 | 134333 | B | 4 |
| SA007 | 吴刚 | 技术部 | 152000 | 162000 | 158000 | 472000 | 157333 | A | 6 |
| SA008 | 郑洁 | 人事部 | 138000 | 145000 | 148000 | 431000 | 143667 | B | 3 |
| SA009 | 钱勇 | 财务部 | 148000 | 152000 | 146000 | 446000 | 148667 | C | 7 |
| SA010 | 王静 | 市场部 | 125000 | 132000 | 128000 | 385000 | 128333 | C | 2 |
| SA011 | 刘洋 | 销售部 | 162000 | 155000 | 168000 | 485000 | 161667 | A | 4 |
| SA012 | 陈晨 | 技术部 | 155000 | 148000 | 162000 | 465000 | 155000 | B | 5 |
| SA013 | 杨光 | 销售部 | 132000 | 138000 | 135000 | 405000 | 135000 | B | 3 |
| SA014 | 黄蓉 | 人事部 | 142000 | 135000 | 148000 | 425000 | 141667 | C | 4 |
| SA015 | 赵云 | 财务部 | 158000 | 152000 | 165000 | 475000 | 158333 | A | 6 |
表3：产品库存与销售表（12条记录）
| 产品ID | 产品名称 | 类别 | 库存量 | 安全库存 | 上月销量 | 本月销量 | 平均售价 | 成本价 | 是否促销 |
|---|---|---|---|---|---|---|---|---|---|
| P001 | iPhone 15 | 手机 | 150 | 50 | 120 | 150 | 5999 | 4500 | 是 |
| P002 | 华为P60 | 手机 | 45 | 30 | 85 | 120 | 4999 | 3800 | 否 |
| P003 | 小米13 | 手机 | 120 | 40 | 110 | 180 | 3999 | 3000 | 是 |
| P004 | MacBook Pro | 电脑 | 28 | 20 | 25 | 85 | 14999 | 12000 | 是 |
| P005 | 冰箱 | 家电 | 35 | 25 | 30 | 35 | 8999 | 7000 | 否 |
| P006 | 牛奶 | 食品 | 15 | 30 | 45 | 25 | 12 | 8 | 否 |
| P007 | 面包 | 食品 | 8 | 20 | 35 | 30 | 8 | 5 | 是 |
| P008 | 羽绒服 | 服装 | 60 | 40 | 15 | 60 | 599 | 400 | 否 |
| P009 | 防晒霜 | 美妆 | 25 | 15 | 8 | 25 | 89 | 60 | 是 |
| P010 | 月饼 | 食品 | 120 | 50 | 10 | 120 | 198 | 120 | 否 |
| P011 | 矿泉水 | 饮料 | 200 | 100 | 180 | 200 | 2 | 1 | 否 |
| P012 | 巧克力 | 食品 | 18 | 25 | 22 | 12 | 58 | 40 | 是 |
各种平均值计算
1. 全体员工平均月薪：=AVERAGE(E:E) // 结果：21333
2. 技术部平均月薪：
=AVERAGEIF(A:A,"技术部",E:E) // 结果：26000
3. P7级平均月薪：
=AVERAGEIF(C:C,"P7",E:E) // 结果：24000
4. 技术部P7级平均月薪：
=AVERAGEIFS(E:E, A:A,"技术部", C:C,"P7") // 结果：24000
5. 2019年及以后入职的平均月薪：
=AVERAGEIFS(E:E, D:D,">=2019") // 结果：18250
6. 技术部且职级>=P7的平均月薪：
=AVERAGEIFS(E:E, A:A,"技术部", C:C,">=P7") // 结果：26000
7. 排除人事部的平均月薪：
=AVERAGEIF(A:A,"<>人事部",E:E) // 结果：22600
基础练习
- 计算表2中销售部员工的平均业绩
- 计算表3中促销产品的平均售价
中级练习
- 计算表2中职级A且入职>3年的员工平均业绩
- 计算表3中食品类别且库存>20的产品平均销量
示例4：计算员工季度平均业绩
在表2的H列（季度平均）：
=AVERAGE(D2:F2)
结果：
张明：(158000+142000+165000)/3 = 155000
注意：已预计算好，演示公式
示例9：计算特定部门平均业绩
在表2计算销售部平均业绩：
=AVERAGEIF(C2:C16, "销售部", G2:G16)
结果：销售部所有员工季度平均业绩的平均值
示例10：计算高业绩员工平均
计算业绩>150000的员工平均：
=AVERAGEIF(G2:G16, ">150000", G2:G16)
结果：季度平均>150000的员工的平均值
示例11：计算非促销产品平均售价
在表3计算非促销产品平均售价：
=AVERAGEIF(J2:J13, "否", H2:H13) // J列是否促销，H列平均售价
结果：非促销产品的平均售价
示例15：销售部高绩效员工平均
在表2计算销售部且职级A的员工平均业绩：
=AVERAGEIFS(G2:G16, C2:C16, "销售部", H2:H16, "A")
解释：
average_range: G2:G16（季度平均）
criteria_range1: C2:C16（部门），criteria1: "销售部"
criteria_range2: H2:H16（职级），criteria2: "A"
示例16：多条件产品分析
在表3计算手机类别且促销中的产品平均售价：
=AVERAGEIFS(H2:H13, C2:C13, "手机", J2:J13, "是")
结果：类别为"手机"且正在促销的产品平均售价
示例17：复杂条件平均值
计算库存充足（>安全库存）且销量>100的产品平均成本：
=AVERAGEIFS(I2:I13, D2:D13, ">"&E2:E13, F2:F13, ">100")
注意：这里E2:E13需要逐行比较，实际应该为：
=AVERAGEIFS(I2:I13, D2:D13, ">="&E2:E13, F2:F13, ">100")
但Excel不支持数组条件，正确做法：
使用SUMPRODUCT或辅助列
示例18：排除特定条件计算
计算非食品类且非促销产品的平均库存：
=AVERAGEIFS(D2:D13, C2:C13, "<>食品", J2:J13, "否")
结果：类别不是食品且非促销的产品平均库存量
示例19：多条件日期范围
假设表2有月份列，计算Q1销售部业绩：
=AVERAGEIFS(业绩列, 部门列, "销售部", 日期列, ">=2024-01-01", 日期列, "<=2024-03-31")
十一、总结
AVERAGE家族是Excel的"平均计算器"——AVERAGE是基础款，AVERAGEIF是智能款，AVERAGEIFS是专业款，满足你从简单到复杂的所有平均值计算需求！
记忆口诀
AVERAGE求平均，数值相加除个数
AVERAGEIF加条件，筛选之后再平均
AVERAGEIFS多条件，复杂分析真方便
参数顺序要记清，区域大小要一致