公式與函數(shù)匯總

1、格式格式:Sum(number1,number2,)功能功能:返回參數(shù)所對(duì)應(yīng)的數(shù)值之和返回參數(shù)所對(duì)應(yīng)的數(shù)值之和.Average(number1,number2)對(duì)參數(shù)中的數(shù)值求平均數(shù)對(duì)參數(shù)中的數(shù)值求平均數(shù)MAX (number1,number2,) MIN (number1,number2,)COUNT (number1,number2,) 統(tǒng)計(jì)數(shù)值型數(shù)據(jù)個(gè)數(shù)統(tǒng)計(jì)數(shù)值型數(shù)據(jù)個(gè)數(shù)COUNTIF (range,criteria) 統(tǒng)計(jì)給定區(qū)域內(nèi)滿足特定條件單元格的數(shù)目，條件可以表示為統(tǒng)計(jì)給定區(qū)域內(nèi)滿足特定條件單元格的數(shù)目，條件可以表示為32,”32”,”32”,”apples”,b4IF(logi

2、cal_test,value_if_true, value_if_false) 根據(jù)邏輯計(jì)算的真假值根據(jù)邏輯計(jì)算的真假值,返回不同結(jié)果返回不同結(jié)果.若若logical_test為真為真,返回返回value_if_true,否則否則,返回返回value_if_false Replace( )REPLACE(old_text,start_num,num_chars,new_text)Old_text是要替換其部分字符的文本。Start_num是要用new_text替換的old_text中字符的位置。Num_chars是希望REPLACE使用new_text替換old_text中字符的個(gè)數(shù)。值為0時(shí)

3、,即為插入.New_text是要用于替換old_text中字符的文本。如:REPLACE(A2,6,5,“*”)從第六個(gè)字符開始，替換5個(gè)字符REPLACE(A3,3,2,“10”)用10替換2009的最后兩位REPLACE(A4,1,3,“”)用替換前三個(gè)字符 Year( ) Now( ) AND( ) OR( ) ABS( ) 數(shù)組公式可以同時(shí)進(jìn)行多個(gè)計(jì)算并返回一種或多種結(jié)果。數(shù)組公式對(duì)兩組或多組被稱為數(shù)組參數(shù)的數(shù)值進(jìn)行運(yùn)算。每個(gè)數(shù)組參數(shù)必須有相同數(shù)量的行和列。除了不能用Ctrl+Shift+Enter生成公式外，創(chuàng)建數(shù)組公式的方法與創(chuàng)建其他公式的方法相同。輸入數(shù)組公式首先必須選擇用來存放

4、結(jié)果的必須選擇用來存放結(jié)果的單元格區(qū)域單元格區(qū)域（可以是一個(gè)單元格）， RANK(number,ref,order)Number為需要找到排位的數(shù)字。Ref為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref中的非數(shù)值型參數(shù)將被忽略。Order為一數(shù)字，指明排位的方式。如果order為0（零）或省略，MicrosoftExcel對(duì)數(shù)字的排位是基于ref為按照降序排列的列表。如果order不為零，MicrosoftExcel對(duì)數(shù)字的排位是基于ref為按照升序排列的列表。如:RANK(A3,A2:A6,1) 數(shù)組公式數(shù)組公式:HLOOKUP(lookup_value,table_array,row_index

5、_num,range_lookup)Lookup_value為需要在數(shù)據(jù)表第一行中進(jìn)行查找的數(shù)值。Lookup_value可以為數(shù)值、引用或文本字符串。Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤?duì)區(qū)域或區(qū)域名稱的引用。row_index_num 為table_array中待返回的匹配值的行序號(hào)。 Range_lookup 為一邏輯值，如果range_lookup為TRUE，則table_array的第一行的數(shù)值必須按升序排列；如果range_lookup為FALSE，則table_array不必進(jìn)行排序。(指定在查找時(shí)要求精確匹配，還是大致匹配。如果為FALSE，大致匹配，如

6、果為TRUE或忽略，精確匹配）計(jì)算兩個(gè)時(shí)間的差值計(jì)算兩個(gè)時(shí)間的差值使用減號(hào)運(yùn)算符(-)求時(shí)間之間的差值，并使用TEXT函數(shù)設(shè)置時(shí)間的格式。text(b2-a1,”h:mm:ss”)“hh:mm:ss”“h:mm”“h”等MINUTE(),HOUR()SUMIF(range,criteria,sum_range)Range為用于條件判斷的單元格區(qū)域。Criteria為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為32、32、32或apples。Sum_range是需要求和的實(shí)際單元格。如:SUMIF(A2:A5,160000,B2:B5) 數(shù)據(jù)庫函數(shù)DSU

7、M(database,field,criteria)database：構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。數(shù)據(jù)庫是包含一組相關(guān)數(shù)據(jù)的列表，其中包含相關(guān)信息的行為記錄，而包含數(shù)據(jù)的列為字段。列表的第一行包含每一列的標(biāo)簽。field：指定函數(shù)所使用的列。輸入列標(biāo)簽，并將其包括在雙引號(hào)中，例如“樹齡”或“產(chǎn)量”；此外，也可以輸入代表列表中列位置的數(shù)字（不加引號(hào)）：1表示第一列，2表示第二列，依此類推。criteria：為包含指定條件的單元格區(qū)域?？梢詾閰?shù)criteria指定任意區(qū)域，只要它至少包含一個(gè)列標(biāo)簽并且在列標(biāo)簽下方至少有一個(gè)用于為該列指定條件的單元格。Istext()Istext()ABCDEF

8、樹種樹種高度高度樹齡樹齡產(chǎn)量產(chǎn)量利潤利潤高度高度=蘋果樹1016=梨樹樹種樹種高度高度樹齡樹齡產(chǎn)量產(chǎn)量利潤利潤蘋果樹182014105梨樹12121096櫻桃樹13149105蘋果樹14151075梨樹98876.8蘋果樹89645本次的主要內(nèi)容本次的主要內(nèi)容文本函數(shù)的應(yīng)用文本函數(shù)的應(yīng)用(istext)(istext)其它其它(VlookupCountblank)(VlookupCountblank)文本函數(shù)文本函數(shù)MID 返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。MID(text,start_num,n

9、um_chars)Text是包含要提取字符的文本字符串。Start_num是文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的start_num為1，以此類推。Num_chars指定希望MID從文本中返回字符的個(gè)數(shù)。如果start_num大于文本長度，則MID返回空文本()。如果start_num小于文本長度，但start_num加上num_chars超過了文本的長度，則MID只返回至多直到文本末尾的字符。如果start_num小于1，則MID返回錯(cuò)誤值#VALUE!。如果num_chars是負(fù)數(shù)，則MID返回錯(cuò)誤值#VALUE!。如:A2=FluidFlow=MID(A2,1,5)上面字符串

10、中的5個(gè)字符，從第一個(gè)字符開始(Fluid)=MID(A2,7,20)上面字符串中的20個(gè)字符，從第七個(gè)字符開始(Flow)=MID(A2,20,5)因?yàn)橐崛〉牡谝粋€(gè)字符的位置大于字符串的長度，所以返回空文本()文本函數(shù)文本函數(shù)CONCATENATE (text1,text2,.)將幾個(gè)文本字符串合并為一個(gè)文本字符串。Text1,text2,.為1到30個(gè)將要合并成單個(gè)文本項(xiàng)的文本項(xiàng)。這些文本項(xiàng)可以為文本字符串、數(shù)字或?qū)蝹€(gè)單元格的引用。說明：也可以用&（和號(hào)）運(yùn)算符代替函數(shù)CONCATENATE實(shí)現(xiàn)文本項(xiàng)的合并。Countblank(range )計(jì)算指定單元格區(qū)域中空白單元格的個(gè)

11、數(shù)。 PROPER( ) :將文本字符串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫。將其余的字母轉(zhuǎn)換成小寫。語法:PROPER(text) Text:包括在一組雙引號(hào)中的文本字符串、返回文本值的公式或是對(duì)包含文本的單元格的引用。EXACT :該函數(shù)測(cè)試兩個(gè)字符串是否完全相同。如果它們完全相同，則返回TRUE；否則，返回FALSE。函數(shù)EXACT能區(qū)分大小寫，但忽略格式上的差異。語法語法:EXACT(text1,text2)Text1 待比較的第一個(gè)字符串。Text2 待比較的第二個(gè)字符串。DGET ( ) 從列表或數(shù)據(jù)庫的列中提取符合指定條件的單個(gè)值,且是唯一的。語法:DGET(datab

12、ase,field,criteria)database：構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。數(shù)據(jù)庫是包含一組相關(guān)數(shù)據(jù)的列表，其中包含相關(guān)信息的行為記錄，而包含數(shù)據(jù)的列為字段。列表的第一行包含每一列的標(biāo)簽。field：指定函數(shù)所使用的列。輸入列標(biāo)簽，并將其包括在雙引號(hào)中，例如“樹齡”或“產(chǎn)量”；此外，也可以輸入代表列表中列位置的數(shù)字（不加引號(hào)）：1表示第一列，2表示第二列，依此類推。criteria：為包含指定條件的單元格區(qū)域。可以為參數(shù)criteria指定任意區(qū)域，只要它至少包含一個(gè)列標(biāo)簽并且在列標(biāo)簽下方至少有一個(gè)用于為該列指定條件的單元格。財(cái)務(wù)函數(shù)財(cái)務(wù)函數(shù)DB ：使用固定余額遞減法，計(jì)算一筆資產(chǎn)在

13、給定期間內(nèi)的折舊值。語法：DB(cost,salvage,life,period,month)Cost為資產(chǎn)原值。Salvage為資產(chǎn)在折舊期末的價(jià)值（也稱為資產(chǎn)殘值）。Life為折舊期限（有時(shí)也稱作資產(chǎn)的使用壽命）。Period為需要計(jì)算折舊值的期間。Period必須使用與life相同的單位。Month為第一年的月份數(shù)，如省略，則假設(shè)為12。SYD 返回某項(xiàng)資產(chǎn)按年限總和折舊法計(jì)算的指定期間的折舊值。語法：SYD(cost,salvage,life,per)Cost為資產(chǎn)原值。Salvage為資產(chǎn)在折舊期末的價(jià)值（也稱為資產(chǎn)殘值）。Life為折舊期限（有時(shí)也稱作資產(chǎn)的使用壽命）。Per為期間

14、，其單位與life相同SLN ：返回某項(xiàng)資產(chǎn)在一個(gè)期間中的線性折舊值。語法：SLN(cost,salvage,life)Cost為資產(chǎn)原值。Salvage為資產(chǎn)在折舊期末的價(jià)值（也稱為資產(chǎn)殘值）。Life為折舊期限（有時(shí)也稱作資產(chǎn)的使用壽命）。示例零存整取函數(shù)零存整取函數(shù)FV( )假如你每月初向銀行存入現(xiàn)金假如你每月初向銀行存入現(xiàn)金500元，如果年利元，如果年利2.15%（按月計(jì)息，即月息（按月計(jì)息，即月息2.15%/12）。如果你想知道）。如果你想知道5年后的存款年后的存款總額是多少，可以使用公式總額是多少，可以使用公式“=FV（2.15%/12，60，-500，0，1）”計(jì)算，其結(jié)果為￥計(jì)

15、算，其結(jié)果為￥31，698.67。 式中的2.15%/12為月息；60為總的付款時(shí)間，在按月儲(chǔ)蓄的情況下為儲(chǔ)蓄月份的總和；-500為每月支出的儲(chǔ)蓄金額（-表示支出）；0表示儲(chǔ)蓄開始時(shí)賬戶上的金額，如果賬戶上沒有一分錢，則為0否則應(yīng)該輸入基礎(chǔ)金額；1表示存款時(shí)間是月初還是月末，1為月初0或忽略為月末。 例如：假如某人兩年后需要一筆比較大的學(xué)習(xí)費(fèi)用支出，計(jì)劃從現(xiàn)在起每月初存入例如：假如某人兩年后需要一筆比較大的學(xué)習(xí)費(fèi)用支出，計(jì)劃從現(xiàn)在起每月初存入2000元，如果按年利元，如果按年利2.25%，按月計(jì)，按月計(jì)息（月利為息（月利為2.25%/12），那么兩年以后該賬戶的存款額會(huì)是多少呢？），那么兩年以

16、后該賬戶的存款額會(huì)是多少呢？公式寫為：公式寫為：FV(2.25%/12, 24,-2000,0,1)Pmt()Ipmt() PMT函數(shù),FV函數(shù),PV函數(shù)有什么區(qū)別PMT（ ）功能：基于固定利率及等額分期付款方式，返回貸款的每期付款額。格式：PMT(rate，nper，pv，fv，type)參數(shù)：Rate貸款利率，Nper為總投資期(即該項(xiàng)投資的付款期總數(shù))，Pv為現(xiàn)值(也稱為本金)，fv為最后一次付款后剩余的貸款金額，如果省略fv，則認(rèn)為它的值為零，Type指定各期的付款時(shí)間是在期初還是期末(1為期初。0為期末)。應(yīng)用實(shí)例：購房還款金額假如你為購房貸款十萬元，如果年利率為7%，每月末還款。采

17、用十年還清方式時(shí)，月還款額計(jì)算公式為“=PMT（7%/12，120，-100000）”。其結(jié)果為￥-1，161.08，就是你每月須償還貸款1161.08元。FV( ) 功能：基于固定利率及等額分期付款方式，返回某項(xiàng)投資的未來值。格式：FV(rate，nper，pmt，pv，type)參數(shù)：Rate為各期利率，Nper為總投資期(即該項(xiàng)投資的付款期總數(shù))，Pmt為各期所應(yīng)支付的金額，Pv為現(xiàn)值(即從該項(xiàng)投資開始計(jì)算時(shí)已經(jīng)入帳的款項(xiàng)，或一系列未來付款的當(dāng)前值的累積和，也稱為本金)，Type為數(shù)字0或1(0為期末，1為期初)。.PV( )功能：返回投資的現(xiàn)值(即一系列未來付款的當(dāng)前值的累積和)，如借入方的借入款即為貸出方貸款的現(xiàn)值。格式：PV(rate，nper，pmt，fv，type)參數(shù)：Rate為各期利率，Nper為總投資(或貸款)期數(shù)，Pmt為各期所應(yīng)支付的金額，F(xiàn)v為未來值，Type指定各期的付款時(shí)間是在期初還是期末(1為期初。0為期末)。Ipmt()函數(shù)用來計(jì)算等額還款條件下每期應(yīng)償還的利息部分格式:IPMT(rate,per,nper,pv,fv,type) Rate 為各期利率。 Per 用于計(jì)算其利息數(shù)額的期數(shù)，必須在 1 到 nper 之間。