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

员工销售情况表 某公司员工销售情况表 员工号 产品名称 产品单价（万元） 销售数量（件） 销售额(万元） 产品单品价格表 H01 A产品 0.16 56 9.0 产品名称 产品单价（万元） H01 B产品 0.27 123 33.2 A产品 0.16 H02 B产品 0.27 58 15.7 B产品 0.27 H02 C产品 0.87 109 94.8 C产品 0.87 H03 A产品 0.16 91 14.6 D产品 0.68 H03 B产品 0.27 45 12.2 H03 D产品 0.68 63 42.8 统计表1 H04 C产品 0.87 212 184.4 产品名称 销售数量（件） H05 A产品 0.16 51 8.2 A产品 1180 H05 D产品 0.68 32 21.8 B产品 1328 H06 A产品 0.16 45 7.2 C产品 1118 H06 B产品 0.27 54 14.6 D产品 1181 H06 D产品 0.68 179 121.7 H07 A产品 0.16 39 6.2 H07 C产品 0.87 65 56.6 H08 B产品 0.27 59 15.9 H08 D产品 0.68 156 106.1 H09 A产品 0.16 57 9.1 H09 B产品 0.27 159 42.9 H10 C产品 0.87 61 53.1 H10 B产品 0.27 45 12.2 H10 D产品 0.68 54 36.7 H11 A产品 0.16 79 12.6 H11 C产品 0.87 39 33.9 H12 A产品 0.16 165 26.4 H12 B产品 0.27 59 15.9 H13 B产品 0.27 56 15.1 H13 C产品 0.87 57 49.6 H14 A产品 0.16 59 9.4 H14 D产品 0.68 161 109.5 H15 C产品 0.87 34 29.6 H16 C产品 0.87 67 58.3 H16 D产品 0.68 45 30.6 H17 A产品 0.16 62 9.9 H17 B产品 0.27 89 24.0 H18 B产品 0.27 112 30.2 H18 C产品 0.87 28 24.4 H18 D产品 0.68 52 35.4 H19 A产品 0.16 59 9.4 H19 B产品 0.27 46 12.4 H19 C产品 0.87 123 107.0 H20 B产品 0.27 68 18.4 H20 D产品 0.68 93 63.2 H21 A产品 0.16 78 12.5 H21 B产品 0.27 154 41.6 H21 D产品 0.68 135 91.8 H22 A产品 0.16 98 15.7 H22 C产品 0.87 102 88.7 H23 B产品 0.27 201 54.3 H23 C产品 0.87 48 41.8 H23 D产品 0.68 211 143.5 H24 A产品 0.16 152 24.3 H24 C产品 0.87 91 79.2 H25 A产品 0.16 89 14.2 H25 C产品 0.87 82 71.3 产品销售数量统计图 销售数量（件） A产品 B产品 C产品 D产品 1180 1328 1118 1181 员工销售额统计表 员工号 总销售额（万元） 销售排名 H01 42.2 22 H02 110.5 8 H03 69.6 16 H04 184.4 2 H05 29.9 24 H06 143.5 4 H07 62.8 18 H08 122.0 6 H09 52.1 19 H10 101.9 11 H11 46.6 20 H12 42.3 21 H13 64.7 17 H14 118.9 7 H15 29.6 25 H16 88.9 13 H17 34.0 23 H18 90.0 12 H19 128.9 5 H20 81.6 15 H21 145.9 3 H22 104.4 9 H23 239.5 1 H24 103.5 10 H25 85.6 14 图书销售统计表 经销部门 图书类别 季度 销售数量(册) 销售额(万元) 销售数量排名 销售额排名 第3分部 生物科学 3 124 8680 91 92 第3分部 工业技术 2 321 9630 53 86 第1分部 工业技术 2 435 21750 26 47 第2分部 生物科学 2 256 17920 63 56 第2分部 工业技术 1 167 8350 85 93 第3分部 生物科学 4 157 10990 87 75 第1分部 生物科学 4 187 13090 84 69 第3分部 工业技术 4 213 10650 76 80 第2分部 生物科学 4 196 13720 81 67 第2分部 工业技术 4 219 10950 70 76 求和项:销售额(万元) 列标签 第2分部 生物科学 3 235 16380 64 58 行标签 工业技术 交通科学 农业科学 生物科学 总计 第2分部 生物科学 1 206 14420 79 63 第1分部 80750 134065 83780 88690 387285 第2分部 工业技术 2 211 10550 78 81 第2分部 40750 99422 48910 62440 251522 第3分部 工业技术 3 189 9450 83 87 第3分部 44780 49241 84208 65051 243280 第2分部 农业科学 1 221 6630 68 96 第4分部 64392 50747 93115 100030 308284 第3分部 农业科学 4 434 32960 27 15 第5分部 117625 177532 154804 100761 550722 第1分部 生物科学 3 323 22610 51 46 第6分部 140373 97500 99493 138792 476158 第1分部 工业技术 3 324 16200 49 60 总计 488670 608507 564310 555764 2217251 第1分部 农业科学 4 331 10260 45 82 第1分部 工业技术 4 287 14350 60 65 第2分部 工业技术 3 218 10900 71 77 第3分部 工业技术 1 301 15050 58 61 第3分部 农业科学 1 306 29180 56 25 第3分部 生物科学 2 351 26541 41 34 第2分部 农业科学 2 312 13360 54 68 第3分部 生物科学 1 212 18840 77 55 第1分部 生物科学 1 345 24150 42 38 第1分部 农业科学 3 365 30950 39 21 第1分部 生物科学 2 412 28840 33 26 第2分部 农业科学 4 421 12630 32 72 第1分部 工业技术 1 569 28450 13 29 第1分部 农业科学 2 654 19620 9 51 第1分部 农业科学 1 765 22950 4 43 第4分部 生物科学 3 378 26460 36 35 第3分部 农业科学 3 220 12990 69 70 第4分部 生物科学 1 324 22680 49 45 第4分部 生物科学 2 329 23030 47 41 第4分部 生物科学 4 398 27860 35 31 第1分部 交通科学 1 436 35648 25 14 第4分部 交通科学 3 232 11298 66 74 第1分部 交通科学 3 231 23217 67 40 第4分部 交通科学 1 322 18976 52 53 第2分部 交通科学 1 123 7685 92 95 第2分部 交通科学 2 342 23718 43 39 第4分部 工业技术 1 234 14321 65 66 第2分部 交通科学 4 341 26785 44 32 第4分部 农业科学 4 431 32534 29 16 第3分部 交通科学 1 166 8765 86 91 第4分部 工业技术 3 129 9637 90 85 第3分部 交通科学 3 216 10238 73 83 第4分部 农业科学 2 217 20453 72 50 第3分部 交通科学 4 425 20563 31 49 第1分部 交通科学 4 366 29879 38 22 第4分部 交通科学 2 112 8798 96 90 第4分部 交通科学 4 261 11675 62 73 第1分部 交通科学 2 655 45321 8 8 第4分部 工业技术 2 432 32256 28 17 第2分部 农业科学 3 543 16290 16 59 第4分部 工业技术 4 118 8178 95 94 第4分部 农业科学 1 156 10786 88 79 第3分部 交通科学 2 199 9675 80 84 第4分部 农业科学 3 356 29342 40 24 第2分部 交通科学 3 542 41234 17 11 第3分部 农业科学 2 148 9078 89 89 第5分部 工业技术 1 367 10860 37 78 第5分部 生物科学 1 289 14351 59 64 第5分部 生物科学 2 675 50900 5 4 第5分部 工业技术 2 873 17050 1 57 第5分部 农业科学 1 603 49180 12 5 第5分部 工业技术 3 534 25150 18 37 第5分部 生物科学 3 123 9360 92 88 第5分部 农业科学 2 122 14840 94 62 第5分部 生物科学 4 550 26150 15 36 第6分部 工业技术 1 653 50950 10 3 第6分部 农业科学 1 214 28760 75 27 第6分部 农业科学 2 456 12830 24 71 第6分部 交通科学 1 659 28550 6 28 第6分部 交通科学 2 565 19521 14 52 第6分部 交通科学 3 656 22850 7 44 第6分部 交通科学 4 789 26579 3 33 第6分部 工业技术 2 465 22990 23 42 第6分部 生物科学 1 325 18906 48 54 第6分部 工业技术 3 498 21346 21 48 第6分部 工业技术 4 832 45087 2 9 第6分部 农业科学 3 190 29807 82 23 第5分部 交通科学 1 512 36865 19 13 第5分部 交通科学 2 330 31256 46 19 第5分部 工业技术 4 467 64565 22 2 第5分部 农业科学 3 305 48617 57 7 第5分部 交通科学 3 650 78436 11 1 第5分部 农业科学 4 430 42167 30 10 第5分部 交通科学 4 215 30975 74 20 第6分部 农业科学 4 280 28096 61 30 第6分部 生物科学 2 308 31665 55 18 第6分部 生物科学 3 401 39566 34 12 第6分部 生物科学 4 509 48655 20 6 $$Sheet1 某公司员工销售情况表 员工号 产品名称 产品单价（万元） 销售数量（件） 销售额(万元） 产品单品价格表 H01 A产品 56 产品名称 产品单价（万元） H01 B产品 123 A产品 0.16 H02 B产品 58 B产品 0.27 H02 C产品 109 C产品 0.87 H03 A产品 91 D产品 0.68 H03 B产品 45 H03 D产品 63 统计表1 H04 C产品 212 产品名称 销售数量（件） H05 A产品 51 A产品 H05 D产品 32 B产品 H06 A产品 45 C产品 H06 B产品 54 D产品 H06 D产品 179 H07 A产品 39 H07 C产品 65 H08 B产品 59 H08 D产品 156 H09 A产品 57 H09 B产品 159 H10 C产品 61 H10 B产品 45 H10 D产品 54 H11 A产品 79 H11 C产品 39 H12 A产品 165 H12 B产品 59 H13 B产品 56 H13 C产品 57 H14 A产品 59 H14 D产品 161 H15 C产品 34 H16 C产品 67 H16 D产品 45 H17 A产品 62 H17 B产品 89 H18 B产品 112 H18 C产品 28 H18 D产品 52 H19 A产品 59 H19 B产品 46 H19 C产品 123 H20 B产品 68 H20 D产品 93 H21 A产品 78 H21 B产品 154 H21 D产品 135 H22 A产品 98 H22 C产品 102 H23 B产品 201 H23 C产品 48 H23 D产品 211 H24 A产品 152 H24 C产品 91 H25 A产品 89 H25 C产品 82 员工销售额统计表 员工号 总销售额（万元） 销售排名 H01 H02 H03 H04 H05 H06 H07 H08 H09 H10 H11 H12 H13 H14 H15 H16 H17 H18 H19 H20 H21 H22 H23 H24 H25 图书销售统计表 经销部门 图书类别 季度 销售数量(册) 销售额(万元) 销售数量排名 销售额排名 第3分部 生物科学 3 124 8680 91 92 第3分部 工业技术 2 321 9630 53 86 第1分部 工业技术 2 435 21750 26 47 第2分部 生物科学 2 256 17920 63 56 第2分部 工业技术 1 167 8350 85 93 第3分部 生物科学 4 157 10990 87 75 第1分部 生物科学 4 187 13090 84 69 第3分部 工业技术 4 213 10650 76 80 第2分部 生物科学 4 196 13720 81 67 第2分部 工业技术 4 219 10950 70 76 第2分部 生物科学 3 235 16380 64 58 第2分部 生物科学 1 206 14420 79 63 第2分部 工业技术 2 211 10550 78 81 第3分部 工业技术 3 189 9450 83 87 第2分部 农业科学 1 221 6630 68 96 第3分部 农业科学 4 434 32960 27 15 第1分部 生物科学 3 323 22610 51 46 第1分部 工业技术 3 324 16200 49 60 第1分部 农业科学 4 331 10260 45 82 第1分部 工业技术 4 287 14350 60 65 第2分部 工业技术 3 218 10900 71 77 第3分部 工业技术 1 301 15050 58 61 第3分部 农业科学 1 306 29180 56 25 第3分部 生物科学 2 351 26541 41 34 第2分部 农业科学 2 312 13360 54 68 第3分部 生物科学 1 212 18840 77 55 第1分部 生物科学 1 345 24150 42 38 第1分部 农业科学 3 365 30950 39 21 第1分部 生物科学 2 412 28840 33 26 第2分部 农业科学 4 421 12630 32 72 第1分部 工业技术 1 569 28450 13 29 第1分部 农业科学 2 654 19620 9 51 第1分部 农业科学 1 765 22950 4 43 第4分部 生物科学 3 378 26460 36 35 第3分部 农业科学 3 220 12990 69 70 第4分部 生物科学 1 324 22680 49 45 第4分部 生物科学 2 329 23030 47 41 第4分部 生物科学 4 398 27860 35 31 第1分部 交通科学 1 436 35648 25 14 第4分部 交通科学 3 232 11298 66 74 第1分部 交通科学 3 231 23217 67 40 第4分部 交通科学 1 322 18976 52 53 第2分部 交通科学 1 123 7685 92 95 第2分部 交通科学 2 342 23718 43 39 第4分部 工业技术 1 234 14321 65 66 第2分部 交通科学 4 341 26785 44 32 第4分部 农业科学 4 431 32534 29 16 第3分部 交通科学 1 166 8765 86 91 第4分部 工业技术 3 129 9637 90 85 第3分部 交通科学 3 216 10238 73 83 第4分部 农业科学 2 217 20453 72 50 第3分部 交通科学 4 425 20563 31 49 第1分部 交通科学 4 366 29879 38 22 第4分部 交通科学 2 112 8798 96 90 第4分部 交通科学 4 261 11675 62 73 第1分部 交通科学 2 655 45321 8 8 第4分部 工业技术 2 432 32256 28 17 第2分部 农业科学 3 543 16290 16 59 第4分部 工业技术 4 118 8178 95 94 第4分部 农业科学 1 156 10786 88 79 第3分部 交通科学 2 199 9675 80 84 第4分部 农业科学 3 356 29342 40 24 第2分部 交通科学 3 542 41234 17 11 第3分部 农业科学 2 148 9078 89 89 第5分部 工业技术 1 367 10860 37 78 第5分部 生物科学 1 289 14351 59 64 第5分部 生物科学 2 675 50900 5 4 第5分部 工业技术 2 873 17050 1 57 第5分部 农业科学 1 603 49180 12 5 第5分部 工业技术 3 534 25150 18 37 第5分部 生物科学 3 123 9360 92 88 第5分部 农业科学 2 122 14840 94 62 第5分部 生物科学 4 550 26150 15 36 第6分部 工业技术 1 653 50950 10 3 第6分部 农业科学 1 214 28760 75 27 第6分部 农业科学 2 456 12830 24 71 第6分部 交通科学 1 659 28550 6 28 第6分部 交通科学 2 565 19521 14 52 第6分部 交通科学 3 656 22850 7 44 第6分部 交通科学 4 789 26579 3 33 第6分部 工业技术 2 465 22990 23 42 第6分部 生物科学 1 325 18906 48 54 第6分部 工业技术 3 498 21346 21 48 第6分部 工业技术 4 832 45087 2 9 第6分部 农业科学 3 190 29807 82 23 第5分部 交通科学 1 512 36865 19 13 第5分部 交通科学 2 330 31256 46 19 第5分部 工业技术 4 467 64565 22 2 第5分部 农业科学 3 305 48617 57 7 第5分部 交通科学 3 650 78436 11 1 第5分部 农业科学 4 430 42167 30 10 第5分部 交通科学 4 215 30975 74 20 第6分部 农业科学 4 280 28096 61 30 第6分部 生物科学 2 308 31665 55 18 第6分部 生物科学 3 401 39566 34 12 第6分部 生物科学 4 509 48655 20 6 $$ EXCEL17.xlsx 打开EXCEL17.xlsx文件，按照题目要求完成下列操作并以该文件名(EXCEL17.xlsx)保存文件。 1. 选择Sheet1工作表，将A1:E1单元格合并为一个单元格，文字居中对齐。 【答案】选中工作表sheet1中的A1:E1单元格，单击【开始】|【对齐方式】中的“合并后居中”按钮，这样同时完成两项操作：选中的单元格合并为一个单元格、单元格中的内容水平居中对齐。 2. 利用VLOOKUP函数，依据G3:H7单元格区域“产品单品价格表”中信息填写Sheet1工作表中C3:C57单元格区域“产品单价(万元)”列的内容(数值型，保留小数点后2位)。 【答案】 步骤1：选定C3单元格，单击【公式】|【函数库】分组中的“插入函数”（fx）按钮，在弹出的“插入函数”对话框中，“或选择类别”后的下拉框中选择“全部”，选择函数中选择VLOOKUP，单击“确定”按钮，在弹出的“函数参数”对话框中输入参数，Lookup-value里输入“B3”，Table-array参数里输入“$G$3:$H$7”，col-index-num参数里输入“2”，range-lookup参数输入“0”，单击“确定”按钮。 步骤2：选定单元格C3，鼠标定位到C3单元格右下角触发智能填充功能，双击进行其他单元格的内容填充。 步骤3：选中C3:C57单元格区域，单击右键，选择“设置单元格格式”按钮，在【数字】选项卡中，在分类中选择“数值”，小数位数为2，单击“确定”按钮。 3. 计算员工不同产品的销售额置于E3:E57单元格区域“销售额(万元)”列(数值型，保留小数点后1位)。 【答案】 步骤1：双击E3单元格，输入公式“=C3*D3”，按回车键，选定单元格E3，双击E3单元格右下角的智能填充按钮，即可计算出E4:E57单元格区域的销售额。 步骤2：选中E3:E57单元格区域，单击右键，选择“设置单元格格式”按钮，在【数字】选项卡中，在分类中选择“数值”，小数位数为1，单击“确定”按钮。 4. 计算每种产品总的销售数量置于统计表1的H11:H14单元格区域“销售数量(件)”列(要求利用SUMIF函数)。 【答案】 步骤1：将光标定位于H11单元格，单击【公式】|【插入库】分组中的【插入函数】按钮，打开“插入函数”对话框，在选择函数中选中“SUMIF”函数，单击“确定”函数，打开“函数参数”对话框； 步骤2：第一个参数是要进行计算的单元格，这里我们是要找到所有的A产品，那么就可以选择产品名称区域“$B$3:$B$57”。第二个参数是条件，也就是参数一的条件，这里输入“G11”。第三个参数是要求和的区域，这里是“$D$3:$D$57”，单击“确定”按钮，即可求出A产品销售数量。 步骤3：双击H11单元格的智能填充柄，即可完成其他产品销售数量计算。 5. 选取“员工销售额统计表”工作表，利用SUMIF函数,根据Sheet1工作表的内容计算每个员工各种产品总的销售额置于“总销售额(万元)”列(数值型，保留小数点后1位)。 【答案】 步骤1：将光标定位于工作表“员工销售额统计表”中的B2单元格，单击【公式】|【插入库】分组中的【插入函数】按钮，打开“插入函数”对话框，在选择函数中选中“SUMIF”函数，单击“确定”函数，打开“函数参数”对话框。 步骤2：第一个参数是要进行计算的单元格，这里我们是要找到所有的员工号为H01的员工，那么就可以选择员工号区域“Sheet1!$A$3:$A$57”。第二个参数是条件，也就是参数一的条件，这里输入“A2”。第三个参数是要求和的区域，这里是“Sheet1!$E$3:$E$57”， 单击“确定”按钮，即可求出员工号为H01的总销售额。 步骤3：双击B2单元格的智能填充柄，即可完成其他员工总销售额计算。 步骤4：选中B2:B26单元格区域，单击右键，选择“设置单元格格式”按钮，在【数字】选项卡中，在分类中选择“数值”，小数位数为1，单击“确定”按钮。 6. 利用RANK.EQ函数给出根据总销售额由高到低的排名。 【答案】 步骤1：将光标定位于C2单元格，单击“插入函数”按钮，打开“插入函数”对话框，在“或选择类别”中选择“全部”，在“选择函数”中选中“RANK.EQ”函数，单击“确定”按钮，打开“函数参数”对话框。 步骤2：第一个参数是要参与排名的数，这里选B2。第二个参数是排名区域，这里是B2到B26区域，因此应选中（$B$2:$B$26），注意这里要使用绝对引用符号。第三个参数是表示降序还是升序，0或忽略是表示降序，非零值为升序，本题要求降序，因此输入0或不输入。单击“确定”按钮。双击F3单元格的智能填充柄，即可完成其他行的排名计算。 7. 利用条件格式绿色数据条渐变填充修饰Sheet1工作表的E3:E57单元格区域“销售额(万元)”列。 【答案】选中sheet1工作表中的E3:E57单元格区域，单击【开始】|【样式】分组中的“条件格式”下拉按钮，选择“数据条”中的“渐变颜色”下的“绿色数据条”。 8. 选取Sheet1工作表中统计表1中的“产品名称”列(G10:G14)、“销售数量(件)”列(H10:H14)数据区域的内容建立“簇状条形图”。 【答案】选中工作表sheet1中的G10:H14单元格区域，单击【插入】|【图表】分组中的“推荐的图表”，打开“插入图表”对话框，在“推荐的图表”中选择“簇状条形图”，单击“确定”按钮。 9. 图表标题为“产品销售数量统计图”，以“布局2”和“样式8”修饰图表，并将图表数据条颜色更改为“彩色/调色板3”。将图表插入到当前工作表的“G16:J28”单元格区域内，将Sheet1工作表命名为“员工销售情况表”。 【答案】 步骤1：将“图表标题”修改为“产品销售数量统计图”。 步骤2：单击【图表工具】|【设计】|【图表布局】分组中的“快速布局”下拉按钮，选择“布局2”，在【图表样式】分组中选择“样式8”。 步骤3：在【图表样式】分组中，单击“更改颜色”下拉框中的“彩色-彩色调色板3”。 步骤4：拖动并适当调整图表大小，使其在G16:J28单元格区域内。 步骤5：双击工作表名称“Sheet1”，将其修改为“员工销售情况表”。 10. 选择“图书销售统计表”工作表，对工作表“图书销售统计表”内数据清单的内容建立数据透视表，按行标签为“经销部门”，列标签为“图书类别”，数值为“销售额(万元)”求和布局，并置于现工作表的I11:N19单元格区域。 【答案】 步骤1：打开工作表“图书销售统计表”，单击任一单元格，单击【插入】|【表格】分组中的“数据透视表”，打开“创建数据透视表”对话框。 步骤2：在单选按钮“选择一个表或区域”下的“表/区域”中显示“图书销售统计表!$A$1:$G$97”，选择“现有工作表”单选按钮，单击“位置”后选取按钮，用鼠标选中I11:N19单元格区域，即可在位置中显示“图书销售统计表!$I$11:$N$19”，单击“确定”按钮。 步骤3：在“数据透视表字段”窗口中的“选择要添加到报表的字段”中选中“经销部门”项并拖动到“行”中，选中“图书类别”项并拖动到“列”中，选中“销售额（万元）”项并拖动到“值”中，单击“关闭”按钮。 按<Ctrl+S>组合键保存EXCEL17.xlsx工作簿。 原创精品资源学科网独家享有版权，侵权必究！ 学科网（北京）股份有限公司 $$