豪放的诗句-

2021年1月26日发(作者：如此多娇吧)
省二级
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
函数，
对
Sh eet1
中的每个同学排名情况进行统计，
并将排名结果保存到表中的
“排名”
列当中。

排名：
=RANK(G2,G$$2:G$$39)
或者
=RANK(F2,F$$2:F$$39)
然后利用填充柄复制公式

3.
使用逻辑函数判断
Sheet1
中每个同学的每门功课是否均高于平均分，
如 果是，
保存结果为
TRUE
，
否则，保存结果为
FALSE
，将结果保存在表中的“三科成绩是否均超过平均”列当中。

=IF(AND(C2>AVE RAGE($$G$$2:$$G$$39),D2>AVERAGE($$G$$2:$$G$$39),E2>AVERAGE ($$G$$2:$$G$$39)),TRUE,FAL
SE)
或者：

=IF( C2>AVERAGE($$C$$2:$$C$$39),IF(D2>AVERAGE($$D$$2:$$D$$39),I F(E2>AVERAGE($$E$$2:$$E$$39),TRUE,FA
LSE),FALSE),FA LSE)
4.
根据
Sheet1
中的结果，使用统计函数，统计“数学” 考试成绩各个分数段的同学人数，将统计
结果保存到
Sheet2
中的相应位置。

数学分数位于
0
到
20
=COUNTIF(She et1!$$D$$2:$$D$$39,

分的人数：

数学分数位于
20
到
=COUNTIF(Sheet1!$$D$$2:$$D$$39,
40
分的人数 ：

20
数学分数位于
40
到
=COUNTIF(Shee t1!$$D$$2:$$D$$39,
60
分的人数：

40
数学分数位于
60
到
=COUNTIF(Sheet1!$$D$$2:$$D$$39,
80分的人数：

60
数学分数位于
80
到
=COUNTI F(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.
筛选条件：“语文”< br>>
＝
75
，“数学”
>
＝
75
，“英语”< br>>
＝
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
a.
统计出版社名称为“高等教育出版社”的书的种类数；

=COUNTIF(D2:D51,
高等教育出版社

或者：

=DCOUNT(A1:I51,7
，
K14:K15)
其中
K14:K15
为自己构建的条件区域。

b.
统计订购数量大于
110
且小于
850
的书的种类数。

=COUNTIF(G2:G51,
或者：

=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,
闰年

平年
< br>闰年

平年

或者：

=IF(OR(AND(A2/4 =TRUNC(A2/4),A2/100<>TRUNC(A2/100)),A2/400=TRUNC(A 2/400)),
闰年

平年

5.
将
Sheet1
复制到
Sheet3
中，对
Sheet3
进行高级筛选，要求：
a.
筛选条件为“订数
>=500
，且金额总数
<=300 00
”；

订数

>=500
金额

<=30000
b.
将结果保存在
Sheet2
中。

6.
根据
Sh eet1
中的结果，在
Sheet4
中新建一张数据透视表，要求：

a.
显示每个客户在每个出版社所订的教材数目；

b.
行区域设置为：“出版社”；

c.
列区域设置为：“客户”；

d.
计数项为订数。

透视表如下：

求和项
:
订数

出版社

北京航大

北京理工

电子工业出版社

东北财经大学出版社

复旦大学

高等教育

高等教育出版社

高教

华东师大

科学

科学出版社


客户

c1




555






10719






2940


c2












c3



c4
63


71

75




76

203


106



509




总计

63
421
421
626
75
106
1061
1061
10719
509
76
203
2940
4
立信会计

立信会计出版社

辽宁美术出版社

南京大学

清华大学

人民大学

人民卫生

上海外语教育出版社

天津人民美术出版社

外语教学与研究出版社

浙江科技出版社

浙江科学技术

浙江科学技术出版社

中国金融

中国金融出版社

中国人大

中国人民大学出版社

中国物资

总计

















500




80

58




366


58



106



645

240

120

721
















9855
1504


500


160






585



109
27158
1965
167



1968
637
80
58
240
120
721
366
500
58
9855
1504
106
500
160
645
224
224
752
109
2343
33434
637
(
四
)
姓名
(
时间函数
).xls

1.
使用时间函数，对
Sheet1
中用户的年龄进行计算。


要求：计算用户的年龄，并将其计算结果填充到“年龄”列当中。

=YEAR(NOW())-YEAR(C2)
或者：

=YEAR(TODAY())-YEAR(C2)

2.
使用
R EPLACE
函数，对
Sheet1
中用户的电话号码进行升级。


要求：对“原电话号码”列中的电话号码进行升级。


升级方法是在区号（
0571
）后面加上“
8
”，


并将其计算结果保存在“升级电话号码”列的相应单元格中。

=REPLACE(F2,1,4,
或者：

=REPLACE(F2,5,8,
（注意；先设单元格格式设为常规，再用公式。）

3.
使用逻辑函数，
判断
Sheet1
中的
“大于等于< br>40
岁的男性”
，
将结果保存在
Sheet1
中的
“ 是否
>=40
男性”。

=IF(D2>=40,IF(B2=
男

4.
对
Shee t1
中的数据，根据以下条件
,
利用函数进行统计：

a.
统计性别为“男”的用户人数，将结果填入
Sheet2
的
B1
单元格中；

=COUNTIF(Sheet1!B2:B37,
男

b. 统计年龄为“
>40
”岁的用户人数，将结果填入
Sheet2
的
B2
单元格中。
=COUNTIF(Sheet1!D2:D37,
5.
将
Sheet1
复制到
Sheet3
，并对
Sheet3
进 行高级筛选。

a.
筛选条件为：“性别”－女、“所在区域”－西湖区；


5
所在区域

性

别

西湖区

女

b.
将筛选结果保存在
Sheet3
中。

6.
根据
Sheet1
的结果，创建一数据透视图
Chart1< br>，要求：

a.
显示每个区域所拥有的用户数量；

b. x
坐标设置为“所在区域”；

c.
计数项为“所在区域”；

d.
将对应的数据透视表保存在
Sheet4
中。


透视图
chart1
如下所示；

数据透视表如下：

计数项
:
所在区域



所在区域

汇总

拱墅区

6
江干区

6
上城区

5
西湖区

6
下城区

6
余杭区

7
总计

36



6
(
五
)
产品
(
数组公式
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,

4.
使用函数，对
Shee t3
中的
B21
单元格中的内容进行判断，判断其是否问文本，如果是，结果为
“
TRUE
”；如果不是，结果为“
FALSE
”，并将结果保存在
Sheet3
中的
B22
单元格当中。

=ISTEXT(B21)
5.
将
Sheet1
复制到
Sheet4
中，对
Sheet4
进行高级筛选，要求：

a.
筛选条件：“产品为白炽灯，商标为上海”，并将结果保存；

产品

商标

白炽灯

上海

b.
将结果保存在
Sheet4
中。

6.
根据
Sh eet1
的结果，在
Sheet5
中创建一张数据透视表，要求：

a.
显示不同商标的不同产品的采购数量；

b.
行区域设置为“产品”；

c.
列区域设置为“商标”；

d.
计数项为“采购盒数”。

数据透视表如下：

计数项
:
采购盒数

商标

产品

白炽灯

氖管

其他

日光灯

总计


北京



上海

4
1
2

5
1
2
8




7


总
(
空白
)
计



9

2

2

2


15

(
六
)
房产销售表
(
数组公式
).xls
1.
利用公式，计算
Sheet1
中的房价总额。

房价总额的计算公式为：“面积
*
单价”

=F3*G3

7
2.
使用数组公式，计算
Sheet1
中的契税总额。


契税总额的计算公式为：“契税
*
房价总额”

{=H3:H26*I3:I26}
3.
使用函数，根据
Sheet1< br>中的结果，统计每个销售人员的销售总额，将结果保存在
Sheet2
中的相
应 的单元格中。

人员甲：

=SUMIF(Sheet1!K3:K26,A2,Sheet1!I3:I26)
然后利用填充柄复制公式

4.
使用
RANK
函数，根据
Sheet2
的结果，对每个销售人员的销售情况

进行排序，并将结果保存
在“排名”列当中。

人员甲：

=RANK(B2,B$$2:B$$6)
然后利用填充柄复制公式

5. < br>将
Sheet1
复制到
Sheet3
中，并对
Sheet3< br>进行高级筛选，要求：

a.
筛选条件为：“户型”为两室一厅，“房价总额”
>1000000
；

户型

两室一厅

房价总额

>1000000
b.
将结果保存在
Sheet3
中。

6. < br>根据
Sheet1
的结果，创建一张数据透视图
Chart1
，要求；

a.
显示每个销售人员销售房屋所缴纳契税总额；

b.
行区域设置为“销售人员”；

c.
计数项设置为契税总额；

d.
将对应的数据透视表保存在
Sheet4
中。

数据透视图
chart1
如下所示
:

数据透视表如下：

求和项
:
契税总额



销售人员

汇总


8
人员丙

人员丁

人员甲

人员戊

人员乙

总计


199857.4008
59564.1012
244122.8748
147790.5024
86253.5637
737588.4429
(
七
)
公务员考试成绩表
(if
函数
).xls
1.
使用IF
函数，对
Sheet1
中的“学位”列进行自动填充。


要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）：

-
博士研究生－博士

-
硕士研究生－硕士

-
本科－学士

-
其他－无
< br>=IF(G3=
博士研究生

博士

硕士研究生

硕士

本科

学士

无

2.
使用数组公式，在
Sheet1
中计算：

a.
“ 笔试比例分”，计算方法为：（笔试成绩
/3
）
*60
％

{=I3:I18/3*0.6}
b.
“面试比例分”，计算方法为：面试成绩
*40
％

{=K3:K18*0.4}
c.
“总成绩”，计算方法为：笔试比例分
+
面试比例分

{=J3:J18+L3:L18}

3.
修改数组公式，将
Sh eet1
复制到
Sheet2
，在
Sheet2
中计算：

要求：修改“笔试比例分”的计算，计算方法为：（（笔试成绩
/2
）
*60
％）。

{=I3:I18/2*0.6}
4.
在
Sheet2
中，添加一列，将其命名为“排名”。

要求：使用
RANK
函数，根据“总成绩”对所有考生排名。

=RANK(M3,M$$3:M$$18)

5.
将
Sheet2< br>复制到
Sheet3
，并对
Sheet3
进行高级筛选。

a.
筛选条件为：“报考单位”－中院、“性别”－男、“学历”－硕士研究生

报考单位

性别

一中院

三中院

男

男

学历

硕士研究生

硕士研究生

b.
将筛选结果保存在
Sheet3
中

6.
根据
S heet2
，在
Sheet4
中新建一数据透视表。要求：

a.
显示每个报考单位的人的不同学历的总人数

b.
行区域设置为“报考单位”

c.
列区域设置为“学历”

d.
数据区域设置为“学历”

e.
计数项为学历

数据透视表如下：

计数项
:
学历

学历

报考单位

本科

区法院

三中院

市高院

一中院



总计


3
3
1
7


博士研究生




1
1
2


大专







硕士研究生

总计

1


4

3
1
2
5
3
4
2
5
16
9

(
八
)
员工姓名
(REPLACE
函数
).xls
1.
使用< br>REPLACE
函数，对
Sheet1
中的员工代码进行升级，要求：

a.
升级方法：在
PA
后面加上
0
；

b.
将升级后的员工代码结果填入表中的“升级员工代码”列中。

=REPLACE(B2,3,4,

2.
使用时间函数，对
Sh eet1
员工的“年龄”和“工龄”进行计算，并将结果填入到表中的“年龄”
列和“工龄”列 中。

年龄：
=YEAR(TODAY())-YEAR(E2)
工龄：
=YEAR(TODAY())-YEAR(G2)

3.
使用统计函数，对
Sheet1
中的数据，根据以下统计条件进行如下统计。

a.
统计男性员工的人数，结果填入
N3
单元格中；
COU NTIF(D2:D65,
男


b.
统计高级工程师人数， 结果填入
N4
单元格中；
COUNTIF(I2:I65,
高级工程师


c.
统计工龄大于等于
10
的人数，果填入
N 5
单元格中。
COUNTIF(H2:H65,

4.
使用逻辑函数，判断员工是否有资格评“高级工程师”。


评选条件为：工龄大于
20
，且为工程师的员工。

=IF(I2=
工程师

=IF(AND(H2>20,I2=
工程师

是
否


5.
将
Sheet1
复制到
S heet2
中，并对
Sheet2
进行高级筛选，要求：

a.
筛选条件为：“性别”－男，“年龄”
>30
，“工龄”
>
＝
10
，“职称”－助工；

性别

男

年龄

>30
工龄

>=10
职称

助工

b.
将结果保存在
Sheet2
中。

6.
根据
Sh eet1
中的数据，创建一张数据透视图
Chart1
，要求：

a.
显示工厂中各个职称的人数；

b. x
坐标设置为“职称”；

c.
计数项为职称；

d.
将对应的数据透视表保存在
Sheet3
中。

数据透视图
chart1
如下：


10

豪放的诗句-

豪放的诗句-

豪放的诗句-

豪放的诗句-

豪放的诗句-

豪放的诗句-

豪放的诗句-

豪放的诗句-