1

林偉川

常用的進階函數

2

文字函數-Left、Right及Mid函數• 語法： Left (text,num_chars)，Right

(text,num_chars) ，MID(text, start, num_chars)• Text為含有所要選錄文字之字串。• Num_char指定您要 Left、Right及Mid選入的字元數。

2

3

文字函數-Concatenate (&)及LEN函數• 定義：將數個文字串連成一個文字串。• 語法：CONCATENATE (text1,text2,...)• Text1, text2, ...是要連接成一個文字串的 1 到 30 個文字串。文字項目可以是文字字串、數字或單一儲存格的參照位址。

• 定義：LEN 傳回一文字字串的字元個數。• 語法：LEN (text) • Text為所要計算字元個數的文字串，字串中所有的空白亦

當作字元來處理。

4

邏輯函數

• 簡而言之，邏輯函數就是用來判斷是非黑白的。我們先來看看一般邏輯值的應用。利用且、或來組成的條件就是複合條件。

• Ex. =c9>=60

3

5

邏輯函數-AND(且)函數• 語法：AND(logical1,logical2, ...)• logical1,logical2, ...，係指您要測試的 1 到 30 個條件，可能是 TRUE 或 FALSE。

• 也可以透過函數引數交談窗來協助我們填入適當的引數值！在Logical2(第二個條件式) 按一下，就會出現第三個條件式讓你加入。

• =and(c3=“男”,d3>=170)• =and(d3>=165, d3=170),"太高了",IF(C2="男

","","中等身高"))

6

邏輯函數-OR(或)、NOT函數• 語法：OR(logical1,logical2,...)• =IF(OR(C2="女", D2>=170),"身高170公分或為女性","條件不符")

• 定義：將引數之數值予以反轉。NOT 可用來確定某一數值不等於某一特定的數值。

• 語法：NOT(logical)• =IF(NOT(E2>60),"不及格","及格")

4

7

邏輯函數-IF函數• 語法：IF (logical_test, value_if_true,

value_if_false)當 value_if_true 引數或 value_if_false 引數被執行時，則 IF 函數傳回這些引數的運算結果，而非引數本身。

• =IF(E2>=90,"A",IF(E2>=80,"B",IF(E2>=70,"C",IF(E2>=60,"D","F"))))

8

邏輯函數-多層次IF函數為配合 value_if_true 與 value_if_false 引數，以處理更為精巧的條件測試，則可使用多達七層的 IF 函數。

F60 以下

D60 到 69 之間

C70 到 79 之間

B80 到 89 之間

A89 以上

傳回的等級是若分數是

5

9

日期與時間函數-TODAY函數• 語法：TODAY( ) 無時間• 註解： Excel 以循序序列值儲存日期，因此它可被用於計算。預設序列值為 1，表示是 1900 年1 月 1 日，並且 2008 年 1 月 1日的序列值為 39448， 因為此日期是在 1900 年 1 月 1 日的 39448 天之後。

• 語法：Now( ) 無時間• 儲存格格式是 [通用]或[日期]就會像下面：

10

日期與時間函數-Month(日期)函數

• 定義：傳回 serial_number（序列值）代表日期的月份。月份數為介於 1 （1月）到 12（12月）之間的整數。

• 語法：MONTH(serial_number)• 使用 DATE(2008,5,23) 表示 2008 年 5月 23 日。

• YEAR，DAY與MONTH用法相同只是分別傳回日期引數的年與日。

• =IF(MONTH(F2)=MONTH(TODAY()),"本月壽星","")

6

11

日期與時間函數-Month(日期)函數

• 在W4儲存格輸入=TODAY()-D4，即今天的日期減去出生的日期將格式改為[通用]或[數值]。

• 結果為日數。• 將公式改成=(TODAY()-D4)/365，結果即年齡，為19歲多一些。

12

DATEDIF 函數

• DATEDIF 函數可以幫我們計算兩個日期之間的年數、月數或天數。其格式如下：

7

13

DATEDIF 函數應用實例• 若想計算員工中從到職日至 92 年 10 月 31 日止的服務年資, 就可以這麼計算：

14

DATEDIF 的差距單位參數• 在 DATEDIF 函數中, 可依據您要求算的結果, 搭配使用各種差距單位參數, 列表如下供您參考：

8

15

DATEDIF 的差距單位參數• 假設要計算某人的實際年齡滿幾年、幾月、幾天, 只要輸入如下的公式即可計算出來：

16

星期函數-weekday函數

• 定義：使用 index_num 自引數清單中傳回相對應的引數數值。語法：weekday(日期)

• WEEKDAY函數得到星期代碼。• =WEEKDAY(F2)• =weekday(“2004/12/7”)• 傳回 1 – 7 (星期日到星期六)

9

17

算數(數學)函數-INT函數• 定義：傳回指定小數位數無條件捨去之整數值。• 語法：INT (number) • Number想要無條件捨去成為一整數的實數。• =INT((TODAY()-F2)/365.25)• 語法：Round (number,num_digits)• Number想要四捨五入成為一整數的實數。• =ROUND((TODAY()-F2)/365.25,1)

18

算數(數學)函數-Randbetween函數• 定義：傳回一個大於等於 bottom 且小於 top 的隨機亂數。每當工作表重算時，便會傳回一個新的隨機亂數。

• 工具/增益集/分析工具箱 安裝後才可使用• 語法：Randbetween (bottom,top)• 試試看在1~49號中要抽出一個號碼，應該怎麼做？• = Randbetween(1,49)

10

19

算數(數學)函數-RAND函數• 定義：傳回一個大於等於 0 且小於 1 的隨機亂數。每當工作表重算時，便會傳回一個新的隨機亂數。

• 語法：RAND( )• 如果您希望產生的亂數是介於 a 與 b 之間的實數，請使用公式：

• RAND()*(b-a)+a 。• 試試看在1~49號中要抽出一個號碼，應該怎麼做？• =INT(RAND()*49)+1

20

算數(數學)函數-ABS函數• 定義：傳回一個絕對值數值。• 語法：ABS(數值或儲存格)

11

21

算數(數學)函數-Sqrt函數• 定義：傳回一個數值取平方根。• 語法：Sqrt(數值或儲存格)

22

算數(數學)函數-Round函數• 定義：傳回一個數值取4捨5入。• 語法： Round(數值或儲存格,指定位數)• 指定位數>0

– =round(35.32,1) 35.3

• 指定位數=0– =round(35.52,0) 36

• 指定位數

12

23

算數(數學)函數-COUNTIF函數• 計算某範圍內符合某搜尋篩選條件的儲存格個數。

• 語法：COUNTIF(range,criteria)• Range是您想計算符合篩選條件之儲存格個數的儲存格範圍。

• Criteria是用以決定是否要列入計算的搜尋篩選條件

• 例如：我們想要知道每一科及格的人數。• =COUNTIF(E2:E10,”

13

25

統計函數- COUNTA 函數

• COUNTA 函數可用來計算引數範圍含有 "非空白" (包括文字或數字) 資料的儲存格個數。以下圖為例, COUNTA (A1：D3) = 5：

26

統計函數- FREQUENCY函數

• FREQUENCY 函數可用來計算一儲存格範圍內, 各區間數值所出現的次數, 再以垂直陣列回應各次數。使用此函數時, 必須分別指定資料來源範圍以及區間分組範圍, 再以+ + 完成陣列公式的輸入。FREQUENCY 函數的格式為：

– Data_array 要計算出現次數的資料來源範圍。– Bins_array 資料區間分組的範圍。

14

27

FREQUENCY函數實例應用

• 接著請選取 F3:F6 的儲存格範圍, 再輸入公式 "=FREQUENCY (C2:C13,E3:E6)" 然後按下 + + ：

28

統計函數-RANK函數• 定義：傳回某數字在一串數字清單中的等級。數字的等級就是數字相對於清單中其他數值的大小。(如果你把這清單中的數字排序，則此數字的等級就是它所在的位置)。

• 語法：RANK(number,ref,order)• Number是要知道等級的數字。• Ref是一個數值陣列或數值參照位址，非數值將被忽略。

• Order是指定的順序。 0 大到小、 非0 小到大• =RANK(E2,E$2:E$10,0)

15

29

統計函數-MEDIAN函數• 定義：傳回引數串列內的中位數。中位數為一組數字的中間數字；即一半數字的值大於中位數，而另一半數字的值小於中位數。

• 語法：MEDIAN(number1,number2,...)• Number1, number2, ... 是 1 到 30 個數字，您需要找出這些數字的中位數。

• =MEDIAN(E2:E10)

30

統計函數-STDEV函數• 定義：根據一組樣本估計其標準差。標準差主要是用以衡量觀測資料與平均數之間的差異量數。

• 語法：STDEV(number1,number2,...)• Number1, number2, ...是對應於某母群體抽樣樣本的一到三十個數字引數

• STDEV 函數假設它的引數是某母群體的抽樣樣本。如果您的觀測資料代表整個母群體，則應該使用 STDEVP 函數來計算標準差。

• = STDEV(E2:E10)

16

31

統計函數-VAR函數• 定義：估計樣本的變異數。• 語法：VAR(number1,number2,...)• Number1, number2, ...是對應於某母群體抽樣樣本的一到三十個數字引數。

• 註解： VAR 函數假設它的引數串列為母群體的抽樣樣本，如果您的觀測資料代表整個母群體，則使用 VARP 來計算變異數。

• 變異數=標準差2 =• =sqrt(VAR(E2:E10))• = STDEV(e2:e10)

( )

N

XN

ii∑

=

−= 1

2

2µ

σ

32

檢視與參照(尋找與參照)函數-CHOOSE函數

• 定義：使用 index_num 自引數清單中傳回相對應的引數數值，引數的個數可由 1 到 29 個。語法：CHOOSE(index_num,value1,value2,...)

• Index_num是用以指定要選取第幾個引數值的數值。• 如果 index_num 之值為 1，則 CHOOSE 函數會傳回

value1；如果其值為 2，CHOOSE 函數會傳回value2；依此類推。

• 先用WEEKDAY函數得到星期代碼。但是我們想讓它顯示得正式一點，利用CHOOSE函數將B欄的值用星期幾顯示出來。

• =CHOOSE(A14,"mon","tue","wed","thu","fri")

17

33

檢視與參照函數-VLOOKUP函數• 定義：在一陣列或表格的最左欄中尋找含有某特定值的

欄位，再傳回同一列中某一指定儲存格中的值。語法：

• VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

• Lookup_value是您打算在陣列的最左欄中搜尋的值。Lookup_value 可以是數值、參照位址或文字字串。

• Table_array是要在其中搜尋的資料表格。通常是儲存格範圍的參照位址或類似資料庫或清單的範圍名稱。Table_array 第一欄裡的值，可以是文字、數字或邏輯值。字母的大小寫被視為是相同的。

34

檢視與參照函數-VLOOKUP函數• Col_index_num是個數值，代表所要傳回的值位於

table_array 中的第幾欄。如果 col_index_num 引數值為 1，傳回在 table_array 第一欄的值，如果col_index_num 引數值為 2，傳回 table_array 第二欄的值，依此類推。

• Range_lookup是個邏輯值，用來指定 VLOOKUP 之Table_array要先排序與否，或找尋之值為完全吻合或部分符合(0表示要完全吻合)。

• =VLOOKUP(4,A2:J10,4)• HLOOKUP函數跟VLOOKUP用法大同小異，只是它是參考橫向資料範圍。

• =HLOOKUP(80,A2:J10,4) ??

18

35

檢視與參照函數-VLOOKUP函數• =IF(ISNA(VLOOKUP(A5,客戶資料檔!$A$1:$B$6,2,0)),"沒找到",VLOOKUP(A5,客戶資料檔!$A$1:$B$6,2,0))

36

檢視與參照函數-TRANSPOSE函數• 定義：將儲存格之垂直範圍以水平範圍的格式傳回，反之亦可。TRANSPOSE 必須是個有欄和列的陣列分別以有相同數目的欄和列範圍的陣列公式輸入。使用 TRANSPOSE 來移動工作表上陣列的垂直和水平方向。

• 語法：TRANSPOSE(array)• Array是工作表或巨集表中您所要轉置的矩陣或儲存格範圍。轉置矩陣的建立是以陣列的第一列作為新陣列的第一欄，而 陣列的第 2 列則為新陣列的第 2 欄，依此類推。

• Step1：選取欲轉置目的儲存格範圍，並按[插入函數]。

19

37

檢視與參照函數-TRANSPOSE函數• Step2：開啟[插入函數]交談窗，選取類別”檢視與參照”，並選取”TRANSPOSE”函數。

• Step3：按[Array]之摺疊鈕。• Step4：選取原始儲存格範圍表示我們想轉置這個範圍的資料，並按摺疊鈕回來。

• Step5：並確定鈕回來。• Step6：在資料編輯列按一下，並按Ctrl+Shift+Enter。• Step7：看到結果！

• 先選定目的範圍為9列10欄• 於目的地左上角輸入=TRANSPOSE(A1:J10)再按

Ctrl+shift+enter

38

檢視與參照函數- INDEX 函數

• INDEX 函數會在陣列中找到指定欄列交會處的儲存格內容。其公式如下：

20

39

INDEX 函數實例應用• 假設想要在星座圖中查詢男女雙方的速配程度, 就可以利用 INDEX 函數來找到結果：

40

檢視與參照函數- MATCH 函數• MATCH 函數是用來比對一陣列中內容相符的儲存格位置。其函數格式為：

– 當 Match_type 設為 0 時, 表示陣列內容不用排序直接找到完全相符的值；若設為 1 或省略, 表示陣列內容會先遞增排序, 再找等於或僅次於Lookup_value 的值；若設為 -1, 則表示陣列內容會先遞減排序, 再找等於或大於 Lookup_value 的最小值。

21

41

MATCH 函數實例應用

42

MATCH 函數實例應用2. 接著將插入點移至 B11, 輸入公式 =MATCH

(A11, A1：H1, 0)：

22

43

MATCH 函數實例應用3. 最後再將插入點移至 B12, 輸入公式

=INDEX (A1：H7, B10, B11)：

44

財務函數-PV函數• 定義：傳回某項投資的年金現值。年金現值為未來各期年金現

值的總和。

• 語法：PV(rate, nper, pmt, fv, type) =pv(5%,5,10000)• Rate為各期的利率。• nper為年金的總付款期數。• pmt為各期所應給付 (或所能取得) 的固定金額。• fv為最後一次付款完成後，所能獲得的現金餘額 (年金終值)。

如果省略 fv 引數，會自動假定為 0 (例如貸款的年金終值是0 )。

• Type為 0 或 1 的數值，用以界定各期金額的給付時點。0 或省略代表期末付款，1代表期初付款。

• 範例1 ：某銀行為推銷某種基金，年利率4.5%，請你預繳45,000，5年內每年領10,000元，我們想計算一下現值，看是否值得投資。輸入 = PV(4.5%,5,10000)，這5年給我的錢，換成現在只有43,899.77，比我投資的錢還多，當然不值得投資。

23

45

財務函數

• 範例2 ：某保險公司為推銷某種保險，年利率3%，請你預繳每年67,140元，20年後每年領45,666元，我們想計算一下現值，看是否值得投資?

• 只將67,140放定存20年，利率年息為3%: =67140*(1+3%)^20=121262

1804076.943總共

117730.396420

114301.355719

110972.1918

107739.990317

104601.932416

101555.274115

98597.3535214

95725.5859413

92937.4620812

90230.5457111

87602.4715610

85050.943269

82573.731328

80168.671197

77833.661356

75566.66155

73365.690784

71228.8263

69154.22

671401

46

財務函數貸款每月還款-PMT函數• 定義：固定利率與固定期數下，傳回每期付款金額。• 語法：PMT(rate,nper,pv,fv,type)• 有關 PMT 中引數的完整說明，請參閱 PV 函數。• Rate為各期的利率，Nper為年金的總付款期數。• Pv為未來各期年金現值的總和。• Fv為最後一次付款完成後，所能獲得的現金餘額 (年金終

值)。如果省略 fv 引數，會自動假定為 0，也就是說，貸款的年金終值是 0。

• 範例1 ：假設”A”同學想申請助學貸款，年利率7.8%，可借50,000元，期限為5年，看看每月必須負擔多少貸款。

• 輸入= PMT(7.8%/12,5*12,50000)，一個月只要付1,009元。• 範例2 ：假設”A”先生想貸款買房子，年利率3.8%，可借

2,000,000元，期限為20年，看看每月必須負擔多少貸款。• 輸入= PMT(3.8%/12,20*12,2000000)。

24

47

財務函數零存整付-FV函數• 語法：FV(rate,nper,pmt,pv,type)• FV計算零存整付存款之本利和

– FV(月利率, 期數(年)*12,每月存款金額)

• 248481.83 v.s. 10000*(1+1.5%/12)^1累積24次

=FV(C2/12,B2*12,A2)1.5%24-10000年金終值年利率期數(年)每月存款金額

48

財務函數存款/預借現金利率- RATE 函數• RATE 函數可以幫我們計算借了一筆錢, 在固定期數、每期要償還固定金額下, 算出其利率為何。RATE 函數的格式為：

• 格式Rate(Nper,pmt,pv,fv,type)– Nper 為付款的總期數。– Pmt 為各期所應給付的固定金額。– Pv 為未來各期年金現值的總合。– Fv 為最後一次付款後, 所能獲得的現金餘額。– Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。

– Rate(期數,金額, -最後一次現金餘額, -年金現值的總合)

25

49

RATE 函數實例應用 1• 假設古堡銀行推出全新的百萬儲蓄計劃, 強調每月只要儲蓄 7,500 元, 10 年後保證領回 100 萬元, 那到底這個百萬儲蓄計劃的年利率是多少呢？

• 帶入函數計算的結果, 比目前銀行定存約 2% 的利率還要高一些。

1000000=rate(a3,a2*12,,-a5)107500

年金終值年利率期數(年)每月存款金額

50

RATE 函數實例應用 2• 假設古堡銀行提出個人小額信用貸款方案, 借款 30 萬, 每月只要還款 16000, 2 年即可還清。

• 帶入函數得知, 和信用卡循環利息一樣高耶, 還是划不來。

300000=rate(a3,a2*12,-a5)216000預借現金年利率期數(年)每月存款金額

26

51

財務函數算存款期數- NPER 函數• NPER 函數是指每期投入相同金額, 在固定利率的情形下, 計算欲達到某一投資金額的期數。NPER 函數的格式為：

– Rate 為各期的利率。– Pmt 為各期所應給付的固定金額。– Pv 為未來各期年金現值的總合。– Fv 為最後一次付款後, 所能獲得的現金餘額。– Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。

52

• 小風想買一間需頭期款60 萬元的小套房, 目前小風每個月可以存 17,000 元, 而定存年利率為 2.05%, 小風需要存多久才能存夠小套房的頭期款呢？

• 帶入函數計算結果, 表示小風只要存 35 個月就可湊足小套房的頭期款了。

實例應用

27

53

財務函數- IRR 函數• IRR 函數可以用來計算某一連續期間的內部報酬率。其中要注意的是：投入資金必須以負值表示, IRR 才可以計算。IRR 函數的格式為：

– Values 要計算報酬率的現金流量數值。– Guess 預測利率, 若不填則以 10% 為預設值來計算。

12%內部投資報酬率

230000第6年

220000第5年

210000第4年

200000第3年

175000第2年

150000第1年

-800000投入資金

54

折舊函數

• 計算折舊的方法有很多種, 通常會依公司習慣的方式來提列。由於使用不同的折舊函數, 所需用到的參數亦有些許差異, 我們先介紹共通的部份：

• Cost 採購設備或資產所花費的成本。• Salvage 殘值, 亦即此設備或資產過了耐用年限時可回收的價值。

• Life 耐用年限, 亦即此設備或資產的可用年限或生產數量。

28

55

財務函數-SLN函數• 茹葳公司採購一生財設備花了 60 萬元, 預估可以使用 5 年, 殘值餘 4,500 元。若以直線法來攤為費用, 則可使用直線法折舊函數SLN, 其格式如下：

56

財務函數-SLN函數

• 將插入移至 B4 儲存格, 接著輸入公式=SLN ($B$1, $D$1, $F$1)

29

57

財務函數直線折舊額-SLN函數• 直線折舊額=(成本-殘值)/使用年限

58

財務函數-SYD函數• 若茹葳公司想要以年數合計法 (SYD) 來計算每年需攤提的費用, 則可改用 SYD 函數來計算。其格式如下：

30

59

財務函數-SYD函數

60

財務函數直線折舊額-SYD函數• 年數合計法每期折舊額=(成本-殘值)*年數之倒數/年數之合計

31

61

財務函數-DB函數

• 承上例, 若茹葳公司想要以定率遞減法 (DB)來計算每年需攤提的費用, 則須採用 DB 函數, 其格式為：

62

財務函數-DB函數

• 在 B5 拉曳填滿控點至 F5, 即可求得定率遞減法各年度的折舊費用。這是初期折舊的費用較高, 然後逐年遞減的一種加速折舊法。

32

63

財務函數直線折舊額-DB函數• 定率遞減法=固定資產之帳面價值*折舊率• DB(cost, salvage, life, period, month)

64

財務函數-DDB函數

• 若茹葳公司想要以倍率遞減法 (DDB) 來計算每年需攤提的費用, 則可使用 DDB 函數, 其格式如下：

33

65

財務函數-DDB函數

66

財務函數直線折舊額-DDB函數• 倍數餘額遞減法=直線法折舊率的兩倍，不考量殘值• DDB(cost, salvage, life, period, factor)

34

67

HomeWork• 文字函數包括Left、Right等函數• 邏輯函數包括And、Or、Not及If函數• 日期與時間函數包括Today、Date、Time、Year、

Month、Day、Dateif、Weekday函數• 數學函數包括Int、Rand、Countif、Sumif、函數• 統計函數包括Counta、Frequency、Rank 、

Median、Stdev、Var函數• 檢視與參照函數包括Choose、Vlookup、

Hlookup、Transpose函數• 財務函數包括Pv、Pmt、Fv、Rate、Nper、Irr、

Sln、Syd、Db、Ddb函數