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

第4章Excel電子表格處理

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

Excel2003系統(tǒng)默認(rèn)，所有新創(chuàng)建的公式均使用相對(duì)引用。相對(duì)引用的表示：直接用列標(biāo)加行標(biāo)表示。如：A6，C4都是相對(duì)引用例如，將單元格E3中公式“=SUM(B3:D3)”復(fù)制到單元格E4中，則公式變?yōu)椤?SUM(B4:D4)”。二、絕對(duì)引用絕對(duì)引用的含義：如果在公式中使用相對(duì)引用，當(dāng)復(fù)制公式時(shí)，目標(biāo)單元格公式中的引用不會(huì)根據(jù)目標(biāo)單元格和原單元格的相對(duì)位移而產(chǎn)生變化。絕對(duì)引用的表示：在行號(hào)和列號(hào)前加入“$”。如：$A$6，$C$4都是絕對(duì)引用。例如，將單元格E3中公式“=SUM($B$3:$D$3)”復(fù)制到單元格E4中，則公式仍為“=SUM($B$3:$D$3)”。三、混合引用混合引用的含義：在公式中既使用了相對(duì)引用，又使用了絕對(duì)引用，當(dāng)進(jìn)行公式復(fù)制時(shí)，絕對(duì)引用部分保持不變，相對(duì)引用部分隨單元格位置的變化而變化?；旌弦玫谋硎荆涸谛刑?hào)或列號(hào)前加入“$”。如：$A6，A$6，$C4，C$4都是混合引用。例如，將單元格A5中公式“=SUM(A$1:A$4)”復(fù)制到單元格B6中，則公式變?yōu)椤?SUM(B$1:B$4)”。1、計(jì)數(shù)類函數(shù)1.SUM函數(shù)功能：對(duì)給定的所有參數(shù)進(jìn)行求和運(yùn)算。調(diào)用格式：SUM（n1，n2，...）

說(shuō)明：n1,n2,…代表需要求和的單元格或單元格區(qū)域參數(shù)，是數(shù)值型數(shù)據(jù)。如果參數(shù)中出現(xiàn)了邏輯型數(shù)據(jù)或字符型數(shù)據(jù)，則將其作為0處理。2.AVERAGE函數(shù)功能：計(jì)算所有參數(shù)的算術(shù)平均值。調(diào)用格式：AVERAGE(n1，n2，...)。說(shuō)明：n1、n2、...是要計(jì)算平均值的1～30個(gè)參數(shù)。3.COUNT函數(shù)

功能：在數(shù)據(jù)區(qū)內(nèi)統(tǒng)計(jì)數(shù)值型單元格的數(shù)量。調(diào)用格式：COUNT(參數(shù))。說(shuō)明：參數(shù)是包含或引用各種類型數(shù)據(jù)(1～30個(gè))，其中只有數(shù)值型的數(shù)據(jù)才能被統(tǒng)計(jì)。4.COUNTIF函數(shù)

功能：在數(shù)據(jù)區(qū)按條件統(tǒng)計(jì)非空單元格的數(shù)量。調(diào)用格式：COUNTIF(數(shù)據(jù)區(qū)域，邏輯表達(dá)式)

說(shuō)明：“數(shù)據(jù)區(qū)域”為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域?！斑壿嫳磉_(dá)式”為統(tǒng)計(jì)的條件，其形式可以為數(shù)字、表達(dá)式或文本。5.MAX函數(shù)功能：對(duì)給定的所有參數(shù)進(jìn)行求其中的最大值運(yùn)算。調(diào)用格式：MAX（n1,n2,…）說(shuō)明：n1,n2,…代表需要求最大值的單元格或單元格區(qū)域參數(shù)，參數(shù)只能是數(shù)值型數(shù)據(jù)。如果參數(shù)中出現(xiàn)了邏輯型數(shù)據(jù)或字符型數(shù)據(jù)，則將其作為0處理。6.MIN函數(shù)功能：返回給定參數(shù)表中的最小值。調(diào)用格式：MIN(n1，n2，...)。說(shuō)明：n1，n2，...是要從中找出最小值的1到30個(gè)數(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á)式的真假，返回不同的結(jié)果，從而執(zhí)行數(shù)值或公式的條件檢測(cè)任務(wù)。調(diào)用格式：IF（條件，為真時(shí)的值，為假時(shí)的值）說(shuō)明：“條件”是計(jì)算結(jié)果為T(mén)RUE或FALSE的任何數(shù)值或表達(dá)式“條件”為T(mén)RUE時(shí)函數(shù)的返回值是“為真時(shí)的值”，“條件”為FALSE時(shí)函數(shù)的返回值是“為假時(shí)的值”?！盀檎鏁r(shí)的值”和“為假時(shí)的值”可以是一個(gè)表達(dá)式。

RANK函數(shù)用途：返回某一數(shù)值在一列數(shù)值中的相對(duì)于其他數(shù)值的排位。語(yǔ)法：RANK（Number,ref,order）參數(shù)：Number代表需要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域；order代表排序方式參數(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返回所有文本。

如果省略num_chars，則假定其為1實(shí)例：如果A1=電腦愛(ài)好者，則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返回文本串中從指定位置開(kāi)始的特定數(shù)目的字符，該數(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ǔ)法:YEAR(serial_number)參數(shù):Serial_number

為一個(gè)日期值，其中包含要查找年份的日期。應(yīng)使用DATE函數(shù)來(lái)輸入日期，或者將日期作為其他公式或函數(shù)的結(jié)果輸入。例如，使用DATE(2008,5,23)輸入2008年5月23日。如果日期以文本的形式輸入，則會(huì)出現(xiàn)問(wèn)題。NOW函數(shù)用途:

返回當(dāng)前日期和時(shí)間所對(duì)應(yīng)的序列號(hào)。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。語(yǔ)法:NOW()說(shuō)明:MicrosoftExcel可將日期存儲(chǔ)為可用于計(jì)算的序列號(hào)。默認(rèn)情況下，1900年1月1日的序列號(hào)是1而2008年1月1日的序列號(hào)是39448，這是因?yàn)樗?900年1月1日有39448天。序列號(hào)中小數(shù)點(diǎn)右邊的數(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)MicrosoftExcel日期和時(shí)間計(jì)算使用的日期-時(shí)間代碼。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。語(yǔ)法:TODAY()說(shuō)明:MicrosoftExcel可將日期存儲(chǔ)為可用于計(jì)算的序列號(hào)。默認(rèn)情況下，1900年1月1日的序列號(hào)是1而2008年1月1日的序列號(hào)是39448，這是因?yàn)樗?900年1月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è)日期；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_array：為一數(shù)值或?qū)?shù)據(jù)區(qū)域的引用，設(shè)置data_array進(jìn)行頻率計(jì)算的分段點(diǎn)用法：選擇結(jié)果區(qū)域輸入函數(shù)及其參數(shù)同時(shí)按下ctrl+shift+enter三個(gè)鍵結(jié)束輸入（注意：不要按回車(chē)結(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ō)明：Lookup_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為一邏輯值，如果為T(mén)RUE或省略，則返回近似匹配值，也就是說(shuō)，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯(cuò)誤值#N/A。

應(yīng)用舉例：參見(jiàn)圖7，我們?cè)贒65單元格中輸入公式：=VLOOKUP(B65,B2:D63,3,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ù)1PMT固定利率下，投資或貸款等額的分期償還額

2FV求按每期固定利率及期滿的本息總和

3PV每期固定利率及期滿的投資或貸款當(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ù)中常見(jiàn)的參數(shù)：未來(lái)值

(fv)--在所有付款發(fā)生后的投資或貸款的價(jià)值。期間數(shù)(nper)--為總投資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù)。付款(pmt)--對(duì)于一項(xiàng)投資或貸款的定期支付數(shù)額。其數(shù)值在整個(gè)年金期間保持不變。通常pmt包括本金和利息，但不包括其他費(fèi)用及稅款?，F(xiàn)值(pv)--在投資期初的投資或貸款的價(jià)值。例如，貸款的現(xiàn)值為所借入的本金數(shù)額。利率(rate)--投資或貸款的利率或貼現(xiàn)率。類型(type)--付款期間內(nèi)進(jìn)行支付的間隔，如在月初或月末，用0或1表示。1求貸款分期償還額PMT函數(shù)用途：基于固定利率及等額分期付款方式，返回貸款的每期付款額。語(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ì)算為償還一筆貸款，要求在一定周期內(nèi)支付完時(shí)，每次需要支付的償還額，也就是我們平時(shí)所說(shuō)的"分期付款"。比如借購(gòu)房貸款或其它貸款時(shí)，可以計(jì)算每期的償還額。2求某項(xiàng)投資的未來(lái)值FV函數(shù)用途：基于固定利率及等額分期付款方式，返回某項(xiàng)投資的未來(lái)值。語(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)投資開(kāi)始計(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ō)明：應(yīng)確認(rèn)所指定的rate和nper單位的一致性。例如，同樣是四年期年利率為12%的貸款，如果按月支付，rate應(yīng)為12%/12，nper應(yīng)為4*12；如果按年支付，rate應(yīng)為12%，nper為4。在所有參數(shù)中，支出的款項(xiàng)，如銀行存