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函数修炼手册》