- 大学计算机实训教程
- 宝乐尔 白迎霞 斯琴
- 3540字
- 2021-04-03 00:29:31
实验3-2 公式与函数的使用
实验目的
①掌握公式的输入、编辑方法。
②掌握公式复制时，相对地址、绝对地址的区别。
③掌握函数的输入方法及常用函数的使用。
试验内容
新建一工作簿文件，在Sheet1中输入如表3.2.1所示的数据。
表3.2.1 精品家电城销售表
一、公式的使用
1.计算各种家电的“销售合计”及“季度总销售额”
操作步骤：
①单击E3单元格。
②输入英文半角“=”号。
③在“=”号后输入公式“B3+C3+D3”按【Enter】键或单击编辑栏中的“√”按钮，确认刚才输入的公式。
或直接单击“开始”→“编辑”→“自动求和”按钮，如图3.2.1所示，按【Enter】键确认。
图3.2.1 单击“自动求和”按钮的结果
④拖动E3单元格右下角的填充柄至E7。
⑤单击E8单元格，单击“开始”→“编辑”→“自动求和”按钮，确认公式为“=SUM（E3：E7）”，按【Enter】键确认。
2.计算各种家电的“销售百分比”（销售百分比=销售合计/季度总销售额*100）
操作步骤：
①单击F3单元格。
②输入公式“=E3/E8*100”。
③在编辑栏的公式中单击单元格地址E8，再按【F4】键将相对地址转换成绝对地址，公式变为“=E3/E 8*100”。
④拖动F3单元格右下角的填充柄至F7。
思考：如果跳过第③步，直接到第④步的智能填充，结果会如何？为什么？
二、函数的输入
计算各种家电的“平均月度销售额”。
操作步骤：
①单击G3单元格。
②单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框，如图3.2.2所示。
图3.2.2 “插入函数”对话框
③在“或选择类别”中选择“常用函数”，在“选择函数”框中选择“AVERAGE”，单击“确定”按钮。
④弹出“函数参数”对话框，如图3.2.3所示。
图3.2.3 “函数参数”对话框
⑤单击“Number1”文本框右侧的折叠按钮，“函数参数”对话框变换成如图3.2.4所示的折叠对话框。
图3.2.4 折叠对话框
⑥选择求平均值的数据区域B3：D3，选择完后，再单击右侧的折叠按钮返回。
⑦单击“确定”按钮；G3单元格中的公式为“=AVERAGE（B3：D3）”。
⑧拖动G3单元格右下角的填充柄至G7。
Sheet1中“精品家电城销售表”的计算结果如图3.2.5所示。
图3.2.5 “精品家电城销售表”的计算结果
三、常用函数的使用
在Sheet2中输入如图3.2.6所示的数据。
图3.2.6 Sheet2中输入的数据
1.计算各学生的“总分”
操作步骤：
①单击G4单元格。
②单击“开始”→“编辑”→“自动求和”按钮，确认公式为“=SUM（C4：F4）”，按【Enter】键确认。
③拖动G4单元格右下角的填充柄至G15。
2.计算各学生的“平均分”
操作步骤：
①单击H4单元格。
②单击“开始”→“编辑”→“自动求和”下拉按钮，在下拉列表中选择“平均值”，如图3.2.7所示。
③选择计算的单元格区域：C4：F4，确认公式为“=AVERAGE（C4：F4）”，按【Enter】键。
④拖拽H4单元格右下角的填充柄至H15。
3.根据每个学生的“总分”，求出他们的“名次”
操作步骤：
①单击I4单元格。
图3.2.7 “自动求和”下拉列表
②单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框。
③在“搜索函数”文本框中输入“RANK”，按【Enter】键，在“选择函数”列出的函数中选择“RANK”。
④单击“确定”按钮，弹出“函数参数”对话框。
⑤在Number 文本框中输入当前的求名次的单元格G4。
⑥单击Ref 右侧的折叠按钮，在弹出的折叠对话框中选择求名次的数据区域G4：G15。选择完后，再单击右侧的折叠按钮返回。
⑦单击单元格地址G4，再按【F4】键将相对地址转换成绝对地址“$G$4”，同样将“G15”转换成“$G$15”。
⑧“Order”省略，按降序排列进行排位，即G4：G15中数值最大的排名为“1”，RANK函数的参数设置结果如图3.2.8所示。
图3.2.8 RANK函数的参数设置结果
⑨单击“确定”按钮，I4单元格中的公式为“=RANK（G4，$G$4：G$15）”。
⑩拖动I4单元格右下角的填充柄至I15。
思考：如果跳过第⑦步，直接到第⑧步，结果会如何？为什么？
4.根据学生的“平均分”，将其转换成“优、良、中、及格、不及格”五级制“等级”
操作步骤：
①单击J4单元格。
②输入英文半角“=”号，在“=”号后输入下列公式：
=IF（H4>=90，"优"，IF（H4>=80，"良"，IF（H4>=70，"中"，IF（H4>=60，"及格"，"不及格"））））
③拖动J4单元格右下角的填充柄至J15。
Sheet2中“2016—2017年度第二学期计算机科学汉班成绩单”的计算结果如图3.2.9所示。
5.统计“2016—2017年度第二学期计算机科学汉班成绩单”学生的人数，放在C16单元格中
操作步骤：
①单击C16单元格。
②单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框。
③在“或选择类别”中选择“常用函数”，在“选择函数”框中选择COUNT，单击“确定”按钮。
④弹出“函数参数”对话框。
图3.2.9 “2016—2017年度第二学期计算机科学汉班成绩单”的计算结果
⑤单击Value1文本框右侧的折叠按钮，在弹出的折叠对话框中选择统计的数据区域C4：C15。选择完后，再单击右侧的折叠按钮返回。
⑥单击“确定”按钮，则C16单元格中的公式为“=COUNT（C4：C15）”。
注意：
如果用“学号”来统计人数，则要用COUNTA函数，则B16单元格中的公式为“=COUNTA（B4：B15）”。
6.求出“数学”成绩的最高分，放在D16单元格中
操作步骤：
①单击D16单元格。
②单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框。
③在“或选择类别”中选择“常用函数”，在“选择函数”框中选择MAX，单击“确定”按钮。
④弹出“函数参数”对话框。
⑤单击Number1文本框右侧的折叠按钮，在弹出“折叠”对话框中选择统计的数据区域D4：D15。选择完后，再单击右侧的折叠按钮返回。
⑥单击“确定”按钮，则D16单元格中的公式为“=MAX（D4：D15）”。
四、条件函数的使用
在Sheet3工作表中输入如图3.2.10所示的数据。
图3.2.10 Sheet3中输入的数据
1.求出副教授的人数，放在C12单元格中
操作步骤：
①单击C12单元格。
②单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框。
③在“搜索函数”文本框中输入COUNTIF，按【Enter】键，在“选择函数”列出的函数中选择COUNTIF。
④单击“确定”按钮，弹出“函数参数”对话框。
⑤单击Range 文本框，直接输入或用鼠标选择用于条件判断的单元格区域B3：B11。
⑥在“Criteria”文本框中输入“副教授”，如图3.2.11所示。
图3.2.11 COUNTIF“函数参数”对话框
注意：
副教授两边的英文双引号可以不输入，此时在Range 文本框中单击，会发现副教授两边的双引号已自动加上；或单击“确定”按钮退出，Excel 也会自动加上双引号。
⑦单击“确定”按钮，则C12单元格中的公式为：“=COUNTIF（B3：B11，"副教授"）”
2.求出副教授的工资总额，放在C13单元格中
操作步骤：
①单击C13单元格。
②单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框。
③在“搜索函数”文本框中输入SUMIF，按【Enter】键，在“选择函数”列出的函数中选择SUMIF。
④单击“确定”按钮，弹出“函数参数”对话框。
⑤单击Range 文本框，直接输入或用鼠标选择用于条件判断的单元格区域B3：B11。
⑥在Criteria 文本框中输入“"副教授"”。
⑦单击Sum_range 文本框，选择求和的单元格区域F3：F11，如图3.2.12所示。
⑧单击“确定”按钮，则C13单元格中的公式为“=SUMIF（B3：B11，"副教授"，F3：F11）”。
3.求出“补贴”大于100的总和，放在C14单元格中
操作步骤：
①单击C14单元格。
②单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框。
③在“搜索函数”文本框中输入SUMIF，按【Enter】键，在“选择函数”列出的函数中选择SUMIF。
图3.2.12 SUMIF“函数参数”对话框
④单击“确定”按钮，弹出“函数参数”对话框。
⑤单击Range 文本框，直接输入或用鼠标选择用于条件判断的单元格区域E3：E11。
⑥在Criteria 文本框中输入“>100”，如图3.2.13所示。
图3.2.13 SUMIF“函数参数”对话框二
4.求出副教授人均工资，放在C15单元格中
⑦单击“确定”按钮，则C14单元格中的公式为“=SUMIF（E3：E11，">100"）”。
思考：同样是SUMIF函数，与前一实验相比，这里省略了Sum_range，两者有什么差异？
操作步骤：
①单击C15单元格。
②单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框。
③在“搜索函数”文本框中输入AVERAGEIF，按【Enter】键，在“选择函数”列出的函数中选择AVERAGEIF。
④单击“确定”按钮，弹出“函数参数”对话框。
⑤单击Range 文本框，直接输入或用鼠标选择用于条件判断的单元格区域B3：B11。
⑥在Criteria 文本框中输入“″副教授″”。
⑦单击Average_range 文本框，选择求平均值的单元格区域F3：F11，如图3.2.14所示。
⑧单击“确定”按钮，则C15单元格中的公式为“=AVERAGEIF（B3：B11，"副教授"，F3：F11）”。
将以上内容保存在D盘Esy2.xlsx 中。
图3.2.14 AVERAGEIF“函数参数”对话框
上机练习
打开Excel 工作簿文件TESTA.XLSX，请依次完成下列操作：
①计算TESTA.XLSX的工作表“小组数据”中A组和B组数据之和，放置在单元格B12中。
②计算TESTA.XLSX的工作表“工资表”中员工的应发工资、实发工资，其中应发工资=基本工资+职务工资+奖金，实发工资=应发工资-扣除。
③计算TESTA.XLSX的工作表“成绩统计表”中计（4）班各门课程的总分，填入第28行的相应单元格中。
④计算TESTA.XLSX中工作表“成绩统计表（2）”中各门课程的平均分和最高分，填入第28行和29行的相应单元格中。
⑤计算TESTA.XLSX中的工作表“学生考试成绩”中每位学生的总分和均分；在H1单元格中输入文本“等第”，用IF函数计算每位学生的成绩等第，当总分大于310时等第为“优”，否则不输入等第。
⑥计算TESTA.XLSX的工作表“职称统计”中本科以上的比例，计算公式为“（本科生人数+研究生人数）/员工人数”。