最近在臉書跟Line上加入了幾個excel / excel VBA的社群
除了有很多喜好伸手牌的人會問問題之外
也有很多厲害的人會嘗試去解題
有時候看到所用的 excel函數結構非常複雜，真心覺得厲害
而且看到有巧思的運用函數公式，更會讓我想進一步了解公式是如何組合的
我有空的時候也會嘗試解決一些我看得懂的問題
(必須說，有部分的人連問題都說不清楚。也或者是我程度不夠，無法理解)
發現大多數的問題都是在既有資料中，尋找符合條件的目標
因此，多數的問題可以利用 INDEX-SMALL-IF-ROW 所謂的萬金油公式(用途很多)來處理
而在excel 2019之後的版本，可以用 Filter函數+Match函數來處理，並且自動溢位(也就是不用再按Ctrl+Shift+Enter，設定為陣列公式)
例如：以下資料
條件值：以 牌照號碼 為條件，取得符合條件的其他欄位資料，而這一個條件，會有多筆資料
利用 萬金油公式
E2=INDEX(工作表!A:A,SMALL(IF(工作表!$A$2:$A$435=$C$1,ROW($2:$435),“”),ROW(A1)))
F2=INDEX(工作表!D:D,SMALL(IF(工作表!$A$2:$A$435=$C$1,ROW($2:$435),4^10),ROW(A1)))
G2~I2也都是一樣的公式結構
公式說明：由內而外，由右至左來理解
IF函數
透過條件 工作表!$A$2:$A$435=$C$1
為True時，取得在ROW($2:$435)範圍內相對位置的值(ROW())
為False時，則為空值(“”跟4^10的結果都是一樣)
最後會得到一個{True/False}的結果陣列
SMALL函數
要找出符合資料在IF陣列結果的位置，以這個資料表來說就會是列數，並配合 ROW(A1)下向拉動，會產生1、2、3….，可以得到第1個符合的值、 第2個符合的值
INDEX函數
利用SMALL函數得到的列數值，回傳對應的欄位值，例如： 某列的在工作表!A欄的資料
利用 Filter函數
E2=FILTER(INDEX(工作表!A2:O435,,MATCH(E$1,工作表!$A$1:$O$1,0)),工作表!$A$2:$A$435=$C$1,””)
F2=FILTER(INDEX(工作表!A2:O435,,MATCH(F$1,工作表!$A$1:$O$1,0)),工作表!$A$2:$A$435=$C$1,””)
G2~I2也都是一樣的公式結構
公式說明：由內而外，由右至左來理解
MATCH函數
取得符合條件(例如： F$1)在目標資料的相對位置，也就是欄位值
INDEX函數
這時候反而是透過MATCH值，取得資料範圍內的符合欄位的所有值
Filter函數
在INDEX函數取得的資料內，透過條件 工作表!$A$2:$A$435=$C$1，篩選出符合條件的值
後記
之後可以再透過一些排除錯誤值的公式，讓結果更乾淨
如果是多個條件，尋找一個值，可以用 VLOOKUP / LOOKUP，這也有很多應用的技巧