Excel财务应用讲义

Excel高效财务数据分析及财务管理运用袁志刚2021目录销售管理与分析进销存管理财务模型分析薪酬计算与分析费用分析预算管理融资分析往来分析报表设计链接数据库2袁志刚1.1销售周报分析计算周次函数Weeknum=weeknum(日期，2〕2表示一周从星期一开场根据周次统计收入Sumifs函数多条件求和函数，与之同类的还有countifs,averageifs除了sumifs，sumproduct函数也可用于多条件求和=sumifs(求和区域，条件区域1，条件1，条件区域2，条件2,…)制造折线图趋势分析3袁志刚1.2客户分布分析Frequency函数自定义统计区间设定参照值，取区间最小值作为参照值选中与统计区间数量一样的延续单元格输入frequency函数

=frequency(数据区域，参照值区域〕同时按下ctrl+shift+enter键最后按下的组合键将创建一个数组公式不可以单独删除数组公式中的任一公式，需求全选后才可删除构成比例分析绘制饼图设置数据标签4袁志刚1.3客户数变动分析生成透视表选择日期放入行区域右键-创建组，选择年在透视表外输入公式计算客户增长的比例制造图表光标放在透视表内，点击数据透视表工具-选项-数据透视图生成透视图光标放在客户增长率表内，选择“插入〞-折线图客户增量分析创建透视表，将日期放入行区域，将销量字段放入数值区域右键-创建组，选择年和月光标放在销量列，邮件-值汇总根据-选择计数数据透视表工具-选项-数据透视图，生成折线图5袁志刚1.4基于地图的数据分析作用笼统展现基于地理位置的区域数据分析。容易了解，印象深化，效果出众。步骤运用地图矢量图，为每一个区域定义称号，普通是该区域称号的拼音。选中区域，在称号框内输入即可。输入宏，开发工具-visualbasic,双击thisworkbook,在右侧代码窗口输入以下代码：Subuser_click(region_name)ActiveSheet.Shapes(Range("m1").Value).Fill.ForeColor.SchemeColor=xlThemeColorDark1Range("m1").Value=region_nameActiveSheet.Shapes(region_name).Fill.ForeColor.SchemeColor=xlThemeColorAccent6EndSub6袁志刚1.4基于地图的数据分析步骤为每个区域图形指定宏，选中该区域图形，右键-指定宏，输入以下宏称号：

‘thisworkbook.user_click(“区域称号〞)‘

其中，区域称号为为区域图形定义的称号在m1单元格中输入一个区域的称号代码，比如北京地域的代码为beijing,此时即可点击区域实现变色效果。预备好原始数据写一个vlookup函数对地图上选中的区域数据进展查询，该查询的索引字段为m1单元格，该单元格可以恣意指定。根据查询结果制造图表7袁志刚1.5基于地图的色阶分析步骤预备地图矢量文件为每一个区域图形定义称号，选中图形，在称号框内输入称号，称号设为该区域的拼音为数据设置区间，假设分为以下5个区间：为上面5种颜色所在单元格定义称号，分别设为code1-5为这5个区间设置参照值和颜色代码：8袁志刚1.5基于地图的色阶分析步骤为以下图中的3个单元格定义称号，分别为province,vbdata,vbcode,其中数据和颜色代码为vlookup查询公式，根据省份在数据表中查询数据和所属颜色代码制造控件按钮，开发工具-控件-插入-按钮-指定宏-新建，输入以下宏代码：Sub按钮35_单击()Fori=3To34Range("province").Value=Range("data!a"&i).ValueActiveSheet.Shapes(Range("province").Value).SelectSelection.ShapeRange.Fill.ForeColor.RGB=Range(Range("vbcode").Value).Interior.ColorNextiRange("f8").SelectEndSub9袁志刚1.6复合增长率概念CAGR〔CompoundAnnualGrowthRate〕一个目的〔比如销售收入或净利润，投资报答等〕在特定时期内的年度增长率是较长时期内的测算，忽略个别年度的动摇，将增长率平滑，反映目的的整体表现。公式=〔当前数值/基期数值〕^(1/年数〕-110袁志刚1.7波士顿矩阵分析简介波士顿矩阵〔BCGMatrix〕，又称市场增长率-相对市场份额矩阵，是由美国著名的管理学家、波士顿咨询公司开创人布鲁斯·亨德森于20世纪60年代末期首创的。布鲁斯以为决议产品/业务构造的要素可分为2类，市场吸引力与企业实力。在反映市场引力的众多目的〔销售增长率、目的市场容量、竞争对手强弱及利润高低〕中，销售增长率是最具代表性的综合目的；而在反映企业实力的目的，如市场占有率，技术、设备、资金利用才干中，市场占有率是最能直接显示出企业竞争实力的目的。因此，波士顿矩阵选取的纵坐标与横坐标分别是“销售增长率〞及“市场占有率〞。由以上2个要素相互作用，产生4个不同的象限，划分出4类性质的产品/业务：销售增长率和市场占有率都较高的产品/业务〔简称“明星〞〕；销售增长率和市场占有率都较低的产品/业务〔简称“瘦狗〞〕；销售增长率高而市场占有率低的产品/业务〔简称“问号〞〕；销售增长率低而市场占有率高的产品/业务〔简“现金牛〞〕。 11袁志刚问题型业务现金型业务瘦狗型业务明星型业务高高低低市场增长市场份额1.7波士顿矩阵分析1.7波士顿矩阵分析利用散点图制造波士顿矩阵选中表格的数据列，制造散点图，行坐标为市场占有率，纵坐标为增长率选中行坐标轴，设置坐标轴格式，勾选逆序刻度值，将纵坐标交叉选项改为坐标轴值：0.5，将刻度线类型与坐标轴标签设为无选中纵坐标轴，除了不勾选逆序刻度值，其他设置与行坐标轴一样Excel散点图的一个问题是无法为数据添加标签，可以运用一个第三方程序处理：xychartlabels安装此程序后excel会出现一个新的选项卡xychartlabels,选择其中的addlabels命令在其中的selectalabelrange选项中，选择数据表中的表前列即可最后可为坐标轴添加标签或为4个象限添加图片阐明。13袁志刚2.1存货ABC管理概念又称巴雷特分析法，按照价值和库存数量的高低，根据一定的分类规范，确定关键的少数和次要的多数。其分清主次，抓住重点的思想广泛运用于存货管理与本钱管理。A类物资是指种类少、实物量少而价值高的物资，其本钱金额约占70%，而实物量不超越20%。C类物资是指种类多、实物量多而价值低的物资，其本钱金额约占10％，而实物量不低于50％。B类物资介于A类、C类物资之间。其本钱金额约占20％，而实物量不超越30％。管理表制造步骤计算存货金额及数量所占比例，并按金额降序陈列计算存货金额和数量所占累计比例14袁志刚2.1存货ABC管理管理表制造步骤根据一定规范，进展ABC分类。普通将关键的少数作为A类，次要的多数作为B和C类，对A类存货进展重点管理。制造帕累托图以意大利经济学家pareto命名选择存货称号，金额，累计金额百分比3列制造柱形图选择图表工具-规划，左上角点开图表元素选择框，在其中选择累计金额百分比点击设置所选内容格式按钮，将“系列绘制在〞选项由“主坐标轴〞改为“次坐标轴〞将累计金额百分比数据系列的图表类型改为折线图选中副坐标轴，将最大值改为1选中金额数据系列，右键-设置数据系列格式，将分类间距改为0%15袁志刚2.2进销存表设计“表〞功能表格与数据区域表内的数据可以独立和更方便的进展管理，比如自动扩展数据与公式，添加汇总行，挑选，运用表格格式等。创建与编辑将光标放在表中，选择“插入〞-表格经过顶部的表格工具，可以对表格进展各种编辑为表格改换款式：表格工具-表格款式，点击即运用新的款式将表格转化为区域：表格工具-转化为区域在表格最后一行下面输入数据，即可自动扩展表格范围16袁志刚2.3进销存管理数据表设计进出字段用if函数控制显示与否

=if(b2=〞〞,〞〞,c2)Vlookup查询产品价钱

=vlookup(产品称号，根底信息表，价钱所在的列，查询方式〕统计库存创建透视表，行设置为产品，列设置为“进出〞，数值为数量数据透视表工具-域、工程和集-计算项输入称号“库存〞，公式设置为：=进货-发货标识低于平安库存的存货：开场-条件格式-新建规那么-突出显示单元格规那么17袁志刚3.1杠杆平衡模型分析运营杠杆=边沿奉献/〔边沿奉献-固定本钱〕假设运营杠杆系数为2，那么阐明销量增长10%，息税前利润增长〔2×10%〕20%，即高度的运营杠杆，表示销货量的小幅变动，会引起利润的较大变动。一家公司的固定本钱愈高，其企业风险也愈高。故有大量固定本钱的公司，即具有较高的营业杠杆。企业普通可经过添加销售额，降低单位变动本钱和固定本钱等措施来降低运营杠杆和运营风险。财务杠杆是指由于债务的存在而导致每股利润的变动大于息税前利润变动的杠杆效应。财务杠杆系数=息税前利润/(息税前利润-资本总额*负债比例*利率〕18袁志刚3.1杠杆平衡模型分析复合杠杆复合杠杆是指由于固定本钱和固定财务费用的存在而导致的普通股每股利润变动率大于产销量变动率的杠杆效应。复合杠杆系数=运营杠杆系数*财务杠杆系数杠杆平衡模型当其他要素变动时，测算销量的变动文件-选项-自定义功能区，在主选项卡勾选“开发工具〞开发工具-控件-插入，选择“数值调理钮〞，为变量添加调理钮控件右键选择数值调理钮，设置控件格式，设置单元格链接，并运用链接公式使得该单元格链接可以控制变量值19袁志刚3.2盈亏平衡分析分析内容销量平衡分析单价平衡分析变动本钱平衡分析固定本钱平衡分析控件运用开发工具-控件-插入-滚动条右键-单元格链接输入变量计算公式：=当前值*(〔1+单元格链接/50-1)/2)，此公式使得变量以每次1%的幅度变化20袁志刚4.1加班计算前往星期几=Weekday(日期，2)判别加班类型=IF(ISERROR(VLOOKUP(B4,$J$2:$J$11,1,FALSE)),IF(OR(E4=6,E4=7),"周末加班","任务日加班"),"节假日加班")提取不反复姓名=INDEX(A$2:A$32,MATCH(0,COUNTIF(M$1:M1,A$2:A$32),0))统计加班合计数=SUMPRODUCT(($A$2:$A$32=M2)*($H$2:$H$32))21袁志刚4.2薪酬计算与分析奖金匹配hlookup：程度查询设置奖金比例表，其中的参照值应该取区间的最小值=hlookup(某销售员奖金，奖金比例表，比例所在列，查询方式〕

其中查询方式应该设置为1，意为模糊查询。工资变动分析将工资与销售收入数据转化为指数，即基期为100，其他期间为：当期数据/基期数据*100制造折线图计算工资与销售收入相关性：=correl(工资，销售收入〕计算工资与销售收入各自的复合增长率

=〔当期/基期〕^(1/年数〕-1

22袁志刚5.1折旧费用计算曾经计提折旧月份计算=datedif(启用资产日期，today()，〞m〞)其中m表示月份，还可以是y、d，分别表示年，天。折旧额计算直线法

=sln(原值，残值，运用年限〕双倍余额递减法

=ddb(原值，残值，运用年限，第几期〕年数总和法

=syd(原值，残值，运用年限，第几期〕23袁志刚5.2混合本钱分解制造散点图添加趋势线右键-添加趋势线，选择线性勾选“显示公式〞和“显示R平方值〞选项公式Y=7.85x-6，其中7.85为变动本钱，6为固定本钱函数方式也可运用函数计算变动本钱与固定本钱变动本钱

=slope(本钱，产量〕固定本钱

=intercept(本钱，产量〕24袁志刚5.3动态费用查询制造控件开发工具-控件-插入，制造组合框和列表框右键-设置控件格式，为其设置数据源区域和单元格链接写公式，将单元格链接里的序号变为详细值

=index(参数列表，单元格链接〕写公式查询数据

=SUMIFS(OFFSET(数据!$B$3:$B$42,0,参数!$C$1),数据!$B$3:$B$42,参数!$E$2,数据!$A$3:$A$42,统计!E8)〔请参照案例〕绘制饼图显示结果25袁志刚5.4拆分科目与余额分析没有规律，不在同样的位置，没有一致的分隔符不能运用分列工具公式=MID(A2,LENB(A2)-LEN(A2)+1,LEN(A2))Len函数用于前往字符个数，一个汉字作为一个字符Lenb中，一个汉字作为2个字符Mid函数用于分拆文本

=mid(需拆分单元格，从第几位拆分，拆分长度〕26袁志刚5.5费用与产量相关性分析相关系数=correl(费用，产量〕图表方法绘制折线图图表工具-规划-图表元素选择框，选择系列“销量〞设置所选内容格式-系列绘制在改为“次坐标轴〞27袁志刚6.1利润测算单变量求解数据-模拟分析-单变量求解目的单元格设置为利润所在的单元格目的值设置为目的利润可变单元格设置为要求解的变量，比如销量或价钱等点击确定即可看到计算结果28袁志刚6.2添加计算项比较预算与实践数据多重合并预算表与实践表合并预算与实践两个表添加计算项将预算与实践的上级字段改名为版本将版本字段放入列标签将光标放置在版本字段名上，选择透视表工具-选项-公式-计算项将计算项称号定义为“差别〞，公式内容为：=预算-实践，然后点击添加按钮，即可添加差别计算项去掉合计列右键-透视表选项-汇总和挑选，取消勾选“显示行总计〞29袁志刚6.3预算模板制造思绪预算制造-跟踪预算-比较分析模板制造颜色的运用：区分和强调，引导作用控件的运用：选择要显示的不同的数据内容图表：直观的展现导航：便利的操作称号的运用：高效的援用30袁志刚7.1加权平均资金本钱加权平均资金本钱WACC(weightedaveragecostofcapital)WACC=〔债务/资本〕*债务本钱*〔1-企业所得税税率〕+〔1-债务/资本〕*股权本钱控件运用开发工具-控件-滚动条右键-设置控件格式-单元格链接创建公式关联变量与单元格链接Sumproduct用于计算乘积之和，也可以用于计算多条件求和与计数=sumproduct(区域1，区域2)31袁志刚7.2贷款还款计算等额分期还款方式每期归还金额：=pmt(利率，期数，贷款金额〕每期归还的利息：=ipmt(利率，第几期，期数，贷款金额〕每期归还的本金：=ppmt(利率，第几期，期数，贷款金额〕计算利率：=rate(期数，每期还款额，贷款额〕名义利率与实践利率将名义利率转化为实践利率：

=effect(名义利率，每年复利期数〕将实践利率转化为名义利率：

=nominal(实践利率，一年内计息次数〕32袁志刚8.1银行存款余额调理表思绪利用vlookup函数在银行对账单和银行存款日记账之间双向查询，出现#N/A即未达账项，然后利用sumproduct函数将未达账项分类求合计。公式银行已收/企业未收款：=SUMPRODUCT((ISERROR($D$3:$D$19)=TRUE)*($A$3:$A$19>0)*($A$3:$A$19))其中，iserror()=true意味着企业未达，a3:a19>0意味着是企业未收款对账单上的其他工程调理公式以此类推。33袁志刚8.2应收账款管理统计收款金额合计=sumifs(合同金额列，合同编号列，要统计的合同编号〕分客户的账龄分析创建透视表，账龄字段放进展区域，金额放进数值区域光标在行标签区域，右键-创建组，将参数设为1,90,30将账龄字段拖进列标签将客户字段拖进展标签此时可以看到每家客户的应收账款分布汇总情况还可双击希望查看明细的行，对明细数据进展查看34袁志刚8.3应收款账龄分析1【插入】选项卡-选择【数据透视表】2在“字段列表〞任务区中，将过期天数字段拖入行标签，将金额两次拖入数值区域3在透视表中的行标签区域内点击鼠标右键，在右键菜单中选择“组合〞，将起始于，终止于，步长分别改为：1,120,304光标放在“金额2〞的列上右键，选择【值字段设置】，切换为“值显示方式〞，并在列表中选择“占同列数据总和的百分比〞35袁志刚8.4应付账款提示思绪利用条件格式进展应付款提示步骤计算到期天数：=付款日期-today()选中到期天数列,开场-条件格式-新建规那么-只为包含以下内容的单元格设置格式设置单元格值<0,为单元格设置红色填充色设置单元格值<10,为单元格设置蓝色填充色36袁志刚9.1报销单制造为分公司，部门，币种，支付类型等信息设置有效性在参数表上创建部门列表数据-数据有效性设置有效性条件为“序列〞，然后在下方的来源框选择参数表上的部门列表提示：excel2021之前的版本不能跨表选择有效性序列，需求为首先为序列定义称号设置填写提示信息数据-数据有效性，切换到“输入信息〞标签，填写标题和输入信息维护选中需求填写的单元格，右键-设置单元格格式-维护，取消勾选“锁定〞审阅-维护任务表-输入密码37袁志刚9.2称号定义称号可以代表一个单元格或者一个单元格区域，或者是常量，公式。称号的定义：选中需求命名的单元格或区域，在界面左上角称号框中输入称号后回车；需求留意的是：称号框只能用于定义单元格和单元格区域的称号，公式的称号需求在【公式】【称号管理器】中进展定义。称号的援用：需求援用某单元格时输入为该单元格定义的称号：＝称号步骤1：选中需求命名的某个单元格或单元格区域。步骤2：在左上角称号框输入命名后回车。删除称号：选择【公式】菜单【称号管理器】；选中需求删除的称号，点击“删除〞按钮。38袁志刚9.3报表汇总快速汇总表=sum(起始表：终了表！需汇总的单元格〕要求：各个表构造必需一致利用透视表多重合并计算数据区域汇总调出多重合并计算数据区域功能快捷键：文件-选项-快速访问工具栏，选择一切命令，在列表中找到“数据透视表和数据透视图导游〞，选到右侧点击快速访问工具栏上的透视表导游按钮，选择“多重合并计算数据区域〞选项，选择创建单页字段，逐个选择需合并的表，完成即可。优点：数据可多可少，可在一个视图上选择查看各个表的数据39袁志刚9.4数字大写转化转化效果公式中文小写公式：=numberstring(b3,1)中文大写公式：=numberstring(b3,2)逐字转化：=numberstring(b3,3)另外的写法中文小写公式：=text(b3,〞[dbnum1]〞)中文大写公式：=text(b3,〞[dbnum2]〞)逐字转化：=text(b3,〞[dbnum1]0〞)40袁志刚9.5运用技巧格式编号要求：在编号前加N0.字符，并使得编号为8位，缺乏前面补零="NO."&TEXT(A2,"00000000")读取多个其他任务表上的数据公式：=INDIRECT(D4&"!b2")其中D4是任务表称号所在单元格，b2是要读