Excel会计与财务管理-理论的方案暨模型第11章Excel财务管理系统的建立教学课件

1、第11章 Excel财务管理系统的建立本章主要内容： 11.1 宏和VBA 11.2 财务管理模型用户界面的设计 11.3 财务管理系统菜单的设计 11.4 控制应用系统自动启动的方法 第1页，共61页。本章目标：第2页，共61页。VBAVBA是基于Visual Basic for Windows发展而来的新一代标准宏语言，提供了面向对象的程序设计方法和相当完整的程序设计语言。对于在工作中需要经常使用Office套装软件的用户，学习VBA有助于使工作自动化，提高工作效率。另外，由于VBA可直接应用Office套装软件的各项强大功能，所以对于程序设计人员的程序设计和开发也更加方便快捷。11.1

2、宏和VBA第3页，共61页。宏宏程序是指用VBA提供的各种函数、语句、对象、方法和属性等编写的程序。宏程序可以被多次重复使用，自动执行复杂的任务，减少完成任务所需的步骤。11.1 宏和VBA第4页，共61页。宏的创建和运行 使用宏录制创建新宏 使用Visual Basic 编辑器创建新宏11.1 宏和VBA第5页，共61页。11.1.2 使用宏录制器录制宏 宏录制器是Excel提供的一种软件工具，它能够将用户的操作过程记录下来，并自动将所记录的操作转换成VBA程序代码。对于经常重复进行的操作过程，可以通过宏录制器将它记录下来，当需要再次进行这些操作时，只需运行录制的宏，Excel就能自动完成这

3、些重复的操作。需要注意的是，当录制宏的工作开始后，所有的操作步骤都将被记录在宏中，所以应尽量减少不必要的或错误的操作，如果在录制宏时出现失误，更正失误的操作也会记录在宏中。 第6页，共61页。11.1.2 使用宏录制器录制宏_记录宏程序的方法和步骤选择【工具】菜单上的【宏】命令，然后从子菜单中选择【录制新宏】命令。在“宏名”框中输入宏程序的名称。在“保存在”框中，指定存放宏的位置。在“说明”框中，输入要录制宏的有关说明信息。选择【确定】按钮，此时【停止录制】按钮出现在屏幕上。用户可以进行各种操作，如选定单元格、输入标题、设置表格等。单击【停止录制】按钮或者选择【工具】菜单上【宏】命令下的【停止

4、录制】命令，结束宏录制。第7页，共61页。11.1.2 使用宏录制器录制宏_记录宏实例【例11-1】打开工作簿cwgl11，用录制宏的方式记录以下工作：插入一张工作表，在新插入的工作表上制作一张简单的分部销售情况表，表结构如图11-4所示。录制一个能够建立这种表结构的宏Sales。图11-4：销售情况表第8页，共61页。11.1.2 使用宏录制器录制宏_录制宏的过程：（1）选择【工具】菜单上【宏】命令下的【录制新宏】命令；（2）在“宏名”框中输入宏程序的名称“Sales”；（3）在“保存在”框中指定存放宏的位置“当前工作簿”；（4）在“说明”框中，输入要录制宏的有关说明信息；（5）选择【确定】

5、按钮，此时【停止录制】按钮出现在屏幕上；（6）选择【插入】菜单上的【工作表】命令；（7）选择A1单元格，输入“销售情况表”；（8）依次在其他单元格输入文字，并调整字体字号；（9）选择范围A1:AF，合并单元格；（10）选择标题，居中显示，单击【字体】工具，选择14号，加粗字体；（11）单击【停止录制】按钮，结束宏录制。 第9页，共61页。11.1.2 使用宏录制器录制宏_查看所录制的宏宏程序录制完毕后，选择【工具】菜单上【宏】命令下的【宏】菜单项，弹出“宏”对话框。在“宏名”框中选择宏程序“Sales”，单击【编辑】按钮，则可显示刚才所录制的宏程序“Sales”。第10页，共61页。11.1.

6、3 利用VBA编写宏程序宏录制器存在问题：录制的宏往往语句冗长（记录了许多不需要的步骤，不需要的语句），会降低执行速度当录制宏的工作开始后，所有的操作步骤都将被记录在宏中，如果在录制宏时出现失误，更正失误的操作也会记录在宏中。有时为了更正操作，可能需要重新录制新宏宏录制器在灵活度方面有所欠缺有些功能是无法通过录制实现的因此需要用编写宏程序第11页，共61页。11.1.3 利用VBA编写宏程序_宏程序的分类子程序子程序是VBA的最小程序单位。 函数函数由Function和End Function 语句所包含起来的 VBA语句组成。子程序可以执行某种操作，但无返回值。Function函数和Sub子

7、程序很类似，但函数有一个返回值。第12页，共61页。11.1.3 利用VBA编写宏程序_结构化程序设计的控制结构（1）顺序结构（2）选择结构（3）循环结构第13页，共61页。11.1.3 利用VBA编写宏程序（1）顺序结构在一般情况下，一个VBA的Sub子程序和Function函数的执行都是从程序代码的第一个语句行开始，逐条运行程序代码中的语句，直至遇到End Sub或End Function来结束整个程序的执行。这就是顺序结构。第14页，共61页。11.1.3 利用VBA编写宏程序_（1）顺序结构【例11-2】编写一宏程序。过程名：PRO1功能：在cwgl工作簿中的“Sales”工作表中，赋

8、予B1单元报表的标题“销售情况分析表”；赋予B2单元日期“2010年12月”；在A3、B3、C3、D3、E3单元格中依次输入“部门”、“实际销售额”、“保本销售额”、“盈亏状况”、和“销项税”；在A4、B4、C4单元格中依次输入“计算机部”、“100”和“80”。第15页，共61页。11.1.3 利用VBA编写宏程序_（1）顺序结构【例11-2】编写一宏程序。过程名：PRO1功能：在cwgl工作簿中的“Sales”工作表中，赋予B1单元报表的标题“销售情况分析表”；赋予B2单元日期“2010年12月”；在A3、B3、C3、D3、E3单元格中依次输入“部门”、“实际销售额”、“保本销售额”、“盈

9、亏状况”、和“销项税”；在A4、B4、C4单元格中依次输入“计算机部”、“100”和“80”。第16页，共61页。11.1.3 利用VBA编写宏程序_（1）顺序结构编制的宏程序如下：Sub pro1() Range(B1).Select ActiveCell.FormulaR1C1 = 销售情况分析表 Range(B2).Select ActiveCell.FormulaR1C1 = 2010年12月 Range(A3).Select ActiveCell.FormulaR1C1 = 部门 Range(B3).Select ActiveCell.FormulaR1C1 = 实际销售额 Rang

10、e(C3).Select第17页，共61页。11.1.3 利用VBA编写宏程序_（1）顺序结构 ActiveCell.FormulaR1C1 = 保本销售额 Range(D3).Select ActiveCell.FormulaR1C1 = 盈亏状况 Range(E3).Select ActiveCell.FormulaR1C1 = 销项税 Range(A4).Select ActiveCell.FormulaR1C1 = 计算机部 Range(B4).Value = 100 Range(C4).Value = 80End Sub 第18页，共61页。11.1.3 利用VBA编写宏程序_（2）

11、选择结构有些时候，需要程序代码按一定的条件执行，当条件成立时，执行一部分程序代码，条件不成立时执行另外一部分程序代码。这种功能需要用VBA的选择结构来实现。VBA中的条件判断语句有If语句和Select Case语句两种第19页，共61页。11.1.3 利用VBA编写宏程序_（2）选择结构单分支语句IfThen。单行结构条件语句单行结构条件语句是最基本的条件语句，其语法结构为：If 逻辑表达式 Then 语句块结构条件语句单行结构条件语句中，满足条件时只执行一条语句，若有多行语句需要执行，则需使用块结构条件语句。其语法结构如下：If 逻辑表达式 Then 语句序列1 语句序列2第20页，共61

12、页。11.1.3 利用VBA编写宏程序_（2）选择结构二分支语句IfThenElse。其语法结构如下： If 逻辑表达式 Then 语句序列1 Else 语句序列2 End If第21页，共61页。11.1.3 利用VBA编写宏程序_（2）选择结构【例11-5】编写一宏程序。过程名：PRO3功能：如果实际销售额大于等于保本点销售额，在屏幕上输出“胜利完成任务！”，并在盈亏状态单元格赋“盈利”；否则在屏幕上输出“仍需努力！”，并在盈亏状态单元赋“危险”。第22页，共61页。11.1.3 利用VBA编写宏程序_（2）选择结构编写的宏程序如下：Sub PRO3() If Range(B3).Valu

13、e = Range(C3).Value Then MsgBox 胜利完成任务！ Range(D4).Select ActiveCell.FormulaR1C1 = 盈利Else MsgBox 仍需努力！ Range(D4).Select ActiveCell.FormulaR1C1 = 危险End If End Sub第23页，共61页。11.1.3 利用VBA编写宏程序_（2）选择结构多分支语句：ifThenElseIf。语法结构为：If 条件1 Then 语句组1ElseIf 条件2 Then 语句组2 ElseIf 条件n Then 语句组nElse 语句组n+1End If第24页，共

14、61页。11.1.3 利用VBA编写宏程序_（2）选择结构【例11-6】某汽车出租公司可为顾客提供货物运送服务，根据货物的价值及运输路程设置运费，并且可根据路程远近给予适当的折扣。设运费F（单位为元），货物单价P（单位为元），货物重量W（单位为吨），路程S（公里）及优惠系数（D）之间的关系式为：F=P*W*S*(1-D)。优惠系数d与路程远近的关系如下，编写计算折扣的函数。第25页，共61页。11.1.3 利用VBA编写宏程序_（2）选择结构编写的函数如下：Function d(s) If s = 1000 Then d = 0.1 ElseIf s = 750 Then d = 0.07 E

15、lseIf s = 500 Then d = 0.05 ElseIf s = 250 Then d = 0.02 Else d = 0 End IfEnd Function 第26页，共61页。11.1.3 利用VBA编写宏程序_（2）选择结构多分支语句：Select Case。Select Case语句的语法结构如下：Select Case 测试表达式Case 表达式1 语句组1Case 表达式2 语句组2 Case Else 语句组n End Select第27页，共61页。11.1.3 利用VBA编写宏程序_（2）选择结构【例11-7】编写一宏程序。过程名：PRO4功能：如果实际销售额大

16、于保本销售额，在屏幕上输出“盈利！”，并在盈亏状态单元格赋“盈利”；如果实际销售额等于保本销售额，在屏幕上输出“保本！”，并在盈亏状态单元格赋“保本”；如果实际销售额小于保本销售额，在屏幕上输出“亏损！”，并在盈亏状态单元格赋“亏损”。第28页，共61页。11.1.3 利用VBA编写宏程序_（2）选择结构编写的程序如下：Sub PRO4() Dim sx As Double, bx As Double sx = Range(B4).Value bx = Range(C4).Value Select Case sx Case Is bx MsgBox 盈利！ Range(D4).Select A

17、ctiveCell.FormulaR1C1 = 盈利 End SelectEnd Sub 第29页，共61页。11.1.3 利用VBA编写宏程序_（3）循环结构在顺序结构的程序中，每个语句只执行一次。在分支结构的程序中，根据逻辑表达式的值选择某一分支执行，所选分支的语句也只执行一次。然而，在处理实际问题时，经常要用同一种方法对不同的数据进行重复处理，这些相同的操作可以通过重复执行同一程序段来实现。这种重复执行具有特定功能程序段的程序就是循环程序。第30页，共61页。11.1.3 利用VBA编写宏程序_（3）循环结构ForNext循环结构。语法结构为：For counter = start To

18、 end step 步长 循环语句1 循环语句2 循环语句3 循环语句nNext counter该结构的功能是：将初值赋给计数器counter。当初值start没有达到终值end时，VBA自动循环执行循环体中的循环语句，每执行一次循环，计数器加1或增加指定的步长step步长；当计数器达到终值时，跳出循环。第31页，共61页。11.1.3 利用VBA编写宏程序_（3）循环结构【例11-8】某公司职工档案数据保存在Excel工作表中，如图11-7所示。该公司共有1234名职工，每月要从工资表中扣除一定的住房公积金，假设住房公积金按以下的百分比扣除。 第32页，共61页。11.1.3 利用VBA编写

19、宏程序_（3）循环结构首先，自定义函数计算公积金的百分比。Function countrate(salary As Double) Dim rate As Double If salary 2000 Then rate = 0.1 ElseIf salary 1500 Then rate = 0.07 ElseIf salary 1200 Then rate = 0.05 ElseIf salary 1000 Then rate = 0.02 ElseIf salary 800 Then rate = 0.01 Else rate = 0 End If countrate = rateEnd

20、Function第33页，共61页。11.1.3 利用VBA编写宏程序_（3）循环结构编写计算所有职工工积金的宏PRO5：Sub PRO5()For i = 3 To 1236 r = countrate(Cells(i, 2) Cells(i, 3) = r Cells(i, 3).Style = Percent Cells(i, 4) = r * Cells(i, 2) Cells(i, 5) = Cells(i, 2) - Cells(i, 4) NextEnd Sub第34页，共61页。11.1.3 利用VBA编写宏程序_（3）循环结构DoLoop循环结构。语法结构是：Do While

21、 条件 语句序列Loop功能：当条件成立时，VBA重复执行语句序列直到条件不成立，跳出循环。第35页，共61页。11.1.3 利用VBA编写宏程序_（3）循环结构【例11-9】继续例11-8，假设并不知道有多少位职工，在其他条件都相同的条件下，现用DoLoop循环结构来计算每位职工的住房公积金比例、应交公积金和实际发放工资。第36页，共61页。11.1.3 利用VBA编写宏程序_（3）循环结构Sub PRO6()i = 3Do r = countrate(Cells(i, 2) Cells(i, 3) = r Cells(i, 3).Style = Percent Cells(i, 4) =

22、r * Cells(i, 2) Cells(i, 5) = Cells(i, 2) - Cells(i, 4) i = i + 1Loop Until (IsEmpty(Cells(i, 2)End Sub第37页，共61页。11.1.3 利用VBA编写宏程序_（3）循环结构For EachNext语句。For EachNext循环与ForNext循环类似，但它针对数组或对象集合中的每一元素重复一组语句，而不是重复语句一定的次数。语法结构：For Each 对象元素变量 in 对象集合 语句序列1 Exit For 语句序列2Next 对象元素变量第38页，共61页。11.1.3 利用VBA编

23、写宏程序_（3）循环结构【例11-10】编写一程序过程名：PRO7功能：关闭所有打开的工作簿。编写的宏程序如下：Sub PRO7() For Each Book in Workbooks() Book.Close Next BookEnd Sub第39页，共61页。11.1.4 宏程序的运行1.在EXCEL界面运行宏2.在开发环境下运行宏 第40页，共61页。11.1.4 宏程序的运行1.在EXCEL界面运行宏在Excel界面运行宏，适用于使用环境，可通过菜单命令打开“宏”界面，再选择宏名，点击【执行】或者在工作表打开状态下直接调用快捷键ALT+F8，弹出宏界面，再选择宏名，点击【执行】。通过

24、选择自定义菜单或点击自定义按钮的方式执行宏（详见本章11.2和11.3内容）将宏配置为在打开工作簿时自动运行（详见本章11.4节内容）。第41页，共61页。11.1.4 宏程序的运行2.在开发环境下运行宏首先打开Microsoft Visual Basic界面，然后在【运行】菜单上单击【运行子过程/用户窗体】 或者按F5键调出“宏”对话框。第42页，共61页。第11章 Excel财务管理系统的建立 11.1 宏和VBA 11.2 财务管理模型用户界面的设计 11.3 财务管理系统菜单的设计 11.4 控制应用系统自动启动的方法 第43页，共61页。11.2 财务管理模型用户界面的设计本节主要内

25、容：11.2.1 界面设计的主要工具11.2.2 建立各模块用户界面11.2.3 建立财务管理系统界面第44页，共61页。11.2.1 界面设计的主要工具1.绘图工具2.窗体第45页，共61页。11.2.2 建立各模块用户界面第46页，共61页。11.2.3 建立财务管理系统界面1.绘制图形2.建立图形与各功能模块之间的链接第47页，共61页。第11章 Excel财务管理系统的建立 11.1 宏和VBA 11.2 财务管理模型用户界面的设计 11.3 财务管理系统菜单的设计 11.4 控制应用系统自动启动的方法 第48页，共61页。11.3 财务管理系统菜单的设计本节主要内容：11.3.1 菜

26、单管理器11.3.2 在菜单栏中增加【财务管理】菜单11.3.3 财务管理系统框架第49页，共61页。11.3 财务管理系统菜单的设计_建立新菜单的步骤（1）打开工作簿，进入用户界面。（2）选择【工具】菜单上的【自定义】命令，出现自定义对话框。（3）在“命令”标签中选择“新菜单”类别。（4）选择“新菜单”命令，并拖至菜单栏中相应的位置。（5）点击鼠标右键得到定义新菜单的快捷菜单。（6）定义新菜单的名称。（7）重复上述过程定义菜单项、子菜单以及子菜单命令的名称。（8）为菜单项、子菜单命令指定对应的宏程序。第50页，共61页。11.3 财务管理系统菜单的设计_财务管理系统框架财务分析账务处理筹资决

27、策数据处理基础项目投资决策证券投资分析盈亏平衡与经济订货量最优化问题财务预测比率分析杜邦分析长期借款筹资决策租赁借款决策.固定资产更新决策.债券投资分析模型股票投资分析模型盈亏平衡模型经济订货量模型资金管理生产管理投资组合销售预测模型利润预测模型Excel财务管理系统图11-17 Excel财务管理系统功能结构图第51页，共61页。第11章 Excel财务管理系统的建立 11.1 宏和VBA 11.2 财务管理模型用户界面的设计 11.3 财务管理系统菜单的设计 11.4 控制应用系统自动启动的方法 第52页，共61页。11.4 控制应用系统自动启动的方法本节主要内容：11.4.1 对话框操作

28、11.4.2 自动运行宏程序11.4.3 将财务管理系统所在的工作簿设置为在 Excel 启动时自动打开第53页，共61页。11.4.1 对话框操作MsgBox函数:MsgBox对话框函数可用以向用户显示一些提示信息。函数用法：MsgBox(prompt, buttons , title , helpfile, context)第54页，共61页。11.4.1 对话框操作其中各参数及其说明:参数说明prompt必选。字符串表达式，在对话框中显示为消息。prompt的最大长度约为1024个字符，具体长度取决于所用字符的宽度。如果prompt包含多行，可在各行之间使用回车符、换行符或回车符与换行符的组合来分隔各行。buttons可选。其值为数值表达式，它是值的总和，这些值指定要显示的按钮的数目和类型、要使用的图标样式、默认按钮