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

EXCEL18.xlsx 打开EXCEL18.xlsx文件，按照题目要求完成下列操作并以该文件名(EXCEL18.xlsx)保存文件。 1. 选取Sheet1工作表，将A1:E1单元格合并为一个单元格，文字居中对齐。 【答案】选中工作表Sheet1中的A1:E1单元格，单击【开始】|【对齐方式】中的“合并后居中”按钮，这样同时完成两项操作：选中的单元格合并成一个单元格、单元格中的内容水平居中对齐。 2. 利用VLOOKUP函数，依据本工作薄中“课程学分对照表”工作表中信息填写sheet1工作表中C3:C110单元格区域“学分”列的内容。 【答案】 步骤1：在C3单元格中输入“=VLOOKUP(B3,课程学分对照表!$A$1:$B$5,2,0)”公式，按回车键，计算出第一行的学分。 步骤2：双击C3单元格的右下角的黑色方块智能填充柄即可计算出其他学生的学分。 3. 利用IF函数给出“备注”列(E3:E110单元格区域)的内容，备注内容请依据G4:H6单元格区域信息。 【答案】 步骤1：在E3单元格中输入“=IF(D3>=80,"优良",IF(D3>=60,"中等"," "))”公式，按回车键，计算出第一行的备注信息。 步骤2：双击E3单元格的右下角的黑色方块智能填充柄即可计算出其他学生的备注信息。 4. 利用COUNTIF函数计算每门课程(以课程号标识)选课人数置于H14:H17单元格区域。 【答案】 步骤1：在H14单元格中输入“=COUNTIF($B$3:$B$110,G14)”公式，按回车键，计算出课程号为C011课程的选课人数。 步骤2：双击H14单元格的右下角的黑色方块智能填充柄即可计算出其他课程的选课人数。 5. 利用AVERAGEIF函数计算各门课程(以课程号标识)平均成绩置I14:117单元格区域(数值型，保留小数点后1位)。 【答案】 步骤1：在I14单元格中输入“=AVERAGEIF($B$3:$B$110,G14,$D$3:$D$110)”公式，按回车键，计算出课程号为C011课程的平均成绩，双击I14单元格的右下角的黑色方块智能填充柄即可计算出其他课程的平均成绩。 步骤2：选中I14:I17单元格区域，在“开始”|“数字”分组中单击“对话框启动器”，打开“设置单元格格式”对话框，在分类中选中“数值”，在小数位数中输入“1”，单击“确定”按钮。 6. 利用条件格式修饰“成绩”列(D3:D110单元格区域)，将成绩小于60分的单元格设置颜色为“浅红色填充”；将G13:I17单元格区域设置为“橄榄色，表样式中等深浅4”的表格样式。 【答案】 步骤1：选中D3:D110单元格区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在列表中单击“突出显示单元格规则”级联菜单中的“小于”，打开“小于”对话框在值中输入“60”、在设置为中选中“浅红色填充”，单击“确定”按钮。 步骤2：选中G13:I17单元格区域，单击“开始”|“样式”分组中的“套用表格格式”下拉按钮，在列表中选中“橄榄色，表样式中等深浅4”，单击“确定”按钮。 7. .选取Sheet1工作表内“统计表”下的“课程号”列(G13:G17)、“选课人数”列(H13:H17)、“平均成绩”列(I13:I17)数据区域的内容建立“簇状柱形图”。 【答案】选中G13: I17单元格区域，单击“插入”|“插入柱形图或条形图”下拉按钮，在列表中选中“簇状柱形图”。 8. 图表标题为“选课人数与成绩统计图”，利用图表样式“样式11”修饰图表，将图插入到当前工作表的G19:K33单元格区域。 【答案】 步骤1：将图表标题改为“选课人数与成绩统计图”。 步骤2：在“图表工具”|“图表设计”|“图表样式”分组中选中“样式11”。 步骤3：拖放图表到G19:K33单元格区域内。 9. 将Sheet1工作表命名为“成绩统计表”； 将Sheet2工作表命名为“图书销售统计表”。 【答案】 步骤1：双击工作表名称“Sheet1”，将其修改为“成绩统计表”。 步骤2：双击工作表名称“Sheet2”，将其修改为“图书销售统计表” 10. 选择“图书销售统计表”工作表，对工作表“图书销售统计表”内数据清单的内容建立数据透视表，按行标签为“图书类别”，列标签为“经销部门”，数值为“销售额(元)”求和布局，并置于现工作表的I5:N11单元格区域。 【答案】 步骤1：选中“图书销售统计表”工作表，选中A1:G65数据区域，单击“插入”|“表格”分组中的“数据透视表”按钮，打开“来自表格或区域的数据透视表”对话框。 步骤2：选中“现有工作表”按钮，在位置中输入“图书销售统计表!$I$5:$N$11”，单击“确定”按钮。 步骤3：在打开的“数据透视表字段”窗口中，拖动“图书类别”到“行”、拖动“经销部门”到“列”、拖动“销售额（元）”到“值”。 按<Ctrl+S>组合键保存EXCEL18.xlsx工作簿。 原创精品资源学科网独家享有版权，侵权必究！ 学科网（北京）股份有限公司 $$学生课程成绩表 学号 课程号 学分 成绩 备注 c011 S2001 断密错错语写错误密密的语制写的密翻评评 备注填写要求 S2001 C020 成绩 备注 s2001 C026 -80 优良 s2002 c011 )-60且(-7中等 S2002 C020 (60 s2002 C026 S2003 C011 s2003 C020 S2003 C026 s2003 C015 统计表 S2004 C020 课程号 选课人数 平均成绩 s2004 C026 C011 011 S2005 C015 S2005 C020 C020 s2005 C026 C026 S2006 C015 S2006 C020 S2006 C026 s2007 C011 s2007 C020 s2007 C026 S2008 C011 S2008 C020 s2008 C026 s2009 C015 S2000 C020 s2009 C02f s2010 C011 S2010 C020 s2010 C026 s2011 C015 s2011 C020 s2011 C02f s2012 c011 S2012 C020 s2012 C026 S2013 C011 s2013 C020 s2013 C02f s2014 C011 S2014 C020 S2014 C026 s2015 C015 s2015 C020 C011 S2016 语语密错的评密语密评密错语错语评语误评误语密语语。 52016 C020 S2016 C026 s2017 C011 s2017 C020 s2017 C026 S2018 C015 s2018 C020 S2018 C026 s2019 C011 52019 C020 S2019 C026 S2020 C011 s2021 c15 S2021 C020 S2021 C026 s2022 C015 S2022 C020 S2022 C026 S2023 C015 S2023 C026 S2024 C011 s2024 C020 s2024 C026 S2025 C015 S2025 C020 S2025 C026 S2026 c011 s2026 C02f s2027 C11 S2027 C026 S2028 C015 S2028 C026 S2020 C015 s2029 C020 202 C026 S2030 c015 S2030 C020 S2030 C026 s2031 C015 52031 C020 s2031 C026 S2032 C011 S2032 C026 s2033 C020 S2033 C026 s2034 C020 语密错密错错。 85 S2034 C020 S2034 C026 S2035 C011 s2035 C020 S2035 c026 S2036 C011 S2036 C026 S2037 C011 s2037 C020 S2037 C026 s2038 C011 S2038 C026 S2039 c020 S2039 C026 S2040 C020 S2040 C020 S2040 C026 课程号 学分 C011 C015 C020 C026 经销部门 图书类别 可 季度 销售数量(销售额(元) 销售数量排名 销售额排名 第3分部 生物科学 124 8680 61 3 不语记语5的耳语到到提语语用到福5有语到551福5到5回语到语到到1福语 第3分部 工业技术 321 9630 2 29 第1分部 工业技术 2 435 21750 8 第2分部 生物科学 256 17920 2 36 第2分部 8350 55 工业技术 # 第3分部 {生物科学 10990 27 187 第1分部。 {生物科学 13090 54 第3分部 213 一写72 工业技术 10650 第2分部 196 生物科学 13720 210 第2分部 工业技术 10950 第2分部 生物科学 16380 第2分部 {生物科学 206 14420 第2分部 10550 {工业技术 17) 第3分部 工业技术 3 9450 221 3=大得用)分分分二 第2分部 {农业科学 6630 第3分部 农业科学 432 32960 第1分部 323 生物科学 3 22610 第1分部 {工业技术 324 .5 16200 第1分部 ,农业科学 3 10260 第1分部 工业技术 14350 第2分部 工业技术 218 3 10900 第3分部 工业技术 301 15050 第3分部 农业科学 306 29180 第3分部 生物科学 345 24150 第2分部 一将 农业科学 9360 第3分部 生物科学 14840 第1分部 生物科学 24150 第1分部 农业科学 10950 第1分部 生物科学 一将将7 28840 14 第2分部 农业科学 12630 第1分部 2 {工业技术 28450 A 654 第1分部 {农业科学 19620 第1分部 农业科学 765 22950 _ 第4分部 生物科学 26460 16 第3分部 农业科学 12990 42 324 生物科学 第4分部 22680 一习3# 329 第4分部 了生物科学 23030 第4分部 生物科学 400 27860 第1分部 交通科学 一行 35648 第4分部 11298 交通科学 3 39 第1分部 交通科学 23217 40 第4分部 交通科学 18976 6 第2分部 交通科学 7685 第2分部 交通科学 23718 31) 第4分部 工业技术 234 14321 341 26785 第2分部 交通科学 24 第4分部 农业科学 421 32534 71 6B路5识5记 5 第3分部 { 交通科学 166 8765 第4分部 129 9637 工业技术 60 第3分部 交通科学 216 10238 3 46 第4分部 217 20453 农业科学 5 2 432 第3分部 { 交通科学 20563 第1分部 交通科学 365 29879 第4分部 交通科学 112 8798 第4分部 261 交通科学 11675 35 7 第1分部 交通科学 45321 (53得5 2/ 第4分部 工业技术 31256 第2分部 { 农业科学 16290 31 1 第4分部 工业技术 8178 63 第4分部 农业科学 ##7 10786 第3分部 交通科学 9675 2 第4分部 农业科学 29342 13 3 第2分部 交通科学 542 41234 3 6 第3分部 农业科学 148 9078 2 59成绩统计表 学生课程成绩表 学号 课程号 学分 成绩 备注 S2001 C011 4 83 优良 备注填写要求 S2001 C020 2 93 优良 成绩 备注 S2001 C026 4 91 优良 >=80 优良 S2002 C011 4 76 中等 >=60且<=79 中等 S2002 C020 2 85 优良 <60 S2002 C026 4 85 优良 S2003 C011 4 78 中等 S2003 C020 2 87 优良 S2003 C026 4 82 优良 S2003 C015 3 85 优良 统计表 S2004 C020 2 76 中等 课程号 选课人数 平均成绩 S2004 C026 4 85 优良 C011 22 80.0 S2005 C011 4 92 优良 C015 14 82.9 S2005 C020 2 65 中等 C020 34 81.8 S2005 C026 4 84 优良 C026 38 81.1 S2006 C015 3 72 中等 S2006 C020 2 91 优良 S2006 C026 4 87 优良 S2007 C011 4 85 优良 S2007 C020 2 95 优良 S2007 C026 4 89 优良 S2008 C011 4 82 优良 S2008 C020 2 78 中等 S2008 C026 4 74 中等 S2009 C015 3 81 优良 S2009 C020 2 86 优良 S2009 C026 4 89 优良 S2010 C011 4 77 中等 S2010 C020 2 76 中等 S2010 C026 4 95 优良 S2011 C015 3 54 S2011 C020 2 87 优良 S2011 C026 4 74 中等 S2012 C011 4 78 中等 S2012 C020 2 74 中等 S2012 C026 4 89 优良 S2013 C011 4 67 中等 S2013 C020 2 65 中等 S2013 C026 4 67 中等 S2014 C011 4 83 优良 S2014 C020 2 87 优良 S2014 C026 4 87 优良 S2015 C015 3 91 优良 S2015 C020 2 87 优良 S2016 C011 4 56 S2016 C020 2 85 优良 S2016 C026 4 84 优良 S2017 C011 4 72 中等 S2017 C020 2 75 中等 S2017 C026 4 86 优良 S2018 C015 3 95 优良 S2018 C020 2 91 优良 S2018 C026 4 91 优良 S2019 C011 4 83 优良 S2019 C020 2 92 优良 S2019 C026 4 79 中等 S2020 C011 4 83 优良 S2021 C015 3 83 优良 S2021 C020 2 91 优良 S2021 C026 4 85 优良 S2022 C015 3 92 优良 S2022 C020 2 67 中等 S2022 C026 4 89 优良 S2023 C015 3 91 优良 S2023 C026 4 89 优良 S2024 C011 4 83 优良 S2024 C020 2 89 优良 S2024 C026 4 85 优良 S2025 C015 3 90 优良 S2025 C020 2 89 优良 S2025 C026 4 56 S2026 C011 4 79 中等 S2026 C026 4 55 S2027 C011 4 76 中等 S2027 C026 4 76 中等 S2028 C015 3 74 中等 S2028 C026 4 82 优良 S2029 C015 3 85 优良 S2029 C020 2 71 中等 S2029 C026 4 85 优良 S2030 C015 3 75 中等 S2030 C020 2 76 中等 S2030 C026 4 51 S2031 C015 3 93 优良 S2031 C020 2 87 优良 S2031 C026 4 93 优良 S2032 C011 4 95 优良 S2032 C026 4 76 中等 S2033 C020 2 74 中等 S2033 C026 4 81 优良 S2034 C020 2 85 优良 S2034 C020 2 93 优良 S2034 C026 4 95 优良 S2035 C011 4 86 优良 S2035 C020 2 68 中等 S2035 C026 4 74 中等 S2036 C011 4 74 中等 S2036 C026 4 45 S2037 C011 4 82 优良 S2037 C020 2 95 优良 S2037 C026 4 74 中等 S2038 C011 4 89 优良 S2038 C026 4 91 优良 S2039 C020 2 85 优良 S2039 C026 4 85 优良 S2040 C020 2 51 S2040 C020 2 86 优良 S2040 C026 4 95 优良 选课人数与成绩统计图 选课人数 C011 C015 C020 C026 22 14 34 38 平均成绩 C011 C015 C020 C026 79.95454545454545 82.92857142857143 81.82352941176471 81.05263157894737 课程学分对照表 课程号 学分 C011 4 C015 3 C020 2 C026 4 图书销售统计表 经销部门 图书类别 季度 销售数量(册) 销售额(元) 销售数量排名 销售额排名 第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 行标签 第1分部 第2分部 第3分部 第4分部 总计 第3分部 生物科学 4 157 10990 57 43 工业技术 80750 40750 44780 63392 229672 第1分部 生物科学 4 187 13090 54 38 交通科学 134065 99422 49241 50747 333475 第3分部 工业技术 4 213 10650 47 48 农业科学 63780 44910 84208 93115 286013 第2分部 生物科学 4 196 13720 52 37 生物科学 88690 62440 58660 100030 309820 第2分部 工业技术 4 219 10950 42 44 总计 367285 247522 236889 307284 1158980 第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 $$