省二级AOA考试 宝典
AOA Excel 考试题（对照）参考
(一) 学号(数组公式if).xls
1. 使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。
总分：{=C2:C39+D2:D39+E2:E39} 平均分：{=F2:F39/3}
（注意，题目明确要求用数组公式，就必须用数组公式，否则不得分。数组公式中的一对{}不是人为录入的，必须用Ctrl+Shift+Enter组合健输入。）
数组公式的解读：1利用F9键：在编辑中，用鼠标选中需要进行计算的某段公式，将其抹黑，然后按住F9，就得到了公式得及算结果。。。当你对公式利用F9计算结果后，返回时要按ESC或者点击编辑栏左侧的 取消 键。 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到=COUNTIF(Sheet1!$D$2:$D$39,\40分的人数： 20\数学分数位于40到=COUNTIF(Sheet1!$D$2:$D$39,\60分的人数： 40\数学分数位于60到=COUNTIF(Sheet1!$D$2:$D$39,\80分的人数： 60\数学分数位于80到=COUNTIF(Sheet1!$D$2:$D$39,\100分的人数： <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；
筛选的条件区间如下：
1
语文 >=75 数学 >=75 英语 >=75 总分 >=250 b. 将结果保存在Sheet3中。
注：（a）无需考虑是否删除或移动筛选条件;
（b）复制过程中，将标题项“学生成绩表”连同数据一同复制; （c）复制数据表后，粘贴时，数据表必须顶格放置。
6. 根据Sheet1中的结果，在Sheet4中创建一张数据透视表，要求： a. 显示是否三科均超过平均分的学生人数；
b. 行区域设置为：“三科成绩是否均超过平均”； c. 计数项为三科成绩是否均超过平均。 完成的数据透视表应如下所示。 计数项:三科成绩是否均超过平均 三科成绩是否均超过平均 FALSE TRUE 总计 汇总 27 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中。
2
透视图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中的相应位置，要求：
3