Excel高效财务管理应用.pptx

《Excel高效财务管理应用.pptx》由会员分享，可在线阅读，更多相关《Excel高效财务管理应用.pptx（82页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel 高效财务管理应用高效财务管理应用主讲教师主讲教师 韩良智韩良智 教授教授北京科技大学东凌经济管理学院北京科技大学东凌经济管理学院目录目录1 运用运用Excel高效处理数据高效处理数据2 销售管理销售管理3 营运资金管理营运资金管理4 成本费用管理成本费用管理5 财务预测与财务报表分析财务预测与财务报表分析1 运用运用Excel高效处理数据高效处理数据1.1 文本型数据及其处理方法文本型数据及其处理方法1.2 数值型数据及其处理方法数值型数据及其处理方法1.3 日期型数据及其处理方法日期型数据及其处理方法1.4 时间型数据及其处理方法时间型数据及其处理方法1.5 高效整理数据表高效整

2、理数据表1.1 文本型数据及其处理方法文本型数据及其处理方法1.1.1 文本型数据的输入及文本运算符文本型数据的输入及文本运算符1.1.2 常用文本函数的应用常用文本函数的应用1.1.3 将文本型数字转化为纯数字将文本型数字转化为纯数字1.1.1 文本型数据的输入及文本运算符文本型数据的输入及文本运算符 文本型数字的输入文本型数字的输入 设置单元格格式后输入设置单元格格式后输入 使用单引号使用单引号 使用双引号使用双引号 文本运算符文本运算符 &符号符号1.1.2 常用文本函数的应用常用文本函数的应用 LEFT函数：从左边取指定长度的字符。函数：从左边取指定长度的字符。 RIGHT函数：从右边

3、取指定长度的字符。函数：从右边取指定长度的字符。 MID函数：从指定位置取指定长度的字符。函数：从指定位置取指定长度的字符。 TEXT函数：将数值转换为指定格式的文本。函数：将数值转换为指定格式的文本。 ISTEXT函数：检验数据是否为文本。函数：检验数据是否为文本。1.1.3 将文本型数字转化为纯数字将文本型数字转化为纯数字 利用智能标记利用智能标记 选择性粘贴选择性粘贴 利用利用VALUE函数函数 利用公式利用公式 删除数字中的特殊字符删除数字中的特殊字符 【例例1-1】 文本型数据的处理文本型数据的处理1.2 数值型数据及其处理方法数值型数据及其处理方法1.2.1 数值型数据的输入数值型

4、数据的输入1.2.2 与数值型数据有关的函数与数值型数据有关的函数1.2.3 将小写金额转换为大写将小写金额转换为大写1.2.4 缩位显示数据缩位显示数据1.2.1 数值型数据的输入数值型数据的输入 输入负数输入负数 输入分数输入分数 常规数字格式与科学计数法常规数字格式与科学计数法 有效数字位数的限制有效数字位数的限制1.2.2 与数值型数据有关的函数与数值型数据有关的函数 MAX函数：求最大值。函数：求最大值。 MIN函数：求最小值。函数：求最小值。 AVERAGE函数：求平均数。函数：求平均数。 ROUND函数：四舍五入。函数：四舍五入。 INT函数：无条件舍去小数，保留整数部分。函数：

5、无条件舍去小数，保留整数部分。 【例例1-2】处理计算误差处理计算误差1.2.3 将小写金额转换为大写将小写金额转换为大写 使用特殊格式显示大写数字使用特殊格式显示大写数字 使用使用NUMBERSTRING函数函数 【例例1-3】小写金额转换成大写小写金额转换成大写1.2.4 缩位显示数据缩位显示数据 使用自定义数字格式缩位显示数据代码：使用自定义数字格式缩位显示数据代码： 缩小缩小1百位百位 0.00 缩小缩小1千位显示数字：千位显示数字： 0.00， 缩小缩小1万位显示数字：万位显示数字： 0!.0, 缩小缩小10万位显示数字：万位显示数字： 0!.00, 缩小缩小100万位显示数字：万位

6、显示数字： 0.00, 缩小缩小1000万位显示数字：万位显示数字： 0!.0, 缩小缩小1亿位显示数字：亿位显示数字： 0!.00, 缩小缩小10亿位显示数字：亿位显示数字： 0.00,1.3 日期型数据及其处理方法日期型数据及其处理方法1.3.1 日期型数据的输入及显示格式日期型数据的输入及显示格式1.3.2 日期型数据的计算日期型数据的计算1.3.3 与日期型数据有关的函数与日期型数据有关的函数1.3.4 查找与修改非法日期查找与修改非法日期1.3.1 日期型数据的输入及显示格式日期型数据的输入及显示格式 日期型数据的输入日期型数据的输入 日期型数据的显示格式日期型数据的显示格式 常规显

7、示格式常规显示格式 自定义显示格式自定义显示格式1.3.2 日期型数据的计算日期型数据的计算 日期型数据计算的应用情况日期型数据计算的应用情况 计算应收账款账龄计算应收账款账龄 计算应付账款账龄计算应付账款账龄 计算工龄计算工龄 设置常规数据格式设置常规数据格式1.3.3 与日期型数据有关的函数与日期型数据有关的函数 TODAY函数：返回系统当前日期的序列号。函数：返回系统当前日期的序列号。 DATE函数：将三个数字组成一个日期序列号。函数：将三个数字组成一个日期序列号。 YEAR函数：获取指定日期序列号的年份数字。函数：获取指定日期序列号的年份数字。 MONTH函数：获取指定日期序列号的月份

8、数字。函数：获取指定日期序列号的月份数字。 DAY函数：获取指定日期序列号的日数字。函数：获取指定日期序列号的日数字。 WEEKDAY：获取某日期为星期几的数字。：获取某日期为星期几的数字。1.3.4 查找与修改非法日期查找与修改非法日期 查找非法日期查找非法日期 利用利用“单元格格式单元格格式”对话框判断对话框判断 使用使用ISNUMBER函数判断函数判断 设置数据有效性并圈释无效数据设置数据有效性并圈释无效数据 修改非法日期修改非法日期 查找与替换查找与替换 使用函数使用函数 使用分列工具使用分列工具 【例例1-4】处理非法日期数据处理非法日期数据1.4 时间型数据及其处理方法时间型数据及

9、其处理方法1.4.1 时间型数据的输入及显示格式时间型数据的输入及显示格式1.4.2 时间型数据的计算时间型数据的计算1.4.3 与时间型数据有关的函数与时间型数据有关的函数1.4.1 时间型数据的输入及显示格式时间型数据的输入及显示格式 时间型数据的输入时间型数据的输入 时间型数据的显示格式时间型数据的显示格式 常规显示格式常规显示格式 自定义显示格式自定义显示格式1.4.2 时间型数据的计算时间型数据的计算 【例例1-5】计算工作时数计算工作时数 注意跨越午夜的情况注意跨越午夜的情况1.4.3 与时间型数据有关的函数与时间型数据有关的函数 NOW函数：返回当前日期和时间所对应的序列号。函数

10、：返回当前日期和时间所对应的序列号。 HOUR函数：用于获取时间值的小时数。函数：用于获取时间值的小时数。 MINUTE函数：用于获取时间值的分钟数。函数：用于获取时间值的分钟数。 SECOND函数：用于获取时间值的秒数。函数：用于获取时间值的秒数。1.5 高效整理数据表高效整理数据表1.5.1 快速删除数据表中的所有空行快速删除数据表中的所有空行1.5.2 快速填充数据表中的所有空单元格快速填充数据表中的所有空单元格1.5.3 利用分列工具高效整理数据利用分列工具高效整理数据1.5.4 整理数据表综合练习整理数据表综合练习1.5.1 快速删除数据表中的所有空行快速删除数据表中的所有空行 排序

11、法排序法 筛选法筛选法 定位法定位法 【例例1-6】快速删除所有空行快速删除所有空行1.5.2 快速填充数据表中的所有空单元格快速填充数据表中的所有空单元格 快速填充表格中的所有空单元格快速填充表格中的所有空单元格 将空单元格填充为上一个单元格数据将空单元格填充为上一个单元格数据 【例例1-7】快速填充数据快速填充数据1.5.3 利用分列工具高效整理数据利用分列工具高效整理数据 【例例1-8】整理银行对账单整理银行对账单整理前整理后1.5.4 整理数据表综合练习整理数据表综合练习 快速整理从快速整理从ERP中导入的数据表中导入的数据表 【例例1-9】整理数据综合练习整理数据综合练习2 销售管理

12、销售管理 2.1 销售数据的透视分析销售数据的透视分析2.2 数据透视表的高级应用数据透视表的高级应用2.3 高效汇总多重区域的销售数据高效汇总多重区域的销售数据2.4 使用函数汇总销售额使用函数汇总销售额2.5 绘制销售图表绘制销售图表2.1 销售数据的透视分析销售数据的透视分析2.1.1 制作数据透视表的基本方法制作数据透视表的基本方法2.1.2 数据透视表的编辑与更新方法数据透视表的编辑与更新方法2.1.3 运用数据透视表多角度分析财务数据运用数据透视表多角度分析财务数据2.1.4 制作与使用数据透视图制作与使用数据透视图【例例2-1】销售数据透视分析销售数据透视分析2.1.1 制作数据

13、透视表的基本方法制作数据透视表的基本方法 【数据数据】/【数据透视表和数据透视图数据透视表和数据透视图】 合理设置数据透视表的布局合理设置数据透视表的布局 相关概念相关概念 页字段页字段 行字段行字段 列字段列字段 数据字段数据字段2.1.2 数据透视表的编辑与更新方法数据透视表的编辑与更新方法 修改字段名称修改字段名称 设置字段的数字格式设置字段的数字格式 取消数据透视表行总计或列总计取消数据透视表行总计或列总计 取消字段的分类汇总取消字段的分类汇总 合并数据标志合并数据标志 设置错误值或空值的显示方式设置错误值或空值的显示方式 更新数据透视表的数据更新数据透视表的数据2.1.3 运用数据透

14、视表多角度分析销售数据运用数据透视表多角度分析销售数据 修改数据透视表布局修改数据透视表布局 显示明细数据显示明细数据2.1.4 制作与使用数据透视图制作与使用数据透视图 在数据清单基础上制作数据透视图在数据清单基础上制作数据透视图 在数据透视表基础上制作数据透视图在数据透视表基础上制作数据透视图2.2 数据透视表的高级应用数据透视表的高级应用2.2.1 通过组合字段创建多维数据汇总表通过组合字段创建多维数据汇总表2.2.2 通过设置字段显示方式创建各种比较分析表通过设置字段显示方式创建各种比较分析表2.2.3 通过自定义设置灵活编制销售数据汇总表通过自定义设置灵活编制销售数据汇总表2.2.1

15、 通过组合字段创建多维销售数据汇总表通过组合字段创建多维销售数据汇总表 按不同字段分组查看数据按不同字段分组查看数据 按日期分组按日期分组 按地区分组按地区分组 按商品类别分组按商品类别分组2.2.2 通过设置字段显示方式通过设置字段显示方式创建各种比较分析表创建各种比较分析表 差异或差异百分比差异或差异百分比 占同行或同列百分比占同行或同列百分比 占总合百分比占总合百分比 累计值显示累计值显示 纵向差异比较纵向差异比较2.2.3 通过自定义设置通过自定义设置灵活编制数据汇总表灵活编制数据汇总表 添加自定义计算字段添加自定义计算字段 添加自定义计算项添加自定义计算项2.3 使用数据透视表汇总多

16、重区域数据使用数据透视表汇总多重区域数据 【例例2-2】高效汇总多重区域的销售数据高效汇总多重区域的销售数据2.4 使用函数汇总销售数据使用函数汇总销售数据 【例例2-3】按客户汇总销售额按客户汇总销售额 SUMIF函数函数条件求和条件求和2.5 绘制销售图表绘制销售图表2.4.1 制作组合销售图表制作组合销售图表 【例例2-4】 2.4.2 制作销售预算完成进度图表制作销售预算完成进度图表 【例例2-5】2.4.3 绘制动态销售图表绘制动态销售图表 【例例2-6】 INDEX函数函数返回数组中指定行和列处的元素值。返回数组中指定行和列处的元素值。 使用组合框控件使用组合框控件3 营运资金管理

17、营运资金管理3.1 现金管理现金管理3.2 应收账款管理应收账款管理3.3 存货管理存货管理3.4 应付账款管理应付账款管理3.1 现金管理现金管理3.1.1 普通日记账与现金日记账普通日记账与现金日记账3.1.2 现金预算表及其合并计算现金预算表及其合并计算3.1.1 普通日记账与现金日记账普通日记账与现金日记账 【例例3-1】普通日记账与现金日记账普通日记账与现金日记账 整理标准数据清单整理标准数据清单 筛选现金日记账筛选现金日记账 计算现金余额计算现金余额 设置条件格式设置条件格式3.1.2 现金预算表及其合并计算现金预算表及其合并计算 【例例3-2】 数据数据/合并计算合并计算 被合并

18、计算的数据区域结构应相同被合并计算的数据区域结构应相同3.2 应收账款管理应收账款管理3.2.1 建立应收账款台账建立应收账款台账 3.2.2 应收账款的排序分析应收账款的排序分析3.2.3 应收账款的筛选分析应收账款的筛选分析3.2.4 应收账款分类汇总分析应收账款分类汇总分析3.2.5 制作应收账款账龄分析表制作应收账款账龄分析表 3.2.1建立应收账款台账建立应收账款台账【例例3-3】 利用记录单输入和编辑数据利用记录单输入和编辑数据 数据数据/记录单记录单 拆分工作表拆分工作表 窗口窗口/拆分拆分3.2.2 应收账款的排序分析应收账款的排序分析【例例3-4】 数据数据/排序排序 一个关

19、键字的情况一个关键字的情况 多个关键字的情况多个关键字的情况3.2.3 应收账款的筛选分析应收账款的筛选分析【例例3-5】 数据数据/筛选筛选 自动筛选自动筛选 高级筛选高级筛选 一个条件的情况一个条件的情况 多个条件的情况多个条件的情况3.2.4 应收账款分类汇总分析应收账款分类汇总分析【例例3-6】 建立分类汇总建立分类汇总 数据数据/分类汇总分类汇总 查看分类汇总结果查看分类汇总结果 复制分类汇总结果复制分类汇总结果 选择可见单元格选择可见单元格 删除分类汇总删除分类汇总3.2.5 制作应收账款账龄分析表制作应收账款账龄分析表【例例3-7】 SUMIF函数函数根据指定条件对若干单元根据指

20、定条件对若干单元格求和。格求和。 SUMPRODUCT函数函数将给定的几个数将给定的几个数组的对应的元素相乘，并返回乘积之和。组的对应的元素相乘，并返回乘积之和。3.3 存货管理存货管理3.3.1 建立存货收发存明细表建立存货收发存明细表3.3.2 存货查询存货查询3.3.3 存货的存货的ABC分类模型分类模型3.3.4 经济订货批量模型经济订货批量模型3.3.1 建立存货收发存明细表建立存货收发存明细表【例例3-8】 为整列定义名称为整列定义名称 删除网格线删除网格线 运用条件格式设置边框和字体颜色运用条件格式设置边框和字体颜色3.3.2 存货查询存货查询【例例3-9】 VLOOKUP函数函

21、数在表格或数值数组的首列查在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行找指定的数值，并由此返回表格或数组当前行中指定列处的数值。中指定列处的数值。 COLUMN函数函数返回给定引用的列标。返回给定引用的列标。3.3.3 存货的存货的ABC分类模型分类模型 存货的分类存货的分类 A类存货类存货关键的少数关键的少数 C类存货类存货不重要的多数不重要的多数 B类存货类存货居中的类别居中的类别 常见的分类标准：常见的分类标准：存货类别存货类别品种品种%金额金额%A类类10%15%约约80%B类类20%30%约约15%C类类55%80%约约5%3.3.3 存货的存货的ABC分类模型分

22、类模型 【例例3-10】 COUNTA函数函数返回参数列表中非空值的单元返回参数列表中非空值的单元格个数。格个数。 COUNTIF函数函数计算区域中满足给定条件的计算区域中满足给定条件的单元格的个数。单元格的个数。 绘制两轴线柱形图绘制两轴线柱形图3.3.4 经济订货批量模型经济订货批量模型 存货的相关总成本（总订储费用）存货的相关总成本（总订储费用） 经济订货批量经济订货批量 最佳的订货次数最佳的订货次数 最低的总订储费用最低的总订储费用 式中：式中：Q为一次订货批量；为一次订货批量；D为一定时期存货的需求量；为一定时期存货的需求量；A为一次订货费；为一次订货费；P为存货单价；为存货单价；K

23、为存货的存储费率；为存货的存储费率；P K即即为单位存储费用。为单位存储费用。KPQAQDT2PKDAQ2* *QDN DAPKT2*3.3.4 经济订货批量模型经济订货批量模型【例例3-11】计算经济订货批量计算经济订货批量 SQRT函数函数开平方开平方 建立和使用自定义函数建立和使用自定义函数 工具工具/宏宏/Visual Basic编辑器编辑器 插入插入/模块模块 插入插入/过程过程3.4 应付账款管理应付账款管理3.4.1 应付账款到期日的计算应付账款到期日的计算3.4.2 应付账款到期日提前提醒应付账款到期日提前提醒 3.4.1 应付账款到期日的计算应付账款到期日的计算 EDATE函

24、数函数计算出所指定月数之计算出所指定月数之前或之后的日期前或之后的日期 EOMONTH函数函数计算指定日期到月计算指定日期到月底的天数底的天数 【例例3-12】计算应付账款到期日计算应付账款到期日3.4.2 应付账款到期日提前提醒应付账款到期日提前提醒 设置条件格式设置条件格式 【例例3-13】应付账款到期日提前提醒应付账款到期日提前提醒4 成本费用管理成本费用管理4.1 材料费用管理材料费用管理4.2 工资费用管理工资费用管理4.3 期间费用管理期间费用管理4.1 材料费用管理材料费用管理4.1.1 制作限额领料单制作限额领料单4.1.2 快速编制原材料费用预算快速编制原材料费用预算 4.1

25、.1 制作限额领料单制作限额领料单【例例4-1】制作限额领料单制作限额领料单 数据有效性的设置数据有效性的设置 隐藏行和列隐藏行和列 保护单元格和保护工作表保护单元格和保护工作表 为工作簿设置密码为工作簿设置密码3.1.2 快速编制原材料费用预算快速编制原材料费用预算【例例3-2】计算原材料成本计算原材料成本【例例3-3】编制材料需求量预算表编制材料需求量预算表 MMULT函数函数返回两数组的矩阵乘积返回两数组的矩阵乘积4.1.2 快速编制原材料费用预算快速编制原材料费用预算【例例4-2】计算原材料成本计算原材料成本【例例4-3】编制材料需求量预算表编制材料需求量预算表 MMULT函数函数返回

26、两数组的矩阵乘积返回两数组的矩阵乘积4.2 工资费用管理工资费用管理4.2.1 工资和奖金的计算工资和奖金的计算4.2.2 快速制作工资条快速制作工资条 4.2.1 工资和奖金的计算工资和奖金的计算【例例4-4】计算基本工资和工龄工资计算基本工资和工龄工资 根据职称确定基本工资根据职称确定基本工资 根据工龄计算工龄工资根据工龄计算工龄工资【例例4-5】计算销售人员年度工资和奖金计算销售人员年度工资和奖金 HLOOKUP函数函数在表格或数值数组的首行查找指定的在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。数值，并由此返回表格或数组当前列中指定行处的数值。【例例

27、4-6】计算个人所得税计算个人所得税 使用使用IF函数计算函数计算 建立自定义函数计算建立自定义函数计算4.2.2 快速制作工资条快速制作工资条【例例4-7】快速制作工资条快速制作工资条4.3 期间费用管理期间费用管理4.3.1 营业费用自动汇总表营业费用自动汇总表4.3.2 费用预算执行情况分析图费用预算执行情况分析图 4.3.1 营业费用自动汇总表营业费用自动汇总表 INDIRECT函数函数返回由文本字符串指定的引用返回由文本字符串指定的引用 ROW函数函数返回行号返回行号 【例例4-8】 营业费用自动汇总表营业费用自动汇总表4.3.2 费用预算执行情况分析图费用预算执行情况分析图【例例4

28、-9】 组合图表组合图表 数据系列格式设置数据系列格式设置5 财务预测与财务报表分析财务预测与财务报表分析5.1 财务预测财务预测5.2 财务报表分析财务报表分析5.1 财务预测财务预测5.1.1 利用绘图工具预测利用绘图工具预测5.1.2 利用相关函数预测利用相关函数预测5.1.3 利用数据分析工具预测利用数据分析工具预测5.1.1 利用绘图工具预测利用绘图工具预测 【例例5-1】创建散点图创建散点图添加趋势线添加趋势线5.1.2 利用相关函数预测利用相关函数预测 常用的线性预测函数常用的线性预测函数 INTERCEPT函数函数求回归直线的截距求回归直线的截距 SLOPE求回归直线的斜率。求

29、回归直线的斜率。 TREND函数函数返回一条线性回归拟合线的值。返回一条线性回归拟合线的值。 【例例5-2】成本预测成本预测更多的预测函数更多的预测函数 其他线性预测函数其他线性预测函数 LINEST函数函数使用最小二乘法对已知数据进行最佳直使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。线拟合，并返回描述此直线的数组。 FORECAST函数函数根据已有的数值计算或预测未来值。根据已有的数值计算或预测未来值。此预测值为基于给定的此预测值为基于给定的 x 值推导出的值推导出的 y 值。值。 指数预测函数指数预测函数 LOGEST函数函数在回归分析中，计算最符合数据的指数在回归分

30、析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。回归拟合曲线，并返回描述该曲线的数值数组。 GROWTH函数函数根据现有的数据预测指数增长值。根据现有的数据预测指数增长值。5.1.3 利用数据分析工具预测利用数据分析工具预测【例例5-3】基于移动平均法的销售预基于移动平均法的销售预测测 工具工具/数据分析数据分析/移动平均移动平均【例例5-4】基于回归分析法的销售预基于回归分析法的销售预测测 工具工具/数据分析数据分析/回归回归5.2 财务报表分析财务报表分析5.2.1 制作财务报表分析模型的封面制作财务报表分析模型的封面5.2.2 比较财务报表与共同比报表比较财务报表与共

31、同比报表5.2.3 财务比率分析财务比率分析5.2.4 杜邦系统分析杜邦系统分析5.2.5 综合财务分析综合财务分析5.2.6 绘制财务指标雷达图绘制财务指标雷达图5.2.1 制作财务报表分析模型的封面制作财务报表分析模型的封面 插入图形对象插入图形对象 插入艺术字插入艺术字 插入超链接插入超链接 【例例6-5】财务报表分析模型财务报表分析模型5.2.2 比较财务报表与共同比报表比较财务报表与共同比报表 资产负债表分析资产负债表分析 利润表分析利润表分析 现金流量表分析现金流量表分析 AND函数函数 ISBLANK函数函数5.2.3 财务比率分析财务比率分析 偿债能力比率偿债能力比率 获利能力比率获利能力比率 营运能力比率营运能力比率 成长能力比率成长能力比率5.2.4 杜邦系统分析杜邦系统分析 录制宏录制宏 查看宏代码查看宏代码 运行宏运行宏 使用命令按钮运行宏使用命令按钮运行宏5.2.5 编制综合评分表编制综合评分表 选定评价指标选定评价指标 确定标准评分值确定标准评分值 确定行业标准值确定行业标准值 计算指标实际值计算指标实际值 计算综合得分计算综合得分 做出综合评价做出综合评价5.2.6 绘制财务指标雷达图绘制财务指标雷达图