excel高效财务数据分析及财务管理应用.ppt

Excel高效财务数据分析及财务管理应用 袁志刚 2015 目录 销售管理与分析 进销存管理 财务模型分析 薪酬计算与分析 费用分析 预算管理 融资分析 往来分析 报表设计 链接数据库 袁志刚 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 基于地图的数据分析 作用 形象展示基于地理位置的区域数据分析。 容易理解，印象深刻，效果出众。 步骤 使用地图矢量图，为每一个区域定义名称，一般是该区域名称的拼音。选中区域，在名称框内输入即可。 输入宏，开发工具-visual basic,双击thisworkbook,在右侧代码窗口输入以下代码： Sub user_click(region_name) ActiveSheet.Shapes(Range("m1").Value).Fill.ForeColor.SchemeColor = xlThemeColorDark1 Range("m1").Value = region_name ActiveSheet.Shapes(region_name).Fill.ForeColor.SchemeColor = xlThemeColorAccent6 End Sub 袁志刚 6 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_单击() For i = 3 To 34 Range("province").Value = Range("data!a" & i).Value ActiveSheet.Shapes(Range("province").Value).Select Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range("vbcode").Value).Interior.Color Next i Range("f8").Select End Sub 袁志刚 9 1.6 复合增长率 概念 CAGR（Compound Annual Growth Rate） 一个指标（比如销售收入或净利润，投资回报等）在特定时期内的年度增长率 是较长时期内的测算，忽略个别年度的波动，将增长率平滑，反映指标的整体表现。 公式