Excel在财会中高级应用技术基础.ppt

Excel在财会中的高级应用 主讲 董黎明Liming dong 15058182069 电子表格软件 Lotus1 2 3 电子表格软件的开山鼻祖 1983年由Lotus 莲花公司 出品 该公司现已被IBM收购 Cced 是国内著名字表处理软件之一 它1988年首创中文字表编辑之概念 将文字编辑 表格制作 数据运算 排版打印以及数据库报表输出等多项功能融为一体 问世十多年来 以其精湛的程序设计 方便实用的功能赢得了广大用户的喜爱 版本cced2000为止 金山WPS 是金山软件公司的一种办公软件 最初出现于1989年 在微软Windows系统出现以前 DOS系统盛行的年代 WPS曾是中国最流行的文字处理软件 现在WPS最新版为2009版 华表软件 华表表格是符合中国人使用习惯的电子表格软件 具有类似数据库一样的数据查询功能 华表提供日常办公及企业管理常用的表格模板近300个 是办公处理的好助手 华表集办公表处理与数据表处理于一身 既具有电子表灵活好用的特点 又具有类似数据库一样强大的四维数据查询功能 是财务人员 统计人员 管理者等数据分析人员的最佳选择 华表专业版及用华表做的一些应用 其中包括支票模版 定价系统等 Excel 1985年1 01版本 Office简介 在Office中各个组件有着比较明确的分工 一般说来 Word主要用来进行文本的输入 编辑 排版 打印等工作 Excel主要用来进行有繁重计算任务的预算 财务 数据汇总等工作 PowerPoint主要用来制作演示文稿和幻灯片及投影片等 Access是一个桌面数据库系统及数据库应用程序 Outlook是一个桌面信息管理的应用程序 FrontPage主要用来制作和发布因特网的Web页面 Excel概述 Excel是微软Office中的一个重要组件 是专门为商务使用所设计的应用软件 也是目前较为优秀的一种数据分析和处理工具 是构建管理信息系统 MIS 的理想软件工具之一 广泛地应用于管理 统计 财经 金融等众多领域 管理者在进行管理活动的过程中 经常会抽象出一些具有逻辑关系的数据 并将其组合为具有二维特征和逻辑关系的表格 随着计算机在企业管理活动中的应用 管理者借助各种软件工具 将各种表格移植到计算机中 使得数据的收集 处理和分析更为科学 便捷 同时更有利于管理理论和管理实践的紧密结合 企业管理水平因而日益提高 而Excel正是这样一种非常优秀的数据收集 数据处理和数据分析的软件工具 Excel2010版介绍 Excel的应用 一般应用 基本电子表格的功能使用高级应用 涉及自动处理的编程应用平台应用 商业化 系统化的应用 如excel服务器模式 勤哲公司 Excel服务器是一个面向最终用户的信息系统设计与运行平台 它利用Excel作为主要操作界面 结合了数据库技术 工作流技术和Web技术 各企事业单位的管理人员和各岗位的工作人员 只要会用Excel 就能够设计和实现符合自己需要的网络化的管理信息系统 能够把Excel文件的内容保存到数据库中 能够实现数据的自动汇总统计 能够设定工作流 使Excel表单按规定流转传递 不仅能支持局域网内的应用 还可以支持互联网上的应用 实现远程管理 还能够与企业已有的财务系统 ERP系统等进行数据集成 消灭企业中的信息孤岛 最重要的是 利用Excel服务器这样一个平台 用户不需要依赖IT专业人员就可实现自己想要的管理信息系统 并且能够随着企业业务的发展及管理进步 随时调整 改变 增加系统的功能 使得信息系统真正成为随需应变的业务支撑 课程定位 高级应用有基础 会编程 有专业 会应用 有想法 会实现 有问题 会解决 学习资源 1 网络 百度 谷歌 2 图书馆 大致分为两类 自然科学类和财经类推荐 excel高效办公 财务管理 人民邮电出版社 excelvba基础与实例应用 中国青年出版社3 教材和实验指导书4 excel的帮助文件 教学目的与要求 1 熟悉Excel的基本功能 2 理解相对地址引用和绝对地址引用的区别 3 掌握常用财务函数的使用方法 4 掌握宏的录制和修改宏的方法 5 掌握VBA编程技术 通过本章和学习 系统掌握Excel这个优秀电子表格软件的功能 理解相对地址和绝对地址引用的区别 掌握常用财务函数和使用方法 掌握VBA编程的基本步骤和语法结构 掌握宏的录制与修改宏的方法 为学习后面各章奠定扎实的基础 第1章Excel在财会中高级应用技术基础 Excel的工作簿 Excel的窗口界面 Excel概述 Excel的菜单 Excel的工具 Excel的函数 Excel的财务工具 Excel的建模步骤 Excel的VBA编程基础 第1章计算机财务管理技术基础 1 1Excel概述 Excel是微软Office中的一个重要组件 是专门为商务使用所设计的应用软件 也是目前较为优秀的一种数据分析和处理工具 是构建管理信息系统 MIS 的理想软件工具之一 管理者在进行管理活动的过程中 经常会抽象出一些具有逻辑关系的数据 并将其组合为具有二维特征和逻辑关系的表格 随着计算机在企业管理活动中的应用 管理者借助各种软件工具 将各种表格移植到计算机中 使得数据的收集 处理和分析更为科学 便捷 同时更有利于管理理论和管理实践的紧密结合 企业管理水平因而日益提高 而Excel正是这样一种非常优秀的数据收集 数据处理和数据分析的软件工具 Excel2010版介绍 1 2Excel的窗口界面 Excel是Office中的一个重要组件 完整安装Office2003之后就可以使用该软件了 在使用Excel2003之前 首先了解一下它是由哪些部分组成的 以及各个部分的具体特点及功能 首先 开机后选择 开始 程序 MicrosoftExcel 命令 启动Excel软件 Excel对数据 信息的组织是分层的 最顶层是工作簿 在工作簿中有255张工作表 每张工作表由65536行和256列组成 行和列的交叉点为单元格 目前处于活动状态的单元格为当前单元格 Excel把数据和信息按以下层次顺序进行组织 工作簿工作表行 列单元格当前单元格 Excel的启动 1 3Excel的工作簿 在打开的Excel文档窗口中有一个工作簿窗口 它被Excel暂时命名为Book1 在存储文件时 用户可改用自己定义的文件名字 再次打开它时 工作簿窗口以该文件名命名 1 3 1工作表1 从工作表队列中选择当前工作表 用鼠标单击预选定的工作表 使其成为当前工作表 如果要同时选择多张工作表 可以按住ctrl键的同时 用鼠标单击需要的工作表 如果要选择一系列连续的工作表 可以按住shift键的同时 用鼠标单击需要的最后一张工作表 2 改变工作表的个数 一个工作簿最多包含255张工作表 改变工作表的个数的方法 除了一张一张插入工作表以外 还可以通过选择 工具 选项 常规 命令 来改变 新工作簿内工作表数 Excel的工作表 Excel工作簿中的每一张工作表都由256列 column X65536行 row 组成 每一个行列交叉点的小格称单元格 cell 它是工作表的最小单元 也是Excel的工作对象 能够接受和处理数据的单元格称为当前单元格 1 3 2单元格与单元区域1 选择当前单元 选定单个单元格用鼠标单击预选定的单元格即可 使其成为当前的单元格 同时选择多个单元格的单元区域 ctrl键 鼠标单击需要的单元格 选择一系列连续的单元区域 可以按用鼠标来拖拉 要选择某一行或某一列单元区域 可以直接单击该行或该列的行标数字或列标字母 选择整张工作表的所有单元格作为单元区域 则可以单击工作表左上角的空白单元格 选择某个单元格或单元格区域 在名称框中输入单元格或单元格区域的地址 然后回车 注意 ctrl shift endorhome ctrl shift 方向键 2 单元地址 celladdress 单元地址是指一个单元格或一组单元格在工作表中的位置 只有明确单元地址 Excel才能够辨别目标单元 进行数据分析处理 两种表现形式 相对地址 relativeaddress A1 B12等 绝对地址 absoluteaddress A 1 B 12 两者的应用区别在于对单元格的引用 3 单元格的引用 在数据分析中需要在函数中引用单元格或单元区域的数据 单元引用有相对引用 绝对引用和混合引用之分 利用F4键可以快速改变单元格地址引用方式 在按下 Ctrl 的情况下 可以实现用拖动法复制公式 1 相对引用 相对引用是公式中参数的地址以单元的相对地址来表示 当因插入 拷贝等原因引起行 列地址的变化时 公式中的相对引用随公式的移动而自动修改 修改参数的原则是 原公式与原参数的位移和移动后的公式与新参数的位移保持不变 2 绝对引用 绝对引用是指公式中参数以单元的绝对地址来表示 当因插入 拷贝等原因引起行 列地址的变化 公式中的绝对地址引用不会随公式的地址变化而变化 3 混合引用 单元的混合引用是指公式中参数的行或列一方用绝对地址另一方用相对地址来表示 在插入或拷贝时 绝对地址的行或列不变 相对地址的行或列变动 1 3 3滚动条与滚动框1 用鼠标指向垂直滚动条上 下箭头 单击鼠标 则窗口向前 后滚动一行 用鼠标指向滚动条的左 右箭头 单击鼠标 则窗口向左 右滚动一列 2 利用滚动条的滚动框 加速滚动 用鼠标指向垂直滚动条中滚动框的上 下方单击 屏幕上 下滚动一屏 用鼠标指向水平滚动条的左方 右方单击 屏幕左 右滚动一屏 3 拖支滚动框 在滚动条中移动 当松开鼠标时 滚动框在滚动条中的位置 即是当前显示屏在整个表中的位置 1 4Excel的菜单 Excel中的菜单分为主菜单和快捷菜单 在Excel主菜单中 包含若干菜单项 每个菜单项被选中时 可引出一下拉式菜单 其中包含着若干个命令供选择 单击鼠标右键会弹出快捷菜单 供你选择 菜单的命令项为实体字表示可以选择 虚体项表示不可选择 表示提供所需信息后执行 带 表示选择子命令才能执行 1 5Excel的工具栏 Excel的工具栏有标准工具栏和格式化工具栏 其他工具栏需特别指定才会放入工具栏区域中 工具栏中的每个图像按钮就是一个工具 代表了一种功能 从 视图 工具栏 中可以增加工具栏或减少工具栏 1 6Excel的函数 Excel提供了大量函数 函数是一个预先写好的特殊公式 根据一个或多个参数 自变量 执行操作 并返回一个值 因变量 函数可以单独使用 也可以作为较大公式的一部分 使用函数可以简化或缩短工作表中的公式 使数据处理简单方便 1 6 1函数的基本语法Excel函数基本上由三个部分组成 即函数名称 园括号和参数 函数的语法 函数名称 参数1 参数2 参数n 如sum c2 c12 sum a1 b3 c5 d7 1 6 2参数的类型Excel函数的参数是函数的自变量 是数据处理的基础 基本参数有五种类型 数值 单元引用 文本 逻辑值 错误值 1 数值 包括正负数和小数点 用来进行数学运算 2 单元引用 即某个单元格或单元区域 如A1 B 5 C1 C5等 3 文本 用 括住的字符可以作为参数 如 单位 sales 等 4 逻辑值 逻辑值有true和false两个 也可以用表达式代替 5 错误值 由 开头 结尾的参数 如 NAME 等八个参数 函数运算常见错误及分析 1 出现此错误的原因是因为公式产生结果太长 超出了单元格的宽度 单元格容纳不下 只要适当增加单元格的宽度就可解决此问题 2 NIV 0出现此错误的原因是用户在除法运算中 将除数设为0 或者是在公式中所引用的单元格为空白单元格或是包含0值的单元格 解决的办法是修改除数 使其不为0 或是修改单元格引用 以使所引用的单元格指向不为0值的单元格 3 N A此信息表示在函数和公式中没有可用的数值可以引用 当公式中引用某单元格数据时 如该单元格暂时没有数据 就会出现该错误信息 解决的办法是仔细检查函数或公式中引用的单元格 确认已在其中正确地输入了数据 4 NAME 如果用户在操作中删除了公式中所使用的以名称表示的单元格 或者使用了不存在的名称以及拼写错误 就会显示该错误信息 解决此问题的方法首先是确认函数或公式中引用的名称确实存在 如果所需的名称事先并没有被确定 用户需要添加相应的名称 其次在输入公式过程中要保证引用名称输入的正确性 5 NULL 出错原因是在函数或公式中使用了不正确的区域运算符或者不正确的单元格引用 解决这个问题的方法是 如果要引用两个并不交叉的区域 应该使用联合运算符即逗号 如果确实是需要使用交叉运算符 用户需重新选择函数或公式中的区域引用 并保证两个区域有交叉的区域 6 NUM 当用户在需要数字参数的函数中使用了不能被Excel接受的参数或公式产生的数字太大或太小 Excel不能表示 就会显示信息 用户在计算过程中如果能够首先检查数字是否会超出相应的限定区域 并确认函数内使用的参数都是正确的 就可以避免出现此类错误 7 REF 出现该错误的原因是由于删除了在公式中引用的单元格或者是将要移动的单元格粘贴到了由其他公式引用的单元格中 解决的方法是检查函数或公式中引用的单元格是否被删除 或者启动相应的应用程序 8 VALUE 出现该错误的原因是因为在需要引用数字或逻辑值的单元格时 错误地引用了包含文本的单元格 Excel不能将文本转换为正确的数据类型 确认公式或函数所需的运算符或参数正确 并且公式引用的单元格中包含有效的数值 就会解决此问题 1 6 3函数的使用1 直接在公式中输入函数 这种方法是选择单元格 输入 号 然后按照函数的语法直接键入 如 sum 10 20 30 1 if条件函数语法格式为 if 条件 条件为真执行 条件为假执行 IF H1 60 及格 不及格 IF H1 90 优秀 IF H1 80 良好 IF H1 70 中等 IF H1 60 及格 不及格 2 VLOOKUP函数 是一个非常重要的查找与引用函数 可以迅速地从复杂的数据堆中找出所需要的信息 语法格式 VLOOKUP lookup value table array col index num range lookup 如 VLOOKUP c12 A3 f8 6 false Lookup value为需要在数组第一列中查找的数值 Lookup value可以为数值 引用或文本字符串 Table array为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 例如数据库或列表 Col index num为table array中待返回的匹配值的列序号 Range lookup为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为TRUE或省略 则返回近似匹配值 如果range value为FALSE 函数VLOOKUP将返回精确匹配值 如果找不到 则返回错误值 N A 语法意义 以c12单元格的参数为主键 查找数据表 A3 F8 第一列的信息 如果查到了c12相对应的信息 则返回 A3 F8 数据表中第6列的数据 如果没查到则返回错误值 N A 3 sumif函数 分类汇总函数 语法格式为 SUMIF range criteria sum range 如 sumif A1 A4 160000 B1 B4 语法意义 以 160000为主键在A1 A4单元格区域中查找符合条件 160000 的数据项 找到后把相关B1 B4单元格区域的数值加总 并显示出来 4 数据库函数 DGET 是一个非常重要的查找与引用函数 从数据清单数据库中提取符合给定条件且唯一存在的值 语法格式 DGET database field criteria database 构成列表或数据库的单元格区域 field 指定函数所使用的列 criteria 为一组包含给定条件的单元格区域 可以为参数criteria指定任意区域 只要它至少包含一个列标志和列标志下方用于设定条件的单元格如DGET B3 C8 2 C10 C11 语法意义 查找数据表 B3 C8 第2列符合 c10 c11 的数据 如果查到了对应的信息 则返回 B3 C8 数据表中第2列相应的数据 2 使用菜单 插入 函数 命令 这种方法是通过函数指南完成函数的输入 使用函数的注意事项 1 函数名与其后 园括号之间不能有空格 2 当有多个参数时 参数之间用 分隔 参数最多30个 3 参数部分总长度不能超过1024个字符 4 根据实际需要 参数可以是数字 文本 逻辑值 工作表中的单元格或区域地址等 也可以是各种表达式或函数 5 函数格式中的 和 都必须是半角字符 而非全角的中文字符 3 财务函数的主要类型Excel提供了许多财务函数 这些函数为财务分析提供了极大的便利 财务函数大体可分为四类 即投资计算函数 偿还率计算函数 折旧计算函数 债券及其他金融函数 1 7数据输入技巧数据输入是数据处理的基础 提高数据输入的准确性和效率是输入数据的关键 除了从键盘直接输入数据外 还要根据不同的情况 注意数据输入技巧的应用 常用的数据输入技巧主要有以下几种 1 7 1用序列填充法实现自动输入数据1 数值数据的序列填充输入ctrl键的使用2 非数值数据的序列填充输入1 7 2用记录单输入数据1 记录单输入数据的适用条件2 用记录单输入数据的方法 1 7 3利用数据有效性输入数据1 利用数据有效性输入数据的适用条件2 利用数据有效性输入数据的方法1 7 4利用宏命令输入数据1 利用宏命令输入数据的适用条件2 用宏命令输入数据的方法1 7 5导入外部数据1 导入外部数据的应用范围2 导入外部数据的方法 1 7Excel的财务管理工具 Excel是通过其工具实现数据的采集 加工处理和信息输出的 Excel常用的工具主要有单变量求解 方案求解 规划求解 数据透视表和宏 1 7 1单变量求解所谓的 单变量求解 即改变某数学模型的某一变量时 自动计算出与该变量相关的另一相关结果 单变量求解工具主要用于解决由由果求因的问题 在Excel表格中如果指定一目标单元格的数值 只希望调整另一原因单元格的内容就可以使用单变量求解工具 如果已知公式预期的结果 但是不知得到这个结果所需的某一输入值 就可以使用 单变量求解 1 7 2方案求解方案是一组命令的组成部分 这些命令有时也称作假设分析工具 相关结果依据一个或多个变量 通过相关算法求解而来 方案是MicrosoftExcel保存在工作表中并可进行自动替换的一组值 方案求解可以处理两个以上变量的复杂计算问题 最多可以引用32个变量 1 7 3规划求解所谓的 规划求解 就是在给定的多个方案中根据求极值原理 进行最优化求解的过程 通过规划求解可以进行资源的最佳配置 实现效益最大化或耗费的最小化 实例 1 7 4数据透视表数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格 可以转换行和列以查看源数据的不同汇总结果 可以显示不同页面以筛选数据 还可以根据需要显示区域中的明细数据 创建数据透视表有以下几个来源 MicrosoftExcel数据清单或数据库 外部数据 多个MicrosoftExcel工作表或另一个数据透视表 1 7 5宏 Macroinstruction 是指一些操作的集合 或是一连串命令的集合 使用宏语言VBA编写程序 可以建立一个完整的财务管理应用系统 有效地控制Excel 使不懂Excel的财务管理人员也能方便 简捷地使用该系统 进行管理 分析和决策 宏语言通过使用Excel中的对象对Excel进行控制 宏 1 8用Excel建立财务管理模型的基本程序 开始 明确任务和目标 确定模型的具体形式 在计算机环境中建立模型 模型求解 模型评价 正确吗 支持决策 Y N 程序设计步骤 分析问题 确定解题方法 确定算法 写出操作步骤 画流程图 用高级语言写出源程序 上机运行程序 运行结果评价 正确吗 分析整理结果 Y N 1 9Excel的VBA编程基础 1 9 1VBA简介VBA VisualBasicforApplications 应像Excel的遥控器 它能够使Excel的操作自动化 使Excel的应用更方便 更快捷 VBA具有与VB相同的语言引擎 但它不能独立于主应用程序Excel运行 它以宏 macroinstruction 的形式运行 VBA是一种计算机编程语言 用它提供的语句 命令可以编写包含若干指令序列的宏程序 以指挥Excel进行相应的工作 宏程序是指用VBA提供的各种函数 语句 对象 方法和属性等编写的程序 在Excel中也称为过程 过程是在VBA模块中的一个可执行的VBA程序代码块 过程由程序代码序列组成 这些代码序列组合在一起可完成某项任务 VBA中的过程主要可分为两类 子过程 subprocedure 与过程函数 functionprocedure 一 常量 变量 运算符和表达式 一 常量在整个程序运行过程中 值保持不变的量称为常量 常量一般有 数值常量 如3 5 78 12等都是数值常量 字符及字符串常量 字符常量用定界符 表示 如 销售量 655 ABC 等都是字符常量 符号常量 符号常量是指在程序中用符号表示的常量 当在程序中多次使用一些常数值时 可以定义符号常量 语法为 const常量名 常量值 如constPI 3 141 constAA 销售量 逻辑常量 逻辑常量只有两个 即True 真 和False 假 日期常量 用 括起来的字符串就是日期常量 如 3 2 2005 1 10 2006 都是日期常量 二 变量1 变量名在程序运行中值发生变化的数据称为变量 变量是代表数据的一个名称 通过变量可引用它所存储的值 变量的命名必须遵循下列的规则 1 变量名必须以字母或汉字开头 不能以数字或其他字符开头 如A 销售量 部门3 B4等都是合法变量名 而6BB 8X等都是非法的变量名 2 变量名必须由字母 数字 汉字或下划线组成 3 变量名中不能包含标点符号 空格或其他类型的声明字符 等 4 变量名最长不能超过255个字符 5 变量名不能与某些关键字同名 如OR And If Loop等 2 变量的数据类型在VBA中 变量的数据类型多达11种 变量数据类型决定了变量能够存储哪类数据 变量数据类型列表如下 3 变量的声明变量的声明就是事先将变量通知程序 对变量进行声明一般可采用Dim或ReDim 此外 还有Public Private Static 它们既可以对一个变量进行声明 也可以对多个变量进行声明 语法为 1 Dim变量名 As数据类型 变量名 As 数据类型 2 ReDim变量名 As数据类型 变量名 As 数据类型 3 Public变量名 As数据类型 变量名 As 数据类型 4 Private变量名 As数据类型 变量名 As 数据类型 5 Static变量名 As数据类型 变量名 As 数据类型 例如 将ABC定义为字符串变量 其声明为 DimABCAsString在Dim语句声明一个变量后 VBA系统自动为该变量赋值 若变量为数值型 则初值为0 若变量为字符型 则初值为空字符串 未定义数据类型的变量 则默认Variant 此外 也可以采用隐式声明的方式对变量进行声明 即在变量后加一个符号 例如ABC 销售量 这里ABC 就直接定义了ABC为字符串变量 表示定义为字符串 其他一些声明变量的符号有 整型 长整型 单精度浮点型 双精度浮点型 货币型 三 运算符和表达式1 运算符 1 算术运算符算术运算符用来对数值进行计算 包括 加法运算 减法运算 乘法运算 除法运算 乘方运算 和Mod 对数值进行除法运算 返回余数运算 2 关系运算符关系运算符是用来进行比较的运算符 包括 大于 大于等于 等于 不等于 3 连接运算符连接运算符是用来合并字符串的运算符 包括 和 它们用来强制将两个字符串连接起来 例如表达式A 销售量 665 则返回值为 销售量665 需要注意的是 在表达式中在连接符号前后必须有一个空格 4 逻辑运算符逻辑运算符是执行逻辑运算的运算符 包括And 与 Eqv 等价 Imp 包含 Not 非 Or 或 Xor 异或 2 表达式及运算符的优先顺序当表达式中有不止一种运算符时 系统就会按照规定的顺序进行计算 各个运算符的优先顺序是不一样的 1 各种运算符的优先顺序 从高到低 算术运算符 字符串连接运算符 关系运算符 逻辑运算符 2 算术运算符的优先顺序 从高到低 乘方 负号 乘法 除法 整除 Mod 除法取余数 加法 减法 3 逻辑运算符的优先顺序 从高到低 Not And Or Xor Eqv Imp 4 所有关系运算符的优先顺序都相同 即按从左到右的顺序进行 过程就是一段程序代码 它分为两种类型 子程序和自定义函数 子程序以Sub开头 以EndSub结束 自定义函数以Function开头 以EndFunction结束 在Sub或Function与EndSub或EndFunction之间的是程序代码 特别需要注意的是 在程序代码中 引号 逗号 括号等不能是中文状态下的符号 而必须是英文状态下的符号 不论是子程序还是自定义函数 它们都保存在相关工作簿的模块中 模块是工作簿的一个组成部分 通常情况下 模块只有在VBA编辑环境中才能看到 子程序是VBA的最基本程序单位 它可能很简单 也可能很复杂 有的子程序仅几行语句 而有的则会有成千上万行语句 子程序或以带参数 也可以不带参数 以录制宏的方式录制的宏就是不带参数的子程序 1 9 2VBA程序的结构 一 过程 过程以Sub语句开始 以EndSub语句结束 过程可以执行某种操作 但无返回值 其结构为 Sub过程名 命令序列1命令序列nEndSub例如 Submain 销售量 cells 1 1 单价 cells 1 2 callsale 销售量 单价 Endsub Subsale 销售量 单价 销售额 销售量 单价cells 1 3 销售额Endsub在主程序中 将单元格A1中的数据赋值给变量销售量 将单元格B1中的数据赋值给变量单价 然后调用计算销售额的子程序 SubSale 销售量 单价 并将计算的销售额数据输出到单元格C1 二 自定义函数 自定义函数以Function语句开头 以EndFunction语句结束 可以有返回值 其结构为 Function函数名 参数 命令序列EndFunction例如上例 定义自定义函数如下 Function销售额 销售量 单价 销售额 销售量 单价EndFunction主程序为 Submain 销售量 cells 1 1 单价 cells 1 2 Cells 1 3 销售额 销售量 单价 EndSub 三 数组的应用有时需要处理一种特定类型的大量数据 比如要存储一年中每天的销售量 这时如果使用常规变量 就需要365个 显得很不方便 利用数组就可以方便地用一种数组来表示同一种类的大批数据 数组是同类变量的一个有序集合 数组中的元素称为数组元素 数组元素具有相同的名字和数据类型 通过下标 索引 来识别它们 数组元素的表达式为 数组名 下标1 下标2 一 数组声明在使用数组前 必须声明数组 语法为 Dim数组名 数组元素上下界 As数据类型数组元素上下界的个数表示维数 下界可以省略 表示0 当只有一个时表示一维数组 数组的维数最高可达60维 DimAB 10 AsCurrency 表示AB为一维数组 数组元素的个数为11个 0 10 数组的大小不一为11 数据类型为货币型 DimAB 3 4 AsInteger 表示AB为二维数组 其中第一个数组元素个数为4个 0 3 第二个数组元素的个数为5个 0 4 数组元素的大小为20 数据类型为整数型 二 静态数组和动态数组静态数组是指维数和大小固定不变的数组 例如上面的例子均为静态数组 使用Dim来声明 动态数组是指在程序运行时其大小可以变化的数组 使用动态数组可以节省内存 加快运行速度 动态数组使用ReDim来声明 它表示在程序运行中 变量n是变化的 因而数组ABC的大小也是变化莫测的 动态数组的声明必须放在数组元素变量赋值语句的后面 如 PublicSubhhh DimI nAsIntegern InputBox 请输入要计算的次数 ReDimaabc n ForI 1Tonaabc I ICells I 1 aabc I NextIEndSub 四 对象 属性和方法Excel主要有四个基本对象 由上到下分别为Application Excel程序本身 WorkBook 工作簿 Worksheets 工作表对象集 Range 一个单元格或单元格区域 Application对象 worksheets worksheet workbooks workbook 一 Application对象及使用一般情况下 Application对象就是指Excel程序本身 利用其属性可以方便而灵活地控制Excel应用程序的工作环境 常用的属性有 ActivateWorkBooK 当前工作簿 Activateworksheet 当前工作表 Activatecell 当前单元格 Caption 标题 Cursor 鼠标指针 DisplayAlerts 显示警告 StatusBar 状态栏 DisplayStatusBar 显示状态栏 DisplayScrollBar 显示滚动条 DisplayFormulaBar 显示编辑栏 UserName 用户名 等 以VBA程序中 通过简单的VBA语句就可以改变Excel的相应属性值 例如 下面的一个子程序就使用了WorkBook和Worksheet对象 该程序启动Excel并创建一个新的包含一个工作表的工作簿 例1 1 Application对象及使用例模块7PublicSubaaa DimNexcel1AsExcel ApplicationSetNexcel1 NewExcel ApplicationNexcel1 Visible TrueNexcel1 SheetsInNewWorkbook 4nexcel1 Workbooks AddEndSub若不想显示Excel的编辑栏 可使用下面的语句 Application Dislayformulabar false 二 Workbook对象及使用Application对象的下一层是工作簿对象集 它包含有若干工作簿对象 WorkBook 与工作簿对象有关的常用属性和方法有 Activesheet 当前工作表 属性 Saved 已保存 属性 Activate 激活 方法 Open 打开 方法 Close 关闭 方法 Save 保存 方法 SaveAs 另存为 方法等 例1 2 假设Excel已经打开了三个工作簿Book1 xls Book2 xls Book3 xls 下面的子程序的功能就是 1 激活工作簿Book3 并将此工作簿以名字 我的工作簿 保存在 我的文档 文件夹中 2 关闭工作簿Book1 xls 例模块6PublicSubhhh workbooks Book3 activateActiveworkbook saveasfilename c mydocuments 我的工作簿 workbooks Book1 closeEndsub打开工作簿的命令为 workbooks open c 文件夹 文件名 三 Worksheets对象及使用workbook对象的下一层是worksheets对象集 它包含有255张工作表 Worksheet Worksheet对象就是worksheets对象中的一张工作表 与worksheets对象有关的常用属性和方法有 Name 工作表名 属性 Visible 工作表是否可见 属性 Add 添加 方法 Calculate 手工计算 方法 Copy 复制 方法 Move 移动 方法 Delete 删除 方法等 例1 3 假设工作簿 销售管理 xls 中有三个工作表 销售量 销售价格 和 销售额 现在要求编程实现 1 在当前工作簿的工作表 销售量 之前添加一个名为 销售统计 的工作表 2 隐藏工作表 销售价格 3 将工作表 销售额 激活为当前工作表 程序如下 例模块1PublicSub销售管理 Worksheets 销售量 ActivateWorksheets Add Name 销售统计 Worksheets 销售价格 Visible falseWorksheets 销售额 SelectEndSub 四 Range对象及使用worksheet对象的下一层是Range 单元区域 对象 Range对象可以是某个单元格 cell 某一行期 Row 某一列 Column 或者多个相邻或不相邻单元格区域对象 与Range对象有关的常用属性有 Address 位置 Cells 单元格 CurrentRegion 多行多列区域 Formula 公式 Name 单元格区域名称 Value 值 等 单元格区域对象的方法主要有 Autofit 自动列宽 行高 Clear 清除单元格区域中的所有内容 Clearcomments 清除批注 Clearcontents 清除内容 ClearFormats 清除格式 Copy 复制 Cut 剪切 Count 统计行列数 Pastespecial 选择性粘贴 以及单元格格式设置等 例1 4 Range应用实例例模块2PublicSubLLL MsgBox 当前活动单元地址是 ActiveCell AddressCells 3 3 SelectSelection FormulaR1C1 试验 Range b7 Formula sum A1 A6 Range b8 Formula Average A1 B6 Range C2 F6 Name 标题 EndSub 常用的Range命令如下 1 选择单元格命令 Range B2 Select或Cells 2 2 Select2 选择单元格区域命令 Range A1 B2 Select Range Cells 1 1 Cells 2 2 Select Range A1 B2 Activate或Range Cells 1 1 Cells 2 2 Activate3 激活单元格命令 Activatecell4 给激活单元格赋值命令 Activecell value 10或Activecell value 合计 5 直接给单元格赋值 Cells 1 1 200或Cells 1 1 销售量 给单元格区域赋值命令 Range A1 B2 Value 100或Range A1 B2 100或Range Cells 1 1 Cells 2 2 100给单元区域命名命令 Range A1 B2 name 销售量 或Range Cells 1 1 Cells 2 2 name 销售量 8 选择单元格区域命令 必须激活区域内的任一单元格 Range A1 B2 Activate 或Selection CurrentRegion Select或ActivateCell CurrentRegion Selcet9 选择列或选择行的命令 选择第1列 Columns 1 Select选择第1行 Rows 1 Select10 求单元格区域中单元格个数 行数及列数的命令 求单元格个数 X Selection Count求行数 X Selection Rows Count求列数 X Selection Columns Count11 读取激活单元格的行值或列值的命令 读取行值 X ActivateCell Row读取列值 X ActivateCell Column12 将单元格的数据赋值给变量的命令 销售量 Cells 1 1 Value或销售量 Cells 1 1 13 将变量的数据赋值给单元格的命令 Cells 1 1 Value X或Cells 1 1 X14 给单元格输入公式的命令 即在执行程序后 在单元格内出现公式 与在Excel中直接输入公式是一样的 常用的几种命令如下 Cells 2 1 A1 B1 Range A2 A1 B1 Range A2 Formula A1 B1 Cells 2 1 Formula A1 B1 Cells 2 1 A 1 B 1 五 Excel内置函数的使用在利用Excel进行财务管理工作中 经常要使用Excel内置函数 同样 在编制VBA程序时 也常常会在程序中使用Excel内置函数 Excel内置函数基本上有两种形式 一类是参数为单独赋值的函数 即只需将对数数据或单元格引用输入到函数中就可以进行计算 例如PV 现值 函数 FV 终值 函数 ABS 绝对值 函数 DATE函数等 另一类是必须以数组形式或单元格区域形式输入参数的函数 例如NPV函数 IRR函数 AVERAGE函数 MAX函数 MIN函数 SUM函数等 因此 在设计VBA程序的过程中使用Excel内置函数时 必须依据函数的不同特点采用不同的方法输入参数 一 参数单独赋值的函数这类函数可以直接在VBA中使用 例1 5 编写一个根据给定的年数 年利率和年金来计算年金现值的例子 Sub年金现值计算 例模块3年金 Cells 1 2 期限 Cells 2 2 年利率 Cells 3 2 年计息次数 Cells 4 2 总计息期数 期限 年计息次数Cells 5 2 总计息期数Cells 6 2 PV 年利率 年计息次数 总计息期数 年金 EndSub该程序的功能是 分别从单元格B1 B2 B3和B4中取出年金 期限 年利率和年计息次数数据 然后计算总计息期数和年金现值 并分别输出到单元格B5和B6中 二 参数需以数组或单元格区域形式赋值的函数处理这种情况的方法是将函数公式输出到Excel工作表的单元格中 即与在Excel工作表直接输入函数的效果是一样的 当程序运行后 在工作表的单元格中得到的是一个函数公式 显示的是计算结果 例1 6 编写一个使用Sum函数的例子 PublicSub求和 合计 sum A1 A8 Cells 9 1 合计Endsub 1 利用VisualBasic编辑器编辑VBA程序 基本操作过程是 选择 工具 菜单中 宏 命令下的 VisualBasic编辑器 命令 便进入VB编辑器界面 选择 插入 菜单中的 模块 命令 Excel就会在工作簿中增加模块1 此时可以输入和编辑程序 2 利用录制宏的方式编辑VBA程序 基本的操作步骤是 选择 工具 菜单中 宏 命令下的 录制新宏 命令 Excel就会自动录制简单的宏程序 1 9 3VBA程序的设计方法 1 顺序结构 在正常情况下 宏程序的执行是以 顺序 方式进行的 即由上而下逐一执行 1 9 4VBA宏程序的结构 2 分支结构 在某些情况下 宏程序的执行是根据测试的结果进行的 不同的测试结果执行不同的操作 这就要使用分支结构 分支结构有三种结构 1 If condition then statements 2 If condition then statements1 Else statements2 Endif 3 selectcase testexpression case expressionlist1 statements case expressionlist2 statements Endcase 3 循环结构 在某些情况下 宏程序的执行是以每间隔一定的条件重复执行某项操作 即循环结构 循环结构也有三种语法格式 1 Dowhile condition statements loop 2 forcounter starttoend stepincrement statements next counter 3 foreach element in group statements next element 例 将薪酬数据库中大于50000的薪酬数据用黑体红色表示 例模块8Subforeach ForEachnumInRange f2 f57 Ifnum Value 50000Thennum Font Bold Truenum Font ColorIndex 3EndIfNextnumEndSub 1 宏记录器简介Excel提供的宏记录器可以帮助财务管理人员记录其所做的各种操作 当操作结束时 关闭宏记录器 宏程序便保存在宏表中 宏记录完之后 可以通过执行宏程序 使操作自动化 1 9 5宏记录器的使用 1 记录宏的步骤和方法选择 工具 菜单上的 宏 命令 然后从子菜单中选择 录制新宏 命令 弹出录制新宏对话框 设置完毕后按 确定 按钮就可以进行具体操作 录制新宏 完成后单击 停止录制 按钮 结束宏录制 2 执行宏程序宏程序编写之后 或宏程序记录下来之后 要在任何时间执行这个宏 此时Excel会自动执行宏程序中的全部命令 自动完成财务管理人员需要做的工作 执行宏的方法有以下几种 1 通过菜单执行宏程序 选择要执行的工作表 在 工具 菜单中选择 宏 命令 弹出 宏 对话框 在 宏名 框中选择要执行的宏程序 选择 执行 按钮 计算机自动执行该程序 2 设置宏按钮执行宏程序 选择要执行的工作簿 选择 视图 菜单上的 工具 子菜单中的 窗体 命令 然后从弹出的窗体对话框中单击按钮 并在工作表中的适当位置画出按钮 并与录制的宏程序联接就完成了按钮的设置 然后 单击按钮就可以自动运行宏程序 习题 2 理解Excel单元格的相对地址和绝对地址 3 通过上机操作 理解Excel工作簿中下列操作的方法和结果 从工作表列队中选择工作表 改变工作簿中工作表的数量 选择单元及单元区域 建立水平分割和垂直分割 1 通过上机操作 熟悉Excel的主窗口 4 通过使用 插入 菜单的 函数 命令 观察各种函数的语法 功能和返回值 5 理解计算机财务管理模型的建立程序