EXCEL技巧笔记

个人曾经解决过的Excel难题，真实案例，最不相信的就是记忆，录于此，便于后期查阅，

途中出现的百度知道乃是我个人的号。

1.数列多组查找（通过两个线索找对应的值）

数组公式：INDEX($C$2:C5,MATCH(E2&F2,$A$2:$A$5&$B$2:$B$5,0),1)

2.数组条件计数（已知一个确定的线索和不确定的线索，找符合的数量）

数组公式：SUMPRODUCT(($D$3:$D$13=H3)*($C$3:$C$13>0))

3.数字列转字母

 

 

 找出指定左右两边纯数字个数

公式：SUBSTITUTE(ADDRESS(1,N10,4),1,"")

 4.多列值查找（线索在多列上）

 公式：=@INDEX($A$1:$F$6,SUMPRODUCT(($A$1:$F$6=I4)*ROW($A$1:$F$6)),SUMPRODUCT(($A$1:$F$6=I4)*COLUMN($A$1:$F$6))+1)

5.地址整体偏移

 

 公式：=SUM(INDIRECT("D"&10*COLUMN(A$2)-8),INDIRECT("B"&10*COLUMN(A$2)-7),INDIRECT("F"&10*COLUMN(A$2)-6))

6.提取文件路径文件名

 公式：=MID(D4,-LOOKUP(,-FIND("\",D4,ROW(A:A)))+1,-LOOKUP(,-FIND(".",D4,ROW(A:A)))--LOOKUP(,-FIND("\",D4,ROW(A:A)))-1)

7.日期格式替换

 

 公式：=TEXT(LEFT(SUBSTITUTE(A2,".","/"),FIND("-",SUBSTITUTE(A2,".","/"))-1),"yyyymmdd")&TEXT(MID(SUBSTITUTE(A2,".","/"),FIND("-",SUBSTITUTE(A2,".","/"))+1,9),"-emmdd")

8.金额转换大写

 公式：SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","零")

9.单格时间差

 

公式1：ROUND((MID(A1,FIND("-",A1)+1,5)-MID(A1,1,FIND("-",A1)-1))*24,2)&"小时"

公式2：24*(REPLACE(A1, 1, FIND("-", A1), "") -LEFT(A1, FIND("-", A1) -1))

10.组合找最大值

 

 数组公式：=INDEX($O$7:O31,MATCH(N7&MAXIFS(Q7:Q31,P7:P31,N7),$P$7:$P$31&$Q$7:$Q$31,0),1)

11.找单元格中数字第一次出现的位置

 公式：MIN(FIND({0,1,2,3,4,5,6,7,8,9},G12&5^19))

12.求两个不同单元格内数据集合

 公式1：=IF(ISNUMBER(FIND(--MID($A2,COLUMN(A1),1),$B2)),MID($A2,COLUMN(A1),1),"")

 公式2：=IF(ISNUMBER(FIND(--MID($A2,COLUMN(A1),1),$B2)),LEN($B2)-LEN(SUBSTITUTE($B2,MID($A2,COLUMN(A1),1),"")),"")

13.求两个日期的时间差

 

 公式：=TEXT(((C1-A1)*24+HOUR((C1&D1)-(A1&B1)))/24,"d天h小时")

14.判断单元格内是否有某数

 

 公式：=IF(OR(A2={1,13,25,37,49,61}),"正常",IF(OR(A2={2,14,26,38,50}),"异常",IF(OR(A2={3,15,27,39,41,53}),"观察",IF(OR(A2={4,16,28,40,52}),"红线",IF(OR(A2={5,17,29,41,53,65}),"其它1",IF(OR(A2={6,18,30,42,54,66}),"其它2",IF(OR(A2={7,19,31,43,55,67}),"其它3","错误")))))))

15.单行拆分多行

 

 

 公式：=INDEX($1:$1,1,7*ROW($A1)-6+COLUMN(B1)-1)

16.指定字符求在范围内第几行

 公式：=MATCH(L3,LEFT(N:N,FIND(" ",N:N)-1),0)

17.公式实现单元格内容倒序

 公式：=TEXTJOIN("",1,MID(A2,101-ROW($1:$100),1))（textjoin函数需要excel2016版本才支持）

 18.根据列内容在范围随机填充数据

 

 公式：=@INDIRECT("a"&RANDBETWEEN(3,14))

19.区域去重复

组合函数：=INDEX($A$2:$A$100,MIN(IF(COUNTIF(B$2:B2,A$3:A$100)=0,ROW($3:$100))))&""

20.列数字转换列字母

 

公式（最大到Z-26列）：=CHAR(64+COLUMN(A1))

公式（最大到AZ-78列） ：=IF(COLUMN(A1)<27,CHAR(64+COLUMN(A1)),IF(COLUMN(A1)<53,"A"&CHAR(COLUMN(A1)-26+64),"B"&CHAR(COLUMN(A1)-26*2+64)))

21.根据序号自动筛选

 

 公式：IFERROR(INDEX($D:$D,SMALL(IF(--($C$3:$C$13=F$2)=1,ROW($3:$13)),ROW($A1)),1),"")