09月22日, 2014 211次
电子表格公式(32个Excel因变量公式大全)
即日和大师瓜分一组常用因变量公式的运用本领。
职场人士必需控制的32个Excel因变量,经心控制那些因变量,处事功效就会有质的提高。
倡导保藏备用,有功夫多熟习一下,本领赶快控制因变量吩咐,处事起来一帆风顺。
一、确定公式1、把公式归来的缺点值表露为空
公式:C2=IFERROR(A2/B2,"")
证明:即使是缺点值则表露为空,要不平常表露。
把公式归来的缺点值表露为空
2、IF的多前提确定
公式:C2=IF(AND(A2<500,B2="未到时"),"补款","")
证明:两个前提同声创造用AND,任一个创造用OR因变量。
IF的多前提确定
二、统计公式3、统计两表反复
公式:B2=COUNTIF(Sheet15!A:A,A2)
证明:即使归来值大于0证明在另一个表中生存,0则不生存。
统计两表反复
4、统计年纪在30~40之间的职工个数
公式:=FREQUENCY(D2:D8,{40,29})
统计年纪在30~40之间的职工个数
5、统计不反复的总人头
公式:C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
证明:用COUNTIF统计出各人的展示度数,用1除的办法把展示度数形成分母,而后相加。
统计不反复的总人头
6、按多前提统计平衡值
F2公式:=AVERAGEIFS(D:D,B:B,"财政口口网",C:C,"大学专科")
按多前提统计平衡值
7、华夏式排名公式
公式:=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))
二、数字处置8、取一致值:=ABS(数字)
9、数字取整:=INT(数字)
10、数字四舍五入:=ROUND(数字,少量位数)
四、乞降公式11、隔列乞降
公式:H3=SUMIF($A$2:$G$2,H$2,A3:G3)
或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
证明:即使题目行没有准则用第2个公式
隔列乞降
12、单前提乞降
公式:F2=SUMIF(A:A,E2,C:C)
证明:SUMIF因变量的基础用法
13、单前提朦胧乞降
公式:详见下图
证明:即使须要举行朦胧乞降,就须要控制通配符的运用,个中对号是表白大肆多个字符,如"*A*"就表白a前和后有大肆多个字符,即包括A。
单前提朦胧乞降
14、多条求朦胧乞降
公式:C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
证明:在sumifs中不妨运用通配符*
多条求朦胧乞降
15、多表沟通场所乞降
公式:b2=SUM(Sheet1:Sheet19!B2)
证明:在表中央简略或增添表后,公式截止会机动革新。
多表沟通场所乞降
16、按日子和产物乞降
公式:F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
证明:SUMPRODUCT不妨实行多前提乞降
按日子和产物乞降
五、搜索与援用公式17、单前提搜索
公式:C11=VLOOKUP(B11,B3:F7,4,FALSE)
证明:搜索是VLOOKUP最长于的,基础用法
单前提搜索
18、双向搜索
公式:=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
证明:运用MATCH因变量搜索场所,用INDEX因变量取值
双向搜索
19、搜索结果一个适合前提记载
公式:详见下图
证明:0/(前提)不妨把不适合前提的形成缺点值,而lookup不妨忽视缺点值
搜索结果一个适合前提记载
20、多前提搜索
公式:详见下图
证明:公式道理同上一个公式
多前提搜索
21、指定非空地区结果一个值搜索
公式:详见下图
证明:略
指定非空地区结果一个值搜索
22、区间取值
公式:详见下图
证明:VLOOKUP和LOOKUP因变量都不妨按区间取值,确定要提防,出卖量列的数字确定要叶序陈设。
区间取值
六、字符串处置公式23、多单位格字适合并
公式:c2=PHONETIC(A2:A7)
证明:Phonetic因变量只能对字符型实质兼并,数字不不妨。
多单位格字适合并
24、截取除后3位除外的局部
公式:=LEFT(D1,LEN(D1)-3)
证明:LEN计划出总参谋长度,LEFT从左边截总参谋长度-3个
截取除后3位除外的局部
25、截取 - 之前的局部
公式:B2=Left(A1,FIND("-",A1)-1)
证明:用FIND因变量搜索场所,用LEFT截取。
截取 - 之前的局部
26、截取字符串中任一段
公式:B1=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
证明:公式是运用强插N个空字符的办法举行截取
截取字符串中任一段
27、字符串搜索
公式:B2=IF(COUNT(FIND("河南",A2))=0,"否","是")
证明:FIND搜索胜利,归来字符的场所,要不归来缺点值,而COUNT不妨统计出数字的个数,这边不妨用来确定搜索能否胜利。
28、字符串搜索一对多
公式:B2=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其余","东北")
证明:树立FIND第一个参数为恒量数组,用COUNT因变量统计FIND搜索截止
七、日子计划公式29、两日功夫隔的年、月、日计划
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" 天数的差。忽视日子中的年。
30.扣除周末的处事日天数
公式:
C2=NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)
证明:归来两个日子之间的一切处事日数,运用参数引导哪些天是周末,以及有几何天是周末。周末和任何指定于假期的日子不被视为处事日
扣除周末的处事日天数
八、其余常用公式31、创造处事表目次的公式
把一切的处事表称呼列出来,而后机动增添超链接,处置处事表就特殊简单了。
运用本领:
第1步:在设置称呼中输出公式:
=MID(GET.WORKBOOK(1),FIND(&q口口网uot;]",GET.WORKBOOK(1))+1,99)&T(NOW())
创造处事表目次的公式办法1
第2步:在处事表中输出公式并拖动,处事表列表和超链接已机动增添
=IFERROR(HYPERLINK("#'"&INDEX(Shname,ROW(A1))&"'!A1",INDEX(Shname,ROW(A1))),"")
创造处事表目次的公式办法2
32、中英文互译公式
公式:=FILTERXML(WEBSERVICE("http://fanyi.youdao.com/translate?&i="&A2&"&doctype=xml&version"),"//translation口口网")
中英文互译公式
excel中的因变量公式变幻无穷,即日就整治这么多了。即使你能控制一半,在处事中也基础上遇不到困难了。
证明:素材与图影片来源自搜集,版权归原作家一切,如有侵权请接洽处置。