AOAEXCEL答案整理.pdf

目录 1 目录 Excel习题指南 . 2 第 1套 三科成绩 . 2 第 2套 服装采购 . 4 第 3套 教材订购 . 8 第 4套 电话升级 . 11 第 5套 灯泡采购 . 13 第 6套 房产销售 . 15 第 7套 公务员考试 . 18 第 8套 员工信息 . 20 第 9套 停车记录 . 23 第 10套 温度情况 . 26 第 11 套 学生成绩 . 28 第 12套 销售统计 . 30 第 13套 等级考试 . 32 第 14套 通讯费计划表 . 35 第 15套 医院病人护理 . 37 第 16套 图书订购信息 . 40 第 17套 学生体育成绩 . 42 第 18套 员工资料表 . 45 第 19套 公司员工信息表 . 48 第 20套 优等生 . 50 第 21套 零件检测 . 53 第 1 套 三科成绩 2 Excel 习 题 指南 本文针对AOA练习系统中的 21套Excel题目进行分析，并给出参考答案。 第 1 套 三科成绩 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存 盘。 1. 在Sheet1的 A50 单元格中输入分数 1/3。 输入：0 1/3 2. 在Sheet1中使用函数计算全部语文成绩中奇数的个数，结果存放在 B50 单元格中。 答案：=SUM(MOD($C$2:$C$39,2) 说明：奇数除以2的余数为 1，偶数除以2的余数为0，余数的总和即为奇数的个数。这里须 用数组公式，否则出错，公式中的“ ”不能输入，须用 Ctrl+Shift+Enter组合健产生。 3.使用数组公式， 对Sheet1 计算总分和平均分， 将其计算结果保存到表中的 “总分” 列和 “平 均分”列当中。 总分： 先选中F2:F39，输入 =C2:C39+D2:D39+E2:E39，再按 Ctrl+Shift+Enter 平均分： 先选中G2:G39，输入 =F2:F39/3，再按Ctrl+Shift+Enter 注意：须用数组公式，否则不得分。按Ctrl+Shift+Enter 组合健后，公式自动用“ ”括 起。 4.使用 RANK函数，对Sheet1 中的每个同学排名情况进行统计，并将排名结果保存到表中的 “排名”列当中。 排名：=RANK(F2,$F$2:$F$39) 然后利用填充柄复制公式。 5.使用逻辑函数判断Sheet1 中每个同学的每门功课是否均高于平均分， 如果是， 保存结果为 TRUE，否则，保存结果为FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。 公式为： =IF(AND(C2AVERAGE($C$2:$C$39),D2AVERAGE($D$2:$D$39),E2AVERAGE($E$2:$E$39) ),TRUE,FALSE) 或者： =IF(C2AVERAGE($C$2:$C$39),IF(D2AVERAGE($D$2:$D$39),IF(E2AVERAGE($E$2:$E$3 9),TRUE,FALSE),FALSE),FALSE) 6.根据 Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将 统计结果保存到Sheet2中的相应位置。 第 1 套 三科成绩 3 数学分数位于0到 20 分的人数： =COUNTIF(Sheet1!$D$2:$D$39,20) 数学分数位于 20 到40 分的人数： =COUNTIF(Sheet1!$D$2:$D$39,40)-COUNTIF(Sheet1!$D$2:$D$39,20) 数学分数位于 40 到60 分的人数： =COUNTIF(Sheet1!$D$2:$D$39,60)-COUNTIF(Sheet1!$D$2:$D$39,40) 数学分数位于 60 到80 分的人数： =COUNTIF(Sheet1!$D$2:$D$39,80)-COUNTIF(Sheet1!$D$2:$D$39,75，“英语”75，“总分”250； 将结果保存在Sheet3 中。 先复制表格，再在Sheet3中设置高级筛选所需的条件区域： （注： “=”要在英文状态下输入） 然后点击数据筛选高级，在对话框选好列表区域和条件区域， 得到筛选结果： 8.根据 Sheet1中的结果，在 Sheet4中创建一张数据透视表，要求： 显示是否三科均超过平均分的学生人数； 行区域设置为：“三科成绩是否均超过平均”； 计数项为三科成绩是否均超过平均。 学号 姓名 语文 数学 英语 总分 平均 排名 三科成绩是否均超过平均 20041004 陆东兵 94 90 91 275 91.67 1 TRUE 20041005 闻亚东 84 87 88 259 86.33 5 TRUE 20041010 周旻璐 94 87 82 263 87.67 4 TRUE 20041012 吕秀杰 81 83 87 251 83.67 10 TRUE 20041018 程俊 94 89 91 274 91.33 2 TRUE 20041019 黄威 82 87 88 257 85.67 7 TRUE 20041026 万基莹 81 83 89 253 84.33 9 TRUE 20041032 赵援 94 90 88 272 90.67 3 TRUE 20041033 罗颖 84 87 83 254 84.67 8 TRUE 20041038 张立娜 94 82 82 258 86.00 6 TRUE 根据条件区域设 置，不一定相同 第 2 套 服装采购 4 鼠标先点在Sheet1的成绩表上， 再选插入数据透视表， 在对话框中选 “现有工作表” ， 如下图： 然后在布局窗口拖动字段“三科成绩是否均超过平均”至“行标签”和“数值”区，如下图： 完成后的数据透视表如下所示： 第 2 套 服装采购 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存 盘。 1在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。 答案：=ROUND(A1,-2) 说明： 第二个参数表示圆整的位数， “2” 表示小数点后2位， “-2” 表示小数点前2位 （即百位） 。 2在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体颜色设置为 红色、加粗显示。 先选中“采购数量”这列的单元格，再点击开始条件格式新建规则，跳出如下对话 框进行相应设置： 行标签 计数项:三科成绩是否均超过平均 FALSE 27 TRUE 11 总计 38 拖动 第 2 套 服装采购 5 3.使用 VLOOKUP函数，对Sheet1中的商品单价进行自动填充。 要求：根据“价格表”中的商品单价，利用VLOOKUP 函数，将其单价自动填充到采购表 中的“单价”列中。 公式为： =VLOOKUP(A11,$F$2:$G$5,2,0) 4.使用逻辑函数，对Sheet1 中的商品折扣率进行自动填充。 要求：根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购 表中的“折扣“列中。 公式为： =IF(B11=A$6,B$6,IF(B11=A$5,B$5,IF(B11=A$4,B$4,B$3) 或者： =IF(B11$A$4,$B$3,IF(B110； 将筛选结果保存在Sheet2中。 先复制“采购表”至Sheet2，其中“单价”和“折扣”两列数据出错，需重新复制、粘贴（用 “值”粘贴）。 再设置条件区域如下： 然后用高级筛选设置好列表区域和条件区域，得如下结果： 8.根据 Sheet1中的采购表，新建一个数据透视图 Chart1，要求： 该图形显示每个采购时间点所采购的所有项目数量汇总情况； x坐标设置为“采购时间”； 求和项为采购数量； 将对应的数据透视表保存在 Sheet3中。 鼠标先点在Sheet1的成绩表上， 再选插入数据透视图， 在对话框中选 “现有工作表” ， 如下图： 项目 采购数量 采购时间 单价 折扣 合计 裤子 185 2008/2/5 80 0.06 13,912.00 衣服 225 2008/3/14 120 0.08 24,840.00 裤子 210 2008/3/14 80 0.08 15,456.00 鞋子 260 2008/3/14 150 0.08 35,880.00 衣服 385 2008/4/30 120 0.1 41,580.00 裤子 350 2008/4/30 80 0.1 25,200.00 鞋子 315 2008/4/30 150 0.1 42,525.00 鞋子 340 2008/5/15 150 0.1 45,900.00 衣服 265 2008/6/24 120 0.08 29,256.00 衣服 320 2008/7/10 120 0.1 34,560.00 裤子 400 2008/7/10 80 0.1 28,800.00 衣服 385 2008/8/19 120 0.1 41,580.00 裤子 275 2008/8/19 80 0.08 20,240.00 鞋子 240 2008/8/19 150 0.08 33,120.00 衣服 360 2008/9/27 120 0.1 38,880.00 裤子 325 2008/9/27 80 0.1 23,400.00 衣服 295 2008/10/24 120 0.08 32,568.00 裤子 155 2008/10/24 80 0.06 11,656.00 鞋子 210 2008/10/24 150 0.08 28,980.00 衣服 395 2008/11/4 120 0.1 42,660.00 裤子 160 2008/11/4 80 0.06 12,032.00 鞋子 275 2008/11/4 150 0.08 37,950.00 采购表第 2 套 服装采购 7 在布局窗口，拖动字段“采购时间”至“行标签”、“采购数量”至“数值”区，如下图所 示： 点中产生的图表，鼠标右键移动图表，出现下图窗口，选“新工作表”： Chart1中产生的透视图如下所示： 同时，在Sheet3中产生了相应的数据透视表，如下所示： 0 200 400 600 800 1000 1200 汇总 汇总 拖动 第 3 套 教材订购 8 第 3 套 教材订购 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果 存盘。 1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。 当录入位数错误时， 提示错误原因， 样式为“警告” ，错误信息为“只能录入5位数字或文本” 。 选定Sheet5的A1，点击数据数据有效性，打开对话框进行设置： 2.在Sheet5的B1单元格中输入分数 1/3 输入：0 1/3 3. 使用数组公式，对Sheet1 中“教材订购情况表”的订购金额进行计算。 将结果保存在该表的“金额”列当中。 计算方法为：金额=订数*单价。 先选中I3:I52，输入 =G3:G52*H3:H52，再按Ctrl+Shift+Enter 注意：按Ctrl+Shift+Enter 组合健后，公式自动用“ ”括起。 4. 使用统计函数，对Sheet1 中“教材订购情况表”的结果按以下条件进行统计，并将结果 保存在Sheet1中的相应位置。要求： 行标签 求和项:采购数量 2008/1/12 135 2008/2/5 450 2008/3/14 695 2008/4/30 1050 2008/5/15 485 2008/6/24 490 2008/7/10 845 2008/8/19 900 2008/9/27 805 2008/10/24 660 2008/11/4 830 总计 7345第 3 套 教材订购 9 统计出版社名称为“高等教育出版社”的书的种类数，并将结果保存在Sheet1 中 L2 单 元格中； 统计订购数量大于110 且小于850的书的种类数，并将结果保存在Sheet1中L3单元格 中。 L2单元格： =COUNTIF(D3:D52,高等教育出版社) L3单元格： =COUNTIF(G3:G52,110)-COUNTIF(G3:G52,850) 5. 使用函数，计算每个用户所订购图书所需支付的金额，并将结果保存在Sheet1中“用户 支付情况表”的“支付总额”列中。 在单元格 L8 中输入公式： =SUMIF(A$3:A$52,K8,I$3:I$52) 然后填充至L11。 6. 使用函数，判断Sheet2 中的年份是否为闰年，如果是，结果保存“闰年”；如果不是， 则结果保存“平年”，并将结果保存在“是否为闰年”列中。 闰年定义：年数能被 4整除而不能被100整除，或者能被 400整除的年份。 公式为： =IF(MOD(A2,400)=0,闰年,IF(MOD(A2,4)0,平年,IF(MOD(A2,100)0,闰年,平 年) 或者： =IF(OR(MOD(A4,400)=0,AND(MOD(A4,4)=0,MOD(A4,100)0),闰年,平年) 7. 将Sheet1中的“教材订购情况表”复制到 Sheet3 中，对Sheet3进行高级筛选。要求 筛选条件为“订数=500，且金额=40 男性”列中。 注意：如果是，保存结果为 TRUE；否则，保存结果为 FALSE。 公式为： =IF(AND(B2=男,D2=40),TRUE,FALSE) 6.根据 Sheet1中的数据，对以下条件，使用统计函数进行统计。要求： 统计性别为“男”的用户人数，将结果填入Sheet2的B2单元格中； 统计年龄为“40”岁的用户人数，将结果填入 Sheet2 的 B3 单元格中。 “男”的用户人数： =COUNTIF(Sheet1!B2:B37,男) “40”岁的用户人数： =COUNTIF(Sheet1!D2:D37,40) 7. 将Sheet1 复制到 Sheet3，并对Sheet3进行高级筛选。 要求： 筛选条件为：“性别”女，“所在区域”西湖区； 将筛选结果保存在Sheet3中。 先将表格从Sheet1复制到 Sheet3，再设置筛选所需的条件区域为： 注意：“性 别”中间有空格。 应用高级筛选后的结果为： 8. 根据 Sheet1的结果，创建一个数据透视图 Chart1。要求： 显示每个区域所拥有的用户数量； x坐标设置为“所在区域”； 计数项为“所在区域”； 将对应的数据透视表保存在 Sheet4中。 鼠标先点在Sheet1的表上， 再选插入数据透视图， 选 “现有工作表” 为 “Sheet4!$A$1”； 进入数据布局窗口后，把字段“所在区域”分别拖入行标签和数值区，如下图： 姓 名 性 别 出生年月 年 龄 所在区域 原电话号码 升级后号码是否=40男性 韩九 女 1973/4/17 40 西湖区 05716742809 057186742809 FALSE 许九 女 1972/9/1 41 西湖区 05716742818 057186742818 FALSE 叶五 女 1970/7/19 43 西湖区 05716742823 057186742823 FALSE 郁九 女 1967/4/5 46 西湖区 05716742827 057186742827 FALSE第 5 套 灯泡采购 13 点中产生的图表，鼠标右键移动图表，出现窗口，选“新工作表”为“Chart1”。即 在Chart1上产生如下透视图： 同时，在Sheet4上产生的数据透视表如下： 第 5 套 灯泡采购 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存 盘。 1.在Sheet1的 B30 单元格中输入分数 1/3 。 输入：0 1/3 2.在Sheet1中设定第 31 行中不能输入重复的数值。 先选中31行，点击数据数据有效性，打开对话框，输入自定义公式： 0 1 2 3 4 5 6 7 8 拱墅区 江干区 上城区 西湖区 下城区 余杭区 汇总 汇总 行标签 计数项:所在区域 拱墅区 6 江干区 6 上城区 5 西湖区 6 下城区 6 余杭区 7 总计 36第 5 套 灯泡采购 14 说明：在Excel中第31行用“31:31”表示；在选中第31行时，单元格A31被激活，公式 “ =COUNTIF(31:31,A31)=1 ”实 际是输入 在 A31 上 的，所 以在 B31 会自 动复制 成 “=COUNTIF(31:31,B31)=1 ”， 以此类推，各单元格的值只能出现一次即不能重复。 3.使用数组公式，计算Sheet1中的每种产品的价值，将结果保存到表中的“价值”列中。 计算价值的计算方法为：“单价*每盒数量*采购盒数”。 数组公式为： =E2:E17*F2:F17*G2:G17 说明：先选中H2:H17，再输入公式，结束按Ctrl+Shift+Enter。 4.在Sheet2中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存 相应的单元格中。 a. 计算：商标为上海，瓦数小于 100的白炽灯的平均单价； =DAVERAGE(A1:H17,E1,J2:L3) b. 计算：产品为白炽灯，其瓦数大于等于80且小于等于 100的数量。 =DSUM(A1:H17,G1,J7:L8) 5.某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查 对象，只能回答Y（吸烟）或者 N（不吸烟）。根据调查情况，制做出Sheet3。请使用函数， 统计符合以下条件的数值。 a.统计未登记的部门个数； =COUNTBLANK(B2:E11) b.统计在登记的部门中，吸烟的部门个数。 =COUNTIF(B2:E11,Y) 6.使用函数，对Sheet3中的 B21单元格中的内容进行判断，判断其是否问文本，如果是，结 果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在 Sheet3中的B22单元格当中。 在B22单元格写入公式： =ISTEXT(B21) 7.将Sheet1复制到Sheet4 中，对Sheet4进行高级筛选，要求： 筛选条件：“产品为白炽灯，商标为上海”，并将结果保存； 将结果保存在Sheet4 中。 先将表格从Sheet1复制到 Sheet4，再设置筛选所需的条件区域为： 第 6 套 房产销售 15 点击数据筛选高级，经高级筛选后的结果如下： 8.根据 Sheet1的结果，在 Sheet5中创建一张数据透视表，要求： 显示不同商标的不同产品的采购数量； 行区域设置为“产品”； 列区域设置为“商标”； 计数项为“采购盒数”。 鼠标先点在Sheet1的表上， 再选插入数据透视表， 选 “现有工作表” 为 “Sheet5!$A$1”； 进入数据布局窗口后，把字段“产品”拖入行标签、 “商标”拖入列标签、 “采购盒数”拖入 数值区，如下图： 在Sheet5上产生的数据透视表如下所示： 第 6 套 房产销售 在考生文件夹的Excel子文件夹中， 已有Excel.xlsx文件。 按下列要求操作， 并将结果存盘。 1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。 当录入位数错误时， 提示错误原因， 样式为“警告” ，错误信息为“只能录入5位数字或文本” 。 产品 瓦数 寿命（小时） 商标 单价 每盒数量 采购盒数 价值 白炽灯 200 3000 上海 4.50 4 3 54.00 白炽灯 80 1000 上海 0.20 40 3 24.00 白炽灯 10 800 上海 0.20 25 2 10.00 白炽灯 100 2000 上海 0.80 10 5 40.00 白炽灯 40 1000 上海 0.10 20 5 10.00 计数项:采购盒数 列标签 行标签 北京 上海 (空白) 总计 白炽灯 4 5 9 氖管 1 1 2 其他 2 2 日光灯 2 2 总计 7 8 15第 6 套 房产销售 16 选定Sheet5的A1，点击数据数据有效性，打开对话框进行设置： 2.在Sheet1中，使用条件格式将“预定日期”列中日期为2008-4-1后的单元格中字体颜色设置 为红色、加粗显示。 先选中“预定日期”这列的单元格，再点击开始条件格式新建规则，跳出如下对话 框进行相应设置： 3. 使用公式，计算Sheet1 中“房产销售表”的房价总额，并保存在“房产总额”列中。 计算公式为：房价总额 = 面积 * 单价。 公式为： =F3*G3 4.使用数组公式，计算Sheet1中“房产销售表”的契税总额，并保存在“契税总额”列中。 计算公式为：契税总额 = 契税 * 房价总额。 数组公式为： =H3:H26*I3:I26 先选中J3:J26，再输入公式，结束按Ctrl+Shift+Enter。 5.使用函数，根据Sheet1中“房产销售表”的结果，在 Sheet2中统计每个销售人员的销售 总额，将结果保存在Sheet2 中的“销售总额”列中。 “人员甲”的销售总额： =SUMIF(Sheet1!K$3:K$26,A2,Sheet1!I$3:I$26) 其它人员的销售总额可用填充柄复制公式求得。 注意：由于销售总额数值较大，应拉大B列的宽度，使得数据全部显示出来，否则扣分。 第 6 套 房产销售 17 6.使用 RANK函数，根据Sheet2中“销售总额”列的结果，对每个销售人员的销售情况进行 排序，并将结果保存在“排名”列当中。 “人员甲”的排名： =RANK(B2,$B$2:$B$6) 然后利用填充柄复制公式。 7.将Sheet1中“房产销售表”复制到 Sheet3中，并对 Sheet3进行高级筛选。要求： 筛选条件为：“户型”为两室一厅，“房价总额”1000000； 将结果保存在Sheet3 中。 先将表格从Sheet1复制到 Sheet3，再设置条件区域为： 点击数据筛选高级，高级筛选后的结果如下： 8.根据 Sheet1中“房产销售表”的结果，创建一个数据透视图 Chart1。要求： 显示每个销售人员所销售房屋应缴纳契税总额混总情况； x坐标设置为“销售人员”； 数据区域为“契税总额”； 求和项设置为契税总额； 将对应的数据透视表保存在 Sheet4中。 鼠标先点在Sheet1的表上， 再选插入数据透视图， 选 “现有工作表” 为 “Sheet4!$A$1”； 进入数据布局窗口后，把字段“销售人员”拖入行标签、 “契税总额”拖入数值区，如下图： 点中产生的图表，鼠标右键移动图表，出现窗口，选“新工作表”为“Chart1”。 户型 房价总额 两室一厅 1000000 姓名 联系电话 预定日期 楼号 户型 面积 单价 契税 房价总额 契税总额 销售人员 客户7 13557112364 2008/5/6 5-401 两室一厅 125.12 8023 1.50% 1003837.76 15057.57 人员戊 客户9 13557112366 2008/4/19 5-501 两室一厅 125.12 8621 1.50% 1078659.52 16179.89 人员乙 客户11 13557112368 2008/2/26 5-601 两室一厅 125.12 8925 1.50% 1116696.00 16750.44 人员丙 客户13 13557112370 2008/9/25 5-701 两室一厅 125.12 9358 1.50% 1170872.96 17563.09 人员乙 客户15 13557112372 2008/9/16 5-801 两室一厅 125.12 9624 1.50% 1204154.88 18062.32 人员乙 客户17 13557112374 2008/5/6 5-901 两室一厅 125.12 9950 1.50% 1244944.00 18674.16 人员甲 客户19 13557112376 2008/7/26 5-1001 两室一厅 125.12 11235 1.50% 1405723.20 21085.85 人员戊 客户21 13557112378 2008/7/23 5-1101 两室一厅 125.12 13658 1.50% 1708888.96 25633.33 人员丙 客户23 13557112380 2008/4/6 5-1201 两室一厅 125.12 14521 1.50% 1816867.52 27253.01 人员丙 房产销售表第 7 套 公务员考试 18 即在Chart1上产生如下透视图： 与此同时，在 Sheet4上产生的数据透视表如下： 第 7 套 公务员考试 在考生文件夹的Excel子文件夹中， 已有Excel.xlsx文件。 按下列要求操作， 并将结果存盘。 1.在Sheet1的 A30 单元格中输入分数 2/3。 输入：0 2/3 2.在Sheet1中，设定第 31 行中不能输入重复的数值。 先选中31行，点击数据数据有效性，打开对话框，输入自定义公式： 0 50000 100000 150000 200000 250000 300000 人员丙 人员丁 人员甲 人员戊 人员乙 汇总 汇总 行标签 求和项:契税总额 人员丙 199857.4008 人员丁 59564.1012 人员甲 244122.8748 人员戊 147790.5024 人员乙 86253.5637 总计 737588.4429第 7 套 公务员考试 19 说明：在Excel中第31行用“31:31”表示；在选中第31行时，单元格A31被激活，公式 “ =COUNTIF(31:31,A31)=1 ”实 际是输入 在 A31 上 的，所 以在 B31 会自 动复制 成 “=COUNTIF(31:31,B31)=1 ”， 依此类推，各单元格的值只能出现一次即不能重复。 3.使用 IF函数，对Sheet1 中的“学位”列进行自动填充。 要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）： - 博士研究生博士 - 硕士研究生硕士 - 本科学士 - 其他无 公式为： =IF(G3=博士研究生,博士,IF(G3=硕士研究生,硕士,IF(G3=本科,学士,无 ) 4.使用数组公式，在Sheet1 中计算： “笔试比例分”，计算方法为：（笔试成绩/3）*60 “面试比例分”，计算方法为：面试成绩*40 “总成绩”，计算方法为：笔试比例分+面试比例分 a.计算公式： =(I3:I18/3)*%60 b.计算公式： =K3:K18*%40 c.计算公式： =J3:J18+L3:L18 注意：a公式中“（）”不能少，须用数组公式，结束按“Shift+Ctrl+Enter”。 5.修改数组公式，将Sheet1 复制到Sheet2，在 Sheet2 中计算： 要求：修改“笔试比例分”的计算，计算方法为：（笔试成绩/2）*60）。 修改后公式为： =(I3:I18/2)*%60 注意：先选中J3:J18整体后再修改，结束按“Shift+Ctrl+Enter” 。 6.在 Sheet2中，添加一列，将其命名为“排名”。 要求：使用RANK函数，根据“总成绩”对所有考生排名。 公式为： =RANK(M3,$M$3:$M$18) 7.将Sheet2复制到Sheet3，并对Sheet3进行高级筛选。 筛选条件为：“报考单位”中院、“性别”男、“学历”硕士研究生 将筛选结果保存在Sheet3中 先把表从Sheet2复制到Sheet3，再设置条件区域如下： 注意：中院包括“一中院”或“三中院”,条件区域多 1 行表示。 点击数据筛选高级，经高级筛选后的结果为： 第 8 套 员工信息 20 8.根据 Sheet2，在 Sheet4 中新建一数据透视表。要求： 显示每个报考单位的人的不同学历的总人数 行区域设置为“报考单位” 列区域设置为“学历” 数据区域设置为“学历” 计数项为学历 注：第1 条要求只是概括性描述，没有对应的实际操作，只需按后面几点设置即可。 鼠标先点在Sheet2的表上， 再选插入数据透视表， 选 “现有工作表” 为 “Sheet4!$A$1”； 进入数据布局窗口后，把字段“报考单位”拖入行标签、“学历”拖入列标签和数值区，如 下图： 在Sheet4上产生的数据透视表如下： 第 8 套 员工信息 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果 存盘。 1.在Sheet4的A1单元格中设置为只能录入5位数字或文本。 当录入位数错误时， 提示错误原因， 样式为“警告” ，错误信息为“只能录入5位数字或文本” 。 选定Sheet4的A1，点击数据数据有效性，打开对话框进行设置： 报考单位 报考职位 准考证号 姓名 性别 出生年月 学历 学位 笔试成绩 笔试成绩比 例分 面试成绩 面试成绩比 例分 总成绩 排名 一中院 法官(刑事、男)050008502309 郎怀民 男 1970/07/30 硕士研究生 硕士 134.00 40.20 86.50 34.60 74.80 8 一中院 法官(民事、男)050008501663 李小珍 男 1979/02/16 硕士研究生 硕士 153.50 46.05 90.67 36.27 82.32 1 一中院 法官(民事、男)050008504259 项文双 男 1972/10/31 硕士研究生 硕士 133.50 40.05 85.00 34.00 74.05 10 公务员考试成绩表 计数项:学历 列标签 行标签 本科 博士研究生 大专 硕士研究生 总计 区法院 3 1 4 三中院 3 3 市高院 1 1 1 2 5 一中院 1 3 4 总计 7 2 2 5 16第 8 套 员工信息 21 2.在Sheet4中，使用函数，将B1中的时间四舍五入到最接近的15分钟的倍数，结果存放在C1 单元格中。 公式为： =ROUND(B1*96,0)/96 说明：Excel的日期或时间是以天为单位存储的，如果直接取整则会四舍五入到天。由于1天 等于96个15分钟，先乘以96倍，再取整则四舍五入到最接近的15分钟的倍数。最后还要除以 96，恢复以天为单位的时间格式。 3.使用 REPLACE函数，对Sheet1中“员工信息表”的员工代码进行升级。要求： 升级方法：在 PA 后面加上 0； 将升级后的员工代码结果填入表中的“升级员工代码”列中。 例如：PA125，修改后为PA0125。 公式为： =REPLACE(B3,3,0,0) 说明：员工代码从第 3位起取 0位，意味着在第3位前插入。 4. 使用时间函数，计算Sheet1中“员工信息表”的“年龄”列和“工龄”列。要求： 使用当前时间，结合表中的“出生年月”、“参加工作时间”列，对员工“年龄”和“工 龄”进行计算； 计算方法为两年份之差。并将结果保存到表中的“年龄”列和“工龄”列中。 年龄公式： =YEAR(Now()-YEAR(E3) 工龄公式： =YEAR(Now()-YEAR(G3) 5.使用统计函数，根据Sheet1中“员工信息表”的数据，对以下条件进行统计。 统计男性员工的人数，结果填入 N3单元格中； 统计高级工程师人数，结果填入 N4单元格中； 统计工龄大于等于 10 的人数，结果填入 N5 单元格中。 N3单元格中填入： =COUNTIF(D3:D66,男) N4单元格中填入： =COUNTIF(I3:I66,高级工程师) N5单元格中填入： =COUNTIF(H3:H66,=10) 第 8 套 员工信息 22 6. 使用逻辑函数，判断员工是否有资格评“高级工程师”。要求： 评选条件为：工龄大于 20，且为工程师的员工； 并将结果保存在“是否资格评选高级工程师”列中； 如果有资格，保存结果为TRUE；否则为FALSE。 公式为： =IF(H320,IF(I3=工程师,TRUE,FALSE),FALSE) 或者 =IF(AND(H320,I3=工程师),TRUE ,FALSE) 7. 将Sheet1的“员工信息表”复制到 Sheet2中，并对 Sheet2进行高级筛选。要求： 筛选条件为：“性别”男，“年龄”30，“工龄”10，“职称”助工； 将结果保存在Sheet2 中。 先把表从Sheet1 复制到Sheet2，再设置条件区域如下： 点击数据筛选高级，经高级筛选后的结果为： 8.根据 Sheet1中的数据，创建一个数据透视图 Chart1。要求： 显示工厂中各种职称人数的汇总情况； x坐标设置为“职称”； 计数项为职称； 数据区域为“职称”； 将对应的数据透视表保存在 Sheet3中。 鼠标先点在Sheet1的表上， 再选插入数据透视图， 选 “现有工作表” 为 “Sheet3!$A$1”； 进入数据布局窗口后，把字段“职称”分别拖入行标签和数值区，如下图： 员工姓名 员工代码 升级员工代码 性别 出生年月 年龄 参加工作时间 工龄 职称 岗位级别 是否有资格评选高级工程师 陈小鹰 PA128 PA0128 男 1963年11月 50 1987年11月 26 助工 5级 FALSE 刘晓瑞 PA405 PA0405 男 1979年3月 34 2000年8月 13 助工 5级 FALSE 程俊 PA602 PA0602 男 1974年1月 39 1992年8月 21 助工 5级 FALSE 张永和 PA225 PA0225 男 1964年12月 49 1988年8月 25 助工 5级 FALSE 董江波 PA306 PA0306 男 1973年1月 40 1991年8月 22 助工 5级 FALSE 蔡立 PA725 PA0725 男 1969年9月 44 1992年9月 21 助工 5级 FALSE 孙铎 PA803 PA0803 男 1970年1月 43 1992年9月 21 助工 5级 FALSE 成晓升 PA829 PA0829 男 1968年4月 45 1988年4月 25 助工 4级 FALSE 陈德福 PA922 PA0922 男 1976年7月 37 1997年8月 16 助工 4级 FALSE 员工信息表第 9 套 停车记录 23 点中产生的图表，鼠标右键移动图表，出现窗口，选“新工作表”为“Chart1”。 即在Chart1上产生如下透视图： 与此同时，在Sheet3上产生数据透视表如下所示： 第 9 套 停车记录 在考生文件夹的Excel子文件夹中， 已有Excel.xlsx文件。 按下列要求操作， 并将结果存盘。 1.在Sheet4的A1单元格中设置为只能录入5位数字或文本。 当录入位数错误时， 提示错误原因， 样式为“警告” ，错误信息为“只能录入5位数字或文本” 。 选定Sheet4的A1，点击数据数据有效性，打开对话框进行设置： 0 5 10 15 20 汇总 汇总 行标签 计数项:职称 高级工 2 高级工程师 14 工程师 11 技工 5 技师 1 技术员 11 中级工 1 助工 19 总计 64第 9 套 停车记录 24 2.在Sheet4的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。 闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。 输入公式： =IF(OR(AND(MOD(YEAR(NOW(),4)=0,MOD(YEAR(NOW(),100)0),MOD(YEAR(NOW(),400)=0) ,TRUE,FALSE) 3.使用 HLOOKUP函数，对Sheet1“停车情况记录表”中的“单价”列进行填充。要求： 根据Sheet1中的“停车价目表” 价格， 使用 HLOOKUP函数对 “停车情况记录表” 中的 “单 价”列根据不同的车型进行填充。 公式为： =HLOOKUP(B9,$A$2:$C$3,2,FALSE) 4.在Sheet1中，使用数组公式计算汽车在停车库中的停放时间。要求： 计算方法为：“停放时间=出库时间-入库时间” 格式为：“小时:分钟:秒” 将结果保存在“停车情况记录表”中的“停放时间”列中。 例如：一小时十五分十二秒在停放时间中的表示为：“1:15:12” 先选中F9:F39，输入公式“=E9:E39-D9:D39”，再按“Shift+Ctrl+Enter” 。 5.使用函数公式，对“停车情况记录表”的停车费用进行计算。要求： 根据Sheet1停放时间的长短计算停车费用， 将计算结果填入到 “停车情况记录表” 的 “应 付金额”列中。 注意： 停车按小时收费，对于不满一个小时的按照一个小时计费； 对于超过整点小时数十五分钟（包含十五分钟）的多累积一个小时。 例如1小时 23分，将以2小时计费。 公式为： =IF(HOUR(F9)=15,HOUR(F9)+1,HOUR(F9)*C9 6.使用统计函数，对Sheet1 中的“停车情况记录表”根据下列条件进行统计。要求： 统计停车费用大于等于 40元的停车记录条数，并将结果保存在J8单元格中； 统计最高的停车费用，并将结果保存在 J9 单元格中。 a.公式为： =COUNTIF(G9:G39,=40) 第 9 套 停车记录 25 b.公式为： =MAX(G9:G39) 7.将Sheet1中的“停车情况记录表”复制到 Sheet2中，对 Sheet2进行高级筛选。要求： 筛选条件为：“车型”小汽车，“应付金额”30； 将结果保存在Sheet2 中。 先复制“停车情况记录表”至 Sheet2，其中“单价”和“应付金额”两列数据出错，需重新 复制、粘贴（粘贴时用“值”选项）。再设置条件区域如下： 点击数据筛选高级，经高级筛选后得到的结果： 8.根据 Sheet1中的“停车情况记录表”，创建一个数据透视图 Chart1。要求： 显示各种车型所收费用的汇总； x坐标设置为“车型”； 求和项为“应付金额”； 将对应的数据透视表保存在 Sheet3中。 鼠标先点在Sheet1的表上， 再选插入数据透视图， 选 “现有工作表” 为 “Sheet3!$A$1”； 进入数据布局窗口后，把字段“车型”拖入行标签、“应付金额”拖入数值区，如下图： 点中产生的图表，鼠标右键移动图表，出现窗口，选“新工作表”为“Chart1”。 即在Chart1上产生如下透视图： 车牌号 车型 单价 入库时间 出库时间 停放时间 应付金额 浙A66871 小汽车 5 9:30:49 15:13:48 5:42:59 30 浙A56587 小汽车 5 15:35:42 21:36:14 6:00:32 30 停车情况记录表第 10 套 温度情况 26 与此同时，在Sheet3上产生数据透视表如下所示： 第 10套 温度情况 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果 存盘。 1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。 当录入位数错误时， 提示错误原因， 样式为“警告” ，错误信息为“只能录入5位数字或文本” 。 选定Sheet5的A1，点击数据数据有效性，打开对话框进行设置： 2.在Sheet5中，使用函数，根据A2单元格中的身份证号码判断性别，结果为“男”或“女” ， 存放在B2单元格中。 倒数第二位为奇数的为“男” ，为偶数的为“女” 。 0 50 100 150 200 250 300 大客车 小汽车 中客车 汇总 汇总 行标签 求和项:应付金额 大客车 200 小汽车 155 中客车 264 总计 619第 10 套 温度情况 27 公式为： =IF(MOD(MID(A2,17,1),2)=1,男,女) 注：身份证号码倒数第二位即第17位。 3.使用 IF函数，对Sheet1“温度情况表”中的“温度较高的城市”列进行填充，填充结果 为城市名称。 公式为： =IF(B3C3,杭州,上海) 4.使用数组公式，对Sheet1“温度情况表”中的相差温度值（杭州相对于上海的温差）进行 计算，并把结果保存在“相差温度值”列中。 计算方法：相差温度值 = 杭州平均气温 - 上海平均气温。 先选中E3:E17，输入公式“= B3:B17-C3:C17”，再按“Shift+Ctrl+Enter”。 5.使用函数，根据Sheet1“温度情况表”中的结果，对符合以下条件的进行统计。要求： 杭州这半个月以来的最高气温和最低气温，保存在相应单元格中； 上海这半个月以来的最高气温和最低气温，保存在相应单元格中。 a.公式分别为： =MAX(B3:B17) =MIN(B3:B17) b.公式分别为： =MAX(C3:C17) =MIN(C3:C17) 6.将Sheet1中的“温度情况表”复制到 Sheet2 中，在 Sheet2中，重新编辑数组公式，将 Sheet2中的“相差的温度