2020年终巨献,Excel公式大全!千万不要藏私,果断分享……

公众号回复2016   下载office2016

总有人在问:谁有Excel公式大全?

说有这个东西的都是忽悠人,无非就是用一些视频频率稍微高一点的公式代表了所谓的公式大全。

压根也没有这种东西!

因为问题一变,公式就会随着变化;数据内容一变,公式也会随着变化,数据呈现的方式一变,公式又得变化。

唯一不变的是公式的规则和函数的运用。

2020年是不平凡的一年,《菜鸟的Excel函数修炼手册》一书也上市一年了,在年末的最后一天,将本书中涉及的函数罗列出来,姑且也算上一回【公式大全】吧……

点击文末阅读原文可以免费试读本书

以下分享10大类132个公式

一、匹配引用类
9个公式示例

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(链接地址,显示内容)

二、日期时间类
19个公式示例

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(开始日期,间隔天数,周末选项,特殊节假日列表)

三、逻辑判断类
4个公式示例

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,……)

四、数据统计类
29个公式示例

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,……)

五、数字处理类
12个公式示例

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(起始值,终止值)

六、文本处理类
25个公式示例

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,……)

七、信息判断类
12个公式示例

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(需要进行检验的单元格)

八、信息获取类
5个公式示例

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(要获取的信息类型)

九、数据库类函数
12个公式示例

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(数据源,要统计的列或字段,条件区域)

十、宏表类函数
5个公式示例

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

(0)

相关推荐