Excel自动计算和数据验证

在会计方面，很都会想到使用财务软件. 但是，实际上，要用刀杀死鸡肉，有时您不需要使用Big Mac，而Excel就足够了. 作为当前最受欢迎的办公自动化软件，Excel绝对不值得一举成名. 其灵活的自动计算和数据验证功能使其在准备工资和财务报表时非常有用. 在工资和财务方面，有必要汇总和计算大量数据. 通常，在计算之前先对某些列进行判断，然后根据判断结果验证数据的正确性. 最终，决定了哪种计算方式. 它还必须具有一定程度的容错能力，可以自动捕获一些不符合要求的输入，并给出相应的错误信息excel验证公式，以便操作员可以及时进行纠正. 具有上述功能的专用财务软件自然可以胜任，但是有一种大炮击中蚊子的感觉. 对于Excel，尽管它是小米加步，但只需要稍微挖掘它的潜力，它也可以比飞机大炮强大. 好吧，不用多说，让我们以准备工资报告为例，介绍如何使用Excel2000强大的计算和数据验证功能.

首先，我们创建一个工资统计报表，该表分为两部分，第一部分是主要工资部分，它反映了工资表的内容（见图1）；第二部分是薪水表的基本数据部分，这里称为基本表（见图2）. 这两个部分合并在一个工作表中.

实现步骤:

1. 创建图1所示的工资报告样式和图2所示的工资报告的基本数据部分样式.

2. 修改手动输入列的字段名称: 显然，应使用密码保护最终工作表以防止泄漏. 尽管需要加密，但是它不会误伤好人. 它应该既集中又民主. 有些字段应允许操作员进行实时更改（例如姓名，性别，出生年份，工作年份，职务代码，技术职务代码等），并且应打开这些字段. 非常简单，单击这些字段所在列顶部的字母栏，将其选中，然后单击鼠标右键，在弹出菜单中选择“格式化单元格”，切换到“保护”选项卡，确保清除了“锁定”复选框（图3）.

3. 设置自动计算列的背景颜色: 为了使自动计算列的显示更加突出，并提示操作员，这些列不需要修改，我们可以修改这些列的背景颜色. 单击这些列顶部的字母栏，将其选中，然后单击鼠标右键，在弹出菜单中选择“设置单元格格式”，切换到“模式”选项卡，然后将单元格阴影设置为蓝色（当然，也可以是其他颜色）（如图4所示）:

4. 创建一个基本表: 从图2所示的基本部分中获得一些字段（例如工作名称，技术职位，工作等级，工作系列，工作薪水，年度职能津贴，技术等级职称津贴），或者可以计算得出，因此操作员无需手动输入. 我们在同一工作表的右侧创建图2中所示的表，然后输入必要的内容.

5. 好的，现在我们要讨论本质部分excel验证公式，即使用Excel函数来设置自动填充数据列的计算公式，Excel提供了大量的应用程序功能，可谓18种武器. 只是想出一种足以取胜的方法. 让我们以搜索功能LOOKUP为例.

获得武器时，您必须熟悉其性能. 函数LOOKUP可以在单行区域或单列区域（向量）中找到该值，然后在第二个单行区域或单列区域中的同一位置返回该值（请指定以获取详细信息）（请参阅相关的帮助文档. ）

（1）设置“职位名称”列的自动填充公式: 在“职位名称”列中选择单元格F6，然后在工具栏下方的公式输入框中输入以下公式:

= IF（E6 =“”，“”，LOOKUP（T（E6），R $ 6: R $ 16，S $ 6: S $ 16））（如图5所示）:

该公式中的表达式表示单元格F6的内容是根据上一个单元“邮政编码”的单元格E6中的内容自动填充的. 如果E6为空，则F6为空，否则R6在该列的6-16行中的单元格E6中查找值. 如果找到，则将S列中的6-16行中的值填充到F6中. 如果在E6中输入“ 1401”，则F6将自动显示“县局局长和同等职位”. 同样，我们可以将此公式复制到F列中F6以下的所有行，以便F列中的所有单元格都具有自动填充功能. 完成: “职位名称”列

（2）以相同的方式，我们可以为以下各列设置公式:

技术位置（H列）: = IF（G6 =“”，“”，查找（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，

查找（E6，R $ 6: R $ 16，V $ 6: V $ 16））

（4）年度工作津贴的公式设置（L列）: 年度职能津贴是员工工作的年数乘以10元的年度津贴，因此L6单元格的公式为:

= IF（D6 =“”，“”，（2002-INT（D6））* 10）

（5）总薪水（第N列）公式设置: 这是一个求和公式. N6单元格的公式设置如下: = K6 + L6 + M6或= SUM（K6: M6）

（6）年龄（P列）公式设置: 在这里，在计算之前，您需要验证输入数据的有效性，必须在1940-1985年间验证在C6单元格中输入的值（显然，如果是如果您的年龄不在此年龄范围内，则应该退休，甚至是非法雇用的童工. 否则，将在P列的相应单元格中显示“╳”，以提醒操作员输入的数据不正确. P6单元格的公式如下

= IF（AND（INT（C6）> 1940，INT（C6）<1985），2002-INT（C6），“╳”）

（7）资历（Q列）公式设置: 同样，在计算资历之前，请确保参加1955年至2002年之间的工作时间，否则在Q列的相应单元格中显示“╳”以提醒数据操作员输入的不正确. 在此列中的单元格Q6的公式设置如下:

= IF（OR（INT（D6）<1955，INT（D6）> 2002），“╳”，2002-INT（D6））

完成上述工作后，我们可以在允许操作的单元格中输入数据，并且相应的单元格会非常听话地自动填充数据. 如果要调整薪水，只需要更改基本数据部分中表的内容，那么薪水表将自动更新，而您不必花费时间和精力来修改整个薪水表格逐项列出. 但是，当我们预览已编制的薪水表时，我们会发现右侧基本部分中的数据也已显示. 这绝对是不可行的，不符合要求，并且很容易导致基础数据的意外修改. 但是Excel很体贴，为我们提供了一种隐藏表列的方法. 使用鼠标左键单击要隐藏的列的顶部位置，选择该列，然后单击鼠标右键，然后在弹出菜单中选择“隐藏”，则该列不会出现并弄乱再次. 这样，我们可以隐藏表格的基本部分，并且最终的薪资报告基本上可以满足安全性的需求.

将工作表设置为保护状态，以便操作员只能修改允许的单元格，并且应拒绝随意更改工资表的自动计算部分以确保数据的完整性. 设置方法是: 选择菜单“工具”-“保护”-“保护工作表”，输入密码（见图6）.

确认后，工作表保护完成.

至此，一个薪资报告系统已经完成，该系统集成了基本数据管理，自动计算并满足某些安全需求.

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/tongxinshuyu/article-193627-1.html