财务管理数据处理技术-Excel Vba 在财务分析和决策中的应用第1章 会计数据处理概论内容提要：1.1会计数据处理技术概述1.2会计数据处理的方法1.1 会计数据处理技术概述会计数据处理技术概述1.1.1 1.1.1 会计数据处理会计数据处理 会计数据处理(Accounting Data Processing)是指对会计数据进行加工处理，生成管理所需会计信息的过程。一般要经过采集、录入、传输、加工、存储、输出等环节。1.1会计数据处理技术概述会计数据处理技术概述1.1.1 1.1.1 会计数据处理会计数据处理1.1.会计数据处理的方式会计数据处理的方式(1) 手工会计数据处理(2) 机械化会计数据处理(3) 计算机会计数据处理1.1 会计数据处理技术概述会计数据处理技术概述1.1.1 1.1.1 会计数据处理会计数据处理2.2.会计数据处理的过程会计数据处理的过程数据收集数据校验数据加工数据传输数据存储1.1 会计数据处理技术概述会计数据处理技术概述1.1.2 1.1.2 会计数据处理的特点会计数据处理的特点1.数据来源广、种类多、综合性强、连续性强、数据量大、类型较为复杂、结构和处理逻辑复杂。2.要求会计数据方便审计。3.会计数据输入时需要进行严格的审核，要求客观、真实、公允性、全面完整和一致性。4.数据处理过程必须符合会计制度和相关法规要求，环节比较多，处理步骤定期重复。5.日常工作操作简单，但准确性要求高。6.证、账、表种类多，数量大，要作为会计档案长期保存，并方便信息输出、查找。7.会计数据处理要求具有安全性、可靠性、及时性、保密性1.1 会计数据处理技术概述会计数据处理技术概述1.1.3 1.1.3 手工会计数据处理手工会计数据处理手工处理主要由人工进行会计数据的收集、分类、汇总与计算。手工处理的优点在于具有良好的适应性和可靠性。手工处理的缺点表现为两低一高，即低速度、低效率及高差错率。手工会计数据处理的技术特性:复杂性、有序性、规范性、分散性、单一性。1.1 会计数据处理技术概述会计数据处理技术概述1.1.4 1.1.4 计算机会计数据处理计算机会计数据处理计算机处理就是以计算机为工具进行会计数据处理计算机会计数据处理的技术特性:集中性、自动性、多样性、开放性、全面性1.1 会计数据处理技术概述会计数据处理技术概述1.1.5 1.1.5 手工会计数据处理与计算机会计数据处理的比较手工会计数据处理与计算机会计数据处理的比较相同之处：目标一致、遵循基本的会计理论与会计方法、遵守会计法规及财经制度、保存会计档案。不同之处：信息载体不同、簿记形式不同、账务处理程序不同、系统的设计方法不同、会计数据的收集传输形式趋于自动化、会计的管理职能进一步强化、会计工作组织和机构发生变化、内部控制方式不同、对人员素质的要求不同。1.2 1.2 会计数据处理的方法会计数据处理的方法1.2.1 1.2.1 会计数据库的建立会计数据库的建立1. 1. 数据获取数据获取/ /管理层管理层数据库的定义与修改、数据的获取、数据库系统的管理。2. 2. 数据存储层数据存储层数据库的主体，所存储的数据包括三部分，其一是从外部数据源抽取、转换、存放；其二是数据库的元数据，其三是针对不同的数据挖掘和分析主题而生成的数据集。3. 3. 数据分析数据分析/ /应用层应用层面向系统的一般用户，满足用户的查询需要、分析的需要1.2 1.2 会计数据处理的方法会计数据处理的方法1.2.2 1.2.2 多种需求下的会计数据处理多种需求下的会计数据处理1.2 1.2 会计数据处理的方法会计数据处理的方法1.2.3 1.2.3 会计数据分析与利用会计数据分析与利用1.1.数据挖掘数据挖掘数据挖掘(Data Mining)就是从大量的、不完全的、有噪声的、模糊的、随机的数据中，提取隐含在其中的、人们事先不知道的、但又是潜在有用的信息和知识的过程。2.2.数据挖掘的范围数据挖掘的范围趋势预测探测以前未发现的模式1.2 1.2 会计数据处理的方法会计数据处理的方法1.2.3 1.2.3 会计数据分析与利用会计数据分析与利用3.3.数据挖掘过程数据挖掘过程1.2 1.2 会计数据处理的方法会计数据处理的方法1.2.3 1.2.3 会计数据分析与利用会计数据分析与利用4.4.数据挖掘方法数据挖掘方法(1) 关联分析(2) 序列模式分析(3) 分类分析(4) 聚类分析5.5.智能信息分析智能信息分析第2章 ExcelExcel基本应用基本应用内容提要：内容提要：2.1 Excel 20072.1 Excel 2007的基本操作的基本操作2.2 Excel 20072.2 Excel 2007电子表格的组成与结构电子表格的组成与结构2.3 2.3 工作簿管理工作簿管理2.4 2.4 工作表管理工作表管理2.5 2.5 单元格区域管理单元格区域管理2.6 2.6 数据处理数据处理2.7 2.7 函数的使用函数的使用2.1 Excel 20072.1 Excel 2007的基本操作的基本操作2.1.1 Excel 20072.1.1 Excel 2007的启动的启动方式一：常规启动Excel 2007的方法，实际上就是运行一个应用程序的操作。方式二：双击建立在Windows桌面上的Microsoft Excel 2007的快捷方式图标。2.1 Excel 20072.1 Excel 2007的基本操作的基本操作2.1.2 Excel 20072.1.2 Excel 2007的退出的退出方式一：单击窗口左上角的Office按钮，从弹出的下拉菜单中选择右下角的“退出Excel”按钮。方式二：单击窗口右上角的关闭窗口按钮。2.1 Excel 20072.1 Excel 2007的基本操作的基本操作2.1.3 Excel 20072.1.3 Excel 2007工作窗口的组成和功能工作窗口的组成和功能1.Office按钮2.快速访问工具栏3.标题栏4.功能区5.名称框6.编辑栏7.工作表格区2.2 Excel 20072.2 Excel 2007电子表格电子表格的组成与结构的组成与结构1.单元格2.单元格区域3.工作表4.工作簿2.3 2.3 工作簿管理工作簿管理2.3.1 2.3.1 创建工作簿创建工作簿2.3.2 2.3.2 保存工作簿保存工作簿2.3.3 2.3.3 打开工作簿打开工作簿2.3.4 2.3.4 关闭工作簿关闭工作簿2.4 2.4 工作表管理工作表管理2.4.1 2.4.1 激活工作表激活工作表2.4.2 2.4.2 插入工作表插入工作表2.4.3 2.4.3 重命名工作表重命名工作表 2.4.4 2.4.4 删除工作表删除工作表 2.4.5 2.4.5 隐藏工作表隐藏工作表2.5 2.5 单元格区域管理单元格区域管理2.5.1 2.5.1 单元格区域的选定单元格区域的选定2.5.2 2.5.2 单元格区域的引用单元格区域的引用2.5.3 2.5.3 单元格区域的移动单元格区域的移动2.5.4 2.5.4 单元格区域的复制单元格区域的复制2.6 2.6 数据处理数据处理常规常规数值数值货币货币会计专用会计专用日期日期时间时间百分比百分比2.6 2.6 数据处理数据处理分数分数科学记数科学记数文本文本特殊特殊批注批注自定义自定义2.6 2.6 数据处理数据处理2.6.1 2.6.1 手动输入数据手动输入数据2.6.2 2.6.2 自动输入数据自动输入数据2.6.3 2.6.3 查找和替换数据查找和替换数据2.6.4 2.6.4 设置数据格式设置数据格式2.6.5 2.6.5 插入符号、分数和特殊字符插入符号、分数和特殊字符2.6.6 2.6.6 数据排列数据排列2.6.7 2.6.7 数据筛选数据筛选2.6.8 2.6.8 数据有效性数据有效性2.7 2.7 函数的使用函数的使用2.7.1 2.7.1 认识函数的参数认识函数的参数1.1.常量常量 2.2.逻辑值逻辑值 3.3.数组数组 4.4.错误值错误值 5.5.单元格引用单元格引用 6.6.嵌套函数嵌套函数 7.7.名称和标志名称和标志 2.7 2.7 函数的使用函数的使用2.7.2 Excel2.7.2 Excel公式中的符号公式中的符号1.1.冒号冒号(:):(:):区域操作符区域操作符2.2.空格空格( ):( ):相交区域操作符相交区域操作符3.3.逗号逗号(,):(,):合并区域操作符合并区域操作符2.7 2.7 函数的使用函数的使用2.7.3 2.7.3 函数的种类函数的种类(1)数据库函数(2)日期与时间函数(3)工程函数(4)财务函数(5)信息函数(6)逻辑函数2.7 2.7 函数的使用函数的使用2.7.3 2.7.3 函数的种类函数的种类(7)查询和引用函数(8)数学和三角函数(9)统计函数(10)文本函数(11)用户自定义函数第第 3 3 章章 外部数据导入外部数据导入内容提要：内容提要：3.1 3.1 数据连接数据连接 3.2 3.2 文本数据导入文本数据导入3.3 3.3 数据库数据导入数据库数据导入 3.4 XML3.4 XML数据导入数据导入 3.1 3.1 数据连接数据连接 3.1.1 3.1.1 显示数据连接显示数据连接3.1.2 3.1.2 创建数据连接创建数据连接3.1.3 3.1.3 使用数据连接使用数据连接3.1.4 3.1.4 管理数据连接管理数据连接3.2 3.2 文本数据导入文本数据导入3.2.1 3.2.1 金蝶金蝶K/3K/3数据导出数据导出3.2.2 Excel3.2.2 Excel文本型数据源导入文本型数据源导入3.3 3.3 数据库数据导入数据库数据导入3.3 3.3 数据库数据导入数据库数据导入3.3.1 3.3.1 用友用友U8.90U8.90数据导出数据导出3.3.2 MicroSoft Query3.3.2 MicroSoft Query方式下数据库数据导入方式下数据库数据导入3.4 XML3.4 XML数据导入数据导入 XML是可扩展标记语言(Extensible Markup Language)的缩写，是Internet环境中跨平台的，依赖于内容的技术，是当前处理结构化文档信息的有力工具，XML与HTML一样，都是SGML(Standard Generalized Markup Language，标准通用标记语言)。XML 遵循业界标准原则，并且可由各种数据库和应用程序进行处理，通过使用XML，应用程序设计人员可以创建他们自己的自定义标记、数据结构和架构。3.4 XML3.4 XML数据导入数据导入 3.4.1 XML3.4.1 XML的基本特征的基本特征1.XML与Access，Oracle和SQL Server等数据库不同。2.XML与HTML的设计区别。3.XML的简单使其易于在任何应用程序中读写数据。4.XML重新定义了SGML的一些内部值和参数。5.XML是个公共格式。 3.4 XML3.4 XML数据导入数据导入 3.4.2 XML3.4.2 XML的重要作用的重要作用1.XML可以将HTML与数据分离。2.XML用于交换数据。3.XML和电子商务。4.XML可被用来共享数据。5.XML可用于存储数据。6.XML可被用来创建新的语言3.4 XML3.4 XML数据导入数据导入 3.4.3 Excel3.4.3 Excel导入的导入的XMLXML数据类型数据类型Excel 主要处理两种类型的 XML 文件：(1)XML 数据文件(*.xml)：此类型文件包含自定义标记和结构化数据。 (2)架构文件 (*.xsd)：此类型文件包含强制实施规则(例如数据类型和验证)的架构标记。第第 4 4 章章 财务管理模型内容提要：内容提要：4.1 4.1 财务分析模型财务分析模型4.2 4.2 固定资产投资模型固定资产投资模型4.3 4.3 存货管理模型存货管理模型4.4 4.4 销售预测模型销售预测模型4.1 4.1 财务分析模型财务分析模型4.1.1 4.1.1 财务分析概述财务分析概述1.1.数据来源数据来源(1)会计核算数据(2)辅助数据2.2.从数据库中获取数据从数据库中获取数据(1)数据连接方式(2)QUERY方式(查询方式)(3)VBA方式4.1 4.1 财务分析模型财务分析模型4.1.1 4.1.1 财务分析概述财务分析概述财务分析即财务报表分析，是在对企业财务报表的数据进行汇总、分析、研究的基础上，评价企业过去和现在的财务状况、经营成果以及预测今后发展趋势的一种重要方法，其主要的方法有比率分析法、趋势分析法和综合分析法。4.1 4.1 财务分析模型财务分析模型4.1.1 4.1.1 财务分析概述财务分析概述1.1.数据来源数据来源(1)会计核算数据(2)辅助数据2.2.从数据库中获取数据从数据库中获取数据(1)数据连接方式(2)QUERY方式(查询方式)(3)VBA方式4.1 4.1 财务分析模型财务分析模型4.1.2 4.1.2 常见财务分析指标常见财务分析指标1.1.变现能力指标变现能力指标(1)流动比率(2)速动比率(3)现金比率4.1 4.1 财务分析模型财务分析模型4.1.2 4.1.2 常见财务分析指标常见财务分析指标2.2.资产管理指标资产管理指标(1)存货周转率(2)应收账款周转率(3)流动资产周转率(4)总资产周转率4.1 4.1 财务分析模型财务分析模型4.1.2 4.1.2 常见财务分析指标常见财务分析指标3.3.负债管理指标负债管理指标(1)资产负债率(2)产权比率(3)有形净值债务比率(4)利息周转倍数4.1 4.1 财务分析模型财务分析模型4.1.2 4.1.2 常见财务分析指标常见财务分析指标4.4.赢利能力指标赢利能力指标(1)销售净利率(2)销售毛利率(3)总资产报酬率(4)净资产收益率4.2 4.2 固定资产投资模型固定资产投资模型4.2.14.2.1固定资产投资概述固定资产投资概述4.2.24.2.2固定资产投资管理程序固定资产投资管理程序数据收集数据校验数据加工数据传输数据存储4.2 4.2 固定资产投资模型固定资产投资模型4.2.3 4.2.3 固定资产投资的现金流量固定资产投资的现金流量1.1.初始现金流量初始现金流量初始现金流量是指固定资产从购入(或更新改造)到投入使用前的时间段内所发生的现金流入和流出的数量，包括四个部分内容：(1)固定资产上的投资(2) 流动资产上的投资(3) 其他投资费用(4) 原有固定资产的变价收入4.2 4.2 固定资产投资模型固定资产投资模型4.2.3 4.2.3 固定资产投资的现金流量固定资产投资的现金流量2.2.营业现金流量营业现金流量营业现金流量是指固定资产投入使用后，在其生命周期内生产经营所带来的现金流入和流出的数量，这种现金流量一般按年度进行计算。这里现金流入一般是指营业现金收入，现金流出是指营业现金支出和交纳的税金。4.2 4.2 固定资产投资模型固定资产投资模型4.2.3 4.2.3 固定资产投资的现金流量固定资产投资的现金流量2.2.营业现金流量营业现金流量如果投资项目的年销售收入等于营业现金收入，付现成本(指不包括折旧的成本)等于营业现金支出，那么，年营业现金净流量可推导为:(1) 税前利润=营业现金收入付现成本折旧(2) 所得税=税前利润所得税税率4.2 4.2 固定资产投资模型固定资产投资模型4.2.3 4.2.3 固定资产投资的现金流量固定资产投资的现金流量2.2.营业现金流量营业现金流量(3) 净利润=营业现金收入付现成本折旧所得税该公式中只有折旧与现金流无关，所以可推导现金净流量公式如下(4) 现金净流量=净利润+折旧 (5) 现金净流量=营业现金收入付现成本所得税=(营业现金收入付现成本)(1所得税率)+折旧所得税率4.2 4.2 固定资产投资模型固定资产投资模型4.2.3 4.2.3 固定资产投资的现金流量固定资产投资的现金流量3.3.终结现金流量终结现金流量终结现金流量是指固定资产报废、出售时所发生的现金流入和流出的数量，包括三个部分的内容：(1) 固定资产的残值收入或变价收入。(2) 原来垫支在各种流动资产上的资金的收回。(3) 停止使用的土地的变价收入等。4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数1.1.净现值函数净现值函数语法：NPV(RATE，VALUE1，VALUE2，)。功能：在已知未来连续期间的现金净流量和贴现率的情况下，返回现金净流量现值之和。参数说明：RATE；期望的收益率VALUE1,VALUE2,VALUEN:1-N期期末的现金净流。4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数2.2.内含报酬率函数内含报酬率函数语法：IRR(VALUE1，VALUE2，GUESS)功能：返回连续期间的现金净流量的现值之和为零的期望收益率。参数说明：GUESS：函数 IRR 计算结果的估计值。4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数3.3.年金现值函数年金现值函数语法：PV(RATE，NPER，PMT，FV，TYPE)。功能：在已知利率、总期数、年金的条件下，返回年金的现值。参数说明：RATE：期望的收益率；NPER：总期数；PMT：年金；FV：终值TYPE:数字0或者1，代表年金的类型。0代表是普通年金，即期末支付，也是默认值；1代表先付年金，即期初支付。4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数4.4.年金终值函数年金终值函数语法：FV(RATE，NPER，PMT，PV，TYPE)。功能：在已知利率、总期数、年金的条件下，返回年金的终值。参数说明：PV:现值4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数5.5.年金函数年金函数语法：PMT(RATE，NPER，PV，FV，TYPE)。功能：在已知利率、总期数、年金现值或年金终值的条件下，返回年金。4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数6.6.年金本金函数年金本金函数语法：PPMT(RATE，PER，NPER，PV，FV，TYPE)功能：在已知利率、总期数、年金现值或年金终值的条件下，返回指定期的年金中的本金值。参数说明：PER：计算的某个指定期间。4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数7.7.年金利息函数年金利息函数语法：IPMT(RATE，PER，NPER，PV，FV，TYPE)。功能：在已知利率、总期数、年金现值或年金终值的条件下，返回指定期的年金中的利息值。4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数8.8.直线折旧函数直线折旧函数语法：SLN(COST，SALVAGE，LIFE)。功能：返回固定资产在一个期间中的线性折旧值。参数说明：COST：资产原值。SALVAGE：预计的净残值。LIFE：折旧期限。4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数9.9.年数总和函数年数总和函数语法：SYD(COST，SALVAGE，LIFE，PER)。功能：返回固定资产按年限总和折旧法计算的指定期间的折旧值。参数说明：PER：计算折旧的指定期间。(其余参数见直线折旧函数)4.2 4.2 固定资产投资模型固定资产投资模型4.2.4 4.2.4 固定资产投资涉及的函固定资产投资涉及的函数数10.10.双倍余额递减函数双倍余额递减函数语法：DDB(COST，SALVAGE，LIFE，PER，FACTOR)。功能：使用双倍余额递减法或其他指定方法，计算固定资产在指定期间内的折旧值。参数说明：FACTOR：为递减的速率。可以为3，4等数字，也可以省略，默认为 2即双倍余额递减法4.3 4.3 存货管理模型存货管理模型4.3.14.3.1存货管理概述存货管理概述4.3.2 4.3.2 经济订货批量法经济订货批量法1.1.理论方法理论方法经济订货批量法：订货批量是指企业每次订购存货的数量。订货批量的增加，一方面可以减少订货的次数，从而减少订货成本，另一方面伴随着平均存货的增加，仓储成本也相应增加；反之，订货批量的减少，将使仓储成本减少而订货成本增加。4.3 4.3 存货管理模型存货管理模型4.3.14.3.1存货管理概述存货管理概述4.3.2 4.3.2 经济订货批量法经济订货批量法1.1.理论方法理论方法经济订货批量法是指在保证企业正常生产经营的情况下，存货总成本最低时的订货批量，由经济订货批量确定的存货成本将是最低成本。经济订货批量理论建立在以下基本假设基础上：(1) 企业存货年需要量是可知的，并且日消耗是基本均衡的。(2) 货物到达的时间是固定的，并且每天送货数量均衡。(3) 不考虑缺货的情况(经济批量法的前提是保证正常的生产经营)4.3 4.3 存货管理模型存货管理模型4.3.2 4.3.2 经济订货批量法经济订货批量法2.2.数学推导数学推导TZ = TD + TG + TCTD =DQ*V+订货固定成本 TG = Q*P*(1 -I)+采购固定成本 TC =2)*(XSDD-*C+仓储固定成本 4.3 4.3 存货管理模型存货管理模型4.3.2 4.3.2 经济订货批量法经济订货批量法2.2.数学推导数学推导经济订货批量D*，是在TZ 最低时的订货批量D*=XSSCQV-*2TZ =DQ*V+2)*(XSDD-*C +Q*P*( 1-I)+G 4.3 4.3 存货管理模型存货管理模型4.3.2 4.3.2 经济订货批量法经济订货批量法2.2.数学推导数学推导最低总变动成本，固定成本不随订货批量变化而变化：最佳订货次数：TZ* =)1 (*2SXCQV-N*=N*=*DQ 4.3 4.3 存货管理模型存货管理模型4.3.2 4.3.2 经济订货批量法经济订货批量法2.2.数学推导数学推导再订货点，订货到货物到达需要一定的时间，这段时间的消耗量就是再订货点：R*=O*XN*=4.3 4.3 存货管理模型存货管理模型4.3.3 4.3.3 工具介绍工具介绍规划求解规划求解1.1.加载规划求解加载规划求解2.2.启动规划求解启动规划求解3.3.规划求解参数说明规划求解参数说明4.4 4.4 销售预测模型销售预测模型4.4.14.4.1销售预测概述销售预测概述销售预测是指在未来一段时间内，对全部产品或特定产品的销售数量与销售金额的一个估计。销售预测是在充分考虑各种影响因素的基础上，结合本企业的销售情况，通过科学的分析方法提出切实可行的销售目标。1.1.销售预测的作用销售预测的作用 2.2.销售预测的程序销售预测的程序3.3.销售预测的常用方法销售预测的常用方法 4.4 4.4 销售预测模型销售预测模型4.4.14.4.1销售预测概述销售预测概述1.1.销售预测的作用销售预测的作用 (1) 通过销售预测，可以调动销售人员的积极性，提高产品的销售数量，加快销售任务的完成进度。 (2) 企业可以以销定产，根据销售预测资料，安排生产，避免产品、库存的积压。4.4 4.4 销售预测模型销售预测模型4.4.14.4.1销售预测概述销售预测概述2.2.销售预测的程序销售预测的程序(1) 确定预测目标 (2) 收集和分析资料4.4 4.4 销售预测模型销售预测模型4.4.14.4.1销售预测概述销售预测概述3.3.销售预测的常用方法销售预测的常用方法 (1) 德尔菲法。德尔菲法是指以不记名方式根据专家意见作出销售预测的方法。德尔菲法通常包括召开一组专家参加的会议，第一阶段得到的结果总结出来可作为第二阶段预测的基础. 通过组中所有专家的判断、观察和期望来进行评价，最后得到共享具有更少偏差的预测结果。4.4 4.4 销售预测模型销售预测模型4.4.14.4.1销售预测概述销售预测概述3.3.销售预测的常用方法销售预测的常用方法 (2) 时间序列分析法。时间序列分析法是利用变量与时间存在的相关关系，通过对以前数据的分析来预测将来的数据。在分析销售收入时，将销售收入按照年或月的次序排列下来，以观察其变化趋势。时间序列分析法现已成为销售预测中具有代表性的方法。4.4 4.4 销售预测模型销售预测模型4.4.14.4.1销售预测概述销售预测概述3.3.销售预测的常用方法销售预测的常用方法 (3) 回归分析法。各种事物彼此之间都存在直接或间接的因果关系。 同样的，销售量亦会随着某种变量的变化而变化。当销售与时间之外的其他事物存在相关性时，就可运用回归和相关分析法进行销售预测。4.4 4.4 销售预测模型销售预测模型4.4.2 4.4.2 工具与函数工具与函数1.1.数据透视表数据透视表数据透视表是一种对大量数据汇总和建立交叉分析的数据分析技术和工具。2.LINEST2.LINEST函数函数( (回归分析函数回归分析函数) )线性回归方程分为直线回归方程Y=A*X+B和曲线回归方程Y=A*X+B*X2+C。Excel可以利用以往历史数据和LINEST函数测算方程的各个参数从而确定方程式。第第5 5章章 Excel VBA Excel VBA基础基础内容提要：内容提要：5.1 VBA5.1 VBA基础知识基础知识5.2VBA5.2VBA集成开发环境集成开发环境(IDE)(IDE)5.3VBA5.3VBA对象、属性、方法和事件对象、属性、方法和事件5.45.4模块、函数和过程模块、函数和过程5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.65.6窗体及控件窗体及控件5.75.7Excel VBAExcel VBA操作操作AccessAccess数据库数据库5.1 VBA5.1 VBA基础知识基础知识 5.1.1 什么是VBA Excel不仅仅是一个被广泛应用的电子表格软件，它除了具有一般电子表格软件的数据处理、统计分析、图表功能外，所具备的最大特点是集成了VBA 环境。 从 Microsoft Office 97 版本开始，微软为所有的 Office 系列软件加入了统一的应用程序扩展语言Visual Basic For Application(VBA)，并提供了VBA 的 IDE 环境(Integrated Development Environment，即应用程序集成开发环境)。5.1 VBA5.1 VBA基础知识基础知识 5.1.2 VBA与VB(1)Visual Basic代码最终被编译为可执行程序；而 VBA 是使已有应用程序自动化，始终为解释执行。(2)Visual Basic 具有自己的开发环境，而VBA 必须“寄生于”已有的应用程序。(3)Visual Basic 开发出的应用程序编译后可脱离VB环境执行，但执行VBA 应用程序访问被“寄生的”应用程序.(4)使用 VBA 开发，可以使用相应“寄生”应用程序的已有功能，大大简化开发.5.2VBA5.2VBA集成开发环境集成开发环境(IDE)(IDE)5.2.1 打开VBA IDEVBA 代码和Excel 文件是保存在一起的，可以通过单击打开VBA 的IDE 环境，如图 5-1所示，进行程序设计和代码编写。5.2.2 VBA IDE的组成默认情况下，VBA IDE 环境上方为菜单和工具条，左侧上方窗口为工程资源管理器窗口，资源管理器窗口之下为属性窗口，右侧最大的窗口为代码窗口。5.3VBA5.3VBA对象、属性、方法和事件对象、属性、方法和事件 5.3.1 什么是对象、属性、方法Ball.Kick()这种命令方式同样包含了名词Ball，只是它的位置放在了前面，在VBA中它就是“对象”。动词“Kick()”，它通过小数点符号“.”连接在名词之后，在VBA中它就是“方法”。所有VBA中的方法都有一对括号“( )”跟随。括号内可以指定副词，也可以不指定. 5.3VBA5.3VBA对象、属性、方法和事件对象、属性、方法和事件 5.3.2 VBA5.3.2 VBA中的事件中的事件事件是 Excel 根据工作簿所发生的特定动作来执行代码的方式。通过在对应的事件下编写代码来响应事件，当触发某个事件时，就会执行这些代码。窗体、窗体上的控件都定义了很多事件，用户窗体编程时，编写一个事件的响应代码有两种方式，对于默认事件，双击这个控件，就会自动打开代码编辑器，新建或定位到这个事件。或者，可以在代码编辑器上方左侧的“对象框”选择对象，然后在其右侧的“过程/事件框”选择响应的事件，即可定位或创建这个事件.5.45.4模块、函数和过程模块、函数和过程5.4.1 5.4.1 模块模块模块是保存自定义的过程、函数的地方，也是保存录制的宏的场所。有两种基本类型的模块：类模块和标准模块.5.45.4模块、函数和过程模块、函数和过程5.4.2 5.4.2 过程过程过程是最基本的运行单位。一个完整的过程一般格式如下：Sub Test() End Sub在以上程序中，Sub 代表过程种类，表示运行指定的操作，但不返回运行结果；Test表示过程名称，最后以End Sub 结束。5.45.4模块、函数和过程模块、函数和过程5.4.3 5.4.3 函数函数函数要返回一个值，其类型通过As Type 来定义，要从函数返回一个值，只需将该值赋给函数名。在过程的任意位置都可以出现这种赋值。如果没有对 name 赋值，则过程将返回一个默认值：数值函数返回0，字符串函数返回一个零长度字符串 ()，Variant 函数则返回Empty。如果在返回对象引用的Function 过程中没有将对象引用赋给name(通过 Set)，则函数返回Nothing。5.45.4模块、函数和过程模块、函数和过程5.4.4 5.4.4 调用过程和函数调用过程和函数从其他过程调用一个过程(Sub)时，必须键入过程名称以及任何需要的参数值。Call语句可有可无，如果使用它，则参数必须以括号括起来。可以使用 Sub 过程去组织其他的过程。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.1 Visual Basic5.5.1 Visual Basic的命名规则的命名规则(1) 第一个字符必须使用英文字母。(2) 不能在名称中使用空格、句点(.)、惊叹号(!)、或 、&、$，# 等字符。(3) 名称的长度不可以超过 255 个字符。(4) 通常，使用的名称不能与 Visual Basic 本身的 Function 过程、语句以及方法的名称相同. (5) 不能在范围的相同层次中使用重复的名称。 (6) Visual Basic不区分大小写，但它会在名称被声明的语句处保留大写。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.2 5.5.2 变量及其作用范围变量及其作用范围1.1.变量的数据类型变量的数据类型变量的数据类型控制变量允许保存何种类型的数据。在选用数据类型时，一个目标是选择需要存储空间尽量小的数据类型来保存所需要的数据。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.2 5.5.2 变量及其作用范围变量及其作用范围2.2.用用DimDim语句声明变量语句声明变量创建变量可以使用Dim语句，声明变量的 Dim语句的语法如下：Dim 变量名 AS 数据类型这条语法中的变量名代表将要创建的变量名。对变量的命名规则和对过程的命名规则相同。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.2 5.5.2 变量及其作用范围变量及其作用范围3.3.使用数组使用数组数组是具有相同数据类型并共同享有一个名字的一组变量的集合，数组中的元素通过索引数字加以区分，定义数组的语法如下(其中n是数组元素的个数)：Dim array_name(n) As type注意注意：括号中的数字是9而不是10。这是因为在默认的情况下，第一个索引数字是0。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.2 5.5.2 变量及其作用范围变量及其作用范围4.4.变量赋值变量赋值声明变量后就可以给变量赋值。5.5.变量的作用范围变量的作用范围变量和常量可以在两个地方定义：过程中定义和在模块顶部一个名为“通用声明”的区域内定义。而且定义的位置就决定了作用域。还有一种等级的作用域称为公共级。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.3 5.5.3 条件语句条件语句1.If.Then.Else 1.If.Then.Else 语句语句根据条件的值，可使用 If.Then.Else 语句运行指定的语句或一个语句块。If.Then.Else 语句可根据需要嵌套多级，然而，为了可读性可能会使用 Select Case 语句而不使用多嵌套级的 If.Then.Else 语句。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.3 5.5.3 条件语句条件语句2.Case 2.Case 语句语句当一个表达式与几个不同的值相比较时，可以使用 Select Case 语句来交替使用在 If.Then.Else 语句中的 Else If。If.Then.Else 语句会计算每个 Else If 语句的不同的表达式，在控制结构的顶部，Select Case 语句只计算表达式一次。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.4 5.5.4 循环语句循环语句1.For.Next 1.For.Next 语句语句可以使用 For.Next 语句去重复一个语句块，而它的次数的数字是指定的。For 循环使用一个计数变量，当重复每个循环时它的值会增加或减少。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.4 5.5.4 循环语句循环语句2.For Each.Next 2.For Each.Next 语句语句For Each.Next 语句会重复一个语句块，而它是作用于集合中的每个对象或是数组中的每个元素。当循环执行一次则 Visual Basic 会自动设置一个变量。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.4 5.5.4 循环语句循环语句3.3.使用使用Do.Loop Do.Loop 语句语句可以使用 Do.Loop 语句去运行语句的块，而它所用的时间是不确定的。当条件为 True时，此语句会一直重复。当使用 While 关键字去检查 Do.Loop 语句中的条件时，可以有两种方法。可以在进入循环之前检查条件式，也可以在循环至少运行一次之后才检查条件式。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.4 5.5.4 循环语句循环语句4.4.使用使用With With 语句语句With objectstatementsEnd WithWith 语句可以对某个对象执行一系列的语句，而不用重复指出对象的名称。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.5 5.5.5 其他常用常见的语法元素其他常用常见的语法元素1.MsgBox 1.MsgBox 函数函数作用：在弹出对话框中给用户提示信息。语法：MsgBox( prompt , buttons , title ,helpfile, context ) 说明：在 MsgBox 函数的语法中，粗体的斜体字是此函数的命名参数。方括号所包含的参数是选择性的(在实际编写的代码中不用键入方括号)。唯一必须提供的参数(prompt)是作为提示的文本。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.5 5.5.5 其他常用常见的语法元素其他常用常见的语法元素2.2.添加注释添加注释注释可以为读代码的人解释过程或是特别的命令。Visual Basic 在运行过程时，会忽略掉注释。注释行可由省略符号()或 Rem 接着一个空格作为开始，并且可以加在过程的任何地方。为了在语句的同一行中添加注释，必须在语句后面插入一个省略符号，然后加上注释文本。按照默认规定，注释会以绿色文本显示。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.5 5.5.5 其他常用常见的语法元素其他常用常见的语法元素3.Activate 3.Activate 方法方法作用：激活对象。语法：object.Activate说明如下：在 Activate 方法语法中，斜体字 object 是一个所提供信息的占位符，5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.5 5.5.5 其他常用常见的语法元素其他常用常见的语法元素4.Set 4.Set 语句语句作用：将对象引用赋给变量或属性。语法：Set objectvar = New objectexpression | Nothing 说明如下：objectvar 必须是与所赋对象相一致的对象类型。Dim、Private、Public、ReDim以及 Static 语句都只声明了引用对象的变量。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.5 5.5.5 其他常用常见的语法元素其他常用常见的语法元素5.Beep 5.Beep 语句语句作用：通过计算机喇叭发出一个声调。语法：Beep说明如下：呼叫的频率与时间长短取决于硬件和系统软件，并随电脑不同而不同。5.5 Excel VBA5.5 Excel VBA基础语法基础语法 5.5.5 5.5.5 其他常用常见的语法元素其他常用常见的语法元素6.GoTo 6.GoTo 语句语句作用：无条件地转移到过程中指定的行。语法：GoTo line说明如下： 必要的 line 参数可以是任意的行标签或行号。GoTo 只能跳到它所在过程中的行。5.65.6窗体及控件窗体及控件 5.6.1 5.6.1 设计用户窗体设计用户窗体5.6.2 5.6.2 使用控件使用控件5.7Excel VBAExcel VBA操作操作AccessAccess数据库数据库5.7.1 5.7.1 什么是什么是ADOADO微软公司的ADO (ActiveX Data Objects) 是一个用于存取数据源的COM组件。它提供了编程语言和统一数据访问方式OLE DB的一个中间层。允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只需关心数据库的连接。访问数据库的时候，关于SQL的知识不是必要的，但是特定数据库支持的SQL命令仍可以通过ADO中的命令对象来执行。5.7Excel VBAExcel VBA操作操作AccessAccess数据库数据库5.7.2 ADO5.7.2 ADO访问访问ACCESSACCESS数据库基础知识数据库基础知识1.ADO1.ADO的基本对象的基本对象(1) Connection 对象：该对象主要是开启程序和数据库之间的连结。(2) Command 对象：该对象主要可以用来对数据库发出一些指令。(3) Recordset 对象：该对象表示的是来自基本表或命令执行结果的记录全集。5.7Excel VBAExcel VBA操作操作AccessAccess数据库数据库5.7.1 5.7.1 什么是什么是ADOADO5.7.2 ADO5.7.2 ADO访问访问ACCESSACCESS数据库基础知识数据库基础知识2.RECORDSE2.RECORDSE对象的属性和方法介绍对象的属性和方法介绍(1) OPEN方法，打开一个记录集对象。(2) CLOSE方法，关闭一个记录集对象。(3) UPDATE方法，保存记录集到目前的所有修改。(4) BOF属性，返回逻辑值，TRUE为当前操作的记录的位置在记录集第一条记录的前面。5.7Excel VBAExcel VBA操作操作AccessAccess数据库数据库5.7.1 5.7.1 什么是什么是ADOADO5.7.2 ADO5.7.2 ADO访问访问ACCESSACCESS数据库基础知识数据库基础知识2.RECORDSE2.RECORDSE对象的属性和方法介绍对象的属性和方法介绍(5) EOF属性，返回逻辑值，TRUE为当前操作的记录的位置在记录集最后一条记录的后面。(6) MOVEFIRST方法，将记录集中的第一条记录作为当前操作的记录。(7) MOVELAST方法，将记录集中的最后一条记录作为当前操作的记录。5.7Excel VBAExcel VBA操作操作AccessAccess数据库数据库5.7.1 5.7.1 什么是什么是ADOADO5.7.2 ADO5.7.2 ADO访问访问ACCESSACCESS数据库基础知识数据库基础知识2.RECORDSE2.RECORDSE对象的属性和方法介绍对象的属性和方法介绍(8) MOVENEXT方法，将记录集的下一条记录作为当前操作的记录。(9) MOVEPREVIOUS方法，将记录集的上一条记录作为当前操作的记录。(10) ADDNEW方法，创建一条新的空白记录，位于当前记录集的最后一条记录之后。5.7Excel VBAExcel VBA操作操作AccessAccess数据库数据库5.7.1 5.7.1 什么是什么是ADOADO5.7.2 ADO5.7.2 ADO访问访问ACCESSACCESS数据库基础知识数据库基础知识2.RECORDSE2.RECORDSE对象的属性和方法介绍对象的属性和方法介绍(11) DELETE方法，删除当前操作的记录，删除后，当前操作记录的下一条记录作为当前操作的记录。(12) RECORDCOUNT方法，返回一个数字，表示当前记录集的记录总数。5.7Excel VBAExcel VBA操作操作AccessAccess数据库数据库5.7.1 5.7.1 什么是什么是ADOADO5.7.2 ADO5.7.2 ADO访问访问ACCESSACCESS数据库基础知识数据库基础知识3.3.如何在如何在ExcelExcel中应用中应用ADOADO对象对象(1) MICROSOFT ADO EXT2.8 FOR DDL AND SECURITY(2) MICROSOFT ACTIVE DATA OBJECTS(MULTI-DIMENSIONAL)2.8 LIBRARY(3) MICROSOFT ACTIVE DATA OBJECTS RECORDSET 2.8 LIBRARY(4) MICROSOFT ACTIVE DATA OBJECTS 2.8 LIBRARY(5) MICROSOFT JET AND REPLICATION OBJECTS 2.6 LIBRARY5.7 ExcelVBA ExcelVBA操作操作AccessAccess数据库数据库5.7.3 5.7.3 向向AccessAccess数据库中添加数据数据库中添加数据5.7.4 5.7.4 从从AccessAccess数据库中检索数据数据库中检索数据5.7.5 5.7.5 从从AccessAccess数据库中修改数据数据库中修改数据5.7.6 5.7.6 从从AccessAccess数据库中删除数据数据库中删除数据第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型内容提要：内容提要：6.16.1时间价值计算模型时间价值计算模型6.2 6.2 长期借款敏感性分析模型长期借款敏感性分析模型6.36.3应收账款账龄分析模型应收账款账龄分析模型6.46.4固定资产折旧处理模型固定资产折旧处理模型第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.16.1时间价值计算模型时间价值计算模型最直观体现时间价值的是货币时间价值，即目前拥有的货币比未来收到的同样金额的货币具有更大的价值，因为目前拥有的货币可以进行投资，在目前到未来这段时间里获得复利。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.16.1时间价值计算模型时间价值计算模型6.1.1 6.1.1 实验目的实验目的1.熟悉Excel VBA程序设计环境。2.了解用户窗体的创建。3.了解对象属性的设计。4.了解时间价值函数的使用。5.了解Excel VBA程序编写的基本方法。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.16.1时间价值计算模型时间价值计算模型6.1.2 6.1.2 实验内容实验内容建立时间价值计算模型，该模型在用户给定金额、期限、年利率、计息次数以及计息时间(期初或期末)等信息后，能够计算一笔资金的复利终值或现值、普通年金或先付年金的终值与现值等。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.2 6.2 长期借款敏感性分析模型长期借款敏感性分析模型敏感性分析是通过分析、预测项目主要影响因素发生变化时对项目经济评价指标(如NPV、IRR等)的影响，从中找出敏感因素，并确定其影响程度的一种分析方法。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.2 6.2 长期借款敏感性分析模型长期借款敏感性分析模型6.2.1 6.2.1 实验目的实验目的1.进一步熟悉和掌握各种对象的属性、事件和方法。2.进一步熟悉各种程序设计技巧。3.熟悉Excel VBA中图形绘制的方法。 第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.2 6.2 长期借款敏感性分析模型长期借款敏感性分析模型6.2.2 6.2.2 实验内容实验内容企业出于投资的需要，其借款金额和时间是基本不变的。在企业取得的借款为浮动利率借款的情况下，借款利率可能会随着市场环境的变化而发生变化，企业需要了解借款利率变化后企业各年的偿还本金和支付利息的变化情况。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.2 6.2 长期借款敏感性分析模型长期借款敏感性分析模型6.2.2 6.2.2 实验内容实验内容通过Excel，建立长期借款敏感性分析模型，该模型通过用户提供的借款金额、借款年限、基准利率、年付息次数、变化的利率等数据，计算分析基准利率和变化利率下的每期偿还的本金和利息，并绘制图表显示相应的信息。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.2 6.2 长期借款敏感性分析模型长期借款敏感性分析模型账龄是指负债人所欠账款的时间。账龄越长，发生坏账损失的可能性就越大。账龄分析法是指根据应收帐款的时间长短来估计坏账损失的一种方法，又称应收账款账龄分析法。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.36.3应收账款账龄分析模型应收账款账龄分析模型6.3.1 6.3.1 实验目的实验目的1.掌握Excel VBA与Excel电子表格的联系。2.掌握各种进行电子表格处理的函数的使用方法。3.进一步熟悉程序设计，尤其是循环语句的使用技巧。4.了解对电子表格进行动态数据处理的方法。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.36.3应收账款账龄分析模型应收账款账龄分析模型6.3.2 6.3.2 实验内容实验内容企业发生的应收账款时间长短不一，有的尚未超过信用期，有的则已逾期拖欠。一般来说，逾期拖欠时间越长，催收的难度越大，成为坏账的可能性也就越高。所以，企业需要定期对应收账款进行账龄分析，并编制应收账款账龄分析表，以加强对应收账款的管理。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.36.3应收账款账龄分析模型应收账款账龄分析模型6.3.2 6.3.2 实验内容实验内容通过Excel建立应收账款账龄分析模型，根据给定的应收账款金额、客户数量以及应收账款账龄区间，对这些应收账款进行分类统计分析，计算出不同客户在不同账龄区间的应收账款数额及其占应收账款总额的百分比、计算出不同账龄区间内的应收账款总额及其占应收账款总额的百分比。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.46.4固定资产折旧处理模型固定资产折旧处理模型固定资产折旧指一定时期内为弥补固定资产损耗按照规定的固定资产折旧率提取的固定资产折旧，它反映了固定资产在当期生产中的转移价值。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.46.4固定资产折旧处理模型固定资产折旧处理模型6.4.1 6.4.1 实验目的实验目的1.了解各种折旧函数的使用。2.掌握程序编写折旧计算的各种方法。3.了解用Excel VBA编写系统的方法。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.46.4固定资产折旧处理模型固定资产折旧处理模型6.4.2 6.4.2 实验内容实验内容固定资产是工业企业进行生产的三大要素之一，是企业进行生产时使用或凭借的劳动资料，加强固定资产的管理是每一个企业日常管理的重要工作。第第6 6章章 Excel VBA Excel VBA建立建立财务管理模型财务管理模型6.46.4固定资产折旧处理模型固定资产折旧处理模型6.4.2 6.4.2 实验内容实验内容本模型能够对企业的固定资产进行增、减、改的操作，并能够按期对固定资产进行折旧处理(提供多种折旧方法供用户选择)，同时能够按照用户要求，生成某一个期间的折旧数据，为会计处理提供信息。