別跑!非常好用但你不會的Excel公式大全來了

  文 | 盧子

昨天留言區有讀者說要講一些高難度的,好,滿足你!不過在這之前先看個故事,我怕你跑了!

春秋時期,楚國有一個自稱叫葉公的人。葉公經常對別人說:「我特別喜歡龍,龍多麼神氣、多麼吉祥啊!」

於是當他家裝修房子的時候,工匠們就幫他在房梁上、柱子上、門窗上、牆壁上到處都雕刻上龍,家裡就像龍宮一樣。就連葉公自己的衣服上也繡上了栩栩如生的龍。

葉公喜歡龍的消息傳到了天宮中真龍的耳朵裡,真龍想:「沒想到人間還有一個這樣喜歡我的人呢!我得下去看看他。」

有一天,龍從天上降下來,來到了葉公的家裡。龍把大大地頭伸進葉公家的窗戶,長長的尾巴拖在地上。葉公聽到有聲音,就走出臥室來看,這一看可不得了了,一只真龍正在那裡瞪著自己,葉公頓時嚇得臉色蒼白,渾身發抖,大叫一聲逃走了。

你要的高難度公式來了,你別逃走了!!!

1、提取不重復地市,並獲取相對應的區縣

別跑!非常好用但你不會的Excel公式大全來了

地市,在D2輸入公式,按Ctrl+Shift+Enter三鍵結束,下拉填充公式。

=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$19,$A$2:$A$19,0)=ROW($2:$19)-1,ROW($2:$19),4^8),ROW(A1)))&”

區縣,在E2輸入公式,按Ctrl+Shift+Enter三鍵結束,下拉和右拉填充公式。

=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($2:$19),4^8),COLUMN(A1)))&”

2、根據品名查詢所有符合條件的值

別跑!非常好用但你不會的Excel公式大全來了

在G4輸入公式,按Ctrl+Shift+Enter三鍵結束,下拉和右拉填充公式,並將G列的區域設置為日期格式。

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$16=$H$1,ROW($2:$16)),ROW(A1))),”)

3、依次查找滿足條件的值,如網路技術應用,第一個對應值是李四,第二個是麗萍,第三個是海華。

別跑!非常好用但你不會的Excel公式大全來了

在E2輸入公式,按Ctrl+Shift+Enter三鍵結束,下拉填充公式。

=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$16=D2,ROW($2:$16)),COUNTIF(D$2:D2,D2))),”)

4、將小寫金額轉變成大寫金額

別跑!非常好用但你不會的Excel公式大全來了

在D4輸入公式下拉。

=IF(C40),’零’&TEXT(INT(C4*10)-INT(C4)*10,'[dbnum2]’)&’角’,TEXT(INT(C4*10)-INT(C4)*10,'[dbnum2]’)&’角’))&IF((INT(C4*100)-INT(C4*10)*10)=0,’整’,TEXT(INT(C4*100)-INT(C4*10)*10,'[dbnum2]’)&’分’)))

5、將B列的金額依次拆分到後面的單元格。

別跑!非常好用但你不會的Excel公式大全來了

在C2輸入公式下拉和右拉。

=LEFT(RIGHT(TEXT($B2*100,’ ¥000;;’),COLUMNS(F:$P)))

6、將字符內的數字提取出來

別跑!非常好用但你不會的Excel公式大全來了

在B2輸入公式,按Ctrl+Shift+Enter三鍵結束,下拉填充公式。

=-LOOKUP(1,-MID(A2,MIN(FIND(ROW($1:$10)-1,A2&1/17)),ROW($1:$15)))

7、根據D列的所有序號,查詢A列在B列的對應番號,並合併起來。

別跑!非常好用但你不會的Excel公式大全來了

直接輸入公式,按Ctrl+shift+Enter三鍵結束。適用版本Excel2016。

=TEXTJOIN(‘,’,1,IFERROR(VLOOKUP(N(IF({1},–TRIM(MID(SUBSTITUTE(D2,’,’,REPT(‘ ‘,50)),{1,2,3,4,5,6,7,8,9}*50-49,50)))),A:B,2,0),”))

今日互動

今天就先上7個,感覺如何?要不要再來7個深刻體會一下?

作者:盧子,清華暢銷書作者,《Excel效率手冊 早做完,不加班》系列叢書創始人,個人公眾號:Excel不加班(ID:Excelbujiaban)