excel高级财务应用.ppt

主讲 刘会齐liuhq 8888 第1讲Excel财务应用基础 一 第1讲Excel财务应用基础 一 一 课程简介二 excel2003简介三 表格设计四 财务数据的处理五 图表分析财务表格数据信息 一 课程简介 课程基本目标及要求为什么学习这门课如何学习这门课程课程内容考核方式参考资料 1 课程基本目标及要求 目标 利用excel工具进行财务核算和管理要求 熟练掌握excel在财务核算和管理中的应用注重实践性 达到解决问题的最佳效果 高效 快速提高工作效率的便捷方法安全 单元格 工作表和工作簿保护美观 怎样制作专业 规范的Excel报表 2 为什么学习这门课 Excel强大的功能提高财会人员能力 核算和管理 实际应用需求 解决实际业务需求为主体 3 如何学习这门课程 基本知识必须熟练掌握 多实践 实践性非常强 需要动手实践 在解决问题的基础上不断优化方案 多交流 需要多交流 吸取他人方案的优点 不断提高自己水平 4 课程内容 excel基础excel基本应用Excel高级应用 Excel简介工作簿及工作表表格格式设置 数据操作及管理图表应用公式与函数的应用规划求解和方案管理单双变量模拟运算数据分析工具宏与VBA自动化功能 财务核算与管理模型财务分析模型财务预算模型筹资决策模型投资决策模型利润规划模型 讲授实践 研讨 5 考核方式 总成绩 平时20 小组30 考试50 考试 在机房完成考试内容 提交电子文件 6 参考资料 Excel高级财会应用吕志明编著清华大学出版社北京交通大学出版社Excel在财务管理与分析中的应用韩良智编著中国水利水电出版社VBA财务管理应用详解韩良智编著中国铁道出版社有关Excel相关网站 二 excel2003简介 excel知识体系excel2003中文版的启动与退出excel2003窗口excel系统选项及常见错误 1 excel知识体系 如何成为操作高手 掌握每一项菜单命令的用法掌握常用快捷键双手上阵鼠标键盘齐上阵 技巧 减少击键次数数字小键盘的盲打批量录入相同数据 ctrl键 行列互转 插入行列录入日期 可免除年的输入 当前日期ctrl 绝对引用相对引用 2 excel2003中文版的启动与退出 启动退出 桌面快捷方式 双击图标 开始 程序 文件夹开始 internet区 excel图标 需要先附到开始菜单 开始 高频使用区 excel图标打开一个excel文档 单击标题栏右侧的按钮双击标题栏左端的程序控制按钮选择 文件 退出或关闭 命令Alt F4 3 excel2003窗口 1 标题栏 标题栏位于excel操作界面顶端 包括程序控制图标 程序名 当前打开的文档名称以及3个窗口控制按钮通过标题栏可以进行如下几种操作 单击标题栏中的程序控制按钮 在弹出的控制菜单中可以对窗口进行各种操作 如移动窗口 缩小窗口 放大窗口及快速关闭窗口等 当窗口处于非最大化状态时 拖动标题栏可将窗口移动到任意位置 单击按钮可将窗口最小化到任务栏中 单击按钮可将窗口放大到整个屏幕 单击按钮可以退出wxcel程序 程序控制按钮 文件名 最大化 最小化 关闭按钮 2 菜单栏 Ctrl Alt Shift 思考 1 几个基本符号含义2 组合键使用 3 工具栏 意义 简单 快捷 形象直观 便于记忆和使用 几种操作快速显示或隐藏 快捷菜单操作 移动工具栏 鼠标移至工具栏前面 出现移动符号 填加或删除按钮 工具栏最右侧添加或删除按钮 3 菜单 视图 工具栏 4 数据编辑区与状态栏 单元格地址 编辑内容 当前工作状态 数据编辑区 用来输入或编辑单元格或图表的值或公式 也可以显示 状态栏 选定操作或执行命令的信息 5 任务窗口 操作 执行某个超级链接切换到其他任务窗口搜索 查询浮动性 鼠标右键可取消浮动 菜单 视图 任务窗口 6 几种光标含义 4 excel系统选项及常见错误 菜单 工具 选项 功能 进行excel工作环境设置的窗口 续 注意观察该工作表的变化 软件操作习惯 简洁的界面 三 表格设计 工作簿操作工作表操作单元格操作财务表格格式设置 1 工作簿操作 工作簿含义创建工作簿打开工作簿保存 另存工作簿关闭工作簿 创建空白工作簿工具栏中的新建按钮CTRL N 文件 新建 选空白工作簿创建基于模版的工作簿 打开工作簿工具栏中的打开按钮CTRL O 文件 打开 选工作簿双击excel文件注意打开方式选择 工作簿用于处理和存储数据的文件 含有多个工作表 保存工作簿工具栏中的保存按钮CTRL S 文件 保存 或 文件 另存为 2 工作表操作 工作表的选择插入工作表删除工作表重命名工作表移动或复制工作表隐藏工作表窗口拆分与冻结 菜单 插入 工作表 快捷菜单 右键单击工作表标签区 菜单 编辑 删除工作表 快捷菜单 右键单击某工作表标签 菜单 格式 工作表 重命名 快捷菜单 右键单击某工作表标签 菜单 编辑 移动工作表 快捷菜单 右键单击某工作表标签 鼠标拖拽 菜单 格式 工作表 隐藏 菜单 窗口 冻结 拆分 续 冻结线 冻结线 冻结时单元格位置 续 思考 什么情况下使用冻结 什么情况下使用拆分 3 单元格操作 单元格表示方法单元格选择插入单元格删除单元格单元格剪切与粘贴合并单元格单元格格式设置单元格的引用行列操作 行列表示B5 单个 多个 区域 行 列 整个工作表 连续 不连续Ctrl键 shift键使用 鼠标使用 菜单 插入 单元格 选项对话框 快捷菜单 插入 选项对话框 可以实现整行插入或删除操作 菜单快捷菜单可以实现单元格的移动 菜单 格式 单元格 对齐页签 快捷菜单 格式 单元格 对齐页签 工具栏合并单元格按钮 通过地址引用 相对引用和绝对引用 通过名称引用 整行 整列 插入 删除 隐藏 取消隐藏 行高设置 列宽设置 1 单元格合并 使用菜单 格式 单元格 使用工具栏 2 单元格的引用 在公式中用到了其他单元格在表格中的位置 引用的作用在于标识工作表中的单元格或单元格区域 并指明公式中所使用的数据的单元格位置 引用不同工作簿中的单元格称为链接 续 相对引用 相对引用也称为相对地址引用 是指在一个公式中直接用单元格的列标与行号来取用某个单元格中的内容 某超市3月份的进货单如图所示 计算 金额 在G3计算公式 C3 D3 F3然后向下复制该公式 续 在E8输入公式 c 5 C8然后向下复制此公式 绝对引用 绝对引用总是在指定位置引用单元格 如果公式所在单元格的位置改变 绝对引用保持不变 绝对引用的形式是在引用单元格的列号与行号前面加 符号 比如 A 1就是对A1单元格的绝对引用 如上图绝对引用的 C 5 其含义是什么 续 混合引用 混合引用具有绝对列和相对行 或是绝对行和相对列 比如 A1 B1 你能知道上述引用的结果吗 续 1 引用相同工作表中的单元格 例 G3 G5 G10 102 引用同一工作簿的不同工作表中的单元格 例 Sheet1 G3 Sheet1 G5 Sheet1 E273 引用不同工作簿中的单元格 例 Book1 Sheet1 IL 4 Book1 Sheet2 E 74 同一公式中存在几中不同的引用 例 Book1 Sheet1 A 4 Sheet1 G7 F9 内部引用与外部引用 4 财务表格格式设置 1 单元格格式设置 格式菜单或者快捷菜单可以设置 字体格式 边框效果 数字格式 对齐方式 思考 如下费用报销单如何设置格式 使用了哪些功能 参见课堂练习1 续 2 应用单元格样式 样式是字体 字号 填充颜色等格式设置的组合 将这个组合作为一个集合进行命名和存储 以方便用户对表格进行快速格式效果的设置 样式可以根据用户需求新建 也可以应用已有的样式 3 套用表格格式 快速设置工作表中单元格的格式效果 可以使用套用表格格式功能 套用表格格式后的工作表将具有数据排序 筛选等分析功能 续 4 单元格区域条件格式 使用条件格式功能对工作表中单元格区域进行格式效果的设置 从而突出显示单元格 课堂思考题 1 请将员工工资表中客户部的基本工资大于1200元的员工填充为红色 参见课堂练习2 2 将产品报价单设置为如下格式 参见课堂练习3 四 财务数据的处理 一 数据的输入与编辑 二 财务表格数据的处理 三 利用数据透视表分析报表 一 数据的输入与编辑 1 建立数据清单注意事项2 excel常见数据类型3 数据有效性4 输入数据5 修改 复制 移动或删除数据 1 建立数据清单注意事项 Excel数据的管理主要通过数据清单实现的 数据清单相当于一张完整的报表 它是包含相关数据的一系列工作表行 通常情况下 数据清单第一行具有列标志 避免在数据清单中存在空行或空列避免在单元格的开头和末尾输入空格避免在一张工作表中建立多个数据清单 最好一个 数据清单与其他数据之间至少留出一个空行或空列关键数据最好置于数据清单顶部或底部 2 excel常见数据类型 菜单 格式 单元格 快捷菜单类型 常规 数值 货币 会计专用 日期 时间 百分比注意 货币 会计专用 日期 百分比等类型使用 3 数据有效性 菜单 数据 有效性 作用 实现对单元格中所输入的数据进行检查 输入提示等 例如 可以实现数据范围控制 实现输入数据类型的提示等 4 输入数据 单元格中输入的内容可以是文字 数据 日期 符号 字符串等 方法1 选中单元格 输入数据 按enter键 方法2 利用excel的记录单输入 数据 记录单 记录单是系统提供的一个数据规范化输入界面 可以实现数据新增 删除 查询等操作必须基于数据清单来使用 1 基本数据的输入 输入过程输入数值输入文本输入日期输入公式 选定要输入数据的单元格 从键盘上输入数据 按Enter键后 正数的输入负数的输入 负号的输入注意 分数的输入 02 5否则系统按照时间对待 货币数据的输入 字符文本应逐字输入数字文本 开头输入 或用 数字 方式 文本形式的公式 插入 对象 菜单命令 yy mm dd 或mm dd形式通过格式化形式的日期 复制相同数据填充复制相同数据 选中单元格 拖拽柄拖拽 用Enter输入相同数据 多个单元格输入数据 选择区域输入数据CTRL ENTER 复制数据 选中区域 然后粘贴 2 相同数据的输入 3 编号的输入 复制输入连续的编号 两个单元格同时选中 拖拽完成 填充产生连续编号 编辑 填充 序列 某一单元格填入初始值 然后使用菜单命令完成 利用自定义格式产生特殊编号 例如 yl 02 001 yl 02 002 4 组合多个单元格数据 用 运算符组合数据 ADKDKD DKA 的结果为ADKDKDDKA 用 和文本函数的结合 Left text n right text n mid text n1 n2 5 采用下拉列表进行数据选择 下拉列表的适用范围下拉列表建立方法 适合项目个数少而规范的数据 比如职称 工种 单位及产品类型等 这类数据适宜采用Excel的 数据有效性 检验方式 以下拉列表的方式输入 选中要建立下拉列表的单元格区域 选择 数据 有效性 菜单项 选择 设置 标签 然后从 允许 下拉列表中选择 序列 选项 在 来源 文本框中输入列表内容 如职称名字 助教 讲师 副教授 教授 6 在一行中输入多行数据 使用组合键 按 Alt 和 Enter 组合键 7 填加批注 单击 插入 批注 命令 或在弹出的快捷菜单中选择 插入批注 命令 删除批注 菜单或快捷菜单常用于单元格加入批注 5 修改 复制 移动或删除数据 修改数据方法 复制数据方法 移动数据方法 删除数据方法 直接全屏修改利用记录单 选定区域后执行 利用查找与替换的功能 全部内容批量修改或删除 利用复制和粘贴命令利用多重剪贴板工具 利用剪切和粘贴命令利用鼠标拖动 直接按 Del 键菜单 编辑 清除 记录单 二 财务表格数据的处理 1 查找与替换数据2 数据的排序3 数据的筛选4 数据的汇总 1 查找与替换数据 全屏区域直接查找利用记录单 适合数据记录较少 可以逐条查询 也可以指定查询条件 查找功能 菜单 编辑 查找 或按组合键 CTRL F H 对话框中指定条件 2 数据的排序 排序是指根据某一列数据的顺序重新对行的位置进行调整 Excel提供了对字符 数字等数据分别按照主要关键字 次要关键字 第三关键字进行三级排序的功能 每个关键字可以按升序或者降序排列 排序方法 使用菜单 数据 排序 通过对话框指定条件 使用工具栏按钮 适合按一个关键字进行排序 续 1 排序规则 数值 按数值的大小 字母 按字母先后顺序 日期 按日期的先后 汉字 按汉语拼音的顺序或笔画顺序 逻辑值 升序时false排在true前面 降序时相反 空格 排在最后 续 2 步骤 定位在数据区域任一单元格执行 数据 排序 功能 系统弹出排序对话框指定关键字 升序或降序按确定键后 显示出结果 3 自定义排序 用户按照指定的特殊次序进行的排序 进行自定义排序需要按照如下步骤进行 需要先自定义序列 菜单 工具 选项 自定义序列选项卡 添加 执行排序功能 数据 排序 选择关键字 在 选项 排序选项 中选择自定义序列 续 1 定义自定义序列 2 执行排序功能 3 数据的筛选 数据筛选是指把数据清单或数据库中不满足条件的数据记录隐藏起来 只显示满足条件的数据记录 常见的数据筛选方法有 自动筛选自定义筛选高级筛选 续 1 自动筛选 步骤 单击数据清单任一非空单元格单击 数据 筛选 自动筛选 功能 系统自动在列标题下添加了下拉按钮 进行选择若要恢复所有的记录 单击标题右边的下拉列表中的 全部 项再次执行 数据 筛选 自动筛选 命令 可取消自动筛选状态 续 2 自定义筛选 在标题列的下拉列表中选择 自定义 命令 对话框中定义条件即可 续 3 高级筛选 利用高级筛选可以使用较多条件对数据清单进行筛选 这些条件可以是与条件 也可以是或条件 或者两者组合 常见的高级筛选有如下两种 一般条件下的高级筛选计算条件下的高级筛选 续 一般条件下的高级筛选步骤如下 建立一个条件区域 在条件区中 同行的条件是与条件 不同行条件是或条件 单击菜单 数据 筛选 高级筛选 功能 根据对话框设置即可一般条件筛选可以实现满足一个条件的筛选 满足多个条件的筛选和满足多个条件中任意一个条件的筛选等筛选操作 续 参见演示文件 请思考 两个结果有什么区别 续 计算条件下的高级筛选步骤如下 在数据清单以外的任一空单元格内输入计算公式 建立一个条件区域 条件区域的列标可以是数据清单列标题以外的文本 筛选条件中的地址必须是相对引用 计算区的引用必须是绝对引用 单击菜单 数据 筛选 高级筛选 功能 根据对话框设置即可 参见演示文件 续 注意 高级筛选和自动筛选区别 自动筛选以下拉列表方式来过滤数据 而高级筛选则是必须给出用来作为筛选的条件 并使用该条件来筛选数据 4 数据的汇总 当数据清单中有大量数据时 需要将某种类型的数据统计出来 利用分类汇总功能来实现 数据的汇总有如下两种方式 自动分类汇总和嵌套分类汇总注意 分类汇总是根据字段名称进行的 因此必须保证数据清单中每一列都有名称汇总之前对数据按照要汇总的关键字进行排序 续 1 自动分类汇总 参见演示文件 单击 数据 排序 功能 在弹出对话框中设置关键字 并排序单击 数据 分类汇总 功能 弹出分类汇总对话框 设置分类字段 汇总方式 汇总项等内容 单击确定按钮得到结果 注意汇总结果行号左侧各种符号含义 续 参见演示文件 单击 数据 排序 功能 在弹出对话框中设置关键字 并排序单击 数据 分类汇总 功能 弹出分类汇总对话框 设置分类字段 汇总方式 汇总项等内容 单击确定按钮得到结果 续 2 嵌套分类汇总 先按主次关键字排序 然后分别汇总 三 利用数据透视表分析报表 1 建立数据透视表2 使用数据透视表3 更新数据透视表4 编辑数据透视表5 删除数据透视表6 建立数据透视图 数据透视表示用于快速汇总大量数据的交互式表格 利用数据透视表可以对数据表的行或列进行旋转 以便查看对源数据的不同汇总结果 方便更好的对比和分析 1 建立数据透视表 在建立数据透视表之前 必须保证数据源是一个数据清单或数据库 即每列要有标题 单击 数据 数据透视表和数据透视图 菜单 系统弹出创建数据透视表向导对话框 依次按照向导完成设置即可 续 参见演示文件 2 使用数据透视表 在下拉列表中选择要查看的项目即可实现对相应内容的利用观察数据透视表含义使用组及显示明细数据功能进行分组 参见演示文件 3 更新数据透视表 当数据清单中的数据发生变化时 单击数据透视表中的任一单元格 单击鼠标右键 在快捷菜单中选择 刷新数据 命令 数据透视表的菜单 数据 刷新数据 命令 4 编辑数据透视表 建立数据透视表后 有时需要进行编辑修改 修改数据透视表的具体步骤是 选取数据透视表中的任意单元格 单击鼠标右键 在弹出的快捷菜单中选择 数据透视表向导 命令 打开 数据透视表和数据透视图向导 布局 对话框 再进行相应的修改即可 5 删除数据透视表 删除数据透视表所在的工作表 在快捷菜单中选择 删除 命令仅删除数据透视表 单击 数据透视表 工具栏中的 数据透视表 下拉式箭头 从弹出的菜单中单击 选定 整张表格 命令 再单击 编辑 删除 命令 6 建立数据透视图 在数据透视表中选取任意一个单元格 单击鼠标右键 在系统弹出的快捷菜单中选择 数据透视图 命令 则系统自动创建一张数据透视图单击 数据 右边的倒三角形 在下拉列表中选择要查看的项目 五 图表分析财务表格数据信息 一 excel图表基础 二 图表的应用 一 excel图表基础 1 图表建立2 图表编辑3 动态图表的建立 1 图表建立 建立图表过程 按照图表向导说明 一步一步进行操作即可以完成图表的制作 建立方法 菜单 插入 图表 工具栏 工具栏上的图表向导按钮 Excel2003提供了14种标准图表类型 柱形图 条形图 折线图 饼图 XY散点图 面积图 圆环图 雷达图 曲面图 气泡图 股价图 圆柱图 圆锥图 棱锥图 每种图表类型又都有几种不同的子类型 Excel还提供了约20种自定义图表类型 用户可根据不同的需要选用适当的图表类型 2 图表编辑 1 修改坐标轴格式 2 修改字体和字号 3 改变图表的大小 4 移动或复制图表 5 添加数据标志 6 改变图表颜色 图案 边框 鼠标移到坐标上 单击右键 再快捷菜单选择相应的项目既可 选中图表区域 并激活 图标边框出现8个操作炳 使用鼠标调整大小即可 单击需要显示数据表之的数据点 鼠标右键弹出快捷菜单 选择 数据系列格式 数据标志选项卡 在数据标签包括区域选中 值 复选框既可 选中图表 鼠标右键弹出快捷菜单 选择 图表区格式 弹出对话框后进行设置 3 动态图表的建立 Excel提供了静态图表和动态图表 步骤 设计动态图表数据区域使用INDIRECT函数编写公式选择目标单元格 按F9键 则相关有关资料选择动态图表区域 单击工具栏图表按钮 选择图表类型 选择不同动态值 按f9刷新即可得到不同的图表 二 图表的应用 1 创建比较分析柱形图2 创建百分比结构分析饼图3 创建面积效果分析图4 创建数据走势分析折线图 1 创建比较分析柱形图 使用柱形图可以清晰观察数据分布情况 Excel中常用的柱形图有 簇状柱形图 堆积柱形图和三维柱形图 簇状柱形图主要用于比较相交类别轴上的数值大小 堆积柱形图主要用于比较相交于类别轴上每一数值所占总数值的大小三维柱形图主要用于比较相交于类别轴和相交于系列轴的数值 参见演示文件 2 创建百分比结构分析饼图 饼图用于显示数据系列中每一项占该系列数值总和的比例关系 Excel中常见有 饼图 复合饼图和分离型饼图 饼图常用于财务成本数据分析 费用占比分析等方面 参见演示文件 3 创建数据走势分析折线图 使用折线图可以对大批分组的数据进行分析 并能快速确定产品的销售趋势和走向 Excel常见折线图有 折线图 堆积折线图 数据点折线图 堆积数据点折线图等 参见演示文件 小结 表格设计数据输入数据管理图表 作业 1 参见学生作业资料表 2 请分组 提交小组名单 本讲还有问题