第 9 章 函數的應用 著作權所有 © 旗標出版股份有限公司

本章提要 統計函數 財務函數 數學與三角函數 邏輯函數 檢視與參照函數 日期及時間函數 文字函數

統計函數 MEDIAN 函數 STDEV 函數 VAR 函數 COUNTA 函數 RANK 函數 COUNTIF 函數 實例應用 STDEV 函數 VAR 函數 COUNTA 函數 RANK 函數 實例應用 COUNTIF 函數 FREQUENCY 函數

MEDIAN 函數 MEDIAN 為計算中位數的函數, 用來找出一組數值資料的中間值, 如果有偶數個數引數, 則 MEDIAN 函數就會計算中間兩個數字的平均值, 例如：MEDIAN (9,0,3) = 3、MEDIAN (1,2,3,4)=2.5。當一組資料包含了幾個特別大或特別小的數值時, 計算中位數就會比計算平均還要來得客觀一些。MEDIAN 函數的格式為：

實例應用 有一名運動員想了解自己在一分鐘內大約可以投進幾顆籃球, 所以他做了十次的測試, 並把每次一分鐘投進的球數紀錄下來。因此, 我們決定應用 MEDIAN 函數來計算結果！

STDEV 函數 STDEV 為計算標準差的函數, 當標準差愈小時, 代表一組數值越集中於平均值附近。 STDEV 函數的格式為：

實例應用 假設有兩組學生, 他們測量身高的結果記錄如下：甲組：160cm、155cm、165cm、170cm、162cm、158cm、148cm；乙組：172cm、151cm、153cm、164cm、175cm、148cm、156cm。 若我們想了解哪一組學生身高分佈較為平均, 就可使用 STDEV 函數來計算：

實例應用

VAR 函數 VAR 為計算變異數的函數。變異數在統計學上也是相當重要的資訊, 它其實就是標準差的平方, 可用來觀察資料的離散程度。

實例應用 我們以上述的甲、乙組學生身高資料為例, 計算兩組學生身高的變異數：

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

實例應用 請開啟範例檔案 Ch09-01, 切換到COUNTA 工作表： 這是一張各廠牌翻譯機的功能比較表, B2：E2 是翻譯機的廠牌名稱, A3：A13 則列出各項翻譯機的功能, 若某翻譯機擁有該項功能, 則在對應的儲存格內填入 "★" 符號。現在我們要利用 COUNTA 函數, 計算出每部翻譯機具有幾項功能, 以便做為購買時的參考：

實例應用

實例應用 我們先來看看 "哈雷族" 翻譯機：

實例應用 接著, 請您拉曳 B14 的填滿控點至 E14 , 將結果計算出來。

RANK 函數 RANK 函數可計算某數字在一個儲存格範圍中的順序等級, 通常用來計算排名。RANK 函數的格式為： Number 為所要排序比較的數字。 Ref 為排序比較的範圍。 Order 指定排序順序, 若是輸入 0 或空白表示會把 Ref 當成由大到小來判斷 Number 的等級, 也就是遞減排序, 若不是 0, 則會把 Ref 當成由小到大來判斷 Number 的等級, 亦即遞增排序。

實例應用 請開啟範例檔案 Ch09-01, 切換到 RANK 工作表。這是一個班級的學期成績, 現在我們要利用 RANK 函數, 計算出班上同學的排名。 請將插入點移至 I3 儲存格輸入公式 "=RANK (H3,$H$3:$H$12)"：

實例應用

實例應用 接著, 請拉曳 I3 的填滿控點至 I12, 就可以看到所有名次都已排列正確：

COUNTIF 函數 COUNTIF 函數可以計算指定範圍內符合特定條件的儲存格數目。 COUNTIF 函數的格式為： Range 為計算、篩選條件的儲存格範圍。 Criteria 為篩選的準則或條件。

實例應用 請開啟範例檔案 Ch09-01, 切換到 COUNTIF 工作表。假設我們想要知道本次入學成績中, 筆試的及格人數和不及格人數各有幾位。 請將插入點移至 G2 輸入公式 "=COUNTIF (C2：C11, ">=60")：

實例應用

實例應用

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

實例應用 請開啟範例檔案 Ch09-01, 切換到 FREQUENCY 工作表。假設我們想從學生成績單裡分別找出會計檢定成績不及格 (70 分以下) 的人數、成績介於 70~79之間的人數、成績介於 80~89 之間的人數、以及成績 90 分以上的人數。首先我們將要找的資料分組, 例如 E3:E6 的分組陣列就代表 0~69 分、70~79 分、80~89 分、及 90 分以上的 4 組：

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

實例應用

實例應用 當公式完成時, 請注意觀察此公式和一般我們所輸入的公式略有不同。公式左右會以一對大括弧包圍, 表示這是一組陣列公式。而陣列公式必須要一起修改或刪除, 否則會出現提示訊息告知 。若想要刪除此公式, 請先選取整個陣列公式的範圍, 再按下 鍵。

財務函數 PV 函數 FV 函數 PMT 函數 RATE 函數 NPER 函數 IRR 函數 折舊函數 實例應用 實例應用 實例應用 1 實例應用 2 RATE 函數 NPER 函數 實例應用 IRR 函數 折舊函數 實例應用 1 實例應用 2 實例應用 3 實例應用 4

PV 函數 PV 函數是用來求算現值的函數。透過此函數, 可以反推在某種獲利條件下, 所需要的本金, 以便評估某項投資是否值得。PV 函數的格式為： Rate 為各期的利率。 Nper 為付款的總期數。 Pmt 為各期所應給付的固定金額。 Fv 為最後一次付款以後, 所能獲得的現金餘額。 Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。

實例應用 假設郵局推出一種儲蓄理財方案：年利率為 2.5%, 只要您現在先繳 120,000 元, 就可在未來的 10 年內, 每年領回 13,500 元, 這時候, 我們就可以利用 PV 函數來評估此項方案是否值得投資？

FV 函數 FV 函數是用來計算未來值的函數。透過它, 可評估參與某種投資時最後可獲得的淨值。FV 函數的格式為： Rate 為各期的利率。 Nper 為付款的總期數。 Pmt 為各期所應給付的固定金額。 Pv 為年金淨現值。此欄若不填則以 0 代替。 Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。此欄若不填則以 0 代替。

實例應用 假設銀行年利率為 2%, 您從現在起, 每月固定存款 8,000 元, 那麼在 5 年後, 您一共存了多少錢呢？ 由上述說明可知 Rate 為 2%/12 (2% 是年利率, 每月存款所以要除以 12), Nper 為 5*12 (一年 12 期, 持續 5 年), Pmt 為 -8000 (由於是付款, 故代入負數)：

PMT 函數 PMT 函數可幫我們計算在固定期數、固定利率的情況下, 每期要償還的錢。PMT 函數的格式如下： Rate 為各期的利率。 Nper 為付款的總期數。 Pv 為未來各期年金的總淨值, 即貸款總金額。 Fv 為最後一次付款以後, 所能獲得的現金餘額。 Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。

實例應用 1 假設旗旗銀行提供申請購屋貸款的優惠方案, 貸款年利率為 7%, 可借得 3,000,000 元, 期限為 20 年, 這時候您就可以透過PMT 函數, 算算每月必須負擔多少貸款？

實例應用 2 假設您想在 4 年後存滿 800,000 元做為留學基金, 現今的年利率為 2%, 則每個月應存多少錢才能達成這個目標呢？ 由上圖得知：PMT (2%/12,4*12,0,-800000) = $16,022.77, 也就是說您只要每個月固定存入 $16,023 元, 4 年後就可以順利的出國留學了。

RATE 函數 RATE 函數可以幫我們計算借了一筆錢, 在固定期數、每期要償還固定金額下, 算出其利率為何。RATE 函數的格式為： Nper 為付款的總期數。 Pmt 為各期所應給付的固定金額。 Pv 為未來各期年金現值的總合。 Fv 為最後一次付款後, 所能獲得的現金餘額。 Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。

實例應用 1 假設古堡銀行推出全新的百萬儲蓄計劃, 強調每月只要儲蓄 7,500 元, 10 年後保證領回 100 萬元, 那到底這個百萬儲蓄計劃的年利率是多少呢？ 帶入函數計算的結果, 比目前銀行定存約 2% 的利率還要高一些。

實例應用 2 假設古堡銀行提出個人小額信用貸款方案, 借款 30 萬, 每月只要還款 16000, 2 年即可還清。 帶入函數得知, 和信用卡循環利息一樣高耶, 還是划不來哦。

NPER 函數 NPER 函數是指每期投入相同金額, 在固定利率的情形下, 計算欲達到某一投資金額的期數。NPER 函數的格式為： Rate 為各期的利率。 Pmt 為各期所應給付的固定金額。 Pv 為未來各期年金現值的總合。 Fv 為最後一次付款後, 所能獲得的現金餘額。 Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。

實例應用 小風想買一間需自備款 60 萬元的小套房, 目前小風每個月可以存 17,000 元, 而定存年利率為 2.05%, 小風需要存多久才能存夠小套房的頭期款呢？ 帶入函數計算結果, 表示小風只要存 35 個月就可湊足小套房的頭期款了。

IRR 函數 IRR 函數可以用來計算某一連續期間的內部報酬率。其中要注意的是：投入資金必須以負值表示, IRR 才可以計算。IRR 函數的格式為： Values 要計算報酬率的現金流量數值。 Guess 預測利率, 若不填則以 10% 為預設值來計算。

實例應用 假設瑪琦朵行動咖啡館提出一投資方案, 投資者只要投入資金 80 萬, 便可以在 6 年內可分別領回：15 萬、17.5 萬、20 萬、21 萬、22 萬、及 23 萬, 求此投資的內部報酬率：

實例應用 由圖可知其內部報酬率為 12%。

折舊函數 計算折舊的方法有很多種, 通常會依公司習慣的方式來提列。由於使用不同的折舊函數, 所需用到的參數亦有些許差異, 我們先介紹共通的部份： Cost 採購設備或資產所花費的成本。 Salvage 殘值, 亦即此設備或資產過了耐用年限時可回收的價值。 Life 耐用年限, 亦即此設備或資產的可用年限或生產數量。

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

實例應用 1 請開啟範例檔案 Ch09-02, 將插入移至 B4 儲存格, 接著輸入公式 "=SLN ($B$1, $D$1, $F$1)"：

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

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

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

實例應用 3

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

實例應用 4 若想要了解這 4 個應用實例的結果, 可切換到折舊 –OK 工作表來查看。

數學與三角函數 ABS 函數 實例應用 SQRT 函數 RANDBETWEEN 函數 SUMIF 函數 實例應用 ROUND 函數

ABS 函數 ABS 是用來計算絕對值的函數, 此函數只能有一個引數, 且引數必須是數值、或是一個含有數值的儲存格、或是一個可傳回一個數值的函數, 例如：ABS (SUM (1,2,3))。

實例應用 同 PMT 函數的實例, 假設旗旗銀行提供申請購屋貸款的優惠方案, 貸款年利率為 4.5%, 可借得 3,000,000 元, 期限為 20 年, 請您算算每月必須負擔多少貸款金額？

SQRT 函數 SQRT 是用來計算平方根的函數, 例如 SQRT (25) = 5、SQRT (49) = 7。需特別注意的是, SQRT 的引數必須是一個正數或一個內含有正數值的儲存格, 或是一個可傳回正數值的函數, 否則就會出現錯誤訊息。

實例應用 我們曾經介紹過用來計算「變異數」的 VAR 函數, 以及「標準差」函數 STDEV, 且知道標準差其實就是變異數的平方根, 因此當我們算出變異數的時候, 便可直接利用 SQRT 函數來求出標準差。

RANDBETWEEN 函數 RANDBETWEEN 函數用來傳回您所指定的數字範圍間的任意一個亂數, 且在每次計算工作表時, 都會傳回一個新的亂數。格式為： Bottom 為 RANDBETWEEN 傳回的最小整數。 Top 為 RANDBETWEEN 傳回的最大整數

實例應用 假設學校合作社每學年提撥款項, 購買精美文具組回饋給各班學生, 但每班只有一位幸運得主, 這時候我們可以使用RANDBETWEEN 函數抽出每班的得獎人座號：

實例應用 請在 C3 填入公式 "= RANDBETWEEN (1,B3)"。 將 C3 的公式拉曳複製至 C10。

SUMIF 函數 SUMIF 函數可用來加總符合某搜尋準則的儲存格。它的格式為： Range 是要搜尋的儲存格範圍。 Criteria 是判斷是否進行加總的搜尋準則, 它可以是數字、表示式或文字。例如：20、"66"、"Happy"、或 ">100"。 Sum_range 是實際要加總的儲存格。Sum_range 和 Range 是相對應的, 當範圍中的儲存格符合搜尋準則時, 其對應的 Sum_range 儲存格就會被加入總數。

實例應用 請開啟範例檔案 Ch09-03 並切換到 SUMIF 工作表：

實例應用 這是一張旗旗公司在三大書局的圖書銷售統計表, 現在我們要利用 SUMIF 函數, 幫旗旗公司計算在這一季中, 每一本書一共賣出多少本？ 請選取 F11 儲存格, 輸入公式 "= SUMIF (A2：A13,"Office XP 非常Easy", B2：B13)", 以便算出 "Office XP 非常 Easy" 一共賣了多少本：

實例應用 請您自行輸入 F12 、F13 的公式, 完成銷售量統計的計算, 或是切換到 SUMIF-OK 觀看成果。

ROUND 函數 ROUND 函數可您依指定的位數, 將數字四捨五入。其格式如下：

ROUND 函數 當 Num_digits 大於 0 時, 數字會被四捨五入到指定的小數位數, 例如：ROUND (35.32,1) = 35.3。 當 Num_digits 等於 0 時, 數字會被四捨五入到整數, 例如：ROUND (76.82,0)= 77。 當 Num_digits 小於 0 時, 數字將被四捨五入到小數點左邊的指定位數, 例如：ROUND (22.5,-1) = 20。

實例應用 請開啟範例檔案 Ch09-03, 切換至 ROUND 工作表, 我們要計算出每一本書在三家書局內, 平均賣出多少本？

實例應用 要計算每本書平均賣出多少本, 可利用之前求出來的總銷售量來除以 3 , 然後搭配 ROUND 函數將數值四捨五入到整數： 另外兩本書請您自己輸入公式求出結果, 或參考已經輸入完成的 ROUND-OK 工作表。

實例應用

邏輯函數 IF 函數 實例應用 1 實例應用 2 AND 函數 實例應用 OR 函數

IF 函數 IF 函數用來判斷測試條件是否成立, 如果所傳回的值為 TRUE 時, 就執行條件成立時的作業, 反之則執行條件不成立時的作業。IF 函數的格式為：

實例應用 1 請開啟範例檔案 Ch09-04, 切換到 IF 工作表, 這是一張學生成績列表：

實例應用 1 現在, 我們來使用 IF 函數做判斷, 如果學生平均成績大於或等於 60 分, 則在最後的 "總評" 欄內填入 "Pass"；若平均低於 60 分, 就填入 "重修"。首先建立第一位學生的判斷式：

實例應用 1 拉曳 H2 的填滿控點至 H11, 便可得到每位學生的總評結果囉！

實例應用 2 IF 函數不只可以判斷條件成立與不成立的 2 種情況, 我們還可以寫成巢狀 IF 的方式, 以判斷更多的狀況並給予不同的處理作業。 以上題為例, 若平均低於 60 分, 填入 "重修"；平均介於 60~80 分, 填入 "普普", 平均大於 80 則填入 "佳", 那麼公式可改寫如下：

實例應用 2

AND 函數 AND 函數的所有引數都必須是邏輯判斷式 (可得到 TRUE 或 FALSE 的結果) 或包含邏輯值的陣列、參照位址, 且當所有的引數都成立時才傳回 TRUE, 它的格式為：

實例應用 請開啟範例檔案 Ch09-04, 切換到 AND 工作表, 這是某班級的學生成績列表：

實例應用 假設有一檢定考試, 必須要國文、英文這兩科的成績都大於 80 分才能報名參加, 這時候我們可以使用 AND 函數並搭配前面的 IF 函數來找出符合報考資格的學生：

實例應用 接著拉曳 G2 的填滿控點至 G11, 則到底哪些學生能參加檢定考試就一目了然啦！

OR 函數 OR 函數和 AND 函數一樣, 所有引數都必須是邏輯判斷式, 不同的是, 當引數中只要有一個成立就傳回 TRUE, 其格式為：

實例應用 請開啟範例檔案 Ch09-04, 切換到 OR 工作表。假設有一檢定考試, 若其中一科成績低於 60 分就不予合格證明, 我們就可以使用 OR 函數搭配 IF 函數來找出合格的學生：

實例應用

實例應用 最後一樣拉曳 F2 的填滿控點至 F13, 就可以知道學生的合格情形。

檢視與參照函數 HLOOKUP 函數 實例應用 INDEX 函數 MATCH 函數

HLOOKUP 函數 HLOOKUP 函數的功用就是在清單的第一列中尋找特定值, 若找到就傳回所找的那一欄中某個欄位的值。在製作個人成績單時, 曾介紹過 VLOOKUP 這個函數, 其實 HLOOKUP 函數的用法就和VLOOKUP 函數幾乎一樣哦。 HLOOKUP 函數的格式為：

實例應用 夢夢公司的業務人員薪資是依據業績的高低而有所不同, 且夢夢公司已經建立好一份業務人員薪資績效對照表, 可用來查詢不同業績的底薪與獎金 。 請開啟範例檔案 Ch09-05, 切換到 HLOOKUP 工作表, 首先來查詢底薪：

實例應用

實例應用 將插入點移至 D7 儲存格, 輸入公式 "=HLOOKUP (C7, $B$2：$F$4, 2)

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

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

MATCH 函數 MATCH 函數是用來比對一陣列中內容相符的儲存格位置。其函數格式為： 當 Match_type 設為 0 時, 表示陣列內容不用排序直接找到完全相符的值；若設為 1 或省略, 表示陣列內容會先遞增排序, 再找等於或僅次於 Lookup_value 的值；若設為 -1, 則表示陣列內容會先遞減排序, 再找等於或大於 Lookup_value 的最小值。

實例應用 當我們到郵局寄送快捷時, 為了要快速查詢寄送地點到目的地的郵資, 可以利用MATCH 和 INDEX 函數設計簡便的查詢公式。 請開啟範例檔案 Ch09-05, 切換到 MATCH 工作表, 將插入點移至 B10, 輸入公式 =MATCH (A10, A1：A7, 0)：

實例應用

實例應用 接著將插入點移至 B11, 輸入公式 =MATCH (A11, A1：H1, 0)：

實例應用 最後再將插入點移至 B12, 輸入公式 =INDEX (A1：H7, B10, B11)：

日期及時間函數 TODAY 函數 應用實例 DATEDIF 函數 DATEDIF 的差距單位參數

TODAY 函數 TODAY 函數會傳回現在系統的日期, 可應用於輸入報告完成時間或是用來計算年資、年齡。

應用實例 請開啟範例檔案 Ch09-06, 並切換至 TODAY 工作表。美美公司想要在年終獎金的部份, 針對在公司服務滿 10 年的同仁發放年資獎金。我們用 TODAY 這個函數和到職日相減, 所減出來的數字表示天數, 再除上 365.25 (每 4 年閏 1 天) 即可算出年資：

應用實例

應用實例 拉曳 E3 的填滿控點複製公式後, 就可以看出符合年資獎金條件的員工有哪些了：

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

應用實例 承續上例, 若美美公司想計算員工中從到職日至 92 年 10 月 31 日止的服務年資, 就可以這麼計算：

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

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

文字函數 LEFT 函數 實例應用 RIGHT 函數 MID 函數 CONCATENATE 函數

LEFT 函數 LEFT 函數可以幫我們從字串的最左邊開始擷取指定長度的字串。其格式為：

實例應用 旗旗公司的全年度教育訓練課程已經公告出來, 原始資料是直接輸入課程的起迄時間, 若我們想要讓課程的起迄時間分開存於不同儲存格, 便可利用 LEFT 函數取出課程開始時間：

RIGHT 函數 RIGHT 函數可以幫我們從字串的最右邊開始擷取指定長度的字串。其格式為：

實例應用 我們已經利用 LEFT 函數取出課程的開始時間, 接著再利用 RIGHT 函數來取出課程的結束時間 ：

實例應用

MID 函數 MID 函數可以讓我們在字串中傳回自指定起始位置到指定長度的字串, 其格式如下：

實例應用 請切換至 Ch09-06 的 MID 工作表, 其中的 B 欄紀錄行動電話的資料, 其格式在輸入時是以 XXXX-XXXXXX 為格式, 但現在卻想要改成 XXXX-XXX-XXX 這樣的格式。我們可以利用 MID 函數將所要的資料取出, 再加上其他格式：

實例應用

實例應用

CONCATENATE 函數 CONCATENATE 函數可以讓我們將多組字串組合成單一字串, 其格式如下：

實例應用 小銘將 Outlook Express 中的朋友通訊錄名單匯入 Excel 中使用, 但卻發現 Outlook Express 的欄位是依照名字、姓氏的方式來存放, 跟小銘平常習慣 "姓名" 的排放方式不同, 那麼小銘可以利用CONCATENATE 函數快速地將二個欄位的字串組合起來哦。請開啟範例檔案 Ch09-06, 切換到 CONCATENATE 工作表, 將插入點移至 C2, 輸入公式 =CONCATENATE (B2, A2)：

實例應用