今天跟大家一起来聊一聊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、按数字区域间取对应的值 公式;详见下图 说明:略 ![]() 6、字符串处理公式公式:详见下图 公式说明: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) 说明:返回这个区间的的所有正常工作日数,使用参数指示哪些天是周末,以及有多少天是周末。法定节假日均不是工作日。 ![]() 公式的积累是一个漫长的过程,由浅入深,大家可以每天学习一个,也就差不多一个月就可以搞定。看文章学会收藏是个好习惯,你应该也要学会,还没收藏的朋友赶快收藏一波吧。 |
|