用Excel函数录入性别和出生年月

1、用 Excel 函数自动录入性别与生日 作者：陈洪敏 单位：徐州技师学院 地址：徐州西郊丁楼 - 徐州技师学院 邮编： 221151 摘要：在我们的日常生活中， 经常遇到输入某些人的基本信息。 比如： 在学校的招生工作中，要录入学生的“性别”与“出生年月”等。使 用 Excel 函数，通过身份证编码能够地自动录入。能够做到简便、快 速、且不易出错。关键字： 函数；公式；身份证编码 作者简介： 陈洪敏( 1979- )，女，江苏省徐州技师学院公共服务系 教师， 在职硕士 。邮箱： chenhongmingood一、明确概念：1、什么是函数？Excel 函数即是预先定义，执行计算、分析等处理数据任

2、务的特 殊公式。以常用的求和函数 SUM为例，它的语法是“SUM(numberl,number2,)”。其中“SUMl 称为函数名称，一 个函数只有唯一的一个名称， 它决定了函数的功能和用途。 函数名称 后紧跟左括号， 接着是用逗号分隔的称为参数的内容， 最后用一个右 括号表示函数结束。参数是函数中最复杂的组成部分， 它规定了函数的运算对象、 顺 序或结构等。 使得用户可以对某个单元格或区域进行处理， 如分析存 款利息、确定成绩名次、计算三角函数值等。2、什么是公式？函数与公式既有区别又互相联系。 如果说前者是 Excel 预先定义 好的特殊公式，后者就 是由用户自行设计对工作表进行计算和处理

3、的 计算式。以公式“二SUM(E1:H1)*A1+26为例，它要以等号“二”开始，其 内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26” 则是常量，“ *”和“ +”则是算术运算符 (另外还有比较运算符、文 本运算符和引用运算符 )。如果函数要以公式的形式出现， 它必须有两个组成部分， 一个是 函数名称前面的等号，另一个则是函数本身。二、函数分析在用 Excel 函数自动录入性别与生日中，用到的函数有：1、LEN函数返回文本字符串中的字符数。语法：LEN(text)Text是要查找其长度的文本。空格将作为

4、字符进行计数。例如:AB1公式结果2 -LEM("I love my home 1 and 1192、M0函数返回两数相除的余数。结果的正负号与除数相同语法:MOD(number,divisor)Number为被除数Qivisor 为除数。例如：AJB11公式结果2=MOD(3, 2)! i3、MID函数返回文本字符串中从指定位置开始的特定数目的字 符，该数目由用户指定。语法:MID(text,start_num,num_chars)Text为包含要提取字符的文本字符串;Start_num为文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推;Num_c

5、hars指定希望MID从文本中返回字符的个数。例如：AB11公式结果2=1110 7? 4)1119754、DATE函数返回年月曰语法：DATE(year,month,day)。例如:AC1年月日2；2OOSLtII1.&&3公式：I二DATE(AZ BN C2)4结果：2008-S-85、IF函数执行真假值判断，根据对指定条件进行逻辑评价的真假 而返回不同的结果。语法：IF(logical-test,value-if-true,value-if-false)若Logical-test表达式成立，返回value-if-true ，否则返回

6、value-if-false。例女口ALB'1公式：|=IF (MOD (7, 2)=2, TRUE, FALSE) I2结果：FALSE在某些情况下，您可能需要将某函数作为另一函数的参数使用。 例如，上面的公式使用了嵌套的 M0函数。公式可包含多达7级的嵌 套函数。当函数B在函数A中用作参数时，函数B则为第二级函数。 例如MOD®数是第二级函数。因为它是IF函数的参数。在 MOD®数 中嵌套的函数为第三级函数，以此类推。下面，我们将会应用到嵌套 函数。三、应用根据现行的居民身份证号码编码规定，正在使用的18位的身份证编码。它的第17位为性别(奇数为男，偶数为女)，

7、第18位为效验 位。而早期使用的是15位的身份证编码，它的第15位是性别(奇数 为男，偶数为女)。为了适应上述情况，必须设计一个能够适应两种 身份编码的性别与出生年月的计算公式。1、性别:ABCDE1学号II姓名1性别:岀生日期身份证号220070001郑美娜L女|1320324198904261860320070002丄何昙.320325198805191990420070003旧丽雷320325199008223628520070004.痢彥莹r3203241988021469536 120070005仁张健* 3203248705072597 120070006.壬翔乱.Z 320325

8、870623691820070007津政阳：320325199662082535r 9120070008颁群i3263251939090135721020070009讣亲晓娟!F 3203258706142861120070010.J.MWj.32032419880617684912 20070011:丛征: r 320323860809127.f =IF(LEN®2)<5, IF (MOD (KID (E2.15.1). 2)1,"男："女 IF (MOD (MID (EZ 17,1), 2)=1,"男笃 *)在上图的E列依次输入身份证号，要求在

9、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 函 数 构 成 ，

10、其 中 “ 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 函 数

11、从E2的指定位置(第15位)提取1个字符，而MODS数将该字符与 2 相除，获取两者的余数。如果两者能够除尽，说明提取出来的字符 是 0(否则就是 1)。逻辑条件“ MOD(MID(E2,15,1),2)=1 ”不成立，这 时就会在C2单元格中填入“女”，反之则会填入“男”。如果LEN函数提取的E2等单元格中的字符长度不等于15,则会执 行第2个IF函数。除了 MID函数从E2的指定位置(第17位，即倒数 第 2位)提取 1 个字符以外,其他运算过程与上面的介绍相同。2、出生年月：6=IF(LBNCB2)=15, DATEClSOIDCE T, 2)ID(E2, % 2)ID(E2,11, 2

12、), DATE(lllDCEN L 4) JID(E2,11, 2k MID(E2,13, 2)BCDE别 |'|4 名 姓 号 学号 证 份 身23456cS91 01 11 2nr 关 4 J 1gI".1_ fluU=_匸女- - - - - -F W _ - - -丄JB-. - - = - - - -an 4- _ -_ -_ “ -VIP- -一 - - - - - - = - -玉- _ 一 - - 00 ” - ” - -00 - .1 - -_C1 - -N)ii 00 ” -“ - i - - - -” -0O - -小- -n_ -Ji * - -_ Z

13、. - - - = - 一 - - - - - 一 - - - = - 一 - - - - - - 一 - - 厂；FL0 刘 - -八t£L. 二|2二" -£ J r 111 J - = - - - -1(.»- -rilj - - - - - -_ - 一 一-夏 - - - 一 - - -IJU - -Tr -r - - - 匚匕- - BJJJ - “ LAJ - ” "SJ - -r- 十- - - - - - - = 一 一 - = - - - - 一 - - - = 一 一 - = - - - - 一 - - - =翔 匸Q07

14、0QQ 二一 一 .KT H I- .J 旦 - 政一 ks-7 0 Q Q 0 -o2- - -一 - - 5丿- i 卩山- fclclr - .- - .I- _ - J _ i -h- p _ HM- -鬥- 11 - r丄-_- r E F PL群 伯- 008 070 _20L一 A 另_572013909zx fa-198325320FL-一 一-一 一-一 一-一 一-白冃 晓 陈 F : |_ r J F 一一一 - -A_ - -_ -_ -,I- -一 -一 -OJi _ - -i r± H -P 一 it i- -醐 _ -一 -_ - -jr"M

15、一 -_ -_ -II- -峠 - -一 -7 _ -Ct 一 - 一一 - -沪也_ -一 -»Bsr - -rF%_ H 一- *;- H - P 一 P 一i_ H 一- P 一i- H - _- 一 P 一i_ H 一-一i- H - _- 一一一囲正 so 一何丛 -1 I I ri I I I-女男_F1111 快速输入学生的出生年月与上面我们介绍性别的快速输入的思路相同。在D2单元格中输入如下公式： “ =IF(LEN(E2)=15, DATE(19&MID(E2,7,2),MID(E2,9,2),MID(E2,11,2),DATE(MID(E2,7 ,4),M

16、ID(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,则执 行参数中的第一个DATEi数，否则就执行第二个DATE

17、!数。7在参数“ 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函数。上面已介绍过，不再赘述。通过身份证编码自动录入的性别与生日后，表格如下:ABCDE11学号电名II性軻岀生日期ni身份证号2 J20070001.郑美娜女19S94-2«：320324198904261860320070002荷昊i男1988-3-1920325193805191996420070003.Ml女1WO-8-22L.3203251990082236285200700041983-24432032419880214695362007