今天跟大家一起来聊一聊excel中常用的函数集合。在这当中我有一部分就使用的是简单的写法,如果有不清楚的可以在线评论区留言,小编在下一次出技巧的时候为大家补上。
主要目录
一、数字处理
1、取绝对值函数2、取整函数3、四舍五入函数二、常用的判断公式1、如果计算的结果值错误那么显示为空2、IF语句的多条件判定及返回值三、常用的统计公式1、统计在两个表格中相同的内容2、统计不重复的总数据四、数据求和公式1、隔列求和的应用2、单条件求和应用3、单条件模糊求和的应用4、多条件模糊求和的应用5、多表相同位置求和的应用6、按日期和产品求和五、查找与引用公式1、单条件查找2、双向查找3、查找最后一条符合条件的有效记录。4、多条件查找5、指定区域最后一个非空数据的查找6、按数字区域间取对应的值六、字符串处理公式1、多单元格字符串的合并2、截取结果3位之外的部分3、截取特定字符前的部分4、截取字符串中任一段的公式5、字符串查找公式6、字符串查找一对多用法七、日期计算相关1、日期间相隔的年、月、天数计算2、扣除周末天数的工作日天数一、数字处理
1、取绝对值函数
公式:=ABS(数字)
2、取整函数
公式:=INT(数字)
3、四舍五入函数
公式:=ROUND(数字,小数位数)
二、判断公式
1、如果计算的结果值错误那么显示为空
公式:=IFERROR(数字/数字,)
说明:如果计算的结果错误则显示为空,否则正常显示。
如图,在C2单元格内输入公式:=IFERROR(A2/B2,)
2、IF语句的多条件判定及返回值
公式:IF(AND(单元格(逻辑运算符)数值,指定单元格=返回值1),返回值2,)
如图,在C2单元格内输入公式:C2=IF(AND(A2500,B2=未到期),补款,)
说明:所有条件同时成立时用AND,任一个成立用OR函数。
三、常用的统计公式
1、统计在两个表格中相同的内容
公式:B2=COUNTIF(数据源:位置,指定的,目标位置)
说明:如果返回值大于0说明在另一个表中存在,0则不存在。
如果,在此示例中所用到的公式为:B2=COUNTIF(Sheet15!A:A,A2)
2、统计不重复的总数据
公式:C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明:用COUNTIF函数统计出源数据中每人的出现次数,并用1除的方式把变成分数,最后再相加。
四、数据求和公式
1、隔列求和的应用
公式:H3=SUMIF($A$2:$G$2,H$2,A3:G3)或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
说明:如果在标题行中没有规则就可以用第2个公式
2、单条件应用之求和
公式:F2=SUMIF(A:A,C:C)
说明:这是SUMIF函数的最基础的用法
,E2
3、单条件应用之模糊求和
公式:详见下图
说明:在使用模糊求和的时候要对通配符的使用有一定的了解,例如表示任意N个字符可以用“*”,实例:*A*表示A前后的任意N个字符,也包括他本身。
4、多条件应用之模糊求和
公式:
说明:在sumifs函数中也可以使用通配符*
5、多表相同位置求和的应用
公式:
说明:此公式为实时更新,也就是说我们在表中间删除和添加都不会影响结果。
6、按日期和产品求和
公式:
说明:SUMPRODUCT也可以完成多条件求和
1、单条件查找
公式1:
说明:VLOOKUP是excel中最常用的查找方式
2、双向查找
公式:
说明:用MATCH和INDEX这两个公式组合使用
MATCH函数查位置,用INDEX函数取值
3、查找最后一条符合条件的有效记录
公式:详见下图
说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值
4、多条件查找
公式:详见下图
说明:公式原理同上一个公式
5、按数字区域间取对应的值
公式;详见下图
说明:略
公式:详见下图
公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。
1、多单元格字符串的合并
公式:
说明:Phonetic函数只能合并字符型数据,不能合并数值。
2、截取结果3位之外的部分
公式:
说明:LEN计算总长度,LEFT从左边截总长度-3个
公式:
说明:用FIND查找位置,用LEFT函数截取。
4、截取字符串中任一段的公式
公式:
说明:公式是利用强制插入功能插入N个空字符的方式进行截取
5、字符串查找公式
公式:
说明: FIND查找成功,返回字符位置,否则返回无效值,而COUNT统计出数字的个数,此处用来判定查找是否成功。
6、字符串查找一对多用法
公式:
说明:设置FIND第一个参数:常量数组,用COUNT函数统计查找结果
七、日期计算相关
1、日期间相隔的年、月、天数计算
A2是开始日期(2011-12-2),B2是结束日期(2013-6-11)。计算:
相差多少天的公式为:=datedif(A2,B2,d) 其结果:557
相差多少月的公式为: =datedif(A2,B2,m) 其结果:18
相差多少年的公式为: =datedif(A2,B2,Y) 其结果:1
不考虑年份相隔多少月的公式为:=datedif(A1,B1,Ym) 其结果:6
不考虑年份相隔多少天的公式为:=datedif(A1,B1,YD) 其结果:192
不考虑年份月份相隔多少天的公式为:=datedif(A1,B1,MD) 其结果:9
datedif函数第3个参数说明:
Y 时间段中的整年数。
M 时间段中的整月数。
D 时间段中的天数。
MD 日期中天数的差。忽略月和年。
YM 日期中月数的差。忽略日和年。
YD 日期中天数的差。忽略年。
2、扣除周末天数的工作日天数
公式:
C2=NETWORKDAYS.INTL(IF(B2DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)
说明:返回这个区间的的所有正常工作日数,使用参数指示哪些天是周末,以及有多少天是周末。法定节假日均不是工作日。
公式的积累是一个漫长的过程,由浅入深,大家可以每天学习一个,也就差不多一个月就可以搞定。看文章学会收藏是个好习惯,你应该也要学会,还没收藏的朋友赶快收藏一波吧。
联系客服