EXCEL实用技巧及财务模型.ppt

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