1、目录 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套 员工资料
2、表 . 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) 说明：奇数除以
3、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、起。 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(C2AVERAG
5、E($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,“=80“) 7.将Sheet1复制到Sheet3 中，并对Sheet3进行高级筛选，要求： 筛选条件：“语文”75，“数学”75，“英语”75，“总分”250； 将结
6、果保存在Sheet3 中。 先复制表格，再在Sheet3中设置高级筛选所需的条件区域： （注： “=”要在英文状态下输入） 然后点击数据筛选高级，在对话框选好列表区域和条件区域， 得到筛选结果： 8.根据 Sheet1中的结果，在 Sheet4中创建一张数据透视表，要求： 显示是否三科均超过平均分的学生人数； 行区域设置为：“三科成绩是否均超过平均”； 计数项为三科成绩是否均超过平均。 学号 姓名 语文 数学 英语 总分 平均 排名 三科成绩是否均超过平均 20041004 陆东兵 94 90 91 275 91.67 1 TRUE 20041005 闻亚东 84 87 88 259 86.3
7、3 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
8、 258 86.00 6 TRUE 根据条件区域设 置，不一定相同 第 2 套 服装采购 4 鼠标先点在Sheet1的成绩表上， 再选插入数据透视表， 在对话框中选 “现有工作表” ， 如下图： 然后在布局窗口拖动字段“三科成绩是否均超过平均”至“行标签”和“数值”区，如下图： 完成后的数据透视表如下所示： 第 2 套 服装采购 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存 盘。 1在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。 答案：=ROUND(A1,-2) 说明： 第二个参数表示圆整的位数， “2” 表示
9、小数点后2位， “-2” 表示小数点前2位 （即百位） 。 2在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体颜色设置为 红色、加粗显示。 先选中“采购数量”这列的单元格，再点击开始条件格式新建规则，跳出如下对话 框进行相应设置： 行标签 计数项:三科成绩是否均超过平均 FALSE 27 TRUE 11 总计 38 拖动 第 2 套 服装采购 5 3.使用 VLOOKUP函数，对Sheet1中的商品单价进行自动填充。 要求：根据“价格表”中的商品单价，利用VLOOKUP 函数，将其单价自动填充到采购表 中的“单价”列中。 公式为： =VLOOKUP(A11,$F$2
10、:$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”或者都用“”。 5.利用公式，计算Sheet1中的“合计金额”。 要求：根据“采购数量”，“单价”和“折扣”，计算采购的“合计金额”。 计算公式：单价*采购数量*（1-折扣） 公式为： =D11*B11*(1-E11) 注意：使用公式“=B11*D11*(1-E11)”理论上也
11、对，但系统不给分。 6.使用 SUMIF函数，计算各种商品的采购总量和采购总金额，将结果保存在 Sheet1中的“统 计表”当中相应位置。 在J12单元格填入采购总量： =SUMIF(A$11:A$43,I12,B$11:B$43) 然后用填充柄填充至 J13、J14。 在K12单元格填入采购总金额： =SUMIF(A$11:A$43,I12,F$11:F$43) 然后用填充柄填充至 K13、K14。 选择 设置 第 2 套 服装采购 6 7. 将Sheet1中的“采购表”复制到 Sheet2中，并对 Sheet2进行高级筛选。要求： 筛选条件为：“采购数量”150，“折扣”0； 将筛选结果保
12、存在Sheet2中。 先复制“采购表”至Sheet2，其中“单价”和“折扣”两列数据出错，需重新复制、粘贴（用 “值”粘贴）。 再设置条件区域如下： 然后用高级筛选设置好列表区域和条件区域，得如下结果： 8.根据 Sheet1中的采购表，新建一个数据透视图 Chart1，要求： 该图形显示每个采购时间点所采购的所有项目数量汇总情况； x坐标设置为“采购时间”； 求和项为采购数量； 将对应的数据透视表保存在 Sheet3中。 鼠标先点在Sheet1的成绩表上， 再选插入数据透视图， 在对话框中选 “现有工作表” ， 如下图： 项目 采购数量 采购时间 单价 折扣 合计 裤子 185 2008/2
13、/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.
14、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/2
15、4 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中产生了相应的数据透视表，如下
16、所示： 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 中“教材订购情况表”的订购金额进行计算。
17、 将结果保存在该表的“金额”列当中。 计算方法为：金额=订数*单价。 先选中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 200
18、8/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中“
19、用户 支付情况表”的“支付总额”列中。 在单元格 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(OR(MOD(A4,400)=0,AND(MOD(A4,4)=0,MOD(A4,100)=500，且金额
20、=30000”； 将结果保存在Sheet3 中。 先复制表格，再在Sheet3中设置筛选所需的条件区域： 用高级筛选置好列表区域和条件区域，得到如下结果： 8. 根据 Sheet1中“教材订购情况表”的结果，在Sheet4 中新建一张数据透视表。要求： 显示每个客户在每个出版社所订的教材数目； 客户 ISSN 教材名称 出版社 版次 作者 订数 单价 金额 c1 7-03-027426-7 大学信息技术基础 科学出版社 二版 胡同森 1249 18 22482 c1 7-03-012346-8 化工原理(上） 科学出版社 一版 何潮洪 冯霄 767 38 29146 c1 7-121-0282
21、8-9 数字电路 电子工业出版社 一版 贾立新 555 34 18870 c1 7-04-021908-1 复变函数 高等教育出版社 四版 西安交大 540 29 15660 c1 7-04-001245-2 大学文科高等数学 1 高等教育出版社 一版 姚孟臣 518 26 13468 c1 7-81080-159-7 大学英语 快读 2 上海外语教育出版社 修订 谌馨蒸 500 28 14000 c1 7-5341-1523-4 C程序设计基础 浙江科学技术出版社 一版 胡同森 500 30 15000 c3 7-5303-8878-0 国际贸易 中国金融出版社 05版 刘诚 645 35
22、22575 c4 7-402-15710-6 新编统计学原理 立信会计 二版 唐庆银 637 32 20384 c4 7-04-113245-8 经济法（含学习卡） 高等教育 二版 曲振涛 589 35 20615第 3 套 教材订购 10 行区域设置为“出版社”； 列区域设置为“客户”； 求和项为订数； 数据区域设置为“订数”。 鼠标先点在Sheet1的表上， 再选插入数据透视表， 选 “现有工作表” 为 “Sheet4!$A$1”， 进入以下数据布局窗口： 在Sheet4上得到数据透视表，如下所示： 求和项:订数 列标签 行标签 c1 c2 c3 c4 总计 北京航大 63 63 北京理工
23、 421 421 电子工业出版社 555 71 626 东北财经大学出版社 75 75 复旦大学 106 106 高等教育 1061 1061 高等教育出版社 10719 10719 高教 509 509 华东师大 76 76 科学 203 203 科学出版社 2940 2940 立信会计 637 637 立信会计出版社 80 80 辽宁美术出版社 58 58 南京大学 240 240 清华大学 120 120 人民大学 721 721 人民卫生 366 366 上海外语教育出版社 500 500 天津人民美术出版社 58 58 外语教学与研究出版社 9855 9855 浙江科技出版社 150
24、4 1504 浙江科学技术 106 106 浙江科学技术出版社 500 500 中国金融 160 160 中国金融出版社 645 645 中国人大 224 224 中国人民大学出版社 585 167 752 中国物资 109 109 总计 27158 1965 1968 2343 33434 拖动 第 4 套 电话升级 11 第 4 套 电话升级 在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存 盘。 1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。 当录入位数错误时， 提示错误原因， 样式为“警告” ，错误信息为“只能录入5位数字或文
25、本” 。 选定Sheet5的A1，点击数据数据有效性，打开对话框进行设置： 2.在Sheet5的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) 注：当前年份用YEAR(NOW()表示，也可要YEAR(TODAY()。 3.使用时间函数，对Sheet1 中用户的年龄进行计算。要求： 使用当前时间，结合用户的出生
26、年月，计算用户的年龄，并将其计算结果保存在“年龄” 列当中。计算方法为两个时间年份之差。 公式为： =YEAR(NOW()-YEAR(C2) 或者： =YEAR(TODAY()-YEAR(C2) 4.使用 REPLACE函数，对Sheet1中用户的电话号码进行升级。要求： 对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”， 并将其计算结果保存在“升级电话号码”列的相应单元格中。 例如：电话号码“05716742808”升级后为“057186742808” 公式为： =REPLACE(F2,5,0,“8“) 说明：从电话号码的第5位起选0位，指在第5位前插入。
27、第 4 套 电话升级 12 5. 在Sheet1中，使用 AND 函数，根据“性别”及“年龄”列中的数据，判断所有用户是否 为大于等于40岁的男性，并将结果保存在“是否=40 男性”列中。 注意：如果是，保存结果为 TRUE；否则，保存结果为 FALSE。 公式为： =IF(AND(B2=“男“,D2=40),TRUE,FALSE) 6.根据 Sheet1中的数据，对以下条件，使用统计函数进行统计。要求： 统计性别为“男”的用户人数，将结果填入Sheet2的B2单元格中； 统计年龄为“40”岁的用户人数，将结果填入 Sheet2 的 B3 单元格中。 “男”的用户人数： =COUNTIF(Sh
28、eet1!B2:B37,“男“) “40”岁的用户人数： =COUNTIF(Sheet1!D2:D37,“40“) 7. 将Sheet1 复制到 Sheet3，并对Sheet3进行高级筛选。 要求： 筛选条件为：“性别”女，“所在区域”西湖区； 将筛选结果保存在Sheet3中。 先将表格从Sheet1复制到 Sheet3，再设置筛选所需的条件区域为： 注意：“性 别”中间有空格。 应用高级筛选后的结果为： 8. 根据 Sheet1的结果，创建一个数据透视图 Chart1。要求： 显示每个区域所拥有的用户数量； x坐标设置为“所在区域”； 计数项为“所在区域”； 将对应的数据透视表保存在 She
29、et4中。 鼠标先点在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 郁九 女
30、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行，点击数据数据有效性，打开对话框，输入自定义公
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中的每种产品的
32、价值，将结果保存到表中的“价值”列中。 计算价值的计算方法为：“单价*每盒数量*采购盒数”。 数组公式为： =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.某公司对各个部门员工吸
33、烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查 对象，只能回答Y（吸烟）或者 N（不吸烟）。根据调查情况，制做出Sheet3。请使用函数， 统计符合以下条件的数值。 a.统计未登记的部门个数； =COUNTBLANK(B2:E11) b.统计在登记的部门中，吸烟的部门个数。 =COUNTIF(B2:E11,“Y“) 6.使用函数，对Sheet3中的 B21单元格中的内容进行判断，判断其是否问文本，如果是，结 果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在 Sheet3中的B22单元格当中。 在B22单元格写入公式： =ISTEXT(B21) 7.将Sheet1复制到
34、Sheet4 中，对Sheet4进行高级筛选，要求： 筛选条件：“产品为白炽灯，商标为上海”，并将结果保存； 将结果保存在Sheet4 中。 先将表格从Sheet1复制到 Sheet4，再设置筛选所需的条件区域为： 第 6 套 房产销售 15 点击数据筛选高级，经高级筛选后的结果如下： 8.根据 Sheet1的结果，在 Sheet5中创建一张数据透视表，要求： 显示不同商标的不同产品的采购数量； 行区域设置为“产品”； 列区域设置为“商标”； 计数项为“采购盒数”。 鼠标先点在Sheet1的表上， 再选插入数据透视表， 选 “现有工作表” 为 “Sheet5!$A$1”； 进入数据布局窗口后，
35、把字段“产品”拖入行标签、 “商标”拖入列标签、 “采购盒数”拖入 数值区，如下图： 在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.0
36、0 白炽灯 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后的单元格中字体颜色设置 为红色、加粗显示。 先选中“预定日期”这列的单元格，再点击开始条件格
37、式新建规则，跳出如下对话 框进行相应设置： 3. 使用公式，计算Sheet1 中“房产销售表”的房价总额，并保存在“房产总额”列中。 计算公式为：房价总额 = 面积 * 单价。 公式为： =F3*G3 4.使用数组公式，计算Sheet1中“房产销售表”的契税总额，并保存在“契税总额”列中。 计算公式为：契税总额 = 契税 * 房价总额。 数组公式为： =H3:H26*I3:I26 先选中J3:J26，再输入公式，结束按Ctrl+Shift+Enter。 5.使用函数，根据Sheet1中“房产销售表”的结果，在 Sheet2中统计每个销售人员的销售 总额，将结果保存在Sheet2 中的“销售总额
38、”列中。 “人员甲”的销售总额： =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进行高级筛选。要求： 筛选条件为：
39、“户型”为两室一厅，“房价总额”1000000； 将结果保存在Sheet3 中。 先将表格从Sheet1复制到 Sheet3，再设置条件区域为： 点击数据筛选高级，高级筛选后的结果如下： 8.根据 Sheet1中“房产销售表”的结果，创建一个数据透视图 Chart1。要求： 显示每个销售人员所销售房屋应缴纳契税总额混总情况； x坐标设置为“销售人员”； 数据区域为“契税总额”； 求和项设置为契税总额； 将对应的数据透视表保存在 Sheet4中。 鼠标先点在Sheet1的表上， 再选插入数据透视图， 选 “现有工作表” 为 “Sheet4!$A$1”； 进入数据布局窗口后，把字段“销售人员”拖入
40、行标签、 “契税总额”拖入数值区，如下图： 点中产生的图表，鼠标右键移动图表，出现窗口，选“新工作表”为“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
41、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 人员
42、甲 客户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上产
43、生的数据透视表如下： 第 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.
44、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 中的“学位”列进行自动填充。 要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）： - 博士研究生博
45、士 - 硕士研究生硕士 - 本科学士 - 其他无 公式为： =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.修
46、改数组公式，将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进行高级筛选。 筛选条件为：“报考单位”中院、“性别”男、“学历”硕士研究生 将筛选结果保存在She
47、et3中 先把表从Sheet2复制到Sheet3，再设置条件区域如下： 注意：中院包括“一中院”或“三中院”,条件区域多 1 行表示。 点击数据筛选高级，经高级筛选后的结果为： 第 8 套 员工信息 20 8.根据 Sheet2，在 Sheet4 中新建一数据透视表。要求： 显示每个报考单位的人的不同学历的总人数 行区域设置为“报考单位” 列区域设置为“学历” 数据区域设置为“学历” 计数项为学历 注：第1 条要求只是概括性描述，没有对应的实际操作，只需按后面几点设置即可。 鼠标先点在Sheet2的表上， 再选插入数据透视表， 选 “现有工作表” 为 “Sheet4!$A$1”； 进入数据布局
48、窗口后，把字段“报考单位”拖入行标签、“学历”拖入列标签和数值区，如 下图： 在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 公务员考试成绩表 计数项:学历 列标签 行标签 本科 博士研究生
