Tag Archives: excel

Excel 小技巧

前陣子看到網路上的美股投資 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]

  1. http://www.twoinvesting.com/2017/10/dividend-stock-portfolio-spreadsheet-on-google-sheets/

Excel 小技巧

有時候, 在 excel 的工作表 2008 中想要參考工作表 2007 中某一格的內容, 它的寫法如下:

工作表 2007 假設有一個 A1 = 5

工作表 2008 假設有一個 A1 = 8, A2 想要用工作表 2007 的 A1 去加工作表 2008 的 A1,

則 A2 的公式是 +(‘2007’!A1)+A1

其中 +() 是不能少的, 關鍵字 ‘2007’ 是工作表 2007 的名字, 然後用單引號刮起來, 後面要有驚嘆號! 接下來就是欄位的寫法, 和平常一樣.

如果對應的欄位, 希望在 copy and paste 之後對到固定的欄位還是不變的, 那就要把
A1 改成 $A$1 這種格式.