计算机应用基础课件第6章Excel综合应用课件

第6章Excel综合应用《计算机基础》课程组2013年9月

6.1学生成绩处理

6.1.1知识点解析排位函数RANK.EQRANK.EQ函数的功能是返回一个数字在数据区域中的排位。其排位大小与数据区域中的其他值相关。如果多个值具有相同的排位，则返回该组数值的最高排位。要计算B3单元格在B2:B10单元格区域中排在第几位，其对应的公式为“=RANKD.EQ(B3,B3:B10,0)”。6.1学生成绩处理

6.1.1知识点解析排位函数RANK.EQRANK.EQ函数的语法格式为RANK.EQ(Number,Ref,Order)，三个参数的含义分别是：Number：需要找到排位位置的数字或单元格。Ref：需要参与排位的数据区域，Ref中的非数值型值将被忽略。Order：如果order为0或省略，是按降序排位，否则为升序排位。函数RANK.EQ对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如B3和B6单元格的数值一样，它们对应的排位都为4，B9单元格的排位则为6（没有排位为5的数值）。6.1学生成绩处理

6.1.1知识点解析绝对引用在计算出3单元格的排名后，通过公式复制计算其他人的排名时会发现，B6单元格的数值与B3单元格相等，而其排名却是2。查看C6单元格的公式，其公式为“=RANK.EQ(B6,B6:B13,0)”。公式显示的是“B6单元格在B6:B13单元格区域中排在第几位”，而实际上是要计算“B6单元格在B2:B10单元格区域中排在第几位”。之所以出现错误，是因为在进行公式复制时，公式中的单元格引用是相对引用。6.1学生成绩处理

6.1.1知识点解析绝对引用如果希望公式中引用的单元格或单元格区域不随公式位置的变化而变化，可以使用绝对引用。绝对引用是指当把公式复制到其它单元格时，公式中引用的单元格或单元格区域中行和列的引用不会改变。绝对引用的单元格名称中其行和列之前均会加上$以示区分，如$A$4表示绝对引用A4单元格。因此，在C3单元格的编辑框中，选择“B3:B13”，按下F4键，实现相对引用转换到绝对引用，“B3:B13”变化为“$B$3:$B$10”，然后进行公式复制时，就能得到正确的结果了。6.1学生成绩处理

6.1.1知识点解析多条件统计函数COUNTIFS如何统计游戏积分15000～20000（含15000，不含20000）的人数？可以转换为：统计B3:B10单元格区域中数值在15000～20000（含15000，不含20000）的单元格个数可以采用COUNTIFS函数来实现。6.1学生成绩处理

6.1.1知识点解析多条件统计函数COUNTIFS如何统计游戏积分15000～20000（含15000，不含20000）的人数？可以转换为：统计B3:B10单元格区域中数值在15000～20000（含15000，不含20000）的单元格个数可以采用COUNTIFS函数来实现。6.1学生成绩处理

6.1.1知识点解析多条件统计函数COUNTIFSCOUNTIFS函数语法格式为COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)其参数的含义分别是：criteria_range1：为第一个需要计算其中满足某个条件的单元格数目的单元格区域（简称条件区域）。criteria1：为第一个区域中将被计算在内的条件（简称条件），其形式可以为数字、表达式或文本。criteria_range2为第二个条件区域。

criteria2为第二个条件，依次类推。6.1学生成绩处理

6.1.1知识点解析多条件统计函数COUNTIFS对应的公式为“=COUNTIFS(B3:B10,">=15000",B3:B10,"<20000")”6.1学生成绩处理

6.1.1知识点解析复杂条件格式与混合引用将排行榜（在C列）前3名的整行数据（从A列C列）都用“灰色背景”标识出来6.1学生成绩处理

6.1.1知识点解析复杂条件格式与混合引用将排行榜（在C列）前3名的整行数据（从A列C列）都用“灰色背景”标识出来需要对A3:C10单元格区域运用复杂条件格式6.1学生成绩处理

6.1.1知识点解析复杂条件格式与混合引用第3行数据（A3:C3）的所有单元格对应的条件格式可以描述为：如果第3行中的第C列单元格中的数据<=3（C3<=3），那么对该单元格应用“灰色背景”格式。第4行数据（A4:C4）的所有单元格对应的条件格式可以描述为：如果第4行中的第C列单元格中的数据<=3（C4<=3），那么对该单元格应用“灰色背景”格式。从以上分析可以知道，对第3到第10行（A3:C10）的所有单元格对应的条件格式的公式为“=$C3<=3”。在该条件表达式中，地址“$C3”为混合引用单元格地址。如在用条件“=$C3<=3”对A3:C10的单元格进行条件格式判断时，A3、B3、C3（第3行）单元格的格式判断条件为“C3单元格的内容是否<=3”，在A8、B8、C8单元格（第8行）单元格的格式判断条件为“C8单元格的内容是否<=3”。6.2学生成绩处理

6.2.1任务实现（1）填写考勤记录、平时成绩、期中成绩及期末成绩。（2）根据考勤成绩计算办法（考勤以100分计，迟到1次扣5分，请假1次扣10分，旷课1次扣20分），计算考勤成绩。（3）根据总评成绩计算办法（总评成绩=考勤成绩×10%+平时成绩×20%+期中成绩×20%+期末成绩×50%，成绩四舍五入，旷考计为旷考），计算总评成绩。（4）将总评成绩转换为总评等级：总评成绩>=90分为A；>=80分为B；>=70分为C；>=60分为D；60分以下为E，旷考为F。（5）统计每个分数段的人数。6.2学生成绩处理

6.2.1任务实现请参照书上步骤，自行完成上、下页边距为3cm，左、右页边距为2cm。纸张采用B5纸横向打印，打印的表格在页面中采用水平居中。总评成绩分段人数统计信息不打印。如果成绩单需要分多页打印，则每页需要打印标题（第1行到第4行）信息。6.3学生成绩处理

6.3.1总结与提高1.IF函数嵌套如果O5单元格的值为64公式“=IF(O5<60,”E“,IF(O5<70,”D“,IF(O5<80,”C“,IF(O5<90,”B“,”A“))))”的执行结果为多少？公式“=IF(O5<60,"E",IF(60<=O5<70,"D",IF(70<=O5<80,"C",IF(80<=O5<90,"B","A"))))”的执行结果为多少？AD6.3学生成绩处理

6.3.3总结与提高1.IF函数嵌套为防止出现条件交叉包含，在我们进行多条件嵌套时：（1）条件判断要么从最大到最小，要么从最小到最大，不要出现大小交叉情况。（2）如果条件判断为从大到小，通常使用的比较运算符为大于>或大于等于>=。（2）如果条件判断为从小到大，通常使用的比较运算符为小于<或小于等于<=。6.3学生成绩处理

6.3.1总结与提高2.用IF函数防止异常结果出现如果某个同学迟到了10次，旷课了3次，请问他的考勤成绩是多少？与现实相符吗？怎么解决判断“考勤成绩”是不是小于0，若是，“考勤成绩”计为0分，否则计为计算出来的成绩IF(100-COUNTIF(C5:J5,"迟到")*5-COUNTIF(C5:J5,"请假")*10-COUNTIF(C5:J5,"旷课")*20>=0,100-COUNTIF(C5:J5,"迟到")*5-COUNTIF(C5:J5,"请假")*10-COUNTIF(C5:J5,"旷课")*20,0)6.3学生成绩处理

6.3.1总结与提高3.排位函数RANK除了可以使用RANK.EQ函数来排名外，也可以使用RANK函数。RANK函数返回一个数字在数字列表中的排位。RANK函数是在Excel2007版本及之前的版本中提供的函数。在Excel2010版本中，提供了RANK.EQ等新函数来替代它，而Excel2010版本中仍提供RANK函数是为了保持与Excel早期版本的兼容性。6.3学生成绩处理

6.3.1总结与提高4.COUNTIFS函数统计“全勤，并且总评成绩为80~89的学生数”？统计“考勤成绩=100，并且总评成绩为80~89的学生数”（1）O5:O32单元格区域中单元格的值>=80。（2）O5:O32单元格区域中单元格的值<90。（3）K5:K32单元格区域中单元格的值=100因此，其对应的表达式为“=COUNTIFS(O5:O32,">=80",