EXCEL常用函数公式大全及举例_中学教育-高考.pdf

《EXCEL常用函数公式大全及举例_中学教育-高考.pdf》由会员分享，可在线阅读，更多相关《EXCEL常用函数公式大全及举例_中学教育-高考.pdf（8页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、EXCEL 常用函数公式大全及举例 一、相关概念（一）函数语法 由函数名+括号+参数组成 例：求和函数：SUM（A1,B2,)。参数与参数之 间用逗号“,”隔开（二）运算符 1.公式运算符：加（+）、减（-）、乘（*）、除（/）、百分号（%）、乘幂（）2.比较运算符：大与（）、小于（）、等于（=）、小于等于（=）、不等于（）3.引用运算符：区域运算符（：）、联合运算符（,）（三）单元格的相对引用与绝对引用 例：A1$A1 锁定第 A 列 A$1 锁定第 1 行$A$1 锁定第 A 列与第 1 行 二、常用函数（一）数学函数 1.求和 =SUM（数值 1,数值 2,）2.条件求和 =SUMIF（

2、查找的范围,条件(即对象),要求和的范围）例：（1）=SUMIF(A1:A4,”=200”,B1:B4)函数意思：对第 A1 栏至 A4 栏中，大于等于 200 的数值对应的第 B1 列至B4 列中数值求和（2）=SUMIF(A1:A4,”300”,C1:C4)函数意思：对第 A1 栏至 A4 栏中，小于 300 的数值对应的第 C1 栏至 C4栏中数值求和 3.求个数 =COUNT（数值 1,数值 2,）例：（1）=COUNT(A1:A4)函数意思：第 A1 栏至 A4 栏求个数 （2）=COUNT(A1:C4)函数意思：第 A1 栏至 C4 栏求个数 4.条件求个数 =COUNTIF（范围

3、,条件）例：（1）=COUNTIF(A1:A4，”200”)函数意思：第 A1 栏至 A4 栏中不等于 200 的栏求个数 （2）=COUNTIF(A1:C4,”=1000”)函数意思：第 A1 栏至 C4 栏中大于等 1000 的栏求个数 5.求算术平均数 =AVERAGE（数值 1,数值 2,）例：（1）=AVERAGE(A1,B2)（2）=AVERAGE(A1:A4)6.四舍五入函数 =ROUND(数值,保留的小数位数)7.排位函数 =RANK（数值,范围,序别）1-升序 0-降序 例：（1）=RANK(A1,A1:A4,1)函数意思：第 A1 栏在 A1 栏至 A4 栏中按升序排序，返

4、回排名值。（2）=RANK(A1,A1:A4,0)函数意思：第 A1 栏在 A1 栏至 A4 栏中按降序排序，返回排名值。8.乘积函数 =PRODUCT（数值 1,数值 2,）9.取绝对值 =ABS(数字)10.取整 =INT(数字)（二）逻辑函数 运算符公式运算符加减乘除百分号乘幂比较运算符大与小于等于小于等于大于等于不等于引用运算符区域运算符联合运算符三单元格的相对引用与绝对引用例锁定第列锁定第行锁定第列与第行二常用函数一数学函数求和数值数值条函数意思对第栏至栏中小于的数值对应的第栏至栏中数值求和求个数数值数值例函数意思第栏至栏求个数函数意思第栏至栏求个数条件求个数范围条件例函数意思第栏至

5、栏中不等于的栏求个数函数意思第栏至栏中大于等的栏求个数至栏中按升序排序返回排名值函数意思第栏在栏至栏中按降序排序返回排名值乘积函数数值数值取绝对值数字取整数字二逻辑函数条件函数条件执行条件执行条件假可执行七层嵌套例真假三文本函数截取函数文本截取长度从左边截条件函数：=IF（条件,执行条件,执行条件假）可执行七层嵌套 例：A1=5 A2=3 A3=6 A4=7=IF(A1A2,1,0)=1（真）=IF(A1A2,IF(A3A4,8,9),1)=9（三）文本函数 1.截取函数：=LEFT(文本,截取长度)从左边截取=RIGHT(文本,截取长度)从右边截取=MID(文本,开始位,截取长度)从中间截取

6、 2.计算字符长度：LEN(文本)文本的长度 3.合并字符函数：CONCATENATE(文本 1,文本 2,)4.在字符串中查找特定字符：FIND(文本,范围,数值)数值表示查找第几个=FIND(“a”,“abcadeafga”,2)=4=FIND(“a”,“abcadeafga”,3)=7=FIND(“a”,“abcadeafga”,4)=10 5.比较两个字符是否完全相符：EXACT(文本 1,文本 2)=EXACT(“a”,“a”)=TRUE=EXACT(“a”,“ab”)=FALSE=EXACT(“a”,“A”)=FALSE 6.将数值转化为文本：TEXT(数值,参数)参数一般为 0

7、7.将数值型字符转换为数值：VALUE(数值文本)（四）判断公式 1.把公式产生的错误显示为空 运算符公式运算符加减乘除百分号乘幂比较运算符大与小于等于小于等于大于等于不等于引用运算符区域运算符联合运算符三单元格的相对引用与绝对引用例锁定第列锁定第行锁定第列与第行二常用函数一数学函数求和数值数值条函数意思对第栏至栏中小于的数值对应的第栏至栏中数值求和求个数数值数值例函数意思第栏至栏求个数函数意思第栏至栏求个数条件求个数范围条件例函数意思第栏至栏中不等于的栏求个数函数意思第栏至栏中大于等的栏求个数至栏中按升序排序返回排名值函数意思第栏在栏至栏中按降序排序返回排名值乘积函数数值数值取绝对值数字取整

8、数字二逻辑函数条件函数条件执行条件执行条件假可执行七层嵌套例真假三文本函数截取函数文本截取长度从左边截公式：C2=IFERROR(A2/B2,”)说明：如果是错误值则显示为空，否则正常显示。2.IF 多条件判断返回值 公式：C2=IF(AND(A2500,B2=”未到期”),”补款”,”)说明：两个条件同时成立用 AND，任一个成立用 OR 函数。（五）统计公式 1.统计两个表格重复的内容 公式：B2=COUNTIF(Sheet15!A:A,A2)说明：如果返回值大于 0 说明在另一个表中存在，0 则不存在。运算符公式运算符加减乘除百分号乘幂比较运算符大与小于等于小于等于大于等于不等于引用运算

9、符区域运算符联合运算符三单元格的相对引用与绝对引用例锁定第列锁定第行锁定第列与第行二常用函数一数学函数求和数值数值条函数意思对第栏至栏中小于的数值对应的第栏至栏中数值求和求个数数值数值例函数意思第栏至栏求个数函数意思第栏至栏求个数条件求个数范围条件例函数意思第栏至栏中不等于的栏求个数函数意思第栏至栏中大于等的栏求个数至栏中按升序排序返回排名值函数意思第栏在栏至栏中按降序排序返回排名值乘积函数数值数值取绝对值数字取整数字二逻辑函数条件函数条件执行条件执行条件假可执行七层嵌套例真假三文本函数截取函数文本截取长度从左边截 2.统计不重复的总人数 公式：C2=SUMPRODUCT(1/COUNTIF(

10、A2:A8,A2:A8)说明：用 COUNTIF 统计出每人的出现次数，用 1 除的方式把出现次数变成分母，然后相加。（六）求和公式 1.隔列求和 公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3)或者：=SUMPRODUCT(MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第 2 个公式 运算符公式运算符加减乘除百分号乘幂比较运算符大与小于等于小于等于大于等于不等于引用运算符区域运算符联合运算符三单元格的相对引用与绝对引用例锁定第列锁定第行锁定第列与第行二常用函数一数学函数求和数值数值条函数意思对第栏至栏中小于的数值对应的第栏至栏中数值求和

11、求个数数值数值例函数意思第栏至栏求个数函数意思第栏至栏求个数条件求个数范围条件例函数意思第栏至栏中不等于的栏求个数函数意思第栏至栏中大于等的栏求个数至栏中按升序排序返回排名值函数意思第栏在栏至栏中按降序排序返回排名值乘积函数数值数值取绝对值数字取整数字二逻辑函数条件函数条件执行条件执行条件假可执行七层嵌套例真假三文本函数截取函数文本截取长度从左边截 2.单条件求和 3.单条件模糊求和 4.多条件模糊求和 5.多表相同位置求和 公式：B2=SUM(Sheet1:Sheet19!B2)说明：在表中间删除或添加表后，公式结果会自动更新。6.按日期和产品求和（七）查找与引用公式 1.单条件查找公式 公

12、式 1：C11=VLOOKUP(B11,B3:F7,4,FALSE)说明：查找是 VLOOKUP 最擅长的，基本用法 运算符公式运算符加减乘除百分号乘幂比较运算符大与小于等于小于等于大于等于不等于引用运算符区域运算符联合运算符三单元格的相对引用与绝对引用例锁定第列锁定第行锁定第列与第行二常用函数一数学函数求和数值数值条函数意思对第栏至栏中小于的数值对应的第栏至栏中数值求和求个数数值数值例函数意思第栏至栏求个数函数意思第栏至栏求个数条件求个数范围条件例函数意思第栏至栏中不等于的栏求个数函数意思第栏至栏中大于等的栏求个数至栏中按升序排序返回排名值函数意思第栏在栏至栏中按降序排序返回排名值乘积函数数

13、值数值取绝对值数字取整数字二逻辑函数条件函数条件执行条件执行条件假可执行七层嵌套例真假三文本函数截取函数文本截取长度从左边截 2.双向查找公式 公式=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0)说明：利用 MATCH 函数查找位置，用 INDEX 函数取值 3.查找最后一条符合条件的记录。公式：详见下图 说明：0/（条件）可以把不符合条件的变成错误值，而 lookup 可以忽略错误值 运算符公式运算符加减乘除百分号乘幂比较运算符大与小于等于小于等于大于等于不等于引用运算符区域运算符联合运算符三单元格的相对引用与绝对引用例锁定第列锁定第行锁定

14、第列与第行二常用函数一数学函数求和数值数值条函数意思对第栏至栏中小于的数值对应的第栏至栏中数值求和求个数数值数值例函数意思第栏至栏求个数函数意思第栏至栏求个数条件求个数范围条件例函数意思第栏至栏中不等于的栏求个数函数意思第栏至栏中大于等的栏求个数至栏中按升序排序返回排名值函数意思第栏在栏至栏中按降序排序返回排名值乘积函数数值数值取绝对值数字取整数字二逻辑函数条件函数条件执行条件执行条件假可执行七层嵌套例真假三文本函数截取函数文本截取长度从左边截 4.多条件查找 公式：详见下图 说明：公式原理同上一个公式 5.指定区域最后一个非空值查找 6.按数字区域间取对应的值 运算符公式运算符加减乘除百分号乘幂比较运算符大与小于等于小于等于大于等于不等于引用运算符区域运算符联合运算符三单元格的相对引用与绝对引用例锁定第列锁定第行锁定第列与第行二常用函数一数学函数求和数值数值条函数意思对第栏至栏中小于的数值对应的第栏至栏中数值求和求个数数值数值例函数意思第栏至栏求个数函数意思第栏至栏求个数条件求个数范围条件例函数意思第栏至栏中不等于的栏求个数函数意思第栏至栏中大于等的栏求个数至栏中按升序排序返回排名值函数意思第栏在栏至栏中按降序排序返回排名值乘积函数数值数值取绝对值数字取整数字二逻辑函数条件函数条件执行条件执行条件假可执行七层嵌套例真假三文本函数截取函数文本截取长度从左边截