（高职)20200201《Ｅxcel在财务中的应用》 教材函数　复习版ppt课件.pptx

《（高职)20200201《Ｅxcel在财务中的应用》 教材函数　复习版ppt课件.pptx》由会员分享，可在线阅读，更多相关《（高职)20200201《Ｅxcel在财务中的应用》 教材函数　复习版ppt课件.pptx（57页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、20200201xcel在财务中的应用 教材函数复习版xcelxcel在财务中的应用常用在财务中的应用常用函数函数MAXMAXMAX(number1,number2,.)MAX(number1,number2,.)MINMINMIN(number1,number2,.)MIN(number1,number2,.)HLOOKUPHLOOKUPHLOOKUP(lookup_value,table_array,row_index_num,range_lookup)HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUPVL

2、OOKUPVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)LOOKUPLOOKUPLOOKUP(lookup_value, lookup_vector, result_vector)MATCHMATCHMATCH(lookup_value, lookup_array, match_type)MATCH(lookup_value, lookup_array, match_type)INDEXINDEXIND

3、EX(array, row_num, column_num)INDEX(array, row_num, column_num)IFIFIF(logical_test,value_if_true,value_if_false)IF(logical_test,value_if_true,value_if_false)ABSABSABS(number)AVERAGEAVERAGEAVERAGE(number1, number2, .)AVERAGEIFAVERAGEIFAVERAGEIF(range, criteria, average_range)SumSumSum(number1,number2

4、,.)Sum(number1,number2,.)SumSum(A2:A10, C2:C10)(A2:A10, C2:C10)Sum(A2:A10)Sum(A2:A10)SumSum(A1,A2,A3,B1,B2,B3)(A1,A2,A3,B1,B2,B3)ALT+ “ALT+ “=”=”SUMIFSUMIFSUMIF(range, criteria, sum_range)SUMPRODUCTSUMPRODUCT SUMPRODUCT SUMPRODUCT （array1array1，array2array2，array3array3，.）COUNTCOUNTCOUNT(value1, val

5、ue2, .)COUNTA(value1, value2, .)COUNTIF(要检查哪些区域? 要查找哪些内容?)FINDFINDFIND(find_text, within_text, start_num)TRIMTRIM清除前后空格清除前后空格TRIM(text)EXACTEXACTEXACT(text1, text2)RANKRANKRANK(number,ref,order)DAYSDAYSDAYS(end_date, start_date)DATEDIFDATEDIF计算期间内的年数、月数、天数计算期间内的年数、月数、天数DATEDIFDay/Month/YearDay/Month

6、/YearDAY(serial_number)DAY(serial_number)REPLACEREPLACEREPLACE(old_text, start_num, num_chars, new_text)INTInt( number )Int( number )And/OrAnd/OrAND(A21,A21,A2100)CONCATENATECONCATENATE(text1, text2, .)CONCATENATE(text1, text2, .)LENLEN(text)LEN(text)TEXTTEXTTEXT(value, format_text)TEXT(value, forma

7、t_text)TEXT(0.285,0.0%0.0%)TEXT(YEAR(A5),dbnum2#TEXT(YEAR(A5),dbnum2#年)SUBSTITUTESUBSTITUTESUBSTITUTE(text, old_text, new_text, instance_num)REPLACEREPLACEREPLACE(old_text, start_num, num_chars, new_text)REPLACE(old_text, start_num, num_chars, new_text)TRUNCTRUNCTRUNC(number, num_digits)。MIDMIDMID(t

8、ext, start_num, num_chars)MID(text, start_num, num_chars)RIGHTRIGHTRIGHT(text,num_chars)LEFTLEFTLEFT(text, num_chars)LEFT(text, num_chars)INDIRECTINDIRECTINDIRECT(ref_text, a1)SQRTSQRT(number)SQRT(number)EFFECTEFFECTEFFECT(nominal_rate, npery)名义利率每年复利期数NOMINALNOMINALNOMINAL(effect_rate, npery)实际利率每年

9、复利期数ACCRINTMACCRINTM(issue,settlement,rate,par,basis)发行日到期日年利率票面面值日计数基准 DATE(年，月，日）ACCRINTACCRINTACCRINT(issue, first_interest, settlement, rate, par, frequency, basis, calc_method)OUNDOUNDROUND(number, num_digits)为需要进行四舍五入的数字oundup/oundup/oundown/oundown/roundroundROUNDUP(number, num_digits)ROUNDUP

10、(number, num_digits)ROUNDDOWN(number, num_digits)ROUNDDOWN(number, num_digits)MROUND(number, multiple)MROUND(number, multiple)CEILINGCEILING向上舍入为指定的倍数向上舍入为指定的倍数CEILING(number, significance)CEILING(number, significance)FVFVFV(rate,nper,pmt,pv,type)PVPVPV(rate,nper,pmt,Fv,type)PMTPMTPMT(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)NPERNPERNPER(rate,pmt,pv,fv,type)NPER(rate,pmt,pv,fv,type)NPVNPVNPV(rate,NPV(rate,value1value1,value2, .),value2, .)NPV 投资开始于 value1 现金流所在日期的前一期IRRIRRIRR(values, guess)可以是数组公式录入