Excel函数公式汇总

卓越

Excel函数公式汇总

#ComputerLevel#倒计时11天

这些Excel函数公式你掌握了吗？

01编写函数之前要考虑的要点

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

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

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

(4)-*/这五个符号是数字计算机对文本进行加、减、乘、除、加的符号。

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

(6)绝对引用：选择单元格或区域后按F4，即fnF4。

(7)混合参考：

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

固定订单不变：订单号前添加$

02求和函数

功能：对指定参数求和

函数格式：sum（数据字段）

2级样题：

1在计算表上完成以下计算：

计算每个学生的“总分”并将其放入J2至J503单元中。

函数公式：sum(E2:I2)

03平均功能

功能：对显示参数进行平均

特征格式：平均值（区域）

2级样题：

计算每个学生的“平均分”，将其放入单位K2至K503中，并将K2:K503字段中的数值格式化以保存2个小数位。

函数公式：mean(E2:I2)

04如果有功能

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

函数格式：if(布尔表达式，布尔返回yes，布尔返回no)

主要的：

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函数是WPS2级中的新问题类型。如果您参加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多条件平均

平均值（实际平均区域、条件区域1、条件1、条件区域2、条件2，）

功能：求满足多个条件的值的平均值。

2级示例问题1：

根据总成绩栏，分别计算男学生和女学生的M2和N2总平均分。

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

2级示例问题2：

在M5和N5中分别按总分计算1班男生和2班男生的总平均分

函数公式：midlevels(J2:J503,D2:D503,'1级',C2:C503,'男')

08max求最大值函数

max函数求最大值：求指定字段中的最大值

函数格式：max(面积)

2级示例：根据总分列查找单元格K2中的第一名分数

函数公式：max(J2:J503)

09求第n大值函数

Large函数：查找指定字段中的第L个最大值

函数格式：large（字段，返回最大值）

二级样题：根据总分列，在单元格K2中找出总分中第二名的分数。

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

10Min求最小成本函数

min函数求最小值：求指定字段中的最小值

函数格式：min(字段)

2级示例：根据总分列查找单元格K2中最低的分数

函数公式：max(J2:J503)

11小函数：查找指定字段中第n个最小值

函数格式：small(字段，返回最小值)

题目要求：根据总分列求出单元格K2中倒数第二个总分

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

12int舍入函数

int舍入函数：将指定数字向下舍入，丢弃所有小数并仅保留整数。

函数格式：int（数值）

题目要求：求常规分数栏的总分

函数公式：int(C2)

13循环舍入函数

round函数：对显示的数字进行四舍五入

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

题目要求：根据常规账户栏信息，四舍五入后放入E栏。

函数公式：round(C2,0)

14舍入函数

向上取值功能：对指定号码进行向上取值选择

函数格式：求和（数值，保存小数位）

题目要求：根据普通账户栏信息，四舍五入后只保留小数点后2位，放入E栏。

函数公式：泛化(C2,2)

注：该函数可用于根据月份查找季度

15向下舍入功能

向下舍入值函数：将指定数字的值向下舍入

函数格式：四舍五入（数值，保留小数位）

题目要求：根据普通账户栏信息，四舍五入后只保留小数点后2位，放入E栏。

函数公式：round(C2,2)

注：此功能和前一个功能的典型测试方法是停车费。

2级样题：

根据停车时间和缴费标准，计算当前缴费金额并计算出拟接受资费的预计缴费金额并填写“建议缴费金额”栏；建议修改后的付款额与当前付款额之间的差额，并填写“差额”栏。

函数公式：

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

良好的回报（J2*24*60/150）*E2

L2-K2

16sqrt平方根函数

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

函数格式：sqrt（数值）

函数公式：sqrt(A2)

17mod残差函数

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

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

函数公式：sqrt(D2,2)

注：rest函数通常用于定义奇数和偶数

18number函数查找号码

count函数：查找指定范围内数字单元格的数量

函数格式：count（字段）

函数公式：count(A1:A7)

19计算多项式以找到数字的函数

countifs多条件计数功能：查找给定字段中满足多个条件的单元格数量

函数格式：count(字段1,条件1,字段2,条件2)

2级样题：

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

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

像这样编写这个问题的公式更容易，因此您可以使用填充柄来填充。

数字(B2:B21,A26,C2:C21,B26)

20Rank求排名函数

排序功能：查找某些数据在指定区域的排名

函数格式：rank（排序对象、排序数据字段、降序顺序或升序）

2级示例

在工作表的S1单元格中输入“真实薪资排名”根据“实际薪资”栏中的数据，通过“真实薪资排名”栏中的公式或函数计算出真实薪资排名第一个是“第一”，第二个是“2号”等等。将显示为

函数公式：'RANK(R2,$R$2:$R$21,0)'

防范措施：

(1)第二个参数必须加引号

(2)组合文字的字符

(3)第三个参数一般省略不写0，如果要默认升序排序，则第三个参数写1。

21rankeq函数

使用rank和rankeq是一致的。Rank是Excel早期版本中提供的函数，而rankeq仅出现在Excel2010中。同时，微软计划将rankeq替换为rankeq以避免与rankavg混淆。未来某个版本可能不再使用rank功能。

22从左侧取左文本函数

getlefttext函数fromleft：从文本左侧减去指定数量的字符

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

2级样题：

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

函数公式：sol(C3,3)

23mid从mid获取文本函数

mid从中间取文本函数：从文本中间删除指定数量的字符

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

2级样题：

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

函数公式：mean(C3,1,3)

防范措施：

（1）平均函数输出的结果是文本，不能直接参与计算，如果要参与数值计算，必须先转换0。

24从右侧获取右侧文本函数

right函数从右侧提取文本：从文本右侧减去指定数量的字符

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

右（A2,2）

25求嵌入函数

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

函数格式：find（指定字符、字符串、开始搜索的字符数）

2级样题：

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

函数公式：MID([@PolicyName],FIND('〔',[@PolicyName])1,4)'年份'

防范措施：

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

(2)find函数定位指定字符。

(3)是文本连接器

(4)“[@策略名称])1”中的1表示从“〔”字符的最后一位开始。

26中型、现代、巢状

2级示例：使用公式和函数按“初三学生资料”工作表中的顺序输入每个学生的性别“男”或“女”

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

分析：身份证第17位为奇数，表示姓名为男性，偶数表示性别为女性，所以本题函数嵌套逻辑如下：减去身份证第17位然后mod函数2-求除以的余数，然后根据余数使用if函数进行判断。，否则将输出“female”。

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

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

函数公式：today()

28yr求yr函数

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

函数格式：年（日期）

函数公式：年份(C2)

29月球发现月球功能

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

函数格式：月（日期）

函数公式：月(C2)

30day函数查找日期数字

函数式：day(C2)

31使用date查找日期函数

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

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

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

32datedif查找向上的日期范围函数

Datedif函数求日期间隔：计算两个日期之间的间隔（年/月/日）

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

2级样题：

在“员工活动摘要”工作表的“资历”列的空白单元格（G2：G201）中输入公式，并使用DATEDIF函数计算截至今天的“资历”，“今天”到每次此动态时间工作簿被打开属于。

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

防范措施：

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

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

33day360()函数查找天数之间的天数

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

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

2级样题：

使用公式和函数在员工主文件工作表中查找截至2015年9月30日每个员工的年龄。年龄应在一年后计算。每月按30天和360天计算。

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

注：day360是按一年360天计算

34Weekday查找一周的函数

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

函数格式：工作日（日期，返回类型）

2级样题：

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

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

防范措施：

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

（2）weekday函数经常与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(order_info!G:G,order_info!B:B,customer_info!A2),customer_level!$A$1:$B$11,2,1)

(1)查询目标必须位于查询数据字段的第一列

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

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

(4)第三个参数中输入0表示精确匹配参考，输入1表示近似匹配应用。

36搜索功能

搜索数组查询功能：使用数组构造查询字段和结果字段进行查询

函数格式：lookup（查询对象、查询数据字段、结果数据字段）

2级样题：

在《2012年班级法》工作表中，根据学生的学号，用公式在“班级”栏中填写该学生的班级名称：学号的第三位数字为职业代码。第四位数字代表类别序号，即01为“法律类别”，02为“法律类别2”，03为“法律类别3”，04为“法律类别”。

函数公式

SEARCH(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（查询对象、查询数据字段、精确匹配或近似匹配）

查询需求：查询值为“computer”的列位置。

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

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

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

2级样题：

在单元格S3中创建公式，使用Index函数和Match函数，根据单元格R3中的城市名称和单元格S2中的月份名称查询上面三个单元格中显示的最终结果；这是广州七月的降雨。

函数公式：INDEX(rainstats[[一月]:[十二月]],MATCH(R3,rainstats[城市(毫米)],0),MATCH(S2,rainstats[[#title],[一月]:[十二月]],0))

40最终函数

功能：乘积求和函数，将相应字段中的单元格相乘，然后将这些乘积相加

函数格式：Sumproduct(字段1*字段2*…)

最初

问题要求：根据单元格A1:C10中的数据，分别求出A、B、C公司的销售额。

函数公式：SUMPRODUCT((A2:A10'A')*(B2:B10)*(C2:C10))

先进的

2级样题：

在“2013年图书销售分析”工作表中，计算2013年不同类型图书的月销售量并将统计结果填写在相应的单元格中，在汇总行单元格中分别计算每月的图书销售总额。

函数公式：

产品((表1[图书名称][@图书名称])*(年份(表1[日期])2013)*(月份(表1[日期])1),表1[销售额(图书)])

41数组函数

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

2级样题：

使用成绩单、分数统计和分数表工作表中的信息完成班级摘要和学校摘要工作表上相应空白列的数值计算。

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

(2)“分数表”工作表给出了本次考试每道题的类型和分数。（注：本次考试共50题，包括客观题[1]至[40]和主观题[41]至[50]）；

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

警告：

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

（2）要输入数组公式，首先要选择用于存储结果的单元格范围（可以是单元格），在编辑栏中输入公式，然后按CtrlShiftEnter组合键运行数组。

42EExcel工作表常用快捷键

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

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

所有朋友关注我们的百家账号后，私信发送“Excel素材”，即可自动获取链接并下载素材，应用对应的函数公式。

为了系统总结Excel函数公式，我们复习了二级计算机真题。获得办公计算机技能二级证书。

这是我们第一次尝试整理和分享这些知识点，不知道如何与大家分享，希望大家更容易接受。在接下来的几天里，我将根据整理的知识点和资料编写24个短视频课程并上传到我们的百度帐号，任何人都可以关注我的百家帐号看到更多有趣的内容。

欢迎大家关注我的百家号，查看更多有趣的内容。