excel函数常用公式大全
本文已影响2.01W人
本文已影响2.01W人
excel函数常用公式大全,Excel是工作中最常用的工具之一,只要搞清楚它的一些使用小技巧,工作效率那是嗖嗖的往上蹭。以下分享excel函数常用公式大全,都是我们工作中经常用到的公式。
excel函数常用公式1
最常用的Excel函数公式大全
一、数字处理
1、取绝对值
=ABS(数字)
2、取整
=INT(数字)
3、四舍五入
=ROUND(数字,小数位数)
二、判断公式
1、把公式产生的错误值显示为空
公式:C2
=IFERROR(A2/B2,"")
说明:如果是错误值则显示为空,否则正常显示。
2、IF多条件判断返回值
公式:C2
=IF(AND(A2<500,B2="未到期"),"补款","")
说明:两个条件同时成立用AND,任一个成立用OR函数。
三、统计公式
1、统计两个表格重复的内容
公式:B2
=COUNTIF(Sheet15!A:A,A2)
说明:如果返回值大于0说明在另一个表中存在,0则不存在。
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个公式
2、单条件求和
公式:F2
=SUMIF(A:A,E2,C:C)
说明:SUMIF函数的基本用法
3、单条件模糊求和
公式:详见下图
说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。
4、多条件模糊求和
公式:C11
=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
说明:在sumifs中可以使用通配符*
5、多表相同位置求和
公式:b2
=SUM(Sheet1:Sheet19!B2)
说明:在表中间删除或添加表后,公式结果会自动更新。
6、按日期和产品求和
公式:F2
=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
说明:SUMPRODUCT可以完成多条件求和
五、查找与引用公式
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、按数字区域间取对应的值
公式:详见下图
公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。
六、字符串处理公式
1、多单元格字符串合并
公式:c2
=PHONETIC(A2:A7)
说明:Phonetic函数只能对字符型内容合并,数字不可以。
2、截取除后3位之外的部分
公式:
=LEFT(D1,LEN(D1)-3)
说明:LEN计算出总长度,LEFT从左边截总长度-3个
3、截取-前的部分
公式:B2
=Left(A1,FIND("-",A1)-1)
说明:用FIND函数查找位置,用LEFT截取。
4、截取字符串中任一段的公式
公式:B1
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
说明:公式是利用强插N个空字符的方式进行截取
5、字符串查找
公式:B2
=IF(COUNT(FIND("河南",A2))=0,"否","是")
说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功。
6、字符串查找一对多
公式:B2
=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果
七、日期计算公式
1、两日期相隔的年、月、天数计算
A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。计算:
相隔多少天?=datedif(A1,B1,"d") 结果:557
相隔多少月? =datedif(A1,B1,"m") 结果:18
相隔多少年? =datedif(A1,B1,"Y") 结果:1
不考虑年相隔多少月?=datedif(A1,B1,"Ym") 结果:6
不考虑年相隔多少天?=datedif(A1,B1,"YD") 结果:192
不考虑年月相隔多少天?=datedif(A1,B1,"MD") 结果:9
datedif函数第3个参数说明:
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。
"MD" 天数的差。忽略日期中的月和年。
"YM" 月数的差。忽略日期中的日和年。
"YD" 天数的差。忽略日期中的年。
2、扣除周末天数的工作日天数
公式:C2
=NETWORKDAYS、INTL(IF(B2 说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日 excel函数常用公式2 EXCEL函数公式大全: 1、vlookup(依据哪些找,到在哪里,找哪一个,0)函数的应用:主要是做配对,根据一个字段名关系2个表,随后将2个表配对出去。 这一作用类似SQL中的联表检索:selectt1、a0,t2、a1,t2、a2fromt1,t2wheret1、a0=t2、a0 vlookup句子为:vlookup(a0,t2,a1a2,0):在其中a0即是标准,依据a0寻找t2表格中的a1和a2字段名。 2、iferror(value,value_if_error)函数的应用:表明分辨value的准确性,假如value恰当则返回恰当结果,不然返回value_if_error。 在C2单元格键入公式计算=IFERROR(A2/B2,"不正确") 3、row(单元格)函数的应用:Row函数用以返回单元格的号码,Rows函数用于返回二维数组或引入单元格的个数。 4、match(查找值,查找范畴,0)函数的应用:Match函数用以返回查找值在二维数组的部位,表明查找小于或等于lookup_value的最高值。 这一作用类似SQL中的标准检索:selectindex(a0)fromt1wherea0=90 5、index(查看范畴,行,列)函数的应用:返回表或地区中的值或对值的'引入。 返回相匹配的值,下边的函数返回B4的数据信息。 6、ABS(必须估算的值)函数的应用:取平方根=abs(-2)得2。 7、MAX&MIN(必须估算的二维数组)函数:取二维数组中的极值和极小值=max(A3:A13)得最高值。 8、round(必须舍的值,舍的十位数)函数的应用:四舍五入=ROUND(2、52,1),保存一位小数。 9、ROUNDUP(必须舍的值,舍的十位数)函数的应用:依照规定的小数位数往上舍入,即无论是几都进一,roundup(月份/3,0)为第哪季。 10、ROUNDDOWN(必须舍的值,舍的十位数)函数的应用:依照规定的小数位数往下舍入,无论是几都舍弃,rounddown((月份+2)/3,0)为第哪季。 11、MID(text,start_num,num_chars)函数:从某一段字符串数组中截取下规定数目的标识符,=mid(c2,7,8):从第七位逐渐,提取8字符。 12、MOD(分子结构,真分数)函数:取余函数。 13、IF(logic_condition,true_result,false_result)函数:某一标准创立后实行第一个,不然实行第二个。 =IF(AND(A2<500,B2="未期满"),"补款","") 表明:2个标准与此同时创立用AND,任一个创立用OR函数。 以上就是13种EXCEL常用的函数公式了,欢迎大家参考,希望对大家有所帮助。 excel函数常用公式3 1、身份证号码提取出生日期 公式:=--TEXT(MID(B3,7,8),"0000-00-00") 在这里我们使用mid函数提取身份中号码中的出生日期,然后使用text函数设置数字的格式,因为text是一个文本函数,所以它输出的结果是一个文本,我们在公式的最前面输入两个减号,将文本格式的数值转换为常规格式的设置 2、计算年龄 公式:=DATEDIF(C3,TODAY(),"Y") Datedif函数的作用是计算两个时间的差值, 第一参数:开始时间,就是我们提取身份证号码的时间 第二参数:结束时间,我们使用today函数获取今天的日期 第三参数:返回类型,在这里我们设置为Y,代表年份 3、根据身份证号码提取性别 公式:=IF(MOD(MID(B3,17,1),2)=1,"男","女") 身份证号码中也包含着每个人的性别信息,它只与身份证号码的第17位有关,第17位是计数性别为男,为偶数性别则为女,所以我们使用mid提取第17位的数值,然后使用mod函数计算这个数的奇偶性,最后使用if函数判断输出结果 4、合并单元格求和 公式:=SUM(C3:C14)-SUM(D4:D14) 在这里我们需要先选择想要求和的区域,然后在编辑栏输入公式,最后按Ctrl+回车批量填充公式
怎样在excel表格中提取我想要的数据
excel从多张表中提取数据
recollect Forget----painrecollect为题短文
excel如何从一个表格中提取想要的数据
大牌明星演译AlexanderMcQueen全新升级升級手袋系列产品
excel口红什么牌子 excel口红怎么样
excel身份证格式怎么调整
becca oceanjeewels系列彩妆有什么 becca粉紫色高光多少钱
Excel速成教程 Excel快速入门教程
excel的实用技巧
ecco bella唇釉怎么样?ecco bella唇釉好用吗
老师用爱情讲函数什么情况 当函数遇上爱情会发生什么
xxxtentacion抖音很火的歌叫什么 jocelyn flores歌词
excel如何提取数字部分内容
excel表格怎么批量提取数据
cecilmcbee官网地址 cecilmcbee什么档次
excel某一列提取部分数字
Black Fleece在哪里买 Black Fleece品牌介绍
coccinelle是什么牌子?coccinelle哪个国家的?coccinelle什么档次?
excel渐变腮红色号 常盘工业excel渐变腮红试色
lee是什么牌子中文名 lee和lee cooper是哪个好
adidasTERREX X RICO LEE 一“件”穿梭,敢行未来
nike vapormax 全新 laceless版本什么时候发售多少钱
AlexanderMcQueen秀上这种疯狂珠宝皆来源于ShaunLeane之手
Alexander McQueen新凶器
花花公子女郎Alexa Cole大尺度照曝光
ecco bella口红怎么样?ecco bella口红好用吗
Converse x Jordan全新配色什么时候发售 converse pro leather x jordan发售时间
ALEXANDER MCQUEEN骷髅的魅力
excel供应链经营数据分析
excel函数公式大全 excel函数常用公式
葡萄园内常用的灌溉方式
花花公子女郎Alexa Cole曝光大尺度照