AOA考试宝典Excel要点Word格式文档下载.docx

AOA考试宝典Excel要点Word格式文档下载.docx

《AOA考试宝典Excel要点Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《AOA考试宝典Excel要点Word格式文档下载.docx（36页珍藏版）》请在冰点文库上搜索。

>

=0

20

=20

40

=40

60

=60

80

=80

=100

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

a.筛选条件：

“语文”>

＝75，“数学”>

＝75，“英语”>

＝75，“总分”>

＝250；

筛选的条件区间如下：

语文

英语

总分

=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）

采购总金额：

A$43,I12,F$11:

F$43）

5.对Sheet2中的“采购表”进行高级筛选。

a.筛选条件为：

“采购数量”>

150，“折扣”>

0；

采购数量

折扣

150

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

6.根据Sheet1中的采购表，新建一个数据透视图Chart1，要求：

a.该图形显示每个采购时间点所采购的所有项目数量汇总情况；

b.x坐标设置为“采购时间”；

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

透视图chat1如下所示；

Sheet3中的透视表如下所示：

求和项:

项目

采购时间

裤子

鞋子

衣服

2008-1-12

45

70

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

100

265

490

2008-7-10

400

320

845

2008-8-19

275

240

900

2008-9-27

325

360

805

2008-10-24

155

295

660

2008-11-4

160

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:

=850"

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）<

"

）））

=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.显示每个客户在每个出版社所订的教材数目；

“出版社”；

c.列区域设置为：

“客户”；

d.计数项为订数。

透视表如下：

客户

c1

c2

c3

c4

北京航大

63

北京理工

421

电子工业出版社

555

71

626

东北财经大学出版社

75

复旦大学

106

高等教育

1061

10719

高教

509

华东师大

76

科学

203

科学出版社

2940

立信会计

637

立信会计出版社

辽宁美术出版社

58

南京大学

清华大学

人民大学

721

人民卫生

366

上海外语教育出版社

500

天津人民美术出版社

外语教学与研究出版社

9855

浙江科技出版社

1504

浙江科学技术

浙江科学技术出版社

中国金融

中国金融出版社

645

中国人大

224

中国人民大学出版社

585

167

752

中国物资

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单元格中；

B2:

B37,"

b.统计年龄为“>

40”岁的用户人数，将结果填入Sheet2的B2单元格中。

D2:

D37,"

5.将Sheet1复制到Sheet3，并对Sheet3进行高级筛选。

a.筛选条件为：

“性别”－女、“所在区域”－西湖区；

所在区域

性别

西湖区

女

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

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

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

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

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

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

透视图chart1如下所示；

数据透视表如下：

拱墅区

6

江干区

上城区

5

下城区

余杭区

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

9

氖管

1

2

其他

日光灯

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）

“户型”为两室一厅，“房价总额”>

1000000；

户型

房价总额

两室一厅

1000000

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

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

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

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

数据透视图chart1如下所示:

契税总额

销售人员

人员丙

199857.4008

人员丁

59564.1012

人员甲

244122.8748

人员戊

147790.5024

人员乙

86253.5637

737588.4429

（七）公务员考试成绩表（if函数）.xls

1.使用IF函数，对Sheet1中的“学位”列进行自动填充。

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

-博士研究生－博士

-硕士研究生－硕士

-本科－学士

-其他－无

=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进行高级筛选。

“报考单位”－中院、“性别”－男、“学历”－硕士研究生

报考单位

性别

学历

一中院

男

硕士研究生

三中院

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

6.根据Sheet2，在Sheet4中新建一数据透视表。

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

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

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

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

e.计数项为学历

本科

博士研究生

大专

区法院

3

市高院

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进行高级筛选，要求：

“性别”－男，“年龄”>

30，“工龄”>

＝10，“职称”－助工；

年龄

工龄

职称

30

=10

助工

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

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

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

c.计数项为职称；

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

数据透视图chart1如下：

高级工

高级工程师

14

工程师

技工

技师

技术员

中级工

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.使用函数公式，计算停车费用，要求：

根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。

注意：