AOA-EXCEL答案整理.doc

目录目录Excel 习题指南 . 2 第 01 套 三科成绩.2 第 02 套 服装采购.4 第 03 套 教材订购.8 第 04 套 电话升级. 11 第 05 套 灯泡采购.13 第 06 套 房产销售.15 第 07 套 公务员考试.18 第 08 套 员工信息.20 第 09 套 停车记录.23 第 10 套 温度情况.26 第 11 套 学生成绩 .28 第 12 套 销售统计.30 第 13 套 等级考试.32 第 14 套 通讯费计划表.35 第 15 套 医院病人护理.37 第 16 套 图书订购信息.40 第 17 套 学生体育成绩.42 第 18 套 员工资料表.45 第 19 套 公司员工信息表.48 第 20 套 优等生.50 第 21 套 零件检测.531第 1 套 三科成绩Excel 习题指南本文针对 AOA 练习系统中的 21 套 Excel 题目进行分析，并给出参考答案。第 1 套 三科成绩在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。1. 在Sheet1的 A50 单元格中输入分数 1/3。输入：0 1/32. 在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$39),TRUE,FALSE),FALSE),FALSE)6.根据 Sheet1 中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到 Sheet2 中的相应位置。2第 1 套 三科成绩数学分数位于 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,60)数学分数位于 80 到 100 分的人数：=COUNTIF(Sheet1!$D$2:$D$39,=100)-COUNTIF(Sheet1!$D$2:$D$39,=80)7.将 Sheet1 复制到 Sheet3 中，并对 Sheet3 进行高级筛选，要求：u 筛选条件：“语文”75，“数学”75，“英语”75，“总分”250；u 将结果保存在 Sheet3 中。先复制表格，再在 Sheet3 中设置高级筛选所需的条件区域：（注：“=”要在英文状态下输入）然后点击数据筛选高级，在对话框选好列表区域和条件区域，根据条件区域设置，不一定相同得到筛选结果：学号姓名语文数学英语总分平均排名三科成绩是否均超过平均20041004200410052004101020041012200410182004101920041026200410322004103320041038陆东兵闻亚东周旻璐吕秀杰程俊黄威万基莹赵援罗颖张立娜94849481948281948494908787838987839087829188828791888988838227525926325127425725327225425891.6786.3387.6783.6791.3385.6784.3390.6784.6786.00 1 5 410 2 7 9 3 8 6TRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE8.根据 Sheet1 中的结果，在 Sheet4 中创建一张数据透视表，要求：u 显示是否三科均超过平均分的学生人数；u 行区域设置为：“三科成绩是否均超过平均”；u 计数项为三科成绩是否均超过平均。3第 2 套 服装采购鼠标先点在 Sheet1 的成绩表上，再选插入数据透视表，在对话框中选“现有工作表”，如下图：然后在布局窗口拖动字段“三科成绩是否均超过平均”至“行标签”和“数值”区，如下图：拖动完成后的数据透视表如下所示：行标签FALSETRUE总计计数项:三科成绩是否均超过平均271138第 2 套 服装采购在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。1在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。答案：=ROUND(A1,-2)说明：第二个参数表示圆整的位数， 表示小数点后2位，“2”“-2”表示小数点前2位（即百位）。2在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体颜色设置为红色、加粗显示。先选中“采购数量”这列的单元格，再点击开始条件格式新建规则，跳出如下对话框进行相应设置：4第 2 套 服装采购选择设置3.使用 VLOOKUP 函数，对 Sheet1 中的商品单价进行自动填充。u 要求：根据“价格表”中的商品单价，利用 VLOOKUP 函数，将其单价自动填充到采购表 中的“单价”列中。公式为：=VLOOKUP(A11,$F$2:$G$5,2,0)4.使用逻辑函数，对 Sheet1 中的商品折扣率进行自动填充。u 要求：根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购 表中的“折扣“列中。公式为：=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(B11$A$5,$B$4,IF(B11”或者都用“150，“折扣”0；u 将筛选结果保存在 Sheet2 中。先复制“采购表”至 Sheet2，其中“单价”和“折扣”两列数据出错，需重新复制、粘贴（用“值”粘贴）。再设置条件区域如下：然后用高级筛选设置好列表区域和条件区域，得如下结果：项目采购数量采购表采购时间单价折扣合计裤子衣服裤子鞋子衣服裤子鞋子鞋子衣服衣服裤子衣服裤子鞋子衣服裤子衣服裤子鞋子衣服裤子鞋子185225210260385350315340265320400385275240360325295155210395160275 2008/2/5 2008/3/14 2008/3/14 2008/3/14 2008/4/30 2008/4/30 2008/4/30 2008/5/15 2008/6/24 2008/7/10 2008/7/10 2008/8/19 2008/8/19 2008/8/19 2008/9/27 2008/9/272008/10/242008/10/242008/10/24 2008/11/4 2008/11/4 2008/11/4 80120 80150120 80150150120120 80120 80150120 80120 80150120 801500.060.080.080.08 0.1 0.1 0.1 0.10.08 0.1 0.1 0.10.080.08 0.1 0.10.080.060.08 0.10.060.0813,912.0024,840.0015,456.0035,880.0041,580.0025,200.0042,525.0045,900.0029,256.0034,560.0028,800.0041,580.0020,240.0033,120.0038,880.0023,400.0032,568.0011,656.0028,980.0042,660.0012,032.0037,950.008.根据 Sheet1 中的采购表，新建一个数据透视图 Chart1，要求：u 该图形显示每个采购时间点所采购的所有项目数量汇总情况；u x 坐标设置为“采购时间”；u 求和项为采购数量；u 将对应的数据透视表保存在 Sheet3 中。鼠标先点在 Sheet1 的成绩表上，再选插入数据透视图，在对话框中选“现有工作表”，如下图：6第 2 套 服装采购在布局窗口，拖动字段“采购时间”至“行标签”、“采购数量”至“数值”区，如下图所示：拖动点中产生的图表，鼠标右键移动图表，出现下图窗口，选“新工作表”：Chart1 中产生的透视图如下所示：汇总120010008006004002000汇总同时，在 Sheet3 中产生了相应的数据透视表，如下所示：7第 3 套 教材订购行标签求和项:采购数量2008/1/121352008/2/54502008/3/146952008/4/3010502008/5/154852008/6/244902008/7/108452008/8/199002008/9/278052008/10/246602008/11/4830总计7345第 3 套 教材订购在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。选定Sheet5的A1，点击数据数据有效性，打开对话框进行设置：2.在Sheet5的B1单元格中输入分数 1/3输入：0 1/33. 使用数组公式，对 Sheet1 中“教材订购情况表”的订购金额进行计算。u 将结果保存在该表的“金额”列当中。u 计算方法为：金额=订数*单价。先选中 I3:I52，输入 =G3:G52*H3:H52，再按 Ctrl+Shift+Enter注意：按 Ctrl+Shift+Enter 组合健后，公式自动用“ ”括起。4. 使用统计函数，对 Sheet1 中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在 Sheet1 中的相应位置。要求：8第 3 套 教材订购uu统计出版社名称为“高等教育出版社”的书的种类数，并将结果保存在 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 中的年份是否为闰年，如果是，结果保存“闰年”；如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。u 闰年定义：年数能被 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 进行高级筛选。要求u 筛选条件为“订数=500，且金额=30000”；u 将结果保存在 Sheet3 中。先复制表格，再在 Sheet3 中设置筛选所需的条件区域：用高级筛选置好列表区域和条件区域，得到如下结果：客户ISSN教材名称出版社版次作者订数单价金额c1c1c1c1c1c1c1c3c4c47-03-027426-7大学信息技术基础 二版科学出版社胡同森7-03-012346-8化工原理(上）科学出版社一版何潮洪 冯霄7-121-02828-9数字电路 电子工业出版社一版贾立新7-04-021908-1复变函数 高等教育出版社四版西安交大7-04-001245-2大学文科高等数学 1一版高等教育出版社姚孟臣7-81080-159-7大学英语 上海外语教育出版社谌馨蒸快读 2 修订7-5341-1523-4C程序设计基础浙江科学技术出版社胡同森一版7-5303-8878-0国际贸易 中国金融出版社05版刘诚7-402-15710-6新编统计学原理立信会计 二版唐庆银7-04-113245-8经济法（含学习卡）二版高等教育曲振涛1249 767 555 540 518 500 500 645 637 58918383429262830353235224822914618870156601346814000150002257520384206158. 根据 Sheet1 中“教材订购情况表”的结果，在 Sheet4 中新建一张数据透视表。要求：显示每个客户在每个出版社所订的教材数目；9第 3 套 教材订购uuuu行区域设置为“出版社”；列区域设置为“客户”；求和项为订数；数据区域设置为“订数”。鼠标先点在 Sheet1 的表上，再选插入数据透视表，“现有工作表”“Sheet4!$A$1”选为，进入以下数据布局窗口：拖动在 Sheet4 上得到数据透视表，如下所示：求和项:订数行标签北京航大北京理工电子工业出版社东北财经大学出版社复旦大学高等教育高等教育出版社高教华东师大科学科学出版社立信会计立信会计出版社辽宁美术出版社南京大学清华大学人民大学人民卫生上海外语教育出版社天津人民美术出版社外语教学与研究出版社浙江科技出版社浙江科学技术浙江科学技术出版社中国金融中国金融出版社中国人大中国人民大学出版社中国物资总计列标签c1c2c363421555106106110719509 76203294063780582401207213665005898551504106500160645224585167 10927158 1965 1968 23437175c4总计 63 421 626 75 106 1061 10719 509 76 203 2940 637 80 58 240 120 721 366 500 58 9855 1504 106 500 160 645 224 752 1093343410第 4 套 电话升级第 4 套 电话升级在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。选定Sheet5的A1，点击数据数据有效性，打开对话框进行设置：2.在Sheet5的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。u 闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。输入公式： =IF(OR(AND(MOD(YEAR(NOW(),4)=0,MOD(YEAR(NOW(),100)0),MOD(YEAR(NOW(),400)=0),TRUE,FALSE)注：当前年份用YEAR(NOW()表示，也可要YEAR(TODAY()。3.使用时间函数，对 Sheet1 中用户的年龄进行计算。要求：u 使用当前时间，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄” 列当中。计算方法为两个时间年份之差。公式为：=YEAR(NOW()-YEAR(C2)或者：=YEAR(TODAY()-YEAR(C2)4.使用 REPLACE 函数，对 Sheet1 中用户的电话号码进行升级。要求：u 对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”， 并将其计算结果保存在“升级电话号码”列的相应单元格中。u 例如：电话号码“05716742808”升级后为“057186742808”公式为：=REPLACE(F2,5,0,8)说明：从电话号码的第 5 位起选 0 位，指在第 5 位前插入。11第 4 套 电话升级5. 在 Sheet1 中，使用 AND 函数，根据“性别”及“年龄”列中的数据，判断所有用户是否为大于等于 40 岁的男性，并将结果保存在“是否=40 男性”列中。u 注意：如果是，保存结果为 TRUE；否则，保存结果为 FALSE。公式为：=IF(AND(B2=男,D2=40),TRUE,FALSE)6.根据 Sheet1 中的数据，对以下条件，使用统计函数进行统计。要求：u 统计性别为“男”的用户人数，将结果填入 Sheet2 的 B2 单元格中；u 统计年龄为“40”岁的用户人数，将结果填入 Sheet2 的 B3 单元格中。 “男”的用户人数： =COUNTIF(Sheet1!B2:B37,男)“40”岁的用户人数： =COUNTIF(Sheet1!D2:D37,40)7. 将 Sheet1 复制到 Sheet3，并对 Sheet3 进行高级筛选。 要求：u 筛选条件为：“性别”女，“所在区域”西湖区；u 将筛选结果保存在 Sheet3 中。先将表格从 Sheet1 复制到 Sheet3，再设置筛选所需的条件区域为：注意：“性 别”中间有空格。应用高级筛选后的结果为：姓名性别出生年月年龄所在区域 原电话号码升级后号码是否=40男性韩九许九叶五郁九女女女女1973/4/17 1972/9/11970/7/19 1967/4/540414346西湖区西湖区西湖区西湖区05716742809057167428180571674282305716742827057186742809057186742818057186742823057186742827FALSEFALSEFALSEFALSE8.uuuu根据 Sheet1 的结果，创建一个数据透视图 Chart1。要求：显示每个区域所拥有的用户数量；x 坐标设置为“所在区域”；计数项为“所在区域”；将对应的数据透视表保存在 Sheet4 中。鼠标先点在 Sheet1 的表上，再选插入数据透视图，“现有工作表”“Sheet4!$A$1”选为；进入数据布局窗口后，把字段“所在区域”分别拖入行标签和数值区，如下图：12第 5 套 灯泡采购点中产生的图表，鼠标右键移动图表，出现窗口，选“新工作表”为“Chart1”。即在 Chart1 上产生如下透视图：汇总876543210拱墅区 江干区 上城区 西湖区 下城区 余杭区汇总同时，在 Sheet4 上产生的数据透视表如下：行标签拱墅区江干区上城区西湖区下城区余杭区总计计数项:所在区域 6 6 5 6 6 736第 5 套 灯泡采购在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。1.在Sheet1的 B30 单元格中输入分数 1/3 。输入：0 1/32.在Sheet1中设定第 31 行中不能输入重复的数值。先选中31行，点击数据数据有效性，打开对话框，输入自定义公式：13第 5 套 灯泡采购说明：在Excel中第31行用“31:31”表示；在选中第31行时，单元格A31被激活，公式“ =COUNTIF(31:31,A31)=1 ” 实 际 是 输 入 在 A31 上 的 ， 所 以 在 B31 会 自 动 复 制 成“=COUNTIF(31:31,B31)=1”，以此类推，各单元格的值只能出现一次即不能重复。3.使用数组公式，计算 Sheet1 中的每种产品的价值，将结果保存到表中的“价值”列中。u 计算价值的计算方法为：“单价*每盒数量*采购盒数”。数组公式为： =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 进行高级筛选，要求：u 筛选条件：“产品为白炽灯，商标为上海”，并将结果保存；u 将结果保存在 Sheet4 中。先将表格从 Sheet1 复制到 Sheet4，再设置筛选所需的条件区域为：14第 6 套 房产销售点击数据筛选高级，经高级筛选后的结果如下：产品瓦数 寿命（小时） 商标单价每盒数量 采购盒数价值白炽灯白炽灯白炽灯白炽灯白炽灯200 80 10100 4030001000 80020001000上海上海上海上海上海4.500.200.200.800.10 4402510203325554.0024.0010.0040.0010.008.根据 Sheet1 的结果，在 Sheet5 中创建一张数据透视表，要求：u 显示不同商标的不同产品的采购数量；u 行区域设置为“产品”；u 列区域设置为“商标”；u 计数项为“采购盒数”。鼠标先点在 Sheet1 的表上，再选插入数据透视表，“现有工作表”“Sheet5!$A$1”选为；进入数据布局窗口后，把字段“产品”拖入行标签、“商标”拖入列标签、“采购盒数”拖入数值区，如下图：在 Sheet5 上产生的数据透视表如下所示：计数项:采购盒数行标签白炽灯氖管其他日光灯总计列标签北京 上海 (空白) 总计45911222 227815第 6 套 房产销售在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。15第 6 套 房产销售选定Sheet5的A1，点击数据数据有效性，打开对话框进行设置：2.在Sheet1中，使用条件格式将“预定日期”列中日期为2008-4-1后的单元格中字体颜色设置为红色、加粗显示。先选中“预定日期”这列的单元格，再点击开始条件格式新建规则，跳出如下对话框进行相应设置：3. 使用公式，计算 Sheet1 中“房产销售表”的房价总额，并保存在“房产总额”列中。u 计算公式为：房价总额 = 面积 * 单价。公式为： =F3*G34.使用数组公式，计算 Sheet1 中“房产销售表”的契税总额，并保存在“契税总额”列中。u 计算公式为：契税总额 = 契税 * 房价总额。数组公式为： =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 列的宽度，使得数据全部显示出来，否则扣分。16第 6 套 房产销售6.使用 RANK 函数，根据 Sheet2 中“销售总额”列的结果，对每个销售人员的销售情况进行排序，并将结果保存在“排名”列当中。“人员甲”的排名： =RANK(B2,$B$2:$B$6)然后利用填充柄复制公式。7.将 Sheet1 中“房产销售表”复制到 Sheet3 中，并对 Sheet3 进行高级筛选。要求：u 筛选条件为：“户型”为两室一厅，“房价总额”1000000；u 将结果保存在 Sheet3 中。先将表格从 Sheet1 复制到 Sheet3，再设置条件区域为：户型房价总额两室一厅 1000000点击数据筛选高级，高级筛选后的结果如下：姓名联系电话预定日期楼号 房产销售表户型面积单价契税房价总额契税总额销售人员客户7客户9客户11客户13客户15客户17客户19客户21客户23135571123641355711236613557112368135571123701355711237213557112374135571123761355711237813557112380 2008/5/6 5-401 两室一厅2008/4/19 5-501 两室一厅 2008/2/26 5-601 两室一厅2008/9/25 5-701 两室一厅2008/9/16 5-801 两室一厅 2008/5/6 5-901 两室一厅2008/7/26 5-1001 两室一厅2008/7/23 5-1101 两室一厅 2008/4/6 5-1201 两室一厅125.12125.12125.12125.12125.12125.12125.12125.12125.12 8023 8621 8925 9358 9624 99501123513658145211.50%1.50%1.50%1.50%1.50%1.50%1.50%1.50%1.50%1003837.761078659.521116696.001170872.961204154.881244944.001405723.201708888.961816867.5215057.5716179.8916750.4417563.0918062.3218674.1621085.8525633.3327253.01人员戊人员乙人员丙人员乙人员乙人员甲人员戊人员丙人员丙8.根据 Sheet1 中“房产销售表”的结果，创建一个数据透视图 Chart1。要求：u 显示每个销售人员所销售房屋应缴纳契税总额混总情况；u x 坐标设置为“销售人员”；u 数据区域为“契税总额”；u 求和项设置为契税总额；u 将对应的数据透视表保存在 Sheet4 中。鼠标先点在 Sheet1 的表上，再选插入数据透视图，“现有工作表”“Sheet4!$A$1”选为；进入数据布局窗口后，把字段“销售人员”拖入行标签、“契税总额”拖入数值区，如下图：点中产生的图表，鼠标右键移动图表，出现窗口，选“新工作表”为“Chart1”。17第 7 套 公务员考试即在 Chart1 上产生如下透视图：汇总300000250000200000150000100000500000人员丙人员丁人员甲人员戊人员乙汇总与此同时，在 Sheet4 上产生的数据透视表如下：行标签人员丙人员丁人员甲人员戊人员乙总计求和项:契税总额 199857.4008 59564.1012 244122.8748 147790.5024 86253.5637737588.4429第 7 套 公务员考试在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。1.在Sheet1的 A30 单元格中输入分数 2/3。输入：0 2/32.在Sheet1中，设定第 31 行中不能输入重复的数值。先选中31行，点击数据数据有效性，打开对话框，输入自定义公式：18第 7 套 公务员考试说明：在Excel中第31行用“31:31”表示；在选中第31行时，单元格A31被激活，公式“ =COUNTIF(31:31,A31)=1 ” 实 际 是 输 入 在 A31 上 的 ， 所 以 在 B31 会 自 动 复 制 成“=COUNTIF(31:31,B31)=1”，依此类推，各单元格的值只能出现一次即不能重复。3.使用 IF 函数，对 Sheet1 中的“学位”列进行自动填充。u 要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）： - 博士研究生博士 - 硕士研究生硕士 - 本科学士 - 其他无公式为： =IF(G3=博士研究生,博士,IF(G3=硕士研究生,硕士,IF(G3=本科,学士,无)4.使用数组公式，在 Sheet1 中计算：u “笔试比例分”，计算方法为：（笔试成绩/3）*60u “面试比例分”，计算方法为：面试成绩*40u “总成绩”，计算方法为：笔试比例分+面试比例分a.计算公式： =(I3:I18/3)*%60b.计算公式： =K3:K18*%40c.计算公式： =J3:J18+L3:L18注意：a 公式中“（）”不能少，须用数组公式，结束按“Shift+Ctrl+Enter”。5.修改数组公式，将 Sheet1 复制到 Sheet2，在 Sheet2 中计算：u 要求：修改“笔试比例分”的计算，计算方