考试宝典(Excel)

省二级AOA考试 宝典 AOA Excel 考试（对照）参考 (一) 学号(数组if).xls 1. 使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。 总分：{=C2:C39+D2:D39+E2:E39} 平均分：{=F2:F39/3} （注意，题目明确要求用数组公式，就必须用数组公式，否则不得分。数组公式中的一对{}不是人为录入的，必须用Ctrl+Shift+Enter组合健输入。） 2. 使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。 排名：=RANK(G2,G$2:G$39) 或者 =RANK(F2,F$2:F$39) 然后利用填充柄复制公式 3. 使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。 =IF(AND(C2>AVERAGE($G$2:$G$39),D2>AVERAGE($G$2:$G$39),E2>AVERAGE($G$2:$G$39)),TRUE,FALSE) 或者： =IF(C2>AVERAGE($C$2:$C$39),IF(D2>AVERAGE($D$2:$D$39),IF(E2>AVERAGE($E$2:$E$39),TRUE,FALSE),FALSE),FALSE) 4. 根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2中的相应位置。 数学分数位于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") 或者：利用数据库函数Dcount，如下所示，在sheet2表中自己构建条件区间，如；B10:C11。 =DCOUNT(Sheet1!A1:I39,Sheet1!D1, B10:C11) 数学 数学 数学 数学 数学 数学 数学 数学 数学 数学 >=0 <20 >=20 <40 >=40 <60 >=60 <80 >=80 <=100 5. 将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选，要求： a. 筛选条件：“语文”>＝75，“数学”>＝75，“英语”>＝75，“总分”>＝250； 筛选的条件区间如下： 语文 数学 英语 总分 >=75 >=75 >=75 >=250 b. 将结果保存在Sheet3中。 注：（a）无需考虑是否删除或移动筛选条件; （b）复制过程中，将标题项“学生成绩表”连同数据一同复制; （c）复制数据表后，粘贴时，数据表必须顶格放置。 6. 根据Sheet1中的结果，在Sheet4中创建一张数据透视表，要求： a. 显示是否三科均超过平均分的学生人数； b. 行区域设置为：“三科成绩是否均超过平均”； c. 计数项为三科成绩是否均超过平均。 完成的数据透视表应如下所示。 计数项:三科成绩是否均超过平均 　 三科成绩是否均超过平均 汇总 FALSE 27 TRUE 11 总计 38 (二) 折扣表(采购表)(VLOOKUP函数).xls 1. 使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。 要求：根据“价格表”中的商品单价，利用VLOOKUP函数， 将其单价自动填充到采购表中的“单价”列中。 =VLOOKUP(A11,F$2:G$5,2,0) 或者用数组公式做： =VLOOKUP($A$11:$A$43,$F$2:$G$5,2,0) 2. 使用逻辑函数，对Sheet1中的商品折扣率进行自动填充。 要求：根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的“折扣“列中。 =IF(B11>=A$6,B$6,IF(B11>=A$5,B$5,IF(B11>=A$4,B$4,B$3))) 3. 利用公式，计算Sheet1中的“合计金额”。 要求：根据“采购数量”，“单价”和“折扣”，计算采购的“合计金额”。 计算公式：单价*采购数量*（1-折扣） =D11*B11*(1-E11) 4. 使用SUMIF函数，统计各种商品的采购总量和采购总金额，将结果保存在Sheet1中的“统计表”当中。 采购总量：=SUMIF(A$11:A$43,I12,B$11:B$43) 采购总金额：=SUMIF(A$11:A$43,I12,F$11:F$43) 5. 对Sheet2中的“采购表”进行高级筛选。 a. 筛选条件为：“采购数量”>150，“折扣”>0； 采购数量 折扣 >150 >0 b. 将筛选结果保存在Sheet2中。 6. 根据Sheet1中的采购表，新建一个数据透视图Chart1，要求： a. 该图形显示每个采购时间点所采购的所有项目数量汇总情况； b. x坐标设置为“采购时间”； c. 将对应的数据透视表保存在Sheet3中。 透视图chat1如下所示； Sheet3中的透视表如下所示： 求和项:采购数量 项目 　 　 　 采购时间 裤子 鞋子 衣服 总计 2008-1-12 45 70 20 135 2008-2-5 185 140 125 450 2008-3-14 210 260 225 695 2008-4-30 350 315 385 1050 2008-5-15 120 340 25 485 2008-6-24 125 100 265 490 2008-7-10 400 125 320 845 2008-8-19 275 240 385 900 2008-9-27 325 120 360 805 2008-10-24 155 210 295 660 2008-11-4 160 275 395 830 总计 2350 2195 2800 7345 (三) 客户（教材）(数组公式if).xls 1. 使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的“金额”列当中。 {=G2:G51*H2:H51} 2. 使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置，要求： a. 统计出版社名称为“高等教育出版社”的书的种类数； =COUNTIF(D2:D51,"高等教育出版社") 或者： =DCOUNT(A1:I51,7，K14:K15) 其中K14:K15为自己构建的条件区域。 b. 统计订购数量大于110且小于850的书的种类数。 =COUNTIF(G2:G51,">110")-COUNTIF(G2:G51,">=850") 或者： =DCOUNT(A1:I51,7,L14:M15) 条件区域如下： 出版社 订数 订数 高等教育出版社 >110 <850 3. 使用函数计算，每个用户所订购图书所需支付的金额总数，将结果保存在Sheet1中的相应位置。 =SUMIF(A$2:A$51,K8,I$2:I$51) 4. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。 说明：闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。 =IF(MOD(A2,400)=0,"闰年",IF(MOD(A2,4)<>0,"平年",IF(MOD(A2,100)<>0,"闰年","平年"))) 或者： =IF(OR(AND(A2/4=TRUNC(A2/4),A2/100<>TRUNC(A2/100)),A2/400=TRUNC(A2/400)),"闰年","平年") 5. 将Sheet1复制到Sheet3中，对Sheet3进行高级筛选，要求： a. 筛选条件为“订数>=500，且金额总数<=30000”； 订数 金额 >=500 <=30000 b. 将结果保存在Sheet2中。 6. 根据Sheet1中的结果，在Sheet4中新建一张数据透视表，要求： a. 显示每个客户在每个出版社所订的教材数目； b. 行区域设置为：“出版社”； c. 列区域设置为：“客户”； d. 计数项为订数。 透视表如下： 求和项:订数 客户 　 　 　 　 出版社 c1 c2 c3 c4 总计 北京航大 　 　 63 　 63 北京理工 　 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 浙江科技出版社 1504 1504 浙江科学技术 　 106 106 浙江科学技术出版社 500 500 中国金融 　 160 160 中国金融出版社 　 645 645 中国人大 　 224 224 中国人民大学出版社 585 167 752 中国物资 　 109 109 总计 27158 1965 1968 2343 33434 (四) 姓名(时间函数).xls 1. 使用时间函数，对Sheet1中用户的年龄进行计算。 要求：计算用户的年龄，并将其计算结果填充到“年龄”列当中。 =YEAR(NOW())-YEAR(C2) 或者： =YEAR(TODAY())-YEAR(C2) 2. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。 要求：对“原电话号码”列中的电话号码进行升级。 升级方法是在区号（0571）后面加上“8”， 并将其计算结果保存在“升级电话号码”列的相应单元格中。 =REPLACE(F2,1,4,"05718") 或者： =REPLACE(F2,5,8,"8"&RIGHT(F2,7)) （注意；先设单元格格式设为常规，再用公式。） 3. 使用逻辑函数，判断Sheet1中的“大于等于40岁的男性”，将结果保存在Sheet1中的“是否>=40男性”。 =IF(D2>=40,IF(B2="男",TRUE,FALSE),FALSE) 4. 对Sheet1中的数据，根据以下条件,利用函数进行统计： a. 统计性别为“男”的用户人数，将结果填入Sheet2的B1单元格中； =COUNTIF(Sheet1!B2:B37,"男") b. 统计年龄为“>40”岁的用户人数，将结果填入Sheet2的B2单元格中。=COUNTIF(Sheet1!D2:D37,">40") 5. 将Sheet1复制到Sheet3，并对Sheet3进行高级筛选。 a. 筛选条件为：“性别”－女、“所在区域”－西湖区； 所在区域 性 别 西湖区 女 b. 将筛选结果保存在Sheet3中。 6. 根据Sheet1的结果，创建一数据透视图Chart1，要求： a. 显示每个区域所拥有的用户数量； b. x坐标设置为“所在区域”； c. 计数项为“所在区域”； d. 将对应的数据透视表保存在Sheet4中。 透视图chart1如下所示； 数据透视表如下： 计数项:所在区域 　 所在区域 汇总 拱墅区 6 江干区 6 上城区 5 西湖区 6 下城区 6 余杭区 7 总计 36 (五) 产品(数组公式if).xls 1. 使用数组公式，计算Sheet1中的每种产品的价值，将结果保存 到表中的“价值”列中。 计算价值的计算方法为：“单价*每盒数量*采购盒数”。 {=E2:E17*F2:F17*G2:G17} 2. 在Sheet2中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存相应的单元格中。 a. 计算：商标为上海，瓦数小于100的白炽灯的平均单价； =DAVERAGE(A1:H17,E1,J2:L3) b. 计算：产品为白炽灯，其瓦数大于等于80且小于等于100的数量。 =DSUM(A1:H17,G1,J7:L8) 3. 某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。根据调查情况，制做出Sheet3。请使用函数，统计符合以下条件的数值。 a. 统计未登记的部门个数； =COUNTBLANK(B2:E11) b. 统计在登记的部门中，吸烟的部门个数。 =COUNTIF(B2:E11,"Y") 4. 使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否问文本，如果是，结果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在Sheet3中的B22单元格当中。 =ISTEXT(B21) 5. 将Sheet1复制到Sheet4中，对Sheet4进行高级筛选，要求： a. 筛选条件：“产品为白炽灯，商标为上海”，并将结果保存； 产品 商标 白炽灯 上海 b. 将结果保存在Sheet4中。 6. 根据Sheet1的结果，在Sheet5中创建一张数据透视表，要求： a. 显示不同商标的不同产品的采购数量； b. 行区域设置为“产品”； c. 列区域设置为“商标”； d. 计数项为“采购盒数”。 数据透视表如下： 计数项:采购盒数 商标 　 　 　 产品 北京 上海 (空白) 总计 白炽灯 4 5 　 9 氖管 1 1 2 其他 2 2 日光灯 　 2 2 总计 7 8 　 15 (六) 房产销售表(数组公式).xls 1. 利用公式，计算Sheet1中的房价总额。 房价总额的计算公式为：“面积*单价” =F3*G3 2. 使用数组公式，计算Sheet1中的契税总额。 契税总额的计算公式为：“契税*房价总额” {=H3:H26*I3:I26} 3. 使用函数，根据Sheet1中的结果，统计每个销售人员的销售总额，将结果保存在Sheet2中的相应的单元格中。 人员甲： =SUMIF(Sheet1!K3:K26,A2,Sheet1!I3:I26) 然后利用填充柄复制公式 4. 使用RANK函数，根据Sheet2的结果，对每个销售人员的销售情况 进行排序，并将结果保存在“排名”列当中。 人员甲： =RANK(B2,B$2:B$6) 然后利用填充柄复制公式 5. 将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选，要求： a. 筛选条件为：“户型”为两室一厅，“房价总额”>1000000； 户型 房价总额 两室一厅 >1000000 b. 将结果保存在Sheet3中。 6. 根据Sheet1的结果，创建一张数据透视图Chart1，要求； a. 显示每个销售人员销售房屋所缴纳契税总额； b. 行区域设置为“销售人员”； c. 计数项设置为契税总额； d. 将对应的数据透视表保存在Sheet4中。 数据透视图chart1如下所示: 数据透视表如下： 求和项:契税总额 　 销售人员 汇总 人员丙 199857.4008 人员丁 59564.1012 人员甲 244122.8748 人员戊 147790.5024 人员乙 86253.5637 总计 737588.4429 (七) 公务员考试成绩表(if函数).xls 1. 使用IF函数，对Sheet1中的“学位”列进行自动填充。 要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）： - 博士研究生－博士 - 硕士研究生－硕士 - 本科－学士 - 其他－无 =IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="本科","学士","无"))) 2. 使用数组公式，在Sheet1中计算： a.“笔试比例分”，计算方法为：（笔试成绩/3）*60％ {=I3:I18/3*0.6} b.“面试比例分”，计算方法为：面试成绩*40％ {=K3:K18*0.4} c.“总成绩”，计算方法为：笔试比例分+面试比例分 {=J3:J18+L3:L18} 3. 修改数组公式，将Sheet1复制到Sheet2，在Sheet2中计算： 要求：修改“笔试比例分”的计算，计算方法为：（（笔试成绩/2）*60％）。 {=I3:I18/2*0.6} 4. 在 Sheet2中，添加一列，将其命名为“排名”。 要求：使用RANK函数，根据“总成绩”对所有考生排名。 =RANK(M3,M$3:M$18) 5. 将Sheet2复制到Sheet3，并对Sheet3进行高级筛选。 a. 筛选条件为：“报考单位”－中院、“性别”－男、“学历”－硕士研究生 报考单位 性别 学历 一中院 男 硕士研究生 三中院 男 硕士研究生 b. 将筛选结果保存在Sheet3中 6. 根据Sheet2，在Sheet4中新建一数据透视表。要求： a. 显示每个报考单位的人的不同学历的总人数 b. 行区域设置为“报考单位” c. 列区域设置为“学历” d. 数据区域设置为“学历” e. 计数项为学历 数据透视表如下： 计数项:学历 学历 　 　 　 　 报考单位 本科 博士研究生 大专 硕士研究生 总计 区法院 3 　 1 　 4 三中院 3 3 市高院 1 1 1 2 5 一中院 　 1 3 4 总计 7 2 2 5 16 (八) 员工姓名(REPLACE函数).xls 1. 使用REPLACE函数，对Sheet1中的员工代码进行升级，要求： a. 升级方法：在PA后面加上0； b. 将升级后的员工代码结果填入表中的“升级员工代码”列中。 =REPLACE(B2,3,4,"0"&RIGHT(B2,3)) 2. 使用时间函数，对Sheet1员工的“年龄”和“工龄”进行计算，并将结果填入到表中的“年龄”列和“工龄”列中。 年龄：=YEAR(TODAY())-YEAR(E2) 工龄：=YEAR(TODAY())-YEAR(G2) 3. 使用统计函数，对Sheet1中的数据，根据以下统计条件进行如下统计。 a. 统计男性员工的人数，结果填入N3单元格中；COUNTIF(D2:D65,"=男") b. 统计高级工程师人数，结果填入N4单元格中；COUNTIF(I2:I65,"=高级工程师") c. 统计工龄大于等于10的人数，果填入N5单元格中。COUNTIF(H2:H65,">=10") 4. 使用逻辑函数，判断员工是否有资格评“高级工程师”。 评选条件为：工龄大于20，且为工程师的员工。 =IF(I2="工程师",IF(H2>20,TRUE,FALSE),FALSE) =IF(AND(H2>20,I2="工程师"),"是","否") 5. 将Sheet1复制到Sheet2中，并对Sheet2进行高级筛选，要求： a. 筛选条件为：“性别”－男，“年龄”>30，“工龄”>＝10，“职称”－助工； 性别 年龄 工龄 职称 男 >30 >=10 助工 b. 将结果保存在Sheet2中。 6. 根据Sheet1中的数据，创建一张数据透视图Chart1，要求： a. 显示工厂中各个职称的人数； b. x坐标设置为“职称”； c. 计数项为职称； d. 将对应的数据透视表保存在Sheet3中。 数据透视图chart1如下： 数据透视表如下： 计数项:职称 　 职称 汇总 高级工 2 高级工程师 14 工程师 11 技工 5 技师 1 技术员 11 中级工 1 助工 19 总计 64 (九) 停车价目表(HLOOKUP函数).xls 1. 使用HLOOKUP函数，对Sheet1中的停车单价进行自动填充。 要求：根据Sheet1中的“停车价目表”价格，利用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。 =HLOOKUP(B9,A$2:C$3,2,FALSE) 或者用数组公式： =HLOOKUP($B$9:$B$39,$A$2:$C$3,2,0) 2. 在Sheet1中，利用时间函数计算汽车在停车库中的停放时间，要求： a. 公式计算方法为“出库时间-入库时间” b. 格式为：“小时：分钟：秒” （例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”） =E9-D9 3. 使用函数公式，计算停车费用，要求： 根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。 注意： a. 停车按小时收费，对于不满一个小时的按照一个小时计费； b. 对于超过整点小时数十五分钟的多累积一个小时。 （例如1小时23分，将以2小时计费） =IF(AND(HOUR(F9)=0,MINUTE(F9)>0),1*C9,IF(MINUTE(F9)>15,(HOUR(F9)+1)*C9,HOUR(F9)*C9)) 4. 使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计，要求： a. 统计停车费用大于等于40元的停车记录条数 =COUNTIF(G9:G39,">=40") b. 统计最高的停车费用 = MAX(G9:G39) 5. 对Sheet2，进行高级筛选，要求： a. 筛选条件为：“车型”－小汽车，“应付金额”>＝30； b. 将结果保存在Sheet2中。 车型 应付金额 小汽车 >=30 6. 根据Sheet1，创建一个数据透视图Chart1，要求： a. 显示各种车型所收费用的汇总； b. 行区域设置为“车型”； c. 计数项为“应付金额”； d. 将对应的数据透视表保存在Sheet3中。 数据透视图chart1如下： 数据透视表如下： 求和项:应付金额 　 车型 汇总 大客车 200 小汽车 155 中客车 264 总计 619 (十) 平均气温日期(if函数).xls 1. 使用IF函数，对Sheet1中的“温度较高的城市”列进行自动填充。 =IF(B2>C2,"杭州","上海") 2. 使用数组公式，对Sheet1中的相差温度值（杭州相对于上海的温差）进行填充。 {=B2:B16-C2:C16} 3. 利用函数，根据Sheet1中的结果，符合以下条件的进行统计。 a. 杭州这半个月以来的最高气温和最低气温；=MAX(B2:B16) =MIN(B3:B17) b. 上海这半个月以来的最高气温和最低气温。=MAX(C2:C16) = MIN(C3:C17) 4. 将Sheet1复制到Sheet2中，在Sheet2中，重新编辑数组公式，将Sheet2中的“相差的温度值”中的数值取其绝对值（均为正数）。 {=ABS(B2:B16-C2:C16)} 5. 将Sheet2复制到Sheet3中，并对Sheet3进行高级筛选，要求： 筛选条件：“杭州平均气温”>=20，“上海平均气温”<20 杭州平均气温 上海平均气温 >=20 <20 6. 根据Sheet1中的结果，在sheet4中创建一张数据透视表，要求： a. 显示杭州气温高于上海气温的天数和上海气温高于杭州气温的天数； b. 行区域设置为“温度较高的城市”； c. 计数项设置为温度较高的城市。 数据透视表如下； 计数项:温度较高的城市 　 温度较高的城市 汇总 杭州 11 上海 4 总计 15 (十一) 学生成绩表.xls 1. 使用REPLACE函数，将Sheet1中“学生成绩表”的学生学号进行更改，并将更改的学号填入到“新学号”列中，学号更改的方法为：在原学号的前面加上“2009”。 例如："001" -> "2009001" =REPLACE(A3,1,0,"2009") 2. 使用数组公式，对Sheet1中“学生成绩表”的“总分”列进行计算。 计算方法：总分 =语文 + 数学 + 英语 + 信息技术 + 体育 {=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24} 3. 使用IF函数，根据以下条件，对Sheet1中“学生成绩表”的“考评”列进行计算。 条件：如果总分>=350，填充为“合格”；否则，填充为“不合格”。 =IF(J3>=350,"合格","不合格") 4. 在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况计算，并将结果填入到相应的单元格当中。 条件： （a）计算：“语文”和“数学”成绩都大于或等于85的学生人数； =DCOUNTA(A2:K24,B2,M2:N3) （b）计算：“体育”成绩大于或等于90的“女生”姓名； =DGET(A2:K24,C2,M7:N8) （c）计算：“体育”成绩中男生的平均分； =DAVERAGE(A2:K24,I2,M12:M13) （d）计算：“体育”成绩中男生的最高分。 =DMAX(A2:K24,I2,M12:M13) 5. 将Sheet1中的“学生成绩表”复制到Sheet2当中，并对Sheet2进行高级筛选。 要求： （a）筛选条件为：“性别” - 男；“英语” - >90；“信息技术” - >95； （b）将筛选结果保存在Sheet2中。 性别 英语 信息技术 男 >90 >95 注意： （a）无需考虑是否删除或移动筛选条件; （b）复制过程中，将标题项“学生成绩表”连同数据一同复制; （c）复制数据表后，粘贴时，数据表必须顶格放置。 6. 根据Sheet1中“学生成绩表”，在Sheet3中新建一张数据透视表。 要求： （a）显示不同性别、不同考评结果的学生人数情况； （b）行区域设置为“性别”； （c）列区域设置为“考评”； （d）数据区域设置为“考评”； （e）计数项为“考评”。 数据透视表； 计数项:考评 考评 　 　 性别 不合格 合格 总计 男 3 12 15 女 1 6 7 总计 4 18 22 (十二) 销售统计表.xls 1. 使用VLOOKUP函数， 对Sheet1中的“3月份销售统计表”的“产品名称”列和“产品单价”列进行填充。 要求：根据“企业销售产品清单”，使用VLOOKUP函数，将产品名称和产品单价填充到“3月份销售统计表”的“产品名称”列和“产品单价”列中。 “产品名称”列；=VLOOKUP(F3,$A$2:$C$10,2,FALSE) “产品单价”列：=VLOOKUP(F3,$A$2:$C$10,3,FALSE) 2. 使用数组公式，计算Sheet1中的“3月份销售统计表”中的销售金额，并将结果填入到该表的“销售金额”列中。计算方法：销售金额 = 产品单价 * 销售数量 {=H3:H44*I3:I44} 3. 使用统计函数，根据“3月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入该表的“总销售额”列。 =SUMIF($K$3:$K$44,N3,$L$3:$L$44) 4. 在Sheet1中，使用RANK函数，在“分部销售业绩统计”表中，根据“总销售额”对各部门进行排名，并将结果填入到“销售排名”列中。 =RANK(O3,$O$3:$O$5) 5. 将sheet1中的“三月份销售统计表”复制到Sheet2中，对Sheet2进行高级筛选。 要求： （a）筛选条件为：“销售数量”－>3、“所属部门”－市场1部、“销售金额”->1000 （b）将筛选结果保存在Sheet2中。 销售数量 所属部门 销售金额 >3 市场1部 >1000 注意： （a）无需考虑是否删除或移动筛选条件; （b）复制过程中，将标题项“三月份销售统计表”连同数据一同复制; （c）复制数据表后，粘贴时，数据表必须顶格放置。 6. 根据Sheet1的“3月份销售统计表”中的数据，新建一个数据透视图Chart1。 要求： （a）该图形显示每位经办人的总销售额情况 （b）x坐标设置为“经办人”; （c）数据区域设置为“销售金额”; （d）求和项为销售金额; （e）将对应的数据透视表保存在Sheet3中。 数据透视图Chart1： 数据透视表： 求和项:销售金额 　 经办人 汇总 甘倩琦 5752 李成蹊 4416 刘 惠 18232 孙国成 6920 王 勇 3976 吴 仕 12116 吴小平 3424 许 丹 11352 赵 荣 6436 总计 72624 (十三) 学生成绩表.xls 1. 使用数组公式，根据Sheet1中“学生成绩表”的数据，计算考试总分， 并将结果填入到“总分”列中。 计算方法：总分 = 单选题 + 判断题 + windows操作题 + Excel操作题 + PowerPoint操作题 + IE操作题 {=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I57} 2. 使用文本函数中的一个函数，在Sheet1中，利用“学号”列的数据，根据以下要求获得考生所考级别，并将结果填入“级别”列中。 要求： （a）学号中的第八位指示的考生所考级别，例如：“085200821023080”中的“2”标识了该考生所考级别为二级 （b）在“级别”列中，填入的数据是函数的返回值。 =MID(A3,8,1) 3. 使用统计函数，根据以下要求对Sheet1中“学生成绩表”的数据进行统计。 要求： （a）统计“考1级的考生人数”，并将计算结果填入到N2单元格中； =COUNTIF(C3:C57,"1") （b）统计“考试通过人数（>=60）”,并将计算结果填入到N3单元格中； =COUNTIF(J3:J57,">=60") （c）统计“全体1级考生的考试平均分”，并将计算结果填入到N4单元格中。（其中，计算时候的分母直接使用“N2”单元格的数据） =SUMIF(C3:C57,"1",J3:J57)/N2 4. 使用财务函数，根据以下要求对Sheet2中的数据进行计算。 要求： （a）根据“投资情况表1”中的数据，计算10年以后得到的金额，并将结果填入到B7单元格中； =FV(B3,B5,B4,B2) （b）根据“投资情况表2”中的数据，计算预计投资金额，并将结果填入到E7单元格中。 =PV(E3,E4,E2) 5. 将Sheet1中的“学生成绩表”复制到Sheet3，并对Sheet3进行高级筛选。 要求： （a）筛选条件为：“级别”－2、“总分”－>=70 ； （b）将筛选结果保存在Sheet3中。 级别 总分 2 >=70 注意： （a）无需考虑是否删除或移动筛选条件; （b）复制过程中，将标题项“学生成绩表”连同数据一同复制; （c）复制数据表后，粘贴时，数据表必须顶格放置。 6. 根据Sheet1中的“学生成绩表”，在Sheet4中新建一张数据透视表。 要求： （a）显示每个级别不同总分的人数汇总情况； （b）行区域设置为“级别”； （c）列区域设置为“总分”； （d）数据区域设置为“总分”； （e）计数项为总分。 计数项:总分 总分 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 级别 39 45 48 49 51 52 55 57 58 60 62 63 64 65 66 67 68 69 70 71 72 73 74 75 78 82 83 93 总计 1 1 1 　 1 2 1 　 1 1 1 1 1 3 1 3 2 1 1 　 1 3 　 3 1 2 1 1 　 34 2 　 1 1 1 1 2 3 1 1 11 3 1 1 1 1 2 1 1 1 1 10 总计 2 1 1 1 2 1 1 1 1 1 3 3 4 1 5 2 3 4 1 1 4 1 4 2 2 1 1 1 55 二、AOA Excel 用到的函数参考 1．排名函数：RANK（被排名的单元，排名的范围_绝对引用，0降序、非0 升序） RANK(G2,$G$2:$G$39,0) 2．判断函数：IF（条件，真取值，假取值） 3． 数据库计数函数：DCOUNT（数据库范围，被计数列，放条件的区域） DCOUNT(Sheet1!A1:I39,Sheet1!D1,B10:C11) 4．纵向对照表查找并填值函数：VLOOKUP（被对照的区域，对照的表格区域，填对照表格2列，0） VLOOKUP($A$11:$A$43,$F$2:$G$4,2,0) 5. 横向对照表查找并填值函数：HLOOKUP（被对照的区域，对照的表格区域，填对照表格2行，0） HLOOKUP($B$9:$B$39,$A$2:$C$3,2,0) 6. 条件求和函数：SUMIF(放各种品种的区域，指定求和的品种，被求和的区域) SUMIF(A11:A43,”=衣服“,B11:B43) 7. 取日期的年份函数：YEAR(日期) 当天日期函数：TODAY() YEAR(TODAY())-YEAR(C2) 8. 字符串替换函数：REPLACE(被替换的单元，第几个字符开始，共几个，新字符串) REPLACE(F2,5,8,"8"&RIGHT(F2,7)) 9．取字符串函数：RIGHT(被取的单元，右起取几个) LEFT(被取的单元，左起取几个) RIGHT(F2,7) 10. 条件计数函数：COUNTIF(被计数的区域范围,"条件") COUNTIF(Sheet1!$B$2:$B$37,"=男") 11.数据库中求平均值函数：DAVERAGE(数据库范围，被求平均值的列，放条件的区域) DAVERAGE(A1:G17,E1,J2:L3) 12.计数空白单元格个数函数：COUNTBLANK(被计数区域范围) COUNTBLANK(B2:E11) 13.判定是否文本单元格的函数： ISTEXT(单元格) IF(ISTEXT(C21),"TRUE","FALSE") 14.横向对照表查找并填值函数：HLOOKUP（被对照的区域，对照的表格区域，填对照表格2行，0） HLOOKUP($B$9:$B$39,$A$2:$C$3,2,0) 15.财务函数：PMT 贷了多少款，年利息是多少，贷多少年，等额分期按年偿还贷款金额（年末） 功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额 格式：PMT(rate，nper，pv，fv，type) rate：贷款利率（年利息） nper：该项贷款的总贷款期限或者总投资期（贷款年限） pv：从该项贷款（或投资）开始计算时已经入账的款项（贷款金额） fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末 例：按年偿还贷款金额（年末） =PMT(B4,B3,B2,0,0) A B 1 贷款情况 2 贷款金额： 1000000 3 贷款年限： 15 4 年利息： 4.98% 16. 财务函数：IPMT 贷了多少款，年利息是多少，贷多少年，等额分期按年偿还贷款后，利息逐月减少，求某月所交的利息。 功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期限内的利息偿还额 格式：IPMT(rate，per，nper，pv，fv) rate：各期利率 （月利息，年利息/12） per：用于计算利息数额的期数，介于1～nper之间 （第9月） nper：总投资（或贷款）期，即该项投资（或贷款）的付款期总数 （年数*12月） pv：从该项投资（或贷款）开始计算时已经入账的款项（贷款金额） fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 例：第9月贷款利息金额 =IPMT(B4/12,9,B3*12,B2,0) 17．财务函数：FV 先投一笔钱，每年再投一笔钱，有年利率回报，多少年以后的总金额。 功能：基于固定利率及等额分期付款方式，返回某项投资的未来值 格式：FV (rate，nper，pmt，pv，type) rate：各期利率（年利率） nper：总投资（或贷款）期，即该项投资（或贷款）的付款期总数（再投资年限） pmt：各期所应支付的金额（每年再投资金额） pv：现值，即从该项投资开始计算时已经入账的款项，也称为本金 （先投资金额） type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末。 例：计算10年以后得到的金额： =FV(B3,B5,B4,B2,0) A B 1 投资情况表1 2 先投资金额： -1000000 3 年利率： 5% 4 每年再投资金额： -10000 5 再投资年限： 10 6 7 10年以后得到的金额： 18．财务函数：PV 每年投一笔钱，有年利率回报，多少年以后预计投资总金额。 功能：一系列未来付款的当前值的累积和，返回的是投资现值 格式：PV(rate，nper，pmt，fv，type) rate：贷款利率（年利率） nper：该项贷款的总贷款期限或者总投资期 （年限） pmt：各期所应支付的金额 （每年投资金额） fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末 例：计算预计投资金额 =PV(E3,E4,E2,0,0) D E 1 投资情况表2 2 每年投资金额： -1500000 3 年利率： 10% 4 年限： 20 5 6 7 预计投资金额： 三、AOA Excel 考试题用到的数据参考 1． 数组公式：{ 数组包含数个单元格，这些单元格形成一个整体范围，对应同一个不变公式运算 } 与填充柄的区别是：填充柄的公式随着单元格的变化而变化。 2. 分类汇总：按不同的项目汇总 ①对分类汇总的列按项目先排序。 ②“分类汇总”，分类汇总对话框，安要求填写：分类字段、汇总方式、选定汇总项。 ③单击对话框中的“全部删除”可恢复成汇总前的原始数据；以便高级筛选和数据透视表用。 3. 高级筛选：筛选条件较多的情况 ①先要建立一个条件区域，用来指定筛选条件（条件区域如何做？）。 ②选定被筛选的数据列表区域。 ③套中条件区域。 4. 数据透视表和数据透视图：用“数据透视表和数据透视图向导” ①选择所创建的数据透视表的数据源类型（会自动出默认）。下一步 ②选择数据源的区域，包括那张表sheet？（没有汇总过的）。 ③“布局”将要生成的数据透视表的版式和选项。 1 PAGE 16