EXCEL在财务工作中的高级应用.docx
《EXCEL在财务工作中的高级应用.docx》由会员分享，可在线阅读，更多相关《EXCEL在财务工作中的高级应用.docx（14页珍藏版）》请在冰豆网上搜索。
EXCEL在财务工作中的高级应用
EXCEL在财务工作中的高级应用
（一）
EXCEL在财务工作中的高级应用
主讲老师：
王海洪
第一节 利用EXCEL获取数据
财务数据从哪里来?
财务软件输出的结果【用友】--【输出】
经财务软件建立的账套
经其他系统生成的非EXCEL格式数据
EXCEL格式数据……
一、直接打开法（文件/打开）
EXCEL可以直接打开的文件格式数据：
二、利用“获取外部数据”功能导入数据
1．新建新的EXCEL文件
2．执行命令：
数据/获取外部数据/导入文本文件
数据/获取外部数据/新建WEB查询
三、获取外部数据库数据
1．数据库的格式EXCEL不支持。
2．财务分析时只需要使用数据库中一部分数据。
3．数据库数据量较大。
数据库
是存储在计算机内按一定结构组成的数据集合。
关系型数据库应用较广泛，关系型数据库的描述包括各个数据表的描述以及表与表之间关系的描述。
获取外部数据库数据的方法
（1）确认所需要的电子数据。
（2）数据源设置。
【控制面板】--【管理工具】--【ODBC数据源】--【Access数据库】
（3）利用MSQuery建立数据库查询。
【excel】--【数据】-【导入外部数据】-【新建数据库查询】--选择下面路径
例：
对主营业务收入的完整性进行分析
需要对主营业务收入入账所依据的销售发票的号码的连续性进行检查。
需要检查销售发票信息。
UFDATA.MDB
销售发票主表名称：
SaleBillVouch
SBVID：
ID号
cSBVcode：
发票号
cVouchType：
发票类别
dDate：
开票日期
【控制面板】--【管理工具】--【ODBC数据源】--【Access数据库】
EXCEL在财务工作中的高级应用
（二）
第二节 运用EXCEL函数与公式进行会计业务处理
一、关于函数与公式
EXCEL提供的函数其实是一些预定义的公式,它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。
函数的语法
函数名（参数1，参数2，参数3……）
公式是用运算符号将常数、函数、单元格地址等连接起来的式子，用于解决特定的问题。
EXCEL函数类型
日期与时间函数：
NOW（）工龄值，年龄值
YEAR（serial_number）
TODAY（）
WEEKDAY（serial_number,return_type）
数学与三角函数：
SUM（number1,number,…）
SUMIF（range,criteria,sum_range）
SIN（number）
查询和引用函数
LOOKUP（lookup_value,lookup_vector,result_vector）
HLOOKUP（lookup_value,table_arry,row_index_num,range_lookup）
VLOOKUP（lookup_value,table_arry,col_index_num,range_lookup）
EXCEL在财务工作中的高级应用（三）
查询和引用函数
LOOKUP（lookup_value,lookup_vector,result_vector）
HLOOKUP（lookup_value,table_arry,row_index_num,range_lookup）算奖金
VLOOKUP（lookup_value,table_arry,col_index_num,range_lookup）
统计函数
一般统计函数
AVERAGE（number1,number2,…）
数理统计函数
VAR（value1,value2,…）
财务函数
SLN（cost,salvage,life）折旧
财务函数中常见的参数有：
fv:
在所有付款发生后的投资或贷款的价值。
nper:
投资的总支付期间数。
pmt:
对于一项投资或贷款的定期支付数额。
pv:
在投资初期的投资或贷款的价值。
rate:
投资或贷款的利率或贴现率。
type:
付款期间内进行支付的间隔。
逻辑函数
IF函数
IF（logical_test,value_if_true,value_if_false）
IF（D4=H4,”资产负债平衡”,”资产负债不平衡”）
例:
个人所得税的计算
I5中输入如下公式：
=IF（AND（H5<=35000,H5>9000）,H5*0.25-1005,IF（AND（H5<=9000,H5>4500）,H5*0.2-555,IF（AND（H5<=4500,H5>1500）,H5*0.1-105,IF（AND（H5<=1500,H5>0）,H5*0.03,0））））
H5：
应纳税所得额
文本函数（大写变小写）动态变化
CONCATENATE（text1,text2,text3……）
例：
=CONCATENATE（‘A’,’ B’,’ C’）
ABC
数据库函数：
当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库函数。
信息函数：
可以确定存储在单元格中的数据类型。
工程函数：
用于工程分析。
二、应收账款核算与管理
（一）创建应收账款明细表【数据】--【数据有效性】--【设置】--允许序列，框选范围
（二）逐笔登记应收账款记录
（三）判断应收账款是否到期及计算未到期金额
表示对于一项投资或贷款的定期支付数额的财务函数参数的是pmt
（四）设置应收账款到期前后一周内自动提醒
（五）逾期应收账款计算
（六）应收账款催款函的编制
（七）应收账款账龄统计
三、固定资产核算
（一）使用折旧函数建立固定资产折旧计算模型
1．建立固定资产折旧方法比较表
2．使用折旧函数计算每年计提折旧
3．计算每年折旧后账面价值
（二）制作固定资产折旧函数动态比较图表
点击需要放置的表格—插入----图表—折线图
EXCEL在财务工作中的高级应用（四）
第三节 运用EXCEL数据透视图表进行财务工作
一、数据透视图表的概述
数据透视表的定义：
是一种对大量数据快速汇总和建立交叉列表的交互式表格。
数据透视图的定义：
表达数据透视表中内容的图形。
二、数据透视表的一般使用方法
创建数据透视图表【选中图】--【数据】--【数据透视表数据透视图】
添加、移动字段定义报表布局
设置字段显示方式右键—字段设置
对字段项目分组
用页面字段查看数据子集
查看或隐藏细节数据
在透视表中使用排序和筛选
在数据透视表中填加计算项
三、数据透视图的一般使用方法
创建、改变【数据】--【数据透视表数据透视图】
四、利用数据透视表进行固定资产分析
创建固定资产数据透视表
改变行列标签进行分析
改变数值计算类型进行分析
设置筛选字段进行分析右键
对数据进行排序进行分析
五、利用数据透视图进行工资分析
打开薪资汇总表
建立数据透视表
建立数据透视图选项布局
进行工资分析！
刷新
EXCEL在财务工作中的高级应用（五）
第四节 运用EXCEL数据分析工具库进行财务工作
一、数据分析工具库介绍【工具】--【数据分析】
【工具】--【加载宏】--勾选分析工具库
EXCEL中的数据分析工具库，里面有一些与统计有关的统计或工程宏函数，有些工具甚至提供表格或图表功能，方便会计人员在财务工作中开展数据统计分析工作。
二、数据分析的应用
EXCEL在财务工作中的高级应用（六）
二、数据分析的应用
财务预测，是指对企业未来的销量、收入、费用、利润及融资需求等财务指标所做的估计和推测。
财务预测是编制财务预算的基础。
三、产品销售预测
是指根据市场调查所得到的有关数据，通过对有关因素的分析研究，对产品在未来一定时期内的市场销售量水平及变化趋势的预计与推测。
1．使用散点图拟合趋势线预测销售量【插入】--【散点图】--点击一个散点—右键—添加趋势线—勾选显示公式，显示r平方值---在需要预测表格中输入刚才公式
2．使用LINEST函数预测销售量斜率截距
LINEST函数：
使用最小二乘法对已知数据进行最佳直线拟合。
LINEST（known_y’s,known-x’s,const,stats）
known_y’s:
是y=mx+b表达式中已知的y值集合。
known-x’s:
是y=mx+b表达式中已知的x值集合。
公式输入
=INDEX（LINEST（C2:
C13,B2:
B13）,1）*B14+INDEX（LINEST（C2:
C13,B2:
B13）,2）
CORREL函数：
返回两组数值之间的相关关系。
CORREL （array1,array2）
3．利用矩阵法预测销售额带数据标线的折线图，有周期性
4．使用回归分析法预测销售额
费用预测管理是企业实施战略管理的重要组成部分，能为企业控制生产经营活动提供依据。
四、产品费用预测
费用预测管理是企业实施战略管理的重要组成部分，能为企业控制生产经营活动提供依据。
如：
管理费用、财务费用、销售费用的预测。
1．管理费用的移动平均预测两次移动平均
2．利用一次指数平滑法预测销售费用
升级会员 