對投資新手的簡單易懂教學-Excel在投資分析資料的應用-HLOOKUP、OFFSET等函數
前言
從公開資訊觀測站抓個股的財務分析資料時，有時要抓資料，可能不只一家的話，通常是將資料先抓到EXCEL裡，之後再統一整理，那也許會有讀者在想，是否有辦法能讓我們以有效率與省力的方式來整理出想要的投資資料表?
本文則從公開資訊觀測站裡，把台積電(2330)、聯電(2303)、大立光(3008)三間公司的財務分析資料(108年度- 110年度)進行整理，選擇使用HLOOKUP函數與OFFSET函數做示範，以下是其相關說明。
HLOOKUP函數
官方說明(摘要如下):
在表格或數值陣列的第一列尋找值，然後傳回表格或陣列之指定列中同一欄的值。 當比較值位於資料表的第一列中，而且您想要在指定列數下方尋找時，請使用 HLOOKUP。
HLOOKUP 函數語法具有下列引數：
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value(必填)=要搜尋的比對目標
table_array(必填)=指定比對目標所在的資料表範圍
row_index_num(必填)=以比對目標為基準，往下移動到達要抓的目標資料所需的列數。
Range_lookup(選擇性)=若輸入0或FALSE，在指定的資料表範圍找的比對目標為「完全一致」，若為1或TRUE 或省略，則比對的結果是大約符合(或完全相符)，一般使用上都是輸入0，也就是要找「完全一致」的結果。
OFFSET函數
官方說明(摘要如下):
傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。 傳回的參照可以是單一儲存格或一個儲存格範圍。 您可以指定要傳回的列數和欄數。
OFFSET 函數語法具有下列引數：
OFFSET(reference, rows, cols, [height], [width])
Reference=資料的起始位置。
Rows(必填)=這是要左上角儲存格往上或往下參照的列數。
Cols(必填)=這是要結果的左上角儲存格向左或向右參照的欄數。
[height](選擇性)=這是要傳回參照的列數高度。Height必須是正數。
[width](選擇性)=這是要傳回參照的欄數寬度。Width必須是正數。
範例說明(HLOOKUP)
|
位置參數* |
110年度 |
台積電(2330)-右邊是公式如何撰寫 |
|
| 2 | 負債比率(%) | 41.73(「=HLOOKUP($I$2,$F$2:$F$21,$H3,0)」) |
|
| 5 |
速動比率(%) |
190.61(=HLOOKUP($I$2,$F$2:$F$21,$H4,0)) |
|
| 16 |
純益率(%) |
37.61(=HLOOKUP($I$2,$F$2:$F$21,$H5,0)) | |
| 13 |
資產報酬率(%) |
18.56(=HLOOKUP($I$2,$F$2:$F$21,$H6,0)) |
|
| 14 |
權益報酬率(%) |
29.69(=HLOOKUP($I$2,$F$2:$F$21,$H7,0)) | |
| 12 |
總資產週轉率(次) |
0.49(=HLOOKUP($I$2,$F$2:$F$21,$H8,0)) |
指定的台積電(2330)財務分析數字，可用「=HLOOKUP($I$2,$F$2:$F$21,$H3,0)」
Hlookup的第一個引數$I$2就是上表的「 110年度」位置。
Hlookup的第二個引數$F$2:$F$21就是「台積電(2330)最近三年度財務資料分析」110年整欄的財務分析數字。
Hlookup的第三個引數就是位置參數，代表「台積電(2330)最近三年度財務資料分析」的財務分析項目的位置。
Hlookup的第四個引數就是0，代表要找「完全符合」的比對目標。 在此階段，我們能用位置參數控制Hlookup第三個引數，來簡化公式的輸入。再下一階段，我們則能進一步用offset控制資料範圍的移動。
Hlookup與Offset的進階使用技巧
從台積電到聯電的資料表要往下跳22行
第三個公司「大立光」的財務資料分析如下 (資料一樣引自公開資訊觀測站)
從台積電到大立光的資料表要往下跳44行
在下圖可看到，在原本Hlookup第二個範圍數字部分，我們再加一個Offset函數，代表依照0、22、44位置參數的指定，能讓Hlookup抓資料的範圍依序在台積電、聯電、大立光三個財務分析表跳動，並依不同項目去抓財務比率。
其他在工作面的應用(提升工作效率)
HLOOKUP與OFFSET函數組合，在工作上可以應用在不同月份或年度抓的資料表範圍，或是不同部門或事業部的資料範圍，此法一樣是妙用無窮!
若有讀者有Excel相關問題，可在底下留言。
免責聲明:以上資料僅供網友參考，若任何人依此資料進行投資，請自行承擔後果。