Excel例题及常用函数.docx
- 文档编号:4341206
- 上传时间:2022-11-30
- 格式:DOCX
- 页数:12
- 大小:20.50KB
Excel例题及常用函数.docx
《Excel例题及常用函数.docx》由会员分享,可在线阅读,更多相关《Excel例题及常用函数.docx(12页珍藏版)》请在冰豆网上搜索。
Excel例题及常用函数
Exce
l考试题
(一)、学号(数组公式if).xls
1.使用数组公式,对Sheet1计算总分和平均分,将其计算结果保存到表中的“总分”列和“平均分”列当中。
{=C2:
C39+D2:
D39+E2:
E39} {=F2:
F39/3}
2.使用RANK函数,对Sheet1中的每个同学排名情况进行统计,并将排名结果保存到表中的“排名”列当中。
RANK(G2,$G$2:
$G$39,0)
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")
4.根据Sheet1中的结果,使用统计函数,统计“数学”考试成绩各个 分数段的同学人数,将统计结果保存到Sheet2中的相应位置。
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中。
6.根据Sheet1中的结果,在Sheet4中创建一张数据透视表,要求:
a.显示是否三科均超过平均分的学生人数;
b.行区域设置为:
“三科成绩是否均超过平均”;
c.计数项为三科成绩是否均超过平均。
(二)、折扣表(采购表)(VLOOKUP函数).xls
1.使用VLOOKUP函数,对Sheet1中的商品单价进行自动填充。
要求:
根据“价格表”中的商品单价,利用VLOOKUP函数,
将其单价自动填充到采购表中的“单价”列中。
VLOOKUP($A$11:
$A$43,$F$2:
$G$4,2,0)
2.使用逻辑函数,对Sheet1中的商品折扣率进行自动填充。
要求:
根据“折扣表”中的商品折扣率,利用相应的函数,将其折扣率自动填充到采购表中的“折扣“列中。
#
IF(B11>=$A$6,10%,IF(B11>=$A$5,8%,IF(B11>=$A$4,6%,0)))
3.利用公式,计算Sheet1中的“合计金额”。
要求:
根据“采购数量”,“单价”和“折扣”,计算采购的“合计金额”。
计算公式:
单价*采购数量*(1-折扣)
4.使用SUMIF函数,统计各种商品的采购总量和采购总金额,将结果保存在Sheet1中的“统计表”当中。
SUMIF(A11:
A43,F2,B11:
B43) SUMIF(A11:
A43,F2,F11:
F43)
5.对Sheet2中的“采购表”进行高级筛选。
a.筛选条件为:
“采购数量”>150,“折扣”>0;
采购数量
折扣
>150
>0
b.将筛选结果保存在Sheet2中。
6.根据Sheet1中的采购表,新建一个数据透视图Chart1,要求:
a.该图形显示每个采购时间点所采购的所有项目数量汇总情况;
b.x坐标设置为“采购时间”;
c.将对应的数据透视表保存在Sheet3中。
(三)、客户(教材)(数组公式if).xls
1.使用数组公式,计算Sheet1中的订购金额,将结果保存到表中的 “金额”列当中。
{=G2:
G51*H2:
H51}
2.使用统计函数,对Sheet1中结果按以下条件进行统计,并将结果保存在Sheet1中的相应位置,要求:
a.统计出版社名称为“高等教育出版社”的书的种类数;
DCOUNT(A1:
I51,7,K14:
K15)
b.统计订购数量大于110且小于850的书的种类数。
DCOUNT(A1:
I51,7,L14:
M15)
出版社
订数
订数
高等教育出版社
>110
<850
3.使用函数计算,每个用户所订购图书所需支付的金额总数,将结果保存在Sheet1中的相应位置。
SUMIF($A$2:
$A$51,"=c1",$I$2:
$I$51)
4.使用函数,判断Sheet2中的年份是否为闰年,如果是,结果保存“闰年”,如果不是,则结果保存“平年”,并将结果保存在“是否为闰年”列中。
说明:
闰年定义:
年数能被4整除而不能被100整除,或者能被400整除的年份。
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.显示每个客户在每个出版社所订的教材数目;
b.行区域设置为:
“出版社”;
c.列区域设置为:
“客户”;
d.计数项为订数。
(四)、姓名(时间函数).xls
1.使用时间函数,对Sheet1中用户的年龄进行计算。
####
要求:
计算用户的年龄,并将其计算结果填充到“年龄”列当中。
YEAR(TODAY())-YEAR(C2)
2.使用REPLACE函数,对Sheet1中用户的电话号码进行升级。
要求:
对“原电话号码”列中的电话号码进行升级。
升级方法是在区号(0571)后面加上“8”,
并将其计算结果保存在“升级电话号码”列的相应单元格中。
REPLACE(F2,5,8,"8"&RIGHT(F2,7))
3.使用逻辑函数,判断Sheet1中的“大于等于40岁的男性”,将结果保存在Sheet1中的“是否>=40男性”。
IF(D2>=40,IF(B2="男","是","否"),"否")
4.对Sheet1中的数据,根据以下条件,利用函数进行统计:
a.统计性别为“男”的用户人数,将结果填入Sheet2的B1单元格中;COUNTIF(Sheet1!
$B$2:
$B$37,"=男")
b.统计年龄为“>40”岁的用户人数,将结果填入Sheet2的B2单元格中。
COUNTIF(Sheet1!
$D$2:
$D$37,">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中,利用数据库函数及已设置的条件区域,计算以下 情况的结果,并将结果保存相应的单元格中。
a.计算:
商标为上海,瓦数小于100的白炽灯的平均单价;
DAVERAGE(A1:
G17,E1,J2:
L3)
b.计算:
产品为白炽灯,其瓦数大于等于80且小于等于100的数量。
DCOUNT(A1:
G17,G1,J7:
L8)
3.某公司对各个部门员工吸烟情况进行统计,作为人力资源搭配的一个数据依据。
对于调查对象,只能回答Y(吸烟)或者N(不吸烟)。
根据调查情况,制做出Sheet3。
请使用函数,统计符合以下条件的数值。
a.统计未登记的部门个数;
COUNTBLANK(B2:
E11)
b.统计在登记的部门中,吸烟的部门个数。
COUNTIF(B2:
E11,"Y") COUNTIF(B2:
E11,"N")
4.使用函数,对Sheet3中的B21单元格中的内容进行判断,判断其是否问文本,如果是,结果为“TRUE”;如果不是,结果为“FALSE”,并将结果保存在Sheet3中的B22单元格当中。
IF(ISTEXT(C21),"TRUE","FALSE")
5.将Sheet1复制到Sheet4中,对Sheet4进行高级筛选,要求:
a.筛选条件:
“产品为白炽灯,商标为上海”,并将结果保存;
产品
商标
白炽灯
上海
b.将结果保存在Sheet4中。
6.根据Sheet1的结果,在Sheet5中创建一张数据透视表,要求:
a.显示不同商标的不同产品的采购数量;
b.行区域设置为“产品”;
c.列区域设置为“商标”;
d.计数项为“采购盒数”。
(六)、房产销售表(数组公式).xls
1.利用公式,计算Sheet1中的房价总额。
房价总额的计算公式为:
“面积*单价”
2.使用数组公式,计算Sheet1中的契税总额。
契税总额的计算公式为:
“契税*房价总额”
{=H3:
H26*I3:
I26}
3.使用函数,根据Sheet1中的结果,统计每个销售人员的销售总额,将结果保存在Sheet2中的相应的单元格中。
SUMIF(Sheet1!
K3:
K26,"人员甲",Sheet1!
I3:
I26)
4.使用RANK函数,根据Sheet2的结果,对每个销售人员的销售情况 进行排序,并将结果保存在“排名”列当中。
RANK(B2,$B$2:
$B$6,0)
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*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,0)
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,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(AND(H2>20,I2="工程师"),"是","否")
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($B$9:
$B$39,$A$2:
$C$3,2,0)
2.在Sheet1中,利用时间函数计算汽车在停车库中的停放时间,要求:
a.公式计算方法为“出库时间-入库时间”
b.格式为:
“小时:
分钟:
秒”
(例如:
一小时十五分十二秒在停放时间中的表示为:
“1:
15:
12”)
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)
5.对Sheet2,进行高级筛选,要求:
a.筛选条件为:
“车型”-小汽车,“应付金额”>=30;
b.将结果保存在Sheet2中。
车型
应付金额
小汽车
>=30
6.根据Sheet1,创建一个数据透视图Chart1,要求:
a.显示各种车型所收费用的汇总;
b.行区域设置为“车型”;
c.计数项为“应付金额”;
d.将对应的数据透视表保存在Sheet3中。
二、AOA Excel考试题用到的函数参考
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 贷了多少款,年利息是多少,贷多少年,等额分期按年偿还贷款后,利息逐月减少,求某月所交的利息。
功能:
基于固定利率及等额分期付款方式,返回投资或贷款在某一给定期限内的利息偿还额
格式:
IPMT(rate,per,nper,pv,fv)
rate:
各期利率(月利息,年利息/12)
per:
用于计算利息数额的期数,介于1~nper之间(第9月)
nper:
总投资(或贷款)期,即该项投资(或贷款)的付款期总数(年数*12月)
pv:
从该项投资(或贷款)开始计算时已经入账的款项(贷款金额)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 例题 常用 函数