《ecel常用函数公式大全及举例》由会员分享,可在线阅读,更多相关《ecel常用函数公式大全及举例(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(范围,条件)例:(1) =COUNTIF(A1:A4,”200
3、”) 函数意思:第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栏中按升序排序,返回排名值。 (2) =RANK(A1,A1:A4,0) 函数意思:第A1栏在A
4、1栏至A4栏中按降序排序,返回排名值。8. 乘积函数 =PRODUCT(数值1,数值2,)9. 取绝对值 =ABS(数字)10. 取整 =INT(数字)(二)逻辑函数条件函数:=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(
5、文本1,文本2,)4. 在字符串中查找特定字符:FIND(文本,范围,数值)数值表示查找第几个=FIND(“a”,“abcadeafga”,2)=4=FIND(“a”,“abcadeafga”,3)=7=FIND(“a”,“abcadeafga”,4)=105. 比较两个字符是否完全相符:EXACT(文本1,文本2)=EXACT(“a”,“a”)=TRUE=EXACT(“a”,“ab”)=FALSE=EXACT(“a”,“A”)=FALSE6. 将数值转化为文本:TEXT(数值,参数) )参数一般为07. 将数值型字符转换为数值:VALUE(数值文本)(四)判断公式1. 把公式产生的错误显示为
6、空公式:C2=IFERROR(A2/B2,”)说明:如果是错误值则显示为空,否则正常显示。2. IF多条件判断返回值公式:C2=IF(AND(A2500,B2=”未到期”),”补款”,”)说明:两个条件同时成立用AND,任一个成立用OR函数。(五)统计公式1. 统计两个表格重复的内容公式:B2=COUNTIF(Sheet15!A:A,A2)说明:如果返回值大于0说明在另一个表中存在,0则不存在。2. 统计不重复的总人数公式:C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。(六)求和公式
7、1. 隔列求和公式:H3=SUMIF($A$2: $G$2,H$2,A3:G3)或者: =SUMPRODUCT(MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明:如果标题行没有规则用第2个公式2. 单条件求和3. 单条件模糊求和4. 多条件模糊求和5. 多表相同位置求和公式:B2=SUM(Sheet1:Sheet19!B2)说明:在表中间删除或添加表后,公式结果会自动更新。6. 按日期和产品求和(七)查找与引用公式1. 单条件查找公式公式1: C11=VLOOKUP(B11,B3:F7,4,FALSE)说明:查找是VLOOKUP最擅长的,基本用法2. 双向查找公式公式=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0)说明:利用MATCH函数查找位置,用INDEX函数取值3. 查找最后一条符合条件的记录。公式:详见下图说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值4. 多条件查找公式:详见下图说明:公式原理同上一个公式5. 指定区域最后一个非空值查找6. 按数字区域间取对应的值 8