excel2007技巧之6函數(shù)高級(jí)應(yīng)用實(shí)戰(zhàn)技巧.doc

Chapter 6 圖形和圖表實(shí)戰(zhàn)技巧嵌套函數(shù)實(shí)戰(zhàn)技巧函數(shù)的嵌套是指某個(gè)函數(shù)引用另一個(gè)函數(shù)的返回值來進(jìn)行計(jì)算。文本函數(shù)實(shí)戰(zhàn)技巧文本函數(shù)用于對(duì)文本數(shù)據(jù)進(jìn)行提取、刪除、替換以及組合等運(yùn)算。統(tǒng)計(jì)函數(shù)實(shí)戰(zhàn)技巧財(cái)務(wù)函數(shù)實(shí)戰(zhàn)技巧查找和引用函數(shù)實(shí)戰(zhàn)技巧統(tǒng)計(jì)函數(shù)主要有對(duì)單元格數(shù)量統(tǒng)計(jì)、排位、檢驗(yàn)與回歸分析等功能。財(cái)務(wù)函數(shù)用于對(duì)財(cái)務(wù)數(shù)據(jù)進(jìn)行分析和計(jì)算，是財(cái)務(wù)工作中必不可少的工具。查找和引用函數(shù)用于在數(shù)據(jù)表中查找某一特定的內(nèi)容，或者引用公式中的相關(guān)信息內(nèi)容。日期和時(shí)間函數(shù)用于對(duì)日期和時(shí)間進(jìn)行計(jì)算，修改等操作中。日期函數(shù)實(shí)戰(zhàn)技巧函數(shù)高級(jí)應(yīng)用實(shí)戰(zhàn)技巧6函數(shù)是Excel中重要的內(nèi)容，本章將在上一章的基礎(chǔ)上，從不同的函數(shù)類型著手，詳細(xì)為讀者講述函數(shù)高級(jí)應(yīng)用的內(nèi)容和技巧，以幫助讀者完全掌握這部分的知識(shí)。本章導(dǎo)讀6.1 日期和時(shí)間函數(shù)實(shí)戰(zhàn)技巧257例1 快速將日期轉(zhuǎn)換為星期輸入輸入輸入在Excel中使用函數(shù)可以快速將日期轉(zhuǎn)換為星期，具體操作方法如下： 在工作表中輸入幾個(gè)日期，然后在單元格D3中輸入函數(shù)“=TEXT(WEEKDAY(D3),”dddd”，如下圖所示。 按【Enter】鍵，將日期轉(zhuǎn)換成星期，在單元格D4中輸入“=TEXT(WEEKDAY(C4),”aaaa”，如下圖所示。 按【Enter】鍵，得到中文的星期，結(jié)果如下圖所示。例2 計(jì)算當(dāng)前日期是全年的第幾天在Excel中使用函數(shù)可以快速將日期轉(zhuǎn)換為星期，具體操作方法如下： 打開“臺(tái)歷”工作表，復(fù)制并修改。在單元格G5中輸入公式“=E4-DATE(YEAR(E4),1,0)”，如下圖所示。 按【Ctrl+Enter】組合鍵，得到公式計(jì)算結(jié)果，如下圖所示。例3 計(jì)算上個(gè)月的天數(shù)在Excel中計(jì)算上個(gè)月的天數(shù)，可使用如下的兩種方法。方法一：使用DAY函數(shù)計(jì)算輸入 新建工作表并輸入數(shù)據(jù)內(nèi)容，在單元格C4中輸入公式“=DAY(B4-DAY(B4)”，如下圖所示。拖動(dòng) 按【Ctrl+Enter】組合鍵，得到公式計(jì)算結(jié)果，然后用拖動(dòng)填充柄填充公式，最終效果如下圖所示。輸入DAY函數(shù)用于返回所引用日期中的天數(shù)，DAY(B4)計(jì)算得出天數(shù)5，DAY(B4-DAY(B4)的含義是返回5天前的天數(shù)，即上個(gè)月最后一天的天數(shù)。方法二：使用DAY函數(shù)計(jì)算輸入 在單元格D4中輸入公式“=DAY(EOMONTH(B4,-1)”，如下圖所示。拖動(dòng) 按【Ctrl+Enter】組合鍵，得到公式計(jì)算結(jié)果，然后拖動(dòng)填充柄填充公式，結(jié)果如下圖所示。EOMONTH函數(shù)用于返回所引用日期之前或之后的月份最后一天的序列號(hào)。EOMONTH(B4,-1)的含義是返回單元格B4中日期前一個(gè)月的最后一天的序列。例4 輕松求得當(dāng)前日期所在的周數(shù)每一年的1月1日所在的周是一年中的第一周，使用WEEKNUM函數(shù)可求得某個(gè)日期所在的周數(shù)，具體操作方法如下： 打開“臺(tái)歷”工作表，在單元格G6中輸入函數(shù)“=WEEKNUM(E5)”，如下圖所示。 單擊【Ctrl+Enter】組合鍵，得到當(dāng)前日期所在年中的周數(shù)，如下圖所示。WEEKNUM函數(shù)的語法為WEEKNUM(serial_num,return_type)，Serial_num代表一周中的日期，Return_type為1個(gè)數(shù)字，確定星期計(jì)算從哪一天開始，如果為1，說明一周開始于星期日如果為2，則說明一周開始于星期一，如果省略不寫，則默認(rèn)為1。例5 巧妙計(jì)算員工年齡在Excel中，如果已知員工的出生年月，就可使用YEAR函數(shù)快速的計(jì)算出員工的年齡，具體操作方法如下：輸入 在單元格D3中輸入公式“=YEAR(TODAY()-C3)-1900”，如下圖所示。 按【Ctrl+Enter】組合鍵，得到結(jié)果，如下圖所示。輸入輸入 在“開始”選項(xiàng)卡下“數(shù)字”組中的“數(shù)字格式”下拉列表框中，將該單元格的數(shù)字格式設(shè)置為“常規(guī)”，該單元格中將自動(dòng)顯示年齡，如下圖所示。 將鼠標(biāo)指針移動(dòng)單元格D3的右下角，拖動(dòng)填充柄，填充公式，效果如下圖所示。因?yàn)镋xcel中使用了1900年時(shí)間系統(tǒng)，公式TODAY()-C3進(jìn)行計(jì)算時(shí)將自動(dòng)將相減的結(jié)果加上1900，所有要得到正確的年齡，還需減去1900。例6 計(jì)算兩個(gè)日期之間工作日的天數(shù)計(jì)算兩個(gè)日期之間工作日的天數(shù)的具體操作方法如下： 在單元格C6中輸入公式“=NETWORKDAYS(C4,D4)”，如下圖所示。 按【Ctrl+Enter】組合鍵，得出計(jì)算結(jié)果，如下圖所示。NETWORKDAYS返回參數(shù) start_date 和 end_date 之間完整的工作日數(shù)值。其中不包括周日和假期。例7 計(jì)算同年中兩個(gè)日期之間的月數(shù)計(jì)算同年中兩個(gè)日期之間的月數(shù)的具體操作方法如下：在單元格E4中輸入公式“=MONTH(D4)-MONTH(C4)”，按【Ctrl+Enter】鍵即可計(jì)算出結(jié)果，如下圖所示。輸入輸入例8 計(jì)算跨年份兩個(gè)日期之間的月數(shù) 在單元格E5中輸入公式“=YEAR(D5)-YEAR(C5)*12+MONTH(D5)-MONTH(C5)”，按【Ctrl+Enter】鍵即可計(jì)算出結(jié)果，如下圖所示。例9 計(jì)算兩個(gè)日期之間的年數(shù)計(jì)算年數(shù)的方法很簡(jiǎn)單，只需用YEAY函數(shù)將兩個(gè)返回值相減即可，如下圖所示。輸入6.2 查找和引用函數(shù)實(shí)戰(zhàn)技巧例1 使用函數(shù)巧查詢?cè)贓xcel中，VLOOKUP函數(shù)用于在表格數(shù)組的首列查找指定的值，并由此返回表格數(shù)組當(dāng)前行中其他列的值。下面使用該函數(shù)來快速查找所需的值，具體操作方法如下： 打開“樓盤概覽”工作表，在單元格區(qū)域E14:F15中輸入數(shù)據(jù)內(nèi)容和公式，如下圖所示。輸入 在單元格E15中輸入相應(yīng)的樓盤名稱，將在單元格F15中顯示該樓盤的開盤時(shí)間，如下圖所示。VLOOKUP函數(shù)最后一個(gè)參數(shù)為邏輯值，其中false值代表精確匹配，true值代表模糊匹配。例2 用V.LOOKUP函數(shù)逆向查詢從上一個(gè)技巧中，已知VLOOKUP函數(shù)只能根據(jù)首列中指定的值查詢其他列與之相對(duì)應(yīng)的內(nèi)容。其實(shí)，使用該函數(shù)和其他函數(shù)的嵌套也可以實(shí)現(xiàn)通過其他列的數(shù)據(jù)來查詢首列的內(nèi)容，即逆向查詢，其具體操作方法如下： 如果已知樓盤的開盤時(shí)間，要查詢樓盤名稱，可輸入公式“=VLOOKUP(F16,IF(0,1,D5:D12,E5:E12),2,FALSE)”，如下圖所示。輸入5.單擊 按【Ctrl+Enter】組合鍵，將得到樓盤的名稱，如下圖所示。例3 CHOOSE函數(shù)的用法CHOOSE函數(shù)可以根據(jù)索引號(hào)返回?cái)?shù)值參數(shù)列表中的數(shù)值。下面通過使用該函數(shù)得到獎(jiǎng)金等級(jí)為例，介紹其使用方法。 在員工的獎(jiǎng)金名單中，選擇單元格D3，在其中輸入公式“=CHOOSE(IF(C3=5000,1,2),一等獎(jiǎng),二等獎(jiǎng),三等獎(jiǎng))”，如下圖所示。輸入 按【Ctrl+Enter】組合鍵，得到獎(jiǎng)金等級(jí)，如下圖所示。 通過拖動(dòng)填充柄，復(fù)制公式，得到其他員工的銷售獎(jiǎng)金等級(jí)，如下圖所示。例4 快速查找化工原料的數(shù)量繼續(xù)上一個(gè)實(shí)例，通過將INDEX函數(shù)與MATCH函數(shù)配合使用，可以查找某種化學(xué)原料的數(shù)量，其具體操作方法如下： 在化工原料信息工作表中，單擊單元格D48，從中輸入公式“=INDEX(C39:C45, MATCH(草酸鹽,B39:B45,0)”，如下圖所示。輸入 按【Ctrl+Enter】組合鍵確認(rèn)，將得到“草酸鹽”的數(shù)量為460，如下圖所示。例5 使用函數(shù)進(jìn)行動(dòng)態(tài)求和下面通過實(shí)例來介紹使用函數(shù)進(jìn)行動(dòng)態(tài)求和的過程，具體操作方法如下：1.輸入2.選擇拖動(dòng) 新建工作表，從中輸入產(chǎn)品銷售額的相關(guān)信息，選擇單元格D11，然后單擊“數(shù)據(jù)”選項(xiàng)卡下“數(shù)據(jù)工具”組中的“數(shù)據(jù)有效性”按鈕下方的下拉按鈕，在彈出的下拉菜單中選擇“數(shù)據(jù)有效性”選項(xiàng)，如下圖所示。單擊2.設(shè)置1.選擇青年以3.單擊4.選擇 將彈出“數(shù)據(jù)有效性”對(duì)話框，在其中的“允許”下拉列表框中選擇“序列”選項(xiàng)，在“來源”文本框中設(shè)置序列來源，如下圖所示。 單擊“確定”按鈕，在單元格D11右側(cè)將出現(xiàn)一個(gè)下拉按鈕，單擊它，將彈出下拉列表，可以從中進(jìn)行選擇，效果如下圖所示。2.單擊1.選擇 選擇單元格區(qū)域C4:C8，然后單擊“公式”選項(xiàng)卡下“定義的名稱”組中的“定義名稱”按鈕，將彈出“新建名稱”對(duì)話框，保持默認(rèn)即可，如下圖所示。 分別選擇單元格區(qū)域D4:D8、E4:E8和F4:F8，定義名稱，然后單擊“定義的名稱”組中的“名稱管理器”按鈕，彈出“名稱管理器”對(duì)話框，從中可查看定義的名稱，如下圖所示。2.輸入1.選擇 選擇單元格D12，從中輸入公式“=SUM(INDIRECT($D$11)”，按【Ctrl+Enter】組合鍵確認(rèn)，將得到“銷售點(diǎn)1”的所有產(chǎn)品的銷售額，如下圖所示。例6 判斷員工姓名是否重復(fù)錄入在員工信息表中，要判斷是否重復(fù)錄入了員工的信息，可通過查找員工姓名是否重復(fù)，來進(jìn)行判斷（假如員工中沒有重名者），其具體操作方法如下： 新建員工信息表，選擇單元格H4，從中輸入“=IF(ROW()=MATCH(C4,$C$1:$C$16,0),重復(fù))”，如下圖所示。輸入拖動(dòng) 按【Ctrl+Enter】組合鍵確認(rèn)輸入，然后拖動(dòng)填充柄進(jìn)行填充，將查找出重復(fù)的姓名，如下圖所示。例7 快速生成數(shù)字序列制作工作表時(shí)，經(jīng)常要輸入數(shù)字序列，輸入數(shù)字序列的方法有很多，這里我們使用函數(shù)來快速輸入數(shù)字序列，其具體操作方法如下： 新建工作表，選擇單元格區(qū)域A1:A10，然后按【F2】鍵，從中輸入“=ROW(1:10)”，如下圖所示。2.輸入1.選擇 按【Ctrl+Shift+Enter】組合鍵，確認(rèn)數(shù)組輸入，即可快速填充好數(shù)字序列，如下圖所示。輸入 要在一行中填充數(shù)字序列，可以先選擇要輸入數(shù)字序列的單元格區(qū)域，然后按【F2】鍵輸入函數(shù)“=COLUMN(A:F)”，如下圖所示。 按【Ctrl+Shift+Enter】組合鍵，確認(rèn)數(shù)組輸入，也可快速填充字序列，如下圖所示。例8 快速引用不同工作表中的數(shù)據(jù)在制作工作表時(shí)，經(jīng)常用在不同的工作表中引用數(shù)據(jù)信息，下面介紹一種使用函數(shù)引用其他工作表中的數(shù)據(jù)的方法。 在Sheet3中創(chuàng)建一張員工檔案表，存放員工的基本信息，如下圖所示。3.選擇2.單擊1.選擇 切換到Sheet1中，輸入有關(guān)員工工資的信息，然后選擇單元格B4，單擊“公式”選項(xiàng)卡下“函數(shù)庫”組中的“查找與引用”下拉按鈕，在彈出的下拉菜單中選擇VLOOKUP函數(shù)，如下圖所示。 彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的參數(shù)，如下圖所示。設(shè)置拖動(dòng) 單擊【確定】按鈕，關(guān)閉對(duì)話框，得到公式結(jié)果，然后拖動(dòng)填充柄填充公式，如下圖所示。例9 返回最后一個(gè)非空單元格在包含空的單元格中，要查找最后一個(gè)非空單元格中的值，可按如下方法操作： 建工作表，從中制作有關(guān)產(chǎn)品的信息數(shù)據(jù)表，新然后在單元格E13中輸入公式“LOOKUP(CHAR(255),B:B)”，然后按【Ctrl+Enter】組合鍵確認(rèn)，得到公式查找的結(jié)果，如下圖所示。輸入輸入 在單元格E14中輸入公式“LOOKUP(9E+307,C:D)，然后按【Ctrl+Enter】組合鍵，得到最后結(jié)果，如下圖所示。CHAR函數(shù)用于返回對(duì)應(yīng)于數(shù)字代碼的字符，它可將其他類型計(jì)算機(jī)文件中的代碼轉(zhuǎn)換為字符；9E+307為Excel中可以輸入的最大數(shù)值。例10 快速查找最后非空單元格的位置快速查找最后一個(gè)非空單元格的位置的具體操作步驟如下：輸入 在單元格F3中輸入公式“=MATCH(CHAR(1),B:B,-1)”，如下圖所示。 按【Ctrl+Enter】組合鍵，查找到的最后一個(gè)非空單元格的位置為11，如下圖所示。例11 快速定位到空單元格通過創(chuàng)建單元格名稱和使用鏈接函數(shù)可以快速定位到空單元格中。下面以具體的實(shí)例介紹快速定位到空單元格的方法。2.單擊1.輸入 新建工作表，從中輸入產(chǎn)品信息，單擊“公式”選項(xiàng)卡下“定義的名稱”組中的“定義名稱”按鈕，如下圖所示。 彈出“新建名稱”對(duì)話框，在“名稱”文本框中輸入文字“空單元格”，在“引用位置”填充2.輸入單擊2.輸入2.輸入1.輸入文本框中輸入公式“=OFFSET($B$1,COUNTA($B:$B)+1,)”如下圖所示。 單擊“確定”按鈕，創(chuàng)建動(dòng)態(tài)名稱，然后選擇單元格F3，從中輸入“=HYPERLINK(#空單元格,快速定位)”，如下圖所示。 公式確認(rèn)后，將在單元格F3中顯示鏈接文字“快速定位”，單擊它，將定位到“產(chǎn)品”列下方的空單元格中，如下圖所示。例12 快速返回唯一值列表有的工作表中有不少重復(fù)的數(shù)據(jù)信息，要過濾這些重復(fù)的信息，讓表中顯示唯一的數(shù)據(jù)值，可以采用如下的方法。 打開一張產(chǎn)品信息表，在該表中“產(chǎn)品”列中有重復(fù)的產(chǎn)品信息，如下圖所示。 在單元格B14中輸入公式“=OFFSET($B$2,SMALL(IF(MATCH($B$3:$B$11,$B$3:$B$11,0)=ROW($B$3:$B$11)-2,ROW($B$3:$B$11)-2),ROW(A1),)”，如下圖所示。 按【ctrl+shift+enter】組合鍵，返回產(chǎn)品值“AC001”，使用填充柄進(jìn)行填充，將得到不重復(fù)的值，結(jié)果如下圖所示。MATCH($B$3:$B$11,$B$3:$B$11,0)返回各產(chǎn)品名稱所在的單元格位置，該位置如果為該產(chǎn)品名稱所在表中的位置，說明該產(chǎn)品名稱不重復(fù)，否則產(chǎn)品名稱重復(fù)。6.3 財(cái)務(wù)函數(shù)實(shí)戰(zhàn)技巧例1 使用PMT函數(shù)計(jì)算還款金額輸入選擇PMT函數(shù)返回在固定利率下，投資或貸款的等額分期償還額。使用該函數(shù)計(jì)算貸款的還款額的具體操作方法如下： 打開“分期付款計(jì)算表”工作表，選擇單元格E4，如下圖所示。選擇 單擊“公式”選項(xiàng)卡下“函數(shù)庫”組中的“插入函數(shù)”按鈕，彈出“插入函數(shù)”對(duì)話框，在“選擇函數(shù)”列表框中選擇PMT函數(shù)，如下圖所示。 單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，從中輸入?yún)?shù)，如下圖所示。 單擊“確定”按鈕，得到計(jì)算結(jié)果，用前面所講的復(fù)制公式的方法，復(fù)制公式，計(jì)算出其他的月償還金額，如下圖所示。PMT函數(shù)的語法結(jié)構(gòu)為PMT（rate, nper,pv,fv,type），其中，rate為貸款利率。nper為該項(xiàng)貸款的付款期總數(shù)。pv為現(xiàn)值，或一系列未來付款的當(dāng)前值的累積和，也稱為本金。Fv為未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略 fv，則假設(shè)其值為0，也就是一筆貸款的未來值為0。Type為數(shù)字 0 或 1，用以指定各期的付款時(shí)間是在期初還是期末。如果省略，則默認(rèn)為0，代表期末付款。例2 使用PMT函數(shù)計(jì)算存款金額假設(shè)在銀行存款，要使10年后得到7萬元，則計(jì)算每月的存款金額，可使用如下的方法得出： 新建工作表，從中輸入數(shù)據(jù)，并在單元格C5中輸入公式“=PMT(C4/12,C3*12,0,70000)”如下圖所示。輸入 按【Ctrl+Enter】組合鍵，計(jì)算出每個(gè)月應(yīng)存款的金額，如下圖所示。例3 使用PPMT函數(shù)求本金償還額PPMT函數(shù)基于固定利率及等額分期付款方式，用于返回投資在某一給定期間內(nèi)的本金償還額。使用該函數(shù)計(jì)算本金償還金額的具體操作方法如下： 新建工作表，從中輸入數(shù)據(jù)，選擇單元格C5，輸入“=PPMT(”，如下圖所示。輸入設(shè)置 按【Shift+F3】組合鍵，彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置參數(shù)，如下圖所示。拖動(dòng) 單擊“確定”按鈕，求得返還的第一年本金額，然后拖動(dòng)填充柄，填充公式，最終效果如下圖所示。例4 使用IPMT函數(shù)求本金償還額IPMT函數(shù)基于固定利率及等額分期付款方式，用于返回給定期數(shù)內(nèi)對(duì)的利息償還額。使用該函數(shù)計(jì)算利息償還金額的具體操作方法如下： 以上個(gè)技巧中的表格為基礎(chǔ)，添加“返還利息”列，在單元格D5中輸入函數(shù)“=IPMT($D$3,B5,$C$3,$B$3)”，如下圖所示。填充輸入 按【Ctrl+Enter】組合鍵，得到第一年的返還利息額，通過拖動(dòng)填充柄，填充公式，得到每年的返還利息，最終效果如下圖所示。例5 使用FV函數(shù)計(jì)算投資價(jià)值使用FV函數(shù)可以返回投資的現(xiàn)值?，F(xiàn)值為一系列未來付款的當(dāng)前值的累積和。該函數(shù)的使用方法如下： 在“投資計(jì)算表”中單擊單元格F5，然后在其中輸入函數(shù)“=FV(C5/12,D5,E5)”，如下圖所示。輸入 按【Enter】鍵，因?yàn)槭潜砀袼宰詣?dòng)填充公式計(jì)算出結(jié)果，如下圖所示。該函數(shù)的語法結(jié)構(gòu)為FV(rate,nper,pmt,fv,type)，其中，pmt為各期所應(yīng)支付的金額，其數(shù)值在整個(gè)年金期間保持不變。通常，pmt 包括本金和利息，但不包括其他費(fèi)用或稅款。如果忽略 pmt，則必須包含 fv 參數(shù)。Fv為未來值，或在最后一次支付后希望得到的現(xiàn)金余額，如果省略 fv，則假設(shè)其值為零?？梢愿鶕?jù)保守估計(jì)的利率來決定每月的存款額。如果忽略 fv，則必須包含 pmt參數(shù)。例6 使用PV函數(shù)計(jì)算年金的現(xiàn)值假設(shè)要購買一種養(yǎng)老保險(xiǎn)，購買成本為10萬元，該保險(xiǎn)可以在20年內(nèi)于每月末回報(bào)800元、投資報(bào)酬率為10，要計(jì)算該保險(xiǎn)的年金現(xiàn)值，其具體的計(jì)算方法如下： 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格C7，從中輸入函數(shù)“=PV(C4/12,C3*12,C2,0)”，如下圖所示。輸入該函數(shù)的語法結(jié)構(gòu)為PV(rate, nper,pmt,fv,type)。 按【Ctrl+Enter】組合鍵，計(jì)算出年金的現(xiàn)值，如下圖所示。計(jì)算得出的年金現(xiàn)值為82.899.69元，小于10萬元，所以購買該保險(xiǎn)不劃算。例7 用PV函數(shù)求一次性支付金額假設(shè)要用5年時(shí)間分期付款購車，每年預(yù)付6000元，銀行利率為6.5，要計(jì)算如果要一次性支付完，需支付的金額，其具體的計(jì)算方法如下： 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格C7，從中輸入函數(shù)“=PV(C4,C3,C2,1)”，如下圖所示。輸入 按【Ctrl+Enter】組合鍵，計(jì)算出的一次性支付金額，如下圖所示。例8 使用SLN函數(shù)計(jì)算資產(chǎn)折舊SLN函數(shù)用于計(jì)算某項(xiàng)資產(chǎn)在一個(gè)期間中的線性折舊值。使用該函數(shù)計(jì)算線性折舊值的具體操作方法如下： 新建工作表，從中輸入數(shù)據(jù)，選擇單元格E3，如下圖所示。1.輸入選擇 單擊“公式”選項(xiàng)卡下“函數(shù)庫”組中的“插入函數(shù)”按鈕，彈出“插入函數(shù)”對(duì)話框，在“選擇函數(shù)”列表框中選擇SLN函數(shù)，如下圖所示。選擇 單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，從中輸入?yún)?shù)，然后單擊“確定”按鈕，得到每年的線性折舊值，如下圖所示。選擇輸入SLN函數(shù)的語法結(jié)構(gòu)為SLN(cost,salvage,life)，其中，cost為固定資產(chǎn)原值，salvage為指定固定資產(chǎn)殘值，life為折舊周期。例9 用固定余額遞減法求資產(chǎn)折舊DB函數(shù)用于計(jì)算在固定利率下給定日期內(nèi)的資產(chǎn)折舊值，其具體操作方法如下：選擇 新建工作表，從中輸入數(shù)據(jù)，選擇單元格C6，如下圖所示。 單擊編輯欄左側(cè)的“插入函數(shù)”按鈕，彈出“插入函數(shù)”對(duì)話框，在“選擇函數(shù)”列表框中選擇DB函數(shù)，如下圖所示。DB函數(shù)的語法結(jié)構(gòu)為DB(cost,salvage,life,period,month)，其中Period為需要計(jì)算折舊值的期間，Month為第一年使用的月數(shù)，如省略，則代表12個(gè)月。設(shè)置 單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，從中輸入?yún)?shù)，然后單擊“確定”按鈕，得到每年的線性折舊值，如下圖所示。設(shè)置2.輸入1.填充 通過向下拖動(dòng)該單元格右下角的填充柄，在單元格區(qū)域C7:C15填充公式。然后在單元格C16中輸入公式“=B3-C3-SUM(C6:C15)”，計(jì)算出固定資產(chǎn)在使用周期內(nèi)最后6個(gè)月的折舊值，如下圖所示。使用固定余額遞減法計(jì)算折舊值，最后一期的折舊值應(yīng)該通過公式“固定資產(chǎn)原值-剩余殘值-每年中折舊值之和”來計(jì)算。例10 用雙倍余額遞減法求資產(chǎn)折舊使用DDB函數(shù)可以計(jì)算固定資產(chǎn)折舊，其具體操作方法如下：3.選擇2.單擊1.輸入 新建工作表，從中輸入數(shù)據(jù)，然后單擊“公式”選項(xiàng)卡下“函數(shù)庫”組中的“財(cái)務(wù)”下拉按鈕，在彈出的下拉面板中選擇DDB函數(shù)，如下圖所示。DDB函數(shù)的語法結(jié)構(gòu)為DDB(cost,salvage,life,period,factor)，其中Factor為余額遞減速率。如果factor被省略，默認(rèn)為2（即雙倍余額遞減法）。 將彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的函數(shù)，如下圖所示。 單擊“確定”按鈕，得到使用雙倍余額遞減法計(jì)算出的折舊值，然后在單元格區(qū)域C7:C11中填充公式，得到的結(jié)果如下圖所示。選擇還可以使用1.5倍或其他余額遞減法計(jì)算固定資產(chǎn)折舊值。例11 用年限總和折舊法求資產(chǎn)折舊SYD函數(shù)用于返回固定資產(chǎn)按年限總和折舊法計(jì)算出的指定期內(nèi)的折舊值。使用該函數(shù)計(jì)算資產(chǎn)折舊的具體操作方法如下：設(shè)置2.輸入1.輸入設(shè)置3.選擇2.單擊1.輸入 新建工作表，從中輸入數(shù)據(jù)，然后單擊“公式”選項(xiàng)卡下“函數(shù)庫”組中的“財(cái)務(wù)”下拉按鈕，在彈出的下拉面板中選擇SYD函數(shù)，如下圖所示。 將彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的函數(shù)，如下圖所示。填充 單擊“確定”按鈕，得到使用年限綜合折舊法計(jì)算出的折舊值，然后在單元格區(qū)域C7:C12中填充公式，得到的結(jié)果如下圖所示。 使用SUM函數(shù)可以計(jì)算出累計(jì)的折舊值，如下圖所示。例12 用可變遞減折舊法求折舊值VDB函數(shù)使用雙倍余額遞減法或其他指定的方法，返回指定的任何期間內(nèi)（包括部分期間）的資產(chǎn)折舊值。使用該函數(shù)計(jì)算資產(chǎn)折舊的具體操作方法如下： 新建工作表，從中輸入數(shù)據(jù)，然后單擊在單元格D4中輸入“=VDB(”，如下圖所示。 按【Shift+F3】組合鍵，將彈出“函數(shù)參數(shù)”對(duì)話框，在其中可以設(shè)置其具體的函數(shù)參數(shù)，如下圖所示。 單擊“確定”按鈕，得出第一個(gè)月的資產(chǎn)折舊值，如下圖所示。輸入輸入 在相應(yīng)的單元格分別輸入函數(shù)，如下圖所示。輸入輸入輸入 計(jì)算得出的各資產(chǎn)折舊值，如下圖所示。VDB函數(shù)的語法結(jié)構(gòu)為VDB(cost,salvage,life,start_period,end_period,factor,no_switch)，其中，Start_period為進(jìn)行折舊計(jì)算的起始期間；End_period為進(jìn)行折舊計(jì)算的截止期間；No_switch為某個(gè)邏輯值，當(dāng)該參數(shù)為TRUE時(shí)，表示當(dāng)折舊值大于余額遞減計(jì)算值時(shí)，不會(huì)轉(zhuǎn)用直線折舊法，當(dāng)為FALSE時(shí)，即會(huì)專用直線折舊法。例13 使用VDB函數(shù)計(jì)算累計(jì)折舊使用VDB函數(shù)計(jì)算累計(jì)的資產(chǎn)折舊值，具體操作方法如下： 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格D6，從中輸入函數(shù)“=VDB(B3,C3,D3,0,2+1/2,TRUE)”如下圖所示。按【Enter】鍵，將得到第3年第2個(gè)季度末的累計(jì)資本折舊。然后在單元格D10中輸入函數(shù)“=VDB(B3,C3,D3,0,2+1/2,1.5,TRUE)”，如下圖所示。 按【Ctrl+Enter】組合鍵，得到使用1.5倍余額遞減法求得的第3年第2個(gè)季度末的累計(jì)資產(chǎn)折舊，如下圖所示。例14 使用NPER函數(shù)計(jì)算投資期數(shù)假設(shè)現(xiàn)有銀行存款2萬元，以后每月月初往銀行存款1500元，銀行的存款利率是3.3%，如果要計(jì)算存多久后，銀行存款變?yōu)?0萬元，可按如下的方法計(jì)算： 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格C7，從中輸入函數(shù)“=NPER(”，如下圖所示。輸入設(shè)置輸入 按【shift+F3】組合鍵，彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的參數(shù)，如下圖所示。 單擊“確定”按鈕，得到計(jì)算結(jié)果，然后雙擊該單元格，顯示公式，并將其修改為“=NPER(C3/12,C4,C2,C5,1)/12”，按【Ctrl+Enter】組合鍵，計(jì)算的結(jié)果如下圖所示。修改NPER函數(shù)基于固定利率及等額分期付款方式，返回某項(xiàng)投資的總期數(shù)。例15 使用RATE函數(shù)計(jì)算假設(shè)5年前向某公司投入資金10萬元作為入股資金，投入后每年年終可獲分紅6000元，現(xiàn)在公司一次性支付40萬元，購買這些股份，要計(jì)算這筆資金的年利率，可使用RATE函數(shù)來計(jì)算，其具體的計(jì)算方法如下： 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格C7，從中輸入函數(shù)“=RATE(”，如下圖所示。 按【shift+F3】組合鍵，彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的參數(shù)，如下圖所示。2.選擇1.選擇設(shè)置輸入 單擊“確定”按鈕，得到年利率，如下圖所示。RATE函數(shù)用于返回年金的各期利率。RATE函數(shù)的語法結(jié)構(gòu)為RATE(nper,pmt,pv,fv,type,guess)，其中, Guess為預(yù)期利率，如果省略，則默認(rèn)為10%。例16 用IRR函數(shù)計(jì)算內(nèi)部收益率IRR函數(shù)用于返回由數(shù)值代表的一組定期現(xiàn)金流的內(nèi)部收益率。這些現(xiàn)金流必須按固定的間隔產(chǎn)生，如按月或按年。內(nèi)部收益率為投資的回收利率，其中包含定期支付（負(fù)值）和定期收入（正值）。使用該函數(shù)計(jì)算內(nèi)部收益率的具體方法如下： 新建工作表，從中輸入數(shù)據(jù)，然后選擇單元格C14，按【Shift+F3】組合鍵，彈出“插入函數(shù)”對(duì)話框，從中選擇IRR函數(shù)，如下圖所示。設(shè)置 單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置函數(shù)的參數(shù)，如下圖所示。輸入 單擊“確定”按鈕，得到投資三年后的內(nèi)部收益率，然后在單元格C15中輸入函數(shù)“=IRR(C5:C10)”，如下圖所示。 按【Ctrl+Enter】組合鍵，計(jì)算出投資五年后的內(nèi)部收益率，如下圖所示。例17 用XIRR函數(shù)計(jì)算內(nèi)部收益率XIRR函數(shù)返回一組不定期發(fā)生的現(xiàn)金流的內(nèi)部收益率。使用該函數(shù)計(jì)算內(nèi)部收益率的具體操作方法如下：選擇2.選擇1.輸入設(shè)置輸入 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格C12，從中輸入函數(shù)“=XIRR(”，如下圖所示。 按【shift+F3】組合鍵，彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的參數(shù)，如下圖所示。 單擊“確定”按鈕，得到計(jì)算結(jié)果，然后在“開始”選項(xiàng)卡下“數(shù)字”組中設(shè)置該單元格的數(shù)字格式為“百分比”，如下圖所示。例18 用MIRR函數(shù)計(jì)算內(nèi)部收益率假設(shè)投資某個(gè)項(xiàng)目，所需資金30萬元，從銀行的貸款利率是4.5%，投資的收益是8.9%，其具體的計(jì)算方法如下： 新建工作表，從中相關(guān)的輸入數(shù)據(jù)，然后選擇單元格E6，如下圖所示。 按【shift+F3】組合鍵，彈出“插入函數(shù)”對(duì)話框，從中選擇MIRR函數(shù)，如下圖所示。MIRR函數(shù)返回某一連續(xù)期間內(nèi)現(xiàn)金流的修正內(nèi)部收益率。該函數(shù)計(jì)算充分考慮了投資的成本和現(xiàn)金再投資的收益率。該函數(shù)的語法結(jié)構(gòu)為MIRR(values,finance_rate,reinvest_rate)，其中，Values為一個(gè)數(shù)組或?qū)Π瑪?shù)字的單元格的引用。這些數(shù)值中包括支出（負(fù)值）及收入（正值）；Finance_rate為現(xiàn)金流中使用的資金支付的利率；Reinvest_rate為將現(xiàn)金流再投資的收益率。設(shè)置設(shè)置 單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，從中輸入各相關(guān)參數(shù)，如下圖所示。 單擊“確定”按鈕，得到投資一年的修正內(nèi)部收益率，然后向下拖動(dòng)填充柄，填充公式，結(jié)果如下圖所示。在7年內(nèi)，投資項(xiàng)目的內(nèi)部收益率都為負(fù)，所以該投資不可行。例19 用NPV函數(shù)計(jì)算凈現(xiàn)值假設(shè)一年前投資5000元，在本年的年末凈收入1200元，在第二年的年末凈收入達(dá)到5000元，第三年的年末凈收入達(dá)到15000元，折現(xiàn)率8%，要計(jì)算其凈現(xiàn)值，可使用NPV函數(shù)計(jì)算，其具體操作方法如下： 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格C11，從中輸入函數(shù)“=NPV(”，如下圖所示。輸入 按【shift+F3】組合鍵，彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的參數(shù)，如下圖所示。 單擊“確定”按鈕，輸入公式，然后雙擊該單元格，將公式修改為“=NPV(C9,C6,C7,C8)+C5”得到凈現(xiàn)值，如下圖所示。NPV函數(shù)通過使用貼現(xiàn)率以及一系列未來支出（投資額）和收入額，返回一項(xiàng)投資的凈現(xiàn)值。例20 計(jì)算期初投資的凈現(xiàn)值假設(shè)第一年初投資5000元，第一年年末凈收入1000元，第二年年末凈收入6000元，第三年年末的凈收入為120 00元，折現(xiàn)率10%，要計(jì)算其凈現(xiàn)值，可按如下方法操作： 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格C11，從中輸入函數(shù)“=NPV(”，如下圖所示。輸入修改輸入設(shè)置 按【shift+F3】組合鍵，彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的參數(shù)，如下圖所示。Value1, value2, .在時(shí)間上必須具有相等間隔，并且都發(fā)生在期末。 單擊“確定”按鈕，輸入公式，然后雙擊該單元格，將公式修改為“=NPV(C9,C6,C7,C8)+C5”得到凈現(xiàn)值，如下圖所示。Rate為貼現(xiàn)率，通常為必要收益率或加權(quán)資本成本；NPV函數(shù)的參數(shù)為每年末凈現(xiàn)金流量，所以在該實(shí)例中不能直接將“年初的投資額”作為NPV函數(shù)的參數(shù)。例21 用XNPV函數(shù)計(jì)算凈現(xiàn)值XNPV函數(shù)用于返回一組不定期發(fā)生的現(xiàn)金流的凈現(xiàn)值，使用該函數(shù)計(jì)算凈現(xiàn)值的具體操作方法如下： 新建工作表，從中輸入數(shù)據(jù)，然后單擊單元格C12，從中輸入函數(shù)“=XNPV(”，如下圖所示。設(shè)置 按【shift+F3】組合鍵，彈出“函數(shù)參數(shù)”對(duì)話框，從中設(shè)置相應(yīng)的參數(shù)，如下圖所示。 單擊“確定”按鈕，得到不定期現(xiàn)金流的凈現(xiàn)值，結(jié)果如下圖所示。6.4 文本函數(shù)實(shí)戰(zhàn)技巧例1 從身份證中提取出生日期拖動(dòng)2.輸入設(shè)備輸入1.輸入設(shè)備使用MID函數(shù)能夠從身份證中提取出生日期，具體操作方法如下： 新建工作表，在單元格B2中輸入身份證號(hào)，在單元格C2中輸入函數(shù)，如下圖所示。MID函數(shù)用于返回文本字符串中從指定位置開始的特定數(shù)目的字符，語法為：MID(text,start_num,num_chars)。例如，MID(B2,7,4)是指從單元格B2中第7個(gè)字符開始提取，提取4個(gè)字符。 按【Enter】鍵，得到的結(jié)果如下圖所示。例2 用特殊符號(hào)補(bǔ)充空位在財(cái)務(wù)工作中，通常要將輸入金額中的空位補(bǔ)齊，在Excel中使用函數(shù)可以輕松的辦到，具體操作方法如下。 在單元格中輸入數(shù)據(jù)和函數(shù)，如下圖所示。 按【Ctrl+Enter】組合鍵，得到公式的計(jì)算結(jié)果；然后拖動(dòng)填充并進(jìn)行公式填充，得到的結(jié)果如下圖所示。REPT函數(shù)用來不斷地重復(fù)顯示某一文本字符串，對(duì)單元格進(jìn)行填充。例3 創(chuàng)建文本直方圖使用TEPT函數(shù)還可以創(chuàng)建文本直方圖，具體操作方法如下。 輸入 打開“樓盤概覽”工作表，在單元格I5中輸入函數(shù)“=REPT(“”,INT(H5/100)，如下圖所示。 按【Ctrl+Enter】組合鍵，得到公式的計(jì)算結(jié)果；然后將公式復(fù)制到單元格區(qū)域I6:I12中，得到的結(jié)果如下圖所示。例4 快速統(tǒng)計(jì)單詞個(gè)數(shù)如果單元格中的字符是英文單詞，因?yàn)橛⑽闹械拿總€(gè)單詞由不同的字母組成，因此不能簡(jiǎn)單地用LEN函數(shù)來統(tǒng)計(jì)，這時(shí)可用如下的方法來統(tǒng)計(jì)單詞的個(gè)數(shù)。2.輸入1.輸入2.輸入1.輸入 新建一張工作表，在單元格B16中輸入單詞，在單元格B18中輸入公式“=LEN(B16)-LEN(SUBSTITUTE(B16,” ”,”)+1”，其中SUBSTITUTE函數(shù)用于將單元格B6中的空格替換為空,也就是刪除空格，LEN函數(shù)用于計(jì)算字符數(shù)，兩個(gè)LEN函數(shù)求得的字符數(shù)相減，即為空格數(shù)，空格數(shù)再加1，即為空格隔開的單詞數(shù)，如下圖所示。輸入 按【Enter】鍵，得出計(jì)算結(jié)果，如下圖所示。LEN函數(shù)用于返回文本字符串中的字符數(shù)，是Excel中較常用的函數(shù)。例5 快速統(tǒng)計(jì)字符出現(xiàn)的次數(shù)在工作中，有時(shí)需要統(tǒng)計(jì)單元格中出現(xiàn)某以相同的字符的次數(shù)，這時(shí)，也可應(yīng)用LEN函數(shù)。其具體操作方法如下： 新建工作表，在其中輸入文本內(nèi)容，然后在單元格B18中輸入公式“=（LEN(B16)-LEN(SUBSTITUTE(B16),”風(fēng)尚”，”)）/2”，該公式是用于計(jì)算單元格B16中“風(fēng)尚”的個(gè)數(shù)，如下圖所示。 按【Ctrl+Enter】組合鍵，得到計(jì)算的結(jié)果，如下圖所示。例6 用嵌套函數(shù)限定產(chǎn)品的價(jià)格使用SUM、N和FREQUENCY嵌套函數(shù)也可以限定產(chǎn)品的價(jià)格，下面仍以上個(gè)技巧中的實(shí)例為例進(jìn)行介紹。 在單元格C3中輸入公式“=MIN(19.8,MAX(12.1,D4)”，如下圖所示， 拖動(dòng)輸入輸入 按【Ctrl+Enter】組合鍵，得到公式計(jì)算結(jié)果，如下圖所示。使用MIN和MAX函數(shù)的嵌套，通過將價(jià)格與最大值、最小值的比較來實(shí)現(xiàn)限價(jià)。例7 巧妙生成工資條打印工資條是現(xiàn)代辦公中經(jīng)常遇到的問題，在Excel中使用函數(shù)也可以自動(dòng)創(chuàng)建工資條，然后打印出來即可。其操作方法如下： 在Excel中，創(chuàng)建“公司員工工資表”，如下圖所示。 選擇單元格B19，并在其中輸入公式“=IF(MOD(ROW(C3)-2,3),OFFSET($B$3,(MOD(ROW(C3)-3,3)0)*ROUND(ROW(C3)-2)/3,),COLUMN(C3)-2),)”如下圖所示。 按【Ctrl+Enter】組合鍵，得到公式的計(jì)算結(jié)果，然后向右拖動(dòng)填充柄，再向下拖動(dòng)填充柄，得到完整的工資條，如下圖所示。MOD(ROW(C3)-3,3)0)*ROUND(ROW(C3)-2)/3,)的含義是將MOD函數(shù)求得的余數(shù)和0進(jìn)行比較，如果邏輯值為FALSE，不管ROUND函數(shù)求得的數(shù)值為多少，它們相乘的結(jié)果始終為零，這樣就保證了OFFSET函數(shù)所返回的行數(shù)不變，即顯示列標(biāo)題。如果MOD函數(shù)求得余數(shù)不為零，則和0進(jìn)行比較后，得到邏輯值TRUE,與ROUND函數(shù)相乘后，得到下移行數(shù)，從而通過OFFSET函數(shù)顯示從單元格B3開始向下移一定行數(shù)后的單元格數(shù)據(jù)。例8 巧妙使用循環(huán)引用記錄時(shí)間顯示3.輸入1.選擇填充輸入設(shè)置如果能夠在工作表中記錄數(shù)據(jù)輸入的時(shí)準(zhǔn)確時(shí)間，將給用戶的工作帶來很大的好處。在Excel中使用函數(shù)能夠輕松實(shí)現(xiàn)用戶的要求，其具體操作步驟如下： 新建工作表，從中輸入數(shù)據(jù)，將“時(shí)間”列的數(shù)字公式設(shè)置為“時(shí)間”，然后在可修改“時(shí)間”的數(shù)字格式為“常規(guī)”，如下圖所示。 在單元格C3中輸入公式“=IF(ISBLANK(B3),IF(C3=,NOW(),C3)”，如下圖所示。要正確得出輸入的時(shí)間，應(yīng)該先輸入并復(fù)制公式，然后在“收入”列中輸入數(shù)據(jù)。 確認(rèn)公式后，使用填充柄填充公式，如下圖所示。2.選中 打開“Excel選項(xiàng)”對(duì)話框，在左側(cè)的列表中選擇“公式”選項(xiàng)，在右側(cè)的“計(jì)算選項(xiàng)”選項(xiàng)區(qū)中選中“啟用迭代計(jì)算”復(fù)選框，并將“最多迭代次數(shù)”設(shè)置為1，如下圖所示。 在“收入”列中輸入收入額，在“時(shí)間”列的相應(yīng)單元格中將顯示輸入時(shí)間，如下圖所示。例9 巧用公式屏蔽出錯(cuò)信息在填充公式時(shí)，有時(shí)候可能會(huì)因?yàn)楣街幸玫膯卧裥畔⒏淖?，?dǎo)致其不滿足公式的參數(shù)定義等原因，導(dǎo)致公式計(jì)算結(jié)果錯(cuò)誤，例如，在“合格產(chǎn)品統(tǒng)計(jì)”工作表中，由于要統(tǒng)計(jì)合格產(chǎn)品的個(gè)數(shù)，所以使用了公式“=C4*E4”，在產(chǎn)品合格率未檢測(cè)出來時(shí)，使用該公式計(jì)算，就會(huì)顯示錯(cuò)誤信息，如下圖所示。輸入輸入要避免這種情況的發(fā)生，可以通過對(duì)公式進(jìn)行完善，巧妙地解決單元格中顯示錯(cuò)誤的公式計(jì)算結(jié)果，其具體操作方法如下： 在單元格F4中輸入公式“=IF(ISNUMBER(C4*E4),C4*E4,無法計(jì)算)”，如下圖所示。顯示輸入 得到計(jì)算結(jié)果后，拖動(dòng)填充柄復(fù)制公式，單元格F7中將不會(huì)得到錯(cuò)誤信息，效果如下圖所示。 在單元格C9中輸入公式“=COS(B9*PI()/180”或者“COS(RADIANS(B9)”，都將求得角度為30的余弦，如下圖所示。COS函數(shù)的語法結(jié)構(gòu)為：COS(number)。Number為需要求余弦的角度，以弧度表示。如果角度是以度表示的，則可將其乘以PI()/180或使用RADIANS函數(shù)將其轉(zhuǎn)換成弧度。6.5 統(tǒng)計(jì)函數(shù)實(shí)戰(zhàn)技巧例1 計(jì)算某個(gè)單元格值出現(xiàn)的次數(shù)用COUNTIF函數(shù)可以計(jì)算單元格區(qū)域中滿足給定條件的單元格的個(gè)數(shù)，下面以求“GHU系列產(chǎn)品種類”為例，介紹該函數(shù)的使用方法： 打開“商品價(jià)格表”，從中輸入數(shù)據(jù)，然后在單元格F21中輸入公式“=COUNTIF(F13:F19,GHU)”，如下圖所示。輸入 按【Ctrl+Enter】組合鍵，得到公式計(jì)算結(jié)果，如下圖所示。例2 求滿足多個(gè)條件的單元格個(gè)數(shù)顯示求滿足多個(gè)條件的單元格個(gè)數(shù)，最常用的方法是使用COUNT和IF函數(shù)嵌套來實(shí)現(xiàn)。下面以計(jì)算“員工考核成績(jī)表”中各科成績(jī)都在75分以上的員工人數(shù)為例，介紹其具體方法。 新建工作表，從中輸入數(shù)據(jù)，然后在單元格E12中輸入公式“=COUNT(IF(C4:C1075)*(D4:D1075)*(E4:E1075),B4:E10)”，如下圖所示。輸入 按【Ctrl+shift+Enter】組合鍵，得到公式計(jì)算結(jié)果，計(jì)算出滿足條件的單元格有3個(gè)，如下圖所示。該實(shí)例中的IF條件句用于返回各科成績(jī)均大于75分的單元格區(qū)域。再使用COUNT函數(shù)計(jì)算數(shù)值結(jié)果的個(gè)數(shù)。例3 巧用COUNTIFS函數(shù)求個(gè)數(shù)COUNTIFS函數(shù)是Excel 2007中新增的函數(shù)，該函數(shù)也可用于計(jì)算某個(gè)單元格區(qū)域中滿足多重條件的單元格數(shù)目。其具體操作方法如下： 打開“員工考核成績(jī)表”，在單元格D5中輸入公式“=COUNTIFS(C4:C10,75,D4:D10,75,E4:E10,75)”，如下圖所示。 按【Ctrl+Enter】組合鍵，將得到計(jì)算的結(jié)果，統(tǒng)計(jì)出滿足條件的人數(shù)有1人，如下圖所示。使用COUNTIFS函數(shù)時(shí)，只有當(dāng)單元格區(qū)域中的數(shù)據(jù)滿足為其指定的所有相應(yīng)條件時(shí)才對(duì)其進(jìn)行計(jì)算。例4 對(duì)考核成績(jī)實(shí)現(xiàn)同分同名次排列在用Excel統(tǒng)計(jì)成績(jī)時(shí)，根據(jù)成績(jī)高低進(jìn)行排序，按序列自動(dòng)填充出名次。當(dāng)成績(jī)相同時(shí)，填充出名次還是不同的，如下圖所示。此時(shí)，可以利用使用RANK排位函數(shù)實(shí)現(xiàn)同分同名次的操作，具體操作方法如下： 打開“員工考核成績(jī)表”，添加“總分”和“排名”列，在單元格G4中輸入函數(shù)“=RANK(F4,F$4: F$10,0)”，如下圖所示。填充輸入輸入 按【Ctrl+Enter】組合鍵后，會(huì)得到該行總成績(jī)的分?jǐn)?shù)排名。使用拖動(dòng)填充柄的方法，向下填充公式即可排名，效果如下圖所示。例5 用COUNTIF 函數(shù)也求名次排列使用COUNTIF計(jì)數(shù)函數(shù)也可以對(duì)考核成績(jī)實(shí)現(xiàn)同分同名次排列，具體操作方法如下： 在G4單元格中輸入函數(shù)“=COUNTIF(F$4:F$10,&F4)+1”，如下圖所示。輸入 按【Ctrl+Enter】組合鍵，顯示名次。然后通過拖動(dòng)該單元格的填充柄向下填充即可實(shí)現(xiàn)排名，如下圖所示。例6 實(shí)現(xiàn)同分同名次排列的嵌套函數(shù)采用IF函數(shù)和COUNT函數(shù)的嵌套照樣能夠?qū)崿F(xiàn)同分同名次排列的嵌套，具體操作方法如下： 在單元格G4中輸入“1”，然后在G5單元格中輸入函數(shù)“=IF(F5=F4,G4, COUNT($F$4:F5)”，如下圖所示。輸入輸入輸入 按【Ctrl+Enter】組合鍵，該單元格中顯示排名。拖動(dòng)該單元格中的填充柄向下填充公式，得到排名結(jié)果，如下圖所示。對(duì)于前兩種方法，在沒有將總分進(jìn)行排序的情況下，也可以得出正確的排名；而最后一種方法只有在對(duì)總分進(jìn)行降序排序時(shí)才能得出正確的排名結(jié)果。例7 用FREQUENCY函數(shù)求頻率填充FREQUENCY頻率函數(shù)用于計(jì)算數(shù)值在某個(gè)單元格區(qū)域內(nèi)的出現(xiàn)頻率，然后返回一個(gè)垂直數(shù)組。例如，計(jì)算不同的分段中考核成績(jī)的個(gè)數(shù)，其具體使用方法如下：輸入 新建工作表，從中輸入姓名和成績(jī)兩列數(shù)據(jù)，如下圖所示。輸入 在工作表中添加“成績(jī)分段”和“各分段中成績(jī)的個(gè)數(shù)”列，并在“分段”列中輸入幾個(gè)分段，表示考核成績(jī)劃分為=60、6170、7180、8190、90分以上五個(gè)區(qū)域，如下圖所示。 選擇單元格區(qū)域D3:D7，然后按【F2】鍵輸入公