對投資新手的簡單易懂教學-Excel在投資分析資料的應用-Vlookup函數

如何學習使用EXCEL進行資料分析?

拜賜於OFFICE軟體的普遍,所以許多人在處理財務數字或其他數據資料時,常會用到EXCEL進行資料處理。而在學校或是職場也很多機會用到,有些人會簡單地使用四則運算與基本函數「SUM」、「AVERAGE」、「ROUND」等,或是操作篩選或樞鈕分析功能,進階的甚至會撰寫VBA程式來處理大量資料。而本系列文章將從常用的「VLOOKUP函數」開始,以詳細與白話方式,且搭配圖片與文字進行投資資料處理的範例說明。

VLOOKUP官方定義

微軟官方網站,對VLOOKUP函數的說明如下(摘錄)

=VLOOKUP (您想要尋找的是什麼、您想要尋找的地方、包含要傳回值的範圍中的欄號、會返回大約或完全相同的相符專案 -表示為 1/TRUE 或 0/FALSE) 。

學習範例

下表是從網路找四間公司的實際營收整理成資料表做VLOOKUP的範例說明

(財務數字來源: 奇摩股市)

股票代號 公司名稱 2020年營收(仟元)
年增率
2330
台積電
1,339,254,811
25.17%
2303
聯華電子
176,820,914
19.31%
2317
鴻海
5,358,023,065
0.28%
3008
大立光
55,944,489
-7.90%

超詳細說明

設計目標是想用VLOOKUP找出指定股票代號的營收與年增率。

B9欄位就是要輸入的目標值(例如下圖是2303),而因為要鎖定位置,所以全部都加$符號鎖定位置。而A3:D6則是VLOOKUP要搜尋的表格位置(要加$符號鎖定),

其中要注意的是因VLOOKUP只能從第一欄往右邊的資料搜尋,所以要比對的目標值要是第一欄,如下表的A欄)。


C9欄位則是從B9輸入後就會自動帶出對應的營收,所以要輸入「=VLOOKUP($B$9,$A$3:$D$6,3,0)」
,其中黃底紅字標示的「3」代表從A欄開始往右數的第3欄,而藍底黑字的「0」,代表B9輸入的目標值與搜尋A欄的值要完成一致(輸入1則是近似值,筆者都會選0完全一致)。


D9欄位則是「=VLOOKUP($B$9,$A$3:$D$6,4,0)」,內容與C9大多一樣,只有一個地方不同,就是黃底紅字標示的「4」,代表帶出從A欄開始往右數的第4欄數字。

許多人在處理財務或其他數字資料時,常會用到EXCEL進行資料處理,在學校或是職場EXCEL常是必須學會的技能,簡單的如使用四則運算與基本函數「SUM」、「AVERAGE」、「ROUND」等,或是操作篩選或樞鈕分析,進階的甚至用到撰寫VBA程式以處理大量資料。而本系列文章將從常用的VLOOKUP函數開始,以盡量詳細與白話方式,且搭配圖片與文字進行完整的範例說明。


今天如果想進行比對。可像下圖將B9、C9、D9選取與複製,然後在下一行進行貼上。


然後直接在B10輸入股票代號「3008」,可發現C10D10的欄位完全對應到3008的營收與年增率,這是因為先前在搜尋目標置的位置輸入是$B9只鎖定B欄位置,而不鎖定「列」的位置,之後我們就能直接複製欄位,只要產出資料欄位(例如C欄與D欄)的公式沒有變動,就能一直複製,然後輸入想要的目標值,函數就會自動帶出要對應的數字。

許多人在處理財務或其他數字資料時,常會用到EXCEL進行資料處理,在學校或是職場EXCEL常是必須學會的技能,簡單的如使用四則運算與基本函數「SUM」、「AVERAGE」、「ROUND」等,或是操作篩選或樞鈕分析,進階的甚至用到撰寫VBA程式以處理大量資料。而本系列文章將從常用的VLOOKUP函數開始,以盡量詳細與白話方式,且搭配圖片與文字進行完整的範例說明。

延伸的小技巧(VLOOKUP資料搜尋欄位的控制)

如果有讀者想把營收與年增率的位置做交換是否可以?可否自由地依需要把結果擺在想要的欄位?

案是可以的,可以用一個在旁邊輸入位置「參數」的技巧,控制資料搜尋的欄位。
C10位置的公式是「=VLOOKUP($B10,$A$3:$D$6,C$8,0)」,其中黃底的C$8代表第8「列」位置鎖定,但讓公式的「欄」位置能變動,如此便能讓C10與C11抓年增率的欄位數「4」,D10與D11抓營收的欄位數「3」,產生資料欄位變動的結果!

而其餘公式內容如下:
C11=「VLOOKUP($B11,$A$3:$D$6,C$8,0)」
D10=「VLOOKUP($B10,$A$3:$D$6,D$8,0)」
D11=「VLOOKUP($B11,$A$3:$D$6,D$8,0)」


許多人在處理財務或其他數字資料時,常會用到EXCEL進行資料處理,在學校或是職場EXCEL常是必須學會的技能,簡單的如使用四則運算與基本函數「SUM」、「AVERAGE」、「ROUND」等,或是操作篩選或樞鈕分析,進階的甚至用到撰寫VBA程式以處理大量資料。而本系列文章將從常用的VLOOKUP函數開始,以盡量詳細與白話方式,且搭配圖片與文字進行完整的範例說明。

取代VLOOKUP的方法

VLOOKUP雖然好用,但使用上也有若干限制,此時便可以考慮使用INDEX&MATCH等函數的組合,
請讀者參考

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

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

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

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

留言