《Ｅxcel在財務(wù)中的應(yīng)用》 函數(shù)　復(fù)習(xí)版

《Ｅxcel在財務(wù)中的應(yīng)用》常用函數(shù)MAXMAX(number1,number2,...)MINMIN(number1,number2,...)HLOOKUPHLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUPVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)LOOKUPLOOKUP(lookup_value,lookup_vector,[result_vector])MATCHMATCH(lookup_value,lookup_array,[match_type])INDEXINDEX(array,row_num,[column_num])IFIF(logical_test,value_if_true,value_if_false)嵌套公式的編輯技巧反映實際與目標(biāo)的偏離程度（實際值-目標(biāo)值）/目標(biāo)值[1-（實際值-目標(biāo)值）/目標(biāo)值]將反正指標(biāo)調(diào)整為正向指標(biāo)ABSABS(number)AVERAGEAVERAGE(number1,[number2],...)AVERAGEIFAVERAGEIF(range,criteria,[average_range])SumSum(number1,number2,….)Sum(A2:A10,C2:C10)Sum(A2:A10)Sum(A1,A2,A3,B1,B2,B3)ALT+“=”SUMIFSUMIF(range,criteria,[sum_range])SUMPRODUCT

SUMPRODUCT（array1，[array2]，[array3]，...）COUNTCOUNT(value1,[value2],...)COUNTA(value1,[value2],...)COUNTIF(要檢查哪些區(qū)域?要查找哪些內(nèi)容?)FINDFIND(find_text,within_text,[start_num])TRIM清除前后空格TRIM(text)EXACTEXACT(text1,text2)RANKRANK(number,ref,[order])DAYSDAYS(end_date,start_date)DATEDIF計算期間內(nèi)的年數(shù)、月數(shù)、天數(shù)

DATEDIFDay/Month/YearDAY(serial_number)REPLACEREPLACE(old_text,start_num,num_chars,new_text)INT

Int(number)And/OrAND(A2>1,A2<100)CONCATENATE

CONCATENATE(text1,[text2],...)LEN

LEN(text)TEXTTEXT(value,format_text)TEXT(0.285,"0.0%")TEXT(YEAR(A5),"[dbnum2]####年")SUBSTITUTESUBSTITUTE(text,old_text,new_text,[instance_num])REPLACEREPLACE(old_text,start_num,num_chars,new_text)TRUNCTRUNC(number,[num_digits])。MIDMID(text,start_num,num_chars)RIGHTRIGHT(text,[num_chars])LEFTLEFT(text,[num_chars])INDIRECTINDIRECT(ref_text,[a1])SQRT

SQRT(number)EFFECTEFFECT(nominal_rate,npery)名義利率每年復(fù)利期數(shù)NOMINALNOMINAL(effect_rate,npery)實際利率每年復(fù)利期數(shù)ACCRINTMACCRINTM(issue,settlement,rate,par,basis)

發(fā)行日到期日年利率票面面值日計數(shù)基準(zhǔn)DATE(年，月，日）ACCRINTACCRINT(issue,first_interest,settlement,rate,par,frequency,[basis],[calc_method])ＲOUNDROUND(number,num_digits)為需要進(jìn)行四舍五入的數(shù)字Ｒoundup/Ｒoundown/ＭroundROUNDUP(number,num_digits)ROUNDDOWN(number,num_digits)MROUND(number,multiple)CEILING向上舍入為指定的倍數(shù)

CEILING(number,significance)FVFV(rate,nper,pmt,pv,type)PVPV(rate,nper,pmt,Fv,type)PMTPMT(rate,nper,pv,fv,type)PPMTPPMT(rate,per,nper,pv,fv,type)IPMTIPMT(rate,per,nper,pv,fv,type)RATERATE(nper,pmt,pv,fv,type,guess)NPERNPER(ra