AOA EXCEL（答案）.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,“75，“数学”75，“英语”75，“总分”250； 将结果保存在 Sheet3 中。 先复制表格，再在 Sheet3 中设置高级筛选所需的条件区域： （注： “=”要在英文状态下输入） 然后点击数据筛选高级，在对话框选好列表区域和条件区域， 得到筛选结果： 8.根据 Sheet1 中的结果，在 Sheet4 中创建一张数据透视表，要求： 显示是否三科均超过平均分的学生人数； 行区域设置为：“三科成绩是否均超过平均”； 计数项为三科成绩是否均超过平均。 学号学号姓名姓名语文语文数学数学英语英语总分总分平均平均排名排名三科成绩是否均超过平均三科成绩是否均超过平均 20041004 陆东兵94909127591.671TRUE 20041005 闻亚东84878825986.335TRUE 20041010 周旻璐94878226387.674TRUE 20041012 吕秀杰81838725183.6710TRUE 20041018 程俊94899127491.332TRUE 20041019 黄威82878825785.677TRUE 20041026 万基莹81838925384.339TRUE 20041032 赵援94908827290.673TRUE 20041033 罗颖84878325484.678TRUE 20041038 张立娜94828225886.006TRUE 根据条件区域设 置，不一定相同 第 2 套 服装采购 4 鼠标先点在 Sheet1 的成绩表上， 再选插入数据透视表， 在对话框中选 “现有工作表” ， 如下图： 然后在布局窗口拖动字段“三科成绩是否均超过平均”至“行标签”和“数值”区，如下图： 完成后的数据透视表如下所示： 第第 2 套套 服装采购服装采购 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存 盘。 1在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。 答案：=ROUND(A1,-2) 说明： 第二个参数表示圆整的位数，“2” 表示小数点后2位，“-2” 表示小数点前2位 （即百位） 。 2在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体颜色设置为 红色、加粗显示。 先选中“采购数量”这列的单元格，再点击开始条件格式新建规则，跳出如下对话 框进行相应设置： 行标签行标签计数项:三科成绩是否均超过平均计数项:三科成绩是否均超过平均 FALSE27 TRUE11 总计总计3838 拖动 第 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(B110； 将筛选结果保存在 Sheet2 中。 先复制“采购表”至 Sheet2，其中“单价”和“折扣”两列数据出错，需重新复制、粘贴（用 “值”粘贴）。 再设置条件区域如下： 然后用高级筛选设置好列表区域和条件区域，得如下结果： 8.根据 Sheet1 中的采购表，新建一个数据透视图 Chart1，要求： 该图形显示每个采购时间点所采购的所有项目数量汇总情况； x 坐标设置为“采购时间”； 求和项为采购数量； 将对应的数据透视表保存在 Sheet3 中。 鼠标先点在 Sheet1 的成绩表上， 再选插入数据透视图， 在对话框中选 “现有工作表” ， 如下图： 项目项目采购数量采购数量采购时间采购时间单价单价折扣折扣合计合计 裤子1851852008/2/5800.0613,912.00 衣服2252252008/3/141200.0824,840.00 裤子2102102008/3/14800.0815,456.00 鞋子2602602008/3/141500.0835,880.00 衣服3853852008/4/301200.141,580.00 裤子3503502008/4/30800.125,200.00 鞋子3153152008/4/301500.142,525.00 鞋子3403402008/5/151500.145,900.00 衣服2652652008/6/241200.0829,256.00 衣服3203202008/7/101200.134,560.00 裤子4004002008/7/10800.128,800.00 衣服3853852008/8/191200.141,580.00 裤子2752752008/8/19800.0820,240.00 鞋子2402402008/8/191500.0833,120.00 衣服3603602008/9/271200.138,880.00 裤子3253252008/9/27800.123,400.00 衣服2952952008/10/241200.0832,568.00 裤子1551552008/10/24800.0611,656.00 鞋子2102102008/10/241500.0828,980.00 衣服3953952008/11/41200.142,660.00 裤子1601602008/11/4800.0612,032.00 鞋子2752752008/11/41500.0837,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/12135 2008/2/5450 2008/3/14695 2008/4/301050 2008/5/15485 2008/6/24490 2008/7/10845 2008/8/19900 2008/9/27805 2008/10/24660 2008/11/4830 总计总计73457345 第 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/1740西湖区05716742809 057186742809FALSE 许九女1972/9/141西湖区05716742818 057186742818FALSE 叶五女1970/7/1943西湖区05716742823 057186742823FALSE 郁九女1967/4/546西湖区05716742827 057186742827FALSE 第 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 总计总计3636 第 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位数字或文本” 。 产品产品瓦数瓦数 寿命（小时）寿命（小时） 商标商标单价单价每盒数量每盒数量 采购盒数采购盒数价值价值 白炽灯2003000上海4.504354.00 白炽灯801000上海0.2040324.00 白炽灯10800上海0.2025210.00 白炽灯1002000上海0.8010540.00 白炽灯401000上海0.1020510.00 计数项:采购盒数计数项:采购盒数列标签列标签 行标签行标签北京北京上海上海(空白)(空白)总计总计 白炽灯459 氖管112 其他22 日光灯22 总计总计7 78 81515 第 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 姓名姓名联系电话联系电话预定日期预定日期楼号楼号户型户型面积面积单价单价契税契税房价总额房价总额契税总额契税总额销售人员销售人员 客户7135571123642008/5/62008/5/65-401两室一厅125.1280231.50% 1003837.7615057.57人员戊 客户9135571123662008/4/192008/4/195-501两室一厅125.1286211.50% 1078659.5216179.89人员乙 客户11135571123682008/2/265-601两室一厅125.1289251.50% 1116696.0016750.44人员丙 客户13135571123702008/9/252008/9/255-701两室一厅125.1293581.50% 1170872.9617563.09人员乙 客户15135571123722008/9/162008/9/165-801两室一厅125.1296241.50% 1204154.8818062.32人员乙 客户17135571123742008/5/62008/5/65-901两室一厅125.1299501.50% 1244944.0018674.16人员甲 客户19135571123762008/7/262008/7/265-1001两室一厅125.12112351.50% 1405723.2021085.85人员戊 客户21135571123782008/7/232008/7/235-1101两室一厅125.12136581.50% 1708888.9625633.33人员丙 客户23135571123802008/4/62008/4/65-1201两室一厅125.12145211.50% 1816867.5227253.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.4429737588.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.0040.2086.5034.6074.808 一中院法官(民事、男)050008501663李小珍男1979/02/16硕士研究生硕士153.5046.0590.6736.2782.321 一中院法官(民事、男)050008504259项文双男1972/10/31硕士研究生硕士133.5040.0585.0034.0074.0510 公务员考试成绩表公务员考试成绩表 计数项:学历计数项:学历列标签列标签 行标签行标签本科本科博士研究生博士研究生大专大专硕士研究生硕士研究生总计总计 区法院314 三中院33 市高院11125 一中院134 总计总计7 72 22 25 51616 第 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” ； 进入数据布局窗口后，把字段“职称”分别拖入行标签和数值区，如下图： 员工姓名员工姓名 员工代码员工代码 升级员工代码升级员工代码 性别性别出生年月出生年月年龄年龄参加工作时间参加工作时间工龄工龄职称职称岗位级别岗位级别是否有资格评选高级工程师是否有资格评选高级工程师 陈小鹰PA128PA0128男1963年11月501987年11月26助工5级FALSE 刘晓瑞PA405PA0405男1979年3月342000年8月13助工5级FALSE 程俊PA602PA0602男1974年1月391992年8月21助工5级FALSE 张永和PA225PA0225男1964年12月491988年8月25助工5级FALSE 董江波PA306PA0306男1973年1月401991年8月22助工5级FALSE 蔡立PA725PA0725男1969年9月441992年9月21助工5级FALSE 孙铎PA803PA0803男1970年1月431992年9月21助工5级FALSE 成晓升PA829PA0829男1968年4月451988年4月25助工4级FALSE 陈德福PA922PA0922男1976年7月371997年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 总计总计6464 第 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小汽车59:30:49 15:13:48 5:42:5930 浙A56587小汽车515:35:42 21:36:14 6:00:3230 停车情况记录表停车情况记录表 第 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 总计总计619619 第 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 中的“相差的温度值”中的数值取其绝对值（均为正数）。注意： 复制过程中，将标题项“温度情况表”连同数据一同复制； 数据表必须顶格放置。 修改后的数组公式为： =ABS(B3:B17-C3:C17) 注意：先选中 E3:E17 作为整体进行修改，再按组合键“Shift+Ctrl+Enter” 。 7.将 Sheet2 中的“温度情况表”复制到 Sheet3 中，并对 Sheet3 进行高级筛选。要求： 筛选条件：“杭州平均气温”=20，“上海平均气温” “2009001” 公式为： =REPLACE(A3,1,0,“2009“) 行标签行标签计数项:温度较高的城市计数项:温度较高的城市 杭州11 上海4 总计总计1515 第 11 套 学生成绩 29 4.使用数组公式，对 Sheet1 中“学生成绩表”的“总分”列进行计算。 计算方法：总分 =语文 + 数