Excel高效财务管理应用tfq.pptx

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

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

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

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

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

5、金额转换为大写使用特殊格式显示大写数字使用特殊格式显示大写数字使用使用NUMBERSTRING函数函数【例【例1-3】小写金额转换成大写】小写金额转换成大写1.2.4缩位显示数据缩位显示数据 使用自定义数字格式缩位显示数据代码：使用自定义数字格式缩位显示数据代码：缩缩小小1百位百位0.00缩小缩小1千位显示数字：千位显示数字：0.00，缩小缩小1万位显示数字：万位显示数字：0!.0,缩小缩小10万位显示数字：万位显示数字：0!.00,缩小缩小100万位显示数字：万位显示数字：0.00,缩小缩小1000万位显示数字：万位显示数字：0!.0,缩小缩小1亿位显示数字：亿位显示数字：0!.00,缩小缩

6、小10亿位显示数字：亿位显示数字：0.00,1.3日期型数据及其处理方法日期型数据及其处理方法1.3.1日期型数据的输入及显示格式日期型数据的输入及显示格式1.3.2日期型数据的计算日期型数据的计算1.3.3与日期型数据有关的函数与日期型数据有关的函数1.3.4查找与修改非法日期查找与修改非法日期1.3.1日期型数据的输入及显示格式日期型数据的输入及显示格式日期型数据的输入日期型数据的输入日期型数据的显示格式日期型数据的显示格式常规显示格式常规显示格式自定义显示格式自定义显示格式1.3.2日期型数据的计算日期型数据的计算日期型数据计算的应用情况日期型数据计算的应用情况计算应收账款账龄计算应收账

7、款账龄计算应付账款账龄计算应付账款账龄计算工龄计算工龄设置常规数据格式设置常规数据格式1.3.3与日期型数据有关的函数与日期型数据有关的函数TODAY函数：返回系统当前日期的序列号。函数：返回系统当前日期的序列号。DATE函数：将三个数字组成一个日期序列号。函数：将三个数字组成一个日期序列号。YEAR函数：获取指定日期序列号的年份数字。函数：获取指定日期序列号的年份数字。MONTH函数：获取指定日期序列号的月份数字。函数：获取指定日期序列号的月份数字。DAY函数：获取指定日期序列号的日数字。函数：获取指定日期序列号的日数字。WEEKDAY：获取某日期为星期几的数字。：获取某日期为星期几的数字。

8、1.3.4查找与修改非法日期查找与修改非法日期查找非法日期查找非法日期利用利用“单元格格式单元格格式”对话框判断对话框判断使用使用ISNUMBER函数判断函数判断设置数据有效性并圈释无效数据设置数据有效性并圈释无效数据修改非法日期修改非法日期查找与替换查找与替换使用函数使用函数使用分列工具使用分列工具【例【例1-4】处理非法日期数据】处理非法日期数据1.4时间型数据及其处理方法时间型数据及其处理方法1.4.1时间型数据的输入及显示格式时间型数据的输入及显示格式1.4.2时间型数据的计算时间型数据的计算1.4.3与时间型数据有关的函数与时间型数据有关的函数1.4.1时间型数据的输入及显示格式时间

9、型数据的输入及显示格式时间型数据的输入时间型数据的输入时间型数据的显示格式时间型数据的显示格式常规显示格式常规显示格式自定义显示格式自定义显示格式1.4.2时间型数据的计算时间型数据的计算【例【例1-5】计算工作时数】计算工作时数注意跨越午夜的情况注意跨越午夜的情况1.4.3与时间型数据有关的函数与时间型数据有关的函数NOW函数：返回当前日期和时间所对应的序列号。函数：返回当前日期和时间所对应的序列号。HOUR函数：用于获取时间值的小时数。函数：用于获取时间值的小时数。MINUTE函数：用于获取时间值的分钟数。函数：用于获取时间值的分钟数。SECOND函数：用于获取时间值的秒数。函数：用于获取

10、时间值的秒数。1.5高效整理数据表高效整理数据表1.5.1快速删除数据表中的所有空行快速删除数据表中的所有空行1.5.2快速填充数据表中的所有空单元格快速填充数据表中的所有空单元格1.5.3利用分列工具高效整理数据利用分列工具高效整理数据1.5.4整理数据表综合练习整理数据表综合练习1.5.1快速删除数据表中的所有空行快速删除数据表中的所有空行排序法排序法筛选法筛选法定位法定位法【例【例1-6】快速删除所有空行】快速删除所有空行1.5.2快速填充数据表中的所有空单元格快速填充数据表中的所有空单元格快速填充表格中的所有空单元格快速填充表格中的所有空单元格将空单元格填充为上一个单元格数据将空单元格

11、填充为上一个单元格数据【例【例1-7】快速填充数据】快速填充数据1.5.3利用分列工具高效整理数据利用分列工具高效整理数据【例【例1-8】整理银行对账单】整理银行对账单整理前整理后1.5.4整理数据表综合练习整理数据表综合练习快速整理从快速整理从ERP中导入的数据表中导入的数据表【例【例1-9】整理数据综合练习】整理数据综合练习2销售管理销售管理 2.1销售数据的透视分析销售数据的透视分析2.2数据透视表的高级应用数据透视表的高级应用2.3高效汇总多重区域的销售数据高效汇总多重区域的销售数据2.4使用函数汇总销售额使用函数汇总销售额2.5绘制销售图表绘制销售图表2.1销售数据的透视分析销售数据

12、的透视分析2.1.1制作数据透视表的基本方法制作数据透视表的基本方法2.1.2数据透视表的编辑与更新方法数据透视表的编辑与更新方法2.1.3运用数据透视表多角度分析财务数据运用数据透视表多角度分析财务数据2.1.4制作与使用数据透视图制作与使用数据透视图【例【例2-1】销售数据透视分析】销售数据透视分析2.1.1制作数据透视表的基本方法制作数据透视表的基本方法【数据】【数据】/【数据透视表和数据透视图】【数据透视表和数据透视图】合理设置数据透视表的布局合理设置数据透视表的布局相关概念相关概念页字段页字段行字段行字段列字段列字段数据字段数据字段2.1.2数据透视表的编辑与更新方法数据透视表的编辑

13、与更新方法修改字段名称修改字段名称设置字段的数字格式设置字段的数字格式取消数据透视表行总计或列总计取消数据透视表行总计或列总计取消字段的分类汇总取消字段的分类汇总合并数据标志合并数据标志设置错误值或空值的显示方式设置错误值或空值的显示方式更新数据透视表的数据更新数据透视表的数据2.1.3运用数据透视表多角度分析销售数据运用数据透视表多角度分析销售数据修改数据透视表布局修改数据透视表布局显示明细数据显示明细数据2.1.4制作与使用数据透视图制作与使用数据透视图在数据清单基础上制作数据透视图在数据清单基础上制作数据透视图在数据透视表基础上制作数据透视图在数据透视表基础上制作数据透视图2.2数据透视

14、表的高级应用数据透视表的高级应用2.2.1通过组合字段创建多维数据汇总表通过组合字段创建多维数据汇总表2.2.2通过设置字段显示方式创建各种比较分析表通过设置字段显示方式创建各种比较分析表2.2.3通过自定义设置灵活编制销售数据汇总表通过自定义设置灵活编制销售数据汇总表2.2.1通过组合字段创建多维销售数据汇总表通过组合字段创建多维销售数据汇总表按不同字段分组查看数据按不同字段分组查看数据按日期分组按日期分组按地区分组按地区分组按商品类别分组按商品类别分组2.2.2通过设置字段显示方式通过设置字段显示方式创建各种比较分析表创建各种比较分析表差异或差异百分比差异或差异百分比占同行或同列百分比占同

15、行或同列百分比占总合百分比占总合百分比累计值显示累计值显示纵向差异比较纵向差异比较2.2.3通过自定义设置通过自定义设置灵活编制数据汇总表灵活编制数据汇总表添加自定义计算字段添加自定义计算字段添加自定义计算项添加自定义计算项2.3使用数据透视表汇总多重区域数据使用数据透视表汇总多重区域数据【例【例2-2】高效汇总多重区域的销售数据】高效汇总多重区域的销售数据2.4使用函数汇总销售数据使用函数汇总销售数据【例【例2-3】按客户汇总销售额】按客户汇总销售额SUMIF函数函数条件求和条件求和2.5绘制销售图表绘制销售图表2.4.1制作组合销售图表制作组合销售图表【例【例2-4】2.4.2制作销售预算

16、完成进度图表制作销售预算完成进度图表【例【例2-5】2.4.3绘制动态销售图表绘制动态销售图表【例【例2-6】INDEX函数函数返回数组中指定行和列处的元素值。返回数组中指定行和列处的元素值。使用组合框控件使用组合框控件3营运资金管理营运资金管理3.1现金管理现金管理3.2应收账款管理应收账款管理3.3存货管理存货管理3.4应付账款管理应付账款管理3.1现金管理现金管理3.1.1普通日记账与现金日记账普通日记账与现金日记账3.1.2现金预算表及其合并计算现金预算表及其合并计算3.1.1普通日记账与现金日记账普通日记账与现金日记账【例【例3-1】普通日记账与现金日记账】普通日记账与现金日记账整理

17、标准数据清单整理标准数据清单筛选现金日记账筛选现金日记账计算现金余额计算现金余额设置条件格式设置条件格式3.1.2现金预算表及其合并计算现金预算表及其合并计算【例【例3-2】数据数据/合并计算合并计算 被合并计算的数据区域结构应相同被合并计算的数据区域结构应相同3.2应收账款管理应收账款管理3.2.1建立应收账款台账建立应收账款台账3.2.2应收账款的排序分析应收账款的排序分析3.2.3应收账款的筛选分析应收账款的筛选分析3.2.4应收账款分类汇总分析应收账款分类汇总分析3.2.5制作应收账款账龄分析表制作应收账款账龄分析表3.2.1建立应收账款台账建立应收账款台账【例【例3-3】利用记录单输

18、入和编辑数据利用记录单输入和编辑数据数据数据/记录单记录单拆分工作表拆分工作表窗口窗口/拆分拆分3.2.2应收账款的排序分析应收账款的排序分析【例【例3-4】数据数据/排序排序一个关键字的情况一个关键字的情况多个关键字的情况多个关键字的情况3.2.3应收账款的筛选分析应收账款的筛选分析【例【例3-5】数据数据/筛选筛选自动筛选自动筛选高级筛选高级筛选一个条件的情况一个条件的情况多个条件的情况多个条件的情况3.2.4应收账款分类汇总分析应收账款分类汇总分析【例【例3-6】建立分类汇总建立分类汇总数据数据/分类汇总分类汇总查看分类汇总结果查看分类汇总结果复制分类汇总结果复制分类汇总结果选择可见单元

19、格选择可见单元格删除分类汇总删除分类汇总3.2.5制作应收账款账龄分析表制作应收账款账龄分析表【例【例3-7】SUMIF函数函数根据指定条件对若干单元根据指定条件对若干单元格求和。格求和。SUMPRODUCT函数函数将给定的几个数将给定的几个数组的对应的元素相乘，并返回乘积之和。组的对应的元素相乘，并返回乘积之和。3.3存货管理存货管理3.3.1建立存货收发存明细表建立存货收发存明细表3.3.2存货查询存货查询3.3.3存货的存货的ABC分类模型分类模型3.3.4经济订货批量模型经济订货批量模型3.3.1建立存货收发存明细表建立存货收发存明细表【例【例3-8】为整列定义名称为整列定义名称删除网

20、格线删除网格线运用条件格式设置边框和字体颜色运用条件格式设置边框和字体颜色3.3.2存货查询存货查询【例【例3-9】VLOOKUP函数函数在表格或数值数组的首列查在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中找指定的数值，并由此返回表格或数组当前行中指定列处的数值。指定列处的数值。COLUMN函数函数返回给定引用的列标。返回给定引用的列标。3.3.3存货的存货的ABC分类模型分类模型存货的分类存货的分类A类存货类存货关键的少数关键的少数C类存货类存货不重要的多数不重要的多数B类存货类存货居中的类别居中的类别常见的分类标准：常见的分类标准：存货类别存货类别品种品种%金额金额%

21、A类类10%15%约约80%B类类20%30%约约15%C类类55%80%约约5%3.3.3存货的存货的ABC分类模型分类模型【例【例3-10】COUNTA函数函数返回参数列表中非空值的单元返回参数列表中非空值的单元格个数。格个数。COUNTIF函数函数计算区域中满足给定条件的计算区域中满足给定条件的单元格的个数。单元格的个数。绘制两轴线柱形图绘制两轴线柱形图3.3.4经济订货批量模型经济订货批量模型存货的相关总成本（总订储费用）存货的相关总成本（总订储费用）经济订货批量经济订货批量最佳的订货次数最佳的订货次数最低的总订储费用最低的总订储费用式中：式中：Q为一次订货批量；为一次订货批量；D为一

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

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

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

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

26、例【例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-9】组合

27、图表组合图表数据系列格式设置数据系列格式设置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求回归直线的斜率。求回归直线的斜率。TREND函数函数返回

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

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

30、析杜邦系统分析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绘制财务指标雷达图绘制财务指标雷达图