浙江省计算机二级高级办公软件应用AOA教程全图文并茂就算菜鸟也看得懂课件文档格式.docx
《浙江省计算机二级高级办公软件应用AOA教程全图文并茂就算菜鸟也看得懂课件文档格式.docx》由会员分享,可在线阅读,更多相关《浙江省计算机二级高级办公软件应用AOA教程全图文并茂就算菜鸟也看得懂课件文档格式.docx(38页珍藏版)》请在冰豆网上搜索。
40"
)-COUNTIF(Sheet1!
等等
5.将Sheet1复制到Sheet3中,并对Sheet3进行高级筛选,要求:
a.筛选条件:
“语文”>
=75,“数学”>
=75,“英语”>
=75,“总分”>
=250;
b.将结果保存在Sheet3中。
(1)将Sheet1复制到Sheet3中
(2)在Sheet3的空白处,自己先建如下条件
语文
数学
英语
总分
>
=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(各种商品,衣服,采购数量)等等,等等!
A$43,I12,F$11:
F$43)=SUMIF(各种商品,衣服,合)等等,等等!
统计表
统计类别
总采购量
总采购金额
2800
305424
5.对Sheet2中的“采购表”进行高级筛选。
a.筛选条件为:
“采购数量”>
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:
=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.显示每个客户在每个出版社所订的教材数目;
“出版社”;
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(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中。
(五)、灯泡采购(数组公式if).xls
1.使用数组公式,计算Sheet1中的每种产品的价值,将结果保存到表中的“价值”列中。
计算价值的计算方法为:
“单价*每盒数量*采购盒数”。
{=E2:
E17*F2:
F17*G2:
G17}
2.在Sheet2中,利用数据库函数及已设置的条件区域,计算以下情况的结果,并将结果保存相应的单元格中。
数据库函数格式为:
函数名称(database,field,criteria)
database(数据库):
包含有每列项目标题的长方形单元格区域,一般即整个工作表。
field(字段):
指定数据库函数所作用的数据列名。
如E1(单价)
criteria(条件区域):
一组包含给定条件的单元格区域。
如在sheet2表中自己先构建条件区间,如;
J
K
L
条件区域1:
商标
产品
瓦数
上海
白炽灯
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)等等!
“户型”为两室一厅,“房价总额”>
1000000;
户型
房价总额
两室一厅
1000000
6.根据Sheet1的结果,创建一张数据透视图Chart1,要求;
a.显示每个销售人员销售房屋所缴纳契税总额;
b.行区域设置为“销售人员”;
c.计数项设置为契税总额;
(七)、公务员考试(if函数).xls
1.使用IF函数,对Sheet1中的“学位”列进行自动填充。
填充的内容根据“学历”列的内容来确定(假定学生均已获得相应学位):
-博士研究生-博士
-硕士研究生-硕士
-本科-学士
-其他-无
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进行高级筛选。
“报考单位”-中院、“性别”-男、“学历”-硕士研究生
报考单位
性别
学历
一中院
男
硕士研究生
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进行高级筛选,要求:
“性别”-男,“年龄”>
30,“工龄”>
=10,“职称”-助工;
年龄
工龄
职称
30
=10
助工
b.将结果保存在Sheet2中。
6.根据Sheet1中的数据,创建一张数据透视图Chart1,要求:
a.显示工厂中各个职称的人数;
b.x坐标设置为“职称”;
c.计数项为职称;
(九)、停车收费(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,进行高级筛选,要求:
“车型”-小汽车,“应付金额”>
=30;
车型
应付金额
小汽车
=30
6.根据Sheet1,创建一个数据透视图Chart1,要求:
a.显示各种车型所收费用的汇总;
b.行区域设置为“车型”;
c.计数项为“应付金额”;
(十)、气温比较(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中,重