Excel高效财务管理应用

Excel高效财务管理应用主讲教师韩良智教授北京科技大学东凌经济管理学院[e-mail]目录1运用Excel高效处理数据2销售管理3营运资金管理4成本费用管理5财务预测与财务报表分析1运用Excel高效处理数据1.1文本型数据及其处理方法1.2数值型数据及其处理方法1.3日期型数据及其处理方法1.4时间型数据及其处理方法1.5高效整理数据表1.1文本型数据及其处理方法1.1.1文本型数据的输入及文本运算符1.1.2常用文本函数的应用1.1.3将文本型数字转化为纯数字1.1.1文本型数据的输入及文本运算符•文本型数字的输入–设置单元格格式后输入–使用单引号–使用双引号•文本运算符–&符号1.1.2常用文本函数的应用•LEFT函数：从左边取指定长度的字符。•RIGHT函数：从右边取指定长度的字符。•MID函数：从指定位置取指定长度的字符。•TEXT函数：将数值转换为指定格式的文本。•ISTEXT函数：检验数据是否为文本。1.1.3将文本型数字转化为纯数字•利用智能标记•选择性粘贴•利用VALUE函数•利用公式•删除数字中的特殊字符•【例1-1】文本型数据的处理1.2数值型数据及其处理方法1.2.1数值型数据的输入1.2.2与数值型数据有关的函数1.2.3将小写金额转换为大写1.2.4缩位显示数据1.2.1数值型数据的输入•输入负数•输入分数•常规数字格式与科学计数法•有效数字位数的限制1.2.2与数值型数据有关的函数•MAX函数：求最大值。•MIN函数：求最小值。•AVERAGE函数：求平均数。•ROUND函数：四舍五入。•INT函数：无条件舍去小数，保留整数部分。•【例1-2】处理计算误差1.2.3将小写金额转换为大写•使用特殊格式显示大写数字•使用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,,–缩小10亿位显示数字：0.00,,,1.3日期型数据及其处理方法1.3.1日期型数据的输入及显示格式1.3.2日期型数据的计算1.3.3与日期型数据有关的函数1.3.4查找与修改非法日期1.3.1日期型数据的输入及显示格式•日期型数据的输入•日期型数据的显示格式–常规显示格式–自定义显示格式1.3.2日期型数据的计算•日期型数据计算的应用情况–计算应收账款账龄–计算应付账款账龄–计算工龄•设置常规数据格式1.3.3与日期型数据有关的函数•TODAY函数：返回系统当前日期的序列号。•DATE函数：将三个数字组成一个日期序列号。•YEAR函数：获取指定日期序列号的年份数字。•MONTH函数：获取指定日期序列号的月份数字。•DAY函数：获取指定日期序列号的日数字。•WEEKDAY：获取某日期为星期几的数字。1.3.4查找与修改非法日期•查找非法日期–利用“单元格格式”对话框判断–使用ISNUMBER函数判断–设置数据有效性并圈释无效数据•修改非法日期–查找与替换–使用函数–使用分列工具•【例1-4】处理非法日期数据1.4时间型数据及其处理方法1.4.1时间型数据的输入及显示格式1.4.2时间型数据的计算1.4.3与时间型数据有关的函数1.4.1时间型数据的输入及显示格式•时间型数据的输入•时间型数据的显示格式–常规显示格式–自定义显示格式1.4.2时间型数据的计算•【例1-5】计算工作时数•注意跨越午夜的情况1.4.3与时间型数据有关的函数•NOW函数：返回当前日期和时间所对应的序列号。•HOUR函数：用于获取时间值的小时数。•MINUTE函数：用于获取时间值的分钟数。•SECOND函数：用于获取时间值的秒数。1.5高效整理数据表1.5.1快速删除数据表中的所有空行1.5.2快速填充数据表中的所有空单元格1.5.3利用分列工具高效整理数据1.5.4整理数据表综合练习1.5.1快速删除数据表中的所有空行•排序法•筛选法•定位法•【例1-6】快速删除所有空行1.5.2快速填充数据表中的所有空单元格•快速填充表格中的所有空单元格•将空单元格填充为上一个单元格数据•【例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销售数据的透视分析2.1.1制作数据透视表的基本方法2.1.2数据透视表的编辑与更新方法2.1.3运用数据透视表多角度分析财务数据2.1.4制作与使用数据透视图【例2-1】销售数据透视分析2.1.1制作数据透视表的基本方法•【数据】/【数据透视表和数据透视图】•合理设置数据透视表的布局•相关概念–页字段–行字段–列字段–数据字段2.1.2数据透视表的编辑与更新方法•修改字段名称•设置字段的数字格式•取消数据透视表行总计或列总计•取消字段的分类汇总•合并数据标志•设置错误值或空值的显示方式•更新数据透视表的数据2.1.3运用数据透视表多角度分析销售数据–修改数据透视表布局–显示明细数据2.1.4制作与使用数据透视图–在数据清单基础上制作数据透视图–在数据透视表基础上制作数据透视图2.2数据透视表的高级应用2.2.1通过组合字段创建多维数据汇总表2.2.2通过设置字段显示方式创建各种比较分析表2.2.3通过自定义设置灵活编制销售数据汇总表2.2.1通过组合字段创建多维销售数据汇总表•按不同字段分组查看数据–按日期分组–按地区分组–按商品类别分组2.2.2通过设置字段显示方式创建各种比较分析表•差异或差异百分比•占同行或同列百分比•占总合百分比•累计值显示•纵向差异比较2.2.3通过自定义设置灵活编制数据汇总表•添加自定义计算字段•添加自定义计算项2.3使用数据透视表汇总多重区域数据•【例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营运资金管理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】–数据/合并计算–被合并计算的数据区域结构应相同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】•数据/排序–一个关键字的情况–多个关键字的情况3.2.3应收账款的筛选分析【例3-5】•数据/筛选–自动筛选–高级筛选•一个条件的情况•多个条件的情况3.2.4应收账款分类汇总分析【例3-6】•建立分类汇总–数据/分类汇总•查看分类汇总结果•复制分类汇总结果–选择可见单元格•删除分类汇总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】•为整列定义名称•删除网格线•运用条件格式设置边框和字体颜色3.3.2存货查询【例3-9】•VLOOKUP函数——在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。•COLUMN函数——返回给定引用的列标。3.3.3存货的ABC分类模型•存货的分类–A类存货——关键的少数–C类存货——不重要的多数–B类存货——居中的类别•常见的分类标准：存货类别品种%金额%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为一定时期存货的需求量；A为一次订货费；P为存货单价；K为存货的存储费率；PK即为单位存储费用。KPQAQDT2PKDAQ2***QDNDAPKT2*3.3.4经济订货批量模型【例3-11】计算经济订货批量•SQRT函数——开平方•建立和使用自定义函数–工具/宏/VisualBasic编辑器–插入/模块–插入/过程3.4应付账款管理3.4.1应付账款到期日的计算3.4.2应付账款到期日提前提醒3.4.1应付账款到期日的计算•EDATE函数——计算出所指定月数之前或之后的日期•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.1制作限额领料单【例4-1】制作限额领料单•数据有效性的设置•隐藏行和列•保护单元格和保护工作表•为工作簿设置密码3.1.2快速编制原材料费用预算【例3-2】计算原材料成本【例3-3】编制材料需求量预算表–MMULT函数——返回两数组的矩阵乘积4.1.2快速编制原材料费用预算【例4-2】计算原材料成本【例4-3】编制材料需求量预算表–MMULT函数——返回两数组的矩阵乘积4.2工资费用管理4.2.1工资和奖金的计算4.2.2快速制作工资条4.2.1工资和奖金的计算【例4-4】计算基本工资和工龄工资–根据职称确定基本工资–根据工龄计算工龄工资【例4-5】计算销售人员年度工资和奖金–HLOOKUP函数——在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。【例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】•组合图表•数据系列格式设置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函数——返回一条线性回归拟合线的值。•【例5-2】成本预测更多的预测函数•其他线性预测函数–LINEST函数——使用最小二乘法对已知数据进行最佳直线拟合，并