01、进行函数书写前要注意的事项
1、函数公式三要素(等号、函数名、参数)
2、所有的标点符号输入都必须在英文法状态下输入
3、数值计算时所有单元格都必须是数值,文本不能相加
4、+ - * / & 这五个符号分别是数值计算机的加减乘除和文本相加符号
5、填充柄的使用,双击单元格右下角的填充柄就可以按照当前单元格自动填充整列数据了。
6、绝对引用:选中单元格或者区域后按F4,笔记本电脑是fn+F4
7、混合引用:
- 固定列不变:在列英文字母前加$
- 固定行不变:在行数字前加$
02、sum求和函数
功能:对指定参数进行求和
函数格式:=sum(数据区域)
二级例题:
在“成绩表”中,计算每个学生的“总分”将其放入J2到J503单元中。
函数公式:=sum(E2:I2)
03、average求平均函数
功能:对指定参数进行求平均值
函数格式:=average(区域)
二级例题:
计算每个学生的“平均分”, 将其放入K2到K503单元中,并将区域K2:K503的数值格式设置为小数点后保留2位。
函数公式:=average(E2:I2)
04、if函数
if逻辑判断函数:根据逻辑判断是或否,返回两种不同的结果
函数格式:=if(逻辑判断语句,逻辑判断"是"返回的结果,逻辑判断"否"返回的结果)
初级:
根据“高等数学”列的数据,在F列求出每一个学生的高数等级,成绩>=60等级为及格,成绩<60等级为不及格。
函数公式:=if(E2>=60,"及格","不及格”)
中级:
二级例题:
根据“高等数学”列的数据,在F列求出每一个学生的高数等级
函数公式:
=if(E2>=90,"优秀",if(E2>=80,"良好",if(E2>=60,"及格",“不及格")))
08、max求最大值函数
max求最大值函数:求指定区域中的最大值
函数格式:=max(区域)
二级例题:根据总分列,在K2单元格求出总分第1名分数
函数公式:=max(J2:J503)
09、large求第n大值函数
large函数:求指定区域中的第L大值
函数格式:=large(区域,返回第几个最大值)
二级例题:根据总分列,在K2单元格求出总分第2名分数
函数公式:=large(J2:J503,2)
10、min求最小值函数
min求最小值函数:求指定区域中的最小值
函数格式:=min(区域)
二级例题:根据总分列,在K2单元格求出总分倒数第1名的分数
函数公式:=max(J2:J503)
11、small函数:求指定区域中的第n小值
函数格式:=small(区域,返回第几个最小值)
题目要求:根据总分列,在K2单元格求出总分倒数第2名分数
函数公式:=small(J2:J503,2)
12、int取整函数
int取整函数:对指定数字向下取整,把全部的小数都去掉,只保留整数
函数格式:=int(数值)
题目要求:求出平时成绩列的整数成绩
函数公式:=int(C2)
13、round四舍五入函数
round四舍五入函数:对指定数字进行四舍五入
函数格式:=round(数值,保留小数位数)
题目要求:根据平时成绩列的数据,四舍五入取整放到E列
函数公式:=round(C2,0)
16、sqrt开平方根函数
sqrt开平方根函数:求一个非负实数的平方根
函数格式:=sqrt(数值)
函数公式:=sqrt(A2)
17、mod求余函数
mod求余函数:求某个数字除以另一个数字的余数
函数格式:=mod(被除数,除数)
函数公式:=sqrt(D2,2)
注意事项:求余函数通常用于判断奇偶
18、count求个数函数
count求个数函数:求指定区域中数值单元格的个数
函数格式:=count(区域)
函数公式:=count(A1:A7)
20、rank求排名函数
rank排名函数:求某个数据在指定区域中的排名
函数格式:=rank(排名对象,排名的数据区域,降序或者升序)
二级例题
在工作表的S1单元格录入“实发排行榜”,依据“实发合计”列的数据,在“实发排行榜”列中通过公式或者函数计算实发工资排行榜,实发合计排名第一的,显示“第1名”,实发合计排名第二的,显示“第2名”,以此类推。
注意事项:
(1)第二参数一定要绝对引用
(2)&为文本连接符号
(3)第三参数通常省略不写,0和默认都为降序排名,如果要升序排名,则第3个参数写1。
21、rank.eq函数
Rank和rank.eq的用法一致。rank是Excel早起版本就有的函数,而rank.eq是Excel2010才开始出现的,同时增加了rank.avg函数,微软准备用rank.eq替换rank函数,以避免与rank.avg混淆。将来的某个版本可能就不会再用rank函数了,现在的版本之所以保留rank,是为了与低版本Excel兼容。
22、left从左侧取文本函数
left从左侧取文本函数:从文本左侧起提取文本中的指定个数的字符
函数格式:=left(要提取的字符串,提取的字符数)
二级例题:
使用公式统计每个活动地点所在的省份或直辖市,并将其填写在"地区"列所对应的单元格中,例如"北京市”、“浙江省"。
函数公式:=left(C3,3)
23、mid从中间取文本函数
mid从中间取文本函数:从文本中间提取文本中的指定个数的字符
函数格式:=mid(要提取的字符串,从第几位开始取,提取的字符数)
二级例题:
使用公式统计每个活动地点所在的省份或直辖市,并将其填写在"地区"列所对应的单元格中,例如"北京市”、“浙江省"。
函数公式:=mid(C3,1,3)
注意事项:
(1)mid函数提取的结果是文本,不能直接参与计算,如要参与数值计算需先+0进行转换
24、right从右侧取文本函数
right从右侧取文本函数:从文本右侧起提取文本中的指定个数的字符
函数格式:=right(要提取的字符串,提取的字符数)
=right(A2,2)
26、mid,mod,if嵌套
二级例题:在工作表"初三学生档案”中,利用公式及函数依次输入每个学生的性别“男”或“女”
函数公式:=if(mod(mid([@身份证号码,17,1),2)=1,"男”,"女")
解析:身份证第17位数是奇数代表姓名为男,偶数代表性别为女,所以这题的函数嵌套逻辑是这样的,先用mid函数将身份证的第17位数取出来,然后用mod函数除以2求余数,然后根据余数用if函数来进行判断,如果余数等于1的话,就输出“男”,否则输出“女”。
27、today()求当前日期函数
today求当前日期函数:求电脑系统中今天的日期
函数公式:=today()
28、year求年份函数
year求年份函数:求指定日期的年份
函数格式:=year(日期)
函数公式:=year(C2)
29、month求月份函数
month求月份函数:求指定日期的月份
函数格式:=month(日期)
函数公式:=month(C2)
30、day求日期号函数
函数公式:=day(C2)
31、date求日期函数
date日期函数:将年月日三个值转变成日期格式
函数格式:=date(年,月,日)
函数公式:=date(D2,E2,F2)
32、datedif向上求日期间隔函数
datedif求日期间隔函数:计算两个日期之间的间隔(年/月/日)
函数格式:=datedif(起始日期,终止日期,返回类型)
二级例题:
在“员工绩效汇总”工作表的“工龄"列的空白单元格(G2:G201)中,输入公式,使用函数DATEDIF计算截至今日的“工龄”, 注意,每满一年工龄加1, ”今日”指每次打开本工作簿的动态时间。
函数公式:=datedif(F2,today(),"y")
注意事项:
(1)返回类型返回相距多少年用"y",相距多少月用"m",相距多少天用"d",三种情况都要加双引号
(2)datedif是根据一年365天计算的满365天才算一年
33、days360()求日期间隔天数函数
days360求日期间隔函数:计算两个日期之间相隔的天数(一年按360天算)
函数格式:=days360(起始日期,终止日期)
二级例题:
在工作表"员工基础档案"中,利用公式及函数求每位员工截止2015年9月30日的年龄,年龄需要按周岁计算,满1年才计1岁,每月按30天、一年按360天计算。
函数公式:=int(days360([@出生日期],"2015-9-30")/360)
注意事项:days360是根据一年360天进行计算的
34、weekday求星期函数
weekday求星期函数:将某个日期所处的星期转换成数字
函数格式:=weekday(日期,返回类型)
二级例题:
如果"日期"列中的日期为星期六或星期日,则在"是否加班’列的单元格中显示"是",否则显示否" (必须使用公式)。
函数公式:=if(weekday(A3,2)>5,"是","否")
注意事项:
(1)第二参数返回类型填写2是根据中国人习惯,星期一返回1,星期二返回2,以此类推
(2)weekday函数常用于跟if函数结合判断是否加班
35、vlookup函数
vlookup查询函数:在指定区域的首列沿垂直方向查找指定的值,返回同一行中的其他值
函数格式:=vlookup(查询对象,查询的数据区域,结果所在的列数,精确匹配或者近似匹配)
精确匹配:
初级
题目要求:根据"姓名"列内容,使用vlookup函数,生成"成绩"列内容。对照关系参照A1:C9
函数公式:=vlookup(G2,$B$1:$C$9,2,0)
二级例题:
依据"费用类别编号"咧内容,使用VL00KUP函数,生成"费用类别"列内容。对照关系参考"费用类别"工作表。
函数公式:=vlookup(E3,费用类型!$A$3:$B$12,2,0)
注意事项:
(1)查询目标必须位于查询数据区域的首列
(2)第二参数(查询的数据区域)要绝对引用
(3)第三参数写0代表精确匹配引用,写1代表近似匹配应用
近似匹配:
近似匹配的意思就是——在比查找值小的范围内匹配与之最接近的值。
vlookup近似匹配初级版
二级例题:
根据学生的成绩等级,查询学生的等级
函数公式:=vlookup(F2,$A$2:$B$6,2,1)
解析:在比查找值小的范围内匹配与之最接近的值。例如上题,58<60,所以他只能匹配到成绩为0的等级;如果是62,62<80,62最接近60,所以他就匹配到成绩为60分那个等级。
vlookup近似匹配高级版
二级例题:
根据销售总额查询客户等级销售总额,如下题:
在“客户信息”工作表中,根据每个客户的销售总额计算其所对应的客户等级(不要改变当前数据的排序),等级评定标准可参考“客户等级”工作表。
函数公式:=vlookup(sumifs(订单信息!G:G,订单信息!B:B,客户信息!A2),客户等级!$A$1:$B$11,2,1)
(1)查询目标必须位于查询数据区域的首列
(2)近似匹配的查询区域的首列必须与查询区域的首列的数据类型一样,如果查询区域首列是一个区间数值,则要根据区间的最低值建立一个辅助列。
(3)第二参数(查询的数据区域)要绝对引用
(4)第三参数写0代表精确匹配引用,写1代表近似匹配应用
42、Excel工作表常用快捷键
文章评论