Excel数据库表的数据采集录入技巧

Excel数据库表的数据采集录入技巧

摘要：Excel以其灵活的数据录入和数据处理方式，成为不可或缺的数据处理工具。而Excel数据的录入规范与否很大程度上决定了后续的数据处理是否准确。本文以详述了Excel数据各种规范的采集录入技巧，旨在帮人们提高数据处理的规范性，从而提高办公效率。

关键词： Excel 数据采集录入公式函数管理需求

Excel以其灵活的数据录入和数据处理方式，成为不可或缺的数据处理工具。而Excel数据表结构是否规范，以及Excel数据采集录入和处理规范与否很大程度上决定了后续的数据处理是否准确。笔者对Excel数据的采集录入和处理规范做了较详细的归纳，为人们更好的规划设计数据库表以及高效采集录入数据提供借鉴。

一、Excel数据库表的设计规范

首先，在设计Excel数据库表时要保证所建表格具有规范合理的表体结构，即规范的二维表结构。

其次，一张Excel数据库表应该包含哪些字段，完全取决于管理需求，所以在设计数据表结构前应对管理需求做充分的调研。而且为了确保表格数据的准确性和有效性，采集数据时应该做到只要能通过公式计算取得的数据就一定不要手工录入，这样既可以保持各字段的同步更新，又可以大大减少数据维护工作量及出错的几率。

二、Excel表数据的采集录入技巧

Excel数据库源数据的采集录入是整个Excel数据库的初始化过程，工作量比较大，而且一旦发生错误，会影响后续各种数据处理结果，所以可靠性和高效性是这一阶段工作的两条重要标准。因此应尽量规范各种录入技巧，并利用公式运算减少数据录入及维护的工作量，增加数据可靠性。这里以工资表为例介

1.文本型数据的录入技巧：有些文本型数据由阿拉伯数字组成，很容易当成数值型数据来处理。事实上，它与数值型数据有着本质的区别，即它只是一个标识而已，不能由此衡量数值的大小，比如身份证号、职员编码、电话号码、邮政编码等，而且它不能参加各种数学运算。工资表中“职员代码”就是文本型，这类数据在录入时首先必须定义其数据类型为“文本”型，然后才可正确录入，而且前面要是有零也不会丢掉，也不会因为数据太长变成科学计数的形式。相反，如果不做定义直接录入，前面的零就会丢掉，数据太长时就会自动变成科学计数的形式。

2.日期型数据的录入技巧：日期型数据录入技巧和文本型数据类似，必须先定义单元格数据类型为“日期”型，然后才能正确输入相应数据。需要强调的是，不论定义的日期型是哪一种，输入时只要按YYYY-MM-DD 的形式输入数据即可，工作简化了，效率提高了。如工资表中“参加工作时间”字段。

3.用自动填充法录入数据：自动填充法是Excel中特有的数据输入方法，其用途非常广泛，常用于等差数列的批量化录入以及一些公式的复制，如工资表中的“序号”字段就可用自动填充快速录入；而“应发工资”可以用“底薪+奖金+津贴”的公式先计算出第一位员工的，然后用向下自动填充的方法计算出其他员工的“应发工资”。

4.重复字段值的快速录入：有些字段总是重复一些特定的值，比如工资表中的“性别”和“部门”，无论数据库表有多少记录，这两个字段的取值无非就是那几个特定值中的一个。对于这类数据我们可以用一些偷懒的办法来提高数据录入速度。比如可以利用“数据”菜单的“有效性”设置“性别”或“部门”两列数据的“序列”值（如性别一列的序列值为“男,女”，每两项之间用英文逗号分隔）来形成输入值列表，然后在工作表的“性别”或“部门”两列单元格选择录入相应的值；还可以用多个区域同时输入相同值的方法；还可以先全部以重复次数较多的值填充，然后改掉其他值重复次数较少的单元格。

5.自定义类型的数据的录入：有些字段值有着特殊的格式，例如工资表中“缺勤天数”字段，该字段

值如果带有“天”等单位，则阅读起来清晰明了，但如果录入每个字段值时都带有单位，则输入起来就很麻烦，而且这样的值不再具有数值的特点，不能进行加减乘除等运算。所以在用“缺勤天数”乘以日扣款标准运算取得“缺勤扣款”字段值时就会出错，这时就可以定义“缺勤天数”字段值所在单元格数据类型为自定义型#0”天”，通配符#0表示一位或两位数字，这样虽然看上去该字段值非纯数值型，但在运算中却可以当成纯数值型处理，计算起来简便易行。

6.用公式法对相应字段进行运算取值：在设计数据表时往往会对数据表应该设置哪些字段及字段值的来源进行规划，能用公式计算得到的字段坚决不能手工录入。对于那些应由公式计算取值的字段，就应先将相应的运算关系抽象成数学算式，然后再据此完成Excel运算公式的编辑。需要强调的是，所有公式必须以等号开头，而且公式中涉及的标点符号都应是英文标点。例如工资表中第一位员工的“工龄”可由公式“=year(now())-year(F2)”(假设其参加工作时间的字段值所在单元格为F2)计算取得，其他员工“工龄”则由第一位员工的“工龄”利用相对地址引用进行公式复制(向下自动填充)取得；再如由于我国工资薪金按照超额累进税率征收“所得税”，而应纳个人所得税税额=(应纳税所得额－扣除标准)×适用税率-

速算扣除数，这里将该运算关系抽象成Excel公式

“=IF(K3<=0,0,IF(K3<=500,K3*5%,IF(K3<=2000,K3*10%-25,IF(K3<=5000,K3*15%-125,IF(K3<=20000,K3* 20%-375,K3*25%-1375)))))”(根据实际需要这里只计算了五级累进税额)。总之，对于需要计算取值的字段，一定要分析其相应运算关系，并按照相应规范抽象成Excel算式并进行公式复制，才能进行自动运算、自动更新。

7.利用导入功能处理批量数据的输入和更新：有些数据是其他管理模块已经录入的，可以直接导入到本模块，无需重复录入，这样可大大提高工作效率。例如工资表的“姓名”字段，其字段值录入工作量大，而且管理的许多模块都已经存储了这些信息，我们就可以直接“导入”工资表中，避免重复劳动。

此外，还有货币型、数值型、分数、百分比等等类型数据，在录入时最好先按照相应规范设置相应数据区域的数据类型和格式，这样才能提高录入效率，并统一规范整个数据库表的结构和形式。例如某字段值为百分数，则应先设置相应区域的数字类型为“百分数”，这样在录入数据时，百分号会自动添加而无需录入，大大提高了录入效率。以上以工资表为例详述了常用的Excel源数据的采集录入技巧，而Excel数据录入处理技巧丰富而灵活，但只要将数据库知识与实践充分结合起来，切实领悟以上的每一条处理技巧的原理，就不难达到灵活运用、举一反三的程度。