Excel函数公式汇总

01、进行函数书写前要注意的事项

1、函数公式三要素（等号、函数名、参数）

2、所有的标点符号输入都必须在英文法状态下输入

3、数值计算时所有单元格都必须是数值，文本不能相加

4、+ - * /  & 这五个符号分别是数值计算机的加减乘除和文本相加符号

5、填充柄的使用，双击单元格右下角的填充柄就可以按照当前单元格自动填充整列数据了。

6、绝对引用：选中单元格或者区域后按F4，笔记本电脑是fn+F4

7、混合引用：

• 固定列不变：在列英文字母前加$

• 固定行不变：在行数字前加$

02、sum求和函数

功能：对指定参数进行求和

函数格式：=sum(数据区域)

二级例题：

在“成绩表”中，计算每个学生的“总分”将其放入J2到J503单元中。

函数公式：=sum(E2:I2)

03、average求平均函数

功能：对指定参数进行求平均值

函数格式：=average(区域)

二级例题：

计算每个学生的“平均分”， 将其放入K2到K503单元中,并将区域K2:K503的数值格式设置为小数点后保留2位。

函数公式：=average(E2:I2)

04、if函数

if逻辑判断函数：根据逻辑判断是或否，返回两种不同的结果

函数格式：=if(逻辑判断语句,逻辑判断"是"返回的结果,逻辑判断"否"返回的结果)

初级：

二级例题：

根据“高等数学”列的数据，在F列求出每一个学生的高数等级，成绩>=60等级为及格，成绩<60等级为不及格。

函数公式：=if(E2>=60,"及格","不及格”）

中级：

二级例题：

根据“高等数学”列的数据，在F列求出每一个学生的高数等级

函数公式：

=if(E2>=90,"优秀",if(E2>=80,"良好",if(E2>=60,"及格",“不及格")))

高级：

函数公式：

=IF(P2-SUM(K2:N2)<=2000,0,IF(P2-SUM(K2:N2)<=4000,0+(P2-SUM(K2:N2)-2000)*0.03,IF(P2-SUM(K2:N2)<=8000,0+2000*0.03+(P2-SUM(K2:N2)-4000)*0.08,0+2000*0.03+4000*0.08+(P2-SUM(K2:N2)-8000)*0.1)))

注意事项：

（1）写IF函数的多层嵌套时,一定要注意不能少括号,括号是成对出现的

（2）条件或者返回结果为文本时,一定要加双引号

05、ifs函数

Ifs逻辑判断函数：检查是否满足一个或多个条件并返回对应条件对应的真值

函数格式：=ifs（条件1，结果1，条件2，结果2，……）

二级例题：

函数公式：

=ifs(J2="无优惠",I2*100%,J2="普通",I2*95%,J2="VIP",I2*85%,J2="SVIP",I2*80%）

ifs这个函数是WPS二级新增题型，如果你考MS office则不用ifs这个公式

06、sumifs多条件求和函数

sumifs多条件求和函数：对满足多个条件的数据进行求和

函数格式：=sumifs(求和区域,条件区域1,条件1,条件区域2,条件2)

二级例题：

(1)在"差旅成本分析报告"工作表B3单元格中，统计2013年第二季度发生在北京市的差旅费用总金额。

函数公式：

=SUMIFS(费用报销管理!G2:G401,费用报销管理!D2:D401,"北京市",费用报销管理!A2:A401,">=2013-4-1",费用报销管理!A2:A401,"<2013-7-1")

(2)在"差旅成本分析报告“工作表B4单元格中，统计2013年员工钱顺卓报销的火车票费用总额。

函数公式：

=SUMIFS(费用报销管理!G2:G401,费用报销管理!B2:B401,"钱顺卓",费用报销管理!F2:F401,"火车票")

注意事项：求和区域与条件区域的行数一定要对应相同。

07、averageifs多条件求平均

=averageifs（实际求平均区域,条件区域 1,条件 1,条件区域 2,条件 2，…）

功能：求满足多个条件的数值平均值，函数使用类似sumifs

二级例题1：

根据总分列，分别在M2和N2计算出男同学和女同学的总平均分

函数公式：averageifs(J2:J503,C2:C503,"女")

二级例题2：

根据总分列，分别在M5和N5计算出一班男同学和二班男同学的总平均分

函数公式：=averageifs(J2:J503,D2:D503,"一班",C2:C503,"男"）

08、max求最大值函数

max求最大值函数：求指定区域中的最大值

函数格式：=max(区域)

二级例题：根据总分列，在K2单元格求出总分第1名分数

函数公式：=max(J2:J503)

09、large求第n大值函数

large函数：求指定区域中的第L大值

函数格式：=large(区域,返回第几个最大值)

二级例题：根据总分列，在K2单元格求出总分第2名分数

函数公式：=large(J2:J503,2)

10、min求最小值函数

min求最小值函数：求指定区域中的最小值

函数格式：=min(区域)

二级例题：根据总分列，在K2单元格求出总分倒数第1名的分数

函数公式：=max(J2:J503)

11、small函数：求指定区域中的第n小值

函数格式：=small(区域,返回第几个最小值)

题目要求：根据总分列，在K2单元格求出总分倒数第2名分数

函数公式：=small(J2:J503,2)

12、int取整函数

int取整函数：对指定数字向下取整，把全部的小数都去掉，只保留整数

函数格式：=int(数值)

题目要求：求出平时成绩列的整数成绩

函数公式：=int(C2)

13、round四舍五入函数

round四舍五入函数：对指定数字进行四舍五入

函数格式：=round(数值，保留小数位数)

题目要求：根据平时成绩列的数据，四舍五入取整放到E列

函数公式：=round(C2,0)

14、roundup向上取整函数

roundup向上取值函数：对指定数字进行向上取值

函数格式：=roundup(数值,保留小数位数)

题目要求：根据平时成绩列的数据，向上取整只保留2位小数放到E列

函数公式：=roundup(C2,2)

注意事项：本函数可以用来根据月份求季度

15、rounddown向下取整函数

rounddown向下取值函数：对指定数字进行向下取值

题目要求：根据平时成绩列的数据，向上取整只保留2位小数放到E列

函数公式：=rounddown(C2,2)

注意事项：本函数和上一个函数的典型考法在停车费的题目

二级例题：

依据停放时间和收费标准，计算当前收费金额并填入“收费金额"列;计算拟采用的收费政策的预计收费金额并填入“拟收费金额”列;计算拟调整后的收费与当前收费之间的差值并填入“差值”列.

函数公式：

=ROUNDUP(J2*24*60/15,0)*E2

=ROUNDDOWN(J2*24*60/15,0)*E2

=L2-K2

16、sqrt开平方根函数

sqrt开平方根函数：求一个非负实数的平方根

函数格式：=sqrt(数值)

函数公式：=sqrt(A2)

17、mod求余函数

mod求余函数：求某个数字除以另一个数字的余数

函数格式：=mod(被除数,除数)

函数公式：=sqrt(D2,2)

注意事项：求余函数通常用于判断奇偶

18、count求个数函数

count求个数函数：求指定区域中数值单元格的个数

函数格式：=count(区域)

函数公式：=count(A1:A7)

19、countifs多条件求个数函数

countifs多条件求个数函数：求指定区域中满足多个条件的单元格个数

函数格式：=countifs(区域1,条件1,区域2,条件2)

二级例题：

利用“性别”列和“合同种类”列数据，使用countifs函数完成C26:C29单元格的计算

函数公式1：=countifs(B2:B21,"男",C2:C21,"合同工")

函数公式2：=countifs(B2:B21,A26,C2:C21,B26)

20、rank求排名函数

rank排名函数：求某个数据在指定区域中的排名

函数格式：=rank(排名对象,排名的数据区域，降序或者升序)

二级例题

注意事项：

（1）第二参数一定要绝对引用

（2）&为文本连接符号

（3）第三参数通常省略不写，0和默认都为降序排名，如果要升序排名，则第3个参数写1。

21、rank.eq函数

Rank和rank.eq的用法一致。rank是Excel早起版本就有的函数，而rank.eq是Excel2010才开始出现的，同时增加了rank.avg函数，微软准备用rank.eq替换rank函数，以避免与rank.avg混淆。将来的某个版本可能就不会再用rank函数了，现在的版本之所以保留rank，是为了与低版本Excel兼容。

22、left从左侧取文本函数

left从左侧取文本函数：从文本左侧起提取文本中的指定个数的字符

函数格式：=left(要提取的字符串,提取的字符数)

二级例题：

使用公式统计每个活动地点所在的省份或直辖市，并将其填写在"地区"列所对应的单元格中，例如"北京市”、“浙江省"。

函数公式：=left(C3,3)

23、mid从中间取文本函数

mid从中间取文本函数：从文本中间提取文本中的指定个数的字符

函数格式：=mid(要提取的字符串，从第几位开始取，提取的字符数)

二级例题：

使用公式统计每个活动地点所在的省份或直辖市，并将其填写在"地区"列所对应的单元格中，例如"北京市”、“浙江省"。

函数公式：=mid(C3,1,3)

注意事项：

（1）mid函数提取的结果是文本，不能直接参与计算，如要参与数值计算需先+0进行转换

24、right从右侧取文本函数

right从右侧取文本函数：从文本右侧起提取文本中的指定个数的字符

函数格式：=right(要提取的字符串,提取的字符数)

=right(A2,2)

25、find定位函数

find定位函数：计算指定字符在指定字符串中的位置

函数格式：=find(指定字符,字符串,开始进行查找的字符数)

二级例题：

注意事项：

（1）find函数第三参数一般省略不写，第一参数要加双引号

（2）find函数求出指定字符所在的位置，通常情况下都会再与别的函数嵌套使用

（3）&为文本连接符

（4）"[@政策名称])+1"这个+1，表示从"〔"这个符号的后一位开始取。

26、mid,mod,if嵌套

二级例题：在工作表"初三学生档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”

函数公式：=if(mod(mid([@身份证号码,17,1),2)=1,"男”,"女")

解析：身份证第17位数是奇数代表姓名为男，偶数代表性别为女，所以这题的函数嵌套逻辑是这样的，先用mid函数将身份证的第17位数取出来，然后用mod函数除以2求余数，然后根据余数用if函数来进行判断，如果余数等于1的话，就输出“男”，否则输出“女”。

27、today()求当前日期函数

today求当前日期函数：求电脑系统中今天的日期

函数公式：=today()

28、year求年份函数

year求年份函数：求指定日期的年份

函数格式：=year(日期)

函数公式：=year(C2)

29、month求月份函数

month求月份函数：求指定日期的月份

函数格式：=month(日期)

函数公式：=month(C2)

30、day求日期号函数

函数公式：=day(C2)

31、date求日期函数

date日期函数：将年月日三个值转变成日期格式

函数格式：=date(年,月,日)

函数公式：=date(D2,E2,F2)

32、datedif向上求日期间隔函数

datedif求日期间隔函数：计算两个日期之间的间隔(年/月/日)

函数格式：=datedif(起始日期,终止日期,返回类型)

二级例题：

在“员工绩效汇总”工作表的“工龄"列的空白单元格(G2:G201)中，输入公式，使用函数DATEDIF计算截至今日的“工龄”, 注意，每满一年工龄加1， ”今日”指每次打开本工作簿的动态时间。

函数公式：=datedif(F2,today(),"y")

注意事项：

（1）返回类型返回相距多少年用"y"，相距多少月用"m"，相距多少天用"d"，三种情况都要加双引号

（2）datedif是根据一年365天计算的满365天才算一年

33、days360()求日期间隔天数函数

days360求日期间隔函数：计算两个日期之间相隔的天数(一年按360天算)

函数格式：=days360(起始日期,终止日期)

二级例题：

在工作表"员工基础档案"中，利用公式及函数求每位员工截止2015年9月30日的年龄,年龄需要按周岁计算，满1年才计1岁，每月按30天、一年按360天计算。

函数公式：=int(days360([@出生日期],"2015-9-30")/360)

注意事项：days360是根据一年360天进行计算的

34、weekday求星期函数

weekday求星期函数：将某个日期所处的星期转换成数字

函数格式：=weekday(日期,返回类型)

二级例题：

如果"日期"列中的日期为星期六或星期日，则在"是否加班’列的单元格中显示"是"，否则显示否" (必须使用公式)。

函数公式：=if(weekday(A3,2)>5,"是","否")

注意事项：

（1）第二参数返回类型填写2是根据中国人习惯，星期一返回1，星期二返回2，以此类推

（2）weekday函数常用于跟if函数结合判断是否加班

35、vlookup函数

vlookup查询函数：在指定区域的首列沿垂直方向查找指定的值，返回同一行中的其他值

函数格式：=vlookup(查询对象,查询的数据区域,结果所在的列数,精确匹配或者近似匹配)

精确匹配：

初级

题目要求：根据"姓名"列内容，使用vlookup函数，生成"成绩"列内容。对照关系参照A1:C9

函数公式：=vlookup(G2,$B$1:$C$9,2,0)

二级例题：

依据"费用类别编号"咧内容,使用VL00KUP函数,生成"费用类别"列内容。对照关系参考"费用类别"工作表。

函数公式：=vlookup(E3,费用类型!$A$3:$B$12,2,0)

注意事项：

（1）查询目标必须位于查询数据区域的首列

（2）第二参数(查询的数据区域)要绝对引用

（3）第三参数写0代表精确匹配引用，写1代表近似匹配应用

近似匹配：

近似匹配的意思就是——在比查找值小的范围内匹配与之最接近的值。

vlookup近似匹配初级版

二级例题：

根据学生的成绩等级，查询学生的等级

函数公式：=vlookup(F2,$A$2:$B$6,2,1)

解析：在比查找值小的范围内匹配与之最接近的值。例如上题，58＜60，所以他只能匹配到成绩为0的等级；如果是62，62<80，62最接近60，所以他就匹配到成绩为60分那个等级。

vlookup近似匹配高级版

二级例题：

根据销售总额查询客户等级销售总额，如下题：

在“客户信息”工作表中，根据每个客户的销售总额计算其所对应的客户等级(不要改变当前数据的排序)，等级评定标准可参考“客户等级”工作表。

函数公式：=vlookup(sumifs(订单信息!G:G,订单信息!B:B,客户信息!A2),客户等级!$A$1:$B$11,2,1)

36、lookup函数

lookup数组查询函数：利用数组构建查询区域和结果区域实现查询

函数格式：=lookup(查询对象,查询的数据区域,结果的数据区域)

二级例题：

在“2012级法律”工作表中,利用公式、根据学生的学号、将其班级的名称填入“班级”列,规则为:学号的第三位为专业代码、第四位代表班级序号,即01为“法律-班"，02为“法律二班”，03为“法律三班”, 04为“法律四班”

函数公式

=LOOKUP(MID([@学号],3,2),{"01","02","03","04"},{"法律一班","法律二班","法律三班","法律四班"})

注意事项：

（1）查询的数据区域与结果的数据区域要一一对应

（2）查询的数据区域和结果的数据区域要用{}数组括号

37、index

index函数：查找指定区域中指定行与指定列的单元格

函数格式：=index(查询的数据区域,返回的行号,返回的列号)

题目要求：提取第3行第5列的数据出来

=index($A$1:$E$5,3,5)

38、match函数

match函数：查找指定值在指定区域中的位置

函数格式：=match(查询对象,查询的数据区域,精确匹配或者近似匹配)

函数公式：=match("电脑",A:A,0)

注意事项：第3个参数输入0就是精确匹配

39、index和match函数组成二维查询

二级例题：

在S3单元格中建立公式，使用Index函数和Match函数,根据R3单元格中的城市名称和S2单元格中的月份名称,查询对应的降水量;以上三个单元格最终显示的结果为广州市7月份的降水量。

函数公式：=INDEX(降水量统计[[1月]:[12月]],MATCH(R3,降水量统计[城市（毫米）],0),MATCH(S2,降水量统计[[#标题],[1月]:[12月]],0))

40、sumproduct函数

功能：积和函数，对应区域的单元格相乘，然后再对这些乘积求和

函数格式：=Sumproduct(区域1*区域2*…)

初级

题目要求：根据Ａ１：Ｃ１０的单元格数据，分别求出甲乙丙公司的销售额

高级

二级例题：

在"2013年图书销售分析”工作表中，统计2013年各类图书在每月的销售量，并将统计结果填充在所对应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。

函数公式：

41、数组函数

功能：数组函数是用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。

二级例题：

利用“成绩单” 、“小分统计" 和"分值表”工作表中的数据，完成”按班级汇总"和“按学校汇总”工作表中相应空白列的数值计算。具体提示如下:

(1)“考试学生数”列必须利用公式计算，“平均分” 列由“成绩单”工作表数据计算得出;

(2)“分值表”工作表中给出了本次考试各题的类型及分值。(备注: 本次考试一共50道小题，中[1]至[40]为客观题，[41]至[50]为主观题) ;

函数公式：=MAX(IF((成绩单!$A$2:$A$950=按班级汇总!A2)*(成绩单!$B$2:$B$950=按班级汇总!B2),成绩单!$D$2:$D$950))

注意：

（1）数组公式的特点就是所引用的参数是数组参数，包括区域数组和常量数组。执行多重计算,它返回的是一组数据结果。
（2）输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl+Shift+Enter组合键运行数组。

42、Excel工作表常用快捷键

各位小伙伴给我们的微信公众号发送“Excel素材”就可以下载素材练习相应的函数公式了。

为了系统地汇总Excel函数公式，我们查阅了所有的计算机二级真题，经过三天的整理、分类、筛选、修改，反复的校对，我们希望自己整理出的知识点能够帮助更多的人学习office技能考取计算机二级证书。

这是我们第一次尝试整理分享这些知识点，然后我们也不太懂得以怎样的方式跟大家分享，希望大家多提提意见，怎么样分享你们才让更加容易接受。接下来几天我们将根据整理出的知识点和素材录制24个短视频课程上传到我们的哔哩哔哩账号上，欢迎大家扫码关注~

哔哩哔哩账号二维码

往期回顾