HI，大家好，我是星光。

今天給大家分享五個函數(shù)公式，一個比一個燒腦，據(jù)說全部都能看懂掌握的，必然是函數(shù)高手——看我眼神，騙你娶你，不論男女。

如果看不懂呢？

那也沒關(guān)系。這五個函數(shù)公式不但燒腦，還有些實用價值。

對絕大部分人來說，復(fù)雜的函數(shù)公式，就像成型的VBA模塊代碼，并不是用于理解學(xué)習(xí)的；只要記得有這么個函數(shù)套路，需要時復(fù)制粘貼應(yīng)用就行了。

每天早上吃個雞蛋就挺好，又何必去琢磨如何開養(yǎng)雞場呢？

這話是錢鐘書大佬說的??

1）文本格式時間轉(zhuǎn)換

燒腦指數(shù)☆

如下圖所示，需要將A列的文本轉(zhuǎn)換為分鐘。

B2單元格輸入數(shù)組公式如下▼

單位轉(zhuǎn)換： 

=SUM(--('0 '&

TRIM(MID(

SUBSTITUTE(

 SUBSTITUTE(

  SUBSTITUTE(

  A2,'分鐘','/1440'),

 '小時','/24'&REPT(' ',99)),

'天','/1'&REPT(' ',99)),

{0,1,2}*99+1,99))))*1440

有朋友想你這是轉(zhuǎn)換為分鐘，如何轉(zhuǎn)換為小時呢？攤手：60分鐘等于1小時，將結(jié)果除以60就是小時，除以60*24就是天。

2）數(shù)據(jù)模糊匹配查詢

燒腦指數(shù)☆☆

如下圖所示，需要根據(jù)D:E列的數(shù)據(jù)，計算A里人名的性別。需要注意的是，D列的人名和A列的人名并非完全對應(yīng)，例如看見星光和看星光；不過簡稱的每一個字符都出現(xiàn)在全稱中。

B2單元格輸入公式如下▼

模糊匹配： 

=INDEX(E:E,

MATCH(,

MMULT(-ISERR(

 FIND(MID(D$1:D$5,

  COLUMN(A:X),1),A2)),

 ROW($1:$24)),

))&''

解法1： 

=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)))

解法2： 

=SUM(IFERROR(

FILTERXML('<a><b>'&

CONCAT(IF(ISERR(

-MID(A2,ROW($1:$99),1)),

'</b><b>',

MID(A2,ROW($1:$99),1)))

&'</b></a>',

'a/b'),0))

4）金額小寫轉(zhuǎn)大寫

燒腦指數(shù)☆☆☆

Excel自帶的小寫轉(zhuǎn)換大寫的單元格數(shù)字格式，無法處理角和分的問題，但有一個函數(shù)公式可以做到。

B2單元格輸入公式如下▼

小寫轉(zhuǎn)大寫 

=SUBSTITUTE(SUBSTITUTE(

IF(-RMB(A2,2),

TEXT(A2,';負(fù)')

&TEXT(INT(ABS(A2)+0.5%),

'[dbnum2]G/通用格式元;;')

&TEXT(RIGHT(RMB(A2,2),2),

'[dbnum2]0角0分;;整'),),

'零角',IF(A2^2<1,,'零')),

'零分','整')

PS：年輕人不講武德，WPS有自帶金額大寫功能。

如果使用函數(shù)執(zhí)行多表合并，可以在匯總表的A1單元格輸入以下數(shù)組公式，向下向右復(fù)制填充。

數(shù)組公式 

=INDIRECT(LOOKUP(ROW(A1)-1,

MMULT(N(ROW($1:$3)>COLUMN(A:C)),

COUNTIF(INDIRECT(

{'2018年';'2019年';'2020年'}

&'!a:a'),

'<>')),

{'2018年';'2019年';'2020年'})

&'!R'&ROW(A1)-LOOKUP(ROW(A1)-1,

MMULT(N(ROW($1:$3)>COLUMN(A:C)),

COUNTIF(INDIRECT(

{'2018年';'2019年';'2020年'}

&'!a:a'),

'<>')))&'C',0)&''