萬金油公式可以放棄了

前言

什麼是萬金油公式？

如果你連這個都不知道，說明你離高手還有那麼一點點距離。

萬金油公式在提取不重複值和一對多查找中經常用到，別說是新手，就是自認為函數很牛的同學也未必能真正搞懂，並一氣呵成的給你寫出來，因為數組公式本身就非常複雜，還要用到index min(small) if countif row(column)等函數的高難度組合。下面就請牛逼閃閃的萬金油公式登場：

1萬金油公式

取不重複記錄：

在G1中輸入萬金油公式：=INDEX($B:$B,MIN(IF(COUNTIF($F$1:F1,$B$2:$B$11)=0,ROW($B$2:$B$11),4^4)))&""，按Ctrl Shift Enter三鍵結束。右拉公式到I列

一對多查找：

在G2中輸入萬金油公式：

=INDEX($C:$C,SMALL(IF($B$2:$B$11=G$1,ROW($B$2:$B$11),4^4),ROW(A1)))&""，按Ctrl Shift Enter三鍵結束。下拉右拉公式，填充G2：I11區域。

是不是有點懵，如果感覺不適的話，就放棄吧，等你感覺火候差不多的時候，再回過頭來看看。今天我教你個簡單的，不過要加輔助列，就像做幾何時加輔助線一個道理，一學就會，方便理解：

取不重複記錄：

在最左邊增加輔助列1

A2 =SUM(COUNTIF($B$2:B2,B2)=1,A1)，下拉公式。

意思：如果是第一次出現就把上面的序號加1，否則序號不變。

解釋：COUNTIF($B$2:B2,B2)=1，如果成立，則說明是第一次出現，返回TRUE，否則返回FALSE，再與上一單元格求和，SUM函數強制將TRUE和FALSE變成1和0。

在G1輸入公式：

=IFERROR(VLOOKUP(COLUMN(A1),$A$2:$B$11,2,0),"")，右拉公式。

解釋：

VLOOKUP與COLUMN函數配合，返回第幾次出現的數據

IFERROR為容錯公式，避免VLOOKUP查找不到時，顯示錯誤值。

一對多查找：

增加輔助列2

C2=B2&COUNTIF($B$2:B2,B2)，下拉公式

解釋：將B列與出現的次數合併

在G2輸入公式：

=IFERROR(VLOOKUP(G$1&ROW(A1),$C$2:$D$11,2,0),"")，下拉，右拉公式。

解釋：

VLOOKUP與ROW函數配合，返回第幾次出現的數據

解釋：IFERROR為容錯公式，避免VLOOKUP查找不到時，顯示錯誤值。

結論

能解決問題的公式才是好公式，對於複雜的問題，不妨加幾列輔助列，問題也就迎刃而解了。

化繁為簡，實用才是硬道理！

vlookupcountifsum都是最基本函數

END

推薦閱讀：