股票管理 Excel 技巧 - INDEX 和 MATCH
幾年前寫過一篇 "投資入門：利用 Google Spreadsheet 追縱持有股票"，到今天仍是本站較受歡迎的文章之一，一直以來收到很多朋友請求"存取"檔案 (其實點擊"檔案 > 下載"就可以了)，可見不少人對怎樣管理股票紀錄甚有興趣。事實上，網上有大量免費資源提供類似的 Excel 範本，例如長期搶佔Google 搜尋第一位的"史上最懶股票管理表Excel"，就是一個非常推薦的範本。
不過，用過幾個範本就很快悟出一個道理：投資方式和著眼點每個人也不同，因此沒有一個範本可以適合所有人，甚至每一個範本也不能切合你 100% 的需求。因此，懂得若干的 Excel 運算式和技巧便相當重要了。
說到 Excel 技巧，我會用畢生氣力去推薦 Leila Gharani 的 Youtube 頻道，她的頻道介紹了各種 Microsoft Office 的技巧，本站之前介紹過的幾個技巧 (例如 Power Query Editor 和 Pivot Table) 都是啟發於她的影片。
Excel 技巧有很多，而且看似複雜，但常用的招數往往是幾款基本技巧的變奏，例如 INDEX 和 MATCH，以下分享幾個與 INDEX 和 MATCH 有關的應用實例，不止買賣股票，也許在日常工作也用得著。
*********
問 1：我有很多交易紀錄，可以只做某個條件的平均值嗎？例如虧蝕的交易平均輸了多少錢？
答 1：可以用 AVERAGEIF ，即是加入條件的 Average。
=AVERAGEIF(E:E,"<0")
*********
問 2：如何找到最新的交易紀錄？即是每個表格最後的項目？
答 2：算式有點長
=INDEX(目標範圍, MATCH(999999999999,目標範圍))
我們從後面談起，
- MATCH 的部份是找目標範圍最後的項目 (999999999999 是一個足夠大的數字，去告訴 Excel 找最後的項目，然後傳回那個項目的位置 (例如 E13) 給 INDEX
- INDEX 在目標範圍找回第 13 個項目，即 E13 的數值。
*********
問 3：如何找到第 N 個項目的交易紀錄？例如指定表格最後第 3 個項目？
答 3：原理很簡單，首先找到最後一個項目，再向上數 2 個項目，就是最後第 3 個。這個做法叫 Offset，也有一個自己的算式。
=OFFSET(目標項目, 向上/向下數 N 個, 向左/向右數N 個)
配合上面的做法，找出表格最後第 3 個項目就是：
=OFFSET(INDEX(E:E,MATCH(9999999999,E:E)),-2,0)
*********
問 4：如何計算最近 N 個交易紀錄？
答 4：我們上面找到了最後 N 個交易紀錄，也知道找最後一個的方法，所以我們有齊目標範圍的頭和尾，只要應用平常用的 Average、Sum、Max，就可以計算自己想要的東西，例如
=SUM(OFFSET(INDEX(E:E,MATCH(9999999999,E:E)),-2,0):INDEX(E:E,MATCH(9999999999,E:E)))
*********
問 5：如何計算最近 N 個交易的積數 (Sum Product)？
答 5：有時候我們需要運算多個數字，例如入場價和買入數量，去運算自己的入場成本，當要處理很多項目的時候，就要用 SUMPRODUCT 的功能。
假設要計算最近 3 項交易的成本，可以這樣做：
=SUMPRODUCT(OFFSET(INDEX(D:D,MATCH(99999999,D:D)),-2,0):INDEX(D:D,MATCH(99999999,D:D)), OFFSET(INDEX(E:E,MATCH(99999999,E:E)),-2,0): INDEX(E:E,MATCH(99999999,E:E)))
*********
從上面可見，利用了INDEX 和 MATCH 找到最後一個項目後，可以衍生出不同的應用情況，而我相信這些情況都頗為實用 (起碼我自己是很常用到的)。
Excel 算式常常都長過彌敦道，看起來很嚇人，但只要願意逐項拆解，很多時會發現比想像中容易，而且能夠滿足很多需求。
留言
張貼留言