《2022年完整word版,EXCEL常用函数公式大全及举例》由会员分享,可在线阅读,更多相关《2022年完整word版,EXCEL常用函数公式大全及举例(8页珍藏版)》请在金锄头文库上搜索。
1、 1 EXCEL 常用函数公式大全及举例一、相关概念(一)函数语法由函数名 + 括号+ 参数组成例:求和函数: SUM (A1,B2,) 。参数与参数之间用逗号“ ,”隔开(二)运算符1. 公式运算符:加( + ) 、减(-) 、乘(*) 、除(/) 、百分号( %) 、乘幂()2. 比较运算符:大与( ) 、小于( ) 、等于( =) 、小于等于( = ) 、不等于( )3. 引用运算符:区域运算符(: ) 、联合运算符( ,)(三)单元格的相对引用与绝对引用例:A1 $A1 锁定第 A 列A$1 锁定第 1 行$A$1 锁定第 A 列与第 1 行二、常用函数(一)数学函数1. 求和=SUM
2、 (数值 1,数值 2,)2. 条件求和=SUMIF (查找的范围 ,条件(即对象 ),要求和的范围)例: (1)=SUMIF(A1:A4, ” =200 ” ,B1:B4) 函数意思:对第A1 栏至 A4 栏中,大于等于200 的数值对应的第B1 列至 B4 列中数值求和精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 1 页,共 8 页 2 (2)=SUMIF(A1:A4, ” 300 ” ,C1:C4) 函数意思:对第 A1 栏至 A4 栏中,小于 300 的数值对应的第 C1 栏至 C4栏中数值求和3. 求个数=COUNT (数值 1,数值
3、2,)例: (1)=COUNT(A1:A4) 函数意思:第 A1 栏至 A4 栏求个数(2)=COUNT(A1:C4) 函数意思:第 A1 栏至 C4 栏求个数4. 条件求个数=COUNTIF (范围,条件)例: (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
4、. 四舍五入函数=ROUND( 数值,保留的小数位数 ) 7. 排位函数=RANK (数值 ,范围,序别) 1- 升序0- 降序例: (1)=RANK(A1,A1:A4,1) 函数意思:第 A1 栏在 A1 栏至 A4 栏中按升序排序,返回排名值。(2)=RANK(A1,A1:A4,0) 函数意思:第 A1 栏在 A1 栏至 A4 栏中按降序排序,返回排名值。8. 乘积函数=PRODUCT (数值 1,数值 2,)9. 取绝对值=ABS( 数字) 10. 取整=INT( 数字) 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 2 页,共 8 页 3
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( 文本,开始位,截取长度 )从中间截取2. 计算字符长度: LEN(文本)文本的长度3. 合并字符函数: CONCATENATE( 文本 1,文本 2,) 4. 在字符串中查找特定字符:FIND( 文本,范围,数值)数值表示查找第几个=FIND( “ a” ,
6、“ 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. 将数值型字符转换为数值:VALUE(数值文本 ) (四)判断公式精选学习资料 - - - - - - - - - 名师归
7、纳总结 - - - - - - -第 3 页,共 8 页 4 1. 把公式产生的错误显示为空公式: C2=IFERROR(A2/B2, ” ”) 说明:如果是错误值则显示为空,否则正常显示。2. IF 多条件判断返回值公式: C2=IF(AND(A2500,B2=”未到期” ),”补款” ,” ”) 说明:两个条件同时成立用AND ,任一个成立用OR 函数。(五)统计公式1. 统计两个表格重复的内容公式: B2=COUNTIF(Sheet15!A:A,A2) 说明:如果返回值大于0 说明在另一个表中存在,0 则不存在。精选学习资料 - - - - - - - - - 名师归纳总结 - - -
8、- - - -第 4 页,共 8 页 5 2. 统计不重复的总人数公式: C2=SUMPRODUCT(1/COUNTIF(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 个公式精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 5 页,共 8 页 6 2. 单条件求
9、和3. 单条件模糊求和4. 多条件模糊求和5. 多表相同位置求和公式: B2=SUM(Sheet1:Sheet19!B2) 说明:在表中间删除或添加表后,公式结果会自动更新。6. 按日期和产品求和(七)查找与引用公式1. 单条件查找公式公式 1: C11=VLOOKUP(B11,B3:F7,4,FALSE) 说明:查找是 VLOOKUP 最擅长的,基本用法精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 6 页,共 8 页 7 2. 双向查找公式公式=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0) 说明:利用 MATCH 函数查找位置,用INDEX 函数取值3. 查找最后一条符合条件的记录。公式:详见下图说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 7 页,共 8 页 8 4. 多条件查找公式:详见下图说明:公式原理同上一个公式5. 指定区域最后一个非空值查找6. 按数字区域间取对应的值精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 8 页,共 8 页