……要是能重来 我要选李白 至少我还能写写诗来澎湃 逗逗女孩…… 咳,说正事…… 今天和大家分享的几条函数公式,一个比一个烧脑,但又非常实用。 因为烧脑,所以计算原理和过程我们就不解释了。 因为实用,所以建议大家收藏,用到的时候可以直接拿去套用。 1, 文本格式的时间转换。 烧脑指数★ 如下图所示,将A列的文本时间转换成分钟。 B2公式: =SUM(--('0'&TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,'分钟','/1440'),'小时','/24'&REPT(' ',99)),'天','/1'&REPT('',99)),{0,1,2}*99 1,99))))*1440 2、 数据模糊匹配。 烧脑指数★★ 如下图所示,需要根据D:E列的数据,计算A里人名的性别。需要注意的是,D列的人名和A列的人名并非完全对应,例如看见星光和看星光。 B2公式: =INDEX(E:E,MATCH(,MMULT(-ISERR(FIND(MID(D$1:D$5,COLUMN(A:X),1),A2)),ROW($1:$24)),)) 3, 数字小写转大写。 烧脑指数★★★ Excel自带的小写转换大写的自定义格式,或者函数,都无法处理角和分的问题。 B2数组公式: =SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,';负')&TEXT(INT(ABS(A2) 0.5%),'[dbnum2]G/通用格式元;;')&TEXT(RIGHT(RMB(A2,2),2),'[dbnum2]0角0分;;整'),),'零角',IF(A2^2<1,,'零')),'零分','整') 4、提取单元格内数值。 烧脑指数★★★★ 如下图所示,需要将A列数据中的数值提取到B列,数值的分布很零散,基本无规律可循。 B2数组公式: =MID(SUM(MID('01'&A2,1 LARGE(ISNUMBER(-MID(1&A2,ROW($1:$48),1))*ROW($1:$48),ROW($1:$18)),1)*10^ROW($2:$19))%,2,100) 但该公式只能正确提取15个数值,超过15位……还是用自定义函数吧。 代码如下: Function Getnum(rg As Range) Dim reg As Object Set reg = CreateObject('VBScript.RegExp') With reg .Global = True .Pattern = '[^0-9]' End With Getnum = reg.Replace(rg.Value, '') End Function 5, 单元格内数值求和。 烧脑指数★★★★★ 如下图所示,需要将A列混合文本中的数值部分在B列汇总求和。 B2数组公式: =SUM(TEXT(LEFT(TEXT(MID(A2&'a',COLUMN($2:$2),ROW($1:$15)),),ROW($1:$15)-1),'0;-0;0;!0')*ISERR(-MID(A2,COLUMN($2:$2)-1,2))) ----The End---- |
|
来自: L罗乐 > 《字符提取(文本函运用)》