前陣子看到網路上的美股投資 Excel 檔 [1], 覺得很厲害. 仿照它可以用來管理同仁在每個子產品線的計畫投資了多少人力. 不過, 想要把它從 Google Doc 搬到 Windows 平台, 很多厲害的指令都不能用了!!
首先是這個 D2 = if(isblank(A2),,counta(SPLIT(B2,",")))
D2 可以將 B2 裡面的項目數算出來 (counta), 但先要用 SPLIT 把 B2 按照逗號 (,) 隔開. 不過 Office 裡面的 Excel 函數就做不到這點. 取而代之, 另外一個技術是計算逗號數再加一.
B2=IF(ISBLANK(A2),,LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1)
它的原理是先算整個長度 LEN, 再減去字串中的逗號被取代的次數 (SUBSTITUTE). 這樣逗號的前後有空白也沒有關係.
第二個是上述的 C2, 它可以去另外一個工作表 (Portfolio) query 整張 ($B$5:$O$70) 裡面, 有沒有 B 欄的內容等於本頁的 A2 (也就是上圖中的 Apple), 有的話就取出它 O 欄的值.
C2=if(isblank(A2),,query(Portfolio!$B$5:$O$70,“select O where B='"&A2&“‘"))
我覺得這個功能超強大, 不過 Office 的 Excel 也不能用. 取而代之的, 是用
C2=VLOOKUP($B2,Portfolio!$C$5:$O$70, 13, FALSE)
用垂直方向 LOOKUP 找 B2, 找到之後, 選右方第十三欄的內容. 這看起來很矬, 但也算是堪用.
第三個技巧是, H 欄可以把所有股票中, 每個單月的配息分別加起來, 從一月到十二月. 例如:
H2 =sum(query($B$2:$E$64, “select E where B like ‘%Jan%'"))
它 query 整張 (B2:E64) 裡面 B 欄裡面找到有 Jan 這個單字的列, 將其 E 欄的內容累加起來 (sum).
然而, Office Excel 的 query 也不是這樣用的. 因此我先把 H2 的內容 ‘Jan’, 前後加上萬用字元 (*), CONCATENATE(“*",$H2,"*").
CONCATENATE(“*",$H2,"*")
SUMIF() 是條件式的函數, 第一個參數是加總的範圍為整張 (B2:B64), 第二個參數是條件式是否成立. 目前是 *Jan*, *Feb*’…這些是否存在. 第三個參數是要加總的內容.
H2 =SUMIF($B$2:$B$ˊˊ64,CONCATENATE(“*",$H2,"*"),$E$2:$E$64)
這個作法也等效於 Google Doc.
以上供大家參考, 也避免自己忘記.
[Note]
Cash Chou's Blog 