做中學(xué)學(xué)中做-Excel2010案例教程-模塊06函數(shù)的應(yīng)用——制作考試成績(jī)分析表電子課件（高教版）模塊06函數(shù)的應(yīng)用——制作考試成績(jī)分析表你知道嗎？

為了滿足財(cái)務(wù)管理工作中各種數(shù)據(jù)處理的需求，Excel提供了大量的函數(shù)。利用這些函數(shù)不但可以解決許多數(shù)據(jù)處理方面的問(wèn)題，以節(jié)省大量的時(shí)間，而且可以簡(jiǎn)化繁瑣的計(jì)算過(guò)程，使工作變得輕松。應(yīng)用場(chǎng)景：

在考試過(guò)后，成績(jī)

分析師教師的重要工

作，同時(shí)也是十分繁

瑣復(fù)雜的工作，在

Excel中用可以利用

函數(shù)的功能來(lái)輕松完

成這一工作。如圖所

示，是利用Excel2010

的函數(shù)功能制作的

考試成績(jī)分析表。背景知識(shí)

考試是教學(xué)過(guò)程中的一個(gè)重要環(huán)節(jié)，教師的教，目的是為了幫助學(xué)生更好地學(xué)，教學(xué)效果的好壞，主要是從學(xué)生身上反映出來(lái)。對(duì)于教師來(lái)說(shuō)在教學(xué)過(guò)程中可以通過(guò)對(duì)考試結(jié)果的分析，了解學(xué)生對(duì)教材、教法的使用情況，以便調(diào)整教學(xué)內(nèi)容和教學(xué)方法，改進(jìn)教與學(xué)的關(guān)系，以適應(yīng)學(xué)生的特點(diǎn)，滿足學(xué)生的需求。對(duì)于學(xué)生來(lái)說(shuō)學(xué)習(xí)成績(jī)的分析可以對(duì)自己有一個(gè)比較全面清晰的認(rèn)識(shí)，認(rèn)識(shí)到自己的優(yōu)勢(shì)與不足，實(shí)事求是地反思學(xué)習(xí)成敗的原因，有利于改進(jìn)學(xué)習(xí)方法。

在利用考試分?jǐn)?shù)或考試結(jié)果進(jìn)行教育管理時(shí)，決不能將分?jǐn)?shù)絕對(duì)化，不能僅憑考試分?jǐn)?shù)的高低來(lái)評(píng)估教育質(zhì)量及學(xué)生的學(xué)習(xí)情況。應(yīng)對(duì)考試的結(jié)果給予科學(xué)的解釋，解釋分?jǐn)?shù)不僅要有整體觀念，即將個(gè)別學(xué)生的成績(jī)與全體學(xué)生的成績(jī)相比較，以分高下；還要有系統(tǒng)的觀念，即要考慮到學(xué)生原有的基礎(chǔ)水平，判斷該學(xué)生有無(wú)進(jìn)步與提高。設(shè)計(jì)思路

在制作考試成績(jī)分析表的過(guò)程中，主要應(yīng)用到使用函數(shù)來(lái)計(jì)算數(shù)據(jù)，制作考試成績(jī)分析表的基本步驟可分解為：

（1）函數(shù)概述

（2）創(chuàng)建函數(shù)項(xiàng)目任務(wù)6-1函數(shù)概述

在應(yīng)用函數(shù)之前我們首先來(lái)了解一下函數(shù)。動(dòng)手做1了解函數(shù)的定義

在Excel2010中所提的函數(shù)其實(shí)是一些預(yù)定義的公式，它們使用一些稱為參數(shù)的特定數(shù)值，按特定的順序或結(jié)構(gòu)進(jìn)行計(jì)算。用戶可以直接用它們對(duì)某個(gè)區(qū)域內(nèi)的數(shù)值進(jìn)行一系列運(yùn)算，如分析和處理日期值和時(shí)間值、確定貸款的支付額、確定單元格中的數(shù)據(jù)類型、計(jì)算平均值、排序顯示和運(yùn)算文本數(shù)據(jù)等等。例如，SUM函數(shù)將對(duì)單元格或單元格區(qū)域中的數(shù)據(jù)進(jìn)行加法運(yùn)算。動(dòng)手做2了解函數(shù)的基本語(yǔ)法Excel2010函數(shù)由三部分組成，即函數(shù)名稱，括號(hào)和參數(shù)，其結(jié)構(gòu)為以等號(hào)“＝”開(kāi)始，后面緊跟函數(shù)名稱和左括號(hào)，然后以逗號(hào)分隔輸入?yún)?shù)，最后是右括號(hào)。其語(yǔ)法結(jié)構(gòu)為：函數(shù)名稱（參數(shù)1，參數(shù)2，……參數(shù)N）。動(dòng)手做3了解函數(shù)的分類

在Excel2010中，系統(tǒng)提供了12類函數(shù)，這些函數(shù)按功能來(lái)說(shuō)分別為數(shù)據(jù)庫(kù)函數(shù)、日期與時(shí)間函數(shù)、工程函數(shù)、財(cái)務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計(jì)函數(shù)、文本函數(shù)、多維數(shù)據(jù)集函數(shù)、兼容性函數(shù)。

項(xiàng)目任務(wù)6-2創(chuàng)建函數(shù)

在公式中合理地使用函數(shù)，可以大大節(jié)省用戶的輸入時(shí)間，簡(jiǎn)化公式的輸入。動(dòng)手做1應(yīng)用求和函數(shù)

由于用戶對(duì)求和函數(shù)比較熟悉，知道求和函數(shù)的語(yǔ)法為SUM（Number1，Number2……），因此這里可以采取直接輸入的方式創(chuàng)建函數(shù)。在考試成績(jī)分析表中應(yīng)用求和函數(shù)的具體操作步驟如下：

（1）選中“G6”單元格。

（2）首先輸入“=”號(hào)。

（3）然后輸入SUM（C6:F6）。

（4）單擊編輯欄上的“輸入”按鈕，即可得出求和的結(jié)果。

（5）選中“G6”單元格，將鼠標(biāo)移到“G6”單元格的右下角的填充柄處，當(dāng)鼠標(biāo)變成

狀時(shí)向下拖動(dòng)鼠標(biāo)。

（6）當(dāng)拖動(dòng)到目的位置后松開(kāi)鼠標(biāo)，此時(shí)單元格“G6”中的函數(shù)被復(fù)制到了填充柄拖過(guò)的單元格中。

項(xiàng)目任務(wù)6-2創(chuàng)建函數(shù)

動(dòng)手做2應(yīng)用平均值函數(shù)

在考試成績(jī)分析表中應(yīng)用平均值函數(shù)的具體操作步驟如下：

（1）選中“H6”單元格。

（2）在“公式”選項(xiàng)卡下的“函數(shù)庫(kù)”組中單擊“自動(dòng)求和函數(shù)”右側(cè)的下三角箭頭，打開(kāi)“自動(dòng)求和”列表，

（3）在“自動(dòng)求和”列表中單擊“平均值”選項(xiàng)，則在“H6”單元格中出現(xiàn)平均值函數(shù)語(yǔ)法“=AVERAGE(C6:G6)”。

（4）將函數(shù)中的參數(shù)“G6”修改為“F6”，單擊編輯欄上的“輸入”按鈕，即可得出平均值的結(jié)果。

（5）選中“H6”單元格的填充柄向下拖動(dòng)鼠標(biāo)。

項(xiàng)目任務(wù)6-2創(chuàng)建函數(shù)動(dòng)手做3應(yīng)用COUNTIF函數(shù)COUNTIF函數(shù)計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)，他的語(yǔ)法為COUNTIF（range,criteria），Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域，Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。

例如，條件可以表示為10、"10"、">10"或"文本"。

在工作中經(jīng)常需要統(tǒng)計(jì)滿足條件的單元格的數(shù)量，如在考試成績(jī)分析表中需要統(tǒng)計(jì)出不及格的科數(shù)，具體操作步驟如下：

（1）選中“I6”單元格。

（2）在“公式”選項(xiàng)卡下的“函數(shù)庫(kù)”組中單擊“插入函數(shù)”按鈕，打開(kāi)“插入函數(shù)”對(duì)話框。

（3）在“或選擇類別”下拉列表中選擇“統(tǒng)計(jì)”選項(xiàng)，在“選擇函數(shù)”列表框中選擇“COUNTIF”。

（4）單擊“確定”按鈕，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。

（5）在“Range參數(shù)框”中輸入“C6:F6”，在“Criteria參數(shù)框”中輸入“<60”。

（6）單擊“確定”按鈕即可在“I6”單元格中得到統(tǒng)計(jì)結(jié)果。

（7）選中“I6”單元格的填充柄向下拖動(dòng)鼠標(biāo)。

項(xiàng)目任務(wù)6-2創(chuàng)建函數(shù)動(dòng)手做4應(yīng)用RANK函數(shù)RANK函數(shù)最常用的是求某一個(gè)數(shù)值在某一區(qū)域內(nèi)的排名。RANK函數(shù)語(yǔ)法形式：RANK(number,ref,[order])

函數(shù)名后面的參數(shù)中number為需要求排名的那個(gè)數(shù)值或者單元格名稱（單元格內(nèi)必須為數(shù)字），ref為排名的參照數(shù)值區(qū)域，order的為0和1，默認(rèn)不用輸入，得到的就是從大到小的排名，若是想求倒數(shù)第幾，order的值請(qǐng)使用1。

在Excel2010中RANK函數(shù)有兩種，一種是RANK.AVG，一種是RANK.EQ，RANK.AVG函數(shù)的不同之處在于，對(duì)于數(shù)值相等的情況，返回該數(shù)值的平均排名。而作為對(duì)比，RANK.EQ函數(shù)對(duì)于相等的數(shù)值返回其最高排名。動(dòng)手做5應(yīng)用IF函數(shù)

在邏輯函數(shù)中最常用到的為條件函數(shù)IF，它可以執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果，他的語(yǔ)法為IF(logical_test,value_if_true,value_if_false)

其中l(wèi)ogical_tes表示要選取的條件，value_if_true表示條件為真時(shí)返回的值，value_if_false表示條件為假時(shí)返回的值。函數(shù)IF可以嵌套七層，用value_if_false及value_if_true參數(shù)可以構(gòu)造復(fù)雜的檢測(cè)條件。

項(xiàng)目任務(wù)6-2創(chuàng)建函數(shù)動(dòng)手做6應(yīng)用求最大值函數(shù)

在考試成績(jī)分析表中應(yīng)用求最大值函數(shù)的具體操作步驟如下：

（1）選中“C25”單元格。

（2）在“公式”選項(xiàng)卡下的“函數(shù)庫(kù)”組中單擊“自動(dòng)求和函數(shù)”右側(cè)的下三角箭頭，打開(kāi)“自動(dòng)求和”列表。

（3）在“自動(dòng)求和”列表中單擊“最大值”選項(xiàng)，則在“C25”單元格中出現(xiàn)最大值函數(shù)語(yǔ)法“=MAX(C6:C24)”。

（4）單擊編輯欄上的“輸入”按鈕，即可得出最大值的結(jié)果。

（5）選中“C25”單元格的填充柄向右拖動(dòng)鼠標(biāo)。動(dòng)手做7應(yīng)用求最小值函數(shù)

（1）選中“C26”單元格。

（2）在“公式”選項(xiàng)卡下的“函數(shù)庫(kù)”組中單擊“自動(dòng)求和函數(shù)”右側(cè)的下三角箭頭，打開(kāi)“自動(dòng)求和”列表。

（3）在“自動(dòng)求和”列表中單擊“最小值”選項(xiàng)，則在“C26”單元格中出現(xiàn)最小值函數(shù)語(yǔ)法=MIN(C6:C25)。

（4）將函數(shù)中的參數(shù)“C25”修改為“C24”，單擊編輯欄上的“輸入”按鈕，即可得出最小值的結(jié)果。

（5）選中“C26”單元格的填充柄向右拖動(dòng)鼠標(biāo)，

項(xiàng)目拓展其他常用函數(shù)的應(yīng)用動(dòng)手做1求某項(xiàng)投資的未來(lái)值FV

在進(jìn)行財(cái)務(wù)管理的工作中，經(jīng)常會(huì)遇到要計(jì)算某項(xiàng)投資的未來(lái)值的情況，此時(shí)如果利用Excel提供的FV函數(shù)進(jìn)行計(jì)算，則可以幫助用戶進(jìn)行一些有計(jì)劃、有目的、有效益的投資。FV函數(shù)是基于固定利率及等額分期付款方式，它返回的是某項(xiàng)投資的未來(lái)值。其語(yǔ)法形式為FV(rate、nper、pmt、pv、type)。其中rate為各期利率，為一個(gè)固定值，nper為總投資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù)，pmt為各期所應(yīng)付給（或得到）的金額，其數(shù)值在整個(gè)年金期間（或投資期內(nèi)）保持不變，通常pmt包括本金和利息，但不包括其它費(fèi)用及稅款，pv為現(xiàn)值，或一系列未來(lái)付款當(dāng)前值的累積和，也稱為本金，如果省略pv，則假設(shè)其值為零，type為數(shù)字0或1，用以指定各期的付款時(shí)間是在期初還是期末，1為期初，0為期末，如果省略t，則假設(shè)其值為零。

項(xiàng)目拓展其他常用函數(shù)的應(yīng)用動(dòng)手做2求貸款分期償還額PMTPMT函數(shù)是基于固定利率及等額分期付款方式，它返回的是投資或貸款的每期付款額。PMT函數(shù)可以計(jì)算為償還一筆貸款，要求在一定周期內(nèi)支付完時(shí)，每次需要支付的償還額，也就是平時(shí)所說(shuō)的“分期付款”。比如借購(gòu)房貸款或其它貸款時(shí)，可以計(jì)算每期的償還額。PMT函數(shù)的語(yǔ)法形式為：PMT(rate,nper,pv,fv,type)其中，rate為各期利率，是一個(gè)固定值，nper為總投資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù)，pv為現(xiàn)值，或一系列未來(lái)付款當(dāng)前值的累積和，也稱為本金，fv為未來(lái)值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略fv，則假設(shè)其值為零（例如，一筆貸款的未來(lái)值即為零），type為0或1，用以指定各期的付款時(shí)間是在期初還是期末。如果省略type，則假設(shè)其值為零。

項(xiàng)目拓展其他常用函數(shù)的應(yīng)用動(dòng)手做3求某項(xiàng)投資的現(xiàn)值PVPV函數(shù)是用來(lái)計(jì)算某項(xiàng)投資的現(xiàn)值。年金現(xiàn)值就是未來(lái)各期年金現(xiàn)在的價(jià)值的總和。如果投資回收的當(dāng)前價(jià)值大于投資的價(jià)值，則這項(xiàng)投資是有收益的。PV函數(shù)的語(yǔ)法形式為：PV(rate,nper,pmt,fv,type)。其中Rate為各期利率，Nper為總投資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù)。pmt為各期所應(yīng)支付的金額，其數(shù)值在整個(gè)年金期間保持不變。通常pmt包括本金和利息，但不包括其他費(fèi)用及稅款。fv為未來(lái)值，或在最后一次支付后希望得到的現(xiàn)金余額，如果省略fv，則假設(shè)其值為零（一筆貸款的未來(lái)值即為零）。Type用以指定各期的付款時(shí)間是在期初還是期末。

項(xiàng)目拓展其他常用函數(shù)的應(yīng)用動(dòng)手做4直線折舊函數(shù)

企業(yè)中不同的折舊方法直接影響到企業(yè)利潤(rùn)的大小，折舊費(fèi)用低估，則凈利高估；反之，折舊費(fèi)用高估，則凈利低估。因此在為企業(yè)進(jìn)行固定資產(chǎn)投資分析時(shí)，選擇不同的折舊方法是投資決策的一個(gè)重要因素之一，為此，Excel為方便財(cái)務(wù)管理人員進(jìn)行決策分析時(shí)，提供了一些折舊函數(shù)。

直線折舊法是計(jì)算折舊最常用的一種方法，其每期折舊額的公式為：年折舊額＝（原始成本－預(yù)計(jì)凈殘值）/使用年限，使用直線折舊法計(jì)算折舊時(shí)，每期的折舊額都一樣。

此函數(shù)的語(yǔ)法為：SLN（cost,salvage,life）

功能：返回某項(xiàng)固定資產(chǎn)每期按直線折舊法計(jì)算的折舊數(shù)額。所有的參數(shù)值必須是正數(shù)。否則將返回錯(cuò)誤值#NUM!。

在函數(shù)中各參數(shù)的意義如下：Cost為固定資產(chǎn)的原始成本。Salvage為固定資產(chǎn)報(bào)廢時(shí)的預(yù)計(jì)凈殘值。Life為固定資產(chǎn)可使用年數(shù)的估計(jì)數(shù)。

項(xiàng)目拓展其他常用函數(shù)的應(yīng)用動(dòng)手做5年數(shù)總和法折舊函數(shù)

年數(shù)總和法是一種加速折舊方法，它以固定資產(chǎn)的原始成本減去預(yù)計(jì)殘值后的余額乘以一個(gè)逐年遞減的分?jǐn)?shù)，作為該期的折舊額。這個(gè)分?jǐn)?shù)的分母是固定資產(chǎn)使用年限的各年年數(shù)之和，分子是固定資產(chǎn)尚可使用年限。假設(shè)使用年限為L(zhǎng)ife，則遞減分?jǐn)?shù)的分母為：Life+(life-1)+(life-2)+……+1=life(life+1)/2

自第一期至第life期分子分別為life，life-1，life-2……，1。

年折舊額的計(jì)算公式為：

年折舊額＝（原始成本－預(yù)計(jì)凈殘值）×尚可使用年限/使用年限之和

在利用年數(shù)總和法來(lái)計(jì)算折舊額時(shí)，是使用年數(shù)總和法函數(shù)SYD（）來(lái)計(jì)算的。函數(shù)語(yǔ)法為：SYD（cost,salvage,life,per）

功能：返回某項(xiàng)固定資產(chǎn)某期間的按年數(shù)總和法計(jì)算的折舊數(shù)額。其所有的參數(shù)都應(yīng)為數(shù)，否則將返回錯(cuò)誤值#NUM!。

在函數(shù)中各參數(shù)的意義如下：Cost為固定資產(chǎn)的原始成本。Salvage為固定資產(chǎn)報(bào)廢時(shí)的預(yù)計(jì)凈殘值。Life為固定資產(chǎn)可使用年數(shù)的估計(jì)數(shù)。Per為指定要計(jì)算第幾期折舊數(shù)額。

Life與Per參數(shù)應(yīng)采用相同的單位，且per應(yīng)小于或等于life。

項(xiàng)目拓展其他常用函數(shù)的應(yīng)用動(dòng)手做6與函數(shù)AND

語(yǔ)法：AND（logical1，logical2，…）

功能：邏輯“與”函數(shù)，當(dāng)所有參數(shù)的邏輯值均為TRUE時(shí)，才返回TRUE值，如果有任何一個(gè)參數(shù)的邏輯值為FALSE，則返回FALSE。

說(shuō)明：參數(shù)可為1到14，這些參數(shù)都是值為邏輯值的表達(dá)式。如果所指定的范圍中不含邏輯值，AND函數(shù)將返回錯(cuò)誤值#VALUE!。。動(dòng)手做7或函數(shù)OR

語(yǔ)法：OR（logical1，logical2，…）

功能：邏輯“或”函數(shù)，只要所有參數(shù)的邏輯值有一個(gè)為TRUE，其返回TRUE值，否則將返回FALSE。

說(shuō)明：參數(shù)可為1到14，這些參數(shù)都是值為邏輯值的表達(dá)式。如果所指定的范圍中不含邏輯值，OR函數(shù)將返回錯(cuò)誤值#VALUE！。動(dòng)手做8非函數(shù)NOT

語(yǔ)法：NOT（logical）

功能：邏輯“非”函數(shù)，改變logical參數(shù)的邏輯值。

說(shuō)明：如果logical為TRUE，其返回FALSE，如果logical為FALSE，其返回TRUE。

知識(shí)拓展動(dòng)手做1公式返回的錯(cuò)誤信息

在Excel2010工作表的單元格中輸入公式以后，如果輸入的公式不符合格式或者其它要求，就無(wú)法顯示運(yùn)算的結(jié)果，該單元格中會(huì)顯示錯(cuò)誤值信息，如“#####！”、“#DIV/0！”、“#N/A#”、“NAME？”、“#NULL！”、“#NUM！”、“#REF！”、“#VALUE！”。了解這些錯(cuò)誤值信息的含義可以幫助用戶修改單元格中的公式。表列出了Excel2010中的錯(cuò)誤值及其含義。知識(shí)拓展動(dòng)手做2保護(hù)單元格中的公式

如果單元格中的數(shù)據(jù)是公式計(jì)算出來(lái)的，那么當(dāng)選定該單元格后，在編輯欄上將會(huì)顯示出該數(shù)據(jù)的公式。如果用戶工作表中的數(shù)據(jù)比較重要，可以將工作表中單元格中的公式隱藏，這樣可以防止其他用戶看出該數(shù)據(jù)是如何計(jì)算出的。

對(duì)工作表中的公式進(jìn)行保護(hù)，具體步驟如下：

（1）選中要保護(hù)的單元格或單元格區(qū)域。

（2）單擊“開(kāi)始”選項(xiàng)卡下“單元格”組中的“格式”按鈕，在打開(kāi)的下拉列表中選擇“設(shè)置單元格格式”選項(xiàng)，打開(kāi)“單元格格式”對(duì)話框，單擊“保護(hù)”選項(xiàng)卡。

（3）在對(duì)話框中如果選中了“鎖定”復(fù)選框，則工作表受保護(hù)后，單元格中的數(shù)據(jù)不能被修改；如果選中了“隱藏”復(fù)選框，則工作表受保護(hù)后，單元格中的公式被隱藏。

（4）單擊“確定”按鈕。

（5）單擊“審閱”選項(xiàng)卡下“更改”組中的“保護(hù)工作表”按鈕，打開(kāi)“保護(hù)工作表”對(duì)話框。選中“保護(hù)工作表及鎖定的單元格內(nèi)容”復(fù)選框，單擊“確定”按鈕，對(duì)工作表設(shè)置保護(hù)。

設(shè)置了隱藏功能后，在選中含有公式的單元格，則不顯示公式。

知識(shí)拓展動(dòng)手做3查看當(dāng)前工作表中的所有公式

利用Excel2010中的“公式審核”工具可以快速的顯示出當(dāng)前工作表中所有的公式，顯示工作表中的所有公式的具體操作步驟如下：

（1）切換到要顯示公式的工作表中，單擊“公式”選項(xiàng)卡，在“公式審核”選項(xiàng)組中單擊“顯示公式”按鈕，即可將工作表中所有公式顯示出來(lái)。

（2）如果要重新顯示出數(shù)值，再次單擊“顯示公式”按鈕即可。動(dòng)手做4追蹤引用單元格

所謂追蹤引用單元格是指查看當(dāng)前公式是引用哪些單元格進(jìn)行計(jì)算的。當(dāng)公式有錯(cuò)誤值時(shí)，通過(guò)該功能也可輔助查找公式錯(cuò)誤原因。

追蹤引用單元格的具體操作步驟如下