第五章Ecel在财务管理中的高级应用

1、在财务管理中的高级使用Excel模板简介Excel摸板功能及其创建Excel摸板是一种用来生成其他工作簿的格式文件。由一个摸板文件生成的文件，可包括和摸板文件特征相同的布局、文件、数据公式、设置、样式、名称、宏、工作表控制以及VisualBasic模块表每一个由模板文件生成的工作簿是模板的复制品。模板在财会管理、制作报表的数据录入、支出账项以及保证个分支部门预算规范的一致性方面是十分有用的。Excel模板和普通工作簿在形式上是完全一样的，但是功能上有如下两个基本区别。（1）工作簿是一次使用的，模板是多次使用的。打开一个模板时仅打开模板的复制件而不是模板本身。（2）模板文件的扩展名为.xlt除此

2、而外，工作簿模板和工作没有什么不同。1创建和存储工作簿模板创建模板和创建工作簿文件完全一致。开始时，创建Excel的工作表、图表或其他文件，然后加人数据、格式的文件所需的其他信息，最后以模板文件类型存盘。（1）创建一个工作簿模板的实质是在通常的工作表编辑环境下，设计好一个工作簿的表样。设计的工作簿包含模板所需要的全部内容（如表格布局、数据、文本、格式、公式、以及控制等）。所谓创建模板就是它存为模板形式，步骤如下：选取Excel文件“Excel另存为“命令；输入模板名称，按通常方式选取存放模板的文件目录；在Excel“另存为”对话框中选择保存类型为Excel“模板”，它自动将扩展名.xlt加到文

3、件后，如图5-1-1所示；单击Excel“保存”按钮。注意：在Excel“文件名”文本框内，仅仅将文件扩展名改写成.xlt并不能将文件夹及模板格式存储，一定不能忽略上面的第4步。（2）自动模板要改变Excel的缺省、（默认）字体、格式、保护状况等工作属性，最好生成一个自动模板。例如，可以生成一张工作簿的自动模板，它包括用户名字或当前日期作为脚注。如果将一个模板文件以名字BOOK存入目录C:MYDOCUMENTSH下，该模板文件称为自动模板，它为用户所建的一切新工作簿赋予模板式样，实际上它控制所有新工作簿的样式和内容。生成自动模板的步骤如下：打开或创建一个用所有新工作簿样式的工作簿，如必须的数据

4、、公式以及所需要的格式；选取“文件”“另存为”命令；在“文件名”文本框键入Book，如图5-1-2所示；在“保存位置”处选取C:MYDOCUMENTSHY;从“保存类型“列表框内选取”模板“，它将扩展名.xlt加到文件后；单击“保存”按钮。在生成自动模板后，每当打开一个新工作表（例如选取命令“文件”“新建”，或凌晨击常用工具栏的“新建”按钮），新工作表自动命名为BOOK勺模板样式。因为利用模板创建的新文件都以模板名加一个整数的形式命名，所以模板名不宜过长，以不超过6个字符为宜。维护和使用模板创建、存储工作模板是为了使用模板。为了更好地使用模板，还应对模板进行一定的维护。1从模板创建工作表打开一

5、个模板将生成一个以模板为基础的新工作表文件，但模板本身并不改变，新文件使用临时文件名。例如，槿板文件为“工业企业财务报表.XLT”，新文件名则为“工业财务报表1.XLS、工业企业财务报表2.XLS等。根据模板创建的不同形式，从模板创建工作表的方式有所不同。但有一个共同点是，要用模板创建工作表，该模板一定不能以模板文件本身打开。如果创建模板未存放在目录C:MYDOCMENTSH下，则只能使用“文件”“打开”命令，在“打开”对话框中直接选择模板文件打开。注意这时并非打开模板文件本身，而是一个和模板文件内容完全相同的工作表文件。如果创建的模板存放在C:MYDOCUMENTSHY目录下，但名字不是BO

6、OK.XLT（即非自动模板P,而且在该上目录下没有自动模板文件，则除了用“文件”“打开”命令外，使用“文件”“新建”命令将打开一个“新文件”对话框，该对话框内显示所有C:MYDOCMENTSHY目录下模板，用户可从吕选择一个模板用于创建自己的工作簿文件。如果创建了一个自动模板，除了用“文件”“打开”命令外，也可以使用“文件”“新建”命令。这时不打开“新文件”对话框，而是直接按自动模板创建新文件，如图5-1-3所示。如上所述，虽然可以打开一个存于任何一个下模板用“文件夹”“打开”命令，但只有存于目录C:MYDOCUMENTSHY下的模板才能出现在“新文件”对话框内供调用。如果建立闻自动模板，又要

7、使用他模板文件时，也只有使用“文件”“打开”命令。在图5-1-3中单击“确定”按钮，则会出现图5-1-4所示资产负债表模板，它是在“工业企业财务报表.xlt文”件下的。如果要用“工业企业财务报表.XLT的其他模板，另创建一张完全相同的工作表文件，直接用“文件”“打开”命令选中文件“工业企业财务报表.XLT”，按回车建。如果该模板文件存储在C:MYDOCUMENTSHY下，使用“文件”“新建”命令从新文件对话框内选择“工业企业财务报表.XLT”，按回车键。2编辑模板一个模板应当反映不同时间的要求，因此需要有时随时修改。可像编辑普通工作簿一样编辑模板文件。但是，打开模板文件和打开普通文件有一点区别

8、，否则打开的就不是模板文件本身而只是它的复制件。在这种情况下，不能对模板文件做任何修改。如果要编辑模板文件，必须打开模板文件本身，过程如下：选取“文件”“打开”命令，或单击常用工具栏的“打开”按钮；在“打开”对话框内，选取想要编辑的模板文件名；按住Shift键，单击“确定”按钮。因为在第3步按住了shift键，打开的就是模板文件本身。编辑后，再选“文件”“保存文件”命令，将修改后模板文件以同名存盘。2插入工作表模板有时，用户需要组装若干个不同类型的模板到工作簿中。把一个模板插入人当前工作簿之内，其步骤如下：移动鼠标指针至想要插入模板表其前的工作表标签，然后单击鼠标右键；选取“插入”命令，弹出“

9、插入”对话框（见图5-1-5），其中包括早先建立的模板文件“工业企业财务报表.XLT“；选取想要插入的模板表，此处为“工业企业财务报表.XLT；单击“确定”按或按回车键。.3模板格式设计模板格式设计包括表样格式设计、公式设计（和宏设计有关）以及显示形式设计三方面内容。表样格式设计是指按照实际报表的格式，在Excel上进行的报表式样的设计。图5-1-4所示的资产负债表就是工业企业常用的表样格式，关于报表格式设计将在第6章报表模板使用实例中进行详细介绍。公式设计（和宏设计有关）我们将在5.2节中进行介绍。此处主要介绍显示形式的设计。1格式设计的必要性由于Excel的数据格式定义比较复杂，有时难免遇

10、到一些问题。本节主要介绍一些常用的格式定义技巧。如果读者使用的是Office97版本的Excel，当然启动Excel，并在“文件”菜单下选择“新建”时，选择“电子表格模板”，您会看到许多电子模板，如图5-1-3所示。其中包括各种行业的会计报表，您可以选择一个适合本单位的会计报表。这种报表可能并不完全适合本单位，您可以对它进行修改，使之成为一个可用的报表模板。图5-1-4是工业企业财务报表模板中的资产负债表模板。2格式化数字当在Excel里往单元格输入一个数字时，这个数字不可能以输入时的数值形式出现在工作表里，例如某些尾零可能已经被遗漏了。Excel把所有的数字和日期都作为数字存储起来，在屏幕上

11、显示的数字或日期都是被数字格式化了的。Excele有许多定义好的数字和日期/时间格式。另外，用户可以设计自己的宣称格式，可以包括用户指定的字符和符号，指定想要的十进制精度，并能使用16种不同颜色中的任何一种。这些格式和颜色甚至能够根据单元格里的数值范围来进行改变。没有使用的、或者已经被清理的单元格具有常规数字格式，这意味着Excel用要能是最高的精度显示一个数字。如果这个数字太大或太小，就用科学格式来显示。例如：5.367E+0.5。如果一个数字或日期还是太大以致于不适合指定的格式，这个单元格就用#符号来填充。（1）了解格式化数字的潜在危险在屏幕上出现的格式化数字不可能具有计算中使用的数字相同

12、的值。这个差异造成显示的或打印的结果和手工计算的结果不一样。为建立用户自己的整个工作表，以便使显示的数字和那些用在计算里的数字匹配，选择“工具（T）”“选项（0）”命令，“重新计算”标签。选择“以显示值为准（P）”复选框（当选择“确定”按钮时，将会被警告：“数据将永远失去其精度”）。用户也可通过使用Excel中的R0UND0涵数来设置选定单元格的精度。例如，可以在E5单元格里使用公式“=R0UND（B5*C5，2）”，这个公式在求和前对相乘的值进行了舍入处理。在做前一步的计算前总是要舍入的。（2）使用Excel的自动数字格式存储在单元格里的数字、日期的时间都是纯粹的数字，没有经过格式化。然而，

13、Excel要考察输入的数字格式，以决定这个使用程序是否能够格式化一个单元格。例如把数字#12.95输入到一个使用默认的常规格式格里，那么Excel将格式化这个单元格为贷币使用。若输入到一个百分数（如15%）到一个使用常规格式的单元格里，那么虽然它在公式上是15，而在工作表里看到它却是15%。警告：如果一个单元格里填写有#字符，那么按当前的格式，它的列就没有足够的宽度来容纳装入的数字。为了解决这个问题，加宽列宽。如果对这个列的加宽引起了工作表其他地方的格式化问题，使用TEXT（）涵数来把这个数字改变成文字。数字或日期能够超过单元格的宽度，并可以有任何格式，包括自定义的格式。（3）设计自定义数字格

14、式用户可以为金融或科学试验任务设计自己表示数字的格式，并为编制数字目录、电话号码、各国货币等等创建各种不同的格式。无论何时，当需要用特殊的方法显示数字时，都可根据需要使用自定义的数字格式。附注：Excel包括了一些用于社会保险号数字和电话号码的特殊格式，如果这是想要创建的格式类型、就不必再创建自定义的格式了。需要做的事情仅仅是在分类列表中选定特殊项，然后选定所想要的格式。了解自定义数字格式创建一个定义数字格式是很容易的，但需要了解几个Excel用来定义一个数字码的符号。为了创建自己的数字格式，需要在数字标签里把这些符号输入“类型（T）”字框里去。创建的自定义格式有4个部分。就像下面所示的语法例

15、子：positiveformat;negativeformat;zeroformt;textformt请注意，每一部分都被一个分号所分开。第一部分指出一单元格里正数的格式，第二部分指出一单元里负数的格式，如此等等。使用的符号作为占位或格式指示符。请注意，0作为一个占位符，并且在没有任何数字被显示的位置上显示一个0。符号-”跟在一个正数格式后面，以保证在这个正数右边留下一个空格，它的宽度和右圆括号一样宽，负数用圆括号括起来。正数和负数都靠每列的右边对齐。为了了解格式的每一部分是如何工作的，考察下面的自定义格式例子：$#，#0-”；（$#，#0”；“Zero”它将显示一个以$#，#-”格式表示的正

16、数，以（$#，#0”格式表示的负数，而文字Zero表示一个0。例如：3550将以$3,550形式出现。-3550将以（$3,550”形式出现，并且0将以单词Zero形式出现。当创建一个自定义格式时能够使用的符号将在图5-1-6中介绍。创建自定义数字格式可在工作表的任何要方创建自定义数字格式，遵循下面的步骤：选定想要使用自定义数字格式的单元格；选择“格式（0）”“单元格（E）”命令；选定“数字”标签；在“分类（C）”的列表里选定“自定义”；如果已经存在的格式和想要创建的自定义格式差不多，从这个列表里选定这格式；在“类型（T”文字框里编辑这个自定义格式图案；选择“确定”按钮。在创建一个自定义数字格

17、式之后，在单元格里输入一个合适的数字，然后用正数、负数和零值测试这个自定义格式。可在工作表的任何单元格中重新使用定义的格式，这只需要通过选定自定义分类，流动到这个列表的底部，然后选定任何自己预定义格式就行。删除自定义格式为了删除一个自定义格式，遵循下面的步骤：选择“格式（0”“单元格（E）”命令；选定“数字”标签；在“分类（C）”列表里选定“自定义”，然后选定想要从这个列表里删除的格式；选择“删除（D）”按钮。提示：不能够删除内置格式。利用数字显示文字用户可以在自定义格式里的分号之间，通过把文字用双引号括起来，并把这些文字内容插到合适位置上的方法，在同一个单元格中用数字来显示文字。单元格里的数

18、字在计算时仍然作为一个数字来使用，但显示时是作为文字来显示。例如用户想要让一部分数字总是在P/N后面，并且在最后三个数字前面显示一个连字符，那么创建一个如下的自定义格式：“P/N”#-#；“ExcelUse”;”EnterNumber”用这个格式，其中的数字5768953将被显示成P/N5768-953;输入一个负数，将显示文字：UsrPositive;而输入一个零将产生文字：EnterNumber.（4）用颜色格式化数据彩色的文字或数字能够帮助用户发现数据项的差别或者认出标志数字（它们超出了某个范围）。单元格的颜色格式化和单元格数字或日期格式化一起进行。用户可在自定义的数字格式的合适部分用括

19、号把颜色名字括起来，以指出要选择的颜色。例如在文字格式位置里的颜色格式改变文字的颜色。并且，如果想要使正数的格式是蓝颜色的，而负数的颜色是红颜色的，使用下面的格式：BLUE$#，#0.00-;RED（$#,#0.00）用户可以使用八种命名了的颜色和任何一种自定义的颜色。用下面的颜色符号来指定颜色：BLACK（黑色）WHITE（白色）RED（红色）GREEN（绿色）BLUE（蓝色）YELLOW（黄色）MEAGENTA（紫红色）CYAN（青色）COLOR#（这里的#是一个在颜色调色板上的颜色编号，从0号到56号）用户通过选择工具（T）”“选项（O）命令和选定颜色”标签，就可看支Excel的颜色调色

20、板。在标准颜色都用数字进行了编号，顺序是从上往下，从左到右（最左上角落的颜色编号是1，最右下角的颜色编号是56）。（5）条件格式化在用户定制的格式化中使用condititonvalue格式符号时，可以对一个单元格进行格式化，以便数字用不同的格式可颜色出现，这依赖于数字的值。若将这个技术用在核查数据输入时出现的错误，来自分析的异常报告的执行程序信息系统方面告别有价值。例如：下面的格式使得在单元格里的所有数字使用0.00的数字格式。当数字大于可等于1，000时，数字就以黑色显示出来；当数字小于或等于500时，数字就以红色显示出来，对于在这两个数字之间的数字都以蓝色显示出来。black=10000.

21、00;read,=5000.00;blue0.00（6）隐藏零值把零值隐藏起来常常能使工作表更容易阅读。在Excel里，把零值隐藏起来有3种选项：在整个工作表里都把零值隐藏起来；创建一个自定义格式；或者使用IF（）函数。为了在整个工作表里把零值隐藏起来，选择“工具（T）”“选项（Q）”命令，选定“视图”标签，然后清除“零值（Z）”复选框。当想要又看零值时，再选定“零值（Z）”复选框。为了使用自定义格式来把零值隐藏起来，在合适的部分使用分号以指出后面跟着零值格式，但并不为零数字输入一个格式，就像下面的格式一样：$#，#-）；（$#，#）；在公式中，使用IF（）函数来隐藏一个零值，像下面的例子一样

22、：=IF（A12+B12=0，“”，A12+B12）这个公式指出：如果A12+B12等于零时，Excel将显示引号之间的东西，上例中什么也不显示（要注意，。如果。如果A12+B12。如果A12+B12若使用一个空格将指示一个零；在有些数据库或在某些数字和文字函数里，空格会产生问题）不等于零，Excel。如果A12+B126）关于数字格式化的提示用户可以使用一个逗号格式（例如：#，#0）把要显示的数字三位三位地分开，这对显示那些具有千位或百位的数字就很有用，例如：用$#,#0,“M（这里的逗号结束格式数字部）这样的格式来对数字12345689进行格式出来的结果为：$123，457M请注意，显示的

23、数据被四舍五入了，而不是被截短了，紧接着的计算继续使用单元格里的实际数字，并不是显示出来的那个截短的数字。TEXT（）函数去把这个数字或公式的结当用户需要把一个数字安放在一个宽度较窄的列里时，使用TEXT（）函数去把这个数字或公式的结#符号来显示。用TEXT（）函数把数字TEXT（）函数里使用的数字公式可以是前面以至于没有安放在窄的单元格里,并且它需要一个A12*C35的公式。#符号来显示。用TEXT（）函数把数字TEXT（）函数里使用的数字公式可以是前面以至于没有安放在窄的单元格里,并且它需要一个A12*C35的公式。果转换成方案形式,这个数字就能覆盖单元格边沿而不必转换成用转换成还能够在其

24、他公式中被引用,并将正确地进行计算。在介绍的自定义数字格式之一。例如A12*C35的结果太大,货币格式,这时就可以在同一个单元格里使用己经使用过的：EXT（A12*C35，“$#符号来显示。用TEXT（）函数把数字TEXT（）函数里使用的数字公式可以是前面以至于没有安放在窄的单元格里,并且它需要一个A12*C35的公式。疑难解答问题：在格式化之后,较长的数字不再适合放在单元格里,这个数字太宽,并且显示出来是#,而不再是数字。回答：把列加宽，一直加到这个数字出现为止。也可以使用TEXT（）函数把这个数字或公式的结果转换成文字形式,这样这个文字就能覆盖单元格边沿。这个转换成文字的数字还能够在其他公

25、式中被引用,并且可以为一个数字进行计算。用户可以通过调整工作表里的列宽的行高以改善工作外观。合适的调整有助于在一页里放下更多的数据，甚至可以在一行或一列里隐藏保密的数据。关于调整列宽、隐藏列，调整行高、隐藏列，调整行，添加颜色、图案和边框知识请参看第1章Excel基础知识和基本支巧。52宏（VBA）及其在财会管理中的使用在实际工作中，常会做一些财务管理分析模型。而每个财务管理模型都分散地放在相应工作簿的要作表中，财务管理人员需要使用该模型进行分析时，必须选择包含模型的工作簿所在的目录，选择目录中的文件（工作簿名字）打开工作簿，再选择具体管理模型据的工作表等，即每次使用模型都必须重复繁琐的操作步

26、骤。为了减少不必要的操作，以及让更多的不太熟悉Excel操作的财务管理人员都能非常方便地使用已经设计好的模型，就需要就用VisualBasicForApplication（简称VBA）宏技术，将设计好的模型组合起来，建立一个面向财务管理人员的财务管理系统。本节主要学习记录和编写宏和编写宏程序，并用宏设计用户界面、系统菜单，建立一个完整的财务管理系统的技术的方法。宏语言概述Excel支持VBA，即MicrosoftsVisualBasic,ApplicationEdition,是从流行的VisualBasic编程语言中派生出的一种语言。Excel提供了其他工作所不能提供的一些更广和特性。然而无论

27、Excel的特性有多广，在特殊行业和特殊情况下用户一定全要求更多的特性来满足其特别的需要。对会记录或书写宏的任何人，都可以添加特性、函数和命令到Excel,让Excel按需要工作。Excel可以广泛地使用于财务、金融、经济、统计和审计等众多领域，它是一个强有力的信息分析和处理工具。这种力量的源泉之一就是它的宏语言（VBA）。利用职权VBA所提供的功能，财务管理人员可以按自动方式行日常的管理、分析、决策任务，还可按需要加入某些自定义功能，直至建立一个完整的财务管理使用系统。1建立宏程序VBA是一种计算机编程语言（第四代语言），用它提供的语句、命令可以编写包含若干指令序列的宏程序，它可以指挥Exc

28、el应该进行哪些工作。（1）宏程序的概念宏程序是指用VBA提供的各种函数、语句、对象、方法和属性等编写的程序。在Excel中也称为过程。过程是在VBA模块中一个可执行的VBA程序代码块，过程有程序代码序列组成，这些代码序列组合在一起可以完成某项任务。VBA中的过程主要可分为两类子过程（SubProcedure）和过程函数（FunctionProcedure）。子过程子过程以SUB语句开始，以ENDSUB语句结束。过程可以执行某种操作，但无返回值。其结构是：SUB过程名（）命令序列1命令序列2ENDSUB过程函数过程函数以FUNCTION语句开头，以ENDFUNCTION语句结束，并可以有返回值

29、。其结构是：FUNCTION函数名胜古迹（参数）命令序列1命令序列NENDFUNCION（2）建立宏程序的方法宏程序存放在Excel工作簿的宏表中，建立宏程序的步骤：进入“工具”“宏”下的“VisualBasic编辑器”，选择“插入”菜单中“添加模块”命令，Excel就会在工作簿中增加一张宏表，并命名为MODULEI，在宏表中建立友程序；或选择“工具”菜单中“宏”下的“记录宏”命令，Excel就会出现“停止录制”图标，用户可以对工作簿、工作表、单元进行各种Excel自动根据操作编制宏程序；当操作完毕时，按停止录制图标，完成一个宏表的制作。同时Exxcel在工作簿中也会增加一张宏表，并命名为MO

30、DULE1。2宏程序结构和宏程序举例宏程序是程序语言，所以它也有第三代语言一样的程序结构。.顺序（SEPUENTAL）；循环（LOOP）；条件（Condition）或Excel分支（Branching）。（1）顺序结构在正常状况下，宏程序的执行是以“Excel顺序“方式进行的，即由上而下逐一执行。用“Excel宏记录器”记录经常性工作时，它也是“Excel”顺序方式。例一：编写一宏程序。过程名为：PROL1功能：xj10.XLS工作簿中的“ExcelSALE”工作中，B1单元赋一报表的标题“恒远公司财务分析”，C2单元赋日期“1998年8月”。编程过程：打开工作簿xj10.XLS；进入“工具”

31、“宏”下的“VisualBasic编辑器“；选择“插入菜单中“宏表”命令下的“模块表”命令，Excel就会在工作簿中增加一张宏表，并自动命名MODEL1；选择MODULE1宏表输入宏程序：SUBPROL1()SHEETS(“ExcelSALE”).SEKECTRANGE(“ExcelBI”).DORMUL“A=Excel恒远财务分析”RANGE(“ExcelC7)”.FORMULARICI=“Excel1998年8月”ENDSUB例二：编写一宏程序函数名为：tax功能：在CWGL10.XLS工人作簿中的MODULEL宏表中编写一函数，即根据销售额（SALES）计算销项税（TAX）的函数。编写过

32、程：选择MODULEI宏表输入宏程序：Functiontax(sales)Tax=sales*0.17EndFunction例三：编写一宏程序。过程名为：PROL2功能：在CWGL10.XLS工作簿中的“FX”工作表中按顺序分别给单元E1、C1、D1赋值10、20、30。编写过程：选择MODULEI宏表输入宏程序：SubPROL20Range(“ExcelB1”).value=10Range(“ExcelC1”).value=20Range(“ExcelD1”).value=30EmdSub(2)分支结构优势需要对默认条件测试，然后根据测试的结果进行不同的操作，这就要使用分支结构。语法1:IF

33、(condtiion)Then(statements)注意：在单行的IFThen语句中不使用Endif语句。当测试的条件为TRUE时需要执行多行程序代码，则必须使用IFTHENENDIF语句。例四：编写一宏程序。过程名为：PROL3功能：如果“总销售额”大于等于50万元，在屏幕上输出“完成任务，应给予嘉奖！”。选择MODULE1宏表输入宏程序：SUBPROL3()IF总销售额=50THENMSGBKX“完成任务，应给予嘉奖！”ENDSUB语法2：IF(CONDITION)THEN(STATEMENTS)ELSE(STATEMENTS)ENDIF例五：编写一宏程序。过程名为：PROL4功能：如果

34、“总销售额”在于等50万元，在屏幕上输出“完成任务，应给予嘉奖！”，否则在屏幕上输出“继续努力”。编程过程：选择MODULEI宏表输入宏程序：SUBPROL4()IF总销售额=50THENMSGBOX“完成任务，应给予嘉奖！”ELSEMSGBOX“继续努力”ENDIFENDSUB语法3：SELECTCASE(TESTEXPRESSION)CASE(PRESSIONLST1)(STAEMENTS)CASE(PRESSIONLST2)(STAEMENTS)ENDSELECT例六：编写一宏程序。过程名为：PROL5功能：根据实际销售量判断其是大于、等于、小于保本点销售量，决定输出“盈利”、“保本”、

35、“亏损”。假设：B2单元为实际销售量，并将该单元名定义为销售量，C1单元为保本点销售量，并将该单元名定位保本点销售量。选择MODULE1宏表输入宏程序：SUBPROL5()SELECTCASE销售额CASE销售额=0THENMSGBOX“盈利！”ELSEMSGBOX“亏损！”ENDIFEXITENDSUB语法2：FOREACH（ELEMENT）IN（GROUP）（STAEMENTS）NEXT（ELEMENT）例九：编写一宏程序过程名为：PROL8功能：关闭所有打开的工作簿。编程过程：选择MODULE1HONG宏表输入宏程序：SUBPROL8（）FOREACHBOOKINWORKBOOKS（）B

36、OOK.CLOSEENDSUB以上是利用VB（VisualBasic）编辑器来编写程序，来完成某段特定的过程。使用这种方法的人需要具有一定的计算基础；Excel还提供使用者一种简单易用的编制宏程序的方法，即宏记录器。宏记录器的使用Excel提供的宏记录器可以帮助财务管理人员所做的各种操作，当操作结束时，关闭宏记录器，宏程序便保存在宏表中。宏记录下来之后，可以通过以下方法执行宏：用“工具”菜单中选择“宏”命令；将客观存在指给某个菜单项，通过选择菜单执行宏；将它指定给宏按钮，通过驱动按钮执行宏。（1）记录宏的方法和步骤选择“工具”菜单上的“记录宏”命令下的“录制新宏”命令；在“宏名字”框中输入宏程

37、序的名字，工程项名可以包括字母、数字和下划线；但必须以字母（某种文字）开头，名字中不能包括空格或标点符号；在“描述”框中，输入和要记录的宏有关的说明信息；选择“确定”按钮，此时停止记录宏按钮出现在屏幕上下；财务管理人员可以进行各种记录的操作，如选定单元格、输入标题、设置表格等；单击“停止录制宏”按结束宏记录。（2）记录宏实例例十：在工作簿CWGL10.XLS中，插入一张工作表，将该工作表名字改为“恒远财务管理系统界面”在C3单元输入标题“恒远财务管理系统”，将该标题的字号设置为14。并在该标题下划线，取消网格线。记录宏的过程：选择“工具”菜单上的“记录宏”俱下的“录制新宏”命令；在“宏名字”框

38、中输入宏程序的名字“infohy”在“描述”框中，输入和要记录的宏有关的说明信息；选择“确定”按钮，此时“停止记录宏”按钮出现在屏幕上；选择“插入”菜单上的“工作表”命令；将新插入的工作表名改为“恒远财务管理系统界面”；选择C3单元，输入标题“恒远财务管理系统”；选择标题，单击“字号”工具，选择14；单击“下划线”工具；选择“工具”菜单上的“选项”命令，去除网格线；单击“停止记录宏”按钮，结束宏记录。（3）记录的宏程序清单infohy宏丁力记录宏观08/08/1998Subinfohy（）Sheets.AddSheets(“SHEET1”).SelectSheets(“sheetl”).Nam

39、远财务管理系统界面”Range(“C3”).SelectActivecell.FormualaR1C1=“恒远财务管理系统”Range(cC3:D3).SelectWithSelection.FontName=”TimesNewRoman”FontStye=”Regular”Size=14EndWithSelection.Font.Underline=xLSingeActiveWindow.DisplayGridines=FalseEndSud通过上述大量的宏程序可以看出，对于学习过其他编程语言的财务管理人员来说，编写宏程序并不困难，只需要学习和掌握VBA提供的各种语句、对象的方法和属性等，就

40、可以很快编出宏程序。对于没有学习过其他编程语言的财务管理人员或对宏语言不熟悉的财务管理人员，可以利用Excel提供的宏记录器自动记录您在Excel中进行的各种操作，也可以通过阅读这些宏程序，不断学习宏的编写方法。值得注意的是，宏记录器编写的程序都是较简单的顺序结构的程序，如果需要比较复杂的结构如分支、循环等结构的程序时，财务管理人员对宏记录器中记录和程序进行修改便可使用。宏的执行宏记录下来之后，可在任何时间执行这个宏，Excel将执行在宏程序中的全部命令。当宏没有被指定到宏按钮或菜单项时，只能按下列步骤执行。在“工具”菜单中选择“宏”命令；在“宏名字”框中选择宏名字“infohy;”选择“执行

41、”按钮。利用宏设计自定义财务函数在实际工作中，人们经常利用宏设计自定义财务函数。取数函娄数就是定义的一种。关于自定义函数的内容不是本章的重点，当读者阅读到这部分内容以后，知道哪此函数是自定义函数就可以了。用户界面的设计和使用在实际工作中，我们会设计若干个财务分析模型，并将同一类模型存放在一个工作簿中，该工作簿的若干张工作表中存放着该类不同问题的模型。如果每个工作簿中实际是一个用户界面，就能使财务管理人员通过该界面一目了然了解到该工作簿中包含哪此模型，并通过驱动办界面上宏按钮直接进入各了模型,完成各种管理、分析、决策工作.给图工具栏选择“视窗”菜单上的“工具栏”命令的“绘图”工具栏，如图5-2-

42、2所示。通过使用绘图工具栏上的各种工具，可以绘制出包括宏按钮、文字框以及正方形等精美的用户界面。下面对工具栏中的各工具进行介绍。绘图选项工具；自选图形：自动创建各种步同的图；直线工具：画直线；箭头工具：画箭头；矩形工具：画矩形；圆形工具：画圆及椭圆；文字框：在任何图形对象中添加工具(文字水平徘列)；竖形文字框：在任何对象中添加工具(文字垂直排列)；艺术字工具：提供各种艺术字体选项；填充色工具：修改图形对象中的填充色；线条颜色工具：修改图形对象中的线条颜色；字体颜色工具：改变字体颜色；(15)线条修改工具：改变线条颜色；阴影工具：为图形及字体设置阴影；三维设计：设计图形的三维效果。建立用户界面以

43、存在的cw105.xls工作簿为例，说明用户界面的设计方法。图5-2-3所示的是财务报表分析模型用户界面实例。(1)在CWGL05.XLSC作簿中插入一个工作表选择“插入”菜单上的“工作表”命令，在CWGL05.XLSE作簿中产生一新表；将新表名改为“财务分析界面”；在该表输入标题、绘制图形。(2)在用户界面上绘制“宏按钮”单击绘图工具下的工具按钮；选择放置按钮的位置，拖动鼠标至所需的尺寸；选中所绘制的工具按钮，点击鼠标至右键，选择指定宏；进入指定宏界面，在“宏名”框中选择录制好的宏程序RATE(CWGL05.XLSE作簿中存放该宏程序)，选择“确定”按钮，即将宏程序指定给宏按钮；或在“宏名字

44、/引用”框中不输入宏名字，并选择“取消”按钮，即不选择宏程序，等以后再指定；输入宏按钮的名字(如比率分析)，单击工作表的任意单元。(3)编辑图形对象当在工作表中建立包括各种图形对象(圆、矩形、按钮等)的用户界面后，有需要对其进行移动、缩放、复制、删除、修改、改变颜色等操作。此时，只需选择该对象，然后用简洁菜单中的命令，便可对其进行各种编辑。编辑方法：将鼠标指针移动某对象；单击鼠标右键，对象被选中并弹出简洁菜单，如图5-2-4所示。使用“指定宏”命令可以将该对象和宏程序相连接。通过该命令也可以将宏按钮定给某一宏程序。不选择菜单中的命令，而用鼠标单击该对象，此时可以用鼠标拖动该对象到任意位置，或改

45、变该对象的尺寸。用户界面的使用从图5-2-3可以看出，财务报表分析模型中包括比例分析、趋势分析、杜邦分析和即出模型四个按钮，这四个按钮分别指定给四个宏程序。因此，财务管理人员想进入模块分析了模型(如比率分析等)进行分析工作只需驱动宏观按钮。例十一：选择“比率分析模型”的rate宏程序如下：Subrate（）Sheets（“比率分析”）.SelectEndSub将该宏程序指定给宏按钮比率分析。现在财务人员进行以下操作：单击“比率分析”宏按钮，出现比率分析模型，如图5-2-5所示，该模型中一个宏按钮对应着一宏程序，单击模型中不同的宏按钮便可得到相应的比率分析结果；当比例分析工作结束后，单击“返回”

46、宏按钮，则自动返回到恒远财务报表分析主界面；同理可经单击财务报表分析界面上的“趋势分析”、“杜邦分析”按钮，又可进行相应分析；当财务报表分析工作结束时，选择“文件”菜单“关闭”俱，则自动关闭该工作簿。前两小节讨论了友语言的宏程序的编制，包含宏按钮在用户界面的建立，以及将宏程序指定给宏按钮的方法。现在我们可以返回去，对前边设计和建立和各种模型改进，即尽可能地在每张工作模型中建立完成某种任务的宏程序（如流动比率分析宏程序、编制报表宏程序、数据采集宏程序等等），建立宏按钮，并将其余相应的宏程序连接；在每个工作簿中都有建立相应的用户界面（财务报表分析模型界面、投资决策模型界面等）。这样，财务管理人员就

47、可通过驱动各类模型界面上的宏按钮进行该工作簿中的各种分析模型，在每个工作表分析模型中又通过驱动宏按钮完成各种管理、分析、决策工作。宏技术的使用可以减少重复工作量，减少出错的可能性。财务管理人员使用宏控制工作表完成各种操作，就像使用遥控器一样简单、直观、安全。菜单的设计方法在实际工作中我们会建立多个包含各种管理、分析、决策的模型，并分别存放在多个工作簿中。管理人员使用每类模型，还必须知道该类模型所在工作簿的名字（如CWGL05.XLS工作簿名字是投资决策模型所在的工作簿的名字），根据名字打开相应的工作簿。如果将这些模型汇总到菜单上，财务管理人员可以清楚地了解财务系统结构，并通过选择菜单上的命令，

48、自动打开包含某类模型的工作簿，进行管理、分析、决策工作。1菜单管理器建立财务管理系统菜单要通过菜单管理器完成。菜单管理器是Excel提供给用户的一个工具，它可以帮助用户建立自己的菜单，并对这些菜单进行修改以满足自己的需要。通过使用菜单管理器，用户可在菜单栏中加入一个菜单，并在菜单中加入菜单命令；用户可以根据需要将某个宏程序指定给菜单命令，当菜单命令被选择时，招待时，执行相应的宏程序。（1）Excel菜单的构成在用户建立自己的菜单前，先了解Excel的菜单结构，内部菜单包含菜单栏、菜单、菜单项、子菜单等项。用户可以通过鼠标选择菜单中的命令，也可以用快捷键选择菜单中的命令。（2）菜单编辑器的使用方

49、法用户可以使用菜单编辑器或编写VBA程序修改内部菜单。有关用VBA个性菜单的内容略，在此主要讨论在工作簿中生成菜单系统的方法：打开工作簿（如CWGL10.XLS）；选择“工具”菜单上的“自定义”命令，在“自定义”窗口中定义用户希望增加的菜单（自定义界面如图5-2-6自定义菜单）。2在菜单栏中增加一外“财务管理”菜单打开工作簿（如CWGL10.XLS）。建立宏表。如果工作簿中有宏表，则选择表；如果没有则建立宏表及宏程序。点VB编辑器，进入“模块表”编辑状态，建立若干个宏程序。CWFX宏：打开CWGL05.XLS工作簿，进入财务报表分析模块。TZJC宏：打开CWGL05.XLS工作簿，进入投资决策

50、分析模块。LDZJ宏：打开CWGL06.XLS工作簿，进入流动资金分析模块。CZSY宏：打开CWGL07.XLS工作簿，进入筹资分析模块。XSLR宏：打开CWGL08.XLS工作簿，进入销售和利润分析。CWJH宏：打开CWGL09.XLS工作簿，进入财务计划模块。其中以CWFX宏举例如下：Subanalusis（）Workbooks.OpenFineame:=”cwcg:l05.xxls”,Updatelinks:=0Sheets（Excel财务报表分析界面）.SelectSudSub选择“菜单编辑器”命令选择“工具”菜单上的“自定义”命令，选择“命令”标签进入菜单编辑状态（如图5-2-6）建

51、立菜单“财务管理“。在图5-2-6中，在标签“命令“中选择新菜单，用鼠标拖动新菜单到菜单行，“更改所选内容”，把“新菜单：更名为“财务管理”，如图5-2-7所示。到此为止，我们建立了一个完整的财务管理系统，其功能结构图如图5-2-8所示。当打开CWGL10.XLS工作簿，菜单栏就有“财务管理”菜单；选择“财务管理”菜单，便弹出下拉菜单。它将财务管理系统功能结构图中和主要功能模块以命令的形式显示在下拉菜单上。财务管理人员可以选择任意命令，相当于选择了结构图中的第一层功能模块。该功能模块包含模型的用户界面展示在屏幕上，单击用户界面上的宏按钮，相当于选择了结构图中第三层功能模块。此时具体的管理、分析

52、、决策模型展示在财务管理人员面前，可以进行各种分析工作。控制使用系统自动启动的方法1建立自动启动宏程序的方法当打开和关闭某一个工作簿时，希望自动执行该工作簿中的某个宏程序或过程，如打开CWGL10.XLS工作簿时，希望自动执行一个宏程序，显示财务管理系统主画面，关闭工作簿时需要自动执行一个宏程序，显示“谢谢使用恒远财务管理系统，再见！”这就需要建立名为Auto-Open或Auto-Close的宏程序。区别自动宏程序于一般宏程序是通过宏过程名来识别的。（1）建立一个工作簿打开时自动招待的宏程序例十二：在CWGL10.XLS工作簿中建立一个自执行的宏程序，其功能是显示“欢迎使用恒远财务管理系统”，

53、自动显示财务管理系统主界面。选择宏表：建立如下程序：SubAuto-Open（）Msgbox（“欢迎您使用恒远财务管理系统”）Sheet恒远财务管理系统界面”）.SelectEndSud当打开CWGL10.XLS工作簿时，屏幕显示如图5-2-9所示。欢迎您使用恒远财务管理系统确定单击“确定”按钮，便显示财务管理系统主画面，如图5-2-3所示。此时，财务管理人员可以选择图5-2-7上的“财务管理”菜单，进入各模型。（2）建立一个关闭工作簿时自动执行的宏程序例十三：在CWGL10.XLS工作簿中建立一个当工作簿关闭时自动执行的宏程序，其功能是显示“谢谢使用财务管理系统，再见！”选择宏表，建立如下程

54、序：SubAuto-Close（）MsgBox（“谢谢使用恒远财务管理系统，再见！”）EndSub当关闭CWGL10.XLS工作簿时，单击“确定”按钮结束。2利用启动目录自动打开工作簿希望启动Excel后，自动打开某个工作簿，则可利用启动目录实现该功能，Excel将启动目录定义为C：ExcelXLSTSRT。制作自动打开工作簿的方法：将需要自动打开的工作簿移动或复制到启动目录。5.3动态数据分析及数据透视表的使用数据动态分析的必要性如今“Excel分析”一词的使用频率是很高的，市场分析、金融分析、财务分析不绝于耳。所谓分析就是让您劈开迷雾，看清庐山真面目，透过现象，看到事物的本质。早期数据分析

55、都需要依靠数据库技术，在有关数据库语言支持下进行操作。实际上，Excel软件本身具有数据库技术的基本功能。当我们在Excel软件里建立了有关工作簿以后，在它下属的各张工作表内自然已经保存有关的数据内容。人们使用Excel软件建立各种工作表，其目的是从中提炼出有用的信息，为决策提供依据。通常，工作表内的数据清单是一般的二维表，虽然它也能在一定程度上反映一些数据的特征，但是，有时候却难以满足一些特殊要求。譬如说，对于如图5-3-1所示的一张某公司的应收账款数据清单，可以很容易地从工作珍内直接得到“各外公司月初结存、月末结存”之类的信息，倘若希望马上回答该信息数据的历史比较或单位（部门）间比较结果，

56、恐怕就不是很容易的了。读者可以通过前述Excel软件使用基础介绍，建立起适合自己工作需要的具有数据管理功能的工伯表，也可通过前述Excel函数调用，在工作表内生成自己工作需要的信息。通过本节介绍的“模板”和“宏”这两种工具的使用，建立起定制自己工作界面、设计自己工作菜单、完成适合于自己工作习惯的，而且可以不断重复使用的工作簿和工作表。正如图5-3-1实例所述，仅依靠前面介绍的那技术是无法马上求得纵向或模横向比较信息的，更无法马上显示环境条件变化的实际结果。为了帮助作废探讨数据内部奥秘，Excel软件提供一个强有力的数据分析工具数据透视表。数据透视表是一种为表现多种事务内在联系而建立的动态数据分

57、析表，利用它您可以像x光透视那样来观察普通二维电子表格呼数据；通过动态变换行列结构，可以看至少同组合的效果，得出不同的汇意报表。数据的动态分析可以在工作表内生成实时信息。要使工作表能产生动态实时信息，除了使用数据透视表工具外，还有必要补充使用一些在Excel软件内已经提供的某些技巧。有关数据动态化的某些技巧1把工作表视为数据库Excel软件内的工作具表并不能完全替代数据库的功能，但在简单的数据信息管理条件下，工作表的结构和Excel软件的某些操作是可以胜任数据库管理的基本要求的。（1）在工作表内创建数据库只要在使用工作表时把工作表的行看为记录，把工作表的列看作为字段。在Excel软件内不需要键

58、入任何命令，就可以创建数据库了。数据库的字段通常要求有字段名，Excel软件工作表内顶项（即报表有效工作区域里的第一项）所填写的字符也就是字段名。写段宽度在Excel软件工作表内由列宽来决定。（2）实现数据库的基本操作在数据库的管理技术中，数据库的基本操作包括：数据输入和数据记录的增、删、改，数据排序和数据的筛选，数据的运算操作和输出。在Excel软件内只要创建了数据。也就是说，在工作表里填写过报表的栏名（或写入过一条或两条记录）后，在“数据”菜单下用鼠标单击“记录单”，就可以在操作窗口生成一个数据输入操作环境，如图5-3-2所示。当然，也可以在工作表内通过直接填写来完成数据输入操作，这是常规

59、的Excel软件基本操作。而数据记录的增、删、改，更应该常规操作方法一致。数据排序和数据的筛选，它是在“数据”菜单下用鼠标单击“排序（S）”或“筛选（F）”而产生如图5-3-3形式。数据排序是数据库操作中常用的管理技巧，它可以使数据记录按要求条件排列，便于查询和浏览。Excel软件中数据排序操作可以完全满足数据库管理需要。先选定要求排序的字段，再选定要求排序的条件。简单的条件不外于升序或降序，但也可以自定义种种条件，甚至可以令数据库籽段按求排序。排序的字段可以不止一个，操作界面允许选定不超过三个字段，但也可以执行多字段的排序操作。由于“排序”菜单后面附有“”标记，有关选定排序条件的对话框，可以

60、在双击菜单后自动出现。数据筛选是实现数据动态化的关键性操作技巧。如5-3-3所示，当操作者选定了“筛选”条目后，可以执行“自动筛选”和“高级筛选”两种不同操作；图内变灰了那个“全部显示”条目，仅当执行了有关筛选操作后才能动作，实际上它不过是承担撤消前设筛选操作的功能。自筛选可使被分析的那个工作表标题栏右边都加上一个操作按钮。鼠标单击此按钮时，屏幕上出现一个下拉式菜单。菜单内罗列了该标题栏下的不重复的全部记录条目，供操作者作为筛选条件予以使用。同时，菜单还提示一个“自定义”的条目。鼠标单击“自定义”后，屏幕推出图5-3-4所示的操作窗口，可以在选定的第一季度字段里加入有关自定义的筛选条件，如：等