在Excel 2007中,增加了AVERAGEIF、AVERAGEIFS、SUMIFS、COUNTIFS和IFERROR五个函数。都可以根据条件在一定范围内自行计算。特别是多条件函数AVERAGEIFS、COUNTIFS和SUMIFS给我们的工作带来了很大的方便。比如,在计算年级表中各班级、各科目的平均分、通过率、优秀率时,不需要先按班级排序再计算平均分、两率。具体实现方法如下:
1.首先,在“入场券编号”下面的空白单元格中输入班级栏,然后在“姓名”栏下方分别输入平均分、通过率和优秀率。
2.在“中国人”一栏下“平均分”对应的单元格中输入=AVERAGEIFS(C2:C132,$G$2:$G$132,'=1 ')。(1)
AVERAGEIFS是一个使用多个条件来计算指定范围内单元格的平均值的函数。其语法格式为:AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…),average_range是要计算平均值的单元格区域,包括数字或名称,包含数字的数组或引用;Criteria_range1,criteria_range2,…指指定条件的单元格区域。准则1,准则2,……指的是具体条件是什么。
公式中的Average_range指的是C2:c132,即“语文”一栏中的所有成绩。Criteria_range1引用G2:G132,即class的列数据,G前后加的$用于拖拽填充其他数学和英语科目($是绝对应用,拖拽公式时引用应用的单元格不变)。Criteria1 1的意思是'=1 '即条件是该类是一类。
3.Enter=COUNTIFS(C2:C132,'=90 '$G$2:$G$132,'=1')/COUNTIF($G$2:$G$132,'=(2)
COUNTIFS计算某个区域中满足多个条件的像元数。其语法格式为:COUNTIFS(Range1,criteria1,range2,criteria2…),Range1,range2,…指统计中哪些区域受条件约束。准则1,准则2,……指的是具体条件是什么。公式中,Range1指的是C2:C132,即“语文”一栏中的分数;Criteria1表示'=90 '即条件是得分大于等于90;2指G2:G132,即“类”的列数据,加$方便拖拽填充其他学科;Criteria2 2的意思是'=1 '即条件是该类为一类。
COUNTIF函数是计算指定区域中满足给定条件的单元格数量的函数。其语法格式为:COUNTIF(range,criteria)。Range是要计算的一个或多个单元格,包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。条件是指进行统计所依据的条件,其形式可以是数字、表达式、单元格引用或文本。公式范围是指G2:G132,即“类”列中的所有单元格;Criteria的意思是'=1 '意思是条件是班级是一班的同学。
4.按照计算通过率的方法计算优秀率,即90变120(150分中120分为优秀)。
5.选择通过率和优良率两行,点击“开始”选项卡下“数字”控制组下的“%”(百分比样式按钮),转换为按百分比显示。您也可以单击按钮“增加小数位数”和“减少小数位数”来增加或减少小数位数。(3)
此外,我们还可以使用COUNTIFS来统计每个分数段的人数。比如统计语文成绩在120到130之间的人数,输入:=COUNTIFS(C2:C132,'=120 'C2:C132,' 130 ')。