第7章應(yīng)用函數(shù)計(jì)算數(shù)據(jù)

1、Excel數(shù)據(jù)處理與分析實(shí)例教程（第3版）教案第7章 應(yīng)用函數(shù)計(jì)算數(shù)據(jù)教學(xué)內(nèi)容1. 函數(shù)的輸入與編輯2. 數(shù)學(xué)的函數(shù)和統(tǒng)計(jì)函數(shù)3. 日期與時(shí)間函數(shù)4. 邏輯函數(shù)5. 文本函數(shù)6. 查找與引用函數(shù)7. 財(cái)務(wù)函數(shù)教學(xué)要求【知識(shí)目標(biāo)】1. 掌握函數(shù)的概念2. 了解函數(shù)的組成【技能目標(biāo)】1. 掌握輸入函數(shù)的方法2. 能使用日期和時(shí)間函數(shù)對(duì)時(shí)間和日期進(jìn)行計(jì)算3. 能使用數(shù)學(xué)函數(shù)進(jìn)行計(jì)算4. 能使用統(tǒng)計(jì)函數(shù)進(jìn)行統(tǒng)計(jì)計(jì)算5. 能使用文本函數(shù)進(jìn)行字符串的處理6. 能使用查找與引用函數(shù)進(jìn)行數(shù)據(jù)的查找7. 能使用邏輯函數(shù)進(jìn)行數(shù)據(jù)的判斷教學(xué)重點(diǎn)1. 函數(shù)的概念2. 函數(shù)的輸入與使用教學(xué)難點(diǎn)1. 函數(shù)的應(yīng)用（選擇合適

2、的函數(shù)進(jìn)行計(jì)算）教學(xué)方法講授法、案例法、演示法課時(shí)數(shù)7課時(shí)（講授4課時(shí)，實(shí)驗(yàn)3課時(shí)）課程導(dǎo)入【思路】導(dǎo)入實(shí)例，提出問(wèn)題，組織學(xué)生討論。通過(guò)討論指出Excel 2016提供了很多函數(shù)可以進(jìn)行各種計(jì)算，并引出第7章要介紹的函數(shù)計(jì)算問(wèn)題?！緦?shí)例】計(jì)算工資項(xiàng)（7.4實(shí)例）計(jì)算銷售業(yè)績(jī)獎(jiǎng)金（7.5實(shí)例）【問(wèn)題】1. 常見(jiàn)的數(shù)學(xué)計(jì)算有哪些？2. 常見(jiàn)的統(tǒng)計(jì)計(jì)算有哪些？ 3. 在Excel中， 可以使用函數(shù)嗎？7.1 認(rèn)識(shí)函數(shù)公式是對(duì)工作表中數(shù)據(jù)進(jìn)行計(jì)算和操作的等式，函數(shù)則是一些預(yù)先編寫的、按特定順序或結(jié)構(gòu)執(zhí)行計(jì)算的特殊公式。Excel函數(shù)是由Excel內(nèi)部預(yù)先定義并按照特定順序和結(jié)構(gòu)來(lái)執(zhí)行計(jì)算、分析等數(shù)據(jù)

3、處理任務(wù)的功能模塊，也被稱為“特殊公式”。與公式一樣，函數(shù)的最終返回結(jié)果也是值。7.1.1 函數(shù)的結(jié)構(gòu)每個(gè)函數(shù)都有相同的結(jié)構(gòu)形式，其格式為：函數(shù)名(參數(shù)1, 參數(shù)2,)，如IF(D2)>0,30,50)。其中，函數(shù)名即函數(shù)的名稱，每個(gè)函數(shù)名唯一標(biāo)識(shí)一個(gè)函數(shù)；參數(shù)是函數(shù)的輸入值，用來(lái)計(jì)算所需的數(shù)據(jù)，參數(shù)可以是數(shù)字、文本、表達(dá)式、單元格引用、區(qū)域名稱、邏輯值，或者是其他函數(shù)。有些函數(shù)不帶參數(shù)，如NOW()函數(shù)、TODAY()函數(shù)等。7.1.2 函數(shù)的種類根據(jù)應(yīng)用領(lǐng)域的不同，Excel函數(shù)分為財(cái)務(wù)函數(shù)、日期與時(shí)間函數(shù)、數(shù)學(xué)與三角函數(shù)函數(shù)、統(tǒng)計(jì)函數(shù)、查找與引用函數(shù)、數(shù)據(jù)庫(kù)函數(shù)、文本函數(shù)、邏輯函數(shù)

4、、信息函數(shù)、工程函數(shù)等。功能區(qū)位于標(biāo)題欄下方，由一組選項(xiàng)卡組成，包括“文件”“開(kāi)始”“插入”“頁(yè)面布局”“公式”“數(shù)據(jù)”“審閱”和“視圖”等。每個(gè)選項(xiàng)卡包含多個(gè)命令組，每個(gè)命令組由一些功能相關(guān)的命令組成。7.2 輸入函數(shù)函數(shù)作為公式來(lái)使用，輸入時(shí)應(yīng)以等號(hào)“=”開(kāi)始，后面是函數(shù)。輸入函數(shù)有3種方法：使用“插入函數(shù)”對(duì)話框輸入、使用“自動(dòng)求和”按鈕輸入和手工直接輸入。若在公式中使用了函數(shù)，而且函數(shù)中的某個(gè)參數(shù)又是一個(gè)函數(shù)，則可以使用函數(shù)的輸入方法進(jìn)行函數(shù)的嵌套輸入。7.2.1 使用“插入函數(shù)”對(duì)話框輸入對(duì)于比較復(fù)雜的函數(shù)或者參數(shù)較多的函數(shù)，可以使用“插入函數(shù)”對(duì)話框輸入。7.2.2 使用“自動(dòng)求和

5、”按鈕輸入 為了方便用戶使用，Excel在“開(kāi)始”選項(xiàng)卡的“編輯”命令組中設(shè)置了一個(gè)“自動(dòng)求和”按鈕 ，單擊該按鈕可以自動(dòng)添加求和函數(shù)；單擊該按鈕右側(cè)的下拉箭頭，會(huì)出現(xiàn)一個(gè)下拉菜單，單擊前5個(gè)選項(xiàng)中的任意一個(gè)，Excel會(huì)非常智能地識(shí)別出待統(tǒng)計(jì)的單元格區(qū)域，并將單元格區(qū)域地址自動(dòng)加到函數(shù)的參數(shù)中，方便了用戶輸入。7.2.3 手工直接輸入如果熟練地掌握了函數(shù)的格式，可以在單元格中直接輸入函數(shù)。當(dāng)依次輸入了等號(hào)、函數(shù)名和左括號(hào)后，系統(tǒng)會(huì)自動(dòng)出現(xiàn)當(dāng)前函數(shù)語(yǔ)法結(jié)構(gòu)的提示信息。如果希望進(jìn)一步了解函數(shù)信息，按【Shift】+【F3】組合鍵，可以打開(kāi)“插入函數(shù)”對(duì)話框。7.2.4 函數(shù)的嵌套輸入當(dāng)處理的問(wèn)題

6、比較復(fù)雜時(shí)，計(jì)算數(shù)據(jù)的公式往往需要使用函數(shù)嵌套。7.3 常用函數(shù)Excel提供了豐富的函數(shù)，可以完成絕大多數(shù)日常工作中的數(shù)據(jù)處理工作。但因篇幅所限，本節(jié)只介紹數(shù)學(xué)函數(shù)、統(tǒng)計(jì)函數(shù)、日期函數(shù)、邏輯函數(shù)、文本函數(shù)、查找與引用函數(shù)、財(cái)務(wù)函數(shù)中一些常用的函數(shù)。7.3.1 數(shù)學(xué)函數(shù)Excel提供了很多數(shù)學(xué)函數(shù)，下面簡(jiǎn)單介紹其中幾種常用的數(shù)學(xué)函數(shù)。1.3 7.3.2 統(tǒng)計(jì)函數(shù)Excel提供了很多統(tǒng)計(jì)函數(shù)，有些可以統(tǒng)計(jì)選定單元格區(qū)域數(shù)據(jù)個(gè)數(shù)，有些可以確定數(shù)字在某組數(shù)字中的排位，還有些可以根據(jù)條件進(jìn)行相關(guān)統(tǒng)計(jì)，這些函數(shù)在實(shí)際應(yīng)用中都非常有用。下面簡(jiǎn)單介紹幾種常用的統(tǒng)計(jì)函數(shù)。7.3.3 日期函數(shù)與日期或時(shí)間有關(guān)的

7、處理可以使用日期與時(shí)間函數(shù)。下面簡(jiǎn)單介紹幾種常用的日期與時(shí)間函數(shù)。7.47.3.4 邏輯函數(shù)Excel提供了6種邏輯函數(shù)，包括與、或、非、真、假和條件判斷。真和假這2個(gè)邏輯函數(shù)沒(méi)有參數(shù)，與、或、非邏輯函數(shù)的參數(shù)均為邏輯值，條件判斷函數(shù)的第1個(gè)參數(shù)為邏輯值。當(dāng)需要進(jìn)行條件判斷時(shí)，可以使用此類函數(shù)。7.3.5 文本函數(shù)與文本有關(guān)的處理可以使用文本函數(shù)。例如，計(jì)算文本的長(zhǎng)度、從文本中提取子字符串、大小寫字母轉(zhuǎn)換、數(shù)字與文本的轉(zhuǎn)換等。7.3.6 查找與引用函數(shù)HLOOKUP函數(shù)、MATCH函數(shù)、VLOOKUP函數(shù)、INDIRECT函數(shù)和ROW函數(shù)都屬于查找與引用函數(shù)，是在查找數(shù)據(jù)時(shí)使用頻率非常高的函數(shù)

8、。通常可以滿足簡(jiǎn)單的查詢需求。例如，根據(jù)學(xué)號(hào)查詢學(xué)生考試的總成績(jī)，根據(jù)產(chǎn)品名稱查詢產(chǎn)品價(jià)格等。重點(diǎn)介紹HLOOKUP函數(shù)和VLOOKUP函數(shù)，并進(jìn)行比較。7.3.7 財(cái)務(wù)函數(shù)財(cái)務(wù)函數(shù)專門用于財(cái)務(wù)計(jì)算，使用這些函數(shù)可以直接得到結(jié)果。7.4 應(yīng)用實(shí)例計(jì)算工資項(xiàng)在第6章的應(yīng)用實(shí)例中，已經(jīng)通過(guò)公式計(jì)算出“工資計(jì)算”表中的部分工資項(xiàng)，但還有些算法相對(duì)復(fù)雜的工資項(xiàng)并沒(méi)有計(jì)算出來(lái)，需要使用函數(shù)。為了便于讀者學(xué)習(xí)，以下按計(jì)算公式難易程度的順序介紹工資項(xiàng)的計(jì)算方法。7.4.1 計(jì)算洗理費(fèi)和書報(bào)費(fèi)假設(shè)該公司規(guī)定，洗理費(fèi)標(biāo)準(zhǔn)按男員工每人每月30元，女員工每人每月50元發(fā)放。也就是說(shuō)“工資計(jì)算”工作表中某名員工的洗理

9、費(fèi)數(shù)據(jù)項(xiàng)的計(jì)算，需要根據(jù)該員工的性別來(lái)決定?？梢允褂肐F函數(shù)進(jìn)行處理。計(jì)算公式為：=IF(人員清單!C2="男", 30,50)7.4.2 計(jì)算工齡補(bǔ)貼一般單位的工資構(gòu)成都包括工齡補(bǔ)貼。假設(shè)該公司工齡補(bǔ)貼的計(jì)算方法是每滿一年增加10元，但是最高不超過(guò)300元。工齡工資的計(jì)算比較復(fù)雜，需要用到多個(gè)函數(shù)，而且函數(shù)需要嵌套使用。原因是“人員清單”工作表中只有“參加工作日期”數(shù)據(jù)，而沒(méi)有現(xiàn)成的工齡數(shù)據(jù)，因此需要先計(jì)算出員工參加工作年份到計(jì)算工資年份的工齡。這里可以利用YEAR函數(shù)和TODAY函數(shù)進(jìn)行計(jì)算。計(jì)算公式為：=MIN(300,(YEAR(TODAY()-YEAR(人員清單!

10、G2)*10)7.4.3 計(jì)算所得稅計(jì)算個(gè)人所得稅是工資管理中不可缺少的一項(xiàng)工作。按照我國(guó)現(xiàn)行稅收制度，不同收入水平的納稅人其納稅稅率不同。個(gè)人所得稅計(jì)算方法為：應(yīng)納稅所得額Í稅率-速算扣除數(shù)。稅率及速算扣除數(shù)如表7-1所示。表7 -1稅率及速算扣除數(shù)級(jí) 數(shù)應(yīng)納稅所得額免征額 稅率 速算扣除數(shù)0 15000 5000 0 01 50018000 5000 3% 02 800117000 5000 10% 2103 17001無(wú)限 5000 20% 1410所得稅的計(jì)算也需要使用IF函數(shù)，但是比計(jì)算洗理費(fèi)要復(fù)雜得多，需要多個(gè)IF函數(shù)嵌套使用。計(jì)算公式為：=IF(R2<5000,0

11、,IF(R2<8000,R2*0.03,IF(R2<17000,R2*0.1-210,R2*0.2-1410)7.5 應(yīng)用實(shí)例計(jì)算銷售業(yè)績(jī)獎(jiǎng)金在第6章的應(yīng)用實(shí)例中，已經(jīng)通過(guò)公式計(jì)算出“2月業(yè)績(jī)獎(jiǎng)金表”中的總獎(jiǎng)金和累計(jì)銷售額2項(xiàng)數(shù)據(jù)，但還有些算法相對(duì)復(fù)雜的數(shù)據(jù)項(xiàng)沒(méi)有計(jì)算出來(lái)，需要使用函數(shù)。本節(jié)將根據(jù)“1月業(yè)績(jī)獎(jiǎng)金表”“銷售情況表”和“獎(jiǎng)金標(biāo)準(zhǔn)”3張工作表計(jì)算2月業(yè)務(wù)員的銷售業(yè)績(jī)獎(jiǎng)金。7.5.1 銷售業(yè)績(jī)獎(jiǎng)金計(jì)算方法簡(jiǎn)介業(yè)務(wù)員的銷售業(yè)績(jī)獎(jiǎng)金一般是根據(jù)獎(jiǎng)金發(fā)放標(biāo)準(zhǔn)計(jì)算出來(lái)的。不同的企業(yè)，獎(jiǎng)金發(fā)放標(biāo)準(zhǔn)有所不同。有些是從業(yè)績(jī)金額中按照固定比例計(jì)算獎(jiǎng)金，有些是從業(yè)務(wù)金額中抽取固定金額作為獎(jiǎng)金，

12、還有些是根據(jù)業(yè)績(jī)金額按照不同比例計(jì)算獎(jiǎng)金。假設(shè)該公司獎(jiǎng)金由基本獎(jiǎng)金和獎(jiǎng)勵(lì)獎(jiǎng)金構(gòu)成。獎(jiǎng)金計(jì)算方法如下。1基本獎(jiǎng)金標(biāo)準(zhǔn)及算法每月基本獎(jiǎng)金按不同百分比計(jì)算。銷售額越高，獎(jiǎng)金比例也越高。2獎(jiǎng)勵(lì)獎(jiǎng)金標(biāo)準(zhǔn)及算法業(yè)務(wù)員除了可以得到基本獎(jiǎng)金外，還可以獲得獎(jiǎng)勵(lì)獎(jiǎng)金。獎(jiǎng)勵(lì)獎(jiǎng)金是根據(jù)累計(jì)銷售業(yè)績(jī)計(jì)算的。當(dāng)累計(jì)銷售業(yè)績(jī)達(dá)到5萬(wàn)元時(shí)，將發(fā)放1000元獎(jiǎng)勵(lì)獎(jiǎng)金。為了不重復(fù)發(fā)放獎(jiǎng)金，公司規(guī)定獎(jiǎng)勵(lì)獎(jiǎng)金發(fā)放后，需從累計(jì)銷售業(yè)績(jī)中扣除5萬(wàn)元，剩余金額累計(jì)到下一個(gè)月的累計(jì)銷售業(yè)績(jī)中。7.5.2 計(jì)算累計(jì)銷售業(yè)績(jī)累計(jì)銷售業(yè)績(jī)需要根據(jù)上一個(gè)月獎(jiǎng)勵(lì)獎(jiǎng)金進(jìn)行計(jì)算，具體方法如下。累計(jì)銷售業(yè)績(jī)計(jì)算比較復(fù)雜，需要用到2個(gè)函數(shù)，而且函數(shù)要嵌套使用

13、。首先需要知道業(yè)務(wù)員在1月得到的獎(jiǎng)勵(lì)獎(jiǎng)金數(shù)額，可以利用VLOOKUP函數(shù)根據(jù)業(yè)務(wù)員姓名來(lái)查找。例如，張濤的數(shù)據(jù)存放在“1月業(yè)績(jī)獎(jiǎng)金表”的第3行，那么查找張濤獎(jiǎng)勵(lì)獎(jiǎng)金的計(jì)算公式為：VLOOKUP(B3,'1月業(yè)績(jī)獎(jiǎng)金表'!$B$3:$H$37,5,0)。其中，第1個(gè)參數(shù)為姓名，第2個(gè)參數(shù)為查找的單元格區(qū)域地址，第3個(gè)參數(shù)為返回值的列號(hào)。由于獎(jiǎng)勵(lì)獎(jiǎng)金位于查找區(qū)域的第5列，因此第3個(gè)參數(shù)值為5。接下來(lái)需要判斷獎(jiǎng)勵(lì)獎(jiǎng)金是否為0，并根據(jù)判斷結(jié)果按上述計(jì)算方法計(jì)算累計(jì)銷售業(yè)績(jī)，這里需要使用IF函數(shù)。計(jì)算公式為：=IF(VLOOKUP(B3,'1月業(yè)績(jī)獎(jiǎng)金表'!$B$3:$H

14、$37,5,0)=0,'1月業(yè)績(jī)獎(jiǎng)金表'!C3+'1月業(yè)績(jī)獎(jiǎng)金表'!E3,'1月業(yè)績(jī)獎(jiǎng)金表'!C3+'1月業(yè)績(jī)獎(jiǎng)金表'!E3-50000)7.5.3 計(jì)算本月銷售業(yè)績(jī)每名業(yè)務(wù)員的銷售業(yè)績(jī)記錄在“銷售情況表”中。2月的銷售業(yè)績(jī)位于該表的第21第32行，其中業(yè)務(wù)員姓名存放在B列，“銷售額”存放在I列。計(jì)算第1名業(yè)務(wù)員“本月銷售業(yè)績(jī)”，可以使用SUMIF函數(shù)，計(jì)算公式為：=SUMIF(銷售情況表!$B$21:$B$32,B3,銷售情況表!$I$21:$I$32)7.5.4 計(jì)算獎(jiǎng)金比例計(jì)算出每名業(yè)務(wù)員本月銷售業(yè)績(jī)后，即可根據(jù)“獎(jiǎng)金標(biāo)準(zhǔn)”

15、和“2月業(yè)績(jī)獎(jiǎng)金表”中的本月銷售業(yè)績(jī)，確定每名業(yè)務(wù)員的獎(jiǎng)金比例。例如，張濤2月銷售業(yè)績(jī)?yōu)?8 500元，對(duì)照獎(jiǎng)金標(biāo)準(zhǔn)可知，張濤的獎(jiǎng)金比例為5%。具體的確定方法是用“2月業(yè)績(jī)獎(jiǎng)金表”中的本月銷售業(yè)績(jī)與“獎(jiǎng)金標(biāo)準(zhǔn)”表進(jìn)行比對(duì)，然后將相應(yīng)的比例值取出。由于這個(gè)操作需要返回同一列中指定行的數(shù)值，因此可以使用HLOOKUP函數(shù)來(lái)實(shí)現(xiàn)。計(jì)算第1 名業(yè)務(wù)員“獎(jiǎng)金百分比”的計(jì)算公式為：=HLOOKUP(E3,獎(jiǎng)金標(biāo)準(zhǔn)!$A$3:$F$4,2)7.5.5 計(jì)算獎(jiǎng)勵(lì)獎(jiǎng)金按照獎(jiǎng)勵(lì)獎(jiǎng)金發(fā)放標(biāo)準(zhǔn)，當(dāng)“累計(jì)銷售業(yè)績(jī)”與“本月銷售業(yè)績(jī)”合計(jì)數(shù)超過(guò)5萬(wàn)元時(shí)，發(fā)放1000元獎(jiǎng)勵(lì)獎(jiǎng)金，此時(shí)可以使用IF函數(shù)計(jì)算每名業(yè)務(wù)員的獎(jiǎng)勵(lì)獎(jiǎng)金

16、。計(jì)算第1名業(yè)務(wù)員“獎(jiǎng)勵(lì)獎(jiǎng)金”的公式為：=IF(C3+E3)>=50000,1000,0)本章主要介紹了函數(shù)的概念和應(yīng)用，包括函數(shù)的結(jié)構(gòu)及分類、函數(shù)的輸入方法、常用函數(shù)的應(yīng)用等。重點(diǎn)是通過(guò)計(jì)算“工資管理”工作簿中的各項(xiàng)工資數(shù)據(jù)和核定“銷售管理”工作簿中業(yè)務(wù)員的銷售業(yè)績(jī)，掌握應(yīng)用Excel函數(shù)完成各種計(jì)算的方法。函數(shù)應(yīng)用是Excel實(shí)現(xiàn)數(shù)據(jù)處理最靈活也是最重要的方面，它可以實(shí)現(xiàn)比較復(fù)雜的數(shù)據(jù)計(jì)算，是學(xué)習(xí)Excel過(guò)程中的難點(diǎn)。一、選擇題（見(jiàn)教材）二、填空題（見(jiàn)教材）三、問(wèn)答題1什么是函數(shù)？函數(shù)的作用是什么？2在輸入函數(shù)時(shí)，如果不了解函數(shù)參數(shù)的含義及用法，應(yīng)該如何做？3不允許在圖7-40所示

17、的報(bào)銷清單表中輸入相同的姓名，應(yīng)該如何做？請(qǐng)簡(jiǎn)單說(shuō)明操作思路。 4假設(shè)每年年末存入銀行30000元，存款的年利率為1.98%，按年計(jì)算復(fù)利，到第5年年末時(shí)全部存款的本利和是多少？5某企業(yè)年貸款1000萬(wàn)元，貸款年利率為6%，從一年后開(kāi)始分5年還清，問(wèn)平均每年還款額應(yīng)該是多少？1對(duì)第6章實(shí)訓(xùn)中所建“工資表”的相關(guān)工資項(xiàng)，按照以下規(guī)則完成計(jì)算。（1）崗位津貼：銷售部為800元，其他部門為1000元。（2）代扣個(gè)稅：假設(shè)以3000元作為個(gè)人收入所得稅起征點(diǎn)。代扣個(gè)稅的計(jì)算方法為“應(yīng)納稅所得額×稅率-速算扣除數(shù)”。其中，稅率及速算扣除數(shù)如表7-2所示。表7 -2稅率及速算扣除數(shù)級(jí)數(shù)應(yīng)納稅所得

18、額稅率速算扣除數(shù)0130003%0130011200010%2102120012500020%1410325001無(wú)限25%26602某公司“效績(jī)獎(jiǎng)金”表如圖7-41所示。請(qǐng)?jiān)诖嘶A(chǔ)上，按照以下要求完成計(jì)算。圖7-41 “績(jī)效獎(jiǎng)金”表（1）計(jì)算評(píng)分、評(píng)定結(jié)果、系數(shù)和績(jī)效獎(jiǎng)金，并將計(jì)算結(jié)果填入相應(yīng)單元格中。其中：評(píng)分=上級(jí)評(píng)分+浮動(dòng)分。評(píng)定結(jié)果和系數(shù)的計(jì)算方法如表7-3所示。表7 -3評(píng)定結(jié)果和系數(shù)的計(jì)算方法評(píng) 分評(píng) 定 結(jié) 果系 數(shù)90優(yōu)秀1.58089良好17079較好0.86069合格0.5<60需要改進(jìn)0績(jī)效獎(jiǎng)金的計(jì)算方法是，如果系數(shù)不為0，績(jī)效獎(jiǎng)金=（基本工資+行政工資）×系數(shù)；否則績(jī)效獎(jiǎng)金為100。（2）計(jì)算總獎(jiǎng)金、平均獎(jiǎng)金和最高獎(jiǎng)金，并將計(jì)算結(jié)果填入工作表的相應(yīng)單元格中。（3）計(jì)算每類評(píng)定結(jié)果的人數(shù)及占總?cè)藬?shù)的比率，并將計(jì)算結(jié)果填入工作