excel实用技巧及财务模型.ppt
1,EXCEL实用技巧及财务模型,2,VI.其他财务模型,V.存货分析模型,IV.费用分析模型,III.工资计算模型,I.基本要求及技巧,II.现金管理模型,内 容,3,4.易更新:引用及链接,3.规范:编财务表基本要求,2.安全:单元格和工作表保护大全,1.高效:如何定制富有财务特色的工具按钮,I.基本要求及技巧,4,1.菜单及工具按钮,I.自定义工具栏和菜单在现有的工具栏/菜单内添加或删除按钮/命令创建自己的工具栏和菜单打开自定义开关:-右键菜单或工具栏 自定义-工具-自定义-视图-工具栏-自定义,5,-.单元格保护1.锁定部分单元格2.锁定全部单元格3.隐藏单元格公式4.隐藏整行/列5.隐藏部分单元格内容-.工作表保护1.隐藏单张工作表(格式-工作表-隐藏)2.锁定单张工作表(工具-保护-保护工作簿)-.工作簿保护1.隐藏工作簿(窗口-隐藏)2.锁定工作簿(工具-保护-保护工作簿)3.为工作簿设置打开/修改密码(文件-另存为-工具-常规选项),2.单元格,工作表保护大全,6,尽量采用长的而不是宽的工作表以方便用Page Up,Page Down 快速流览.不必把所有的信息信息放在一张工作表内.为工作表添加必要的抬头和注释为工作表命名简短而有意义的名称删除多余的空白工作表定义好所有的打印设置以方便使用者需要时直接打印为新数据预留一些空白行或列.这样添加数据时不用再修改公式.,3.使用工作簿/工作表基本规范,7,-.引用1.理解相对,绝对和混合引用2.F4 键的使用-.链接1.对同一工作簿其他工作表的引用/链接2.对其他工作簿中工作表的引用/链接3.更新和改变链接-.超级链接1.创建一个超级链接2.将普通文本转换为一个超级链接3.链接到当前文档4.链接到其他文档5.将超级链接指定给图形,按钮等,4.引用及链接,8,4.怎样用宏编制现金流量表,3.现金日报表,2.宏的录制及编写,1.什么是宏,II.现金管理模型,9,宏:一组指令,告诉 Excel 执行一个或多个操作.,1.什么是宏,10,录制宏的步骤1.启动宏录制器.2.命名宏,指定快捷键,保存位置及描述.3.执行所要录制的操作.4.停止宏录制器.使用宏录制器的优点快速,帮助学习.使用宏录制器的缺点某些语句不能通过录制而必须编写.常产生多余的代码.,2.宏的录制及编写 1/2,11,好习惯1.在宏或自定义函数中使用注释2.给宏,自定义函数和变量取有意义的名字最常用且实用的两个基本 VB 语句1.For Next2.If Else End If,2.宏的录制及编写 2/2,12,定义现金流量表格式为每一类现金收支项目制定统一的代码每日输入现金的收支情况每日更新现金流量表,3.现金日报表/现金流量表,13,4.如何快速建立和取消分级显示,3.工资计算表用到的函数,2.名称的使用,1.公式及函数,III.工资计算模型,14,所有的公式都是以=开头公式包含的运算符:+-*/()如何快速获知公式中某部分的计算结果(巧用 F9 键)如何不用公式而迅速获得常用计算结果(状态栏)公式的 6 种错误值:#DIV/0,#NAME?,#VALUE,#REF!,#N/A,#NUM!,1.公式及函数 1/2,15,一个函数就是一个预定义的公式所有的函数都包括一个函数名和一系列的参数财务工作中常用的函数:数学函数:SUM,SUMIF,SUMPRODUCT,COMBIN,ROUND,ROUNDDOWN,ROUNDUP,FLOOR,CEILING,INT,AVERAGE文本函数:MID,RIGHT,LEFT逻辑函数:IF,AND,OR,NOT查找函数:VLOOKUP,HLOOKUP财务分析函数:PV,NPV,FV,PMT,IRR,SLN,DDB,1.公式及函数 2/2,16,定义名称(Ctrl+F3 键)在公式中使用名称(F3 键)创建名称列表(F3 键)命名常量工作簿和工作表名称的区别,2.名称的使用,17,IFANDSUMPRODUCTCOUNTACOUNTIFSUMIFVLOOKUP,3.工资计算表用到的函数,18,4.快速建立和取消分级显示,建立分级显示取消分级显示显示隐藏的信息隐藏不需看的信息,19,3.使用复合饼图显示费用的其他项,2.使用合并计算功能汇总费用,1.什么是三维公式,IV.费用分析模型,20,1.三维公式,应用:同一工作簿不同工作表相同单元格=SUM(单击起始工作表Shift+单击结束工作表单击要求和的单元格+)例子:=SUM(HR:PRD!E5),21,按位置合并:源工作表布局同目标工作表完全相同打开目标工作表选中将要接受合并数据的区域选择 数据-合并计算,选择函数类型用鼠标选择源数据区域单击“添加“按钮单击“确定“按钮更新:选中合并数据区域选择 数据-合并计算单击“确定“按钮,2.合并计算 1/2,22,按类别合并:源工作表布局同目标工作表不完全相同打开目标工作表选中将要接受合并数据的区域的首个单元格选择 数据-合并计算,选择函数类型选中 标签位置-最左列用鼠标选择源数据区域(包括行标签)单击“添加“按钮单击“确定“按钮更新:选中合并数据区域首个单元格选择 数据-合并计算单击“确定“按钮,2.合并计算 2/2,23,步骤:同一般建立图表步骤区别:选择复合饼图图表类型修改:分割系列第二绘图区包含的内容第二绘图区的尺寸,3.复合饼图,24,3.隐藏基础数据工作表,2.使用控件使您的图表具有对话功能,1.制作色彩鲜艳的图表,V.存货分析模型,25,1.图表 1/4,创建一个新图表 修改及美化图表的外观 使用图表的一些原则,26,创建一个新图表创建图表快捷键:F11标准四步曲:,1.图表 2/4,3.设置图表选项,2.指定数据源,1.选择图表类型,4.指定图表放置位置,27,修改及美化图表的外观 图表标题:命名,设置 图案,字体和对齐方式 坐标轴:显示或隐藏,设置 图案,刻度,字体,数字格式和对齐 方式 网格线:显示或隐藏,设置 图案,刻度 图例:显示或隐藏,设置 图案,字体和位置 数字标志:显示或隐藏,设置 图案,字体,数字格式和对齐方式 数据表:显示或隐藏,设置 图案,字体 背景区域:设置 图案 数据系列:设置 图案,误差线 Y,次序,重叠比例,分类间距和系 列线,1.图表 3/4,28,使用图表的一些原则选用适当的图表类型不要企图在同一张图表内同时表示超过两组的数据不必在图表内显示太多的数字选择恰当的坐标刻度充分利用图表的彩色视觉功能在图表内添加适当的对象以突出重点,1.图表 4/4,29,2.窗体的使用,1,2,30,3.固定资产折旧计算,2.成本报告及分析,1.动态保本图,VI.其它财务模型,4.购房贷款及投资回报计算,31,谢谢大家!,
