Board logo

標題: 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/) Powered by Discuz! 6.0.0