1、1用 Excel 函数自动录入性别与生日作者：陈洪敏单位：徐州技师学院地址：徐州西郊丁楼-徐州技师学院邮编：221151摘要：在我们的日常生活中，经常遇到输入某些人的基本信息。比如：在学校的招生工作中，要录入学生的“性别”与“出生年月” 等。使用Excel 函数，通过身份证编码能够地自动录入。能够做到简便、快速、且不易出错。关键字：函数；公式；身份证编码作者简介：陈洪敏（1979- ），女，江 苏省徐州技 师学院公共服务系教师，在职硕士 。邮箱：一、明确概念：1、什么是函数？Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数 SUM 为例，它的语法是“SUM
2、(number1,number2,)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟2左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。2、什么是公式？函数与公式既有区别又互相联系。如果说前者是 Excel 预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的计算式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括
3、函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则 是对单 元格 A1 的引用(使用其中存储的数据)， “26”则是常量，“*”和“+”则是算术运算符( 另外还有比较运算符、文本运算符和引用运算符)。如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。二、函数分析在用 Excel 函数自动录入性别与生日中，用到的函数有：31、LEN 函数返回文本字符串中的字符数。语法: LEN(text)Text 是要查找其长度的文本。空格将作为字符进行计数。例如：2、MOD 函数返回两数相除的余数。结果的正负号与除数相同。语法: MOD(
4、number,divisor)Number 为被除数;Divisor 为除数。例如：3、MID 函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。语法: MID(text,start_num,num_chars)Text 为包含要提取字符的文本字符串;Start_num 为文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推;Num_chars 指定希望 MID 从文本中返回字符的个数。例如：44、DATE 函数返回年月日。语法：DATE(year,month,day)。例如：5、IF 函数执 行真假值判断，根据 对指定条件进行逻辑评价的
5、真假而返回不同的结果。语法：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 函数中嵌套的函数为第三级函数，以此类推。下面，我们将会应用到嵌套函数。三、应用5根据现行的居民
6、身份证号码编码规定，正在使用的 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,“男“,“女“)”。回车后即可
7、在单元格获得该学生的性别，而后只要把公式复制（将插入点放在 C2单元格的句柄处，按住鼠标左键不松手向下拖动）到 C3、 C4 等单元格，即可得到其他学生的性别。6为了便于大家了解上述公式的设计思路，下面简单介绍一下它的工作原理:该公式由三个 IF 函数构成，其中“IF(MOD(MID(E2,15,1),2)=1,“男“,“女“)”和“IF(MOD(MID(E2,17,1),2)=1,“男“,“女“)”作为第一个函数的参数。公式中“LEN(E2)=15” 是一个逻辑 判断语句，LEN 函数提取 E2 等单元格中的字符长度，如果该字符的长度等于 15，则执行参数中的第一个 IF 函数，否则就执行第
8、二个 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 个字符以外，其他运算
9、过程与上面的介 绍相同。2、出生年月：7快速输入学生的出生年月与上面我们介绍性别的快速输入的思路相同。在 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)”和
10、“DATE(MID(E2,7,4),MID(E2,11,2),MID(E2,13,2)”作为第一个函数的参数。公式中“LEN(E2)=15” 是一个逻辑判断 语句，LEN 函数提取E2 等 单元格中的字符长度，如果该字符的长度等于 15，则执行参数中的第一个 DATE 函数，否则就执行第二个 DATE 函数。8在参数“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 函数。上面已介绍过，不再赘述。通过身份证编码自动录入的性别与生日后，表格如下：
