對投資新手的簡單易懂教學-Excel在投資分析資料的應用-INDEX&MATCH函數組合的公式

前言

這個EXCEL教學系列就會說明一些我以前常用的函數、結合公式的使用方式、一些理念與經驗談，並會盡量以簡單易懂的方式進行說明，以期讓各位讀者理解。

在EXCEL比對資料時，VLOOKUP函數是最常使用的函數，原因在於我們慣用的表格比較適合用查詢「欄(column)」，也就是由上往下去查詢。但VLOOKUP使用上還是有些限制。

Vlookup的「限制」

例如依照微軟網站說明，VLOOKUP 的一個限制是，

它只能尋找資料表陣列中最左邊欄的值。 如果您的尋找值不在陣列的第一欄中，則會看到 #N/A 錯誤。

取代VLOOKUP函數的方法?

那如果我們想比對的方式，剛好就不在查詢範圍的最左邊(第一列)，是否有可替代的方式?
依我的經驗，我通常會使用Index與Match這2個函數的組合來替代VLOOKUP函數。

INDEX函數

微軟網站對INDEX函數的說明如下(摘錄)

INDEX 函數會傳回表格或範圍內的某個值或值的參照。

語法: INDEX(array, row_num, [column_num])

白話說明:
Array:想要呈現比對後結果的「範圍」
row_num:指定結果為範圍內的「第幾列」
column_num:若row_num為空白，則column_num就要輸入指定結果為範圍內的「第幾欄」

MATCH函數

微軟網站對MATCH函數的說明如下(摘錄)

MATCH 函數會搜尋儲存格範圍中的指定項目，並傳回該項目於該範圍中的相對位置。例如，若範圍 A1:A3 中含有值 5、25 及 38，則公式 =MATCH(25,A1:A3,0) 會傳回數字 2，因為 25 是範圍中的第二個項目。

語法: MATCH(lookup_value, lookup_array, [match_type])

白話說明:
lookup_value=要比對的值(比對值)

lookup_array=比對值所在的資料範圍
match_type=控制引數，在此我們需要輸入0，代表要在指定資料範圍內，找尋與「比對值」「完全等於」的「第一個值」。
而這個第一個值也就是與vlookup、hlookup等函數的限制一樣，他們只能找到資料範圍內的第一個搜尋目標，而不能多重尋找目標。

INDEX與MATCH組合的範例說明

我們資料使用與VLOOKUP相同，唯一差異就在於資料表格的擺放方式，為了證明INDEX&MATCH的使用比較靈活，在此特別將營收資料擺在股票代號的「左邊」。

公式寫法範例: =INDEX(B$3:B$6,MATCH($D9,$C$3:$C$6,0))

由於這是結果2個函數的公式，所以我們由內而外地分開說明。
MATCH($D9,$C$3:$C$6,0)
去找出目標值(例如「2303」)在股票代碼欄的位置，而在本例中，這個值會是2，因為2303在這個四個代碼中由上往下數是第二個。
若已知MATCH($D9,$C$3:$C$6,0)=2，

我們就將2代入原來公式，可變成INDEX(B$3:B$6,2)
代表在營收欄裡，要找得「值」是排第二個的，所以比對出來的結果就是176,820,914。
而之所有在公式會有$符號，這是為了接公式時，能直接適用，而不會因為拉公式後，對應的位置會跑掉。

這個設計涉及絕對位置與相對位置，

請參照「對投資新手的簡單易懂教學-Excel在投資分析資料的應用-vlookup函數」的說明!

小訣竅-多個函數組合的公式，可考慮分開測試

這邊也提供一個心得，如果您寫的EXCEL公式夠複雜，建議各個函數，可以單獨寫在旁邊(其他儲存格)，測試結果是否如預想這樣，如果各個函數結果都測試OK，最後再組裝起來。

其實這個概念就和數學推導一樣，過程中每個小部分都會有個小推導，最後合併所有小推導，呈現應有的推導結果。

例如我寫過能將EXCEL工作表的財務數字轉化成完整文字報告，這種公式可能是公式列滿滿的三或四行，裡面充滿了IF的邏輯比較與轉化不同格式的結果，這種太複雜的，有時就需要實際去測試，才會知道自已有沒有想錯，也比較好抓出&修正問題。

若有讀者有Excel相關問題，可在底下留言。

免責聲明:以上資料僅供網友參考，若任何人依此資料進行投資，請自行承擔後果。