Excel超常用的30个函数（可直接复制使用）

简迷离

Excel中最常用的30个函数：

本期Excel教程推荐30个超常用函数，大部分函数可直接复制使用，助力高效办公。
一、数字处理

1、取绝对值
绝对值是指一个数在数轴上所对应点到原点的距离。
举个例子：
3的绝对值为3，-3的绝对值也为3，数字的绝对值可以被认为是与零的距离。
在Excel中函数如下：
=ABS(数字)



ABS案例图

2、取整
举个例子：
10-11之间的所有小数，比如10.99，取整后都为10。
在Excel中函数如下：
=INT(数字)



INT案例图

3、四舍五入
举个例子：
10-11之间的所有小数，比如10.4，输出为10，比如10.5，则输出为11，符合四舍五入的原则
在Excel中函数如下：
=ROUND(数字,小数位数)



ROUND案例图

二、判断公式

1、把公式产生的错误值显示为空
函数说明：
该函数判断单元格中的公式是不是错的，如果输入的是错误的公式，则显示为空，这个函数能帮助验证公式是否正确。
在Excel中函数如下：
公式：
C2=IFERROR(A2/B2,"")



IFERROR案例图

2、IF多条件判断返回值
函数说明：
满足固定条件之后，则输出固定值，该函数两个条件同时成立用AND,任一个成立用OR函数。
公式：
C2=IF(AND(A2<500,B2="未到期"),"补款","")



IF多条件判断案例图

三、统计公式

1、统计两个表格重复的内容
函数说明：
此函数可以自动识别出两个不同的Excel中是否有相同的内容，并筛选出相同内容出现的次数是多少。
公式:
B2=COUNTIF(Sheet15!A:A,A2)



查找重复值

2、统计不重复的总人数
函数说明：
可统计出同一个表中，不出现重复的总人数，比如下图除了A以外，还有4个不重复的姓名，因此输出值则为4。
公式：
C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))



统计不重复总人数

四、求和公式

1、隔列求和
函数说明：
此函数可将不同列的数字，进行求和
公式：
H3=SUMIF($A$2:$G$2,H$2,A3:G3)
或
=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
说明：如果标题行没有规则用第2个公式



隔列求和

2、单条件求和
函数说明：
此函数可实现单条件求和，如下图所示，可求和出A产品的总金额。
公式：
F2=SUMIF(A:A,E2,C:C)



单条件求和

3、单条件模糊求和
函数说明：
如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。
公式：详见下图



单条件模糊求和

4、多条件模糊求和
函数说明：
说明：在sumifs中可以使用通配符*
公式：
C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

5、多表相同位置求和
函数说明：
可汇总多表格同一位置数据总和。
公式：
b2=SUM(Sheet1:Sheet19!B2)



多表格相同位置求和

6、按日期和产品求和
函数说明：
此函数可以按照日期和产品求和，例如A产品1月份的总销量是多少，就能得出是23。
公式：
F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)



按日期和产品求和

五、查找与引用公式

1、单条件查找公式
函数说明：
此函数可根据某个条件查找对应的数据
公式：
C11=VLOOKUP(B11,B3:F7,4,FALSE)
此函数可某个条件查找对应的数据

2、双向查找公式
函数说明：
此函数主要用于双向查找公式，比如提取姓名为张晴，月份为3月份的销量为20。
公式：
=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))



双向查找公式

3、查找最后一条符合条件的记录。
函数说明：
0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值
公式：详见下图



查找记录

4、多条件查找
公式:详见下图
说明:公式原理同上一个公式
5、指定区域最后一个非空值查找
公式:详见下图



指定区域最后一个非空值查找

6、按数字区域间取对应的值
函数说明：
该函数适用于按数字区域间取值的情况，例如姓名李玉兰，销售量>200，实际为300，单个提成即可对应15。
公式：详见下图



按数字区域取值

六、字符串处理公式

1、多单元格字符串合并
函数说明：
Phonetic函数只能对字符型内容合并，数字不可以。
公式：
c2=PHONETIC(A2:A7)

2、截取除后3位之外的部分
函数说明：
LEN计算出总长度,LEFT从左边截总长度-3个
公式：
=LEFT(D1,LEN(D1)-3)

3、截取-前的部分
函数说明：
用FIND函数查找位置，用LEFT截取。
公式:
B2=Left(A1,FIND("-",A1)-1)

4、截取字符串中任一段的公式
函数说明:
公式是利用强插N个空字符的方式进行截取
公式:
B1=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

5、字符串查找
函数说明:
FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。
公式：
B2=IF(COUNT(FIND("河南",A2))=0,"否","是")

6、字符串查找一对多
函数说明：
设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果
公式：
B2=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

七、日期计算公式

1、两日期相隔的年、月、天数计算
函数说明：
A1是开始日期(2011-12-1)，B1是结束日期(2013-6-10)。计算：
相隔多少天?=datedif(A1,B1,"d") 结果：557
相隔多少月? =datedif(A1,B1,"m") 结果：18
相隔多少年? =datedif(A1,B1,"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(B2
说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日。
八、EXCEL格式转换

在使用EXCEL表格中，经常会遇到EXCEL转PDF或者PDF转EXCEL的需求，这里就需要使用这款万能文档格式转换器-转转大师。
以下这款文档转换器，支持PDF/Word/Excel/PPT/图片/电子书等上百种文档常用文档格式批量互转，助力高效办公。



转转大师

总结一下

以上就是比较经常使用的Excel函数以及快捷方法，如果大家还有哪些函数需求，欢迎随时留言，本文会根据需求保持更新。

黄灰红

这么强,支持楼主，佩服