Office高級(jí)應(yīng)用之Excel公式與函數(shù)

MicrosoftOffice——Excel2003公式——函數(shù)公式——函數(shù)函數(shù)是一些預(yù)先定義的內(nèi)置公式，例如：1、SUM求和2、AVERAGE求平均值3、MAX求最大值4、MIN求最小值5、COUNTIF：統(tǒng)計(jì)給定區(qū)域內(nèi)滿足特定條件的個(gè)數(shù)6、RANK排位7、IF8、COUNT9、ROUND函數(shù)輸入1、直接輸入函數(shù)函數(shù)輸入2、使用“公式選項(xiàng)板”輸入函數(shù)函數(shù)輸入3、利用“粘貼函數(shù)”功能

“插入”—“函數(shù)”函數(shù)函數(shù)的形式：函數(shù)名（參數(shù)1，參數(shù)2，……）參數(shù)：常量、單元格引用、區(qū)域、其他函數(shù)例子：=sum(a1:d1)；

=average(a1:a4，b5)函數(shù)形式

在公式或函數(shù)中使用單元格稱為單元格的引用。(1)相對(duì)引用形式：A1、A1:D4特點(diǎn)：把公式復(fù)制到別處時(shí)，被引用的單元格地址將相應(yīng)變化。如何變化？單元格引用(2)絕對(duì)引用形式：$A$1、$A$1:$D$4特點(diǎn)：把公式復(fù)制到別處時(shí)，被引用的單元格地址保持不變。單元格引用(3)混合引用形式：$A1、A$1特點(diǎn)：復(fù)制時(shí)，不加$的行（列）號(hào)發(fā)生變化，加$的行（列）號(hào)保持不變。單元格引用數(shù)學(xué)函數(shù)SUMSUMIFRoundSUM函數(shù)格式：SUM（number1，number2，……）功能：返回指定參數(shù)所對(duì)應(yīng)的數(shù)值之和例如：SUM(A1:A3)、SUM(B2:B4,C5)、SUM(23,45,88)SUMIF函數(shù)SUMIF函數(shù)：用于計(jì)算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值進(jìn)行求和格式為：SUMIF（range，criteria，sum_range）range是條件判斷的單元格區(qū)域；criteria是指定條件表達(dá)式；sum_range

是需要計(jì)算的數(shù)值所在的單元格區(qū)域計(jì)算：屬性值超過(guò)160,000的傭金的和(63,000)另：見(jiàn)題2（4）計(jì)算性別是“男”的英語(yǔ)總分等效于：SUMIF(B2:B5,“男”,C2:C5)ROUND函數(shù)格式：ROUND（number，nun_digits）功能：按指定的位數(shù)對(duì)數(shù)值進(jìn)行四舍五入統(tǒng)計(jì)函數(shù)AverageMax/MinCOUNTCOUNTIFRANKAVERAGE函數(shù)功能：返回指定參數(shù)所對(duì)應(yīng)數(shù)值的算術(shù)平均數(shù)格式：AVERAGE（number1，number2，……）number1，number2等是指定所要進(jìn)行求平均值的參數(shù)注意：該函數(shù)只對(duì)參數(shù)的數(shù)值求平均數(shù)，如區(qū)域引用中包含了非數(shù)值的數(shù)據(jù)，則AVERAGE不把它包含在內(nèi)。1(3)MAX、MINMAX函數(shù)：求參數(shù)列表中對(duì)應(yīng)數(shù)字的最大值;MIN函數(shù)：求參數(shù)列表中對(duì)應(yīng)數(shù)字的最小值;格式：MAX（number1，number2，……）

MIN（number1，number2，……）number1、number2、……：從中找出最大值或最小值的1～30個(gè)數(shù)字參數(shù)COUNT函數(shù)功能：用于返回?cái)?shù)字參數(shù)的個(gè)數(shù)，即統(tǒng)計(jì)單元格區(qū)域中含有數(shù)值類型的單元格個(gè)數(shù)格式：COUNT(value1，value2，……)value1，value2，……表示包含或引用各類型數(shù)據(jù)的參數(shù)注意：1、函數(shù)可以最多附帶上1～30個(gè)參數(shù)

2、只有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計(jì)類似函數(shù)：1、COUNTA函數(shù)：返回參數(shù)組中非空值的數(shù)目，即計(jì)算數(shù)組或單元格區(qū)域中數(shù)據(jù)項(xiàng)的個(gè)數(shù)；2、COUNTBLANK函數(shù)：計(jì)算某個(gè)單元格區(qū)域中空白單元格的數(shù)目；3、COUNTIF函數(shù)：計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。例子COUNTIF(D2:D5,”男”)功能：統(tǒng)計(jì)D2:D5區(qū)域中男性人數(shù)；COUNTIF（C2:C12,“<60”)功能：統(tǒng)計(jì)C2:C12單元格區(qū)域中成績(jī)不及格的人數(shù)見(jiàn)練習(xí)題1（4）RANK返回一個(gè)數(shù)字在數(shù)字列表中的排位。格式：RANK

(number，ref，order)Number：為需要找到排位的數(shù)字。Ref

：為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。

Order：為０（零）或省略，降序；不為零，升序。

例：見(jiàn)題1（2）邏輯函數(shù)IFANDORIF函數(shù)IF（logical_test，value_if_true，value_if_false）logical_test：邏輯條件value_if_true：當(dāng)值為“真”時(shí)的返回值value_if_false：當(dāng)值為“假”時(shí)的返回值注意：在IF函數(shù)中三個(gè)參數(shù)中可以value_if_true或value_if_false，但不能同時(shí)省略例：2（2）、10（1）見(jiàn)題1（3）、2（2）文本函數(shù)Exact：比較兩個(gè)文本字符串是否相同Concatenate：將多個(gè)字符文或單元格中的數(shù)據(jù)連接在一起Substitute：將字符串中的部分字符串以新字符串替代Replace：將某幾位的文字以新的字符串替換。MID/LEFT/RIGHT功能：用來(lái)比較兩個(gè)文本字符串是否相同。如果兩個(gè)字符串相同，則返回“TRUE”，反之，則返回“FALSE”格式：EXACT（text1，text2）text1和text2：兩個(gè)要比較的文本字符串注意：1、在字符串中如果有多余的空格，會(huì)被視為不同

2、EXACT函數(shù)在判別字符串的時(shí)候，會(huì)區(qū)分英文的大小寫(xiě)，但不考慮格式設(shè)置的差異EXACT函數(shù)功能：將多個(gè)字符文本或單元格中的數(shù)據(jù)連接在一起，顯示在一個(gè)單元格中格式：CONCATENATE(text1，text2，……)text1，text2，……：需要連接的字符文本或引用的單元格注意：1、該函數(shù)最多可以附帶30個(gè)參數(shù)

2、如果其中的參數(shù)不是引用的單元格，且為文本格式的，請(qǐng)給參數(shù)加上英文狀態(tài)下的雙引號(hào)使用“&”符連接也能達(dá)到相同的效果例：18（2）CONCATENATE函數(shù)功能：實(shí)現(xiàn)替換文本字符串中的某個(gè)特定字符串格式：SUBSTITUTE（text，old_text，new_text，instance_num）text：原始內(nèi)容或是單元格地址old_text：要被替換的字符串new_text：替換old_text的新字符串如果字符串中含有多組相同的old_text時(shí)，可以使用參數(shù)instance_num來(lái)指定要被替換的字符串是文本字符串中的第幾組。如果沒(méi)有指定instance_num的值，默認(rèn)的情況下，文本中的每一組old_text都會(huì)被替換為new_text。SUBSTITUTE函數(shù)功能：將某幾位的文字以新的字符串替換。其替換功能與SUBSTITUTE函數(shù)大致類似格式：REPLACE（old_text，start_num，num_chars，new_text）old_text：原始的文本數(shù)據(jù)start_num：設(shè)置要從old_text的第幾個(gè)字符位置開(kāi)始替換num_chars：設(shè)置共有多少字符要被替換new_text：要用來(lái)替換的新字符串

例4（2）、8（1）REPLACE函數(shù)信息函數(shù)IsText

檢測(cè)一個(gè)值是否為文本日期和時(shí)間函數(shù)Year(date)4（1）Month(date)Day(date)Now()4（1）HourMinuteSecond…查找和引用函數(shù)VLOOKUP函數(shù)功能：從一個(gè)數(shù)組或表格的最左列中查找含有特定值的字段，并返回表格數(shù)組當(dāng)前行中其他列的值。V表示垂直方向。HLOOKUP函數(shù)功能：從一個(gè)數(shù)組或表格的最左列中查找含有特定值的字段，再返回同一列中某一指定單元格中的值格式：VLOOKUP（lookup_value，talbe_array，col_index_num，range_lookup）

look_value：要在數(shù)組中搜索的數(shù)據(jù)，它可以是數(shù)值、引用地址或文字字符串table_array：要搜索的數(shù)據(jù)表格、數(shù)組或數(shù)據(jù)庫(kù)col_index_num：一個(gè)數(shù)字，代表要返回的值位于table_array中的第幾列。range_lookup：一個(gè)邏輯值，如果其值為“TRUE”或被省略，則返回部分符合的數(shù)值；如果該值為“FALSE”時(shí)，函數(shù)只會(huì)查找完全符合的數(shù)值，如果找不到，則返回錯(cuò)誤值“＃N/A”。例2（1）VLOOKUP函數(shù)功能：可以用來(lái)查詢表格的第一行的數(shù)據(jù)

格式：HLOOKUP（lookup_value，table_array，row_index_num，range_lookup）look_value：要在表格第一行中搜索的值

table_array：要搜索的數(shù)據(jù)表格、數(shù)組或數(shù)據(jù)庫(kù)row_index_num：要返回的值位于table_array列中第幾行

range_lookup：一個(gè)邏輯值，如果其值為“TRUE”或被省略，則返回部分符合的數(shù)值；如果該值為“FALSE”時(shí)，函數(shù)只會(huì)查找完全符合的數(shù)值，如果找不到，則返回錯(cuò)誤值“＃N/A”。例9（1）HLOOKUP函數(shù)數(shù)據(jù)庫(kù)函數(shù)是用于對(duì)存儲(chǔ)在數(shù)據(jù)清單或數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行分析，判斷其是否符合特定的條件。根據(jù)各自函數(shù)所具有的功能不同，可分為兩大類：數(shù)據(jù)庫(kù)信息函數(shù)：直接獲取數(shù)據(jù)庫(kù)中的信息數(shù)據(jù)庫(kù)分析函數(shù)：分析數(shù)據(jù)庫(kù)的數(shù)據(jù)信息數(shù)據(jù)庫(kù)函數(shù)格式為：

函數(shù)名稱（database，field，criteria）database：構(gòu)成數(shù)據(jù)清單或數(shù)據(jù)庫(kù)的單元格區(qū)域field：指定函數(shù)所使用的數(shù)據(jù)列criteria：一組包含給定條件的單元格區(qū)域

數(shù)據(jù)庫(kù)函數(shù)數(shù)據(jù)庫(kù)信息函數(shù)

DCOUNT函數(shù)：返回列表或數(shù)據(jù)庫(kù)中滿足指定條件的記錄字段（列）中包含數(shù)值的單元格的個(gè)數(shù)DGET函數(shù)：從列表或數(shù)據(jù)庫(kù)的列中提取符合指定條件的單個(gè)值DCOUNTA函數(shù)：返回列表或數(shù)據(jù)庫(kù)中滿足指定條件的記錄字段（列）中非空單元格的個(gè)數(shù)數(shù)據(jù)庫(kù)函數(shù)數(shù)據(jù)庫(kù)函數(shù)數(shù)據(jù)庫(kù)分析函數(shù)DAVERAGE函數(shù)：計(jì)算列表或數(shù)據(jù)庫(kù)的列中滿足指定條件的數(shù)值的平均值DMAX函數(shù)：返回列表或數(shù)據(jù)庫(kù)的列中滿足條件的最大值DPRODUCT函數(shù)：返回列表或數(shù)據(jù)庫(kù)中滿足指定條件的記錄字段（列）中數(shù)值的乘積DSUM函數(shù)是用來(lái)返回列表或數(shù)據(jù)庫(kù)中滿足指定條件的記錄字段（列）中的數(shù)字之和例5（2）財(cái)務(wù)函數(shù)PMT計(jì)算貸款按年、按月的償還金額

PMT(rate,nper,pv,fv,type)rate:貸款利率nper:總貸款期限pv:貸款開(kāi)始計(jì)算時(shí)已經(jīng)入賬的款項(xiàng)或一系列未來(lái)付款當(dāng)前值的累積和（貸款金額）fv:未來(lái)值或在最后一次付款后希望得到的現(xiàn)金金額type：邏輯值，用以指定付款時(shí)間是在期初還是期末，1表示期初，0表示期末，默認(rèn)為0【例】某店主決定向銀行貸款50000元，年利息是6%，貸款年限5年，計(jì)算貸款按年償還和按月償還的金額各是多少？財(cái)務(wù)函數(shù)IPMT計(jì)算貸款每月應(yīng)付的利息額（基于固定利率、等額分期付款的方式）

IPMT(rate,per,nper,pv,fv,type)rate：各期利率per：用于計(jì)算利息數(shù)額的期數(shù)nper：總貸款（投資）期【例】17(4)財(cái)務(wù)函數(shù)FV計(jì)算投資未來(lái)收益值

FV(rate,nper,pmt,pv,type)【例】管理者為某項(xiàng)工程進(jìn)行投資，先投資50000元，年利率6%，并在接下來(lái)的5年中每年再投資5000年，那么5年后應(yīng)得到的金額是多少？例：13(4)財(cái)務(wù)函數(shù)PV計(jì)算某項(xiàng)投資所需要的金額

PV(rate,nper,pmt,fv,type)【例】某個(gè)項(xiàng)目預(yù)計(jì)每年投資15000元，投資年限為10年，其回報(bào)年利率是15%，那