常用公式

公式作用公式列子
时分秒换算分钟=HOUR(H2)*60+MINUTE(H2)+SECOND(H2)/60
中式排名=SUMPRODUCT(($G$5:$G$25>G5)/COUNTIF($G$5:$G$25,$G$5:$G$25))+1
美式排名=RANK(E2,$E$2:$E$23,0)
逆向查找=VLOOKUP(C2,IF({1,0},机具编号!E:E,机具编号!B:B),2,)
逆向查找=INDEX(Sheet2!A:A,RANDBETWEEN(1,3))
批量添加前缀=IF(C118="","","J6NL0085"&C118)
多条件查找按SHIFT+CTRL+回车三键退出单元格。=VLOOKUP(A5&B5,IF({1,0},$I$5:$I$20&$J$5:$J$20,$K$5:$K$20),2,0)按
条件求和=SUMIF(D3:D9,"男",C3:C9)或=SUMIFS(C3:C9,C3:C9,">=80",D3:D9,"男")。
查询好搭档=INDEX(E3:E9,MATCH(H3,B3:B9,0))。
中国式排名=SUMPRODUCT((C$3:C$9>C3)/COUNTIF(C$3:C$9,C$3:C$9))+1。
输入日期转日期格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
vlookup多条件匹配{=IFERROR(VLOOKUP(B165&C165,IF({1,0},b!$B$2:$B$167&b!$C$2:$C$167,b!$I$2:$I$167),2,0),"")}
天数转年月日=INT(G17/365)&"年"&INT((G17-INT(G17/365)365)/30)&"月"&(G17-INT(G17/365)365-INT(((G17-INT(G17/365)365)/30))30&"天")
两个日期间的年月日差=DATEDIF(A8,B8,"y")&"年"&DATEDIF(A8,B8,"ym")&"月"&DATEDIF(A8,B8,"md")&"日"
字符重复次数=REPT("年",10)
替换其中字符或者添加字符=REPLACE(A3,1,1,3)
提取固定字符后面的字符=MID(A1,FIND(".",A1)+1,4)
ip格式转换为000.000.000.000=TEXT(SUM(MID(SUBSTITUTE(A2,".",REPT("",15)),{1;2;3;4}15-14,15)10^{9;6;3;0}),REPT("000!.",3)&"000")
ip格式转换为000.000.000.000=TEXT(LEFT(A1,FIND(".",A1)),"000.")&TEXT(TRUNC(MID(A1,FIND(".",A1)+1,3)),"000.")&TEXT(TRUNC(MID(A1,FIND("#",SUBSTITUTE(A1,".","#",2))+1,3)),"000.")&TEXT(MID(A1,FIND("#",SUBSTITUTE(A1,".","#",3))+1,3),"000")
ip格式转换为000.000.000.000=TEXT(LEFT(A1,FIND("/",SUBSTITUTE(A1,".","/",1))-1),"000.")&TEXT(MID(A1,FIND("/",SUBSTITUTE(A1,".","/",1))+1,FIND("-",SUBSTITUTE(A1,".","-",2))-FIND("/",SUBSTITUTE(A1,".","/",1))-1),"000.")&TEXT(MID(A1,FIND("/",SUBSTITUTE(A1,".","/",2))+1,FIND("-",SUBSTITUTE(A1,".","-",3))-FIND("/",SUBSTITUTE(A1,".","/",2))-1),"000.")&TEXT(RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1,".","/",3))),"000")
IP地址删除前面多余的0=MID(B2,1,3)1&"."&MID(B2,5,3)1&"."&MID(B2,9,3)1&"."&MID(B2,13,3)1
判断区间数=IF(P3<=365,"1年内",IF(AND(P3>365,P3<=1095),"1-3年",IF(AND(P3>=1096,P3<=1825),"3-5年",IF(P3>=1826,"5年以上",""))))
如果要得出“-”之前的字段=LEFT(A1,SEARCH("-",A1)-1)
如果要得出“-”之后的字段=RIGHT(A1,LEN(A1)-SEARCH("-",A1))
如果字符串是以“*”或“？”等通配符来隔开的=LEFT(A1,SEARCH("~~",A1)-1)”/"=RIGHT(A1,LEN(A1)-SEARCH("~~",A1))
判断序号的品牌和型号=IF(D2<>"",IF(LEFT(D2,4)="K370","新国都",IF(LEFT(D2,4)="K350","新国都",)),"")
判断序号的品牌和型号=IF(D2<>"",IF(LEFT(D2,4)="K370","K370",IF(LEFT(D2,4)="K350","K350",)),"")
日期重复递增辅助：24/9=2.67，公式：=2020/01/01+2.67/24，向下填充
分钟数值转换为时间显示格式=INT(F4/60)&":"&MOD(F4,60)
时间格式转换为分钟数显示=TEXT(G4,"[m]"
判断时间是否属于某个时间段=IF(OR(MID(I83,12,2)>="17",MID(I83,12,2)<"08"),"夜间发生故障","营业时间发生故障")
判断日期是否属于周末=IF(WEEKDAY(I2,11)>5,"周末","工作日")
IP去除前面多余的0=MID(B2,1,3)1&"."&MID(B2,5,3)1&"."&MID(B2,9,3)1&"."&MID(B2,13,3)1
分钟整数转换为时分格式：=CONCATENATE(INT(N2/60),":",MOD(N2,60))
该函数表示从A1单元格的文本中，找到第四个位置，替换成“-”=REPLACE(A1,4,0,"-")
从文件夹提取文件名的cmd命令：=dir./b>1.xls
IF通配符判断：=IF(NOT(ISERROR(FIND(C6,H:H))),"正确","不正确")
钉钉群导名字信息=MOD(ROW(A2),2)
文本=mid(a1,find("]",a1)+1,len(a1))
数字=mid(a1,2,find("]",a1)-2)
提取固定字符后的字符=MID(A147,FIND(" ",A147)+1,99)
统计数值有多少个=LEN(LEFT(I139,11)
某个数值重复多少次=REPT("*",LEN(MID(I139,FIND("@",I139)+1,99))

标签: none