常用函数使用及重难点总结.docx
- 文档编号:2427449
- 上传时间:2022-10-29
- 格式:DOCX
- 页数:10
- 大小:21.26KB
常用函数使用及重难点总结.docx
《常用函数使用及重难点总结.docx》由会员分享,可在线阅读,更多相关《常用函数使用及重难点总结.docx(10页珍藏版)》请在冰豆网上搜索。
常用函数使用及重难点总结
常用函数使用及重难点总结:
一、成绩统计表
1. RANK函数(统计函数,Rank(number,ref,order),number为需要找到排位的数字;ref为数字列表数组或对数字列表的引用;order为一数字,知名排位的方式,0或省略降序排位,非0升序排位
=RANK(F2,$F$2:
$F$39,0)注意ref为绝对引用
a) 关于Rank函数中的升序排位与降序排位。
b) 算总分排名时,order应为0,即采用降序排位,数字大的排前;在体育比赛,如跑步时间上,order应为1,即采用升序排位,数字小的排前。
2. 使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分,如果是,保存结果为TRUE,否则保存结果为FALSE,将结果保存在表中的“三科成绩是否均超过平均”列当中。
=IF(AND(C10>AVERAGE($C$2:
$C$39),D10>AVERAGE($D$2:
$D$39),E10>AVERAGE($E$2:
$E$39)),"TRUE","FALSE”)
3. 3、根据Sheet1中的结果,使用统计函数,统计“数学”考试成绩各个分数段的同学人数,将统计结果保存到Sheet2中的相应位置。
a) 60~80分数段:
=COUNTIF(D2:
D39,”<80”)-COUNTIF(D2:
D39,”<60”)
b) 80~100分数段:
=COUNTIF(Sheet1!
D2:
D39,"<100")-COUNTIF(Sheet1!
D2:
D39,"<80")
4. 将Sheet1复制到Sheet3中,并对Sheet3进行高级筛选,要求:
筛选条件:
“语文”〉=75,“数学”>=75,“英语”>=75,“总分”>=250;将结果保存在Sheet3中。
在空白区域创建筛选条件,注意筛选条件如“语文“”订数“与原统计表格式保持一致。
数据—筛选—高级筛选,”列表区域“与”条件区域“的选择
二、书籍订购表:
1、 使用统计函数,对Sheet1中结果按以下条件进行统计,并将结果保存在Sheet1中的相应位置,要求:
a) 统计出版社名称为“高等教育出版社”的书的种类数;
b) 统计订购数量大于110且小于850的书的种类数。
a小题使用COUNTIF函数,Range“D3:
D52”,Criteria参数为”高等教育出版社”
b小题=COUNTIF(G3:
G52,”<850”)-COUNTIF(G3:
G52,”<=110”)
2、 使用函数计算,每个用户所订购图书所需支付的金额总数,将结果保存在Sheet1中
的相应位置。
使用SUMIF函数(数学与三角函数),Range[微软用户1]填A3:
A52,Criteria参数为”-c1”,Sum_range参数[微软用户2]为I3:
I52,以此类推
3、 使用函数,判断Sheet2中的年份是否为闰年,如果是,结果保存“闰年”,如果不是,则结果保存“平年”,并将结果保存在“是否为闰年”列中。
说明:
闰年定义:
年数能被4整除而不能被100整除,或者能被400整除的年份。
使用逻辑函数IF,输入公式
=IF(OR(AND(MOD[微软用户3](A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0),”闰年”,”平年”)
三、灯泡生产表:
1、 在Sheet2中,利用数据库函数及已设置的条件区域,计算以下情况的结果,并将结果保存相应的单元格中。
计算:
商标为上海,瓦数小于100的白炽灯的平均单价
计算:
产品为白炽灯,其瓦数大于等于80且小于等于100的数量。
=DAVERAGE(A1:
H17,E1[微软用户4],J2:
L3)
=DCOUNT(A1:
H17,,[微软用户5]J7:
L8)
2、 某公司对各个部门员工吸烟情况进行统计,作为人力资源搭配的一个数据依据。
对于调查对象,只能回答Y(吸烟)或者N(不吸烟)。
根据调查情况,制作出Sheet3。
请使用函数,统计符合以下条件的数值。
a. 统计未登记的部门个数;
b. 统计在登记的部门中,吸烟的部门个数。
=COUNTBLANK[微软用户6](B2:
E11)
=COUNTIF(B2:
E11,“Y”)
3、 使用函数,对Sheet3中的B21单元格中的内容进行判断,判断其是否问文本,如果是,结果为“TRUE”;如果不是,结果为“FALSE”,并将结果保存在Sheet3中的B22单元格中。
=ISTEXT[微软用户7](B21)
四、电话号码分析表
1、 使用时间函数,对Sheet1中用户的年龄进行计算。
要求:
计算用户的年龄,并将其计算结果填充到“年龄”列当中
=YEAR(TODAY()[微软用户8])-YEAR(C2)
2、使用REPLACE函数,对Sheet1中用户的电话号码进行升级。
要求:
对“原电话号码”列中的电话号码进行升级。
升级方法是在区号(0571)后面加上“8”,并将其计算结果保存在“升级电话号码”列
的相应单元格中。
使用文本函数REPLACE:
Old_text:
G2;Start_num:
5;Num_char:
0[微软用户9];New_text:
“8”
2. 使用逻辑函数,判断Sheet1中的“大于等于40岁的男性”,将结果保存在Sheet1中的“是否〉=40男性”。
=IF(AND(B3="男",D3>=40),"是","否")
3. 对Sheet1中的数据,根据以下条件,利用函数进行统计:
a) 统计性别为“男”的用户人数,将结果填入Sheet2的B1单元格中;
b) 统计年龄为“〉40岁”的用户人数,将结果填入Sheet2的B2单元格中。
COUNTIF(Sheet1!
B2:
B37,“男”)COUNTIF(Sheet1!
D2:
D37,”>40”)
五、房产销售表
1、 使用函数,根据Sheet1总的结果,统计每个销售人员的销售总额,将结果保存在Sheet2中相应的单元格中。
解法有二:
a. 在Sheet2需填充的单元格中分别输入函数,如在人员甲的销售总额函数为:
=SUMIF(Sheet1!
K3:
K26,”=人员甲”,Sheet1!
I3:
I26),以此类推。
b. 在Sheet2B2单元格中输入公式:
=SUMIF(Sheet1!
$K$3:
$K$26,Sheet2!
A2,Sheet1!
$I$3:
$I$26),拖拉B2的填充柄。
2、 使用RANK函数,根据Sheet2的结果,对每个销售人员的销售情况进行排序,并将结果保存在“排名”列当中。
RANK函数(统计函数,Rank(number,ref,order),number为需要找到排位的数字;ref为数字列表数组或对数字列表的引用;order为一数字,知名排位的方式,0或省略降序排位,非0升序排位
RANK(B2,$B$2:
$B$[微软用户10]6,0)注意ref为绝对引用
六、服装采购表
1、 使用VLOOKUP函数,对Sheet1中的商品单价进行自动填充。
要求:
根据“价格表”中的商品单价,利用VLOOKUP函数,将其单价自动填充到采购表中的“单价”列中。
=VLOOKUP(A11,$F$2:
$G$4,2,FALSE)[微软用户11]
2、 使用逻辑函数,对Sheet1中的商品折扣率进行自动填充。
要求:
根据“折扣表”中的商品折扣率,利用相应的函数,将其折扣率自动填充到采购表中的“折扣”列中。
=IF(B23<100,"0%",IF(B23<200,"6%",IF(B23<300,"8%","10%")))
3、 使用SUMIF函数,统计各种商品的采购总量和采购总金额,将结果保存在Sheet1中的“统计表”当中。
采购总量:
=SUMIF($A$11:
$A$43,I12,$B$11:
$B$43[微软用户12])然后拖动J12的填充柄
采购总金额:
=SUMIF($A$11:
$A$43,I12,$F$11:
$F$43)
七、公务员考试成绩表
1、 使用IF函数,对Sheet1中的“学位”列进行自动填充。
要求:
填充的内容根据“学历”列的内容来确定(假定学生均已获得相应学位):
博士研究生——博士
硕士研究生——硕士
本科——学士
其他——无
=IF(G7="博士研究生","博士",IF(G7="硕士研究生","硕士",IF(G7="本科","学士","无")))
2、 在Sheet2中,添加一列,将其命名为“排名”。
要求:
使用RANK函数,根据“总成绩”对所有考生排名。
=RANK(M18,$M$3:
$M$18,0)
八、停车收费表
1、 使用HLOOKUP函数,对Sheet1中的停车单价进行自动填充。
要求:
根据Sheet1中的“停车价目表”价格,利用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。
=HLOOKUP(B9,$A$2:
$C$3[微软用户13],2,FALSE)
2、 使用函数公式,计算停车费用,要求:
根据停放时间的长短计算停车费用,将计算结果填入到“应付金额”列中。
注意:
a. 停车按小时收费,对于不满一个小时的按照一个小时收费;
b. 对于超过整点小时数十五分钟的多积累一个小时。
(例如1小时23分,将以2小时计费)
=IF(HOUR(F9)=0,1,IF(MINUTE(F9)>=15,HOUR(F9)+1,HOUR(F9)))*C9
3、 使用统计函数,对Sheet1中的“停车情况记录表”根据下列条件进行统计,要求:
a. 统计停车费用大于等于40元的停车记录条数COUNTIF
b. 统计最高的停车费用=MAX(G9:
G39)
九、温度分析表
1.使用数组公式,对Sheet1中的相差温度值(杭州相对于上海的温差)进行填充。
=B2:
B16-C2:
C16
2.将Sheet1复制到Sheet2中,在Sheet2中,重新编辑数组公式,
将Sheet2中的“相差的温度值”中的数值取其绝对值(均为正数)
=ABS[微软用户14](B2:
B16-C2:
C16)
十、员工情况统计表
1、 使用REPLACE函数,对Sheet1中的员工代码进行升级,要求:
a. 升级方法:
在PA后面加上0;
b. 将升级后的员工代码结果填入表中的“升级员工代码”列中。
=REPLACE(B2:
B65,3,0,0)
2.使用时间函数,对Sheet1员工的“年龄”和“工龄”进行计算,并将结果填入到表中
“年龄”列和“工龄”列中。
年龄计算:
=YEAR(TODAY()[微软用户15])-YEAR(E2)
工龄计算:
如果工龄要写成“**年**个月”这种形式,则
=CONCATENATE(DATEDIF(G8,TODAY(),"y"),"年",DATEDIF(G8,TODAY(),"ym"),"个月")
或=CONCATENATE(YEAR(TODAY())-YEAR(G2),"年",MONTH(TODAY())-MONTH(G2),"个月")
考虑到下题“统计工龄大于等于10的人数,结果填入N5单元格中”,故工龄计算应为=(YEAR(TODAY())-YEAR(G2)),直接求出年份,且无格式。
补充函数:
1.使用IF函数,根据Sheet1中的“图书订购信息
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 常用 函数 使用 难点 总结