Excel中有一個幾乎“萬能”的函數組合,在N多種問題的解決過程中都少不了她的身影,她就是Index+small+if組合!
這個Excel萬金油組合在工作中應用廣泛,我專門挑選了她的幾種典型應用,擴展大家的思路和方法。
今天的內容涉及數組運算,還未晉升到中級的同學們可能不易理解,不過沒關係,大家可以先收藏教程,工作中按此套用。
對於教程有任何想法和需求,可以在底部留言給我,集中的問題我會專門寫教程針對性解決。
1 從單列提取不重複值
在黃色區域輸入公式,提取a列的不重複值
定義名稱a=OFFSET(數據源!$A$2,,,COUNTA(數據源!$A:$A)-1)
C2單元格輸入以下數組公式,按<Ctrl+Shift+Enter>組合鍵
=IFERROR(INDEX(A:A,SMALL(IF(MATCH(a,a,)=ROW(a)-1,ROW(a),4^8),ROW(A1)))&"","")
2 從單列提取重複值
在黃色區域輸入公式,提取a列的重複值
=IFERROR(INDEX(A:A,SMALL(IF(MATCH(a,a,)=ROW(a)-1,4^8,ROW(a)),ROW(A1)))&"","")
在公眾號後臺回覆“index”獲取此函數的史上最全教程。
3 按要求提取數據
結合前兩種技術,配合Excel中的控件,可以定製工作中的數據查找模板,如下圖
定義名稱a=OFFSET('1'!$A$2,,,COUNTA('1'!$A:$A)-1)
C2單元格輸入以下數組公式,按<Ctrl+Shift+Enter>組合鍵
=IFERROR(INDEX(A:A,SMALL(IF(B$1=1,IF(MATCH(a,a,)=ROW(a)-1,ROW(a),4^8),IF(MATCH(a,a,)=ROW(a)-1,4^8,ROW(a))),ROW(A1)))&"","")
在公眾號後臺回覆“萬金油”獲取此案例模板。
4 一對多查找所有符合條件的數據
DATESTRING也是Excel中的隱藏函數,她的作用是轉換日期格式為年月日的形式~
E2單元格輸入以下數組公式,按<Ctrl+Shift+Enter>組合鍵
=INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&""
5 比對並提取兩列數據的差異記錄
定義名稱a=OFFSET('1'!$A$2,,,COUNTA('1'!$A:$A)-1)
定義名稱b=OFFSET('1'!$B$2,,,COUNTA('1'!$B:$B)-1)
D2單元格輸入以下數組公式,按<Ctrl+Shift+Enter>組合鍵
=IFERROR(INDEX(a,SMALL(IF(COUNTIF(b,a),4^10,ROW(a)-1),ROW(A1))),"")
F2單元格輸入以下數組公式,按<Ctrl+Shift+Enter>組合鍵
=IFERROR(INDEX(b,SMALL(IF(COUNTIF(a,b),4^10,ROW(b)-1),ROW(A1))),"")
師父領進門,修行在個人,除了以上幾種應用,Index+small+if組合還有很多小奧妙等待你在實際運用過程中慢慢發現,那種驚喜的感覺還是留給有心人慢慢體味吧~
看完教程想聊點啥?歡迎在底部留言吐槽吧~
如果你覺得有用,就分享給朋友們看看吧~
乾貨每天都有,如果你想每天第一時間收到,
如下操作,兩秒搞定:
新朋友:點頁面右上角三個點→查看公眾號→關注
老朋友:點頁面右上角三個點→查看公眾號→置頂公眾號
更多的Excel應用技巧,可以點擊文尾的“閱讀原文”,獲取更多教程。
今天就到這裡,大家記得收藏此教程,明天見~
李 銳
微軟全球最有價值專家MVP
新浪微博Excel垂直領域第一簽約自媒體
百度名家,百度閱讀認證作者
每日分享職場辦公技巧原創教程
高效工作,快樂生活!
微博 @Excel_函數與公式
微信公眾號(ExcelLiRui)