Excel函数公式汇总（收藏）

01编写函数之前需要考虑的事项

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

2所有标点符号必须以英文模式输入。

3计算数值时，所有单元格必须是数值，不能添加文字。

4、-*/这五个符号分别是数值计算机的加减乘除和文字加法符号。

5、双击单元格右下角的填充柄，即可根据当前单元格自动填充整个数据列。

6绝对参考：选择单元格或范围后按F4。在笔记本电脑上是FnF4。

7混合参考：

固定列保持不变：在列英文字母前添加$

更正后的行不变：在行号前添加$

02总和功能

功能：对指定参数求和

函数格式：sum（数据范围）

2级问题示例：

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

函数公式：sum(E2:I2)

03平均平均功能

功能：指定参数的平均值

函数格式：平均值（区域）

2级问题示例：

计算每个学生的“平均分数”，将其分为单位K2到K503，并格式化K2:K503范围内的数值以保留两位小数。

函数式：Average(E2:I2)

04if函数

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

函数格式：if（逻辑判断语句，逻辑判断“是”返回结果，逻辑判断“否”返回结果）

基本的：

2级问题示例：

使用“高级数学”列中的数据，在F列中查找每个学生的高级数学成绩。>60的成绩被视为通过。

函数式：if(E2>60,'通过','失败')

之间：

2级问题示例：

使用“高级数学”列中的数据，在F列中找到每个学生的高级数学水平。

函数式：

if(E2>90,'优秀',if(E2>80,'良好',if(E2>60,'通过','不及格')))

进步：

函数式：

IF(P2-SUM(K2:N2)

防范措施：

(1)编写IF函数的多级嵌套时，注意不要省略成对出现的括号。

(2)如果条件或返回结果是文本，必须加双引号

05ifs函数

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

函数格式：ifs(条件1,结果1,条件2,结果2,)

2级问题示例：

函数式：

ifs(J2'无折扣',I2*100%,J2'正常',I2*95%,J2'VIP',I2*85%,J2'SVIP',I2*80%)

ifs函数是WPSLevel2中的新题型。如果您参加MSoffice考试，则不需要ifs公式。

06sumifs多条件求和函数

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

函数格式：Sumifs（求和范围、条件范围1、条件1、条件范围2、条件2）

2级问题示例：

（1）在《差旅费分析报告》工作表的B3单元格中，统计2013年第二季度在北京发生的差旅费总额。

函数式：

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

（2）在《差旅费用分析报告》工作表的B4单元格中，统计员工钱顺卓2013年报销的火车票费用总额。

函数式：

SUMIFS（费用报销管理！G2：G401，费用报销管理！B2：B401，‘钱顺着’，费用报销管理！F2：F401，‘火车票’）

注意：求和区和条件区的行数必须相同。

07Averageifs多个条件的平均

Averageifs（实际平均范围、条件范围1、条件1、条件范围2、条件2、）

功能：求满足多个条件的值的平均值。该功能与Sumif类似。

2级示例问题1：

使用总分列，分别计算男女学生M2和N2的平均总分。

函数式：Averageifs(J2:J503,C2:C503,'女')

2级示例问题2：

使用总分列，分别计算M5和N51班男生和2班男生的平均总成绩。

函数式：Averageifs(J2:J503,D2:D503,'1级',C2:C503,'男')08max求最大值函数

Max函数求最大值：求指定范围内的最大值

函数格式：max(面积)

第2阶段示例：使用总分列确定单元格K2中的第一名分数

函数式：max(J2:J503)

09large查找第n大值的函数

big函数：求指定范围内的第L个最大值

函数格式：large（面积，返回最大值）

2级示例问题：使用总分列，确定单元格K2中总分中的第二名分数

功能式：大(J2:J5032)

10min求最小值函数

Min函数求最小值：求指定范围内的最小值

函数格式：min(面积)

级别2的示例：使用总分列，确定单元格K2中底部列的分数

函数式：max(J2:J503)

11小函数：求指定范围内第n个最小值

函数格式：small（范围，返回最小值）

问题要求：使用“总分”栏确定单元格K2中倒数第二个总分

功能式：小(J2:J5032)

12int舍入函数

int四舍五入函数：对指定数字进行四舍五入，删除所有小数位并仅保留整数。

函数格式：int（数值）

题目要求：求平时分数列的整数分数

函数式：int(C2)

13四舍五入功能

Round舍入功能：对指定数字进行四舍五入

函数格式：round（数值，保留小数位）

题目要求：根据常用分栏的数据，四舍五入后填入E栏。

函数式：round(C2,0)

14Roundup舍入功能

数值舍入功能：对指定数字进行向上数值选择

函数格式：向上取整（数值，保留小数位）

题目要求：以常用分栏数据为基础，四舍五入，只保留小数点后两位，填入E栏。

函数式：Roundup(C2,2)

注：该函数可用于根据月份确定季度

15向下舍入功能

下舍入值功能：指定数字的下舍入值

函数格式：向下取整（数值，保留小数位）

题目要求：以常用分栏数据为基础，四舍五入，只保留小数点后两位，填入E栏。

函数式：舍入(C2,2)

注意：此功能和上一个功能的典型测试方法解决了停车费问题。

2级问题示例：

根据停车时间和收费标准，计算当前收费金额并填写“收费金额”栏。计算适用收费政策的预期收费金额并填写建议收费金额列，将建议的调整后收费与当前收费进行比较并填写差异列。

函数式：

综述(J2*24*60/150)*E2

向下舍入(J2*24*60/150)*E2

L2-K2

16平方根函数sqrt

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

函数格式：sqrt（数值）

函数公式：sqrt(A2)

17Mod休息功能

Mod余数函数：求一个数除以另一个数后的余数

函数格式：mod（被除数，除数）

函数公式：sqrt(D2,2)

注：余数函数通常用于判断奇数和偶数

18计数功能查找数量

计数功能：获取指定范围内数字单元格的个数

函数格式：计数（面积）

函数公式：count(A1:A7)

19、统计多个条件求数函数

countifs多条件计数功能：查找给定范围内满足多个条件的单元格数量

函数格式：countifs(范围1，条件1，范围2，条件2)

2级问题示例：

使用“性别”和“合同类型”列中的数据，通过Countifs函数完成单元格C26:C29的计算

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

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

20Rank求排名函数

排名功能：查找特定数据在指定范围内的排名

函数格式：rank（排名对象、排名数据范围、降序或升序）

2级示例

在工作表的单元格S1中输入“实际薪资排名”。利用“实际工资总工资”一栏的数据，用公式或函数计算出实际工资排名为1，显示“第1名”。1”，总排名第二的被称为“No1”。显示2”等。

防范措施：

(1)第二个参数必须用引号括起来

(2)连接文本符号

(3)第三个参数通常省略，不写为0。如果要升序排列，则第三个参数写1。

21rankeq函数

Rank和rankeq的使用是一致的。Rank是Excel早期版本中存在的函数，而rankeq仅出现在Excel2010中。同时添加了rankavg功能。微软计划用rankeq取代rank函数，以避免与rankavg混淆。特定版本可能不再使用排名功能。当前版本保持排名的原因是为了与较低版本的Excel兼容。

22left接管左侧的文本功能

left执行从左开始的文本函数：从文本左侧提取指定数量的字符

函数格式：left（要提取的字符串、要提取的字符数）

2级问题示例：

使用公式计算每个场馆所在的省或直辖市，并将其输入到“地区”列对应的单元格中，例如：例如“北京市”、“浙江省”。

函数式：left(C3,3)

23mid接管中间的文本功能

midtakeonthetextfunctionfromthemiddle：从文本中间提取文本中指定数量的字符

函数格式：mid（要提取的字符串，从哪个位置开始，提取字符数）

2级问题示例：

使用公式计算每个场馆所在的省或直辖市，并将其输入到“地区”列对应的单元格中，例如：例如“北京市”、“浙江省”。

功能式：mid(C3,1,3)

防范措施：

（1）Mid函数提取的结果是文本，不能直接包含在计算中。如果要参与数值计算，首先要转换0。

24right接管右侧的文本功能

rightfunctiontotaketextfromright：从文本右侧的文本中提取指定数量的字符

函数格式：right（要提取的字符串、要提取的字符数）

右(A2,2)

25查找定位功能

查找定位功能：计算指定字符在指定字符串中的位置

函数格式：find（指定开始查找的字符、字符串和字符数）

2级问题示例：

在F列和G列之间插入一个空白列，并在列标题中键入“年份”。F栏“保单名称”中的年份通常用括号“〔〕”括起，例如“财税[2012]75号”中的“2012年”就是年份。从F列中的年份信息中获取年份，并将其输入到新插入的“年份”列中，该列将显示为“2012”。

函数公式：MID([@保单名称],FIND('〔',[@保单名称])1,4)'年份'

防范措施：

(1)搜索函数的第三个参数一般省略，第一个参数必须用双引号括起来。

(2)查找功能查找指定字符的位置。它通常与其他函数嵌套。

(3)是文本连接器

（4）“[@保单名称])1”中的1表示从符号“〔”最后一位开始。

26中间，嵌套时Mod

2级示例：在小学3学生档案工作表中，使用公式和函数输入每个学生的性别（男性或女性），一次一个。

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

分析：身份证第17位为奇数，表示姓名为男性，偶数表示性别为女性。因此，本题的嵌套逻辑如下：先用mean函数输出ID的第17位，然后用mod函数求余数2，然后用if函数根据余数进行判断。如果余数为1，则输出“male”。，否则输出“female”。

27Today()查找当前日期函数

Today函数查找当前日期：查找计算机系统中今天的日期

函数公式：today()

第28年求年份函数

Year函数查找年份：查找指定日期的年份

函数格式：年（日期）

函数公式：年份(C2)

29月求月份函数

Month函数查找月份：查找指定日期的月份

函数格式：月（日期）

函数公式：月(C2)

查找日期编号的第30天函数

函数式：Day(C2)

31使用日期查找日期函数

日期函数：将年月日三个值转换为日期格式

函数格式：日期（年、月、日）

函数公式：日期(D2,E2,F2)

32filef查找日期间隔函数up

filef函数查找日期间隔：计算两个日期之间的间隔（年/月/日）

函数格式：filef（开始日期、结束日期、返回类型）

2级问题示例：

在“员工绩效摘要”工作表的“资历”列的空白单元格(G2:G201)中，输入公式并使用DATEDIF函数计算截至今天的“资历”。请注意，每个服务年数加1“今天”是指每次打开此工作簿时的动态时间。

函数公式：filef(F2,today(),'y')

防范措施：

(1)返回类型用“y”返回年数，用“m”返回月数，用“d”返回天数。在所有三种情况下，都需要双引号。

(2)filef是按一年365天计算的。一年是按365天计算的。

33Days360()函数查找日期之间的天数

Days360日期间隔函数：计算两个日期之间的天数（一年按360天计算）

函数格式：Days360（开始日期、结束日期）

2级问题示例：

在“员工基本档案”工作表中，使用公式和函数确定截至2015年9月30日每位员工的年龄。年龄必须根据一岁计算。仅在一年后进行计数。每月按30天计算，一年按360天计算。

函数公式：int(days360([@birthdate],'2015-9-30')/360)

注：Days360是按每年360天计算

34Weekday查找工作日函数

Weekday函数查找星期几：将特定日期的星期转换为数字

函数格式：星期几（日期，返回类型）

2级问题示例：

如果“日期”列中的日期为周六或周日，“是否加班”列中的单元格将显示“是”，否则将显示“否”（必须使用公式）。

函数式：if(weekday(A3,2)>5,'yes','no')

防范措施：

(1)第二个参数的返回类型输入2是根据中国习惯，周一返回1，周二返回2，以此类推。

(2)星期几函数常与if函数结合使用，判断是否加班。

35Vlookup函数

vlookup查询功能：垂直方向查找指定范围第一列中的指定值，并返回同一行中的其他值

函数格式：vlookup（查询对象、查询数据范围、结果所在列数、精确匹配或近似匹配）

完全符合：

基本的

题目要求：根据Name列的内容，使用vlookup函数生成Score列的内容。比较关系信息参见A1:C9

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

2级问题示例：

根据“消费类别编号”的内容，使用函数VL00KUP生成“消费类别”栏的内容。比较关系可以在费用类别工作表中找到。

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

防范措施：

(1)查询目标必须在查询数据范围的第一列

（2）必须引用第二个参数（查询数据区）

(3)第三个参数写0表示精确匹配引用，写1表示近似匹配应用。

近似匹配：

近似匹配是在比搜索值更小的范围内匹配最接近的值。

vlookup大致匹配初级版本

2级问题示例：

根据学生成绩查询学生成绩

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

解析：匹配比搜索值更小的范围内最接近的值。比如上题，58<60，所以如果是62、62，他只能达到0分的水平

vlookup大致匹配扩展版本

2级问题示例：

根据总销量查询客户级别的总销量，如下：

在“客户信息”工作表中，根据每个客户的总销售额计算相应的客户等级（不要更改当前数据的排序）。评估标准请参阅“客户级别”工作表。

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

(1)查询目标必须在查询数据范围的第一列

(2)近似匹配查询范围的第一列必须与查询范围的第一列具有相同的数据类型。如果查询范围的第一列是区间值，则必须根据区间的最小值创建辅助列。

(3)第二个参数(查询数据区)必须引用

(4)第三个参数写0表示精确匹配引用，写1表示近似匹配应用。

36查找功能

Lookup数组查询功能：使用数组创建查询范围和结果范围来实现查询

函数格式:lookup（查询对象、查询数据区、结果数据区）

2级问题示例：

在2012年班级法工作表中，根据学生的学生证，使用公式输入学生的班级名称。规则是：学生证的第三位数字是职位。第四位数字代表类别序号，即H。01为“法律类别”，02为“法律类别2”，03为“法律类别3”，04为“法律类别4”。

函数式

LOOKUP(MID([@学号],3,2),{'01','02','03','04'},{'法律类别1','法律类别2','法律类别3','法律类别4'})

防范措施：

(1)查询数据区和结果数据区必须一一匹配。

(2)查询数据区和结果数据区必须使用{}数组括号

37索引

索引功能：搜索指定范围内指定行、指定列的单元格

函数格式：index（查询数据范围、返回行号、返回列号）

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

索引($A$1:$E$5,3,5)

38匹配功能

match函数：查找指定值在指定范围内的位置

函数格式：match（查询对象、查询数据范围、精确匹配或近似匹配）

问题要求：查询值为“计算机”的列位置。

函数公式：match('计算机',A:A,0)

注意：第三个参数输入0表示完全匹配。

39、索引和匹配函数形成二维查询

2级问题示例：

在S3单元格中创建公式，使用索引函数和匹配函数，根据R3单元格中的城市名称和S2单元格中的月份名称查询对应的降雨量，即广州市7月份的降雨量。

函数公式：INDEX(降水统计[[1月]:[12月]],MATCH(R3,降水统计[城市(mm)],0),MATCH(S2,降水统计[[#标题],[1月]:[十二月]],0))

40求和积函数

功能：乘积求和函数，将对应范围内的单元格相乘，然后将这些乘积相加

函数格式：和积(面积1*面积2*…)

基本的

问题要求：使用A1:C10中的单元格数据确定公司A、B和C的销售额。

进步

2级问题示例：

在2013年图书销售分析工作表中，统计2013年各类图书的月销量，并将统计结果输入到相应的单元格中。将汇总行添加到表中。单独计算每月图书销售总额。

函数式：

41数组函数

功能：数组函数用于创建单个公式，该公式可生成多个结果或对存储在行和列中的一组参数进行运算。

2级问题示例：

使用“成绩单”、“分数统计”和“分数表”工作表中的数据对“按年级汇总”和“按学校汇总”工作表中相应的空白列进行数值计算。

（1）“考试学生人数”栏采用公式计算；“平均分”栏根据“成绩单”上的数据计算得出；

(2)评分表工作表显示了本次考试每题的类型和评分。（注：本次考试共50题，其中[1]至[40]为客观题，[41]至[50]为主观题。）

函数公式：MAX(IF((Transcript!$A$2:$A$950按类总结！A2)*(Transcript!$B$2:$B$950按类总结！B2),Transcript!$D$2:$D$950))

观察：

(1)数组公式的特点是引用的参数是数组参数，包括面积数组和常量数组。执行多次计算并返回一系列数据结果。

(2)输入数组公式时，首先要选择将要用来存储结果的单元格范围（可以是单元格），在公式栏中输入公式，然后按CtrlShiftEnter组合键执行大批。

42Excel工作表的常规键盘快捷键

43下载练习材料和未来展望

下图中的Excel材料是上面每个函数公式示例的材料。

各位朋友，请将“Excel教材”发送至我们的微信公众号，下载教材并练习相应的函数公式。