《Ｅxcel在财务中的应用》 函数　复习版

《Ｅxcel在财务中的应用》常用函数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)嵌套公式的编辑技巧反映实际与目标的偏离程度（实际值-目标值）/目标值[1-（实际值-目标值）/目标值]将反正指标调整为正向指标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(要检查哪些区域?要查找哪些内容?)FINDFIND(find_text,within_text,[start_num])TRIM清除前后空格TRIM(text)EXACTEXACT(text1,text2)RANKRANK(number,ref,[order])DAYSDAYS(end_date,start_date)DATEDIF计算期间内的年数、月数、天数

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)名义利率每年复利期数NOMINALNOMINAL(effect_rate,npery)实际利率每年复利期数ACCRINTMACCRINTM(issue,settlement,rate,par,basis)

发行日到期日年利率票面面值日计数基准DATE(年，月，日）ACCRINTACCRINT(issue,first_interest,settlement,rate,par,frequency,[basis],[calc_method])ＲOUNDROUND(number,num_digits)为需要进行四舍五入的数字Ｒoundup/Ｒoundown/ＭroundROUNDUP(number,num_digits)ROUNDDOWN(number,num_digits)MROUND(number,multiple)CEILING向上舍入为指定的倍数

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