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

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

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

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

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

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

6、中，中，則公式變?yōu)閯t公式變?yōu)?“ “=SUM(B$1:B$4)”=SUM(B$1:B$4)”。1、計數(shù)類函數(shù)1.SUM函數(shù) 功能：對給定的所有參數(shù)進行求和運算。對給定的所有參數(shù)進行求和運算。 調用格式：SUMSUM（n1n1，n2n2，.） 說明：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ù) 功能：計算所有參數(shù)的算術平均值。計算所有參數(shù)的算術平均值。 調用格式：AVERAG

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

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

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

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

11、區(qū)域；order代表排序方式參數(shù)（如果為“0”或者忽略，則按降序排名，即數(shù)值越大，排名結果數(shù)值越?。蝗绻麨榉恰?”值，則按升序排名，即數(shù)值越大，排名結果數(shù)值大）。舉例：舉例：如在C2單元格中輸入公式：=RANK(B2,$B$2:$B$31,0)，確認后即可得出丁1同學的語文成績在全班成績中的排名結果。 3、文本函數(shù)LEFT 用途：根據(jù)指定的字符數(shù)返回文本串中的第一個或前幾個字符。 語法：LEFT(text，num_chars)。 參數(shù)：Text是包含要提取字符的文本串;Num_chars指定函數(shù)要提取的字符數(shù)，它必須大于或等于0。 如果 num_chars 大于文本長度，則 LEFT 返回所有

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

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

14、期。應使用 DATE 函數(shù)來輸入日期，或者將日期作為其他公式或函數(shù)的結果輸入。例如，使用 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本的形式輸入，則會出現(xiàn)問題。NOW函數(shù)用途: 返回當前日期和時間所對應的序列號。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結果將設為日期格式。語法語法: NOW( )說明說明:Microsoft Excel 可將日期存儲為可用于計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1 而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。序列號中小

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

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

17、一個日期；y（m、d）要求返回兩個日期相差的年（月、天）數(shù)。 實例：在C23單元格中輸入公式：=DATEDIF(A23,TODAY(),y)，確認后返回系統(tǒng)當前日期(用TODAY()表示）與A23單元格中日期的差值，并返回相差的年數(shù)。 特別提醒：這是Excel中的一個隱藏函數(shù)，在函數(shù)向導中是找不到的，可以直接輸入使用，對于計算年齡、工齡等非常有效。頻率分布函數(shù)FREQUENCYFREQUENCY（data_array,bins_array）功能：求出指定區(qū)域中數(shù)值的頻率分布說明：data_array：為一數(shù)組或對一FREQUENCY（data_array,bins_array）數(shù)組值的引用；b

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

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

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

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

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

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