用Excel函数自动录入性别与生日.doc

用Excel函数自动录入性别与生日

摘要：在我们的日常生活中，经常遇到输入某些人的基本信息。比如：在学校的招生工作中，要录入学生的“性别”与“出生年月”等。使用Excel函数，通过身份证编码能够地自动录入。能够做到简便、快速、且不易出错。
关键字：函数；公式；身份证编码
作者简介：
一、明确概念：
1、什么是函数？
　　Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。
　　参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。
　2、什么是公式？
　　函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的计算式。
　　以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。
　　如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。
二、函数分析
　在用Excel函数自动录入性别与生日中，用到的函数有：
1、LEN函数返回文本字符串中的字符数。
　　语法:LEN(text)
Text是要查找其长度的文本。空格将作为字符进行计数。例如：

　　2、MOD函数返回两数相除的余数。结果的正负号与除数相同。
　　语法:MOD(number,divisor)
Number为被除数;Divisor为除数。例如：

　　3、MID函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。
　　语法:MID(text,start_num,num_chars)
Text为包含要提取字符的文本字符串;Start_num为文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推;Num_chars指定希望MID从文本中返回字符的个数。例如：

4、DATE函数返回年月日。
语法：DATE(year,month,day)。例如：

5、IF函数执行真假值判断，根据对指定条件进行逻辑评价的真假而返回不同的结果。
语法：IF(logical-test,value-if-true,value-if-false)
若Logical-test表达式成立，返回value-if-true，否则返回value-if-false。例如

在某些情况下，您可能需要将某函数作为另一函数的参数使用。例如，上面的公式使用了嵌套的MOD函数。公式可包含多达7级的嵌套函数。当函数B在函数A中用作参数时，函数B则为第二级函数。例如MOD函数是第二级函数。因为它是IF函数的参数。在MOD函数中嵌套的函数为第三级函数，以此类推。下面，我们将会应用到嵌套函数。
三、应用
根据现行的居民身份证号码编码规定，正在使用的18位的身份证编码。它的第17位为性别(奇数为男，偶数为女)，第18位为效验位。而早期使用的是15位的身份证编码，它的第15位是性别(奇数为男，偶数为女)。为了适应上述情况，必须设计一个能够适应两种身份编码的性别与出生年月的计算公式。
1、性别：

在上图的E列依次输入身份证号，要求在C列输出学生的性别。在C2单元格中输入如下公式： “=IF(LEN(E2)=15,IF(MOD(MID(E2,15,1),2)=1,"男","女"),IF(MOD(MID(E2,17,1),2)=1,"男","女"))”。回车后即可在单元格获得该学生的性别，而后只要把公式复制（将插入点放在C2单元格的句柄处，按住鼠标左键不松手向下拖动）到C3、 C4等单元格，即可得到其他学生的性别。
　　为了便于大家了解上述公式的设计思路，下面简单介绍一下它的工作原理:该公式由三个IF函数构成，其中“IF(MOD(MID(E2,15,1),2)=1,"男","女")”和“IF(MOD(MID(E2,17,1),2)=1,"男","女")”作为第一个函数的参数。公式中“LEN(E2)=15”是一个逻辑判断语句，LEN函数提取E2等单元格中的字符长度，如果该字符的长度等于15，则执行参数中的第一个IF函数，否则就执行第二个IF函数。
　　在参数“IF(MOD(MID(E2,15,1),2)=1,"男","女")”中。MID函数从E2的指定位置(第15位)提取1个字符，而MOD函数将该字符与2相除，获取两者的余数。如果两者能够除尽，说明提取出来的字符是0(否则就是1)。逻辑条件“MOD(MID(E2,15,1),2)=1”不成立，这时就会在C2单元格中填入“女”，反之则会填入“男”。
如果LEN函数提取的E2等单元格中的字符长度不等于15，则会执行第2个IF函数。除了MID函数从E2的指定位置(第17位，即倒数第2位)提取1个字符以外，其他运算过程与上面的介绍相同。
2、出生年月：

快速输入学生的出生年月与上面我们介绍性别的快速输入的思路相同。在D2单元格中输入如下公式： “=IF(LEN(E2)=15, DATE(19&MID(E2,7,2),MID(E2,9,2),MID(E2,11,2)),DATE(MID(E2,7,4),MID(E2,11,2),MID(E2,13,2)))”。回车后即可在单元格获得该学生的出生年月，而后只要把公式复制到D3、 D4等单元格，即可得到其他学生的生日。
　 D2与C2单元格中都使用了嵌套函数。其中“DATE(19&MID(E2,7,2),MID(E2,9,2),MID(E2,11,2))”和“DATE(MID(E2,7,4),MID(E2,11,2),MID(E2,13,2))”作为第一个函数的参数。公式中“LEN(E2)=15”是一个逻辑判断语句，LEN函数提取E2等单元格中的字符长度，如果该字符的长度等于15，则执行参数中的第一个DATE函数，否则就执行第二个DATE函数。
　　在参数“DATE(19&MID(E2,7,2),MID(E2,9,2),MID(E2,11,2))”中。在19&MID(E2,7,2)中，19与MID函数从E2的第7位开始提取的2个字符进行文本连接运算，得到的字符串作为DATE函数的第一个参数。DATE函数将返回日期。
如果LEN函数提取的E2等单元格中的字符长度不等于15，则会执行第2个DATE函数。上面已介绍过，不再赘述。
通过身份证编码自动录入的性别与生日后，表格如下：

8