专题四十 EXCEL20.xlsx（练习）-《计算机基础及MS Office应用》

成绩统计表 学生成绩统计表 学号 班级 数学（满分150分） 物理 语文（满分150分） 平均成绩（按百分制计算） 备注 S01 一班 118 81 117 79.2 B 人数 S02 一班 122 78 121 80.0 A 一班 8 S11 二班 120 78 112 77.6 B 二班 12 S03 一班 126 81 134 84.8 A 三班 10 S26 三班 116 80 122 79.6 B 总计 30 S06 二班 118 82 121 80.4 A S13 二班 120 88 116 81.8 A S07 一班 131 95 129 89.4 A S16 二班 112 78 121 77.8 B S09 一班 90 68 117 68.7 B 班级 百分比 平均成绩（按百分制计）>=75的人数 S28 二班 95 70 121 71.3 B 一班 16.67% 5 S10 一班 115 73 105 73.2 B 二班 30.00% 9 S22 三班 133 89 118 85.4 A 三班 23.33% 7 S05 三班 130 91 128 87.7 A S12 二班 118 84 111 78.9 B 注：百分比是相应人数占三个班总人数的百分比 S25 三班 105 73 97 69.2 B S14 二班 135 91 120 87.0 A S08 一班 124 80 119 80.7 A S15 二班 127 76 116 79.3 B S17 二班 125 87 130 85.7 A S21 三班 136 92 132 90.2 A S18 二班 122 86 114 81.1 A S30 三班 129 83 118 82.6 A S20 二班 117 68 109 72.9 B S23 三班 105 72 115 72.9 B S04 一班 97 67 120 70.6 B S24 三班 86 63 118 66.3 B S19 二班 107 75 96 70.1 B S27 三班 121 78 119 79.3 B S29 三班 117 76 136 81.6 A 成绩百分比图 百分比 一班 二班 三班 0.16666666666666666 0.3 0.23333333333333334 图书销售统计表 经销部门 图书类别 季度 销售数量(册) 销售额(元) 销售数量排名 销售额排名 第1分部 <=30 第3分部 <=30 经销部门 图书类别 季度 销售数量(册) 销售额(元) 销售数量排名 销售额排名 第3分部 生物科学 3 124 8680 61 60 第3分部 工业技术 2 321 9630 29 54 第1分部 工业技术 2 435 21750 8 23 第2分部 生物科学 2 256 17920 36 28 第2分部 工业技术 1 167 8350 55 61 第3分部 生物科学 4 157 10990 57 43 第1分部 生物科学 4 187 13090 54 38 第3分部 工业技术 4 213 10650 47 48 第2分部 生物科学 4 196 13720 52 37 第2分部 工业技术 4 219 10950 42 44 第2分部 生物科学 3 234 16380 37 29 第2分部 生物科学 1 206 14420 50 34 第2分部 工业技术 2 211 10550 49 49 第3分部 工业技术 3 189 9450 53 55 第2分部 农业科学 1 221 6630 41 64 第3分部 农业科学 4 432 32960 9 4 第1分部 生物科学 3 323 22610 28 22 第1分部 工业技术 3 324 16200 26 31 第1分部 农业科学 4 342 10260 22 50 第1分部 工业技术 4 287 14350 34 35 第2分部 工业技术 3 218 10900 44 46 第3分部 工业技术 1 301 15050 33 32 第3分部 农业科学 1 306 29180 32 9 第3分部 生物科学 2 345 24150 20 15 第2分部 农业科学 2 312 9360 31 56 第3分部 生物科学 1 212 14840 48 33 第1分部 生物科学 1 345 24150 20 15 第1分部 农业科学 3 365 10950 17 44 第1分部 生物科学 2 412 28840 14 10 第2分部 农业科学 4 421 12630 12 40 第1分部 工业技术 1 569 28450 4 11 第1分部 农业科学 2 654 19620 2 26 第1分部 农业科学 1 765 22950 1 20 第4分部 生物科学 3 378 26460 16 14 第3分部 农业科学 3 219 12990 42 39 第4分部 生物科学 1 324 22680 26 21 第4分部 生物科学 2 329 23030 25 19 第4分部 生物科学 4 398 27860 15 12 第1分部 交通科学 1 436 35648 7 3 第4分部 交通科学 3 232 11298 39 42 第1分部 交通科学 3 231 23217 40 18 第4分部 交通科学 1 321 18976 29 27 第2分部 交通科学 1 123 7685 62 63 第2分部 交通科学 2 342 23718 22 17 第4分部 工业技术 1 234 14321 37 36 第2分部 交通科学 4 341 26785 24 13 第4分部 农业科学 4 421 32534 12 5 第3分部 交通科学 1 166 8765 56 59 第4分部 工业技术 3 129 9637 60 53 第3分部 交通科学 3 216 10238 46 51 第4分部 农业科学 2 217 20453 45 25 第3分部 交通科学 4 432 20563 9 24 第1分部 交通科学 4 365 29879 17 7 第4分部 交通科学 2 112 8798 63 58 第4分部 交通科学 4 261 11675 35 41 第1分部 交通科学 2 654 45321 2 1 第4分部 工业技术 2 432 31256 9 6 第2分部 农业科学 3 543 16290 5 30 第4分部 工业技术 4 112 8178 63 62 第4分部 农业科学 1 156 10786 58 47 第3分部 交通科学 2 199 9675 51 52 第4分部 农业科学 3 356 29342 19 8 第2分部 交通科学 3 542 41234 6 2 第3分部 农业科学 2 148 9078 59 57 $$Sheet1 学生成绩统计表 学号 班级 数学（满分150分） 物理 语文（满分150分） 平均成绩（按百分制计算） 备注 S01 一班 118 81 117 人数 S02 一班 122 78 121 一班 S11 二班 120 78 112 二班 S03 一班 126 81 134 三班 S26 三班 116 80 122 总计 S06 二班 118 82 121 S13 二班 120 88 116 S07 一班 131 95 129 S16 二班 112 78 121 S09 一班 90 68 117 班级 百分比 平均成绩（按百分制计）>=75的人数 S28 二班 95 70 121 一班 S10 一班 115 73 105 二班 S22 三班 133 89 118 三班 S05 三班 130 91 128 S12 二班 118 84 111 注：百分比是相应人数占三个班总人数的百分比 S25 三班 105 73 97 S14 二班 135 91 120 S08 一班 124 80 119 S15 二班 127 76 116 S17 二班 125 87 130 S21 三班 136 92 132 S18 二班 122 86 114 S30 三班 129 83 118 S20 二班 117 68 109 S23 三班 105 72 115 S04 一班 97 67 120 S24 三班 86 63 118 S19 二班 107 75 96 S27 三班 121 78 119 S29 三班 117 76 136 Sheet2 经销部门 图书类别 季度 销售数量(册) 销售额(元) 销售数量排名 销售额排名 第3分部 生物科学 3 124 8680 61 60 第3分部 工业技术 2 321 9630 29 54 第1分部 工业技术 2 435 21750 8 23 第2分部 生物科学 2 256 17920 36 28 第2分部 工业技术 1 167 8350 55 61 第3分部 生物科学 4 157 10990 57 43 第1分部 生物科学 4 187 13090 54 38 第3分部 工业技术 4 213 10650 47 48 第2分部 生物科学 4 196 13720 52 37 第2分部 工业技术 4 219 10950 42 44 第2分部 生物科学 3 234 16380 37 29 第2分部 生物科学 1 206 14420 50 34 第2分部 工业技术 2 211 10550 49 49 第3分部 工业技术 3 189 9450 53 55 第2分部 农业科学 1 221 6630 41 64 第3分部 农业科学 4 432 32960 9 4 第1分部 生物科学 3 323 22610 28 22 第1分部 工业技术 3 324 16200 26 31 第1分部 农业科学 4 342 10260 22 50 第1分部 工业技术 4 287 14350 34 35 第2分部 工业技术 3 218 10900 44 46 第3分部 工业技术 1 301 15050 33 32 第3分部 农业科学 1 306 29180 32 9 第3分部 生物科学 2 345 24150 20 15 第2分部 农业科学 2 312 9360 31 56 第3分部 生物科学 1 212 14840 48 33 第1分部 生物科学 1 345 24150 20 15 第1分部 农业科学 3 365 10950 17 44 第1分部 生物科学 2 412 28840 14 10 第2分部 农业科学 4 421 12630 12 40 第1分部 工业技术 1 569 28450 4 11 第1分部 农业科学 2 654 19620 2 26 第1分部 农业科学 1 765 22950 1 20 第4分部 生物科学 3 378 26460 16 14 第3分部 农业科学 3 219 12990 42 39 第4分部 生物科学 1 324 22680 26 21 第4分部 生物科学 2 329 23030 25 19 第4分部 生物科学 4 398 27860 15 12 第1分部 交通科学 1 436 35648 7 3 第4分部 交通科学 3 232 11298 39 42 第1分部 交通科学 3 231 23217 40 18 第4分部 交通科学 1 321 18976 29 27 第2分部 交通科学 1 123 7685 62 63 第2分部 交通科学 2 342 23718 22 17 第4分部 工业技术 1 234 14321 37 36 第2分部 交通科学 4 341 26785 24 13 第4分部 农业科学 4 421 32534 12 5 第3分部 交通科学 1 166 8765 56 59 第4分部 工业技术 3 129 9637 60 53 第3分部 交通科学 3 216 10238 46 51 第4分部 农业科学 2 217 20453 45 25 第3分部 交通科学 4 432 20563 9 24 第1分部 交通科学 4 365 29879 17 7 第4分部 交通科学 2 112 8798 63 58 第4分部 交通科学 4 261 11675 35 41 第1分部 交通科学 2 654 45321 2 1 第4分部 工业技术 2 432 31256 9 6 第2分部 农业科学 3 543 16290 5 30 第4分部 工业技术 4 112 8178 63 62 第4分部 农业科学 1 156 10786 58 47 第3分部 交通科学 2 199 9675 51 52 第4分部 农业科学 3 356 29342 19 8 第2分部 交通科学 3 542 41234 6 2 第3分部 农业科学 2 148 9078 59 57 $$ EXCEL20.xlsx 打开EXCEL20.xlsx文件，按照题目要求完成下列操作并以该文件名(EXCEL20.xlsx)保存文件。 1. 选择sheet1工作表，将A1:G1单元格合并为一个单元格，文字居中对齐。 【答案】选中工作表Sheet1中的A1:G1单元格，单击【开始】|【对齐方式】中的“合并后居中”按钮，这样同时完成两项操作：选中的单元格合并成一个单元格、单元格中的内容水平居中对齐。 2. 计算每个学生按百分制计的平均成绩置于“平均成绩(按百分制计算)”列(数值型，保留1位小数点)。 【答案】 步骤1：在F3单元格中输入“=AVERAGE(C3/150*100,D3,E3/150*100)”，按回车键，双击F3单元格智能填充柄完成其他行填充。 步骤2：选中F3:F32单元格区域，单击“开始”|“数字”分组中的格式下拉按钮，在列表中选中“数字”，单击一次“减少小数位数”按钮，将小数位调为1位，单击“确定”按钮。 3. 利用IF函数计算“备注”列，如果学生平均成绩大于或等于80，填入“A”，否则填入“B”。 【答案】在G3单元格中输入“=IF(F3>=80,"A","B")”，按回车键，双击G3单元格智能填充柄完成其他行填充。 4. 利用COUNTIF函数分别计算一班、二班、三班的人数置于J4:J6单元格区域。 【答案】在J4单元格中输入“=COUNTIF($B$3:$B$32,I4)”，按回车键，拖动J4单元格智能填充柄到J6单元格，完成其他行填充。 5. 利用SUM函数计算三个班的总人数置于J7单元格内。 【答案】选中“J4:J7”单元格区域，单击“开始”|“编辑”分组中的“求和”按钮。 6. 利用COUNTIFS函数分别计算每班平均成绩按百分制计算75分及以上学生的人数分别置于K13:K15单元格区域。 【答案】在K13单元格中输入“=COUNTIFS($F$3:$F$32,">=75",$B$3:$B$32,I13)”，按回车键，双击K13单元格智能填充柄完成其他行填充。 7. 分别计算每班按百分制计算平均成绩75分及以上的人数占三个班总人数的百分比(百分比型，保留2位小数点)置于J13:J15单元格区域内。 【答案】 步骤1：在J13单元格中输入“=K13/$J$7”，按回车键，双击J13单元格智能填充柄完成其他行填充。 步骤2：选中J13: J15单元格区域，单击“开始”|“数字”分组中的格式下拉按钮，在列表中选中“百分比”，单击“增加小数位数”和“减少小数位数”按钮，将小数位调为2位，单击“确定”按钮。 8. 利用条件格式将F3:F32单元格设置“水绿色、个性色5、淡色40%”渐变填充数据条。 【答案】 步骤1：选中F3:F32单元格区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在列表中选中“数据条”中的“其他规则”命令，打开“新建格式规则”对话框。 步骤2：在填充中选中“渐变填充”，在颜色中选中“水绿色、个性色5、淡色40%”，单击“确定”按钮。 9. 选取Sheet1工作表I12:J15数据区域的内容建立“三维饼图”，更改饼图颜色为“彩色调色板4”，图表标题为“成绩百分比图”，标题位于图表上方，图例位置靠上，饼图外显示数据标签;将图表插入到当前工作表的H20:L34单元格区域内。 【答案】 步骤1：选中Sheet1工作表I12:J15数据区域，单击“插入”|“图表”分组中的“插入饼图或圆环图”下拉按钮，在列表中选中“三维饼图”。 步骤2：选中图表，单击“图表工具”|“图表设计”|“图表样式”分组中的“更改颜色”下拉按钮，在列表中选中“彩色调色板4”。 步骤3：单击图表标题将图表标题改为“成绩百分比图”，单击“图表工具”|“图表设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中选中“图表标题”中的“图表上方”。 步骤4：选中图表，单击“图表工具”|“图表设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中选中“图例”中的“顶部”。 步骤5：选中图表，单击“图表工具”|“图表设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中选中“数据标签”级联菜单中的“数据标签外”。 步骤6：拖放图表到“H20:L34”单元格区域内。 10. 将Sheet1工作表命名为“成绩统计表”; 将Sheet2工作表命名为“图书销售统计表”。 【答案】 步骤1：双击Sheet1工作表标签修改名称为“成绩统计表”。 步骤2：双击Sheet2工作表标签修改名称为“图书销售统计表”。 11. 选择“图书销售统计表”工作表，对工作表内数据清单的内容进行高级筛选(在数据清单前插入四行条件区域设在A1:G3单元格区域)，请在对应字段列内输入条件，条件是:经销部门为“第1分部”或“第3分部”且销售数量排名为前30(请用<=30)。 【答案】 步骤1：在“图书销售统计表”工作表中选中前4行，在选中区域上单击鼠标右键，然后单击“插入”命令。 步骤2：复制第5行内容到第1行，在A2单元格中输入“第1分部”、在A3单元格输入“第3分部”，在F2中输入“<=30”、在F3中输入“<=30”。 步骤3：单击“数据”|“排序和筛选”分组中的“高级”按钮，打开“高级筛选”对话框，在列表区域中选中“图书销售统计表!$A$5:$G$69”，在条件区域中选中“图书销售统计表!$A$1:$G$3”，单击“确定”按钮。 按<Ctrl+S>组合键保存EXCEL20.xlsx工作簿。 原创精品资源学科网独家享有版权，侵权必究！ 学科网（北京）股份有限公司 $$