计算机三级考试宝典Excel.docx
《计算机三级考试宝典Excel.docx》由会员分享，可在线阅读，更多相关《计算机三级考试宝典Excel.docx（21页珍藏版）》请在冰豆网上搜索。
计算机三级考试宝典Excel
（一）学号（数组公式if）.xls
1.使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。
总分：
{=C2:
C39+D2:
D39+E2:
E39}平均分：
{=F2:
F39/3}
（注意，题目明确要求用数组公式，就必须用数组公式，否则不得分。
数组公式中的一对{}不是人为录入的，必须用Ctrl+Shift+Enter组合健输入。
）
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）复制数据表后，粘贴时，数据表必须顶格放置。
（二）折扣表（采购表）（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）
（三）客户（教材）（数组公式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））,"闰年","平年"）
）姓名（时间函数）.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单元格中；
=COUNTIF（Sheet1!
B2:
B37,"男"）
b.统计年龄为“>40”岁的用户人数，将结果填入Sheet2的B2单元格中。
=COUNTIF（Sheet1!
D2:
D37,">40"）
（五）产品（数组公式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）
（六）房产销售表（数组公式）.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）
然后利用填充柄复制公式
（七）公务员考试成绩表（if函数）.xls
1.使用IF函数，对Sheet1中的“学位”列进行自动填充。
要求：
填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）：
-博士研究生－博士
-硕士研究生－硕士
-本科－学士
-其他－无
=IF（G3="博士研究生","博士",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）
（八）员工姓名（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="工程师"）,"是","否"）
（九）停车价目表（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.使用函数公式，计算停车费用，要求：
根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。
注意：
a.停车按小时收费，对于不满一个小时的按照一个小时计费；
b.对于超过整点小时数十五分钟的多累积一个小时。
（例如1小时23分，将以2小时计费）
=IF（AND（HOUR（F9）=0,MINUTE（F9）>0）,1*C9,IF（MINUTE（F9）>15,（HOUR（F9）+1）*C9,HOUR（F9）*C9））
4.使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计，要求：
a.统计停车费用大于等于40元的停车记录条数=COUNTIF（G9:
G39,">=40"）
b.统计最高的停车费用=MAX（G9:
G39）
（十）平均气温日期（if函数）.xls
1.使用IF函数，对Sheet1中的“温度较高的城市”列进行自动填充。
=IF（B2>C2,"杭州","上海"）
2.使用数组公式，对Sheet1中的相差温度值（杭州相对于上海的温差）进行填充。
{=B2:
B16-C2:
C16}
3.利用函数，根据Sheet1中的结果，符合以下条件的进行统计。
a.杭州这半个月以来的最高气温和最低气温；=MAX（B2:
B16）=MIN（B3:
B17）
b.上海这半个月以来的最高气温和最低气温。
=MAX（C2:
C16）=MIN（C3:
C17）
4.将Sheet1复制到Sheet2中，在Sheet2中，重新编辑数组公式，将Sheet2中的“相差的温度值”中的数值取其绝对值（均为正数）。
{=ABS（B2:
B16-C2:
C16）}
（十一）学生成绩表.xls
1.使用REPLACE函数，将Sheet1中“学生成绩表”的学生学号进行更改，并将更改的学号填入到“新学号”列中，学号更改的方法为：
在原学号的前面加上“2009”。
例如：
"001"->"2009001"
=REPLACE（A3,1,0,"2009"）
2.使用数组公式，对Sheet1中“学生成绩表”的“总分”列进行计算。
计算方法：
总分=语文+数学+英语+信息技术+体育
{=E3:
E24+F3:
F24+G3:
G24+H3:
H24+I3:
I24}
3.使用IF函数，根据以下条件，对Sheet1中“学生成绩表”的“考评”列进行计算。
条件：
如果总分>=350，填充为“合格”；否则，填充为“不合格”。
=IF（J3>=350,"合格","不合格"）
4.在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况计算，并将结果填入到相应的单元格当中。
条件：
（a）计算：
“语文”和“数学”成绩都大于或等于85的学生人数；
=DCOUNTA（A2:
K24,B2,M2:
N3）
（b）计算：
“体育”成绩大于或等于90的“女生”姓名；
=DGET（A2:
K24,C2,M7:
N8）
（c）计算：
“体育”成绩中男生的平均分；
=DAVERAGE（A2:
K24,I2,M12:
M13）
（d）计算：
“体育”成绩中男生的最高分。
=DMAX（A2:
K24,I2,M12:
M13）
（十二）销售统计表.xls
1.使用VLOOKUP函数，对Sheet1中的“3月份销售统计表”的“产品名称”列和“产品单价”列进行填充。
要求：
根据“企业销售产品清单”，使用VLOOKUP函数，将产品名称和产品单价填充到“3月份销售统计表”的“产品名称”列和“产品单价”列中。
“产品名称”列；=VLOOKUP（F3,$A$2:
$C$10,2,FALSE）
“产品单价”列：
=VLOOKUP（F3,$A$2:
$C$10,3,FALSE）
2.使用数组公式，计算Sheet1中的“3月份销售统计表”中的销售金额，并将结果填入到该表的“销售金额”列中。
计算方法：
销售金额=产品单价*销售数量
{=H3:
H44*I3:
I44}
3.使用统计函数，根据“3月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入该表的“总销售额”列。
=SUMIF（$K$3:
$K$44,N3,$L$3:
$L$44）
4.在Sheet1中，使用RANK函数，在“分部销售业绩统计”表中，根据“总销售额”对各部门进行排名，并将结果填入到“销售排名”列中。
=RANK（O3,$O$3:
$O$5）
（十三）学生成绩表.xls
1.使用数组公式，根据Sheet1中“学生成绩表”的数据，计算考试总分，
并将结果填入到“总分”列中。
计算方法：
总分=单选题+判断题+windows操作题+Excel操作题+PowerPoint操作题+IE操作题
{=D3:
D57+E3:
E57+F3:
F57+G3:
G57+H3:
H57+I3:
I57}
2.使用文本函数中的一个函数，在Sheet1中，利用“学号”列的数据，根据以下要求获得考生所考级别，并将结果填入“级别”列中。
要求：
（a）学号中的第八位指示的考生所考级别，例如：
“085200821023080”中的“2”标识了该考生所考级别为二级
（b）在“级别”列中，填入的数据是函数的返回值。
=MID（A3,8,1）
3.使用统计函数，根据以下要求对Sheet1中“学生成绩表”的数据进行统计。
要求：
（a）统计“考1级的考生人数”，并将计算结果填入到N2单元格中；
=COUNTIF（C3:
C57,"1"）
（b）统计“考试通过人数（>=60）”,并将计算结果填入到N3单元格中；
=COUNTIF（J3:
J57,">=60"）
（c）统计“全体1级考生的考试平均分”，并将计算结果填入到N4单元格中。
（其中，计算时候的分母直接使用“N2”单元格的数据）
=SUMIF（C3:
C57,"1",J3:
J57）/N2
4.使用财务函数，根据以下要求对Sheet2中的数据进行计算。
要求：
（a）根据“投资情况表1”中的数据，计算10年以后得到的金额，并将结果填入到B7单元格中；
=FV（B3,B5,B4,B2）
（b）根据“投资情况表2”中的数据，计算预计投资金额，并将结果填入到E7单元格中。
=PV（E3,E4,E2）
二、AOAExcel考试题用到的函数参考
1．排名函数：
RANK（被排名的单元，排名的范围_绝对引用，0降序、非0升序）
RANK（G2,$G$2:
$G$39,0）
2．判断函数：
IF（条件，真取值，假取值）
3．数据库计数函数：
DCOUNT（数据库范围，被计数列，放条件的区域）
DCOUNT（Sheet1!
A1:
I39,Sheet1!
D1,B10:
C11）
4．纵向对照表查找并填值函数：
VLOOKUP（被对照的区域，对照的表格区域，填对照表格2列，0）
VLOOKUP（$A$11:
$A$43,$F$2:
$G$4,2,0）
5.横向对照表查找并填值函数：
HLOOKUP（被对照的区域，对照的表格区域，填对照表格2行，0）
HLOOKUP（$B$9:
$B$39,$A$2:
$C$3,2,0）
6.条件求和函数：
SUMIF（放各种品种的区域，指定求和的品种，被求和的区域）
SUMIF（A11:
A43,”=衣服“,B11:
B43）
7.取日期的年份函数：
YEAR（日期）当天日期函数：
TODAY（）
YEAR（TODAY（））-YEAR（C2）
8.字符串替换函数：
REPLACE（被替换的单元，第几个字符开始，共几个，新字符串）
REPLACE（F2,5,8,"8"&RIGHT（F2,7））
9．取字符串函数：
RIGHT（被取的单元，右起取几个）LEFT（被取的单元，左起取几个）
RIGHT（F2,7）
10.条件计数函数：
COUNTIF（被计数的区域范围,"条件"）
COUNTIF（Sheet1!
$B$2:
$B$37,"=男"）
11.数据库中求平均值函数：
DAVERAGE（数据库范围，被求平均值的列，放条件的区域）
DAVERAGE（A1:
G17,E1,J2:
L3）
12.计数空白单元格个数函数：
COUNTBLANK（被计数区域范围）
COUNTBLANK（B2:
E11）
13.判定是否文本单元格的函数：
ISTEXT（单元格）
IF（ISTEXT（C21）,"TRUE","FALSE"）
14.横向对照表查找并填值函数：
HLOOKUP（被对照的区域，对照的表格区域，填对照表格2行，0）
HLOOKUP（$B$9:
$B$39,$A$2:
$C$3,2,0）
15.财务函数：
PMT贷了多少款，年利息是多少，贷多少年，等额分期按年偿还贷款金额（年末）
功能：
基于固定利率及等额分期付款方式，返回贷款的每期付款额
格式：
PMT（rate，nper，pv，fv，type）
rate：
贷款利率（年利息）
nper：
该项贷款的总贷款期限或者总投资期（贷款年限）
pv：
从该项贷款（或投资）开始计算时已经入账的款项（贷款金额）
fv：
未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0
type：
一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末
例：
按年偿还贷款金额（年末）=PMT（B4,B3,B2,0,0）
A
B
1
贷款情况
2
贷款金额：
1000000
3
贷款年限：
15
4
年利息：
4.98%
16.财务函数：
IPMT贷了多少款，年利息是多少，贷多少年，等额分期按年偿还贷款后，利息逐月减
升级会员 