任务情景

建筑工程技术1301班有48人,大一第一学期成绩出来以后,班长想把成绩结果统计出来,如果用计算器计算起来太麻烦,他就想利用Excel中的函数计算全班的成绩。

任务目标及效果

将建筑工程技术1301班第一学期成绩表的总分、平均分、名次、等级、单科最高分等利用公式和函数计算出来,效果如图4-33所示。

图4-33 成绩表统计结果

任务分析

1.输入数据。

2.利用公式和函数计算学生的总分、平均分、名次、等级、单科最高分和单科平均分。

知识准备

1.使用公式

公式是一个等式,是一个包含了数据与运算符的数学方程式,它主要包含了各种运算符、常量、函数以及单元格引用等元素。利用公式可以对工作表中的数值进行加、减、乘、除等各种运算,在输入公式时必须以“＝”开始。

(1)使用运算符,如表4-1所示。

表4-1 运算符含义与示例

(续表)

(2)创建公式。

输入公式。双击单元格,将光标放置于单元格中。首先在单元格中输入“＝”号,然后在“＝”号后面输入公式的其他元素,按Enter键即可。修改公式。选择含有公式的单元格,在“编辑栏”中直接修改即可。

特别提示

显示公式

在单元格中输入公式后,将自动显示计算结果。用户可以通过执行“公式”|“公式审核”|“显示公式”命令,来显示单元格中的公式。再次执行“显示公式”命令,将会在单元格中显示计算结果。(注:快捷键“Ctrl＋”)

复制公式主要包括下列几种方法。

①自动填充柄:选择需要复制公式的单元格,移动光标至该单元格右下角的填充柄上,当光标变成十字形状时,拖动鼠标即可。

②利用“剪贴板”:选择需要复制公式的单元格,执行“剪贴板”|“复制”命令即可。

③使用快捷键:选择需要复制公式的单元格,按Ctrl＋C组合键复制公式,选择目标单元格后按Ctrl＋V 组合键粘贴公式即可。

(3)单元格引用。

①相对引用:复制公式时地址跟着发生变化,如C1单元格有公式:＝A1＋B1,当将公式复制到C2单元格时变为:＝A2＋B2,当将公式复制到D1单元格时变为:＝B1＋C1。

②绝对引用:复制公式时地址不会跟着发生变化,如C1单元格有公式:＝＄A＄1＋＄B＄1,当将公式复制到C2单元格时仍为:＝＄A＄1＋＄B＄1,当将公式复制到D1单元格时仍为:＝＄A＄1＋＄B＄1。

③混合引用:复制公式时地址的部分内容跟着发生变化,如C1单元格有公式:＝＄A1＋B＄1,当将公式复制到C2单元格时变为:＝＄A2＋B＄1,当将公式复制到D1单元格时变为:＝＄A1＋C＄1。

2.使用函数

函数是系统预定义的特殊公式,它使用参数按照特定的顺序或结构进行计算。

(1)直接输入。

①单元格输入:双击单元格,首先输入“＝”,然后直接输入函数,按Enter键即可。

②“编辑栏”输入:选择单元格,在“编辑栏”中直接输入“＝”,然后输入函数名与参数,单击“输入”按钮即可。

(2)使用“插入函数”对话框。

对于复杂的函数,用户可以执行“公式”选项标签|“函数库”功能区|“插入函数”命令,在弹出的“插入函数”对话框选择函数,如图4-34所示。

图4-34 “插入函数”对话框

(3)常用函数。

用户在日常工作中经常会使用一些固定函数进行计算数据,从而简化数据的计算,例如,求和函数SUM()、平均数函数AVERAGE()、求最大值函数MAX()、求最小值函数MIN()等。在使用常用函数时,用户只需在“插入函数”对话框中单击“或选择类别”下的三角按钮,选择“常用函数”选项即可。

任务实施

步骤一 建立Excel文档并录入基本数据

新建Excel工作簿,以“建筑工程技术1301班成绩表”命名并保存在桌面,然后在该工作表中输入成绩的相关信息。

(1)启动Excel2010,软件将自动创建名为“Book1.xlsx”的工作簿(默认名称)。

(2)执行“文件”选项标签|“保存”命令,将弹出“另存为”对话框。

(3)录入如图4-35所示的基本数据。

步骤二 利用公式计算总分

在单元格“G3”中输入“＝B3＋C3＋D3＋E3＋F3”,按Enter键即可。(www.daowen.com)

利用复制公式的方法将10个学生的总分全部计算出来。

图4-35 录入数据

步骤三 利用AVERAGE函数计算平均分

将光标定位在单元格中“H3”中,点击插入函数图标,在统计函数选择“AVERAGE”函数,单击确定,如图4-36、图4-37所示。

图4-36 “插入函数”对话框

图4-37 选择“函数参数”对话框

步骤四 利用RANK函数进行排名

将光标定位在单元格中“H3”中,点击插入函数图标,在“插入函数”对话框的“搜索函数”框输入“RANK”,点击“转到”,找到“RANK”函数,如图4-38所示。

图4-38 在“插入函数”对话框搜索“RANK”函数

在“函数参数”对话框的“Number”框中将要查找排名的数据选中,如图4-39所示。在“Ref”框绝对引用用于排名的一组数据,单击“确定”,在单元格“I3”中就会显示排名数字。用复制函数的方法将所有数据的排名计算出来。

图4-39 “RANK”函数参数对话框

步骤五 利用lF函数进行等级划分

按平均分的成绩将学生分为两个等级,平均分大于等于80分的为“优秀”,小于80分的为“合格”。

将光标定位在单元格“J3”中,点击插入函数图标,在“逻辑函数”中找到“IF”函数,点击弹出“IF”函数参数对话框,如图4-40所示“IF”函数参数对话框。

图4-40 “IF”函数参数对话框

步骤六 完成其他“单科平均分”“单科最高分”的计算

同样的方法,利用MAX函数、AVERAGE 函数将“单科平均分”“单科最高分”计算出来。

实战演练

一、选择题

1.在Excel2010中输入分数时,由于日期格式与分数格式一致,所以在输入分数时需要在分子前添加(　)。

A.“－”号　B.“/”号　C.0　D.00

2.数组公式是对一组或多组数值执行多重计算,在输入数组公式后按(　)组合键结束公式的输入。

A.Ctrl＋Shift＋Tab　B.Ctrl＋Alt＋Enter

C.Alt＋Shift＋Enter　D.Ctrl＋Shift＋Enter

3.文本运算符是使用(　)将两个文本连接成一个文本。

A.连接符“&”　B.连接符“∗”　C.连接符“^” D.连接符“＋”

4.在复制公式时,单元格应用将根据引用类型而改,但是在移动公式时,单元格的应用会(　)。

A.保持不变　B.根据引用类型改变

C.根据单元格位置改变　D.根据数据改变

二、填空题

1.公式是一个等式,是一个包含了数据域运算符的数学方程式,在输入公式时必须以(　)开始。

2.公式中的运算符主要包括算数运算符、比较运算符、(　)运算符、(　)运算符与(　)运算符。

3.用户可以通过(　)方法,来改变公式的运算顺序。

4.用户可以通过(　)组合键快速显示或隐藏公式。

三、简答题

1.简述运算符的种类与优先级。

2.单元格引用主要包括哪几个类型? 每种类型具有什么功能?

3.什么是基于多个条件求和?

四、操作题

在工作表中录入如图4-41所示数据,分别利用函数算出“平均分”和“总分”两列中的内容,并且用“Rank”函数根据总分在“排名”列中进行排名。

图4-41