2020年终巨献，Excel公式大全！千万不要藏私，果断分享……
公众号回复2016 下载office2016
总有人在问：谁有Excel公式大全？
说有这个东西的都是忽悠人，无非就是用一些视频频率稍微高一点的公式代表了所谓的公式大全。
压根也没有这种东西！
因为问题一变，公式就会随着变化；数据内容一变，公式也会随着变化，数据呈现的方式一变，公式又得变化。
唯一不变的是公式的规则和函数的运用。
2020年是不平凡的一年，《菜鸟的Excel函数修炼手册》一书也上市一年了，在年末的最后一天，将本书中涉及的函数罗列出来，姑且也算上一回【公式大全】吧……
点击文末阅读原文可以免费试读本书
以下分享10大类132个公式
1、=VLOOKUP(G2,B:E,4,0)
一句话介绍：查找B:E这个区域中第4列与G2单元格内容相同的数据
函数格式：VLOOKUP(要找什么,在哪找,第几列,怎么找)
2、=HLOOKUP(A6,2:5,4,0)
一句话介绍：查找2:5这个区域中第4行与A6单元格内容相同的数据
函数格式：HLOOKUP(要找什么,在哪找,第几列,怎么找)
3、=MATCH(B2,A1:F1,0)
一句话介绍：得到B2中数据在A1:F1中的位置
函数格式：MATCH(要找什么,在哪找,怎么找)
4、=INDEX(B2:E7,3,2)
一句话介绍：得到区域B2:E7中第3行第2列的数据
函数格式：INDEX(单元格区域,行数,列数)
5、=ISERROR(A1)
一句话介绍：得到A1单元格向下三行向右两列的单元格中的值
函数格式：OFFSET(起始位置,行偏移数,列偏移数,高度,宽度)
6、=INDIRECT("Sheet1!A1",1)
一句话介绍：得到Sheet1!A1单元格中的数据
函数格式：INDIRECT(地址,引用方式)
7、=LOOKUP(J2,B:B,A:A)
一句话介绍：得到B列与J2相同数据在A列对应的值
函数格式：LOOKUP(查找值,条件区域,结果区域)
8、=CHOOSE(2,A2,A3,A4,A5)
一句话介绍：得到第二个值A3单元格中的数据
函数格式：CHOOSE(序号,第一个值,第二个值,……)
9、=HYPERLINK("d:\重要文档","重要文档")
一句话介绍：打开D盘的重要文档这个文件夹
函数格式：HYPERLINK(链接地址,显示内容)
1、=YEAR(B15)
一句话介绍：得到B15单元格中日期对应的年份
函数格式：YEAR(日期所在的单元格)
2、=MONTH(B15)
一句话介绍：得到B15单元格中日期对应的月份
函数格式：MONTH(日期所在的单元格)
3、=DAY(B15)
一句话介绍：得到B15单元格中日期对应的日
函数格式：DAY(日期所在的单元格)
4、=HOUR(B16)
一句话介绍：得到B16单元格中时间对应的小时
函数格式：HOUR(时间所在的单元格)
5、=MINUTE(B16)
一句话介绍：得到B16单元格中时间对应的分
函数格式：MINUTE(时间所在的单元格)
6、=SECOND(B16)
一句话介绍：得到B16单元格中时间对应的秒
函数格式：SECOND(时间所在的单元格)
7、=TODAY()
一句话介绍：得到当前系统日期
函数格式：TODAY()
8、=NOW()
一句话介绍：得到当前系统时间
函数格式：NOW()
9、=DATE(2019,11,12)
一句话介绍：根据年月日得到一个日期
函数格式：DATE(年,月,日)
10、=TIME(8,20,45)
一句话介绍：根据时分秒得到一个时间
函数格式：TIME(时,分,秒)
11、=EDATE(TODAY(),6)
一句话介绍：得到当前日期6个月后对应的日期
函数格式：EDATE(起始日期,月数)
12、=DATEDIF(A1,B1,"Y")
一句话介绍：得到B1和A1之间相隔几年
函数格式：DATEDIF(起始日期,截止日期,统计方式)
13、=EOMONTH(A1,6)
一句话介绍：得到A1单元格中日期6个月后月末的日期
函数格式：EOMONTH(起始日期,月数)
14、=WEEKDAY(A1,2)
一句话介绍：得到A1单元格中日期在一周的第几天
函数格式：WEEKDAY(日期,计算方式)
15、=WEEKNUM(A1,2)
一句话介绍：得到A1单元格中日期在一年的第几周
函数格式：WEEKNUM(日期,计算方式)
16、=NETWORKDAYS(A2,B2,C2)
一句话介绍：得到两个日期之间排除周末和特殊节假日后的工作天数
函数格式：NETWORKDAYS(开始日期,结束日期,特殊节假日列表)
17、=NETWORKDAYS.INTL(A2,B2,11,C2)
一句话介绍：得到两个日期之间排除周末（自定义）和特殊节假日后的工作天数
函数格式：NETWORKDAYS.INTL(开始日期,结束日期,周末选项,特殊节假日列表)
18、=WORKDAY(J2,K2,L2)
一句话介绍：根据开始日期得到指定天数后不含周末和节假日的工作日是哪天
函数格式：WORKDAY(开始日期,间隔天数,特殊节假日列表)
19、=WORKDAY.INTL(J2,K2,11,L2)
一句话介绍：根据开始日期得到指定天数后不含周末（自定义）和节假日的工作日是哪天
函数格式：WORKDAY.INTL(开始日期,间隔天数,周末选项,特殊节假日列表)
1、=IF(A1>=60,"合格","不合格")
一句话介绍：A1中的数字大于等于60得到合格，否则得到不合格。
函数格式：IF(条件,条件成立的结果,条件不成立的结果)
2、=AND(多个条件)
一句话介绍：判断是否满足所有条件
函数格式：AND(多个条件)
3、=OR(多个条件)
一句话介绍：判断是否所有条件都不满足
函数格式：OR(多个条件)
4、=IFS(B2=100,"满分",B2>=60,"及格",B2<60,"不及格")
一句话介绍：可以按照不同的条件得到对应的结果
函数格式：IFS(条件1,结果1,条件2,结果2,……)
1、=SUM(A:B,E:F)
一句话介绍：对A、B、E、F这四列中的数字求和
函数格式：SUM(一个或多个数据、单元格)
2、=MIN(A:A)
一句话介绍：得到A列数字中的最小值
函数格式：MIN(一个或多个数据、单元格)
3、=MINA(A:A)
一句话介绍：得到A列数据中的最小值
函数格式：MINA(一个或多个数据、单元格)
4、=MAX(A:A)
一句话介绍：得到A列数字中的最大值
函数格式：MAX(一个或多个数据、单元格)
5、=MAXA(A:A)
一句话介绍：得到A列数据中的最大值
函数格式：MAXA(一个或多个数据、单元格)
6、=MEDIAN(A:A)
一句话介绍：得到A列数字的中位数
函数格式：MEDIAN(一个或多个数据、单元格)
7、=AVERAGE(A:A)
一句话介绍：得到A列数字的平均值
函数格式：AVERAGE(一个或多个数据、单元格)
8、=AVERAGEA(A:A)
一句话介绍：得到A列数据的平均值
函数格式：AVERAGEA(一个或多个数据、单元格)
9、=MODE(A:A)
一句话介绍：得到A列出现频率最高的数字
函数格式：MODE(一个或多个数据、单元格)
10、=LARGE(A:A,3)
一句话介绍：得到A列第3大的数字
函数格式：LARGE(数据区域,k)
11、=SMALL(A:A,3)
一句话介绍：得到A列倒数第3的数字
函数格式：SMALL(数据区域,k)
12、=SUMIF(A:A,“电视”,B:B)
一句话介绍：A列为电视的数据对应B列的值求和
函数格式：SUMIF(条件区域,条件,求和区域)
13、=SUMIFS(D:D,B:B,”沈伊杰”,C:C,”壁挂空调”)
一句话介绍：B列为沈伊杰且C列为壁挂空调的数据对应D列的值求和
函数格式：SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,……)
14、=AVERAGEIF(A:A,“电视”,B:B)
一句话介绍：A列为电视的数据对应B列的值求平均值
函数格式：AVERAGEIF(条件区域,条件,计算区域)
15、=AVERAGEIFS(D:D,B:B,”沈伊杰”,C:C,”壁挂空调”)
一句话介绍：B列为沈伊杰且C列为壁挂空调的数据对应D列的值求平均值
函数格式：AVERAGEIFS(计算区域,条件区域1,条件1,条件区域2,条件2,……)
16、=COUNT(A:A)
一句话介绍：统计A列有多少个数字
函数格式：COUNT(一个或多个数据、单元格)
17、=COUNTA(A:A)
一句话介绍：统计A列有多少个数据
函数格式：COUNTA(一个或多个数据、单元格)
18、=COUNTBLANK(A:A)
一句话介绍：统计A列有多少个空单元格
函数格式：COUNTBLANK(单元格区域)
19、=COUNTIF(B:B,”女”)
一句话介绍：统计B列为女的单元格个数
函数格式：COUNTIF(数据区域,条件）
20、=COUNTIFS(D:D,”>30”,D:D,”<=40”)
一句话介绍：统计D列大于30小于等于40的单元格个数
函数格式：COUNTIFS(数据区域1,条件1,数据区域2,条件2,……）
21、=SUBTOTAL(9,D:D)
一句话介绍：对D列筛选数据进行求和
函数格式：SUBTOTAL(功能代码,数据区域)
22、=AGGREGATE(9,7,D:D)
一句话介绍：对D列筛选出的正常数据进行求和
函数格式：AGGREGATE(功能代码,忽略选项,数据区域,k)
23、=SUMPRODUCT(B2:B6,C2:C6)
一句话介绍：将B2:B6与C2:C6中对应数字相乘后再求和
函数格式：SUMPRODUCT(数据区域1,数据区域2,……)
24、=RANK(B2,B:B,0)
一句话介绍：按照从大到小的顺序计算B2在B列数字中的排名
函数格式：RANK(数字,区域,排序方式)
25、=RANK.EQ(B2,B:B,0)
一句话介绍：按照从大到小的顺序计算B3在B列数字中的排名
函数格式：RANK.EQ(数字,区域,排序方式)
26、=RANK.AVG(B2,B:B,0)
一句话介绍：按照从大到小的顺序计算B4在B列数字中的平均排名
函数格式：RANK.AVG(数字,区域,排序方式)
27、=TRIMMEAN(B2:G2,2/6)
一句话介绍：得到数据区域中除去最大和最小值后的平均值
函数格式：TRIMMEAN(数据区域,百分比)
28、=MAXIFS($D:$D,$A:$A,$G2,$C:$C,H$1)
一句话介绍：得到一个单元格区域中满足一组条件的最大值
函数格式：MAXIFS(结果所在区域,条件区域1,条件1,条件区域2,条件2,……)
29、=MINIFS($D:$D,$A:$A,$G8,$C:$C,H$1)
一句话介绍：得到一个单元格区域中满足一组条件的最小值
函数格式：MINIFS(结果所在区域,条件区域1,条件1,条件区域2,条件2,……)
1、=N(A1)
一句话介绍：将单元格中的数据转换为数字
函数格式：N(要转化的值)
2、=MOD(5,2)
一句话介绍：返回5除以2的余数1
函数格式：MOD(被除数,除数)
3、=INT(9.15)
一句话介绍：得到与9.15最接近的整数9
函数格式：INT(数字)
4、=ROUND(9.156,2)
一句话介绍：将9.156四舍五入保留两位小数得到9.16
函数格式：ROUND(数字,要保留的位数)
5、=ROUNDUP(9.123,2)
一句话介绍：将9.123向上进位并且保留两位小数得到9.13
函数格式：ROUNDUP(数字,要保留的位数)
6、=ROUNDDOWN(9.129,2)
一句话介绍：将9.129向下舍入并且保留两位小数得到9.12
函数格式：ROUNDDOWN(数字,要保留的位数)
7、=TRUNC(9.111,2)
一句话介绍：将9.111从第二位小数进行截取得到9.11
函数格式：TRUNC(数字,要保留位数)
8、=MROUND(13,5)
一句话介绍：得到5的倍数中与13最接近的数字15
函数格式：MROUND(数字,基数)
9、=CEILING(11,5)
一句话介绍：得到5的倍数中大于11的最小值15
函数格式：CEILING(数字,基数)
10、=FLOOR(14,5)
一句话介绍：得到5的倍数中小于14的最大值10
函数格式：FLOOR(数字,基数)
11、=RAND()
一句话介绍：得到一个0到1之间的随机数字
函数格式：RAND()
12、=RANDBETWEEN(1,10)
一句话介绍：得到一个1到10之间的随机整数
函数格式：RANDBETWEEN(起始值,终止值)
1、=TEXT(10,”0000”)
一句话介绍：按照指定的格式将数字10显示为0010
函数格式：TEXT(要处理的数据,格式代码)
2、=LEN(A1)
一句话介绍：得到A1单元格中数据的字符数
函数格式：LEN(要统计长度的数据或单元格)
3、=LENB(A1)
一句话介绍：得到A1单元格中数据的字节数
函数格式：LENB(要统计长度的数据或单元格)
4、=LEFT(A1,2)
一句话介绍：从A1左边截取两个字符
函数格式：LEFT(要截取的单元格或数,截取字数)
5、=LEFTB(A1,2)
一句话介绍：从A1左边截取两个字节
函数格式：LEFTB(要截取的单元格或数,截取字节数)
6、=RIGHT(A1,2)
一句话介绍：从A1右边截取两个字符
函数格式：RIGHT(要截取的单元格或数,截取字数)
7、=RIGHTB(A1,2)
一句话介绍：从A1右边截取两个字节
函数格式：RIGHTB(要截取的单元格或数,截取字节数)
8、=MID(A1,2,3)
一句话介绍：从A1右边截取两个字符
函数格式：MID(要截取的单元格或数,从第几个字开始,截取字数)
9、=MIDB(A1,2,3)
一句话介绍：从A1右边截取两个字节
函数格式：MIDB(要截取的单元格或数,从第几个字开始,截取字节数)
10、=FIND(”A”,A1,3)
一句话介绍：在A1单元格的第3个字开始找字母A，得到A在第几个字，区分大小写。
函数格式：FIND(找什么,在哪找,从第几个字开始找)
11、=FINDB(”A”,A1,3)
一句话介绍：在A1单元格的第3个字开始找字母A，得到A在第几个字，区分大小写。
函数格式：FINDB(找什么,在哪找,从第几个字开始找)
12、=SEARCH(”A”,A1,3)
一句话介绍：在A1单元格的第3个字开始找字母A，得到A在第几个字，不区分大小写。
函数格式：SEARCH(找什么,在哪找,从第几个字开始找)
13、=SEARCHB(”A”,A1,3)
一句话介绍：在A1单元格的第3个字开始找字母A，得到A在第几个字，不区分大小写。
函数格式：SEARCHB(找什么,在哪找,从第几个字开始找)
14、=SUBSTITUTE(B2,”2019年”,”“,2)
一句话介绍：将B2单元格中的第2个2019年替换为空
函数格式：SUBSTITUTE(目标单元格,旧内容,新内容,替换第几个或全替换)
15、=REPLACE(F2,4,4,”****”)
一句话介绍：将F2单元格中第4个字开始的4个字换成*
函数格式：REPLACE(原字符串,开始位置,字节个数,新字符串)
16、=REPLACEB(F2,4,5,”****”)
一句话介绍：将F2单元格中第4个字开始的4个字换成*（以字节为单位）
函数格式：REPLACEB(原字符串,开始位置,字节个数,新字符串)
17、=REPT(“ABC”,5)
一句话介绍：将字符串ABC重复五次
函数格式：REPT(原字符串,重复次数)
18、=TRIM(A1)
一句话介绍：清理A1单元格中多余的空格
函数格式：TRIM(要处理的字符串)
19、=CLEAN(A1)
一句话介绍：删除A1单元格中所有非打印字符
函数格式：CLEAN(要处理的字符串)
20、=CHAR(65)
一句话介绍：得到系统代码65所对应的字符A
函数格式：CHAR(数字)
21、=CODE(“A”)
一句话介绍：得到字符A所对应的系统代码65
函数格式：CODE(字符)
22、=CONCATENATE(A2,”营业额”,B2,”元”)
一句话介绍：得到一个合并后的字符串
函数格式：CONCATENATE(字符串1,字符串2,……)
23、=PHONETIC(A1:C1)
一句话介绍：将单元格区域中的文本进行合并
函数格式：PHONETIC(单元格区域)
24、=CONCAT(A2:D2)
一句话介绍：将单元格区域中的内容进行合并
函数格式：CONCAT(单元格区域)
25、=TEXTJOIN(”-”,1,A2:D2)
一句话介绍：将单元格区域中的内容进行合并，并且以”-”分隔。
函数格式：TEXTJOIN(分隔符,是否忽略空单元格,合并内容1,合并内容2,……)
1、=ISBLANK(A1)
一句话介绍：检验单元格中是否为有数据
函数格式：ISBLANK(需要进行检验的单元格)
2、=ISERROR(A1)
一句话介绍：检验单元格中的数据是否为错误值
函数格式：ISERROR(需要进行检验的单元格)
3、=ISERR(A1)
一句话介绍：检验单元格中的数据是否为”#N/A”以外的错误值
函数格式：ISERR(需要进行检验的单元格)
4、=ISNA(A1)
一句话介绍：检验单元格中的数据是否为错误值#N/A
函数格式：ISNA(需要进行检验的单元格)
5、=ISEVEN(A1)
一句话介绍：检验单元格中的数据是否为偶数
函数格式：ISEVEN(需要进行检验的单元格)
6、=ISODD(A1)
一句话介绍：检验单元格中的数据是否为奇数
函数格式：ISODD(需要进行检验的单元格)
7、=ISLOGICAL(A1)
一句话介绍：检验单元格中的数据是否为逻辑值
函数格式：ISLOGICAL(需要进行检验的单元格)
8、=ISTEXT(A1)
一句话介绍：检验单元格中的数据是否为文本
函数格式：ISTEXT(需要进行检验的单元格)
9、=ISNONTEXT(A1)
一句话介绍：检验单元格中的数据是否为非文本
函数格式：ISNONTEXT(需要进行检验的单元格)
10、=ISNUMBER(A1)
一句话介绍：检验单元格中的数据是否为数值
函数格式：ISNUMBER(需要进行检验的单元格)
11、=ISREF(A1)
一句话介绍：检验单元格中的数据是否为引用
函数格式：ISREF(需要进行检验的单元格)
12、=ISFORMULA(A1)
一句话介绍：检验单元格中的数据是否为公式
函数格式：ISFORMULA(需要进行检验的单元格)
1、=ROW(A1)
一句话介绍：得到单元格的行号
函数格式：ROW(单元格)
2、=COLUMN(A1)
一句话介绍：得到单元格的列号
函数格式：COLUMN(单元格)
3、=ADDRESS(2,1,1,1,"汇总表")
一句话介绍：得到一个单元格地址：汇总表!$A$2
函数格式：ADDRESS(行号,列号,引用方式,引用样式,Sheet名称)
4、=CELL("type",A2)
一句话介绍：得到单元格A2的数据类型
函数格式：CELL(要获取的信息类型,单元格引用)
5、=INFO("DIRECTORY")
一句话介绍：当前目录或文件夹的路径
函数格式：INFO(要获取的信息类型)
1、=DAVERAGE(A1:D22,4,F13:G14)
一句话介绍：计算满足给定条件的列表或数据库的列中数值的平均值。
函数格式：DAVERAGE(数据源,要统计的列或字段,条件区域)
2、=DCOUNT(A1:D22,4,F13:G14)
一句话介绍：从满足给定条件的数据库记录的字段（列）中，计算数值单元格数目
函数格式：DCOUNT(数据源,要统计的列或字段,条件区域)
3、=DCOUNTA(A1:D22,4,F13:G14)
一句话介绍：对满足指定条件的数据库中记录字段（列）的非空单元格进行记数
函数格式：DCOUNTA(数据源,要统计的列或字段,条件区域)
4、=DGET(A1:D22,4,F13:G14)
一句话介绍：从数据库中提取符合指定条件且唯一存在的记录
函数格式：DGET(数据源,要统计的列或字段,条件区域)
5、=DMAX(A1:D22,4,F13:G14)
一句话介绍：返回满足给定条件的数据库中记录的字段（列）中数据的最大值
函数格式：DMAX(数据源,要统计的列或字段,条件区域)
6、=DMIN(A1:D22,4,F13:G14)
一句话介绍：返回满足给定条件的数据库中记录的字段（列）中数据的最小值
函数格式：DMIN(数据源,要统计的列或字段,条件区域)
7、=DPRODUCT(A1:D22,4,F13:G14)
一句话介绍：与满足指定条件的数据库中记录字段（列）的值相乘
函数格式：DPRODUCT(数据源,要统计的列或字段,条件区域)
8、=DSTDEV(A1:D22,4,F13:G14)
一句话介绍：根据所选数据库条目中的样本估算数据的标准偏差
函数格式：DSTDEV(数据源,要统计的列或字段,条件区域)
9、=DSTDEVP(A1:D22,4,F13:G14)
一句话介绍：以数据库选定项作为样本总体，计算数据的标准偏差
函数格式：DSTDEVP(数据源,要统计的列或字段,条件区域)
10、=DSUM(A1:D22,4,F13:G14)
一句话介绍：求满足给定条件的数据库中记录的字段（列）数据的和
函数格式：DSUM(数据源,要统计的列或字段,条件区域)
11、=DVAR(A1:D22,4,F13:G14)
一句话介绍：根据所选数据库条目中的样本估算数据的方差
函数格式：DVAR(数据源,要统计的列或字段,条件区域)
12、=DVARP(A1:D22,4,F13:G14)
一句话介绍：以数据库选定项作为样本总体，计算数据的总体方差
函数格式：DVARP(数据源,要统计的列或字段,条件区域)
1、=EVALUATE(Sheet1!A2)
一句话介绍：使用EVALUATE函数计算出A2单元格中算式的结果
函数格式：EVALUATE(需要计算的表达式)
2、=GET.WORKBOOK(38)
一句话介绍：得到当前工作表的名称
函数格式：GET.WORKBOOK(信息类型代码,工作簿名称)
3、=FILES("D:\ 我的文档 \*.JPG")
一句话介绍：可以获取我的文档目录下的 JPG 图片文件名列表
函数格式：FILES(指定路径)
4、=GET.DOCUMENT(50)
一句话介绍：可以获取当前工作表的总页数
函数格式：GET.DOCUMENT(信息类型代码)
5、=GET.CELL(63,Sheet1!B2)
一句话介绍：可以获得 Sheet1!B2 单元格填充色的数字代码
函数格式：GET.CELL(信息类型代码,单元格)
更多函数教程尽在《才能菜鸟的Excel函数修炼手册》