省二级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")
数学分数位于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