浙江省计算机二级高级办公软件应用AOA教程全图文并茂就算菜鸟也看得懂课件.docx

浙江省计算机二级高级办公软件应用AOA教程全图文并茂就算菜鸟也看得懂课件.docx

《浙江省计算机二级高级办公软件应用AOA教程全图文并茂就算菜鸟也看得懂课件.docx》由会员分享，可在线阅读，更多相关《浙江省计算机二级高级办公软件应用AOA教程全图文并茂就算菜鸟也看得懂课件.docx（38页珍藏版）》请在冰豆网上搜索。

浙江省计算机二级高级办公软件应用AOA教程全图文并茂就算菜鸟也看得懂课件

省二级AOA考试宝典

一、AOAExcel考试题（17题对照）参考

（一）、三科成绩（数组公式if）.xls

1.使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。

（1）鼠标套中[总分]全列（F2到F39）

（2）在编辑栏变成：

=C2:

C39+D2:

D39+E2:

E39（会吗？

记住）

（3）按Ctrl+Shift+Enter变成{=C2:

C39+D2:

D39+E2:

E39}

（4）“总分”全部自动出来。

同理做“平均分”{=F2:

F39/3}

2.使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。

函数RANK的用法：

RANK（排名次的数据依据单元，排名的范围——绝对引用，0（或缺省）/非0）

说明：

0（或缺省）以所在单元的名次是降序的位数排名，否则升序。

=RANK（F2,$F$2:

$F$39,0）

3.使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为FALSE

将结果保存在表中的“三科成绩是否均超过平均”列当中。

（1）Excel中有关逻辑函数

1.AND（X条件，Y条件，Z条件，……）都成立（TRUE），则最终成立。

2.OR（X条件，Y条件，Z条件，……）有一成立，则最终成立。

3.NOT（条件）反之！

（2）IF函数：

IF（判别条件，条件成立（真）时取值，条件不成立（假）时取值）

例：

=IF（C5（成绩）<60，“不及格”，“及格”）

（3）AVERAGE平均值函数

AVERAGE（被求平均值的区域）

=IF（AND（C2>AVERAGE（$C$2:

$C$39）,D2>AVERAGE（$D$2:

$D$39）,E2>AVERAGE（$E$2:

$E$39））,TRUE,FALSE）

4.根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2中的相应位置。

条件统计个数的函数COUNTIF

COUNTIF（被统计个数的区域之绝对引用，条件）

数学分数位于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"）

等等

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

a.筛选条件：

“语文”>＝75，“数学”>＝75，“英语”>＝75，“总分”>＝250；

b.将结果保存在Sheet3中。

（1）将Sheet1复制到Sheet3中

（2）在Sheet3的空白处，自己先建如下条件

语文

数学

英语

总分

>=75

>=75

>=75

>=250

（3）选中Sheet3中的表格全部。

（4）点[数据]→[筛选]→[高级筛选]

（5）如下图：

（6）选中条件区域，成为如下图：

确定！

注：

（a）无需考虑是否删除或移动筛选条件;

（b）复制数据表后，粘贴时，数据表必须顶格放置。

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

a.显示是否三科均超过平均分的学生人数；

b.行区域设置为：

“三科成绩是否均超过平均”；

c.计数项为三科成绩是否均超过平均。

数据透视表：

用“数据透视表和数据透视图向导”

选中Sheet1的整个表：

[数据]→[数据透视表和数据透视图P..]

①选择所创建的数据透视表的数据源类型（会自动出默认）。

下一步

②选择数据源的区域，包括那张表sheet？

（没有汇总过的）。

下一步

③“布局”设计将要生成的数据透视表的版式和选项。

④在以下的图中，按题目要求进行拖放（搞错可拖回去）

（二）、服装采购（VLOOKUP函数）.xls

1.使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。

要求：

根据“价格表”中的商品单价，利用VLOOKUP函数，

将其单价自动填充到采购表中的“单价”列中。

（1）VLOOKUP叫引用函数；一个数组或表格②table_array的最左列中含有特定值的字段，到另一数据表格、数组或数据库①look_value去查找，找到同值时，把②的第col_index_num列的对应值填到①的某一指定单元格中。

VLOOKUP（lookup_value，talbe_array，col_index_num，range_lookup）

look_value：

被查找的列的值，如A11,A12,……会相对下去

table_array：

引用的数据表格、数组或数据库，如$F$2:

$G$4绝对引用。

col_index_num：

一个数字，代表要返回的值位于table_array中的第几列。

rang_lookup：

一个逻辑值，如果其值为TRUE或被省略，则符合多少算多少；如果该值为FALSE时，函数只会查找完全符合的数值，如果找不到，则返回错误值“＃N/A”。

=VLOOKUP（A11,$F$2:

$G$5,2,FALSE）

Look表

A

B

C

D

项目

……

……

单价

……

……

11

衣服

12

裤子

鞋子

衣服

裤子

鞋子

衣服

裤子

第2列

……

……

table表

F

G

1

价格表

2

项目

单价

3

衣服

120

4

裤子

80

5

鞋子

150

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中的“统计表”当中。

（1）判断条件求和函数SUMIF

SUMIF（被判断的区域，判断条件，要求和的列）

=SUMIF（A$11:

A$43,I12,B$11:

B$43）=SUMIF（各种商品，衣服，采购数量）等等，等等！

=SUMIF（A$11:

A$43,I12,F$11:

F$43）=SUMIF（各种商品，衣服，合）等等，等等！

统计表

统计类别

总采购量

总采购金额

衣服

2800

305424

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

a.筛选条件为：

“采购数量”>150，“折扣”>0；

采购数量

折扣

>150

>0

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

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

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

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

c.求和项为采购数量；

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

①选择所创建的数据透视表的数据源类型（会自动出默认）。

选：

数据透视图（及数据透视表）（R）下一步

重要：

对该题来说：

出图后在Sheet3中把“项目”拖到“汇总”

（三）、书籍出版（闰年new）（数组公式if）.xls

1.使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的“金额”列当中。

={=H2:

H51*G2:

G51}

2.使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置，要求：

a.统计出版社名称为“高等教育出版社”的书的种类数；

=COUNTIF（D2:

D51,"高等教育出版社"）

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

=COUNTIF（G2:

G51,">110"）-COUNTIF（G2:

G51,">=850"）

3.使用函数计算，每个用户所订购图书所需支付的金额总数，将结果保存在Sheet1中的相应位置。

SUMIF（A2:

A51,K8,I2:

I51）

等等，等等

4.使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。

说明：

闰年定义：

能被400整除的年份，或者年数能被4整除而不能被100整除。

取除法后得余数的函数：

MOD（被除数，除数）

方法:

IF（,"闰年","平年"）

OR（MOD（x，400）=0,）

AND（MOD（x，4）=0,MOD（x，100）<>0）

成为：

=IF（OR（MOD（A2,400）=0,AND（MOD（A2,4）=0,MOD（A2,100）<>0））,"闰年","平年"）

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

a.筛选条件为“订数>=500，且金额总数<=30000”；

订数

金额

>=500

<=30000

b.将结果保存在Sheet3中。

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

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

b.行区域设置为：

“出版社”；

c.列区域设置为：

“客户”；

d.计数项为订数。

（四）、电话升级（时间函数）.xls

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

要求：

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

（1）取年份函数：

YEAR（date）,取出date的4位年份整数。

（2）取当前日期函数：

NOW（）

=YEAR（NOW（））-YEAR（C2）

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

要求：

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

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

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

（1）字符串替换函数：

REPLACE

功能：

将某几位的文字以新的字符串替换。

其替换功能与SUBSTITUTE函数大致类似

格式：

REPLACE（old_text，start_num，num_chars，new_text）

old_text：

老的文本数据

start_num：

从第几个字符位置开始替换

num_chars：

共替换多少字符（1个汉字算2个）

new_text：

用来替换的新字符串

（2）连字符运算符：

&

（3）RIGHT（text,num_chars）根据所指定的字符数num_chars返回文本字符串text中右面最后一个或多个字符。

=REPLACE（F2,1,4,"05718"）

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

IF嵌套：

IF（判别条件，条件成立（真）时取值，条件不成立（假）时取值）

=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中。

（五）、灯泡采购（数组公式if）.xls

1.使用数组公式，计算Sheet1中的每种产品的价值，将结果保存到表中的“价值”列中。

计算价值的计算方法为：

“单价*每盒数量*采购盒数”。

{=E2:

E17*F2:

F17*G2:

G17}

2.在Sheet2中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存相应的单元格中。

数据库函数格式为：

函数名称（database，field，criteria）

database（数据库）：

包含有每列项目标题的长方形单元格区域，一般即整个工作表。

field（字段）：

指定数据库函数所作用的数据列名。

如E1（单价）

criteria（条件区域）：

一组包含给定条件的单元格区域。

如在sheet2表中自己先构建条件区间，如；

J

K

L

1

条件区域1：

2

商标

产品

瓦数

3

上海

白炽灯

<100

a.计算：

商标为上海，瓦数小于100的白炽灯的平均单价；

DAVERAGE（A1:

G17,E1,J2:

L3）

b.计算：

产品为白炽灯，其瓦数大于等于80且小于等于100的数量（采购合数）。

DSUM（A1:

H17,G1,J7:

L8）

3.某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。

对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。

根据调查情况，制做出Sheet3。

请使用函数，统计符合以下条件的数值。

a.统计未登记的部门个数；

计算空白单元格数目函数：

COUNTBLANK（range）功能：

计算某个单元格区域中空白单元格的数目。

COUNTBLANK（B2:

E11）

b.统计在登记的部门中，吸烟的部门个数。

COUNTIF（B2:

E11,"Y"）

4.使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否问（?

为）文本，如果是，结果为TRUE；如果不是，结果为FALSE，并将结果保存在Sheet3中的B22单元格当中。

函数ISTEXT（value）功能：

判定value是否为文本

ISTEXT（B21）

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

a.筛选条件：

“产品为白炽灯，商标为上海”，并将结果保存；

产品

商标

白炽灯

上海

b.将结果保存在Sheet4中。

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

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

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

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

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

（六）、房产销售（数组公式）.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中。

（七）、公务员考试（if函数）.xls

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

要求：

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

-博士研究生－博士

-硕士研究生－硕士

-本科－学士

-其他－无

IF（G3="博士研究生","博士",IF（G3="硕士研究生","硕士",IF（G3="本科","学士","无"）））

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

a.“笔试比例分”，计算方法为：

（笔试成绩/3）*60％{=I3:

I18/3*60%}

b.“面试比例分”，计算方法为：

面试成绩*40％{=K3:

K18*40%}

c.“总成绩”，计算方法为：

笔试比例分+面试比例分{=J3:

J18+L3:

L18}

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

要求：

修改“笔试比例分”的计算，计算方法为：

（（笔试成绩/2）*60％）。

{=（（I3:

I18/2）*60%）}

4.在Sheet2中，添加一列，将其命名为“排名”。

要求：

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

RANK（M3,M$3:

M$18）

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

a.筛选条件为：

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

报考单位

性别

学历

一中院

男

硕士研究生

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

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

要求：

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

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

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

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

e.计数项为学历

（八）、员工职称（REPLACE函数）.xls

1.使用REPLACE函数，对Sheet1中的员工代码进行升级，要求：

a.升级方法：

在PA后面加上0；

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

REPLACE（B2,3,0,"0"）

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

YEAR（NOW（））-YEAR（E2）YEAR（NOW（））-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）

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

a.筛选条件为：

“性别”－男，“年龄”>30，“工龄”>＝10，“职称”－助工；

性别

年龄

工龄

职称

男

>30

>=10

助工

b.将结果保存在Sheet2中。

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

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

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

c.计数项为职称；

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

（九）、停车收费（HLOOKUP函数）.xls

1.使用HLOOKUP函数，对Sheet1中的停车单价进行自动填充。

要求：

根据Sheet1中的“停车价目表”价格，利用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。

HLOOKUP（B9,A$2:

C$3,2,FALSE）………?

?

?

?

?

?

?

?

2指PAI

2.在Sheet1中，利用时间函数计算汽车在停车库中的停放时间，要求：

a.公式计算方法为“出库时间-入库时间”

b.格式为：

“小时：

分钟：

秒”

（例如：

一小时十五分十二秒在停放时间中的表示为：

“1：

15：

12”）

E9-D9

3.使用函数公式，计算停车费用，要求：

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

注意：

a.停车按小时收费，对于不满一个小时的按照一个小时计费；

b.对于超过整点小时数十五分钟的多累积一个小时。

（例如1小时23分，将以2小时计费）=IF（HOUR（F9）=0,1,IF（MINUTE（F9）>15,HOUR（F9）+1,HOUR（F9）））*C9

4.使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计，要求：

a.统计停车费用大于等于40元的停车记录条数COUNTIF（G9:

G39,">=40"）

b.统计最高的停车费用

函数MAX（range）功能：

求range内的最大值。

MAX（G9:

G39）

5.对Sheet2，进行高级筛选，要求：

a.筛选条件为：

“车型”－小汽车，“应付金额”>＝30；

b.将结果保存在Sheet2中。

车型

应付金额

小汽车

>=30

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

a.显示各种车型所收费用的汇总；

b.行区域设置为“车型”；

c.计数项为“应付金额”；

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

（十）、气温比较（if函数）.xls

1.使用IF函数，对Sheet1中的“温度较高的城市”列进行自动填充。

IF（B2>=C2,"杭州","上海"）

2.使用数组公式，对Sheet1中的相差温度值（杭州相对于上海的温差）进行填充。

{=B2:

B16-C2:

C16}

3.利用函数，根据Sheet1中的结果，符合以下条件的进行统计。

a.杭州这半个月以来的最高气温和最低气温；MAX（B2:

B16）MIN（B2:

B16）

b.上海这半个月以来的最高气温和最低气温。

MAX（C2:

C16）MIN（C2:

C16）

4.将Sheet1复制到Sheet2中，在Sheet2中，重