朱孝天巴厘岛婚礼酒店:让Excel 2000自动计算数据 - awxann的日志 - 网易博客

让Excel 2000自动计算数据

学习 2007-07-30 21:40:00 阅读8 评论0   字号：大中小 订阅

但凡提到会计核算问题，很多人动辄就会想到要用财务软件。然而杀鸡焉用牛刀，其实有时候不必请动这个巨无霸，用Excel足矣。作为当前最流行的办公自动化软件，Excel绝非浪得虚名，其灵活的自动计算和数据校验功能，使得它在工资和财务报表的编制方面大有作为。在工资和财务中，要到对大量的数据进行汇总计算，通常要在计算之前对某些特定的列进行判断，根据判断结果来校验数据的正确性，最后决定要采用那种方式进行计算。还要具有一定的容错性，能够自动捕获一些不符合要求的输入，并给出相应的出错信息，以便操作者能够及时加以纠正。以上这些功能，专门的财务软件自然可以胜任，但未免有一种大炮打蚊子的感觉。而对Excel，只需稍加挖掘其潜力，虽然是小米加步枪，也能强过飞机大炮。好，闲话少说，下面以工资报表编制为实例，向大家介绍如何运用Excel2000强大的计算与数据校验功能。

一、报表样式：

首先我们创建一个工资统计报表，该表分成两部分，第一部分是工资主表部分，它反映工资表的各项内容（如图1）；第二部分是该工资表的基础数据部分，这里称为基础表（如图2）。这两部分合在一张工作表中完成。

图1

图2

二、实现步骤：

1、创建如图1所示的工资报表样式和如图2所示的工资报表基础资料部分的样式。

2、修改手工录入列的字段名：显然最后做好的工作表要用密码加以保护，以防泄密，加密固然需要，但也不能误伤好人，应该既有集中、又有民主。有些字段应该允许操作人员进行实时修改（像姓名、性别、出生年月、工作年月、岗位代码、技术职务代码等），应该对这些字段网开一面。非常简单，鼠标单击这些字段所在列的顶端字母栏，选中它，再单击击鼠标右键，在弹出菜单中选择“设置单元格格式”，切换至“保护”选项卡，确保“锁定”复选框清空（如图3）。

图3

3、设置自动计算列的背景色：为了让自动计算各列以更加醒目显示出来，提示操作人员，这些列不用修改，我们可以修改这些列的背景色。单击这些列的顶端字母栏，选中它，再单击击鼠标右键，在弹出菜单中选择“设置单元格格式”，切换至“图案”选项卡，将单元格底纹设置为蓝色（当然也可以是其它颜色）（如图4）所示：

图4

4、创建基础表：一些字段（如岗位名称、技术职务、岗位等级、岗位系列、岗位工资、年功能津贴、专业技术职等级津贴）是从图2中所示的基础部分取得数据或是经计算得出，因此不需要操作人员进行手工输入。我们在同一张工作表的右边创建如图2所示的表格，并输入必要的内容。

5、好，现在要讲到精华部分了──利用Excel函数设置自动填充数据列的计算公式， Excel提供了大量的应用函数，可谓十八般武器样样俱全。随便拿出一种足以制胜。就举一个查找函数LOOKUP的例子吧。

拿到一样武器，总得熟悉一下它的性能，函数 LOOKUP可以在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值（详细说明请参见有关帮助文档）。

（1）设置“岗位名称”F列的自动填充公式：选中“岗位名称”这一列的单元格F6，在工具栏下的公式输入框中输入如下公式：

=IF(E6="","",LOOKUP(T(E6),R$6:R$16,S$6:S$16))（如图5）所示：

图5

这个公式所表达的意思是，F6单元格的内容是根据前一个单元“岗位代码”E6单元格中内容，完成自动的填充，如果E6为空时，则F6为空，否则从R列的6-16行中查找E6单元格中的值，如果找到则将S列中6-16行与之对应行的值填充到F6中。如我们在E6中输入了“1401”，则F6中自动显示出“县局局长及相当职务”。同样，我们可以将此公式复制到F列的所有F6以下的行中，使所有F列所有单元都具有自动填充功能。完成：“岗位名称”列

（2）以同样的方法，我们可以设置以下各列的公式：

技术职务（H列）：=IF(G6="","",LOOKUP(T(G6),W$5:W$16,X$5:X$16))

岗位等级（I列）：=IF(E6="","",LOOKUP(T(E6),R$6:R$16,U$6:U$16))

岗位系列（J列）：=IF(E6="","",LOOKUP(T(E6),R$6:R$16,T$6:T$16))

专业技术职务资格等级津贴（M列）：

=IF(G6="","",LOOKUP(T(G6),W$5:W$16,Y$5:Y$16))

（3）岗位工资（K列）公式设置：由于岗位工资的设置不仅仅是和E列的值有关，而且要根据O列的值（布尔类型）进行判断，如果O列的值为“是”时，说明该人员是“组长”，岗位工资要增加40，所以公式设置如下：

=IF(O6="是",LOOKUP(E6,R$6:R$16,V$6:V$16)+40,

LOOKUP(E6,R$6:R$16,V$6:V$16))

（4）年功津贴（L列）公式设置：年功能津贴是该工作人员参加工作的年限乘以每年津贴10元，所以L6单元格的公式为：

（5）工资合计（N列）公式设置：这是一个求和公式，N6单元格的公式设置如为：

（6）年龄（P列）公式设置：这里在进行计算这前，需要验证输入数据的合法性，必须对C6单元格输入的值进行校验，在1940-1985这间（很显然，不在这个年龄范围的，要么应该已经退休，要么就算是非法雇用童工），如果不是，那么就会在P列相应的单元格中显示"╳"，以提醒操作员所输入的数据不正确。该P6单元格的公式如下：

（7）工龄（Q列）公式设置：同样，在计算工龄之前要保证参加工作时间在1955-2002年之间，否则会在Q列相应的单元格中显示"╳"，以提醒操作员所输入的数据不正确。该列Q6单元格的公式设置如下：

三、隐藏工资报表基础数据列：

完成以上工作之后，我们可以在允计操作的单元格中输入数据，相应的单元格就会非常听话地自动填上数据。如果要对工资进行调整，只需要改动基础数据部分的表格内容，那么工资表会自动更新，再用不着费时费心地逐项修改整个工资表了。但我们对编制好的工资表进行预览时会发现，右边基础部分的数据也显示出来了。

这样肯定是不行的，既不符合要求，也易造成基础数据的意外修改。但是Excel考虑周到，为我们提供了隐藏表格各列的方法。用鼠标左键单击要隐藏的列的顶端位置，选中该列，然后单击鼠标右键，在弹出菜单中选择“隐藏”，那么这个列就不会再出来捣乱。这样我们就可以隐藏基础部分的表格，最后形成的工资报表能够基本符合安全的需要。

四、设置工作表保护：

将工作表设置为保护状态是为了使操作员只能对允许的单元格进行修改，而对工资表自动计算部分则应拒绝随意修改，以保证数据的完整性。设置方法是：选择菜单“工具”——“保护”——“保护工作表”，输入密码（如图6）。

图6

确定后，就完成了工作表的保护。

至此，一个集基础数据管理、自动计算，且满足一定安全需要的工资报表系统就完成了。

作 者：杨志国

【网路文摘】→应用软件→Excel 专题