「收藏」常用工作EXCEL公式、财税公式、上市分析公式大全

第一：财务常用EXCEL公式

账龄分析公式

用lookup函数可以划分账龄区间

=LOOKUP(D2,G$2:H$6) 如果不用辅助区域，可以用常量数组 =LOOKUP(D2,{0,"小于30天";31,"1~3个月";91,"3~6个月";181,"6-1年";361,"大于1年"})

1. 2

屏蔽错误值公式

把公式产生的错误值显示为空 公式：C2

=IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。

2. 3

完成率公式

如下图所示，要求根据B的实际和C列的预算数，计算完成率。 公示：E2 =IF(C3<0,2-B3/C3,B3/C3)

3. 4

同比增长率公式

如下图所示，B列是本年累计，C列是去年同期累计，要求计算同比增长率。 公示：E2 =(B2-C2)/IF(C2>0,C2,-C2)

4. 5

金额大小写公式

=TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整")

5. 6

单条件查找公式

C11 =VLOOKUP(B11,B3:F7,4,FALSE)

6. 7

多条件查找公式

C35 =Lookup(1,0/((B25:B30=C33)*(C25:C30=C34)),D25:D30)

7. 8

单条件求和公式

F2 =SUMIF(A:A,E2,C:C)

8. 9

多条件求和公式

C11=Sumifs(c2:c7,a2:a7,a11,b2:b7,b11) 13

9. 10

隔列求和公式

H3: =SUMIF($A$2:$G$2,H$2,A3:G3) 如果没有标题，那只能用稍复杂的公式了。 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

第二：财务财税核算常用公式

偿债能力分析

1、流动比率=流动资产/流动负债*100%。指标越高，企业流动资产流转越快，偿还流动负债能力越强。国际公认200%，我国150%较好。

2、速动比率=速动资产(流动资产-存货)/流动负债*100%。国际标志比率100%我国90%左右。

3、资产负债率=负债总额/资产总额*100%。指标越高负债程度高，经营风险大，能以较低的资金成本进行生产经营。保守比率不高于50%，国际公认较好60%。

4、长期资产适合率=(所有者权益 长期负债)/(固定资产 长期投资)*100%

资本状况分析

1、资本保值增值率=扣除客观因素后的年末所有者权益/年初所有者权益*100%，指标越高，资本保全情况越好，企业发展潜力越大，债权人利益越有保障。

2、资本积累率=本年所有者权益增长额/年初所有者权益*100%，指标越高，所有者权益增长越快，资本积累能力越强，保全情况好，持续发展能力越大。

盈利能力分析

1、主营业务毛利率=毛利(主营收入-主营成本)/主营业务收入*100%，介于20%-50%之间，一般相对合理稳定，流动性强的商品，毛利率低。设计新颖的特殊商品(时装)毛利率高。

2、主营业务净利率=净利润/主营业务收入*100%，反映企业基本获利能力。

3、主营业务成本率=主营业务成本/主营业务收入*100%。

4、营业费用率=营业费用/主营业务收入*100%。

5、主营业务税金率=主营业务税金及附加/主营业务收入*100%。

6、资产净利率=税后净利/平均总资产*100%=主营业务净利率*总资产周转率。指标高低于企业资产结构，经验管理水平有密切关系。

7、净资产收益率=净利润/平均所有者权益*100%。反映投资者投资回报率，股东期望平均年度净资产收益率能超过12%

8、不良资产比率=年末不良资总额/年末资产总额*100%

9、资产损失比率=待处理资产损失净额/年末资产总额*100%

10、固定资产成新率=平均固定资产净值/平均固定资产原值*100%

11、流动比率=流动资产/流动负债*100%

12、速动比率=速动资产/流动负债*100%

13、资产负债率=负债总额/资产总额*100%

14、长期资产适合率=(所有者权益 长期负债)/(固定资产 长期投资)*100%

15、资本保值增值率=扣除客观因素后的年末所有者权益/年初所有者权益*100%

16、资本积累率=本年所有者权益增长额/年初所有者权益*100%

17、主营业务毛利率=毛利/主营业务收入*100%

18、主营业务净利率=净利润/主营业务收入*100%

19、营业费用率=营业费用/主营业务收入*100%

20、资产净利率=税后净利/平均总资产*100%=主营业务净利率*总资产周转率

21、净资产收益率=净利润/平均所有者权益*100%

22、管理费用率=管理费用/主营业务收入*100%

23、财务费用率=财务费用/主营业务收入*100%

24、成本、费用利润率=利润总额/(主营业务成本 期间费用)*100%

25、销售收现比=销售收现/销售额

26、营运指数=经营现金净流量/经营所得现金

27、现金比率=现金余额/流动负债*100%

28、现金流动负债比=经营活动净现金流量/流动负债*100%

29、现金债务总额比=经营活动净现金流量/总负债*100%

30、销售现金比率=经营现金净流量/销售额*100%

31、应收帐款周转率(次数)=赊销收入净额/应收帐款平均余额

32、应收帐款周转天数=天数/应收帐款周转次数=计算期天数*应收帐款平均余额/赊销收入净额

销售收入净额=销售收入-现销收入-销售折扣与折让

33、存货周转率=销售成本/平均存货

34、存货周转天数=计算期天数/存货周转率

35、流动资产周转次数(率)=销售收入净额/流动资产平均余额

36、流动资产周转天数=计算期天数/流动资产周转次数

37、总资产周转率=销售收入净额/平均资产总额

38、总资产周转天数=计算期天数/总资产周转率

39、已获利息倍数=(税前利润总额 利息支出)/利息支出

40、总资产报酬率=(利润总额 利息支出)/平均资产总额

营运能力分析

1、应收帐款周转率(次数)=赊销收入净额/应收帐款平均余额，指标高说明收帐迅速，账龄期限短，减少企业收帐费用和坏账损失。

2、应收帐款周转天数=天数/应收帐款周转次数=计算期天数*应收帐款平均余额/赊销收入净额

销售收入净额=销售收入-现销收入-销售折扣与折让

3、存货周转率=销售成本/平均存货。一般情况周转速度越快，变现速度越快，占用存货资金少，占用相同数额的存货而实现的销货成本大。

4、存货周转天数=计算期天数(年360天)/存货周转率

5、流动资产周转次数(率)=销售收入净额/流动资产平均余额，指标高表明企业流动资产周转速度快，利用效果好。

6、流动资产周转天数=计算期天数/流动资产周转次数

7、总资产周转率=销售收入净额/平均资产总额。周转速度快，企业的营运能力强;指标低，说明收入不足，或资产闲置浪费。

8、总资产周转天数=计算期天数/总资产周转率

9、已获利息倍数=(税前利润总额 利息支出)/利息支出

10、总资产报酬率=(利润总额 利息支出)/平均资产总额

综合偿债能力

已获利息倍数=(税前利润总额 利息支出)÷利息支出

盈利能力：

主营业务净利率=(净利润÷主营业务收入)×100%

资产净利率 =(净利润÷平均总资产)×100%=主营业务净利率*总资产周转率

净资产收益率=(净利润÷平均净资产总额)×100%

另：总资产报酬率=(利润总额 利息支出)÷平均资产总额

营运能力：

应收帐款周转率=主营业务收入÷应收帐款平均余额

(高表明企业收帐迅速，帐龄期限短，可减少收帐费用和坏帐损失，相对增加企业流动资产的投资收益，说明资产流动性大，变现快短期偿债能力强)

存货周转率=主营业务成本÷平均存货余额

(高表明企业资产由于销售顺畅而具有较高的流动性存货转现速度快存货占用水平低，过高 存货不足，产品脱销)

(慢：必然占用在量的资金，造成资金浪费，营运困难)

流动资产周转率=主营业务收入÷平均资产总额

总资产周转率=主营业务收入÷平均资产总额

(过低：收入不足，资产闲置,浪费)

发展能力：

销售增长率=(本年主营业务收入-上年主营业务收入)÷上年主营业务收入

资本积累率=[(年末所有者权益-年初所有者权益)÷年初所有者权益]×100%

主营业务利润 = 主营业务收入-主营业务成本-主营业务税金及附加

营业利润 = 主营业务利润 其他业务利润-期间费用

利润总额 = 营业利润 投资收益 补贴收入 营业外收入-营业外支出

净利润 = 利润总额-所得税

财务报表分析的应用

(一)杜帮财务分析体系

1、权益乘数

权益乘数=1÷(1-资产负债率)

2、权益净利率

权益净利率=资产净利率×权益乘数=销售净利率×资产周转率×权益乘数

(二)上市公司财务比率

1、每股收益

每股收益=净利润÷年末普通股份总数=(净利润-优先股股利)÷(年度股份总数-年度末优先股数)

2、市盈率

市盈率(倍数)=普通股每股市价÷普通股每股收益

3、每股股利

每股股利=股利总额÷年末普通股股份总数

4、股票获利率

股票获利率=普通股每股股利÷普通股每股市价×100%

5、股利支付率

股利支付率=(普通股每股股利÷普通股每股净收益)×100%

6、股利保障倍数

股利保障倍数=普通股每股净收益÷普通股每股股利=1÷股利支付率

7、每股净资产

每股净资产=年度末股东权益÷年度末普通股数

8、市净率

市净率(倍数)=每股市价÷每股净资产

(三)现金流量分析

1、流动性分析

(1)现金到期债务比

现金到期债务比=经营现金流量净额÷本期到期的债务

(2)现金流动负债比

现金流动负债比=经营现金流量净额÷流动负债

(3)现金债务总额比

现金债务总额比=经营现金流量净额÷债务总额

2、获取现金能力分析

(1)销售现金比率

销售现金比率=经营现金流量净额÷销售额

(2)每股经营现金流量净额

每股经营现金流量净额=经营现金流量净额÷普通股股数

(3)全部资产现金回收率

全部资产现金回收率=经营现金流量净额÷全部资产×100%

3、财务弹性分析

(1)现金满足投资比率

现金满足投资比率=近5年经营现金流量净额之和÷近5年资本支出、存货增加、现金股利之和

(2)现金股利保障倍数

现金股利保障倍数=每股经营现金流量净额÷每股现金股

第三：实务中上市公司分析常用公式

上市公司分析，常用的指标有以下4类12个：

（1）反映短期偿债能力的指标

流动比率＝流动资产÷流动负债；速动比率＝（流动资产－存货）÷流动负债

（2）反映长期偿债能力的指标

负债比率＝负债总额÷资产总额；长期负债比率＝长期负债÷资产总额

（3）反映资产变现能力的指标

应收账款周转次数＝赊销收入÷平均应收账款余额；存货周转次数＝销售成本÷平均存货余额

（4）反映盈利能力的指标

销售报酬率＝销售利润÷销售收入；净利润＝税后利润÷销售收入

投资报酬率＝税后净利÷平均资产总额

净值报酬率＝税后净利÷股本

除以上这些基本指标外，投资者特别关心的还有：

留存盈利比例＝（税后净利－全部股息）÷税后净利

每股收益＝(税后净利－优先股股息)÷发行在外的普通股总股数

市盈率＝股票市价÷每股收益；股息盈利率＝每股年股息÷每股市价

每股净资产＝净资产总额÷股本；股利支付率＝每股股利÷每股收益

净资产收益率＝税后净利润÷净资产