第4章 Excel 2003電子表格處理-3-公式與函數(shù)

1、第4章 Excel電子表格處理-公式與函數(shù)一、公式 公式的使用 公式輸入以等號(hào)“=”開始，等號(hào)后面是由運(yùn)算符、常量、單元格地址、函數(shù)及括號(hào)組成的表達(dá)式 公式中可以使用單元格引用或已命名的單元格區(qū)域名稱 Excel包含4種類型的運(yùn)算符：算術(shù)運(yùn)算符、關(guān)系運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符Excel運(yùn)算符 運(yùn)算順序： 引用運(yùn)算符（區(qū)域、聯(lián)合、交叉）、負(fù)號(hào)（-）、百分比（%）、乘方（）、乘（*）和除（/）、加（+）和減（-）、連接運(yùn)算符（&）、比較運(yùn)算符（=、=、=、）的順序進(jìn)行運(yùn)算。 二、函數(shù) 函數(shù)是一些已定義的公式，使用Excel提供的內(nèi)置函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，特別適用于執(zhí)行復(fù)雜計(jì)算的公式

2、。函數(shù)可以單獨(dú)使用，也可以出現(xiàn)在公式中，合理使用函數(shù)將大大提高表格計(jì)算的效率。 在單元格中輸入函數(shù)也要以等號(hào)“”開始。其一般格式為： 函數(shù)名(參數(shù)1,參數(shù)2,) 函數(shù)的輸入 單擊“插入”“函數(shù)”菜單命令或單擊工具欄上的“粘貼函數(shù)” 。 在單元格中直接輸入數(shù)據(jù)引用n根據(jù)公式中單元格的引用方式不同，可分為根據(jù)公式中單元格的引用方式不同，可分為絕對(duì)引用、相對(duì)引用、混合引用和三維引用、絕對(duì)引用、相對(duì)引用、混合引用和三維引用、多維引用。多維引用。數(shù)據(jù)引用一、相對(duì)引用 相對(duì)引用的含義：如果在公式中使用相對(duì)引用，當(dāng)復(fù)制公：如果在公式中使用相對(duì)引用，當(dāng)復(fù)制公式時(shí)，目標(biāo)單元格公式中的引用會(huì)根據(jù)目標(biāo)單元格和原單元

3、式時(shí)，目標(biāo)單元格公式中的引用會(huì)根據(jù)目標(biāo)單元格和原單元格的相對(duì)位移而自動(dòng)產(chǎn)生變化。格的相對(duì)位移而自動(dòng)產(chǎn)生變化。 Excel2003Excel2003系統(tǒng)默認(rèn)，所有新創(chuàng)建的公式均使用相對(duì)引用。系統(tǒng)默認(rèn)，所有新創(chuàng)建的公式均使用相對(duì)引用。 相對(duì)引用的表示：直接用列標(biāo)加行標(biāo)表示。直接用列標(biāo)加行標(biāo)表示。 如：如：A6A6，C4C4都是相對(duì)引用都是相對(duì)引用 例如，將單元格例如，將單元格E3E3中公式中公式“=SUM(B3:D3) ”=SUM(B3:D3) ”復(fù)制到單元復(fù)制到單元格格E4E4中，則公式變?yōu)橹校瑒t公式變?yōu)?“ “=SUM(B4:D4)”=SUM(B4:D4)”。二、絕對(duì)引用 絕對(duì)引用的含義：如果

4、在公式中使用相對(duì)引用，當(dāng)復(fù)制如果在公式中使用相對(duì)引用，當(dāng)復(fù)制公式時(shí)，目標(biāo)單元格公式中的引用不會(huì)根據(jù)目標(biāo)單元格和原公式時(shí)，目標(biāo)單元格公式中的引用不會(huì)根據(jù)目標(biāo)單元格和原單元格的相對(duì)位移而產(chǎn)生變化。單元格的相對(duì)位移而產(chǎn)生變化。 絕對(duì)引用的表示：在行號(hào)和列號(hào)前加入在行號(hào)和列號(hào)前加入“$”$”。 如：如：$A$6$A$6，$C$4$C$4都是絕對(duì)引用。都是絕對(duì)引用。 例如，將單元格例如，將單元格E3E3中公式中公式“=SUM($B$3:$D$3) ”=SUM($B$3:$D$3) ”復(fù)制復(fù)制到單元格到單元格E4E4中，則公式仍為中，則公式仍為 “ “=SUM($B$3:$D$3)”=SUM($B$3:$

5、D$3)”。三、混合引用 混合引用的含義：在公式中既使用了相對(duì)引用，又使用了絕對(duì)引用，：在公式中既使用了相對(duì)引用，又使用了絕對(duì)引用，當(dāng)進(jìn)行公式復(fù)制時(shí)，絕對(duì)引用部分保持不變，相對(duì)引用部分隨單元格位當(dāng)進(jìn)行公式復(fù)制時(shí)，絕對(duì)引用部分保持不變，相對(duì)引用部分隨單元格位置的變化而變化。置的變化而變化。 混合引用的表示：在行號(hào)或列號(hào)前加入在行號(hào)或列號(hào)前加入“$”$”。 如：如：$A6$A6， A$6A$6，$C4$C4，C$4C$4都是混合引用。都是混合引用。 例如，將單元格例如，將單元格A5A5中公式中公式“=SUM(A$1:A$4) ”=SUM(A$1:A$4) ”復(fù)制到單元格復(fù)制到單元格B6B6中，中，

6、則公式變?yōu)閯t公式變?yōu)?“ “=SUM(B$1:B$4)”=SUM(B$1:B$4)”。1、計(jì)數(shù)類函數(shù)1.SUM函數(shù) 功能：對(duì)給定的所有參數(shù)進(jìn)行求和運(yùn)算。對(duì)給定的所有參數(shù)進(jìn)行求和運(yùn)算。 調(diào)用格式：SUMSUM（n1n1，n2n2，.） 說(shuō)明：n1,n2,n1,n2,代表需要求和的單元格或單元格區(qū)域參代表需要求和的單元格或單元格區(qū)域參數(shù)，是數(shù)值型數(shù)據(jù)。如果參數(shù)中出現(xiàn)了邏輯型數(shù)據(jù)或字符型數(shù)，是數(shù)值型數(shù)據(jù)。如果參數(shù)中出現(xiàn)了邏輯型數(shù)據(jù)或字符型數(shù)據(jù)，則將其作為數(shù)據(jù)，則將其作為0 0處理。處理。2.AVERAGE函數(shù) 功能：計(jì)算所有參數(shù)的算術(shù)平均值。計(jì)算所有參數(shù)的算術(shù)平均值。 調(diào)用格式：AVERAGE(n1

7、AVERAGE(n1，n2n2，.).)。 說(shuō)明：n1n1、n2n2、.是要計(jì)算平均值的是要計(jì)算平均值的1 13030個(gè)參數(shù)。個(gè)參數(shù)。 3.COUNT函數(shù) 功能：在數(shù)據(jù)區(qū)內(nèi)統(tǒng)計(jì)數(shù)值型單元格的數(shù)量。在數(shù)據(jù)區(qū)內(nèi)統(tǒng)計(jì)數(shù)值型單元格的數(shù)量。 調(diào)用格式：COUNT(COUNT(參數(shù)參數(shù)) )。 說(shuō)明：參數(shù)是包含或引用各種類型數(shù)據(jù)參數(shù)是包含或引用各種類型數(shù)據(jù) (1(13030個(gè)個(gè)) )，其，其中只有數(shù)值型的數(shù)據(jù)才能被統(tǒng)計(jì)。中只有數(shù)值型的數(shù)據(jù)才能被統(tǒng)計(jì)。 4.COUNTIF函數(shù) 功能：在數(shù)據(jù)區(qū)按條件統(tǒng)計(jì)非空單元格的數(shù)量。在數(shù)據(jù)區(qū)按條件統(tǒng)計(jì)非空單元格的數(shù)量。 調(diào)用格式：COUNTIF(COUNTIF(數(shù)據(jù)區(qū)域，

8、邏輯表達(dá)式數(shù)據(jù)區(qū)域，邏輯表達(dá)式) ) 說(shuō)明：“數(shù)據(jù)區(qū)域數(shù)據(jù)區(qū)域”為需要計(jì)算其中滿足條件的單元格為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。數(shù)目的單元格區(qū)域。“邏輯表達(dá)式邏輯表達(dá)式”為統(tǒng)計(jì)的條件，其形式為統(tǒng)計(jì)的條件，其形式可以為數(shù)字、表達(dá)式或文本?？梢詾閿?shù)字、表達(dá)式或文本。5.MAX函數(shù) 功能：對(duì)給定的所有參數(shù)進(jìn)行求其中的最大值運(yùn)算。對(duì)給定的所有參數(shù)進(jìn)行求其中的最大值運(yùn)算。 調(diào)用格式：MAXMAX（n1,n2,n1,n2,） 說(shuō)明：n1,n2,n1,n2,代表需要求最大值的單元格或單元格區(qū)代表需要求最大值的單元格或單元格區(qū)域參數(shù)，參數(shù)只能是數(shù)值型數(shù)據(jù)。如果參數(shù)中出現(xiàn)了邏輯型域參數(shù)，參數(shù)只能是

9、數(shù)值型數(shù)據(jù)。如果參數(shù)中出現(xiàn)了邏輯型數(shù)據(jù)或字符型數(shù)據(jù)，則將其作為數(shù)據(jù)或字符型數(shù)據(jù)，則將其作為0 0處理。處理。6.MIN函數(shù) 功能：返回給定參數(shù)表中的最小值。返回給定參數(shù)表中的最小值。 調(diào)用格式：MIN(n1MIN(n1，n2n2，.).)。 說(shuō)明：n1n1，n2n2，.是要從中找出最小值的是要從中找出最小值的1 1到到3030個(gè)數(shù)字個(gè)數(shù)字參數(shù)參數(shù) 注意: 公式中的標(biāo)點(diǎn)符號(hào)都必須是英文標(biāo)點(diǎn)符號(hào) 使用函數(shù)幫助 幫助目錄使用數(shù)據(jù)函數(shù)引用 找到相應(yīng)函數(shù)查看內(nèi)容2、邏輯類函數(shù)IF函數(shù) 功能：執(zhí)行邏輯判斷，它可以根據(jù)邏輯表達(dá)式的真假，執(zhí)行邏輯判斷，它可以根據(jù)邏輯表達(dá)式的真假，返回不同的結(jié)果，從而執(zhí)行數(shù)值或

10、公式的條件檢測(cè)任務(wù)。返回不同的結(jié)果，從而執(zhí)行數(shù)值或公式的條件檢測(cè)任務(wù)。 調(diào)用格式：IFIF（條件，為真時(shí)的值，為假時(shí)的值）（條件，為真時(shí)的值，為假時(shí)的值） 說(shuō)明： “條件”是計(jì)算結(jié)果為TRUE或FALSE的任何數(shù)值或表達(dá)式 “條件”為TRUE時(shí)函數(shù)的返回值是“為真時(shí)的值”，“條件”為FALSE時(shí)函數(shù)的返回值是“為假時(shí)的值” 。 “為真時(shí)的值”和“為假時(shí)的值” 可以是一個(gè)表達(dá)式。 RANK函數(shù)函數(shù)用途：用途：返回某一數(shù)值在一列數(shù)值中的相對(duì)于其他數(shù)值的排位。 語(yǔ)法：語(yǔ)法：RANK（Number,ref,order） 參數(shù)：參數(shù)：Number代表需要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域；o

11、rder代表排序方式參數(shù)（如果為“0”或者忽略，則按降序排名，即數(shù)值越大，排名結(jié)果數(shù)值越小；如果為非“0”值，則按升序排名，即數(shù)值越大，排名結(jié)果數(shù)值大）。舉例：舉例：如在C2單元格中輸入公式：=RANK(B2,$B$2:$B$31,0)，確認(rèn)后即可得出丁1同學(xué)的語(yǔ)文成績(jī)?cè)谌喑煽?jī)中的排名結(jié)果。 3、文本函數(shù)LEFT 用途：根據(jù)指定的字符數(shù)返回文本串中的第一個(gè)或前幾個(gè)字符。 語(yǔ)法：LEFT(text，num_chars)。 參數(shù)：Text是包含要提取字符的文本串;Num_chars指定函數(shù)要提取的字符數(shù)，它必須大于或等于0。 如果 num_chars 大于文本長(zhǎng)度，則 LEFT 返回所有文本。

12、如果省略 num_chars，則假定其為 1 實(shí)例：如果A1=電腦愛好者，則LEFT(A1，2)返回“電腦”。RIGHT 用途：RIGHT根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。 語(yǔ)法：RIGHT(text，num_chars)。 參數(shù)：Text是包含要提取字符的文本串;Num_chars指定希望RIGHT提取的字符數(shù)，它必須大于或等于0。如果num_chars大于文本長(zhǎng)度，則RIGHT返回所有文本。如果忽略num_chars，則假定其為1。 實(shí)例：如果A1=學(xué)習(xí)的革命，則公式“=RIGHT(A1，2)”返回“革命”。MID 用途：MID返回文本串中從指定位置開始的特定數(shù)目的字符，該

13、數(shù)目由用戶指定。 語(yǔ)法：MID(text，start_num，num_chars)。 參數(shù)：Text是包含要提取字符的文本串。Start_num是文本中要提取的第一個(gè)字符的位置，文本中第一個(gè)字符的start_num為1，以此類推;Num_chars指定希望MID從文本中返回字符的個(gè)數(shù); 實(shí)例：如果a1=電子計(jì)算機(jī)，則公式“=MID(A1，3，2)”返回“計(jì)算”。4、日期函數(shù)YEAR函數(shù) 用途: 返回某日期對(duì)應(yīng)的年份。返回值為 1900 到 9999 之間的整數(shù)。 語(yǔ)法語(yǔ)法: YEAR(serial_number) 參數(shù): Serial_number 為一個(gè)日期值，其中包含要查找年份的日期。應(yīng)使

14、用 DATE 函數(shù)來(lái)輸入日期，或者將日期作為其他公式或函數(shù)的結(jié)果輸入。例如，使用 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本的形式輸入，則會(huì)出現(xiàn)問題。NOW函數(shù)用途: 返回當(dāng)前日期和時(shí)間所對(duì)應(yīng)的序列號(hào)。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。語(yǔ)法語(yǔ)法: NOW( )說(shuō)明說(shuō)明:Microsoft Excel 可將日期存儲(chǔ)為可用于計(jì)算的序列號(hào)。默認(rèn)情況下，1900 年 1 月 1 日的序列號(hào)是 1 而 2008 年 1 月 1 日的序列號(hào)是 39448，這是因?yàn)樗?1900 年 1 月 1 日有 39448 天。序列號(hào)中小數(shù)點(diǎn)右邊

15、的數(shù)字表示時(shí)間，左邊的數(shù)字表示日期。例如，序列號(hào) .5 表示時(shí)間為中午 12:00。 函數(shù) NOW 只有在重新計(jì)算工作表，或執(zhí)行含有此函數(shù)的宏時(shí)改變。它并不會(huì)隨時(shí)更新。 TODAY函數(shù)用途: 返回當(dāng)前日期的序列號(hào)。序列號(hào)Microsoft Excel 日期和時(shí)間計(jì)算使用的日期-時(shí)間代碼。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。語(yǔ)法語(yǔ)法: TODAY( )說(shuō)明說(shuō)明:Microsoft Excel 可將日期存儲(chǔ)為可用于計(jì)算的序列號(hào)。默認(rèn)情況下，1900 年 1 月 1 日的序列號(hào)是 1 而 2008 年 1 月 1 日的序列號(hào)是 39448，這是因?yàn)樗?1900 年 1

16、月 1 日有 39448 天。5、其它函數(shù) MOD 用途：返回兩數(shù)相除的余數(shù)，其結(jié)果的正負(fù)號(hào)與除數(shù)相同。 語(yǔ)法：MOD(number，divisor) 參數(shù)：Number為被除數(shù)，Divisor為除數(shù)(divisor不能為零)。 實(shí)例：如果A1=51，則公式“=MOD(A1，4)”返回3;=MOD(-101，-2)返回1。 DATEDIF 用途：計(jì)算返回兩個(gè)日期參數(shù)的差值。 語(yǔ)法：=DATEDIF(date1,date2,“y”)、=DATEDIF(date1,date2,“m”)、=DATEDIF(date1,date2,“d”) 參數(shù)：date1代表前面一個(gè)日期，date2代表后面一個(gè)日期

17、；y（m、d）要求返回兩個(gè)日期相差的年（月、天）數(shù)。 實(shí)例：在C23單元格中輸入公式：=DATEDIF(A23,TODAY(),y)，確認(rèn)后返回系統(tǒng)當(dāng)前日期(用TODAY()表示）與A23單元格中日期的差值，并返回相差的年數(shù)。 特別提醒：這是Excel中的一個(gè)隱藏函數(shù)，在函數(shù)向?qū)е惺钦也坏降?，可以直接輸入使用，?duì)于計(jì)算年齡、工齡等非常有效。頻率分布函數(shù)FREQUENCYFREQUENCY（data_array,bins_array）功能：求出指定區(qū)域中數(shù)值的頻率分布說(shuō)明：data_array：為一數(shù)組或?qū)σ籉REQUENCY（data_array,bins_array）數(shù)組值的引用；bins_

18、array：為一數(shù)值或?qū)?shù)據(jù)區(qū)域的引用，設(shè)置data_array進(jìn)行頻率計(jì)算的分段點(diǎn)用法：選擇結(jié)果區(qū)域輸入函數(shù)及其參數(shù)同時(shí)按下ctrl+shift+enter三個(gè)鍵結(jié)束輸入 （注意：不要按回車結(jié)束公式）選擇函數(shù)：CHOOSECHOOSE(index_num,value1,value2,) 功能：使用index_num返回?cái)?shù)值參數(shù)清單中的數(shù)值。函數(shù)名稱：VLOOKUP主要功能：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回?cái)?shù)據(jù)表當(dāng)前行中指定列處的數(shù)值。 使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 參數(shù)說(shuō)明：Look

19、up_value代表需要查找的數(shù)值；Table_array代表需要在其中查找數(shù)據(jù)的單元格區(qū)域；Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號(hào)（當(dāng)Col_index_num為2時(shí),返回table_array第2列中的數(shù)值，為3時(shí)，返回第3列的值）；Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說(shuō)，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯(cuò)誤值#N/A。 應(yīng)用舉例：參見圖7，我們?cè)贒65單元格中輸入公式：=VLOOKUP(B65,B2:D63,3,

20、FALSE)，確認(rèn)后，只要在B65單元格中輸入一個(gè)學(xué)生的姓名（如丁48），D65單元格中即刻顯示出該學(xué)生的語(yǔ)言成績(jī)。 VLOOKUP 特別提醒：Lookup_value參數(shù)必須在Table_array區(qū)域的首列中；如果忽略Range_lookup參數(shù)，則Table_array的首列必須進(jìn)行排序。在此函數(shù)的向?qū)е?，有關(guān)Range_lookup參數(shù)的說(shuō)法是錯(cuò)誤的。 第4個(gè)參數(shù)如選True（或省略），第一列中的數(shù)值必須按升序排列，否則不能返回正確的數(shù)值。如果為False，不必進(jìn)行排序。 6、財(cái)務(wù)函數(shù)財(cái)務(wù)函數(shù)1 PMT固定利率下，投資或貸款等額的分期償還額2 FV 求按每期固定利率及期滿的本息總和3

21、PV每期固定利率及期滿的投資或貸款當(dāng)前值的累積和三個(gè)財(cái)務(wù)函數(shù)Excel的財(cái)務(wù)函數(shù) 凡是投資的金額都以負(fù)數(shù)形式表示，收益以正數(shù)形式表示。 在介紹具體的財(cái)務(wù)函數(shù)之前，我們首先來(lái)了解一下財(cái)務(wù)函數(shù)中常見的參數(shù)： 未來(lái)值 (fv)-在所有付款發(fā)生后的投資或貸款的價(jià)值。 期間數(shù) (nper)-為總投資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù)。 付款 (pmt)-對(duì)于一項(xiàng)投資或貸款的定期支付數(shù)額。其數(shù)值在整個(gè)年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費(fèi)用及稅款。 現(xiàn)值 (pv)-在投資期初的投資或貸款的價(jià)值。例如，貸款的現(xiàn)值為所借入的本金數(shù)額。 利率 (rate)-投資或貸款的利率或

22、貼現(xiàn)率。 類型 (type)-付款期間內(nèi)進(jìn)行支付的間隔，如在月初或月末，用0或1表示。1 求貸款分期償還額 PMT函數(shù) 用途：基于固定利率及等額分期付款方式，返回貸款的每期付款額。 語(yǔ)法：語(yǔ)法：PMT(rate,nper,pv,fv,type) 參數(shù)： Rate 貸款利率。 Nper 該項(xiàng)貸款的付款總數(shù)。 Pv 現(xiàn)值，或一系列未來(lái)付款的當(dāng)前值的累積和，也稱為本金。 Fv 為未來(lái)值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略 fv，則假設(shè)其值為零，也就是一筆貸款的未來(lái)值為零。 Type 數(shù)字 0 或 1，用以指定各期的付款時(shí)間是在期初還是期末。PMT函數(shù)可以計(jì)算為償還一筆貸款，要求在一定周期

23、內(nèi)支付完時(shí)，每次需要支付的償還額，也就是我們平時(shí)所說(shuō)的分期付款。比如借購(gòu)房貸款或其它貸款時(shí)，可以計(jì)算每期的償還額。 2 求某項(xiàng)投資的未來(lái)值 FV函數(shù) 用途：基于固定利率及等額分期付款方式，返回某項(xiàng)投資的未來(lái)值。 語(yǔ)法：語(yǔ)法： FV(rate,nper,pmt,pv,type) 參數(shù)： Rate 為各期利率。 Nper 為總投資期，即該項(xiàng)投資的付款期總數(shù)。 Pmt 為各期所應(yīng)支付的金額，其數(shù)值在整個(gè)年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費(fèi)用及稅款。如果忽略 pmt，則必須包括 pv 參數(shù)。 Pv 為現(xiàn)值，即從該項(xiàng)投資開始計(jì)算時(shí)已經(jīng)入帳的款項(xiàng)，或一系列未來(lái)付款的當(dāng)前值的累積和，也稱為本金。如果省略 PV，則假設(shè)其值為零，并且必須包括 pmt 參數(shù)。 Type 數(shù)字 0 或 1，用以指定各期的付款時(shí)間是在期初還是期末。如果省略 type，則假設(shè)其值為零。在日常工作與生活中，我們經(jīng)常會(huì)遇到要計(jì)算某項(xiàng)投資的未來(lái)值的情況，此時(shí)利用Excel函數(shù)FV進(jìn)行計(jì)算后，可以幫助我們進(jìn)行一些有計(jì)劃、有目的、有效益的投資。 說(shuō)明說(shuō)明 ： 應(yīng)確認(rèn)所指定的 rate 和 nper 單位的一致性。例如，同樣是四年期年利率為 12% 的