浙江财经大学东方学院实验报告

Excel在财务管理中的实际运用

               　　　　　　　　　　　　

学生姓名　　朱怡　　     指导教师     许诺

分   院　　会计分院　　　专业名称     12ACA

班　　级　 12ACA2班　　  学　　号   1220100219

       

20##年6月18 日

Excel在财务管理中的实际运用

摘 要：随着经济环境的变化以及经济改革的不断深化,企业对会计提出了更高的要求,即由核算型变为管理型或决策支持型,要实现这种转变,就要求财务管理人员能够利用有关工具对复杂的经营问题,进行预测、决策和分析。Excel软件隐藏着强大的运算、分析、处理表格和数据等功能,通过掌握公式和函数的综合使用、外部数据的处理、数据的假设分析、财务预测等等及它们的使用技巧，使得实务工作中的财务人员在面对纷繁复杂的数据时，只需要几个按钮和几个键，就可以很方便的进行信息整合、统计分析和预测分析等，让各种财务管理与分析中的实际问题变得方便和简单。

关键词：财务管理；工具；信息整合；预测分析

一、  引言

    财务管理(Financial Management)是在一定的整体目标下，关于资产的购置（投资），资本的融通（筹资）和经营中现金流量（营运资金），以及利润分配的管理。也就是说，财务管理是组织企业财务活动，处理财务关系的一项经济管理工作。

Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，是由Microsoft为Windows和Apple Macintosh操作系统的电脑而编写和运行的一款试算表软件。

由于 excel软件具有智能化的计算和数据管理能力，可以从各个维度、各个层次进行数据的计算、分析管理、组织，生成图表，图形及其他材料，并且与各种文字处理软件、图形处理软件、数据库管理软件共享数据等优势功能，所以在实际过程中将excel运用到财务管理工作上，以至于更好地利用复杂繁多的财务管理相关数据，从而简化工作人员的工作。

二、  Excel在财务管理中的运用

（一）利用Excel进行本量利分析

本量利分析（简称为CVP分析)是成本—业务量—利润关系分析的简称，是指在变动成本计算模式的基础上，以数学化的会计模型与图式来揭示固定成本、变动成本、销售量、单价、销售额、利润等变量之间的内在规律性联系，为会计预测、决策和规划提供必要的财务信息的一种定量分析方法[①]。本量利关系的基本公式为：

营业利润=销售收入－总成本

         =销售收入－变动成本－固定成本

         =单价*销售量-单位变动成本*销售量-固定成本

         =（单价-单位变动成本）*销售量-固定成本

了解了本量利分析的基本原理，下面就利用Excel进行几个具体的本量利分析。

1．单一产品保本量和保利量分析

PLM公司为生产座板的小型公司。该公司的负责人对市场进行调查后得出下列信息，见表1。要求：（1）计算每只座板的单位变动成本。（2）计算座板的固定成本总额。（3）根据单价和单位变动成本计算单位贡献边际（4）保本时的作业量。（5）实现目标利润的销售量。

   表1                          公司产品资料表                           单位：元

第一步：点击桌面Excel快捷方式，打开一个工作簿，将工作表“sheet l”重命名为“单一产品保本量和保利量”。

第二步：在工作表中设计表格，根据实务工作要求，通过编辑栏中输入公式：

单位变动成本=直接材料+直接人工+变动性制造费用+变动性销售及管理费用

固定成本=固定性制造费用+固定性销售费用和管理费用

保本时的作业量=固定成本÷单位贡献边际

实现目标利润的销售量=（固定成本+目标利润）÷单位贡献边际

在Excel公式显示为B11 =SUM(B2:B5)，B12 =B7+B8，B13 =B9-B11，B14 =B12/B13，B15 =(B10+B12)/B13。由于B14和B15为数量（只），不应当出现小数，故点击鼠标右键，将单元格格式设置为“数值”，小数位数设置为零。将上述资料录入即可得到预测结果，如图1-1所示，将文件进行保存。

图1-1  单一产品保本量和保利量

2．动态盈亏平衡分析

QAZ公司生产一种产品H，该产品每件售价为60元。该产品的成本构成如下：直接材料每件20元，直接人工每件4元，变动性制造费用每件3元，变动性销售及管理费用每件4元。企业每年发生固定性成本为30000元，假设该产品目前的最高生产能力为6000件。要求：（1）建立盈亏平衡分析模型。（2）对本量利关系进行动态展示。

第一步：构建基本的线性本量利分析模型，见下图1-2。先在数据区输入已知的相关条件，在C12：C15中计算销售收入、贡献边际、总成本和利润指标，并设计结论性文字，如G3=C9/C8，F6=“当前销量为”&C11“，”&“利润为”&C15&“元”，F9=“当前售价=”&C2“元”，F12=“盈亏平衡点：”&ROUND（G3，0）&“件”。

图1-2  线性本量利分析模型

第二步：为绘制盈亏平衡动态图示做准备。

（1）  计算不同的销售量下的贡献边际、固定成本和利润，如表2所示

表2                             辅助绘图表                           单位：元

（2）列出盈亏平衡销售量的垂直绘图点，如表3所示

表3                             盈亏平衡点决策线表                    单位：元

（3）列出当前销售量下的垂直绘图点，如表4所示

表4                             当前销售量决策线表                     单位：元

第三步：绘制图形。选择菜单“插入——图表——XY散点图——无数据点平滑散点图”。点击“下一步”，首先添加名字为“贡献边际”的系列，其中X轴代表销售量，Y轴代表利润。然后依次添加固定成本、利润、盈亏平衡线、当前销售线的系列。添加完后点击“下一步”并设置数轴名称和单位及图形名称，形成盈亏平衡图。如图1-3所示。

第四步：添加控件以建立动态调整模型。打开菜单“视图——工具栏——窗体”，用鼠标单击微调器按钮，鼠标变为“+”字，在图形中画一个大小适中的微调器，然后设置微调器：右键单击微调器，选择“设置控件格式”设置控件，见图1-4。然后添加“当前售价=60元”的说明框。经过上一操作的设置建立了动态链接。只需按动控件的上下键，就可以看到数据区的售价、单位贡献边际、销售收益、贡献边际、利润以及盈亏平衡图在发生变化。最后用同样的方法在表格中添加当前销售量和利润、单位变动成本、固定成本的控件，完成后见图1-5。财务人员还可以并据此绘制利润敏感分析图。

图1-3  盈亏平衡图

图1-4  设置控件格式

图1-5  控件效果图

（二）利用Excel进行短期筹资决策

WER公司拟采购一批材料，供应商规定的付款条件如下：“2/10，1/20，N/30”，每年按360天计算。假设银行短期贷款利率为15%，计算放弃现金折扣的成本，并确定对公司最有利的付款日期，再者目前有一短期投资报酬率为40%，请确定对该公司最有利的付款日期。其中信用筹资公式为：

放弃现金折扣成本＝折扣百分比÷（1－折扣百分比）×360÷（信用期－折扣期）

其中，（信用期－折扣期）表示的是“延期付款天数”即“实际借款天数”。 在Excel中则利

用表格进行快速计算一笔或者多笔筹资。

第一步：利用公式在Excel中计算应付账款的资本成本，新建Excel表如图所2-1示，经计算后的结果如表5所示。

图2-1  信用筹资计算图

表5                             信用筹资结果表                    单位：百分比

由此财务人员可以做出决策：因为放弃现金折扣的成本都比短期货款利率高，所以选择向银行借款来享受现金折扣，其中最有利的是10天内付款。而短期投资的报酬率比放弃现金折扣的成本高，所以选择30天付款，可以用资金先去投资。

（三）利用Excel进行杜邦财务分析

杜邦分析法最先是由美国杜邦公司经理首创并成功运用的，利用Excel可把各种财务指标间的关系，绘制成简洁、明了的杜邦分析图。杜邦分析法以净资产收益率为核心指标，主要反映以下几个财务比率之间的关系：净资产收益率=总资产净利率×权益乘数，总资产净利率=主营业务净利率×总资产周转率，主营业务净利率=净利润/主营业务收入，总资产周转率=主营业务收入/总资产。据此财务人员可以用Excel制作杜邦财务分析体系图。

第一步：新建一个工作表，命名为“杜邦财务分析体系图，单击菜单栏中“工具——选项”，在弹出的“选项”对话框中，打开“视图”选项卡，取消“窗口选项”选项组中的“网格线”，确定后使得工作表变成看不到原来的网格线，显得更清晰、直观。

第二步：选中单元格，右键单击选择“设置单元格格式”，进行边框设置，形成的文本框用于输入文字数据。然后在菜单栏中单击“视图——工具栏——绘图”，利用弹出的“绘图”工具栏中的直线按钮和箭头按纽绘制工作表，使各部分关系更加清晰。

第三步：根据公司的资产负债表和利润表信息，输入营业收入、成本费用总额、流动资产和非流动资产各具体构成部分的数据，如表6所示。（以下数据均借用中粮生化20##年9月30日的资产负债表和利润表）

表6      中粮生化20##年9月30日的资产负债表和利润表部分数据       单位：万元

第四步：使单元格数据按照公式关系进行计算，这样当资产负债表和利润表变化时，只需要更改最下面的组成资产的各部分、组成收入总额的各部分和组成成本费用总额的各部分数值，其他的数值就会相应发生变化。最终效果如图3-1

图3-1  杜邦财务分析体系图

续图3-1  杜邦财务分析体系图

三、结束语

以上实务仅仅是运用Excel完成的一小部分，可以看出操作人员并不需要了解详尽复杂的数学处理原理和程序设计知识，只需掌握 Excel数据处理的方法，理解数据处理的结论，便可以在实际应用中解决具体问题，乃至给出决策建议。

总之，Excel是信息技术的发展给企业提供了强有力的竞争手段，企业财务人员应该加强对Excel的学习、掌握，简化财务人员工作量，节约企业成本，提高会计信息处理能力的准确性、时效性，把财务人员从机械的指标计算中“释放”出来，将更多的精力投入指标的分析当中，更好地去评价企业经营状况，以尽最大可能提高企业的经济效益和管理水平。

参考文献

[1]孙静，王斌：《Excel财务管理应用教学设计》，东北财经大学出版社，20##年10月。

[2]傅丹，姜毅：《财务管理实训教程》，东北财经大学出版社，20##年6月。

[3]Office Home：《办公室之王——财务管理必会Excel应用100例》，电脑报电子音像出版社，20##年7月。

[4]乔世震，王满：《财务管理基础》，《东北财经大学出版社》，20##年2月。

[5]杜艳丽：《Excel在货币时间价值中的综合运用》，《国际商务财会》，20##年，第4期。

[6]钟奎武：《Excel电子表格在财务管理中的应用》，《中国管理信息化》，20##年3月，第13卷第6期。

[7]何琼：《管理观察》，东北财经大学出版社，20##年10月。

[8]金美子，金波：《利用Excel进行财务分析》，《中国管理信息化》，20##年，第14期。

[9]张娇：《浅谈如何在财务管理中贯穿性使用Excel电子表格的处理》，《投资与合作》，20##年，第2期。

[10]刘立华：《EXCEL在财务管理课程群教学中的应用》，《中小企业管理与科技》，20##年，第36期。

---

[①]张娇：《投资与合作》，20##年，第2期。

第二篇：第五章Excel在财务管理中的高级应用

在财务管理中的高级应用

5.1 Excel模板简介

5.1.1 Excel摸板功能及其创建

Excel摸板是一种用来生成其他工作簿的格式文件。由一个摸板文件生成的文件，可包括与摸板文件特征相同的布局、文件、数据公式、设置、样式、名称、宏、工作表控制以及Visual Basic模块表每一个由模板文件生成的工作簿是模板的复制品。模板在财会管理、制作报表的数据录入、支出账项以及保证个分支部门预算规范的一致性方面是十分有用的。

Excel模板与普通工作簿在形式上是完全一样的，但是功能上有如下两个基本区别。

（1） 工作簿是一次应用的，模板是多次应用的。打开一个模板时仅打开模板的复制件而不是模板本

身。

（2） 模板文件的扩展名为.xlt

除此而外，工作簿模板与工作没有什么不同。

1 创建和存储工作簿模板

创建模板与创建工作簿文件完全一致。开始时，创建Excel的工作表、图表或其他文件，然后加人数据、格式的文件所需的其他信息，最后以模板文件类型存盘。

（1）创建一个工作簿模板的实质是在通常的工作表编辑环境下，设计好一个工作簿的表样。设计的工

作簿包含模板所需要的全部内容（如表格布局、数据、文本、格式、公式、以及控制等）。所谓创

建模板就是它存为模板形式，步骤如下：

① 选取Excel文件“Excel另存为“命令；

② 输入模板名称，按通常方式选取存放模板的文件目录；

③ 在Excel“另存为”对话框中选择保存类型为Excel“模板”，它自动将扩展名.xlt加到文件后，如

图5-1-1所示；

④ 单击Excel“保存”按钮。

注意：在Excel“文件名”文本框内，仅仅将文件扩展名改写成.xlt并不能将文件夹及模板格式存储，一定不能忽略上面的第4步。

（2）自动模板

要改变Excel的缺省、（默认）字体、格式、保护状况等工作属性，最好生成一个自动模板。例

如，可以生成一张工作簿的自动模板，它包括用户名字或当前日期作为脚注。如果将一个模板文件以名字BOOK存入目录C：\MYDOCUMENTS\HY下，该模板文件称为自动模板，它为用户所建的一切新工作簿赋予模板式样，实际上它控制所有新工作簿的样式和内容。生成自动模板的步骤如下：

① 打开或创建一个用所有新工作簿样式的工作簿，如必须的数据、公式以及所需要的格

式；

② 选取“文件”“另存为”命令；

③ 在“文件名”文本框键入Book，如图5-1-2所示；

④ 在“保存位置”处选取C:\MYDOCUMENTS\HY;

⑤ 从“保存类型“列表框内选取”模板“，它将扩展名.xlt加到文件后；

⑥ 单击“保存”按钮。

在生成自动模板后，每当打开一个新工作表（例如选取命令“文件”“新建”，或凌晨击常

用工具栏的“新建”按钮），新工作表自动命名为BOOK的模板样式。因为利用模板创建的

新文件都以模板名加一个整数的形式命名，所以模板名不宜过长，以不超过6个字符为宜。

5.1.2 维护和使用模板

创建、存储工作模板是为了使用模板。为了更好地使用模板，还应对模板进行一定的维护。

1 从模板创建工作表

打开一个模板将生成一个以模板为基础的新工作表文件，但模板本身并不改变，新文 件使用临时文件名。例如，槿板文件为“工业企业财务报表.XLT”，新文件名则为“工业财务报表1.XLS、工业企业财务报表2.XLS等。

根据模板创建的不同形式，从模板创建工作表的方式有所不同。但有一个共同点是，要用模板创建工作表，该模板一定不能以模板文件本身打开。

① 如果创建模板未存放在目录C:\MYDOCMENTS\HY下，则只能使用“文件”“打开”命

令，在“打开”对话框中直接选择模板文件打开。注意这时并非打开模板文件本身，而是一个与模板文件内容完全相同的工作表文件。

② 如果创建的模板存放在C:\MYDOCUMENTS\HY目录下，但名字不是BOOK.XLT

（即非自动模板P，而且在该上目录下没有自动模板文件，则除了用“文件”“打开”命令外，使用“文件”“新建”命令将打开一个“新文件”对话框，该对话框内显示所有C:\MYDOCMENTS\HY目录下模板，用户可从吕选择一个模板用于创建自己的工作簿文件。

③ 如果创建了一个自动模板，除了用“文件”“打开”命令外，也可以使用“文件”“新

建”命令。这时不打开“新文件”对话框，而是直接按自动模板创建新文件，如图5-1-3所示。

如上所述，虽然可以打开一个存于任何一个下模板用“文件夹”“打开”命令，但只 有存于目录C:\MYDOCUMENTS\HY下的模板才能出现在“新文件”对话框内供调用。如果建立闻自动模板，又要使用他模板文件时，也只有使用“文件”“打开”命令。

在图5-1-3中单击“确定”按钮，则会出现图5-1-4所示资产负债表模板，它是在“工业企业财务报表.xlt ”文件下的。

如果要用“工业企业财务报表.XLT的其他模板，另创建一张完全相同的工作表文件，直接用“文件”“打开”命令选中文件“工业企业财务报表.XLT”，按回车建。如果该模板文件存储在C:\MYDOCUMENTS\HY下，使用“文件”“新建”命令从新文件对话框内选择“工业企业财务报表.XLT”，按回车键。

2 编辑模板

一个模板应当反映不同时间的要求，因此需要有时随时修改。可像编辑普通工作簿一样编辑模板文件。但是，打开模板文件与打开普通文件有一点区别，否则打开的就不是模板文件本身而只是它的复制件。在这种情况下，不能对模板文件做任何修改。如果要编辑模板文件，必须打开模板文件本身，过程如下：

① 选取“文件”“打开”命令，或单击常用工具栏的“打开”按钮；

② 在“打开”对话框内，选取想要编辑的模板文件名；

③ 按住Shift键，单击“确定”按钮。

因为在第3步按住了shift键，打开的就是模板文件本身。编辑后，再选“文件”“保存文件”命令，将修改后模板文件以同名存盘。

2 插入工作表模板

有时，用户需要组装若干个不同类型的模板到工作簿中。把一个模板插入人当前工作簿之内，其步骤如下：

① 移动鼠标指针至想要插入模板表其前的工作表标签，然后单击鼠标右键；

② 选取“插入”命令，弹出“插入”对话框（见图5-1-5），其中包括早先建立的模板文件“工业企业财

务报表.XLT“；

③ 选取想要插入的模板表，此处为“工业企业财务报表.XLT；

④ 单击“确定”按或按回车键。

5.1.3模板格式设计

模板格式设计包括表样格式设计、公式设计（与宏设计有关）以及显示形式设计三方面内容。表样格式设计是指按照实际报表的格式，在Excel上进行的报表式样的设计。图5-1-4所示的资产负债表就是工业企业常用的表样格式，关于报表格式设计将在第6章报表模板应用实例中进行详细介绍。公式设计（与宏设计有关）我们将在5.2节中进行介绍。此处主要介绍显示形式的设计。

1格式设计的必要性

由于Excel的数据格式定义比较复杂，有时难免遇到一些问题。本节主要介绍一些常用的格式定义技巧。 如果读者使用的是Office 97版本的Excel，当然启动Excel，并在“文件”菜单下选择“新建”时，选择“电子表格模板”，您会看到许多电子模板，如图5-1-3所示。其中包括各种行业的会计报表，您可以选择一个适合本单位的会计报表。这种报表可能并不完全适合本单位，您可以对它进行修改，使之成为一个可用的报表模板。图5-1-4是工业企业财务报表模板中的资产负债表模板。

2 格式化数字

当在Excel里往单元格输入一个数字时，这个数字不可能以输入时的数值形式出现在工作表里，例如某些尾零可能已经被遗漏了。Excel把所有的数字和日期都作为数字存储起来，在屏幕上显示的数字或日期都是被数字格式化了的。

Excele有许多定义好的数字和日期/时间格式。另外，用户可以设计自己的宣称格式，可以包括用户指定的字符和符号，指定想要的十进制精度，并能应用16种不同颜色中的任何一种。这些格式和颜色甚至能够根据单元格里的数值范围来进行改变。

没有使用的、或者已经被清理的单元格具有常规数字格式，这意味着Excel用要能是最高的精度显示一个数字。如果这个数字太大或太小，就用科学格式来显示。例如：5.367E+0.5。如果一个数字或日期还是太大以致于不适合指定的格式，这个单元格就用#符号来填充。

（1）了解格式化数字的潜在危险

在屏幕上出现的格式化数字不可能具有计算中使用的数字相同的值。这个差异造成显示的或打印的结 果与手工计算的结果不一样。

为建立用户自己的整个工作表，以便使显示的数字与那些用在计算里的数字匹配，选择“工具（T）”“选项（O）”命令，“重新计算”标签。选择“以显示值为准（P）”复选框（当选择“确定”按钮时，将会被警告：“数据将永远失去其精度”）。

用户也可通过使用Excel中的ROUNDO涵数来设置选定单元格的精度。例如，可以在E5单元格里使用公式“=ROUND（B5*C5，2）”，这个公式在求和前对相乘的值进行了舍入处理。在做前一步的计算前总是要舍入的。

（2）使用Excel的自动数字格式

存储在单元格里的数字、日期的时间都是纯粹的数字，没有经过格式化。然而，Excel要考察输入的数 字格式，以决定这个应用程序是否能够格式化一个单元格。例如把数字#12.95输入到一个使用默认的常规格式格里，那么E xcel将格式化这个单元格为贷币使用。

若输入到一个百分数（如15%）到一个使用常规格式的单元格里，那么虽然它在公式上是15，而在工作表里看到它却是15%。

警告：如果一个单元格里填写有#字符，那么按当前的格式，它的列就没有足够的宽度来容纳装入的数字。为了解决这个问题，加宽列宽。如果对这个列的加宽引起了工作表其他地方的格式化问题，使用TEXT（）涵数来把这个数字改变成文字。数字或日期能够超过单元格的宽度，并可以有任何格式，包括自定义的格式。

（3）设计自定义数字格式

用户可以为金融或科学试验任务设计自己表示数字的格式，并为编制数字目录、电话号码、各国货币 等等创建各种不同的格式。无论何时，当需要用特殊的方法显示数字时，都可根据需要使用自定义的数字格式。

附注：

Excel包括了一些用于社会保险号数字和电话号码的特殊格式，如果这是想要创建的格式类型、就不必再创建自定义的格式了。需要做的事情仅仅是在分类列表中选定特殊项，然后选定所想要的格式。

①了解自定义数字格式

创建一个定义数字格式是很容易的，但需要了解几个Excel用来定义一个数字码的符号。为了创建自己的数字格式，需要在数字标签里把这些符号输入“类型（T）”字框里去。

创建的自定义格式有4个部分。就像下面所示的语法例子：

positive format; negative format; zero formt; text formt

请注意，每一部分都被一个分号所分开。第一部分指出一单元格里正数的格式，第二部分指出一单元里负数的格式，如此等等。

使用的符号作为占位或格式指示符。请注意，0作为一个占位符，并且在没有任何数字被显示的位置上显示一个0。

符号-）跟在一个正数格式后面，以保证在这个正数右边留下一个空格，它的宽度和右圆括号一样宽，负数用圆括号括起来。正数和负数都靠每列的右边对齐。

为了了解格式的每一部分是如何工作的，考察下面的自定义格式例子：

$#，##0-）；（$#，##0）；“Zero”

它将显示一个以$#，##-）格式表示的正数，以（$#，##0）格式表示的负数，而文字Zero表示一个0。例如：3550将以$3,550形式出现。-3550将以（$3,550）形式出现，并且0将以单词Zero形式出现。

当创建一个自定义格式时能够使用的符号将在图5-1-6中介绍。

②创建自定义数字格式

可在工作表的任何要方创建自定义数字格式，遵循下面的步骤：

选定想要使用自定义数字格式的单元格；

选择“格式（O）”“单元格（E）”命令；

选定“数字”标签；

在“分类（C）”的列表里选定“自定义”；

如果已经存在的格式和想要创建的自定义格式差不多，从这个列表里选定这格式；

在“类型（T）”文字框里编辑这个自定义格式图案；

选择“确定”按钮。

在创建一个自定义数字格式之后，在单元格里输入一个合适的数字，然后用正数、负数和零值测试这个 自定义格式。

可在工作表的任何单元格中重新使用定义的格式，这只需要通过选定自定义分类，流动到这个列表的底

部，然后选定任何自己预定义格式就行。

③删除自定义格式

为了删除一个自定义格式，遵循下面的步骤：

选择“格式（O）”“单元格（E）”命令；

选定“数字”标签；

在“分类（C）”列表里选定“自定义”，然后选定想要从这个列表里删除的格式；

选择“删除（D）”按钮。

提示：不能够删除内置格式。

④利用数字显示文字

用户可以在自定义格式里的分号之间，通过把文字用双引号括起来，并把这些文字内容插到合适位置上 的方法，在同一个单元格中用数字来显示文字。单元格里的数字在计算时仍然作为一个数字 来使用，但显示时是作为文字来显示。例如用户想要让一部分数字总是在P/N后面，并且在最后三个数字前面显示一个连字符，那么创建一个如下的自定义格式：

“P/N”###-##；“ExcelUse”;”Enter Number”

用这个格式，其中的数字5768953将被显示成P/N5768-953；输入一个负数，将显示文字：Usr Positive;而输入一个零将产生文字：Enter Number.

（4）用颜色格式化数据

彩色的文字或数字能够帮助用户发现数据项的差别或者认出标志数字（它们超出了某个范围）。单元格 的颜色格式化与单元格数字或日期格式化一起进行。

用户可在自定义的数字格式的合适部分用括号把颜色名字括起来，以指出要选择的颜色。例如在文字格式位置里的颜色格式改变文字的颜色。并且，如果想要使正数的格式是蓝颜色的，而负数的颜色是红颜色的，使用下面的格式：

[BLUE]$#，##0.00-;[RED]( $#,##0.00)

用户可以使用八种命名了的颜色和任何一种自定义的颜色。用下面的颜色符号来指定颜色：

[BLACK]（黑色）

[WHITE]（白色）

[RED]（红色）

[GREEN]（绿色）

[BLUE]（蓝色）

[YELLOW]（黄色）

[MEAGENTA]（紫红色）

[CYAN]（青色）

[COLOR#]（这里的#是一个在颜色调色板上的颜色编号，从0号到56号）

用户通过选择“工具（T）”“选项（O）”命令和选定“颜色”标签，就可看支Excel的颜色调色板。在标准颜色都用数字进行了编号，顺序是从上往下，从左到右（最左上角落的颜色编号是1，最右下角的颜色编号是56）。

（5）条件格式化

在用户定制的格式化中使用[condititon value]格式符号时，可以对一个单元格进行格式化，以便数 字用不同的格式可颜色出现，这依赖于数字的值。若将这个技术用在核查数据输入时出现的错误，来自分析的异常报告的执行程序信息系统方面告别有价值。

例如：下面的格式使得在单元格里的所有数字使用0.00 的数字格式。当数字大于可等于1，000时，数字就以黑色显示出来；当数字小于或等于500时，数字就以红色显示出来，对于在这 两个数字之间的数字都以蓝色显示出来。

[black][>=1000]0.00;[read][,=500]0.00;[blue]0.00

(6)隐藏零值

把零值隐藏起来常常能使工作表更容易阅读。在Excel里，把零值隐藏起来有3种选项：在整个工作表里都把零值隐藏起来；创建一个自定义格式；或者使用IF（）函数。

为了在整个工作表里把零值隐藏起来，选择“工具（（T）”“选项（O）”命令，选定“视图”标签，然后清除“零值（Z）”复选框。当想要又看零值时，再选定“零值（Z）”复选框。

为了使用自定义格式来把零值隐藏起来，在合适的部分使用分号以指出后面跟着零值格式，但并不为零数字输入一个格式，就像下面的格式一样：

$#，###-）；（$#，###）；

在公式中，使用IF（）函数来隐藏一个零值，像下面的例子一样：

=IF（A12+B12=0，“”，A12+B12）

这个公式指出：如果A12+B12等于零时，Excel将显示引号之间的东西，上例中什么也不显示（要注意， 若使用一个空格将指示一个零；在有些数据库或在某些数字和文字函数里，空格会产生问题）。如果A12+B12不等于零，Excel就会显示出化工的计算结果。

（6）关于数字格式化的提示

用户可以使用一个逗号格式（例如：#，##0）把要显示的数字三位三位地分开，这对显示那些具有千位或百位的数字就很有用，例如：用$#，##0，“M”（这里的逗号结束格式数字部）这样的格式来对数字12345689进行格式出来的结果为：$123，457M。请注意，显示的数据被四舍五入了，而不是被截短了，紧接着的计算继续使用单元格里的实际数字，并不是显示出来的那个截短的数字。

当用户需要把一个数字安放在一个宽度较窄的列里时，使用TEXT（）函数去把这个数字或公式的结

果转换成方案形式，这个数字就能覆盖单元格边沿而不必转换成用###符号来显示。用TEXT（）函数把数字转换成还能够在其他公式中被引用 ，并将正确地进行计算。在TEXT（）函数里使用的数字公式可以是前面介绍的自定义数字格式之一。例如A12*C35的结果太大，以至于没有安放在窄的单元格里，并且它需要一个货币格式，这时就可以在同一个单元格里使用己经使用过的A12*C35的公式。

：EXT（A12*C35，“$#，##0-）；（$#，##0）：0”）

疑难解答

问题：在格式化之后，较长的数字不再适合放在单元格里，这个数字太宽，并且显示出来是####，而不再是数字。

回答：把列加宽，一直加到这个数字出现为止。也可以使用TEXT（）函数把这个数字或公式的结果转换成文字形式，这样这个文字就能覆盖单元格边沿。这个转换成文字的数字还能够在其他公式中被引用，并且可以为一个数字进行计算。

用户可以通过调整工作表里的列宽的行高以改善工作外观。合适的调整有助于在一页里放下更多的数据，甚至可以在一行或一列里隐藏保密的数据。

关于调整列宽、隐藏列，调整行高、隐藏列，调整行，添加颜色、图案和边框知识请参看第1章Excel 基础知识与基本支巧。

5．2 宏（VBA）及其在财会管理中的应用

在实际工作中，常会做一些财务管理分析模型。而每个财务管理模型都分散地放在相应工作簿的要作表中，财务管理人员需要使用该模型进行分析时，必须选择包含模型的工作簿所在的目录，选择目录中的文件（工作簿名字）打开工作簿，再选择具体管理模型据的工作表等，即每次使用模型都必须重复繁琐的操作步骤。为了减少不必要的操作，以及让更多的不太熟悉Excel操作的财务管理人员都能非常方便地使用已经设计好的模型，就需要就用Visual Basic For Application（简称VBA）宏技术，将设计好的模型组合起来，建立一个面向财务管理人员的财务管理系统。本节主要学习记录和编写宏和编写宏程序，并用宏设计用户界面、系统菜单，建立一个完整的财务管理系统的技术的方法。

5.2.1 宏语言概述

Excel支持VBA，即Microsoft s Visual Basic, Application Edition, 是从流行的Visual Basic编程语言中派生出的一种语言。Excel提供了其他工作所不能提供的一些更广和特性。然而无论Excel的特性有多广，在特殊行业与特殊情况下用户一定全要求更多的特性来满足其特别的需要。对会记录或书写宏的任何人，都可以添加特性、函数和命令到Excel,让Excel按需要工作。

Excel可以广泛地应用于财务、金融、经济、统计和审计等众多领域，它是一个强有力的信息分析和处理工具。这种力量的源泉之一就是它的宏语言（VBA）。利用职权VBA所提供的功能，财务管理人员可以按自动方式行日常的管理、分析、决策任务，还可按需要加入某些自定义功能，直至建立一个完整的财务管理应用系统。

1 建立宏程序

VBA是一种计算机编程语言（第四代语言），用它提供的语句、命令可以编写包含若干指令 序列的宏程 序，它可以指挥Excel应该进行哪些工作。

（1）宏程序的概念

宏程序是指用VBA提供的各种函数、语句、对象、方法和属性等编写的程序。在Excel中也称为过程。

过程是在VBA模块中一个可执行的VBA程序代码块，过程有程序代码序列组成，这些代码序列组合在一起可以完成某项任务。

VBA中的过程主要可分为两类——子过程（Sub Procedure）与过程函数（Function Procedure）。

①子过程

子过程以SUB语句开始，以END SUB语句结束。过程可以执行某种操作，但无返回值。其结构是：

SUB过程名（）

命令序列1

命令序列2

END SUB

②过程函数

过程函数以FUNCTION语句开头，以END FUNCTION语句结束，并可以有返回值。

其结构是：

FUNCTION函数名胜古迹（参数?）

命令序列1

命令序列N

END FUNCION

（2）建立宏程序的方法

宏程序存放在Excel工作簿的宏表中，建立宏程序的步骤：

进入“工具”“宏”下的“Visual Basic编辑器”，选择“插入”菜单中“添加模块”命令，Excel就会在工作簿中增加一张宏表，并命名为MODULEI，在宏表中建立友程序；或选择“工具”菜单中“宏”下的“记录宏”命令，Excel就会出现“停止录制”图标 ，用户可以对工作簿、工作表、单元进行各种Excel自动根据操作编制宏程序；当操作完毕时，按停止录制图标，完成一个宏表的制作。同时Exxcel在工作簿中也会增加一张宏表，并命名为MODULE 1。

2 宏程序结构与宏程序举例

宏程序是程序语言，所以它也有第三代语言一样的程序结构。

.顺序 （SEPUENTAL）；

循环（LOOP）；

条件（Condition）或Excel分支（Branching）。

（1）顺序结构

在正常状况下，宏程序的执行是以“Excel顺序“方式进行的，即由上而下逐一执行。用“Excel宏记 录器”记录经常性工作时，它也是“Excel”顺序方式。

例一：编写一宏程序。

过程名为：PROL1

功能：xj10.XLS工作簿中的“ExcelSALE”工作中，B1单元赋一报表的标题“恒远公司财务分析”，C2单元赋日期“19xx年8月”。

编程过程：

① 打开工作簿xj10.XLS；

② 进入“工具”“宏”下的“Visual Basic编辑器“；

③ 选择“插入菜单中“宏表”命令下的“模块表”命令，Excel就会在工作簿中增加一张宏表，并自动

命名MODEL1；

④ 选择MODULE1宏表输入宏程序：

SUB PROL1（）

SHEETS （“ExcelSALE”）.SEKECT

RANGE (“ExcelBI”) .DORMULA=“Excel恒远财务分析”

RANGE（“Excel C7”）.FORMULARICI=“Excel19xx年8月”

END SUB

例二：编写一宏程序

函数名为：tax

功能：在CWGL10.XLS工人作簿中的MODULEL宏表中编写 一函数，即根据销售额（SALES）计算销项税（TAX）的函数。

编写过程：选择MODULEI宏表输入宏程序：

Function tax(sales)

Tax=sales*0.17

End Function

例三：编写一宏程序。

过程名为：PROL2

功能：在CWGL10.XLS工作簿中的“ＦＸ”工作表中按顺序分别给单元Ｂ１、Ｃ１、Ｄ１赋值１０、２０、３０。

编写过程：

选择MODULEI宏表输入宏程序：

Sub PROL20

Range(“ExcelB1”).value =10

Range(“ExcelC1”).value =20

Range(“ExcelD1”).value =30

Emd Sub

（２）分支结构

优势需要对默认条件测试，然后根据测试的结果进行不同的操作，这就要使用分支结构。

语法 １：

IF(condtiion)

Then(statements)

注意：在单行的IF?Then语句中不使用Ｅnd if语句。当测试的条件为TRUE时需要执行多行程序代码，则必须使用IF?THEN?END IF语句。

例四：编写一宏程序。

过程名为：PROL3

功能：如果“总销售额”大于等于５０万元，在屏幕上输出“完成任务，应给予嘉奖！”。

选择MODULE1宏表输入宏程序：

SUB PROL3 ()

IF 总销售额>=50

THEN MSGBKX “完成任务，应给予嘉奖！”

END SUB

语法 ２：

IF (CONDITION) THEN

(STATEMENTS)

ELSE

(STATEMENTS)

ENDIF

例五：编写一宏程序。

过程名为：PROL4

功能：如果“总销售额”在于等５０万元，在屏幕上输出“完成任务，应给予嘉奖！”，否则在屏幕上输出“继续努力”。

编程过程：

选择MODULEI宏表输入宏程序：

SUB PROL4 ()

IF 总销售额>=50 THEN

MSGBOX “完成任务，应给予嘉奖！”

ELSE

MSGBOX“继续努力”

END IF

END SUB

语法 3：

SELECT CASE （TESTEX PRESSION）

CASE （PRESSIONLST1）

（STAEMENTS）

CASE （PRESSIONLST2）

（STAEMENTS）

END SELECT

例六：编写一宏程序。

过程名为：PROL5

功能：根据实际销售量判断其是大于、等于、小于保本点销售量，决定输出“盈利”、“保本”、“亏损”。 假设：B2单元为实际销售量，并将该单元名定义为销售量，C1单元为保本点销售量，并将该单元名定位保本点销售量。

选择MODULE1宏表输入宏程序：

SUB PROL 5（）

SELECT CASE 销售额

CASE 销售额<保本点销售额

MSGBOX “亏损！”

CASE 销售额=保本点销售额

MSGBOX“保本！”

CASE 销售额>保本点销售额

MSGBOX“盈利！”

END SELECT

END SUB

（3）循环结构

语法 1：

DO WHILE （CONDITION）

（STATEMENTS）

LOOP

例七：编写一宏程序。

过程名为：PROL6

功能：当财务管理人员在不输入结束信息命令之前，以至进行数据整理，直到输入了结束信息FALSE后，退出本过程。

编写过程：

选择MODULE1 HONG宏表输入宏程序：

SUB PROL6（）

FLAG=VBYES

DO WHILE FLAG=VBYES

PROCESSUSERDAT’A调用过程整理成本数据

RESPONE=MSGBOX（“继续整理成本数据吗？[TRUE/FALSE]”，YES/NO）

LOOP

END SUB

语法2：

FOR counter=start TO end [step increment]

(statements)

next [counter]

例八：编写一宏程序。

过程名为：PROL7

功能：根据销售量的变化，计算相应的利润，如果利润大于0，输出“盈利”，否则输出“亏损”。

假设：销售量从200变到300，每次增加10，单位变动成本、单价、固定成本等所在单元都已定义了名字。

编程过程：

选择MODULE1HONG宏表稷输入宏程序：

Sub PROL 7 ()

For销售量=200 TO 300 STEP 10

利润=（单价-单位变动成本）*销售量-固定成本

IF 利润>=0 THEN

MSGBOX “盈利！”

ELSE

MSGBOX“亏损！”

END IF

EXIT

END SUB

语法2：FOR EACH （ELEMENT） IN （GROUP）

（STAEMENTS）

NEXT （ELEMENT）

例九：编写一宏程序

过程名为：PROL8

功能：关闭所有打开的工作簿。

编程过程：

选择MODULE1HONG宏表输入宏程序：

SUB PROL8（）

FOR EACH BOOK IN WORKBOOKS （）

BOOK . CLOSE

END SUB

以上是利用VB（Visual Basic）编辑器来编写程序，来完成某段特定的过程。使用这种方法的人需要具有一定的计算基础；Excel还提供使用者一种简单易用的编制宏程序的方法，即宏记录器。

3 宏记录器的使用

Excel提供的宏记录器可以帮助财务管理人员所做的各种操作，当操作结束时，关闭宏记录器，宏程序 便保存在宏表中。宏记录下来之后，可以通过以下方法执行宏：

用“工具”菜单中选择“宏”命令；

将客观存在指给某个菜单项，通过选择菜单执行宏；

将它指定给宏按钮，通过驱动按钮执行宏。

（1）记录宏的方法和步骤

① 选择“工具”菜单上的“记录宏”命令下的“录制新宏”命令；

② 在“宏名字”框中输入宏程序的名字，工程项名可以包括字母、数字和下划线；但必须以字母（某种

文字）开头，名字中不能包括空格或标点符号；

③ 在“描述”框中，输入与要记录的宏有关的说明信息；

④ 选择“确定”按钮，此时[停止记录宏]按钮出现在屏幕上下 ；财务管理人员可以进行各种记录的操

作，如选定单元格、输入标题、设置表格等；

⑤ 单击“停止录制宏”按结束宏记录。

（2）记录宏实例

例十：在工作簿CWGL10.XLS 中，插入一张工作表，将该工作表名字改为“恒远财务管理系统界面”， 在C3单元输入标题“恒远财务管理系统”，将该标题的字号设置为14。并在该标题下划线，取消网格线。

记录宏的过程：

① 选择“工具”菜单上的“记录宏”俱下的“录制新宏”命令；

② 在“宏名字”框中输入宏程序的名字“info hy”

③ 在“描述”框中，输入与要记录的宏有关的说明信息；

④ 选择“确定”按钮，此时“停止记录宏”按钮出现在屏幕上；

⑤ 选择“插入”菜单上的“工作表”命令；

⑥ 将新插入的工作表名改为“恒远财务管理系统界面”；

⑦ 选择C3单元，输入标题“恒远财务管理系统”；

⑧ 选择标题， 单击“字号”工具，选择14；

⑨ 单击“下划线”工具；

⑩ 选择“工具”菜单上的“选项”命令，去除网格线；单击“停止记录宏”按钮，结束宏记录。

（3）记录的宏程序清单

info hy宏

丁力记录宏观 08/08/1998

Sub info hy ()

Sheets.Add

Sheets (“SHEET1”).Select

Sheets (“sheetl”).Name =“恒远财务管理系统界面”

Range (“C3”).Select

Activecell. FormualaR1C1 =“恒远财务管理系统”

Range(cC3:D3).Select

With Selection. Font

Name =”Times New Roman”

Font Stye =”Regular”

Size =14

End With

Selection. Font. Underline =xLSinge

ActiveWindow.DisplayGridines=False

End Sud

通过上述大量的宏程序可以看出，对于学习过其他编程语言的财务管理人员来说，编写宏程序并不困难， 只需要学习和掌握VBA提供的各种语句、对象的方法和属性等，就可以很快编出宏程序。对于没有学习过其他编程语言的财务管理人员或对宏语言不熟悉的财务管理人员，可以利用Excel提供的宏记录器自动记录您在Excel中进行的各种操作，也可以通过阅读这些宏程序，不断学习宏的编写方法。

值得注意的是，宏记录器编写的程序都是较简单的顺序结构的程序，如果需要比较复杂的结构如分支、循环等结构的程序时，财务管理人员对宏记录器中记录和程序进行修改便可使用。

4 宏的执行

宏记录下来之后，可在任何时间执行这个宏，Excel将执行在宏程序中的全部命令。当宏没有被指定到 宏按钮或菜单项时，只能按下列步骤执行。

① 在“工具”菜单中选择“宏”命令；

② 在“宏名字”框中选择宏名字“info hy”;

③ 选择“执行”按钮。

5 利用宏设计自定义财务函数

在实际工作中，人们经常利用宏设计自定义财务函数。取数函娄数就是定义的一种。关于自定义函数的 内容不是本章的重点，当读者阅读到这部分内容以后，知道哪此函数是自定义函数就可以了。

5.2.2 用户界面的设计与使用

在实际工作中，我们会设计若干个财务分析模型，并将同一类模型存放在一个工作簿中，该工作簿的若干张工作表中存放着该类不同问题的模型。如果每个工作簿中实际是一个用户界面，就能使财务管理人员通过该界面一目了然了解到该工作簿中包含哪此模型，并通过驱动办界面上宏按钮直接进入各了模型,完成各种管理、分析、决策工作.

1 给图工具栏

选择“视窗”菜单上的“工具栏”命令的“绘图”工具栏，如图5-2-2所示。通过使用绘图工具栏上的各种 工具，可以绘制出包括宏按钮、文字框以及正方形等精美的用户界面。

下面对工具栏中的各工具进行介绍。

① 绘图选项工具；

② 自选图形：自动创建各种步同的图；

③ 直线工具：画直线；

④ 箭头工具：画箭头；

⑤ 矩形工具：画矩形；

⑥ 圆形工具：画圆及椭圆；

⑦ 文字框：在任何图形对象中添加工具（文字水平徘列）；

⑧ 竖形文字框：在任何对象中添加工具（文字垂直排列）；

⑨ 艺术字工具：提供各种艺术字体选项；

⑩ 填充色工具：修改图形对象中的填充色；

⑾ 线条颜色工具：修改图形对象中的线条颜色；

⑿ 字体颜色工具：改变字体颜色；

⒀~⒂线条修改工具：改变线条颜色；

⒃ 阴影工具：为图形及字体设置阴影；

⒄ 三维设计：设计图形的三维效果。

2 建立用户界面

以存在的cw105.xls工作簿为例，说明用户界面的设计方法。

图5-2-3所示的是财务报表分析模型用户界面实例。

（1）在CWGL05.XLS工作簿中插入一个工作表

① 选择“插入”菜单上的“工作表”命令，在CWGL05.XLS工作簿中产生一新表；

② 将新表名改为“财务分析界面”；

③在该表输入标题、绘制图形。

（2）在用户界面上绘制“宏按钮”

① 单击绘图工具下的工具按钮；

② 选择放置按钮的位置，拖动鼠标至所需的尺寸；

③ 选中所绘制的工具按钮，点击鼠标至右键，选择指定宏；

④ 进入指定宏界面，在“宏名”框中选择录制好的宏程序RATE（CWGL05.XLS工作簿中存放该宏程序），

选择“确定”按钮，即将宏程序指定给宏按钮；或在“宏名字/引用”框中不输入宏名字，并选择

“取消”按钮，即不选择宏程序，等以后再指定；

⑤ 输入宏按钮的名字（如比率分析），单击工作表的任意单元。

（3）编辑图形对象

当在工作表中建立包括各种图形对象（圆、矩形、按钮等）的用户界面后，有需要对其进行移动、缩放、复制、删除、修改、改变颜色等操作。此时，只需选择该对象，然后用简洁菜单中的命令，便可对其进行各种编辑。

编辑方法：

① 将鼠标指针移动某对象；

② 单击鼠标右键，对象被选中并弹出简洁菜单，如图5-2-4所示。

使用“指定宏”命令可以将该对象与宏程序相连接。通过该命令也可以将宏按钮定给某一宏程序。不选 择菜单中的命令，而用鼠标单击该对象，此时可以用鼠标拖动该对象到任意位置，或改变该对象的尺寸。

3 用户界面的使用

从图5-2-3可以看出，财务报表分析模型中包括比例分析、趋势分析、杜邦分析和即出模型四个按钮， 这四个按钮分别指定给四个宏程序。因此，财务管理人员想进入模块分析了模型（如比率分析等）进行分析工作只需驱动宏观按钮。

例十一：选择“比率分析模型”的rate宏程序如下：

Sub rate ()

Sheets(“比率分析”).Select

End Sub

将该宏程序指定给宏按钮比率分析。

现在财务人员进行以下操作：

① 单击“比率分析”宏按钮，出现比率分析模型，如图5-2-5所示，该模型中一个宏按钮对应着一宏程

序，单击模型中不同的宏按钮便可得到相应的比率分析结果；

② 当比例分析工作结束后，单击“返回”宏按钮，则自动返回到恒远财务报表分析主界面；

③ 同理可经单击财务报表分析界面上的“趋势分析”、“杜邦分析”按钮，又可进行相应分析；

④ 当财务报表分析工作结束时，选择“文件”菜单“关闭”俱，则自动关闭该工作簿。

前两小节讨论了友语言的宏程序的编制，包含宏按钮在用户界面的建立，以及将宏程序指定给宏按钮的 方法。现在我们可以返回去，对前边设计和建立和各种模型改进，即尽可能地在每张工作模型中建立完成某种任务的宏程序（如流动比率分析宏程序、编制报表宏程序、数据采集宏程序等等），建立宏按钮，并将其余相应的宏程序连接；在每个工作簿中都有建立相应的用户界面（财务报表分析模型界面、投资决策模型界面等）。这样，财务管理人员就可通过驱动各类模型界面上的宏按钮进行该工作簿中的各种分析模型，在每个工作表分析模型中又通过驱动宏按钮完成各种管理、分析、决策工作。宏技术的使用可以减少重复工作量，减少出错的可能性。财务管理人员使用宏控制工作表完成各种操作，就像使用遥控器一样简单、直观、安全。

5.2.3 菜单的设计方法

在实际工作中我们会建立多个包含各种管理、分析、决策的模型，并分别存放在多个工作簿中。管理人员使用每类模型，还必须知道该类模型所在工作簿的名字（如CWGL05.XLS工作簿名字是投资决策模型所在的工作簿的名字），根据名字打开相应的工作簿。如果将这些模型汇总到菜单上，财务管理人员可以清楚地了解财务系统结构，并通过选择菜单上的命令，自动打开包含某类模型的工作簿，进行管理、分析、决策工作。 1 菜单管理器

建立财务管理系统菜单要通过菜单管理器完成。菜单管理器是Excel提供给用户的一个工具，它可以帮 助用户建立自己的菜单，并对这些菜单进行修改以满足自己的需要。通过使用菜单管理器，用户可在菜单栏中加入一个菜单，并在菜单中加入菜单命令；用户可以根据需要将某个宏程序指定给菜单命令，当菜单命令被选择时，招待时，执行相应的宏程序。

（1）Excel菜单的构成

在用户建立自己的菜单前，先了解Excel的菜单结构，内部菜单包含菜单栏、菜单、菜单项、子菜单

等项。

用户可以通过鼠标选择菜单中的命令，也可以用快捷键选择菜单中的命令。

（2）菜单编辑器的使用方法

用户可以使用菜单编辑器或编写VBA程序修改内部菜单。有关用VBA个性菜单的内容略，在此主要讨论在工作簿中生成菜单系统的方法：

① 打开工作簿（如CWGL10.XLS）；

② 选择“工具”菜单上的“自定义”命令，在“自定义”窗口中定义用户希望增加的菜单（自定义界面

如图5-2-6自定义菜单）。

2 在菜单栏中增加一外“财务管理”菜单

① 打开工作簿（如CWGL10.XLS）。

② 建立宏表。如果工作簿中有宏表，则选择表；如果没有则建立宏表及宏程序。点VB编辑器，进入“模

块表”编辑状态，建立若干个宏程序。

CWFX宏：打开CWGL05.XLS工作簿，进入财务报表分析模块。

TZJC宏：打开CWGL05.XLS工作簿，进入投资决策分析模块。

LDZJ宏：打开CWGL06. XLS工作簿，进入流动资金分析模块。

CZSY宏：打开CWGL07.XLS工作簿，进入筹资分析模块。

XSLR宏：打开CWGL08.XLS工作簿，进入销售与利润分析。

CWJH宏：打开CWGL09.XLS工作簿，进入财务计划模块。

其中以CWFX宏举例如下：

Sub analusis ()

Workbooks.OpenFineame:=”c:\cwgl05.xxls”,Updatelinks:=0

Sheets (Excel财务报表分析界面).Select

Sud Sub

③ 选择“菜单编辑器”命令

选择“工具”菜单上的“自定义”命令，选择“命令”标签进入菜单编辑状态（如图5-2-6）建立菜单 “财务管理“。

在图5-2-6中，在标签“命令“中选择新菜单，用鼠标拖动新菜单到菜单行，“更改所选内容”，把“新菜单：更名为“财务管理”，如图5-2-7所示。

到此为止，我们建立了一个完整的财务管理系统，其功能结构图如图5-2-8所示。

当打开CWGL10.XLS工作簿，菜单栏就有“财务管理”菜单；选择“财务管理”菜单，便弹出下拉菜单。它将财务管理系统功能结构图中和主要功能模块以命令的形式显示在下拉菜单上。财务管理人员可以选择任意命令，相当于选择了结构图中的第一层功能模块。该功能模块包含模型的用户界面展示在屏幕上，单击用户界面上的宏按钮，相当于选择了结构图中第三层功能模块。此时具体的管理、分析、决策模型展示在财务管理人员面前，可以进行各种分析工作。

5.2.4 控制应用系统自动启动的方法

1 建立自动启动宏程序的方法

当打开和关闭某一个工作簿时，希望自动执行该工作簿中的某个宏程序或过程，如打开CWGL10.XLS 工作簿时，希望自动执行一个宏程序，显示财务管理系统主画面，关闭工作簿时需要自动执行一个宏程序，显示“谢谢使用恒远财务管理系统，再见！”这就需要建立名为Auto-Open或Auto-Close的宏程序。区别自动宏程序于一般宏程序是通过宏过程名来识别的。

（1）建立一个工作簿打开时自动招待的宏程序

例十二：在CWGL10.XLS工作簿中建立一个自执行的宏程序，其功能是显示“欢迎使用恒远财务管理 系统”，自动显示财务管理系统主界面。

选择宏表：

建立如下程序：

Sub Auto-Open ()

Msgbox (“欢迎您使用恒远财务管理系统”)

Sheet“恒远财务管理系统界面”).Select

End Sud

当打开CWGL10.XLS工作簿时，屏幕显示如图5-2-9所示。

欢迎您使用恒远财务管理系统

确定

单击“确定”按钮，便显示财务管理系统主画面，如图5-2-3所示。

此时，财务管理人员可以选择图5-2-7上的“财务管理”菜单，进入各模型。

（2）建立一个关闭工作簿时自动执行的宏程序

例十三：在CWGL10.XLS工作簿中建立一个当工作簿关闭时自动执行的宏程序，其功能是显示“谢谢 使用财务管理系统，再见！”

选择宏表，建立如下程序：

Sub Auto-Close ()

MsgBox (“谢谢使用恒远财务管理系统，再见！”)

End Sub

当关闭CWGL10.XLS工作簿时，单击“确定”按钮结束。

2 利用启动目录自动打开工作簿

希望启动Excel后，自动打开某个工作簿，则可利用启动目录实现该功能，Excel将启动目录定义为C：\Excel\XLSTSRT。

制作自动打开工作簿的方法：将需要自动打开的工作簿移动或复制到启动目录。

5.3 动态数据分析及数据透视表的应用

5.3.1 数据动态分析的必要性

如今“Excel分析”一词的使用频率是很高的，市场分析、金融分析、财务分析??不绝于耳。所谓分析就是让您劈开迷雾，看清庐山真面目，透过现象，看到事物的本质。早期数据分析都需要依靠数据库技术， 在有关数据库语言支持下进行操作。实际上，Excel 软件本身具有数据库技术的基本功能。

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

读者可以通过前述Excel软件应用基础介绍，建立起适合自己工作需要的具有数据管理功能的工伯表，也可通过前述Excel函数调用，在工作表内生成自己工作需要的信息。通过本节介绍的“模板”与“宏”这两种工具的应用，建立起定制自己工作界面、设计自己工作菜单、完成适合于自己工作习惯的，而且可以不断重复使用的工作簿与工作表。正如图5-3-1实例所述，仅依靠前面介绍的那技术是无法马上求得纵向或模横向比较信息的，更无法马上显示环境条件变化的实际结果。

为了帮助作废探讨数据内部奥秘，Excel软件提供一个强有力的数据分析工具——数据透视表。数据透视

表是一种为表现多种事务内在联系而建立的动态数据分析表，利用它您可以像x光透视那样来观察普通二维电子表格呼数据；通过动态变换行列结构，可以看至少同组合的效果，得出不同的汇意报表。

数据的动态分析可以在工作表内生成实时信息。要使工作表能产生动态实时信息，除了使用数据透视表工具外，还有必要补充使用一些在Excel软件内已经提供的某些技巧。

5.3.2 有关数据动态化的某些技巧

1 把工作表视为数据库

Excel 软件内的工作具表并不能完全替代数据库的功能，但在简单的数据信息管理条件下，工作表的结 构和Excel软件的某些操作是可以胜任数据库管理的基本要求的。

（1）在工作表内创建数据库

只要在使用工作表时把工作表的行看为记录，把工作表的列看作为字段。在Excel软件内不需要键入 任何命令，就可以创建数据库了。

数据库的字段通常要求有字段名，Excel软件工作表内顶项（即报表有效工作区域里的第一项）所填写的字符也就是字段名。写段宽度在Excel软件工作表内由列宽来决定。

（2）实现数据库的基本操作

在数据库的管理技术中，数据库的基本操作包括：数据输入与数据记录的增、删、改，数据排序与数 据的筛选，数据的运算操作与输出。

在Excel软件内只要创建了数据。也就是说，在工作表里填写过报表的栏名（或写入过一条或两条记录）后，在“数据”菜单下用鼠标单击“记录单”，就可以在操作窗口生成一个数据输入操作环境，如图5-3-2所示。

当然，也可以在工作表内通过直接填写来完成数据输入操作，这是常规的Excel软件基本操作。而数据记录的增、删、改，更应该常规操作方法一致。

数据排序与数据的筛选，它是在“数据”菜单下用鼠标单击“排序（S）?”或“筛选（F）”而产生如图5-3-3形式。

数据排序是数据库操作中常用的管理技巧，它可以使数据记录按要求条件排列，便于查询与浏览。Excel软件中数据排序操作可以完全满足数据库管理需要。先选定要求排序的字段，再选定要求排序的条件。简单的条件不外于升序或降序，但也可以自定义种种条件，甚至可以令数据库籽段按求排序。排序的字段可以不止一个，操作界面允许选定不超过三个字段，但也可以执行多字段的排序操作。由于“排序”菜单后面附有“?”标记，有关选定排序条件的对话框，可以在双击菜单后自动出现。

数据筛选是实现数据动态化的关键性操作技巧。如5-3-3所示，当操作者选定了“筛选”条目后，可以执行“自动筛选”与“高级筛选”两种不同操作；图内变灰了那个“全部显示”条目，仅当执行了有关筛选操作后才能动作，实际上它不过是承担撤消前设筛选操作的功能。

自筛选可使被分析的那个工作表标题栏右边都加上一个操作按钮。鼠标单击此按钮时，屏幕上出现一个下拉式菜单。菜单内罗列了该标题栏下的不重复的全部记录条目，供操作者作为筛选条件予以使用。同时，菜单还提示一个“自定义”的条目。鼠标单击“自定义”后，屏幕推出图5-3-4所示的操作窗口，可以在选定的第一季度字段里加入有关自定义的筛选条件，如：等于、大于、小于某某数；或者加上“与”、“或”逻辑操作而加入有关自定义的筛区域。

高级筛选用来定义更为复杂的筛选，如图5-3-5所示。它不仅可以在原来工作表里显示筛选结果，而且还可以把筛选结果显示在其他位置而生成另一张工作表。可以在操作时任意选定数据区域，也可以在操作时使用条件区域。

条件区域指的是在工作表内填写有筛选条件的某一区域。条件区域包括两部分内容：一部分是标题行，它填写一些与数据库字段名完全相同的字符；另一部分是条件行，它填写相应字段的记录中要求筛选的条件。

例如，在“ABC公司”工作簿的“业绩统计”工作表内填写如图5-3-6所示的数据。在该表的D15：E16区域内填写有条件区域住处信息。图5-3-5的高级筛选操作界面实际上就是针对图5-3-6数据界面。D15：E16

区域选定为条件区域后，具体含义就是要求筛选出属于企业类型的本月销售额大于5000元的客户记录。如果要求把筛选结果显示在工作表的B18：I27位置内，可把“方式”对话框的要求改为“将筛选结果复制到其他位置”。针对图5-3-6数据的操作，屏幕显示有图5-3-7那样的结果。

通过排序与筛选，可以在工作表内实现动态获得有效的信息输出。尤其在高级筛选条件下应用了条件区域操作技术以后，它是使工作内实现动态输出的关键所在。

（3）设法使数据结果动态显示

使用数据库技术，项目的为能有效而及时产生操作者需要的各种信息。这些信息应该是动态的，是可以满足不同时间条件下、不同信息使用者需求的Excel软件内的工作表，虽然可在某些条件下实现数据库管理的基本要求，但是毕竟不能完成全替代数据库的所有功能。

要使用权Excel工作表内的住处输出动态化。首先应掌握在多个工作表内任意取数的技巧。在上述工作表所能承担的数据库基本功能基础上，充分利用高级筛选条件下的条件区域操作技术，把多个工作表内记录自由调度。要想在Excel工作表环境内实现这一要求，关键还要在工作表内进一步地发挥函数作用。

① 再谈条件区域的使用

前面简单地介绍了高级筛选条件下的条件区域操作技术，而且指明它是在工作表内实现数据住处动态化 的基础。为此有必要在此全面介绍条件区域的使用规则。

条件区域是在工作表内列写了数据查询条件的，由若干行列组成的一个区域。条件区域的第一行，填写一些与数据库字段名完全相同的字符。在图5-3-8内把它们统称为字段一、字段二、字段三?等。条件区域的第二行、第三行?等位置里，填写了一些与相应列有关的查询条件，也就是有关数据库字段内的记录查询显示内容。在图5-3-8内把们统称为：字段一的条件一、字段二的条件二，字段二的条件一、字段二的条件二?如此等等。

为了进一步理解条件区域所表达的计算条件，下面结合图5-3-6的数据实例，具体说明高级选条件区域使用规则。

图5-3-8 条件区域的表达规则

实例（1）筛选行业类型属于商业的全部记录；

(4)筛选所在地区不在东，也不在北的全部记录；

年2有1日的全部记录；

（5）筛选所在地区在东，而行业类型为企业的全部记录（注意：条件不列定在同一行的原因）；

20000元的全部记录（注意：这是一个筛选比较复杂的具体操作）；

通过上述8个实例分析，相信读者一定能悟出一些操作规则来。在条件区域内指定计算方式，也就是在工作表有关单元格内输入运算条件。运算条件可以选的关系操作符起头，后面紧跟着具体计算值。

现将应用于条件区域内的关系操作符，结合上述8个实例归纳如图5-3-9所示。

除了关系操作符的应用外，条件区域内的数据排列是表示筛选操作规则的重要组成。筛选操作在单元 格里表达功能定义，而单元格之间的数据排列表达了条件组合的逻辑运算。条件的逻辑运算包括如下两种： 条件的“与（AND）”操作，表示满足于所有条件的记录。操作时必须把这些条件放置于同一行内。参见实例（1）、（2）、（3）、（4）、（5）。

条件的“或（RO）”操作，表示满足于各项条件中的某一条记录。操作时必须把这些条件放置于同一列内。参见实例（7）、并请注意理解实例（6）的真正含义。

通过实例（8）的操作，相信读者一定能得到启发，进一步掌握条件区域的运算公式编辑。在此基础上，也就可使工作的信息输出随操作者的意图而灵活变化。

② 在工作表内形成动态条件

信息输出的灵活变化并不等于住处输出动态化。信息输出动态化的要求为：首先能够及时获得原始素材， 其次能使编辑好的公式执行不同要求的操作，最终目的是应该使参予数据处理的函数与公式动态化。

在Excel软件内，数据是被存储在不同工作簿的各份工作表里的，要在输出住处的工作表里，及时地获得不同条件下的原始数据。具体地说，就上要求及时灵活地调用其他工作表内存储好的原始数据。这时，有必要复习一下在工作表的单元格里昌如何获得信息数据的。

同一张工作表内，编辑某一单元格的取数公式时，仅需要在公式的等号后面加上被取数据存放位置的那个单元格名。

在同一个工作簿的不同工作表内，编辑某一单元格的取数据存放位置的那个单元格名前肌面加上具体的工作表名。

编辑某一单元格的取数公式，要求不同工作簿内取不同工作表内有关单元格里的数据时，除了在公式的等号后面加上被取数据存放位置的工作表名和单元格名以外，还必须再加上有关工作簿的名称。而且，在操作运算时必须注意工作簿之间的数据链接。请参阅第4章内有关多表编辑的内容。

多表编辑可以在不同条件下获得不同工作簿或是其一工作簿内的原始素材，但并不能使编辑好的公式执行不同要求的操作。这个要求是可以依赖于编辑单元格内的数据运算公式实现的。

这里，再以图5-3-6所示实例来说明其查询结果如何实现动态化的。已知图5—3-6要求筛选行业类型属于企业，而本月销售额大于5000元的全部记录。也确实能够筛选出工作表内的相应企业为宏达厂，如图5-3-7结果。现在，设想要求筛选本月销售额大于5000元的，而为任意指定条件的数据。例如要耱筛选本月销售额大于3000、小于5000，在2000到4000范围里?等条件的企业。为了不破坏图5-3-6所示的原有操作环境，可在另外一张工作表里，设计为满足动态查询用的人工界面。例如在图5-5-10的输入界面内，在B2单元格里标记查询条件“请输入要求销售额超过”，在C2单元格里创造好供操作者键入条件数据的环境。这种环境实际上是由图5-3-6所示的E16单元格中公式实现的。

当操作者将图5-3-6所示的E16单元格内容改写成=“>”&C2以后，输入图5-3-10单元格C2里的数据自然转变成图5-3-6单元格E16内的查询条件。由此，图5-3-6的查询操作就可随着操作者在图5-3-10输入界面内的数据变化而自由改变。这是一个极为简单操作方式的改变，也许还可能使读者以为这是多此一举。任何程序设计思路往往都是从极为简单的量变引发为总想不到的质变。Excel软件中，工作表的动态操作就是这

样开始的。图5-3-6单元格E16内容的改写，实际上不过是Excel软件中正文函数的单间应用。E16单元格里等号后面的“>”代表了能起到运算功能的一个字符，&代表了能把字符连接在一起的正文函数运算操作符，C2代表了在单元格C2里的文字。例如：在鞭一工作表内，A1单元格填写“中华”两个字，A2单元格填写“人民共和国”五个字，如果A3单元格里填写了公式“=A1&A2”，那末A3单元格内就可以显示为“中华人民共和国”。以此推论，图5-3-6单元格E16内所示的公式（=“>” &C2）含义，读者一定能够理解的。下面，我们来把这个操作思路进一步推广。

令图5-3-6单元格E16内所示的公式作为“=C2&D2”。再在图5-3-10输入界面内，令单元格C2输入图5-3-9所示的各种关系操作符，令单元格D2输入相应操作参数。那末，高级筛选的条件区域的运算公式可以进一步地动态化了。当然，此时图5-3-10输入界面内的B2单元格内提示信息应该有相应的改变。

现在，将图5-3-6单元格E15内所示的“本月销售额”字符也改写成为公式：

=IF（D5=“M”，“本月销售额”，“累计销售额”）

这是一个条件函数。当我们在在图5-3-10输入界面内的B5单元格内填写提示信息：

请选择查询内容（‘本月销售额’键入M或‘累计销售额’键入Y）：

要求操作者把字符M或Y键入D5单元格内。这样，图书馆5-3-6的筛选操作就不会局限于本月销售额住处了。也就是说，图5-3-6工作表的操作可以更进一步地动态化了。

注意，概念叙述并不等于具体操作。譬如，上面介绍图5-3-6的筛选操作公式内，涉及图5-3-10输入界面内的单元格称，在实际应用操作加上具体工作簿与工作表的标记。再如，要求操作者把字符M 或Y键入D5单元格内，如果键入失误后应该设计保证Excel软件中的工作表仍然正常工作措施。?如此等等的具体要求，应该在实际应用时考虑全面。另外真正用的输入界面一般采用模板或宏技术实现，决不会像图5-3-10输入界面那样简单。工作表可以动态化，数据信息的动态化操作技术的应用，其关键还在于公式与函数的编辑。

③ D函数及其应用

在Excel软件中，有一类专门用于数据库管理的函数集合，它们被称作D函数。这里以常用的求和函数SUM为例，当求和函数用于数据库管理以后，具体函数表达形式就改变为：

DSUM（Satabase Range,Field,Criteria Range）

所有的D函数都有上述括号里的三个工作参数。它们是数据库区域Dstsbsse Rsnge,具体包括代表数据库的那个工作表里的有关单元格集合。而且必须包含标记互不相同段名的标题行。

字段Field具体包括参数与数据运算操作的某一字段。

条件区域Criteis Rsnge具体包括指定操作条件的那些单元集合。

这里，再以图5-3-6所示实例来说明DSUM函数的应用，如图5-3-11所示。假设工作表内已有条件区 域为D15：D16，当在F16单元格内键入DSUM函数DSUM （B3：I13，“本月销售额”，D15：D16）以后，函数内的数据库区域Database Range即为B3：I13，字段Field即为本月销售额，条件区域Criteria Rsnge即为工作表内已有条件区域D15：D16。

DSUM函数的具体操作要求是：把工作表内B3：I13数据库区域中的本月销售额，根据条件区域D15：D16所示要求求和（即把数据库记录中，所在要区为“北”的全部客户单位的本月销售额求和）。按图5-3-11所示：北区的客户单位共有万方贸易公司与弘业证券公司两个，它们记录内的本月销售额分别为9569元与此同时12397元，因而，求和的结果在F16单元格内显示为9569元+12397元=21966元。

D函数的具体形式有许多，它们都有在普通的操作函数前面加上一个D字来表达。大致有把工作表看数据库以后，只要充分调用D函数的操作，就有可能使工作表的数据处理结果进一步动态化。

5.3.3 动态数据分析工具——数据透视表

1 理解数据透视表

数据透视表是组织数据的软设备，可在数据透视表里指定想显示哪些和项，以确定如何组织数据。字段 是一般分类，项是分类里的个别数值和实例。例如，在图5-3-12所示的数据透视表里，公司名称是字段，月份是项。

数据源可以是数据清单或在Excel工作表里的表格，甚至另一个程序创建的数据。

多重数据源可把数据导入数据透视中。如上所述，在本章里，数据清单一词指的是Excel工作表里的数据清单。比如表格数据和多列表格形式的数据，无论在Excel工作表或另一个程序创建的文件里。外部数据另一个程序创建的数据。

创建数据透视表时，指定行、列和页字段。在普通工作表里，只能观察二维表格的行和列。类似地，在数据透视表里，用户一次只能观察单个页字段。然而，可以想象在位上堆叠页，参见图5-3-13。

虽然数据透视表显示的数据类似于任何其他的工作表数据，但在数据区里不能直接输入或修改数据。数据透视表自身连接源数据，在表的单元格里观察的是只读数据。然而，可以修改格式化并从从很多计算选项选定所需数据。

大多数Excel数据清单类似于图5-3-1。这些数据清单包含在容纳指定类型信息的列里排列的多行住处例如图5-3-1的数据是按日期排序各外部公司应收账款数据清单项。

虽然这个数据库包含丰富的信息，但[经难以形成任何类型的综合视图。这正是用户需要使用数据透视表的原因。

数据透视表显示数据库分析的最终结果。利用他们，用户可以根据有尖字段去分析数据库的数值。例如图5-3-12的3个月的外部公司应收账款的情况。数据透视表向导可以帮助建立这个复杂报告。

由此可见，数据透视表是对很多目标有用的分析性报告工具，包含以下5类。

① 重新任意组织数据表格。数据透视表可以说明在数据元素之间的趋势的关系。如图5-3-14显示在重

新按公司汇总排列的应收账款情况之后的与图5-3-12相同的数据透视表。重新组织数据透视表的方法是在屏幕上拖动文字标签到不同位置。

② 创建汇总表格。数据透视表可以汇总数据清单或数据库，从而提供数据的概况视图。例如，创建大量

往来账目组到账户汇总里， 显示数据清单和外部数据库记录的平均值和统计值。

③ 筛选数据透视表数据和创建数据组。检查数据时，使用互相观察踪迹。其他时候，观察数据的了集合。

数据透视表允许瞄准数据数据，例如图5-3-14显示了与图5-3-12相同的数据，但只显示一种产品的销售量。

④ 从外部数据库中提取数据，在对现有数据进行分析时，不仅限于Excel表中的数据，而且可以对如

ACCESS 等的数据进行引用。

⑤ 创建数据透视表图表。虽然数据透视表是好演示工具，但图表可能更好。创建数据透视表额头是很方

便的，处理数据透视表时该图动态变化。

2 如何建立数据透视表

前面说过，数据透视表也是一种表格，可是这种表格是建立在数据清单和数据库之上的。也就是说，建 立数据表必须有数据清单或数据库作为数据来源，通过变换数据清单或数据库上的行列结构来生产不同的组合效果，得到不同和数据透视表。

数据透视表的建立方法并不难，在建立好数据清单或数据库后，可从“数据”菜单中选定“数据透视表”，就可进行建立数据透视表的工作。Excel给出四个步骤，并且提供了很好的向导，它可以带您 踏上未知之旅。只要按照Excel建立透视表的要求亦步亦趋地跟着走，就可到达胜利的彼岸。

下面以“产品销售收入情况表”数据清单作为数据来源，可以利用以下7步来建立数据透视表。

① 打开或切换到为数据源的数据清单“产品销售收入情况表”，并选定其中一个单元格。

② 打开“数据”菜单，单击“数据透视表”命令，屏幕上建立透视表的第一步对话框，如图5-3-15所

示。

③ 在该对话框中，Excel 要求选取定“创建数据透视表的数据源”，共四个单选按钮，缺省情况是Excel

的数据清单或数据库。在对话框下脚有一个问号按扭，单击该按钮可以启动office向导。

④ 单击“下一步”按钮，此时屏幕上弹出建立数据透视表的第二步对话框，在该对话框是，Excel要求

选定参数与建立透视表的源数据范围，缺省情况是选定整个数据清单。

⑤ 单击“下一步”按钮，屏幕上弹出建立数据透视表的第三步对话框，如图5-3-17所示。这是最重要

的一步，它决定着数据透视表的布局。可把对话框右边的定段拖到对话框左边标有“行”、“列”、或“数据”的位置上，拖到“行”位置的字段在即将造成的透视表中就成为标题；同样，拖到“列”位置的字段就成为列标题，而拖到“数据”位置的安段就全得到该字段的汇总信息。

⑥ 在完成透视表的布局后，单击“下一步”按钮，屏幕上弹出建立透视表的第四对话框，也是最后一

步。在该对话框中，Excel要求选定数据透视表的显示位置，缺省的选择是将数据透视表放到一个新的工作表中，也可放中本工作表上。

⑦ 单击“完成”按钮，则数据透视表的建立工作就此结束，屏幕上显示定义的数据透视表，如图5-3-18

数据透视表——步骤之4。同时，您还可以在屏幕上看到一个标有“数据透视表”的浮动工具栏，利用该工具栏的按钮，以完成相应的操作。

注：用于建立数据透视表的数据清单一定要有列标题，并且不能含有汇总数据，否则，Excel无法建立透视表。

3 编辑数据透视表

数据透视表是显示信息的设备，所以不能修改表体上显示的任何数据。但Excel确实提供很多工具去控制在数据透视表里的汇总信息的类型和格式。

（1）如何在数据透视表中选定数据

任何事物的发展都以其内在的需求作动力，建立数据透视表目的仍是为了满足工作需要。建立的数据透视表虽然不错，但仍有可能对其进行动态调整如设置格式、数据项排序、创建数据组德行。这就要求在透视表中选定操作的数据，数据透视表中选定数据与变通工作表中选定数据有不少差异，因此值得专门讨论。

“选定数据”一词在Excel透视表中包含的范围很宽，既可选定全部透视表，也可只选定一字段项标志，还可以选定某一数据项的实例等等。

由于建立透视表后，Excel透视就会当前透视表的窗口中显示“数据透视表”工具栏，即数据透视浮动工具栏。您就可以通过该工具栏，查看透视表数据的选定情况。

（3）在数据透视表中选择数据的步骤

① 若要选定整个数据透视表，您可以打开“数据透视表”工具栏中“数据透视表”菜单，单击“选定”

命令，在随后弹出的选项中单击“整张表格”；也可以直接单击透视表的A1单元格。这两种方法都可选定整个数据透视表达式。

② 若要选定某一字段的所有字段项标志（如“产品名称”字段，它的取值“蓝星啤”、“蓝星听”、“蓝星

扎”等就是该字段的标志），单击该字段按钮即可，如图5-3-19所示。此时，“数据透视表”工具栏中的“选定标志”按钮呈选定状态。由于单击了字段标志，Excel也同时选定了相关数据。

③ 如果只想选定标志而不选定数据，可单击“数据透视表”工具栏中“标志”按钮。

④ 如果您只选定数据而不选定标志，可时单击该工具栏上的“数据”铵钮。

⑤ 若要选定某一数据项的所有实例，单击该数据项即可。如单击“蓝星啤”，则Excel选定该透视表包

含“蓝星啤”在不同销售数据地区的销售数据及汇总信息便逐条列出。

⑥ 如果只想使选定内容中只包括数据项的当前实例，可以单击该数据项，再单击若干次就可以筛选数据

了。

⑦ 如果数据透视表中有几个行字段，那么可以重复进行单击操作，直到选中所需的数据项。

⑧ 也可选择多个数据项。首先单击必要的次数以选择所需的一个数据项，然后按Shift键，或按Ctrl键，

以选择同一字段的其他数据项。按Ctrl键单击还可以取消对数据项的选定。

4 指定源数据

以上讲了利用Excel工作表里的数据清单或表格来制作数据透视表，掌握了如何选择数据透视表的数据。 除了利用Excel工作表里的数据清单或表格外，可以使用其他程序创建的数据作为数据透视表的源，也可使用一个或多个Excel工作表里的多重数据清单。

（1） 若要从当前Excel工作簿现存数据清单创建数据透视表，选定第一个数据透视表向导对话框里的

Microsoft Excel数据清单或数据选项。

源数据清单应当包含列标记，确保在数据透视表向导的“4步骤之2”里输入的范围包含列标记。Excel 使用范围首行里的数值作为字段名。

(2) 使用另一个工作簿的数据汇款单或数据库

若要想指定另一个工作簿里的数据清单，遵循以下步骤：

① 在数据透视表向导的“4步骤之2”里选择浏览按钮，将显示浏览对话框；

② 选定包含想要的数据清单文件，并选择确定按钮返回数据透视表向导；显示“4步骤之2”对话框， 在区域编辑框里显示文件名；

③ 输入源数据清单的名字或范围地址；

④ 选择“下一步”按钮，完成剩下的数据透视表向导的对话框。

（2） 使用外部数据库

① 若想使用外部数据源，在第一个数据透视表向导对话框里选定“外部数据源”选项，并选择“下一步”

按钮，出现“4步骤之2”对话框，如图5-3-20所示。

注：若想从外部数据源检索数据，要在安装Excel时安装了Microsoft Query。

② 选择“获取数据”按钮。启动Microsoft Query程序并显示类似于图5-3-21所示的对话框。

③ 选择数据源在Microsoft Query里，执行一系列操作去定义想要引入Excel的数据。图5-3-22显示例的

查询定义表。在定义了查询之后，从Microsoft Query文件菜单里选择将数据返回到Microsoft Excel命令。选取想要查询的字段，进入筛选定义窗口，见图5-2-22查询向导界面。

④ 更新对外部数据源连接的数据透视表，引起Excel查询该数据源。输入想要查询数据的过滤条件，如

图5-3-23的所示。

⑤ 返回数据透视表向导的“4步骤之2”，数据透视表按所筛选条件，形成数据透视表。

5 创建页字段去筛选数据

以前人类不可能阅读三维文字和图形，所以想在数据透视表里观察的全部字段必须在数据透视表向导的 “4步骤之3”里设法放进列的位置里。

然而，数据透视表可以设立第三维去提供检查数据的附加灵活性，创建面字段创建排序的视图筛选。若要知道这个过程如何工作，首先观察图5-3-18的数据透视表。这个数据透视表仅显示工作代码或项目字段名，因此显示的数据反映了全部。

若想添加在项目或工作代码之间翻转的选项显示这些字段里任何别项数量，则需要创建页字段。

若想在创建数据透视表时创建页字段，遵循以下步骤：

① 启动数据透视表向导，完成数据透视表向导“4步骤之1”和“4步骤之2”：

② 在数据透视表向导的“4步骤之3”对话框里移动想筛选的字段到页步长（P）区。它可以是未显示的

字段，或是在行或列位置里显示的字段。

③ 选择“确定”按钮，继续数据透视表向导里剩下的对话框。在添加页字段之后，数据透视表类似于图

5-3-24，它只显示某一产品的数量和销售金额情况。单击页字段里的箭头，显示页字段里全部项的带汇总选项的列表。简单地选定想观察的项，数据透视表则显示该页。

6 如何更新透视表数据

既然数据透视表是基于数据清单或数据库的，那么，数据清单或数据库一旦发生了变化，不管我们把数 据透视表美化得多么好都有无济于事，因为它已不能反映其真实情况，这也就失去了透视表存在的价值。

因此，当然数据源发生了变化，比如修改了数据或增添了字段字或记录，那就必须及时在透视表中反映出来才行。

Excel对更新数据透视表的问题早有准备，因为数据清单随着时间的推移，增减内容、修改错误是很正常的。如果仅仅是数据清单中的数据发生变化，那么单击工具栏上的一个按钮就行了；若是增减字段或记录，只需要在建立数据透视表的相应对话框中做修改也就解决了。

假定我们只是修改“销售清单”数据单中和部分数据，比如把地区A蓝星啤在8月1日的销售数量1265

改为2265，可以利用下步骤来更新数据透视表：

① 打开“销售情况表”数据清单；

② 修改蓝星啤在8月1日的数据；

③ 打开或切换到需要更新的数据透视表；

④ 单击“数据透视表”工具栏上的“更新数据”按钮，如图5-3-25所示；此时可看到当前透视闪动一

下，数据更新的工作就在瞬间完成了，您可以自己加以验正。

假定“销售情况表”数据清单中增加了“销售员”字段，并添加了相应的数据，可以利用以下步骤来更新数据透视表：

① 打开或切换到需要更新的数据透视表，并选定在任一数据单元格；

② 单击“数据透视表”工具栏上的“数据透视表向导”按钮；

③ 屏幕上弹出建立透视表的第三步对话框，单击“上一步”按钮，回到第二步对话框；

④ 在该对话框中可以看到原有的范围中没有包含“支付方式”字段。为此，修改范围地址，使其地址变

为“$A$1：$H$21”；

⑤ 单击“下一步”按钮，返回第三步骤对话框；

⑥ Excel把“销售员”字段为按钮也放在数据透视区的右边，可以拖曳该字段到“行”或“页”位置，

使其为透视表的行标题或页标题等。

⑦ 假定把“销售员”拖曳到“行”位置，然后单击“下一步”按钮，在第4步对话框中单击“完成”按

钮，此时，可在随后的透视表中看到该行字段字段，如图5-3-26所示。

7 使用数据透视表合计并计算数据

人的欲望总是无止境的。前面已经给出许多累加数据的透视表，这在一定程度上满足了一些需要，但是， 汇总并是仅仅是求和，有时候还需得到平均值、最大值、最小值汇总情况，在这种情况下Excel是如何考虑的呢？

Excel对汇总问题给予充分的重视，它不仅考虑了单纯的汇总计算，包括求和、求平均值等等，而且还提供了许多基准比较和百分比计算，使您不仅能得到汇总数据，还能看到每种汇总数据所占的比例。为了得到这种汇总形式，只需要改变一下显示方式就可以了。

以前面建立的数据透视表为例，假定我们不仅希望看到每个销售员的业绩，而且还想了解他们在整个工作中所占的比例，可以利用以下步骤眯改变汇总方式：

① 打开或切开换到预改变汇总方式的数据透视表，并选定任意数据单元格；

② 单击“数据透视表”工具栏中的“数据透视表字段”按钮，屏幕弹出如图5-3-27数据透视表字段； ③ 在该对话框中，有“源字段”、“汇总方式：等，在”汇总方式”列表框中仍选定“求和”选项； ④ 由于我们想了解每个销售员的业绩及他们在每个工作中所占的比例，所以单击对话框中的“选项”按

钮，弹出“显示方式”列表框；

⑤ 在“显示方式”列表框中选定“占总和的百分比”选项；

⑥ 单击“确定”按钮，之时，在透视表中看到“求和项：数量”已变成了百分比，每个人的工作成绩一

目了然。

这时，透视表中“求和项：数量”中的具体数值没有了，而是被百分比占用，如果需要把产品名称依然 显示在透视表中，则需调出“数据透视向导”进行弥补，方法是：

在“数据透视表”工具栏上单击“数据透视表向导”按钮；

在随之出的第三步对话框中，选定对话框右边的“数量”字段按钮，向右边的数据透视视区拖，直到“数据”位置，再松开左键（根据需要，您可以在数据区放置统一字段的各个汇总数据）；

单击“完成”按钮，屏幕上的透视表中又增加了“求和项：数量”，不过，这个求和项不再是显示比例，而是显示实际数量值。

8 创建合并计算多个数据区域的数据透视表

从多个数据清单或工作表中合并计算数据时，所要合并计算的所有项必须具有同样的行标题和列标题。 创建数据透视表时，不应包括数据源中的整行或整列。

若要使数据透视表更易于更新并且当数据源改变时它也能随之更新，应为每个源区域命名，而且在创建数据透视表时还应使用这些名称。如果命名的区域又扩包含了更多的数据，通过更新数据透视表可加入这些数据。

合并计算使用自定义的页字段来显示每一个源数据区域，页字段中的每一字段项都代表一个或多个源数据区域。例如，在合并市场、销售和制造部门的预算数据时，页字段中有一项代表每一个部门的数据，还有一项显示合并计算后的数据。

如果单击“数据透视表向导——4步骤之1”对话框中的“多重合并计算数据区域”，则右选择所需页字段的方式。

① 可为每个源区域建立一个页字段，也可以只建立一个页字段来显示所有源数据区域的合并计算。请单

击“数据透视表向导——4步骤2”对话框中的“自动建立单页字段”。

② 也可创建多达四个页字段，并为每个数据区域都指定项名称，也可以创建不含页字段的合并计算。要

对部分或全部合并计算进行比较时，请单击“数据透视表向导——4步骤之2”对话框中的“自定义页字段”。

9 创建数据透视表图表

可以创建对数据透视表连接的图表。修改数据透视表的页面布局时图表动态修改。图5-3-28显示数据透 视表及从它创建的图表。注意这个数据透视表有1个行字段和1个列字段。Excel沿图表的分类（X）轴线是列字段里的项。它根据数据透视表里的行组去创建图表系列组。

若想从数据透视表创建图表，遵从以下步骤：

① 选定数据透视创建表。这样做时，避免选定任何汇总的列。还得避免拖动任何字段标签；否则，Excel

认为想移动行字段。

② 在常用工具栏上单击图表向导按钮，遵循在图向导对话框里的命令。

注：可以修改Excel在图表里创建系列表标记的方法。选定该图表后，显示图表向导并在“4步骤之2”里修改设置。

③ 图表向导在图表上部显示页字段里的项。然而，选定页字段里的项时，动态修改从数据透视表创建的

图表去显示选定项的数据。

10 修饰数据透视表

通过前面的介绍，您对数据透视表的功能及其作用已有了一定的了解，它们满足我们许多要求。透视表 不仅可以对所选数据进行分析输出，而且对生成的表格进行修饰。

（1）修改字段名和项名

假定希望修改透视表中两项求和字段名，即把“求和项：销售数量：“改为”“占总和比例”，把“求和项：销售数量2”改为“购置数量”，可以利用以下步骤进行操作：

① 打开或切换到欲修改字段的数据透视表；

② 选定“求和项：销售数量”字段，在窗口的编辑栏将其修改成“占总和比例”，然后按回键；

③ 此时可以看到，在透视表中所有“求和项：销售数量”都被“占总和比例”所代替；

④ 这时的透视表看起来还是不够舒服，那么还可以进一步修改文字、数字的格式（包括字体、字号）。 毕竟数据透视表表示汇总报表，需要经常上报，所以，除了修改字段外，还希望它有更专业的外观， 下面我们就设法让透视表自动套用“自动格式”中的“经典3”格式，操作步骤如下：

① 选定透视表中欲格式化的区域；

② 打开“格式”菜单，单击“自动套用格式”命令；

③ 在随之出现的“自动套用格式”对话框的“格式”列表框中，选定“经典3”格式，然后单击“确定”

按钮。

此时，屏幕上的数据透视表就按照“经典3”的格式重新进行了编排。

（2）格式化数字

创建新数据透视表时，Excel对数据区单元格应用工作表的常规单元格式的数字格式化。然而，可以修改这些格式。

若想对数据透视表数据区应用不同数字格式，循环以下步骤：

① 在数据透视表数据区里选单元格；

② 在数据查询/数据透视表工作栏上单击数据透视表字段按钮，将显示数据透视表字段对话框；

③ 在数据透视表字段对话框里选择“数字”按钮；

④ 在弹出的单元格格式对话框中置前数字标签；

⑤ 用通用的方法选定想要的数字格式，然后选定按钮。该字段的全部单元格保持选定的数据格式，无论

数据区是否改变形状。

11 保存带数据透视表的文件

有时带数据透视表的文件会令人大吃一惊，因为Excel创建源数据的复制并用包含数据透视表的工作表作为隐藏数据保存它。数据透视表引用另一个文件的在量数据时，无论何时保存包含数据透视表的文件，保存相同数据两遍。

若想避免这种重复，在数据透视表向导的“4步骤之4”里撤消选定数据与数据透视表格式一起保存复选框。Excel接着保存数据透视表页面布局但省略源数据的复制。修改——或刷新——数据透视表时，Excel直接从源数据更新它。注意使用保存数据的数据透视表作为另一个数据表的源时，不再用原始数据透视表保存数据。

5.3.4 动态数据分析实例

如上所述，我们已经了解了分析的重要性，并且掌握了一定的对数据分析的方法，即对数据透视表的使用。在小节中将对动态数据分析做一具体的实例讲解。

首先介绍“动态数据分析”的概念。所谓“动态数据分析”是指对现有的基础数据进行整理录入，然后通过一系列的Excel的分析工具，如数据透视表等，自动产生一系列的分析数据、分析报表，以供使用。

如果对Excel中的一些分析要具熟练应用，一定会充分体会到动态数据分析的重要性及其实用性的。该实例结合了恒远报表管理系统的某些特殊功能，比如模板的概念等。

该实例主要引用的是某证券公司的上报报表，报表具体内容主要包括资产负债表、损益表、固定资产折旧明细表、营业费用明细表。在资产负债表、损益表、营业费用明细表等报表中的所有数据都能以恒远报表管理中的模板定义和取数公式定义为基础，通过“报表生成”按钮来实现的，如图5-3-29营业费用明细表模板的定义。

在此模板中，利用了恒远报表管理中的本期发生涵数BQFS（），自动读取某月营业费用明细科目的本期发生数。本年累计数栏利用了怛远报表管理中的表间取数定义。累计数利用了单元格与单元格之间的关系，如二月份的营业费用明细表中职工工资的本年累计数是一月的本年累计数加上二月份的本期数得出二月的职工工资的本年累计数，即本年累计数前一月的累计数+当月的本期发生数。由于职工工资的本期是可以通过怛远报表管理系统的取数公式定义来完成的，职工工资的本年累计数也就自然产生。由此可见，以上这一简单定义就能体现出各个工作中不同数据的动态连接，只要基础数据做好维护，即账务管理中的数据正确，则可以保证报表中取数的正确性。每月的会计报表，

同理可以制作资产负债表、损益表等一些能从账里取出发生数的值，均可通过恒远报表管理系统中的取数公式定义功能来完成，具体定义方法，详见5.1.1“节模板的功能及其创建”。

以上是利用恒远报表管理系统的一些功能，实现财务报表自动生成的。这是恒远公司为广大财务工作者提供的一种良好的财务电算化工具，解除了财务人员在月底出报表时的繁琐工作，化复杂为简单，使以前需要很长时间完成的工作，现在只需几秒钟就能完成，真正实现了财务的智能化管理。

对于没有一点儿计算机基础的财务人员来说，我们可以提供诸如恒远报表管理系统软件等一系列的已经开发好的财务、报表软件，来辅助财务人员的工作。而对那些有了一定计算机基础的财务人员呢？特别是读过本书的人员来说，可经利用已有的Excel各种分析工具，制作出对自己工作有帮助的各种较为复杂的财务分析报表。

图5-3-30是某证券公司的工资模板。该张工资表中的数据由固定字段、基础数据、连接数据组成，固定字段和基础数据是需要人工录入的，而效益工资、应发工资、代扣税、实发工资都可依靠Excel的公式定义来完成。例如效益工资是根据员工每月的出勤天数而定的，如出勤天数多于22天的员工，效益工资为670元，出勤天数在15到22天之内的员工，效益工资为500元，否则效益工资为400元。以上根据出勤天数计算效益工资的方法，如果手工计算，可能会很麻烦。在此利用了Excel中的IF函数，根据员工的出勤天数（即工时），决定应发员工效益工资的多少。根据以上条件，定义公式如下：

“=IF（B4>22，670，IF（B4>15，500，400））”

同理可以定义代扣税等需要条件定义的公式。有了IF函数和定义，只要人工录入少量基础数据，就可以得出关联的其他有关数据。

以上是一张员工工资表的简单编制，每月财务人员只需输它入员工出勤天数，一张完整的工资表就自动产生了。我们可以在Excel中做一个文件，把另存为扩展名为xlt(*.xlt)的形式，即模板的形式，每次只需在文件菜单下选择“新建”，在常用项中选择工资表模板即可。这是利用Excel的模板功能建立常用工作表的基本流程，方法很简单，但很衫。还有一种更为简单的方法，则是利用恒远报表管理软件这一开发好的财务软件工具。在恒远报表管理的“模板册管理”中定义好员工工资表，那么工资表可以连同每月的资产负债和损益表等财务报表一起产生，不需要界面的重复切换。

为了更好地理解数据之间的动态连接，假定这样一种情况：由于员工工资表中如工时一类的基础数据是由手工输入的，有时会产生某些误差，那么如何判断数据是否正确呢？我们还是利用恒远报表管理系统的功能来决解它。以某证券公司上报报表为例，它的营运费用明细表（如图5-3-29）中的职工工资的本期发生数，应该等于员工工资表（如图5-3-30）中员工工资实发金额的合计。恒远报表中提供审核公式定义的功能，帮助您判断。