Excel 商業應用王

顧客行為與消費過程分析實務班

2010-02-22T20:13:00.001+08:00

我在資策會所開的課程。
http://www.iiiedu.org.tw/ites/CBP.htm

實物的交換是很基礎又很原始的社會行為，在交換中，雙方透過自己的需求來進行以物易物。此時，彼此就開始觀察對方的喜好與交易過程，如此，才能取得最有利的位置。工匠式的生產量小而且顧客的需求也不高，所以顧客總是處於弱勢的一方。到了工業革命之後，機器化生產漸漸取代工匠式的生產，產品開始標準化、大量化，此時的顧客是否比較優勢呢？答案是否定的，顧客還是處處受制於工廠的製造能量與樣式。就像工匠式一樣，製造什麼產品，顧客就買什麼。顧客心理想什麼？現實生活又需要什麼？不是工廠所關心的，它們把焦點放在如何生產更多的產品與如何降低更大成本？無所不用其極地設法將利潤極大化。很幸運地！產品越做越多，存貨越堆越高，消費需求量無法抵銷倉庫存貨，行銷的觀念就產生了。於是，賣東西的技巧越來越精細化，對顧客的研究也越來越深。
不管用甚麼方式，行銷的最終目的就是將產品賣給顧客，並試圖滿足他們。假使不瞭解顧客想什麼？需要什麼？任何的管道、方法、產品都無法讓顧客掏出錢來。因此，企業不是只要注重生產產品就好了，還要為了讓顧客產生忠誠度，瞭解他們所處的內外環境，根據心理的因素、個人的行為、社會的狀況與文化的影響，進而分析顧客的需求。
顧客行為是商學院裡的一門重要的課程，但是，學院派比較重視理論與研究的精神；而一般的社會教育機構著重於市場與實用的涵義。但是常常會有頭痛醫頭，腳痛醫腳的現象產生，缺乏整體與系統性的考量。有鑑於此，資策會特舉辦「顧客行為與消費過程分析實務班」，讓學員透過課程與Workshop，掌握整個購買決策的過程，企業就能在購前的分析、購中的態度與購後的評估來滿足顧客內心的渴望。

條件式加總的精靈操作方式

2009-01-07T11:02:00.006+08:00

「條件式加總」是以精靈操作方式以某欄位資料為依據來達成計算的目的。所以只要了解操作方法就可以完成條件式加總，這個方式只能加總，至於其他計算方式（平均數、個數…）就沒辦法執行。

步驟 1. 啟動「條件式加總」的功能

先選擇A1:E57儲存格。再點選「工具」「精靈」「條件式加總」。

##CONTINUE##

步驟 2. 說明及選擇目標儲存格

請看窗格說明。

步驟 3. 加入條件句
選擇加總欄位。假如是數字格式就是儲存格的數字全部加總，文字與日期格式是加總筆數。

選擇「測試欄位」「比較」「測試值」。按「新增條件式」之後，就會在下面空格裡顯示。在此選擇兩項條件式，一項是「日期」大於2006/1/5；另一項是「項目」等於交通費，只要符合這兩項條件都會在「支出金額」加總。再按「下一步」。

步驟 4. 檢視計算結果
看看格子裡是否是指定的條件句。兩個選項都是將計算後的結果放在工作表裡。點選「將公式及測試值都放到工作表內」。然後再點選「下一步」。
這個意思是加總「項目」為「交通費」，「日期」在2006/1/5以後的「支出金額」是10135元。

步驟 5. 顯示計算結果
點選B59，將交通費顯示在此儲存格。

點選A59，將2006/1/5顯示在此儲存格。

點選D59，將計算結果顯示在此儲存格。

前面三個步驟填入運算的條件與結果到儲存格之後，顯示交通費在B59，日期在A59和支出金額在D59的結果。

SUBTOTAL函數的多樣性

2009-01-06T16:07:00.006+08:00

SUBTOTAL()是一個多樣性的函數，它可以部份取代SUM()、AVERAGE()...共11項的函數。它可以忽略工作表中隱藏與篩選的欄列位，所以需要計算工作表的隱藏或篩選欄位，SUBTOTAL()的價值就會特別突顯。##CONTINUE##

首先，從「函數說明」中可以顯示出EXCEL對SUBTOTAL()的說明，如下所述：

傳回一個清單或資料庫的小計。一般都是使用[資料]功能表上的[小計]命令，便可以很容易地建立帶有小計的清單。一旦小計清單建立後，就可以用SUBTOTAL函數來修改。

語法：

SUBTOTAL(function_num,ref1, ref2, ...)

Function_num是數字1到11，(包括隱藏的值)或101到111(忽略隱藏的值)，用以指定要用下列哪一個函數來計算清單中的小計。

在「小計」工作表，D3的儲存格裡並不是SUM()，而是SUBTOTAL()，SUBTOTAL()使用簡單的加總時也可以代替SUM()。SUBTOTAL()第一引數9就是代表加總的意思，所以SUBTOTAL(9,D2:D2)就是加總D2:D2範圍的數值。

為了了解SUBTOTAL()的第一引數所代表的意義，再從「函數說明」中擷取SUBTOTAL()第一引數的說明，如下表：

Function_num	Function_num	函數
(包括隱藏的值)	(忽略隱藏的值)	函數
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

第一引數9與109都代表SUM()的意思。

上面的表格是Excel對SUBTOTAL()的解釋，為了再進一步釐清其中的關係，所以以SUM()與SUBTOTAL()為例子，檢視其中不同的運用情形。

數值	SUM()	SUBTOTAL(9)	SUBTOTAL(109)
一般狀況	全部加總	全部加總	全部加總
隱藏功能	全部加總	全部加總	忽略
篩選功能	全部加總	忽略	忽略
SUBTOTAL()	全部加總	忽略	忽略

以SUM()為例，SUM()所對應SUBTOTAL()的function_num是9，而其他函數所對應的function_num功能都是一樣，會產生表裡的狀況。所以就不加以解釋。SUM()會計算所有的數值儲存格，SUBTOTAL(9)會忽略篩選及SUBTOTAL()儲存格，SUBTOTAL(109)則對隱藏、篩選與SUBTOTAL()都會忽略。

點選「小計」工作表，D64是加總D2:D62的數值，SUBTOTAL(9)遇到D20、D22…以SUBTOTAL()計算的儲存格就跳過去，不予計算。

篩選功能也會影響SUBTOTAL()的計算結果，所以以下程序利用篩選來檢驗這些函數的功能。

步驟1.選擇篩選區域

首先，將56列取消隱藏，然後選擇A1:E57的範圍。

步驟2.選擇自動篩選

點選工具列上的「資料」「篩選」「自動篩選」。

步驟3.產生篩選箭號：點選篩選箭號進行項目選擇。如果只希望某項目出現篩選符號時，就必須在篩選範圍時，只選定該欄的資料即可。例如：只想篩選「支出金額」，就點選D欄。

步驟4.選擇篩選的項目

點選C欄「細目」的「小禮物」作為篩選項目。

步驟5.結果產生

篩選後只顯示「小禮物」的項目。

SUM()的加總還是不變，其他兩個只計算篩選的細目。所以，SUBT0TAL(9)與SUBTOTAL(109)的功能都只能計算篩選的標的。

步驟6.使用AVERAGE()功能

SUBTOTAL()可以對應11種函數，除了SUM()以外，再查看AVERAGE()是否一樣，從D63:D64可以看出SUBTOTAL()的不同，只平均小禮物這六項的支出金額，AVERAGE()平均所有儲存格的值。

從上面的說明之中，可以了解SUBTOTAL()的功能，在篩選與隱藏的時候，可以正確的計算儲存格上的資料。所以，在業務資料的分析上，能提供業務人員更佳的便利性。另外，除了SUM()、AVERAGE()之外，其他相對應的函數也是同樣功能，請讀者自行檢視COUNT()、MAX()…之類的函數。

輕輕鬆鬆玩｢小計｣

2009-01-05T14:15:00.009+08:00

透過函數的技巧可以減輕計算的負擔，但是不只只有函數擁有加總的功能，其實Excel也有許多加總的精靈，可以提供我們降低函數輸入的時間。下面的章節我們將會一一介紹這些技巧，「小計」是其中一種，它能合計相同的項目，但首先必須將相同的項目排序，才能合計。「小計」是以精靈為操作方式，所以不會很難了解其中的意義。
##CONTINUE##
步驟1.排序相同項目
首先，需要點選區域，選擇所有工作表。
然後點選工具列上的「資料」->「排序」->「主要鍵」->「項目」->「遞增」->「次要鍵」->「日期」->「遞增」->「有標題列」->「確定」。B欄就會進行排序，相同項目就會排在一起，就能執行「小計」的功能。

步驟2.點選「小計」功能然後選擇A1:E57的範圍，再點選工具列上的「資料」「小計」。就會出現「小計」對話方塊。

步驟3.選擇對話方塊內容
在「新增小計位置」勾選「支出金額」。就會根據「項目」中相同的項目，進行「支出金額」的數字加總。然後點選「確定」。

步驟4.「小計」完成
接下來就會出現大綱模式，按3會打開項目的所有資料，按2就是合計部分，按1的話只會顯示總計項目。相同項目的欄位就會在支出金額的欄位加總。
點選「＋」號是展開大綱，「－」號是收縮大綱。

所以小計模式可以讓我們更清楚了解合計的功能。按工作表左上角大綱模式的3數字，可以展開所有的資料，就可以看到操作「小計」後的結果。
打開之後，發現在「項目」中相同的項目在「支出金額」加總。如果不要大綱，點選「資料」->「群組與大綱」->「清除大綱」即可。

邏輯判斷條件式加總的應用

2009-01-04T15:18:00.002+08:00

如果要計算多人的支出金額時，就需要用到「+」，「+」類似OR邏輯判斷，判斷引數中只要有一個成立就是TRUE，就能執行最後引數的加總。

OR條件式加總操作

兩個公式都可用「+」來執行OR邏輯判斷。

##CONTINUE##

接下來，需要解析「＋」是如何運用。首先點選B77，在此將利用SUM(IF())執行條件句的運算來說明「+」的邏輯判斷的過程。

{=SUM(IF((E$2:E$62=B67)+(E$2:E$62=B68),D$2:D$62))}

兩邊條件只要其中一個是TRUE就可以成立。

驗證「+」邏輯判斷

了解「+」的判斷之後，再來驗證「+」的邏輯判斷。首先，需要在F欄輸入驗證公式，F2公式是=OR(E2=B$67,E2=B$68)，這個意思是只要E2=B$67或者E2=B$68其中一個為真這個公式就是真。

F欄為驗證欄，E2、E19為「周博東」符合條件就是TRUE，E16為「段政存」也是符合條件，所以也是真。所以加總D欄支出金額。

也可以以數字為邏輯判斷依據，例如：計算「項目」為「文具印刷品」或者「支出金額」＝2000的合計

從B84的公式可以了解，判斷「支出金額」=2000與「項目」=「文具印刷費」，其中有一個是TRUE就加總「支出金額」。為了了解函數的運作方式，在G欄執行驗證的公式，查看所求的判斷是否正確。

不等式判斷加

前面已經說明「＋」與「*」的用法，還有一種邏輯判斷是「－」。「－」代表兩個式子有一個是真就是真，兩個都是真就是偽的邏輯判斷，也可以用「<>」來代替。例如：想要找出「日期」為4月份的支出金額與「細目」為小禮物，但是不包含兩項同時出現的條件。

這個公式是找出月份等於4與C2:C62等於小禮物的條件式。符號「<>」與「-」可以互相代替，都是相同的邏輯判斷，只要兩個條件有一個條件為真，但是剔除兩個條件同時為真，然後在D2:D62加總。

另外，為了了解B77公式的運算程序，所以，在G:I欄驗證「－」的邏輯判斷原理。

G欄是查詢A欄月份是否=4，所以G2的公式是：=MONTH(A2)=4。

H欄是查詢C欄是否=小禮物，所以H2的公式是：=C2="小禮物"。I欄是判斷假如G或H欄有一儲存格是真就顯示D欄「支出金額」。所以I2的公式是：=IF((AND(OR(G2,H2),NOT(AND(G2,H2)))),D2,0)。

在I63的數字與B77是相同的，所以「-」的邏輯判斷與驗證是正確的，為了使驗證公式更加清晰，因此需要解析此判斷公式，此公式為：

=IF((AND(OR(G2,H2),NOT(AND(G2,H2)))),D2,0)

IF()的第一個引數是真，才會執行第二引數D2，否則就會判斷為偽，執行第三引數0。所以這個公式首先判斷(AND(OR(G2,H2),NOT(AND(G2,H2))))是真還是偽，AND()是判斷引數全部是真才是真，裡面有兩個引數OR()、NOT(AND())。OR()是判斷其中一個引數是真就是真，NOT()是判斷引數是真就是偽，偽就是真。所以G2與H2兩個都是偽，就顯示0。另外再來檢視I54，此儲存格顯示250，因為OR(G54,H54)是真，NOT(AND(G54,H54))的判斷式裡，AND()有一個條件式是FALSE，所以判斷為偽，NOT(FALSE)就判斷為TRUE。所以，I54為真就顯示D54的值。I58也顯示0，因為兩個都是真，OR(G58,H58)為真，但是NOT(AND(G58,H58))是偽，所以公式就成為AND(TRUE,FALSE)，邏輯判斷的值就是偽，必須執行IF()第三引數0。其他的公式的判斷式都與上面無異。最後加總I2:62的值，總共為8310，此值與B77、B78的值是一樣，所以「－」的判斷式加總就是AND(OR(),NOT(AND()))的邏輯判斷式，也就是兩個式子其中有一個為真才是真，兩個都是真或都是偽就是偽。

參考：<<業務寶典>>

多條件式加總_SUMPRODUCT

2009-01-03T15:09:00.003+08:00

前面證明SUM(IF())可以用在單條件式加總，其實另外一個函數SUMIF()也具有同樣的功能，但是條件在多個以上時，SUMIF()就無法勝任。此始就需要使用其他的函數，例如：SUMPRODUCT()、SUM()或SUM(IF())。

首先，從「函數說明」中可以顯示出EXCEL對SUMPRODUCT()的說明，如下所述：

傳回各陣列中所有對應元素乘積的總和。
語法：

SUMPRODUCT(array1,array2,array3,...) ，Array1, array2, array3, ... 是2到30個要求其對應元素乘積之和的陣列。

##CONTINUE##

1.基本操作

依照Excel的「說明」解釋此函數是陣列乘積的和，例如：A1與A2的值是1與2，B1與B2的值是3與4，則=SUMPRODUCT(A1:A2,B1:B2)是A1×B1＋A2×B2，答案就是11。

這個公式是：

A1×B1+A2×B2

「*」與「,」所得到的結果都是一樣。

另外SUMPRODUCT()也可以像SUM(IF())一樣，執行邏輯判斷的加總。例如：在D67輸入：

=SUMPRODUCT((E$2:E$62=B67)*(D$2:D$62))

答案就與C67一樣，運算的過程都與SUM(IF())一樣。SUMPRODUCT()比較特殊，它也是以陣列公式計算，但是不用按Ctrl-Shift-Enter。注意引數中間需要用「*」隔開。

2.多條件式總和操作
SUMPRODUCT()有個特殊功能，就是可以進行多條件總和，如果條件是計算「段政存」的「影印卡」支出多少金額？就可以利用多條件進行邏輯判斷，然後加總。

找出「段政存」的「影印卡」支出多少金額。當然也可以找出其他單項或多項項目。

C78:C86是「段政存」的「影印卡」到「辦公用品」的支出金額，點選C78可以顯示SUMPRODUCT()，其的公式為：

=SUMPRODUCT((E$2:E$62=A$73)* (C$2:C$62=A78) * D$2:D$62)

兩邊必須都是TRUE。「*」不能改成
「,」。「*」類似AND的功能。

至於三個條件或更多條件式的用法，只要將條件式依序填入*後面即可，但是最後一個引數是執行加總的範圍。

3.其他函數多條件式加總

SUM()的公式與SUM(IF())一樣可以計算多條件加總，與SUMPRODUCT()的條件句類似，但SUM()必須同時按Crtl-Shift-Enter而且「*」與「,」不能隨意改變。

B78:D86是使用這三種公式所得到的答案，三種公式都一樣，到底哪一種比較好呢？端賴使用者的喜好程度。

接下來需要思考如果要加總「段政存」與「周博東」兩個人所支出的費用時，應該如何計算才好？或則想要加總支出金額等於2000或文具印刷費時應該如何進行才妥當呢?

參考：<<業務寶典>>

函數SUM的應用

2009-01-01T14:08:00.006+08:00

SUM()本來是加總的功能，但是此節將配合其他函數使SUM()的應用更加精進。點選「插入函數」精靈，在「搜尋函數」裡輸入：SUM，點選「函數說明」，顯示「Microsoft Excel 說明」方塊，這是對SUM()的語法及操作說明，引述其中段落：

傳回引數串列中所有數值的總和。
語法：
SUM(number1,number2, ...) Number1, number2, ... 是您想要計算總和的 1 到 30 筆數值引數。
##CONTINUE##
讀者也可以從「說明」中得到其他函數的功能。

1. 單純加總
使用SUM()的時候，大部分都是加總指定的儲存格，也是最常使用的函數之一。除了在儲存格單純加總運算之外，也可以與IF()執行條件式判別運算。
這是簡單的費用支出表，通常在D3、D14、D20、D39、D45、D63執行類別的計算，在D64總計發生的費用。D3的公式為：

=SUM(D2:D2)

，就只有加總一個儲存格的值而已，D20的公式為：=SUM(D15:D19)，就是將D15:D19儲存格的值加總計算。D64的公式為：=SUM(D3,D14,D20,D39,D45,D63)，分別加總各項目的值。

如果只是單純的加總計算，用「＋」也可以達到目的，就不需要用SUM()的功能，就是SUM()還有其他功能，所以SUM()才特別好用。假如要計算某位請款人的所有支出金額時，就需要用到條件句加總SUM(IF())的功能，例如：要計算「段政存」的支出金額。

2. 單條件式加總
在C67輸入=SUM(IF(E$2:E$62=B67,D$2:D$62))，同時按Ctrl-Shift-Enter這三鍵，然後拖曳到C70。

{SUM(IF(E$2:E$62=B67,D$2:D$62))}

這個的意思是假如E$2:E$62請款人這一欄位等於B67「段政存」的條件就在D$2:D$62這個欄位加總。輸入之後必須按Ctrl-Shift-Enter這三鍵，資料編輯欄裡就會多出{}符號。也就是通知Excel必須進行陣列公式。何謂陣列公式呢？IF()這個函數，第一個條件是判斷邏輯值是真了還是偽，通常在判斷是否真偽時，都是一個值與另外一個值判斷是否符合。例如：A1＝1、A2＝1、A3＝3，函數IF(A1=A2,1,0)可解釋為假如A1=A2是真，就等於1，假的就是等於0，因為A1確實等於A2，所以答案就是1。如果公式改變為IF(A2=A3,1,0)，就可以判斷答案是0，因為A2不等於A3。以上不管IF的邏輯判斷式是A1=A2或者A2=A3都是一個值與另一個值得比較而已，並不需要同時按Ctrl-Shift-Enter這三鍵就可以計算。如果像上面公式的IF的邏輯判斷式為E$2:E$62=B67，是眾多的值與一個值做條件式判斷時，就需要用到陣列公式。這個公式如果不用陣列的話，儲存格就是#VALUE!，無法計算。
也可以使用「插入函數」精靈，看看IF()的運作方式，在G1輸入：=IF(E$2:E$62=B67,D$2:D$62)，然後點選插入函數精靈。
此公式首先判斷E2=B67是否真偽，再判斷E3=B67真偽，如果都是真，因為真正公式裡有SUM()，所以會執行第二引數，D2+D3。還有這必須是陣列公式，所以列數必須相等，例如：E2:E62對上D2:D62。

3. 驗證SUM(IF())公式
為了更進一步了解此SUM(IF())的陣列公式，所以使用另一方式解釋SUM(IF())的計算過程。首先，點選C67儲存格，資料編輯列就會出現公式。
選擇IF()將它反白，然後按F9鍵。

按F9之後，就會產生計算結果，這是IF()的邏輯判斷，是真就顯示數字，偽就顯示FALSE。然後SUM()就會將數字全部加總，但是遇到「FALSE」的記號就跳過去不予計算。
=SUM({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;100;FALSE;120;FALSE;FALSE;FALSE;FALSE;FALSE;32;FALSE;FALSE;FALSE;FALSE;140;2000;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;75;250;FALSE;FALSE;FALSE;130;FALSE;FALSE;185;2000;FALSE;616;760;FALSE;FALSE;FALSE;FALSE;109;600;250;1070;320;FALSE;FALSE;FALSE;250;FALSE;FALSE;481;350;FALSE;FALSE;FALSE;FALSE})

另一方面，也可以在F欄增加驗證的公式，看看所求的公式是否正確。

首先，F2儲存格裡的公式是E2=B$67，F3就是E3=B$67，以下類推，儲存格裡B$67是絕對位置往下拖曳是不會變動，F2儲存格裡的E2是相對位置，往下拖曳時在F3儲存格裡公式裡就變成E3，就是會隨著儲存格的位置改變而改變。從F欄可知，在F2儲存格裡E2（周博東） =B$67（段政存）會產生FALSE（注意藍色箭號），一直到第6個F8儲存格才是TRUE。再看看上面按F9鍵所產生的計算結果，前面6個都是FALSE到第7個是100，D8儲存格也是100。由此類推，只有符合條件才會顯示TRUE，也是SUM()計算的標的。
再回顧SUM(IF(E$2:E$62=B67,D$2:D$62))的公式，E$2:E$62=B67會一個一個比對，E$2=B67、E$3=B67…E$62=B67，如果不同時按Ctrl-Shift-Enter這三鍵（記得按之前，看看游標是不是處於資料編輯列上，否則必須按F2鍵），此公式就是錯誤，無法計算。比對完之後，假如是TRUE就會在D$2:D$62加總。其實IF()邏輯判斷值是FALSE的話，就需要執行另外的條件，但是此公式省略IF()的第三引數，所以就忽略條件為偽的計算。經過這些解釋之後，讀者就會很清楚了解條件加總的強大威力。

參考：<<業務寶典>>

到底標會的利率怎麼算-IRR初探

2008-12-28T22:41:00.003+08:00

最近永豐銀行推行MMA標會理財網，一般的標會最大的風險是倒會，而且倒會之後沒有保障，對會員是一種很大的傷害，此理財網以銀行為保障，所以不怕倒會，如果倒會銀行會負責。討論區有人討論標會的利率，但是有各種版本，確實，在跟會中，會員身兼放款者與貸款者，很難計算，但是有了明確利率才能比較此項投資是否妥當。標會利率應該如何計算呢？以下是我的推論。
##CONTINUE##

投資行為是一種比較，比較各個投資項目，找出對自己最有利的項目。通常銀行的利率是一個最基本的比較項目，因為銀行的利率是最穩固、最安全的投資管道之一。利率以%表示，所以任何的投資項目的盈虧都需要計算成一種比率，這種比率有內部報酬率、報酬率、貼現率、折現率、機會成本、資金成本…等等不同的名詞。如此才能與銀行的利率做比較，而且是比較複利而不是單利。

單利
你存在銀行的活期儲蓄存款都是單利計算，如果存入10萬，利率2%，存10天，請問要利息多少？
10萬 × 2%/365 × 10天
因為是每天都以單利計算，所以年利率2%必須除以365天。銀行每年都有兩次付息日，在6/20與12/20左右，所以在附息日之後，利息就可以併入本金計算。

複利
複利為利上滾利，將本金加上利息為本利和，下期就以上期的本利和再加上利息，計算就一直持續下去，直到合約截止。
複利的用法可分成終值、現值、年金終值、年金現職。
終值與年金終值可用Excel FV()計算。
現值與年金現值可用Excel PV()計算。

終值與現值是比較好理解，年金終值與年金現值很難以洞悉他的運作方式。
預計有一基金每年報酬率5%，計畫每年投資5萬，6年後有多少資產？這是計算年金終值，第一年投資5萬，複利期為5年…到第6年，然後將所有本利和加總G3:G8，就等於340,096，你也可以直接用 =FV(5%,6,-50000)，一樣求出相同答案。

假設購買一項保險年金45萬，在今後5年內，每年固定領取10萬，報酬率為4%，是否可行？我們將每年領取10萬依照複利期折現到今年，各年加總可得445,182，也可以用=PV(4%,5,-100000)，也會得到相同的值。

450,000－445,182（B9）＝ -4,818，當然這個投資是虧損。這個就是NPV淨現值，也就是將各期的支出及收入折現為現值，再扣除成本，如果是>0表示投資可行，<0 style="font-weight: bold;">IRR是甚麼呢?
如果你在Excel按F1，然後搜尋IRR就會有此函數的說明
傳回一連串以數字表示的現金流量之內部報酬率。與年金函數不同的是，IRR 函數的每期現金流量不一定要相等。然而，現金流量必須定期發生，如每月一次或每年一次。內部報酬率是指在某項投資計劃固定期間的支出 (負數) 及收入 (正數) 中所能獲得的利率。

如果你沒有學過財務方面的知識，相信你會霧煞煞，但是可以確定IRR()是一種利率。

IRR 就是假設NPV=0（無損益）之下，利率是多少？也就是IRR的值是多少，NPV才會等於零，將每期的金額不管是負數的支出或正數的收入都折現，給予一個 IRR內部報酬率，然後將各期折現後的金額加總等於0，也就是無損益。當然此IRR非常適合計算標會的利率計算，在13期跟會的過程中，你可能第5期才標到，所以前4期與後期7你支出會金，也就是現金流出，第5期你取得得標金額，也就是現金流入。將每一期折現，給予一個利率，讓他們加總等於0，這個利率就是IRR。

假設B欄為各期標金，約定會金為1萬元，C欄是根據標金所付出的金額，第8 期以550得標，所以C9就取得7萬加上D9的47,250元的得標金。C欄是活會的現金流也就是我借錢給會腳賺到利息，C15的IRR是17.21%表示我放款賺到的利率；另外，D欄是死會的現金流，表示我借了47,250，往後每月須繳納1萬元，所以你必支付22.99%的利率。因此，在標會的過程中，你扮演放款者與貸款者。

你是放款者的角色時，你賺了3,920元(I2)；若是貸款者的角色時，你虧了2,750元(I3)，兩者相加，你還是賺了1,170元(I4)。計算IRR是11.33%(I5)，有另外一種計算方式是：

利率＝ (當期得標金 × 2 )÷ 得標總會款 × 12 × 100%

利用這個公式去得I6為11.3%，這只是借款利率。

從IRR中很難得知到底是借款還是貸款利率，可是我們從I4得知，這是有盈餘，再從NPV來看，我們以利率2.7%(目前定存差不多這個利率)計算，得到889元，以就是說以2.7%的利率折現各個現金流，然後將他們加總是正數。所以，此IRR是賺的利率，也是放款利率。

再來看看另外一種得標方式，IRR是8.51%，合計是虧損，NPV也是負數，所以，這是虧的利率，也就是貸款利率。

我看了許多銀行的網站計算標會利率多過於簡便，所以很難反映一般狀況，這樣的說明或許還需要補強的，各位網友如果有更好的想法，歡迎提出，如此才能更精細找到利率。

有些利率的解釋如果還不懂，可以參考數位理財王，裡面有更詳細的解說，也歡迎參考這篇文章理財總動員之精算標會暴利無罪。

圖片作者：waɪ.ti

晨間日記-Excel程式進階版

2008-12-27T16:13:00.003+08:00

陸陸續續接獲許多網友的建議，所以我進行一些修訂。我會分別寄給各位網友，如果你從未留email或未收到請繼續留下。

新增與修訂功能如下：

標題可自由選擇，也會儲存標題
設密碼，忘記密碼可傳至你的Gmail
檢視某段期間可以與去年對照
顯示重要事項倒數日
設定欄位
匯入舊版資料

##CONTINUE##
舊版說明

新版保留舊版所有的功能，而新版新增功能如下：

1.輸入密碼
開啟檔案之後，會顯示密碼輸入的窗格，一開始請輸入123，然後再按"確定"即可。

2.密碼傳至Gmail
一旦忘記密碼，可以輸入4次，4次都錯誤就會顯示詢問是否密碼傳至Gmail的窗格。

當你按"確定"時，就會開啟"備份到Gmail"的窗格，然後輸入Gmail的密碼再按"傳送"就可以了。
接下來你可以到Gmail查看是否顯示晨間日記的密碼。

3.匯入舊版日記記錄
密碼正確就會進入"日記中心"工作表，然後按Ctrl-I準備匯入舊版記錄。
接下來顯示"日記匯入"窗格，再按舊版的檔案名稱，點選"開啟"，就會匯入。

4.設定密碼
在"日記中心"點選"設定"，然後B2輸入你的Gmail帳號，也可以在B3輸入密碼。
然後在D2輸入此程式的密碼。
H:U欄的設定會反映到"日記中心"的J9:J14，一旦活動日期是當日就會顯示。

5.倒數日顯示與標題欄
"setup"工作表的Q:R的項目會顯示在A:B，每一項都會顯示倒數日。
標題欄可以選擇，例如點選C2就可以選擇適當的標題。C2:F2都可以選擇。
標題設定在"setup"工作表的F欄。

6.檢視一段期間與去年對照
如果勾選這兩個項目，就可以檢視今年與去年的一段期間日記。
你就可以對照去年此時發生甚麼事情。

歡迎提出建議。

謝謝慈瑋提供「編譯錯誤發生於隱藏模組:SHEET1」問題的不一樣的解答
我使用的時候也出現「編譯錯誤發生於隱藏模組:SHEET1」問題，確實是沒有安裝月曆控制項(因office2003內沒有包含Access)，網上 mscal.ocx不好找，建議大家到http://www.fontstuff.com/mailbag/qvba01.htm 尋找，依照各個版本分門別類了。

但檔案該放的資料夾與版主說的不同，也不用重新啟動。

For Windows 95, 98 and ME 放在C:\Windows\System

For Windows NT, 2000, XP 放在C:\Windows\System32

從「開始」-->「執行」內輸入「regsvr32 mscal.ocx」來安裝mscal.ocx月曆控制項，應該就可以立即使用了!

晨間日記程式檔案

2008-03-31T15:38:00.002+08:00

晨間日記有許多好處，你可以在這裡找到一些資訊。
我用VBA設計了一個晨間日記的應用程式。
歡迎網友使用。
操作如下：

##CONTINUE##
Step.1 設定日期
開啟檔案之後，點選中間的日期，會顯示日期窗格，然後選擇適當的日期。
標題都可以改或換位置，例如：工作換到人。中間一些天氣...之類也可以改，但是日期不要改。
最好改一次就好，否則以後你會搞不清楚你的紀錄是屬於工作或人。

Step.2 輸入資料及插入圖案
接下來輸入資料，直接打在儲存格就可以了。
在儲存格上點選2次，可以插入圖案。
資料輸入完畢，也插入圖案。就需要按"儲存日記"
將剛剛輸入的資料儲存在資料庫裡。
但是這並不表示已經儲存檔案。

Step.3 插入檔案
再一次點選日期，選擇其他日期。
輸入資料之後，如果你要插入檔案時
可以點選"插入檔案"。

Step.4 搜尋紀錄
如果要以日期來搜尋時，在Step.1點選日期就可以搜尋了。
但是如果要搜尋字串，就必須點選"搜尋紀錄"
在框框裡輸入要搜尋的字串。
按"確定"之後，開始搜尋。
接下來顯是目標紀錄並詢問是否進行下一筆搜尋。
確定的話，搜尋下一筆；取消的話，就是這一筆。
紀錄將會顯示在儲存格裡。

Step.5 備份檔案
你也可以將這份檔案備份到Gmail。
你必須擁有Gmail信箱才能傳送。
只要輸入帳號與密碼就可以了。

如需檔案，請留下Email。

理財力之基金管理表

2008-03-27T08:33:00.003+08:00

這個管理表的功能有：

1. 直觀式檢視所有基金交易狀況。

2. 可查詢國內所有基金

3. 可以看到每筆基金的市價總值、利潤、報酬率、年化報酬率與投資比例。

4. 每筆基金都一個工作表來實際操作基金交易狀況。

5. 可查詢基金某日或某期間的淨值並繪製淨值走勢圖。

6. 可查詢多國今日匯率及歷史匯率並繪製匯率走勢圖。

7. 基金結束之後就轉入結案基金工作表。

8. 以最簡單的輸入方式，取得最大的效果。

##CONTINUE##

步驟1. 啟動程式

啟動基金管理表的程式之後，「基金總覽」是主工作表，所有資料會匯整到這裡。A:L是基本資料與原始購買的訊息；M:Q是當日基金價值部分，也是你最關心的資訊；R:U是進一步統計資料，所以你可以很快了解投資這筆基金所獲得的利潤、報酬率、年化報酬率以及投資比例等等狀況。

步驟2. 選擇基金公司

這個工作表你只需要操作A欄「基金公司」、B欄「基金名稱」與D欄「節數日期」。一開始點選A2就有下拉式方塊，你可以選擇理想的基金公司，然後這個公司旗下有許多基金商品，你就可以選擇B2的選單。選完之後，記得按其他儲存格，任何一個都可以，再按B2，B2的字串就會變色，表示你可以點選這個儲存格來開啟工作表。如果你已經暫停這個基金的交易就必須再D欄填入日期，只要在D2點選兩次就有日曆選單出現，就可以選擇適當的日期。

步驟3. 基金操作表-日期選擇

在上個步驟點選B2之後，就會產生一個新的工作表，底下的工作表索引標籤名稱與「基金總覽」的B2一樣。接下來點選這個工作表的A2兩次，顯示日曆選單，右上角可選擇年度與月份，然後再點選目標日期就可以了。

步驟4. 基金操作表-投資金額輸入

然後在B:E欄輸入金額，如果你是投資定期定額，就在B2輸入投資金額。注意投資費用有區分外加與內含，你必須在Q2:R2點選其中一個，如果是外加你必須加上費用部分。這個表格你必須填入A欄的日期，還有B:D欄選擇一個操作標的，G:H欄的幣值，J:L欄的費用等等，其他部分會自動計算。當你選擇適當的日期之後，這個基金的單位價值N2就會顯示出來，必須連上網路截取資料，所以需要一點時間來下載；當你點選H2的時候，選擇你要計算的幣值，I2就會找出當日的匯率。

步驟5. 基金操作表-費用金額輸入

接下來我們一筆一筆的輸入交易資料，如果你選擇費用外加的話，B欄最後一個有資料的儲存格會顯示提醒你的註解，當滑鼠指向它的時候，就會顯示出來。假設工作表的資料是我們的基金交易過程，你可以點選工作表底下的索引標籤回到「基金總覽」，也可以點選A1返回。

步驟6. 基金操作表-輸入全部交易

接下來我們一筆一筆的輸入交易資料，B欄做後一格儲存格會顯提醒你的註解，當滑鼠指向它的時候就會顯示出來。假設工作表的資料是我們的基金交易過程，你可以點選工作表底下的索引標籤回到「基金總覽」，也可以點選A1。

步驟7. 匯總基金交易資料

回到「基金總覽」工作表，點選A1就可匯入所有基金的交易資料。接下來在輸入第二個、第三個的基金交易資料並匯入，就會將所有資料顯示在「基金總覽」，也會自動計算R:U欄統計部分。

步驟8. 基金結束交易

如果這筆基金已經結束交易，就要在D欄選擇結束日期。一樣在D2點選兩次，顯示日曆選單，再按日期，D2就會顯示你所選的日期。B2的連結就會轉變，然後再點選B2就會轉入「結案基金」工作表。

步驟9. 顯示結案基金

點選B2之後就會來到「結案基金」工作表，表示此筆基金不再交易，此筆基金的工作表的交易過程都會移到這裡，工作表會被移除掉。如果同樣的基金想要再交易就必須在「基金總覽」另外開啟新的交易基金。

步驟10. 繪製統計圖表

點選Q1:U1其中一格就能繪製統計圖表，如果點選Q1就會繪製市價總值的比例圖，將你所投資的基金依照比例匯成圓形圖。從圖中可以清楚看到各個基金所佔的比例分配，其他的功能也是一樣。

步驟11. 基金資料搜尋

你可以從「資料搜尋」來找尋基金淨值的相關資料。A:F欄為基金績效資料，是由MorningStar所製作的評估報告。G:H欄需要輸入資料才能找到目標基金，首先點選G2選擇基金公司，然後點選H2選擇基金標的，接下來點選「開始日期」與「結束日期」，再按「搜尋」就可以搜尋目標基金的資料。

步驟12. 繪製淨值走勢圖

等資料匯入之後，就可以點選「淨值走勢圖」就可以看到淨值的趨勢。

步驟13. 搜尋匯率

匯率可分成今日與歷史匯率的搜尋，A:E欄為今日的匯率；K:L欄為歷史匯率。今日匯率的搜尋需要點選「今日匯率」按鈕就可以連上網路搜尋；而歷史匯率必須選擇「開始日期」、「結束日期」以及「選擇外幣」，注意選擇外幣需要點選I12，再按「搜尋」就可以連上網路來顯示歷史匯率，連上網路需要一點時間。必須注意一點，每家基金公司的參考匯率都會不同，所以顯示匯率不一定與你所交易的匯率相同，但是不同處都是些微變化而以。點選「匯率走勢圖」一樣可以看到匯率趨勢。

網購後端銷售資料整理(Yahoo,興奇,PChome請款用)

2008-03-26T15:49:00.008+08:00

前幾個月有一位朋友希望我幫他的朋友寫一Excel VBA程式，因為他這位朋友是開網購的，有跟Yahoo，興奇，PChome生意往來。問題出現了，這些網路公司的對帳單編號與朋友的不同，例如：PCHome會寄Excel檔的對帳單明細表給廠商，所以他必須要請一位人來處理對帳工作，在Excel的檔案中剪下與貼上，真了很浪費人力。於是我就幫他寫一個程式，只要PCHome或者Yahoo將對帳單寄來的時候，執行程式，一下子就解決了。前後不用花費1分鐘，真是太方便了，也可以省了一個人力。

這是對方的對帳單，需要進一步的整理。
##CONTINUE##

使用VBA將對帳單整理出來。

理財力之自動挑選優良基金的4433法則

2008-03-26T11:46:00.002+08:00

台灣國內與海外的基金有幾百支，一般的投資人絕對沒有那麼多的時間一支一支的研究。所以台大財務金融系所教授李存修、邱顯比建議使用4433投資法則作為挑選基金的方法。所謂的4433法則是：

第一個『四』：一年期基金績效排名在同類型基金前四分之一。

第二個『四』：二年、三年、五年、今年以來基金績效在同類型基金前四分之一。

第三個『三』：六個月基金績效排名在同類型基金前三分之一。

第四個『三』：三個月基金績效排名在同類型基金前三分之一。

##CONTINUE##

一共有四種關卡來篩選績優基金。先以一年為基準，再來是二、三、五年，然後再選擇六個月，最後以三個月的基金績效為主。所以從篩選之中可以了解是注重中長期的績效為準則，再來考慮短期的基金績效。但是如何計算這些基金的績效呢？還好這兩位教授每個月固定在「台灣共同績效評比」網站發佈評比相關數據資料，而且這些資料再發布之前，都會經過各投信公司複查之後沒有問題才公布。有了這些資料你就不用使用公式來計算，只要了解一些指標所代表的意義就可以了。如果你連這些意義都不想了解，也可使用3344法則來挑選適當基金。

檔案中的指標，例如標準差、Beta...等等，你可以用Google找到說明。

我在此用Excel VBA設計一個程式，可以自動利用4433法則找到目標基金。

操作說明：

步驟1.

首先你必須到「台灣共同績效評比」下載評比檔案。程式開啟之後，點選「增益集」-->「理財總動員」-->「基金篩選」，就會顯示「基金篩選」的窗格。

步驟2.

窗格中有兩個主要分頁，一個是「選擇類型」，可以選擇你想要搜尋的基金類型，一共有四個分類，分成大分類、中分類、小分類和細分類。大分類一定要選，其他三種分類不一定有資料，有些中分類一定要選，點選上層分類的時候，如果點選下層分類有顯示資料就表示還有下層分類。例如：點選小分類的「c.單一國家」的時候，細分類還有選項可以選擇。基金類型可以可以查看這個評比檔案的目錄，就有很清楚的分類。

步驟3.

另外一個是「設定」，可以設定一些計算的數字。設定值是根據4433法則而來，你可以修訂這些比例，找到你認為適當的數字。其中六個月的比例計算方法，你可以選擇是以二年、三年或五年的比例，但是有些基金沒有三年以上的資料，這一點必須注意。

步驟4.

了解這個程式的操作之後，在「選擇類型」頁面的大分類選擇「一、股票型」，在中分類選擇「1.投資國內」，再按「確定」就可以了。經過程式篩選之後，第一次選擇一年並把中選的基金標上黃色，第二次選擇二、三、五年並標上綠色，第三次是篩選六個月排名，六個月是根據兩年的排名而來，並幫上淺藍色，最後是看三個月排名部分，篩選只剩兩支基金，這個表示這兩支基金在這兩年來都表現不錯，短期之內績效很好。

理財力之精算標會暴利無罪

2008-03-26T11:44:00.001+08:00

互助會又稱為標會，在台灣非常盛行的一種融資的管道，相信許多台灣人都有跟會的經驗，也有不在少數人被倒過會。我們常常在電視或報紙上看到被害人（會腳或會員）追打或控訴加害人（會頭或會首）的畫面，這些因素都是會首或會腳倒會，其他會腳想要討回公道，也就是將投入的錢拿回來，但是常常事與願違，到頭來都是一場空。你需要錢，標會是一項不錯的選擇；你想獲得高利率，確實標會能帶給你比銀行更高的利率水準，但是你必須防範被倒會的風險。以下將告訴你，如何精確算出你的報酬率？何時標會比較利？

##CONTINUE##

範例：會款計算.xls 的「會單」工作表

步驟1. 計算會員總得款

函數SUM()是合計區域裡面的數字，所以在C2儲存格的公式是=-SUM(C$2:C$7)。因為是計算C2到C7的數字，這區域的數字是負數，所以必須在SUM()之前加上負號，可是當你輸入完公式之後，卻顯示循環參照警告。因為Excel會重複計算儲存格的公式，所以這個公式會自己計算自己的儲存格，形成一種循環計算的現象，因此你必須改為手動計算。

步驟2. 更改計算方式
首先點選「Office 按鈕」-->「Excel選項」-->「公式」（Excel 2003按「工具」-->「選項」-->「計算」），再點選「手動」-->確定」。

步驟3. 複製公式

再次在C2輸入SUM()的公式，然後複製C2，貼在紅色儲存格。結果答案都一樣，顯然是錯誤的，因為計算方式是手動，所以你必須點選該儲存格2次或者點選函數列也可以，然後再按Enter就是手動計算一次，答案就出來了。注意公式的$是絕對位置的標示。

步驟 4. 計算總繳會款與差異

我們知道我們所得到的總會款與每期所應該繳納的會款之後，就可以計算會員總繳會款與差異。I欄就是計算後的數字，J欄則是會員總繳會款與取得總會款的差異。I2的公式為=SUM(C2:H2)-C2，將每一期的會款加總再扣掉自己儲存格。J2的公式為=C2+I2，總繳會款加上取得會款就可知道你賺取或損失的會款。當然沒有加上時間成本，例如J1會首A是0就表示不賺不賠，但是如果會首將這筆錢放入銀行就可賺到利息，將錢投資就可賺到報酬率，所以計算差異結果只是粗估會員們大概的損益。

外標制也是利用同樣的算法算出來，你可以參考「外標制」工作表，看看儲存格的公式。

注意如果停止計算時，需要調回自動，否則其他表格計算會產生問題。

你到底支付或取得多少利率與利息

除了用Excel表格計算取得得標會款之外，也可使用公式方法。

取得總會款＝死會總收入會款＋活會總收入會款

死會總收入會款＝每期約定會款 × 死會會員人數

活會總收入會款＝（【活會人數－ 1】－競標金） × 活會會員人數

活會人數－1是扣掉自己1名。

老周跟了一個24人的會，採內標制，每個月的約定會款為1萬元，老周在第10期出價1,500就把會標下來，請問取得多少總會款？

範例：得標利率計算.xls 的「總會款」工作表

根據公式將公式填入，E8死會會款的公式是=E4*E6，活期會款則是=(E4-E5)*E7，然後總會款是將死會與活會加總起來就可以了。

如果老周想要計算取得總會款的利率時，應該如何計算？（下面的公式是一種比較簡單及普遍的算法）

取得總會款利率＝ (【競標金 × 2】 ÷ 取得總會款) × 100%

年利率＝取得總會款利率 × 12（個月）

代入公式

取得總會款利率＝ (【1,500 × 2】 ÷ 209,000) × 100% ＝ 1.44%

年利率＝ 1.44% × 12 ＝ 17.22%

通常算利率時，只要將支出金額除以總得款就可以了，但是得標的會員除了支付競標金之外，這個會員就成為死會，也就是你無法再賺取他人的競標金，因此你的競標金還要加上他人的，但是他人的並不會跟我們一樣阿！沒錯，所以這只是粗估金額，假設他人的競標金與我們相同，因為我們無法預測以後標會的競標金。所以是競標金乘上兩倍。

如果這個案例是半個月就開標一次的話，公式就應該改變為：

取得總會款利率＝ (【競標金 × 2】 ÷ 取得總會款) × （30 ÷ 15） × 100%

年利率＝取得總會款利率 × 12（個月）

代入公式

取得總會款利率＝ (【1,500 × 2】 ÷ 209,000) × （30 ÷ 15） × 100% ＝ 2.87%

年利率＝ 1.44% × 12 ＝ 34.45%

如果是外標制，而且是每個月開標一次的話，公式就會變化。

取得總會款＝死會總收入會款＋活會總收入會款

死會總收入會款＝每期約定會款 × 死會會員人數＋死會競標金合計

活會總收入會款＝（【活會人數－ 1】－競標金） × 活會會員人數

公式與內標制類似，只是在死會總收入會款中多加一項死會競標金合計，也就是B2:B9的合計。代入公式之後，得到總會款為G11儲存格的243,300元，年利率為14.8%，至於半月標則是29.59%。

內部報酬率

雖然此種計算方式可以找到得標時的年利率，也就是相當借款利率，但是這只是粗估數字，而且沒有加貨幣時間的價值。我們必須了解更精確的算法，如果你拿出你的計算機計算正確的利率，的確有點困難度，還好Excel有個IRR函數可以更精確的計算內部報酬率。所謂內部報酬率是未來的收益折算現值與目前的支出總和為0，貼現率（利率）是多少？所以IRR()可以計算一組定期發生的現金流量的內部報酬率。的確有點難理解，讀者如果想要探究其原因可以參考後面的說明。

範例：得標利率計算.xls的「IRR」工作表

一樣是上面的案例，A欄為每期期數，一共有24期。B欄為各期的競標金，第一期是會首所以B2是0，其他是各得標會員的競標金。C欄為內標制，D欄為外標制是老周支出與得到的會款，負數表示支出，正數是得到總會款，所以，老周在第10期以1,500得標，取得內標制會款209,000元。C26是內部報酬率=IRR(C2:C24)，得到12.1%，這是一個月為一期所以年利率則需要乘以12，得到145.4%。但是這顯然是一個錯誤的答案，因為Excel IRR()是以反覆運算法來取得答案，它的語法是

IRR(values,guess)

第二引數的guess，如果省略的話，就以10%來進行反覆運算，10%是猜測值，但是我們所取得答案如果超乎想像就需要給予適當的猜測值。所以我們在C28再向下修正為1%，所以得到答案為1.3%，年利率為16%。IRR()有時並非只有一個答案，很難講哪一個是比較正確，但是以這個案例顯然1.3%比較適當。所以表示你標到的錢是以年利率16%貸款而來，的確比目前的銀行利率還高，也就當利率等於16%時，你的損益才會平衡。

破除報酬率迷思

當然投資就是希望能有高報酬，標會也一樣，我們總是希望從其他會員賺取更多的會款，也希望自己能夠以最少的競標金就能得到會款。別人要出多少競標金我們是無法控制，但是我們卻能掌握我們的競標金。我們可以依據銀行的利率與本身的需求來找出適當的競標金。

一般報酬率的計算方式是：

報酬率＝（取得總會款－總支出） ÷ 總支出 × 100%

所以，如果以上面內標制的例子，代入公式：

報酬率＝（209,000 － 216,700） ÷ 216,700 × 100% ＝ -3.6%

從報酬率可以得知虧損3.6%，可是這樣的計算方法卻忽略了貨幣的時間價值，畢竟你如果取得總會款存放在銀行也會滋生利息，上面的計算方式完全不考慮你將錢拿去投資所應該擁有的利率。所以我們必須將未來的終值換算成現值，才能準確計算報酬率。

假設以月利率0.2%為準（點選M2可以選擇利率），F欄為內標制每期折現金額，G欄則為外標制每期折現金額。折現之後，算出內標制的總支出為168,966元，與取得總會款的折現（F11）的差，是虧損的現象，再套入上面的公式，有-3%的報酬率，外標制則是-2.5%。你可以調整M2的利率水準看看是否利率調高，報酬率是否提高。F32投資的盈虧你也可以用NPV的函數。

如果你想要到最後第二期才標，而利率水準是2%的話，是否報酬率比較高？其實也不一定，我們從K31得知是-9%。這是個比較極端的例子，因為假設月利率水準是2%，年利率就是24%，以目前台灣的現象是不可能，所以可將利率調整為0.2%就符合目前的現況。

將利率H2調整為0.2%的時候，你可以發現F31報酬率是-3%，而23期得標的話，K31則是12%。所以利率越高就越早得標，然後再去投資，就有可能獲利越高，利率越低，最好越晚標，因為放在銀行的利息也不多，報酬率就越高。當然也必須考量死會競標金的水準。

競標價格多少才會有賺頭

從IRR()的計算結果就可以得知，我們所負擔的利息；從NPV之中也可以了解我們的盈虧，甚至可以得到我們的報酬率，但是如果想要了解這一期要出多少競標金才沒有虧損時（先不考慮銀行利率），應該出多少錢？

範例：得標利率計算.xls的「競標金」工作表

首先點選功能區的「資料」-->「假設狀況分析」-->「目標搜尋」就會顯示「目標搜尋」窗格。為了讓利率等於0，所以C26:C27的IRR需要等於0。「目標儲存格」需要填入C26或直接點選C26儲存格也可以，「目標值」需要輸入0，因為要將IRR調整為0。「變數儲存格」就是我們所要出的競標金，所以就可以直接點選B11。因為C26是計算C2:C25的內部報酬率，所以C11必須要跟B11有所牽連，而C11的公式中，有B11的計算。然後點選「確定」。

答案出來了，B11等於958，IRR就等於0。當然，如果考慮銀行的利率，你就會有賺頭，畢竟958的競標金，是打平的狀態，如果將這筆錢放在銀行，又可賺取利息。或許，你急需要這筆錢，你也可以在「目標值」內輸入你能承擔的利率，這樣就能算出，競標金的價錢。如果想要實際的盈虧金額，也可將「目標儲存格」設定在B32的NPV上。

這裡說明標會所應該注意的事項與計算方法，雖然標會利息比較高，但相對風險也會比較高，以目前工業化會越來越高，人情越來越薄的狀況下，標會並不像以前的社會那麼流行，但是這個確實是個籌資的好管道。畢竟向金融機構借錢，不一定借得到，而標會都不用任何擔保，但相對而言，利率卻有點高。你可以利用上面的方法來計算你所能負擔的利率與取得的利息。一般計算報酬率沒有加上貨幣的時間價值，一旦考慮這個因素時，你就會發現負擔沒有那麼可怕，實際獲利也沒有那麼高。我們上面使用IRR()、NPV()及報酬率來衡量獲利狀況，但是如果狀況不一樣時，有學者認為NPV()是比較好的方法。

Excel許多財務函數，FV()、PV()、IRR()、NPV()等，大部分的理財計算都是運用這些函數，將資金折算成現值或計算為終值來做比較，評估其報酬率。不管基金或股票，其報酬率也不脫這些函數，只是時間如果不固定就需要使用XIRR()或XNPV()來計算，以後我會介紹如何使用這些函數。

＊IRR內部報酬率說明

假設購買一項保險年金45萬，在今後5年內，每年固定領取10萬，報酬率為4%，是否可行？

450,000－445,182（B9）＝ -4,818，當然這個投資是虧損。這個就是NPV淨現值，也就是將各期的支出及收入折現為現值，再扣除成本，如果是>0表示投資可行，<0表示需要剔除這個投資。以這個案例，投資了45萬元，五年下來可以取得50萬，看似大於期初投資金額，但是這是忽略貨幣時間價值，如果加上利率，將各期折現之後，五年下來總共是445,182元，反而虧損4,818元。我們再來看看B8的金額，假如目前有82,192.7元，利率一樣4%，5年之後，本利和是多少？

這是一般的複利利息算法。
FV₅ ＝ 82,192.7 × （1 ＋ 4%）⁵

＝10,000元

沒錯就是1萬元，你也可以將各年度折現金額計算終值，得到的答案都是1萬元。IRR內部報酬率等同於利率、貼現率、折現率、機會成本、資金成本…不同的名詞。IRR與NPV的公式都一樣，只是想要求得的項目是不一樣。

從以上可以了解NPV是：

NPV ＝各期PV加總－ Cost

PV的計算式中有個利率，IRR就是假設NPV=0（無損益）之下，利率是多少？也就是IRR的值是多少，NPV才會等於零。根據PV的公式可以了解IRR是很難求出來，可以用試誤法或者圖解法，但是你用Excel IRR函數就更簡單了。

範例：得標利率計算.xls的「IRR說明」工作表

在D10輸入=IRR(D4:D9)，就可以求出3.62%的內部報酬率，NPV才會等於0，利率負擔過大就有可能加大虧損。其實如果是固定收益，也可以使用RATE()，=RATE(5,-100000,450000)，一樣得到3.62%的值，可是各期標會的支出金額是不固定，所以使用IRR()是比較恰當。

取消惱人的Excel安全性警告視窗的步驟

2008-03-26T11:43:00.002+08:00

我在最佳檢視方法-口袋GTD與一目暸然的月行事曆使用Excel VBA設計程式來使任務可以更有效率地檢視，但是每次打開這些檔案總會顯示"安全性警告"。當然，按個鍵就可順利執行，如果你不想要顯示此視窗的話，可以採取xla的增益集方法，或者使用"數位簽名"的認證方式。

點選工具-->巨集-->安全性-->安全性層級，也可調整4種安全等級。如果調整為低的話，你開啟什麼檔案都可以，不過外面病毒很多，風險很大，所以不建議如此作法。

增益集是儲存該檔時，在"檔案類型"選擇xla，然後點選工具-->增益集-->瀏覽找出該檔案即可。

##CONTINUE##

以下是以數位簽名方式來說明如何取消"安全性警告"視窗。

Step.1 安裝SelfCert.exe
SelfCert.exe 程式不是 Microsoft Office 標準安裝的一部分。如果要安裝 SelfCert.exe 程式，必須執行 Office 安裝CD。選擇office共用的功能-->VBA Project數位憑證-->從我的電腦執行-->更新。

接下來顯示安裝程序，導入檔案。

Step.2 執行SelfCert.exe並填入名稱

通常安裝之後，SelfCert.exe會在C:\Program Files\Microsoft Office\OFFICE11或OFFICE10、OFFICE12。找到檔案之後，執行該檔。顯示窗格，然後填入憑證名稱，再按確定。

Step. 3 啟動VB編輯器

先打開目標檔案，然後點選工具-->巨集-->Visual Basic編輯器。

Step.4 設定數位簽名
開啟VBE之後，點選工具-->數位簽名。

然後顯示數位簽章窗格，點選選擇。

然後看到剛剛在Step.2的簽名，再按確定，結束認證過程。

Step.5 重新啟動檔案
接下來關閉該檔案，再重新啟動，顯示"安全性警告"窗格。然後勾選"總是信任巨集從這個發行者"，再點選啟用巨集即可。可以點選"詳細資料"，顯示剛剛認證的資料。以後只要開啟此檔案就不會顯示"安全性警告"的窗格。

一目暸然的月行事曆

2008-03-26T11:40:00.001+08:00

一直以來我都認為桌曆是很棒的時間規劃工具，一般的桌曆大都是整年份而且以週為單位，日期旁邊都有表格以利記事。事情一來就寫在桌曆上，時間一到就可看到需要處理的事項。但是它還是有缺點，以週為單位無法綜觀整月的待辦事項，而且提醒方面也不是很理想。除非到了該週才知道需要做什麼事，也許可以將該事件寫在當日的前幾天，但是還是有可能錯過該事件。提醒功能還是使用電子式工具比較理想，例如:PC、手機、PDA、鬧鐘...。

也有一種是以月為單位的桌曆，它的好處是可以看到整月的待辦事項或重要會議。如果你認為以週為單位並無法綜觀整月的行程時，或者不喜歡其他電子工具時，使用以下 Excel萬年曆檔案能將空白或有待辦事項的表格列印下來，壓在玻璃或塑膠膜下，或者使用磁鐵連同表格吸在座位前方的隔板上，將能更直觀地了解整月的代辦事項。紙張式書寫也很非常方便，也可拿下來，將它折起來，放在皮夾內或上衣口袋，非常容易攜帶，也容易檢視。

##CONTINUE##

首先點選這裡下載程式，然後點選E1及G1可以選擇年月，底下表格就會顯示該年的月份月曆，每日有六個表格可以填寫，綠色儲存格為當天日期。
如果能配合Google文件上傳此檔案，就可以分享給他人，也能到別處檢視，但是Google文件沒有"驗證"功能，所以年與月的查詢就需要自行輸入。

*2007/9/24 我又添加以年為基礎的桌曆，操作很簡單，只要選擇您所需要的年份即可。

Excel 字串倒頭栽

2008-03-25T15:23:00.000+08:00

我用Excel VBA製作最佳檢視方法-口袋GTD時，因為印出表格後需要摺疊，所以有些表格的字串需要倒轉180度，但是Excel只能轉90度，因此我只好將表格複製成圖片，然後將它旋轉180度，然後印出來。
可是圖片倒轉的文字會扭曲，而且有點對不準。昨天我從網路上搜尋此方面的訊息，發現應該可以讓字串倒轉180度。操作過程如下:
##CONTINUE##

步驟 1.
先選擇儲存格，然後點選左上角字型框，在前面另外填入@。

步驟 2.
點選"格式"，顯示"儲存格格式"窗格。

步驟 3.
選擇"對齊方式"。

步驟 4.
勾選"自動換列"。

步驟 5.
填入"90"或點選上面的指標移動。注意"文字"的框框。

再按"確定"即可完成。

以下是其他字串的旋轉方式，一樣利用上面一些功能調整，"文字"框也一併考慮。讀者可以試看看，創造不同的轉向配合您的需求。

ABC 80/20重點法則在Excel的應用

2008-03-25T15:21:00.000+08:00

在我的拙作裡，曾經提過ABC的分析。雖然長尾效應的觀念最近在業界非常流行，但是對一般企業來講，重點管理還是非常根深蒂固。長尾理論只能在正常銷售之外，額外多出的銷售方法。
ABC分析來自80/20法則也就是柏拉圖（Pareto）法則（與希臘哲學家不同人），柏拉圖是義大利經濟學家，他研究義大利社會財富的分布幅度，發現較少人掌握較多的財富。所以此特性可以延伸到許多社會現象，例如：

80%的業績來自於20%的產品

80%的國民住在20%的城市中

80%的人受制於20%的政治人物

80%的利潤來自於20%的產品…

##CONTINUE##

80/20法則最主要讓我們了解關鍵少數的重要性，也就是我們只要掌握這關鍵的少數就可發揮管理功能。管理每樣產品都是一樣的時間，所以花20%的時間去處理重要的產品的銷售，將節省80%的時間。公司產品如果上百種或上千種時，在管理上就會不知所措，如果將重要產品提出來，實施重點管理將有事半功倍的效果，不只是產品而已，也可以是客戶，利潤…等等，舉凡工作上的任務都可以按此標準來處理。

80/20法則是要發揮這個20的影響力，但是這個20也不一定是20%，或者說也不一定要二分法分成80與20，現在大部份分成ABC三級，假如要分析的品項太多，也可以分成ABCD四級，至於這些等級要多少比例來分析呢？要看企業對A級產品的重視程度，A級是往後重點管理區域，所以A級越大所需要人員及精力也越高，大部份是分成70%、20%、10%。如果品項比較少，也可以分成80%、15%、5%。

下圖是ABC分析圖，可以看出A級產品品項總是少數，但是占公司的營業額份量超過一半以上。

Excel 範例操作

步驟 1. 拖曳欄位清單到樞紐分析表欄位

步驟 2. 計算累計比例

步驟 3. 統計ABC的結構

F2是統計A級的銷售額，使用SUMPRODUCT()。

=SUMPRODUCT(($C2:$C85<71%)*$b2:$b88)

H2是統計C級的銷售額，使用SUMPRODUCT()。=SUMPRODUCT(($C2:$C85>90%)*$B2:$B88)

G2是統計B級的銷售額，=B89-F2-H2，將總銷售量剪掉A、C級銷售量即可。

至於各級的品項統計是使用COUNTIF()，F3的公式為：

=COUNTIF(D:D,"A")

步驟 4. 繪製ABC圖表

首先點選A1:C88，再按「圖表精靈」

步驟 5. 修訂ABC圖表

在89.9%的點也是做同樣動作，圖表就會顯示69.9%與89.9%的數字，也就是ABC級設定的界線，然後用「繪圖」的方式，點選線條在69.9%與89.9%的點往下拖曳到底線。注意劃線的時候需要先點選圖表任何地方，線條才不會在移動時不見了。接著點選「繪圖」文字方塊，輸入ABC級文字，這樣就大功告成了，顯示「ABC柏拉圖分析圖」。

以上文章來自此書。

隔行上色使資料更清楚

2008-03-24T22:17:00.000+08:00

當我們在看銷售報表的時候，內容密密麻麻，看到眼睛都花了，因為銷售記錄有點長，眼睛從A欄看到H欄，常常看不準。如果是閱讀書面報告，大部分的人都是拿一隻直尺對準資料，這樣才不會看錯位置。Excel工作表的記錄如果太多欄位，也可以用分隔視窗將欄位縮短以便對準資料。但是如果能每隔幾行上色也可以達到效果。這是一種顏色管理，必須使用「設定格式化的條件」精靈並在條件裡面設定公式，將工作表儲存格上色就容易清楚辨識。

首先，點選要上色的資料範圍，然後點選「格式」-「設定格式化的條件」。

##CONTINUE##

顯示「設定格式化的條件」對話方塊，選擇「公式為」然後輸入公式：

=MOD(ROW(),2)=1

ROW()是取得參照位址的列號，MOD()是求第一引數除以第二引數的餘數。所以公式是求將列號除以2所得的餘數。所以，A2、B2、C2…列號都是2，2÷2＝0≠1就是FALSE，不會執行格式的變化。如果是A3、B3、C3…的儲存格，列號都是3，3÷2＝1＝1就是TRUE，會執行格式的變化。所以單列就不會上色，雙列就會。如果要隔三列上色的話，就要改成除以3，隔四列就要除以4。

再按「格式」來設定顏色。當公式的條件為真時就會執行格式設定內容。

隔行上色雖然可以對準記錄資料，但是在篩選後，隔行上色就會失效，最主要的是ROW()只能判斷目前所處的列號，篩選後有些欄位會隱藏，ROW()就沒辦法隔行上色，所以使用SUBTOTAL()可以忽略篩選達到隔行上色的效果。

公式應改為：

=MOD(SUBTOTAL(3,A$2:A2),2)=0

SUBTOTAL(3)是COUNTA()的意思，所以在A2儲存格就會先判斷A$2:A2(資料範圍)是幾個？答案是1，然後1÷2的餘數是1不等於0，所以A2是FALSE就不用上色。

點選H欄的「因點」並檢視工作表是否沒有隔行上色，發現此公式確實有隔行上色的效果。

備份檔案到Gmail-不再煩惱檔案遺失

2008-03-24T12:55:00.000+08:00

檔案遺失或損壞是我們最煩惱的事情，你花了這麼多的時間設計、建立、輸入，卻在種種原因之下，不見了或不能用了。如果你能將這這個檔案隨時備份到別了設備之中，等檔案發生問題時，你就可以將它下載下來。
你可以使用Firefox搭配Gspace將檔案移到Gmail，但是我們常常忘了做。
以下是Excel的備份程式，你可以試看看，在輸入資料之後，將此檔案上傳到Gmail。
Gmail的容量陸續增加當中，目前是7G多了。每次可以傳20MB的檔案，對一般的小檔案綽綽有餘。
##CONTINUE##
檔案下載

Step.1
檔案下載之後，開啟Excel 2003。點選"工具"->"巨集"->"Visual Basic編輯器"。
點選左側的"設定"，然後設定你的帳號與密碼。
備份之前你必須擁有Gmail帳號，才能傳到Gmail。
但是如果你要傳到其他的Email也可以，注意要取得它的SMTP並設定。

Step 2.
然後在功能列上點選"備份"->"傳送檔案"，就會顯示窗格，如果訊息顯示沒有問題
再按"確定"上傳。
你也可以選擇"刪除備份選單"，將這個選單從功能列移除。

最後到Gmail看看是否已經傳過去了。