Excel常用函数及使用方法-2020更新.docxVIP

excel 常用函数及使用方法 一、数字处理 （一）取绝对值： =ABS( 数字) （二）数字取整： =INT( 数字 ) （三）数字四舍五入： =ROUND( 数字,小数位数 ) 二、判断公式 （一）把公式返回的错误值显示为空： 1、公式： C2=IFERROR(A2/B2,"") 2、说明：如果是错误值则显示为空，否则正常显示。 （二） IF 的多条件判断 1、公式： C2=IF(AND(A2<500,B2=" 未到期 ")," 补款","") 2、说明：两个条件同时成立用 AND, 任一个成立用 OR 函数。 三、统计公式 （一）统计两表重复 1、公式 :B2=COUNTIF(Sheet15!A:A,A2) 2、说明：如果返回值大于0 说明在另一个表中存在， 0 则不存在。 （二）统计年龄在 30~40 之间的员工个数 公式 =FREQUENCY(D2:D8,{40,29} （三）统计不重复的总人数 1、公式： C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 2、说明：用 COUNTIF 统计出每人的出现次数，用 1 除的方式把出现次数变成 分母，然后相加。 1 （六）按日期和产品求和 1、公式： =SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$ C$25) 2、说明： SUMPRODUCT 可以完成多条件求和 五、查找与引用公式 （一）单条件查找 1、公式 =VLOOKUP(B11,B3:F7,4,FALSE) 2、说明：查找是VLOOKUP 最擅长的，基本用法 （二）双向查找 1、公式： =INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0)) 2、说明：利用 MATCH 函数查找位置，用 INDEX 函数取值 六、字符串处理公式 （一）多单元格字符合并 1、公式： =PHONETIC(A2:A7) 2、说明： Phonetic 函数只能对字符型内容合并，数字不可以。 （二）截取除后 3 位之外的部分 1、公式： =LEFT(D1,LEN(D1)-3) 2、说明： LEN 计算出总长度,LEFT从左边截总长度-3 个 （三）截取 - 之前的部分 1、公式 :=Left(A1,FIND("-",A1)-1) 3 2、说明：用 FIND 函数查找位置，用 LEFT截取。 （四）截取字符串中任一段 1、公式 :=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20)) 2、说明 :公式是利用强插N 个空字符的方式进行截取 （五）字符串查找 1、公式： =IF(COUNT(FIND(" 河南 ",A2))=0," 否"," 是") 2、说明 : FIND 查找成功，返回字符的位置，否则返回错误值，而COUNT 可以 统计出数字的个数，这里可以用来判断查找是否成功。 （六）字符串查找一对多 1、公式： =IF(COUNT(FIND({" 辽宁"," 黑龙江"," 吉林 "},A2))=0," 其他 "," 东北") 2、说明：设置 FIND 第一个参数为常量数组，用 COUNT 函数统计 FIND 查找 结果 七、日期计算公式： 两日期间隔的年、月、日计算， A1 是开始日期（ 2011-12-1 ），B1 是结束日期 (2013-6-10) 。计算： 1、相隔多少天？ =datedif(A1,B1,"d") 结果： 557 2、相隔多少月 ? =datedif(A1,B1,"m") 结果： 18 3、相隔多少年 ? =datedif(A1,B1,"Y") 结果： 1 4、不考虑年相隔多少月？ =datedif(A1,B1,"Ym") 结果： 6 5、不考虑年相隔多少天？ =datedif(A1,B1,"YD") 结果： 192 6、不考虑年月相隔多少天？ =datedif(A1,B1,"MD") 结果： 9 datedif 函数第 3 个参数说明： 4 （1）"Y" 时间段中的整年数 （2）（ 2）"M" 时间段中的整月数 （3）"D" 时间段中的天数 （4）"MD" 天数的差。忽略日期中的月和年 （5）"YM" 月数的差。忽略日期中的日和年 （6）"YD" 天数的差。忽略日期中的年 7、扣除周末的工作日天数 （1）公式： =NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2 015,1,31),11) （2）说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末， 以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日 八、其他常用公式 （一）创建工作