Excel财务应用讲义

会计学1Excel财务应用讲义1.1销售周报分析计算周次函数Weeknum=weeknum(日期，2）2表示一周从星期一开始根据周次统计收入Sumifs函数多条件求和函数，与之同类的还有countifs,averageifs除了sumifs，sumproduct函数也可用于多条件求和=sumifs(求和区域，条件区域1，条件1，条件区域2，条件2,…)制作折线图趋势分析2第1页/共46页1.2客户分布分析Frequency函数自定义统计区间设定参照值，取区间最小值作为参照值选中与统计区间数量相同的连续单元格输入frequency函数

=frequency(数据区域，参照值区域）同时按下ctrl+shift+enter键最后按下的组合键将创建一个数组公式不可以单独删除数组公式中的任一公式，需要全选后才可删除构成比例分析绘制饼图设置数据标签3第2页/共46页1.3客户数变动分析生成透视表选择日期放入行区域右键-创建组，选择年在透视表外输入公式计算客户增长的比例制作图表光标放在透视表内，点击数据透视表工具-选项-数据透视图生成透视图光标放在客户增长率表内，选择“插入”-折线图客户增量分析创建透视表，将日期放入行区域，将销量字段放入数值区域右键-创建组，选择年和月光标放在销量列，邮件-值汇总依据-选择计数数据透视表工具-选项-数据透视图，生成折线图4第3页/共46页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=xlThemeColorAccent6EndSub5第4页/共46页1.4基于地图的数据分析步骤为每个区域图形指定宏，选中该区域图形，右键-指定宏，输入以下宏名称：

‘thisworkbook.user_click(“区域名称”)‘

其中，区域名称为为区域图形定义的名称在m1单元格中输入一个区域的名称代码，比如北京地区的代码为beijing,此时即可点击区域实现变色效果。准备好原始数据写一个vlookup函数对地图上选中的区域数据进行查询，该查询的索引字段为m1单元格，该单元格可以任意指定。根据查询结果制作图表6第5页/共46页1.5基于地图的色阶分析步骤准备地图矢量文件为每一个区域图形定义名称，选中图形，在名称框内输入名称，名称设为该区域的拼音为数据设置区间，假设分为以下5个区间：为上面5种色彩所在单元格定义名称，分别设为code1-5为这5个区间设置参照值和颜色代码：7第6页/共46页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").SelectEndSub8第7页/共46页1.6复合增长率概念CAGR（CompoundAnnualGrowthRate）一个指标（比如销售收入或净利润，投资回报等）在特定时期内的年度增长率是较长时期内的测算，忽略个别年度的波动，将增长率平滑，反映指标的整体表现。公式=（当前数值/基期数值）^(1/年数）-19第8页/共46页1.7波士顿矩阵分析简介波士顿矩阵（BCGMatrix），又称市场增长率-相对市场份额矩阵，是由美国著名的管理学家、波士顿咨询公司创始人布鲁斯·亨德森于20世纪60年代末期首创的。布鲁斯认为决定产品/业务结构的要素可分为2类，市场吸引力与企业实力。在反映市场引力的众多指标（销售增长率、目标市场容量、竞争对手强弱及利润高低）中，销售增长率是最具代表性的综合指标；而在反映企业实力的指标，如市场占有率，技术、设备、资金利用能力中，市场占有率是最能直接显示出企业竞争实力的指标。因此，波士顿矩阵选取的纵坐标与横坐标分别是“销售增长率”及“市场占有率”。由以上2个因素相互作用，产生4个不同的象限，划分出4类性质的产品/业务：销售增长率和市场占有率都较高的产品/业务（简称“明星”）；销售增长率和市场占有率都较低的产品/业务（简称“瘦狗”）；销售增长率高而市场占有率低的产品/业务（简称“问号”）；销售增长率低而市场占有率高的产品/业务（简“现金牛”）。

10第9页/共46页问题型业务现金型业务瘦狗型业务明星型业务高高低低市场增长市场份额1.7波士顿矩阵分析第10页/共46页1.7波士顿矩阵分析利用散点图制作波士顿矩阵选中表格的数据列，制作散点图，行坐标为市场占有率，纵坐标为增长率选中行坐标轴，设置坐标轴格式，勾选逆序刻度值，将纵坐标交叉选项改为坐标轴值：0.5，将刻度线类型与坐标轴标签设为无选中纵坐标轴，除了不勾选逆序刻度值，其他设置与行坐标轴一样Excel散点图的一个问题是无法为数据添加标签，可以使用一个第三方程序解决：xychartlabels安装此程序后excel会出现一个新的选项卡xychartlabels,选择其中的addlabels命令在其中的selectalabelrange选项中，选择数据表中的表前列即可最后可为坐标轴添加标签或为4个象限添加图片说明。12第11页/共46页2.1存货ABC管理概念又称巴雷特分析法，按照价值和库存数量的高低，依据一定的分类标准，确定关键的少数和次要的多数。其分清主次，抓住重点的思想广泛应用于存货管理与成本管理。A类物资是指品种少、实物量少而价值高的物资，其成本金额约占70%，而实物量不超过20%。C类物资是指品种多、实物量多而价值低的物资，其成本金额约占10％，而实物量不低于50％。B类物资介于A类、C类物资之间。其成本金额约占20％，而实物量不超过30％。管理表制作步骤计算存货金额及数量所占比例，并按金额降序排列计算存货金额和数量所占累计比例13第12页/共46页2.1存货ABC管理管理表制作步骤依据一定标准，进行ABC分类。一般将关键的少数作为A类，次要的多数作为B和C类，对A类存货进行重点管理。制作帕累托图以意大利经济学家pareto命名选择存货名称，金额，累计金额百分比3列制作柱形图选择图表工具-布局，左上角点开图表元素选择框，在其中选择累计金额百分比点击设置所选内容格式按钮，将“系列绘制在”选项由“主坐标轴”改为“次坐标轴”将累计金额百分比数据系列的图表类型改为折线图选中副坐标轴，将最大值改为1选中金额数据系列，右键-设置数据系列格式，将分类间距改为0%14第13页/共46页152.2进销存表设计“表”功能表格与数据区域表内的数据可以独立和更方便的进行管理，比如自动扩展数据与公式，添加汇总行，筛选，应用表格格式等。创建与编辑将光标放在表中，选择“插入”-表格通过顶部的表格工具，可以对表格进行各种编辑为表格更换样式：表格工具-表格样式，点击即应用新的样式将表格转化为区域：表格工具-转化为区域在表格最后一行下面输入数据，即可自动扩展表格范围15第14页/共46页2.3进销存管理数据表设计进出字段用if函数控制显示与否

=if(b2=””,””,c2)Vlookup查询产品价格

=vlookup(产品名称，基础信息表，价格所在的列，查询方式）统计库存创建透视表，行设置为产品，列设置为“进出”，数值为数量数据透视表工具-域、项目和集-计算项输入名称“库存”，公式设置为：=进货-发货标识低于安全库存的存货：开始-条件格式-新建规则-突出显示单元格规则16第15页/共46页3.1杠杆平衡模型分析经营杠杆=边际贡献/（边际贡献-固定成本）假设经营杠杆系数为2，则表明销量增长10%，息税前利润增长（2×10%）20%，即高度的经营杠杆，表示销货量的小幅变动，会引起利润的较大变动。一家公司的固定成本愈高，其企业风险也愈高。故有大量固定成本的公司，即具有较高的营业杠杆。企业一般可通过增加销售额，降低单位变动成本和固定成本等措施来降低经营杠杆和经营风险。财务杠杆是指由于债务的存在而导致每股利润的变动大于息税前利润变动的杠杆效应。财务杠杆系数=息税前利润/(息税前利润-资本总额*负债比例*利率）17第16页/共46页3.1杠杆平衡模型分析复合杠杆复合杠杆是指由于固定成本和固定财务费用的存在而导致的普通股每股利润变动率大于产销量变动率的杠杆效应。复合杠杆系数=经营杠杆系数*财务杠杆系数杠杆平衡模型当其他因素变动时，测算销量的变动文件-选项-自定义功能区，在主选项卡勾选“开发工具”开发工具-控件-插入，选择“数值调节钮”，为变量添加调节钮控件右键选择数值调节钮，设置控件格式，设置单元格链接，并使用链接公式使得该单元格链接可以控制变量值18第17页/共46页3.2盈亏平衡分析分析内容销量平衡分析单价平衡分析变动成本平衡分析固定成本平衡分析控件应用开发工具-控件-插入-滚动条右键-单元格链接输入变量计算公式：=当前值*(（1+单元格链接/50-1)/2)，此公式使得变量以每次1%的幅度变化19第18页/共46页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))20第19页/共46页4.2薪酬计算与分析奖金匹配hlookup：水平查询设置奖金比例表，其中的参照值应该取区间的最小值=hlookup(某销售员奖金，奖金比例表，比例所在列，查询方式）

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

=（当期/基期）^(1/年数）-1

21第20页/共46页5.1折旧费用计算已经计提折旧月份计算=datedif(启用资产日期，today()，”m”)其中m表示月份，还可以是y、d，分别表示年，天。折旧额计算直线法

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

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

=syd(原值，残值，使用年限，第几期）22第21页/共46页5.2混合成本分解制作散点图添加趋势线右键-添加趋势线，选择线性勾选“显示公式”和“显示R平方值”选项公式Y=7.85x-6，其中7.85为变动成本，6为固定成本函数方式也可使用函数计算变动成本与固定成本变动成本

=slope(成本，产量）固定成本

=intercept(成本，产量）23第22页/共46页5.3动态费用查询制作控件开发工具-控件-插入，制作组合框和列表框右键-设置控件格式，为其设置数据源区域和单元格链接写公式，将单元格链接里的序号变为具体值

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

=SUMIFS(OFFSET(数据!$B$3:$B$42,0,参数!$C$1),数据!$B$3:$B$42,参数!$E$2,数据!$A$3:$A$42,统计!E8)（请参照案例）绘制饼图显示结果24第23页/共46页5.4拆分科目与余额分析没有规律，不在同样的位置，没有统一的分隔符不能使用分列工具公式=MID(A2,LENB(A2)-LEN(A2)+1,LEN(A2))Len函数用于返回字符个数，一个汉字作为一个字符Lenb中，一个汉字作为2个字符Mid函数用于分拆文本

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

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

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