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

對投資新手的簡單易懂教學-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組合的範例說明

這個EXCEL教學系列就會說明一些我以前常用的函數、結合公式的使用方式、一些理念與經驗談,並會盡量以簡單易懂的方式進行說明,以期讓各位讀者理解。而本文則是說明VLOOKUP的使用限制與如何使用INDEX&MATCH的函數組合去取代VLOOKUP。


我們資料使用與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的邏輯比較與轉化不同格式的結果,這種太複雜的,有時就需要實際去測試,才會知道自已有沒有想錯,也比較好抓出&修正問題。


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

延伸閱讀
pattra的大頭照
貝多羅

曾在上市櫃財務會計單位服務多年,經歷財務、徵信與經營分析等工作,喜歡爬山、旅遊與閱讀。 取筆名「貝多羅」是因最初的佛經是刻寫在貝多羅葉上,希望我的文章能有文以載道之效。

留言