ihao論壇 - Powered by Discuz! Board

標題: EXCEL函數速查一覽12 [打印本頁]

作者: saftion 時間: 2012-11-3 00:39 標題: EXCEL函數速查一覽12

三、函數應用案例── 算賬理財
1.零存整取儲蓄“零存整取”是工薪階層常用的投資方式，這就需要計算該項投資的未來值，從而決定是否選擇某種儲蓄方式。

(1)函數分解
FV 函數基於固定利率及等額分期付款方式，返回某項投資的未來值。語法：FV(rate,nper,pmt,pv,type) Rate 為各期利率；Nper為總投資期，即該項投資的付款期總數；Pmt 為各期所應支付的金額，其數值在整個年金期間保持不變；Pv為現值，即從該項投資開始計算時已經入賬的款項，或一系列未來付款的當前值的累積和；Type為數字0
或1，用以指定各期的付款時間是在期初還是期末。
(2)實例分析
新建一個工作表，在其A1、B1、C1、D1單格分別輸入“投資利率”、“投資期限”、“投資金額”和“帳戶初始金
額”。假設妻子新建一個帳戶每月底存入300 元，年利2.1% (即月息0.00175)，連續存款5 年，可以在A2 、B2、C2 、D2 單格分別輸入“0.00175”、“60”、“500”和“1”。然後選中E2 單格輸入公式“=FV(A2,B2,-C2,D2,1)”，
回車即可獲得該投資的到期本金合計為“￥18,994.67”。公式中的“-C2”表示資金是支出的，“C2”前不加負號也可，
這樣計算出來的結果就是負值。如果丈夫也有“零存整取”帳戶，每月初存入200 元，年利1.28%(即月息0.001667)，連續存款3 年，可以在A3、B3 、C3、D3單格分別輸入“0.001667”、“36”、“200”和“0”。然後把E2 單格中的公式複製到E3 單格(將游標指向E2 單格的拖動柄，當黑色十字游標出現後向下拖動一格)，即可得知該投資的到期本金合計“￥7,426.42”。提示：上述計算結果包括本金和利息，但不包括利息稅等其他費用。

2.還貸金額
如今貸款購買住房進行消費的家庭越來越多，計算貸款的月償還金額是決策的重要依據，下面我們就來設計如何知道自己每月的還款金額。

(1)函數分解
PMT 函數基於固定利率及等額分期付款方式，返回貸款的每期付款額。語法：PMT(rate,nper,pv,fv,type) Rate 為貸款利率；Nper 為該項貸款的付款總數；Pv 為現值，或一系列未來付款的當前值的累積和；Fv為未來值，或在最後一次付款後希望得到的現金餘額；Type為數字0 或1。

(2)實例分析
新建一個工作表，在其A1、B1、C1、D1單格分別輸入“貸款利率”、“還貸年限”、“貸款金額”和“還貸時間”。
假設貸款年利為4.1%(即月息0.00342)，預計的還貸時間為10 年，貸款金額為10 萬元，且每月底還貸。可以在A2、B2、C2、D2單格分別輸入“0.00342”、“360”、“100000”和“1”(表示月末還貸，0 表示月初還貸)。然後選中E2 單格輸入公式“= PMT(A2,B2,C2,,D2)”，回車就可以獲得每月的還款金額為“￥-481.78”。上式中C2 後的兩個逗號之間還有一個參數，表示還貸期限結束時帳戶上的餘額，對這個例子來說應該是0，所以可以忽略該參數或寫成“= PMT(A2,B2,C2,0,D2)”。

3.保險收益
保險公司開辦了一種平安保險，具體辦法是一次性繳費12000 元，保險期限為20 年。如果保險期限內沒有出險，每年返還1 000 元。請問在沒有出險的情況下，它與現在的銀行利率相比，這種保險的收益率如何。

(1)函數分解
RATE 函數返回投資的各期利率。該函數通過迭代法計算得出，並且可能無解或有多個解。語法：RATE(nper,pmt,pv,fv,type,guess) Nper 為總投資期，即該項投資的付款期總數；Pmt 為各期付款額，其數值在整個投資期內保持不變；Pv為現值，即從該項投資開始計算時已經入帳的款項，或一系列未來付款當前值的累積和；Fv為未來值，或在最後一次付款後希望得到的現金餘額；Type為數字0 或1 。

(2)實例分析
新建一個工作表，在其A1、B1、C1、D1單格分別輸入“保險年限”、“年返還金額”、“保險金額”、“年底返還”
和“現行利息”。然後在A2 、B2、C2、D2 和E2 單格分別輸入“20”、“1000”、“12000”、“1”(表示年底返還，0表示年初返還)和“0.02”。然後選中F2 單格輸入公式“=RATE(A2,B2,C2,,D2,E2)”，回車就可以獲得該保險的年收益率為“0.06”。要高於現行的銀行存款利率，所以還是有利可圖的。上面公式中的C2 後面有兩個逗號，說明最後一次付款後帳面上的現金餘額為零。

4.個稅繳納金額
假設個人收入調節稅的收繳標準是：工資在800 元以下的免徵調節稅，工資800 元以上至1 500 元的超過部分按5%的稅率徵收，1 500 元以上至2 000 元的超過部分按8%的稅率徵收，高於2 000 元的超過部分按20%的稅率徵收。我們可以按以下方法設計一個可以修改收繳標準的工作簿：新建一個工作表，在其A1、B1、C1、D1、E1單格分別輸入“姓名”、“工資總額”、“扣款”、“個稅”和“實付工資”。為了方便個稅標準的修改，我們可以另外打開一個工作表(例如Sheet2)，在其A1、B1、C1、D1 、E1 單格中輸入“免征標準”、“低標準”、“中等標準”和“高標準”，然後分別在其下方的單格內輸入“800”、“1500”、“2000”、“2000”。接下來回到工作表Sheet1 中，選中D 列的D2 單格輸入公式“=IF(C2<=Sheet2!A2," ",IF((C2-Sheet2!A2)<=Sheet2!B2,(C2-Sheet2!A2)*0.05,IF
(C2-Sheet2!C2<=Sheet2!C2,(C2-Sheet2!C2)*0.08,IF(C2>Sh eet2!D2,(C2-Sheet2!D2)*0.2))))”，回車後即可計算出C2
單格中的應繳個稅金額。此後用戶只需把公式複製到C3、C4 等單格，就可以計算出其他職工應繳納的個稅金額。上述公式的特點是把個稅的徵收標準放到另一個工作表中，如果徵稅標準發生了變化，用戶只需修改相應單格中的數值，不需要對公式進行修改，可以減少發生計算錯誤的可能。公式中的IF 語句是逐次計算的，如果第一個邏輯判斷“C2-Sheet2!A2)<=Sheet2!B2”成立，即工資收入低於徵收標準，則個稅計算公式所在單格被填入空格；如果第一個邏輯判斷式不成立，則計算第二個IF 語句，直至計算結束。假如徵稅標準多於4 個，可以按上述繼續嵌套IF 函數(最多7 個)。

四、函數應用案例── 資訊統計

使用Excel 管理人事資訊，具有無須編程、簡便易行的特點。假設有一個人事管理工作表，它的A1、B1、C1、D1 、E1、F1、G1 和H1 單格分別輸入“序號”、“姓名”、“身份證號碼”、“性別”、“出生年月”等。自第2 行開始依次輸入職工的人事資訊。為了盡可能減少資料錄入的工作量，下面利用Excel 函數實現資料統計的自動化。

1.性別輸入根據現行的居民身份證號碼編碼規定，正在使用的18 位元的身份證編碼。它的第17 位為性別(奇數為男，偶數為女)，第18 位為效驗位。而早期使用的是15 位元的身份證編碼，它的第15 位是性別(奇數為男，偶數為女)。

(1)函數分解
LEN 函數返回文本字串中的字元數。語法：LEN(text) Text 是要查找其長度的文本。空格將作為字元進行計數。
MOD 函數返回兩數相除的餘數。結果的正負號與除數相同。語法：MOD(number,divisor) Number 為被除數；Divisor為除數。 MID 函數返回文本字串中從指定位置開始的特定數目的字元，該數目由用戶指定。語法：MID(text,start_num,num_chars) Text 為包含要提取字元的文本字串；Start_num 為文本中要提取的第一個字元的位置。文本中第一個字元的start_num 為1 ，以此類推；Num_chars指定希望MID 從文本中返回字元的個數。

(2)實例分析
為了適應上述情況，必須設計一個能夠適應兩種身份編碼的性別計算公式，在D2 單格中輸入“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”。回車後即可
在單格獲得該職工的性別，而後只要把公式複製到D3、D4等單格，即可得到其他職工的性別。為了便於大家瞭解上述公式的設計思路，下面簡單介紹一下它的工作原理：該公式由三個IF 函數構成，其中“IF(MOD(MID(C2,15,1),2)=1,"男","女")”和“IF(MOD(MID(C2,17,1),2)=1,"男","女")”作為第一個函數的參數。公式中“LEN(C2)=15”是一個邏輯判斷語句，LEN 函數提取C2 等單格中的字元長度，如果該字元的長度等於15，
則執行參數中的第一個IF 函數，否則就執行第二個IF 函數。在參數“IF(MOD(MID(C2,15,1),2)=1,"男","女")”中。
MID 函數從C2 的指定位置(第15 位)提取1 個字元，而MOD 函數將該字元與2 相除，獲取兩者的餘數。如果兩者能夠除盡，說明提取出來的字元是0(否則就是1)。邏輯條件“MOD(MID(C2,15,1),2)=1”不成立，這時就會在D2 單格中填入“女”，反之則會填入“男”。如果LEN 函數提取的C2 等單格中的字元長度不等於15，則會執行第2個IF函數。除了MID 函數從C2 的指定位置(第17 位，即倒數第2 位)提取1 個字元以外，其他運算過程
與上面的介紹相同。

2.出生日期輸入
(1)函數分解
CONCATENATE 函數將幾個文本字串合併為一個文本字串。語法：CONCATENATE(text1,text2,...) Text1,text2,...為1～30 個要合併成單個文本項的文本項。文本項可以為文本字串、數位或對單個單格的引用。

(2)實例分析
與上面的思路相同，我們可以在E2 單格中輸入公式“=IF(LEN(C2)=15,CONCATENATE("19",MID(C2,7,2),"年
",MID(C2,9,2),"月",MID(C2,11,2),"日"),CONCCTENCTE(MID(C2,7,4),"年",MID(C2,11,2),"月",MID(C2,13,2),"日"))”。其中“LEN(C2)=15”仍然作為邏輯判斷語句使用，它可以判斷身份證號碼是15 位的還是18 位的，從而調用相應的計算語句。對15 位的身份證號碼來說，左起第7 至12 個字元表示出生年、月、日，此時可以使用MID 函數從身份證號碼的特定位置，分別提取出生年、月、日。然後用CONCATENATE 函數將提取出來的文字合併起來，就能得到對應的出生年月日。公式中“19”是針對早期身份證號碼中存在2000 年問題設計的，它可以在計算出來的出生年份前加上“19”。對“18”位的身份證號碼的計算思路相同，只是它不存在2000 年問題，公式中不用給計算出來的出生年份前加上“19”。注意：CONCATENATE 函數和MID 函數的操作物件均為文本，所以存放身份證號碼的單格必須事先設為文本格式，然後再輸入身份證號。

3.職工資訊查詢
Excel 提供的“記錄單”功能可以查詢記錄，如果要查詢人事管理工作表中的某條記錄，然後把它列印出來，必須採用下面介紹的方法。

(1)函數分解
INDEX 函數返回資料清單或陣列中的元素值，此元素由行序號和列序號的索引值給定。 INDEX 函數有兩種語法形式：陣列和引用。陣列形式通常返回數值或數值陣列，引用形式通常返回引用。當函數INDEX 的第一個參數為陣列常數時，使用陣列形式。語法1(陣列形式)：INDEX(array,row_num,column_num) Array 為單格區域或陣列常量。如果陣列只包含一行或一列，則相對應的參數row_num 或column_num為可選。如果陣列有多行和多列，但只使用row_num 或c olumn_num，函數INDEX 返回陣列中的整行或整列，且返回值也為陣列；Row_num 為陣列中某行的行序號，函數從該行返回數值。如果省略row_num，則必須有column_num；Column_num 為陣列中某列的
列序號，函數從該列返回數值。如果省略column_num，則必須有row_num。語法2(引用形式)：INDEX(reference,row_num,column_num,area_num) Reference 表示對一個或多個單格區域的引用。如果為引用輸入一個不連續的區域，必須用括弧括起來。如果引用中的每個區域只包含一行或一列，則相應的參數row_num 或
column_num 分別為可選項；Row_num 引用中某行的行序號，函數從該行返回一個引用；Column_num引用中某列的列序號，函數從該列返回一個引用；Area_num 選擇引用中的一個區域，並返回該區域中row_num 和column_num 的交叉區域。選中或輸入的第一個區域序號為1，第二個為2，以此類推。如果省略area_num，函數INDEX 使用區域1。 MATCH 函數返回在指定方式下與指定數值匹配的陣列中元素的相應位置。語法：MATCH(lookup_value,lookup_array,match_type) Lookup_value 為需要在資料表中查找的數值；Lookup_value 為需要在Look_array 中查找的數值；Match_type 為數字-1、0或1 。

(2)實例分析
如果上面的人事管理工作表放在Sheet1 中，為了防止因查詢操作而破壞它(必要時可以添加唯讀保護)，我們可以打開另外一個空白工作表Sheet2，把上一個資料清單中的列標記複製到第一行。假如你要以“身份證號碼”作為查詢關鍵字，就要在C2 單格中輸入公式“=INDEX(Sheet1!C2:C600,MATCH( SC S5,Sheet1! SC S2: SC S600,0),1)”。其中的參數“ SC S5”引用公式所在工作表中的C5 單格(也可以選用其他單格)，執行查詢時要在其中輸入查詢關鍵字，也就是待查詢記錄中的身份證號碼。參數“Sheet1!C2:C600”設定INDEX 函數的查詢範圍，引用的是數
據清單C 列的所有單格。MATCH函數中的參數“0”指定它查找“Sheet1! SC S2: SC S600”區域中等於 SC S5的第一個值，並且引用的區域“Sheet1! SC S2: SC S600,0”可以按任意順序排列。上面的公式執行資料查詢操作時，首先由MATCH 函數在“Sheet1! SC S2: SC S600” 區域搜索，找到“ SC S5” 單格中的資料在引用區域中的位置(自上而下第幾個單格)，從而得知待查詢資料在引用區域中的第幾行。接下來INDEX 函數根據MATCH 函數給出的行號，返回“Sheet1!C2:C600”區域中對應行數單格中的資料。假設其中待查詢的“身份證號碼”是“3234567896”，它位於“Sheet1! SC S2: SC S600”區域的第三行，MATCH函數就會返回“3”。接著INDEX 函數返回“Sheet1!C2:C600”區域中行數是“3”的資料，也就是“3234567896”。然後，我們將游標放到C2 單格的填充柄上，當十字游標出現以後向右拖動，從而把C2 中的公式複製到D2、E2 等單格(然後再向左拖動，以便把公式複製到B2、A2單格)，這樣就可以獲得與該身份證號對應的性別、籍貫等資料。注意：公式複製到D2、E2等單格以後，INDEX函數引用的區域就會發生變化，由C2:C600 變成D2

600、E2:E600等等。但是MATCH 函數返回的(相對)行號仍然由查詢關鍵字給出，此後INDEX 函數就會根據MATCH 函數返回的行號從引用區域中找到資料。在Sheet2 工作表中進行查詢時只要在查詢輸入單格中輸入關鍵字，回車後即可在工作表的C2 單格內看到查詢出來的身份證號碼。如果輸入的身份證號碼關鍵字不存在或輸入錯誤，則單格內會顯示“#N/A”字樣。

歡迎光臨 ihao論壇 (https://ihao.org/dz5/)