巧妙设计花名册excel实战.docVIP

●巧妙设计花名册 PAGE 2008MUSIC2008 醉音竹林 巧妙设计花名册 （Excel实战宝典） ——作者：醉音竹林 目录 TOC \o 1-3 \h \z \u 一、身份证系列 1 （一）判断身份证正误 1 （二）自动提取性别 3 （三）自动提取出生年月 5 （四）自动提取年龄 8 二、提取特定文字 10 （一）提取乡镇名称 10 （二）提取村/居委会名称 11 （三）提取自然村名称 11 三、常用数据统计 12 （一）特定姓氏、民族总数统计 12 （二）特定数据总数统计 13 四、万能表花名册 13 （一）万能表 13 （二）万能表运用解剖 14 — PAGE 11 — 2008MUSIC2008 醉音竹林 众所周知，微软公司推出的Microsoft 2003是运用最广泛的电脑办公处理软件，它主要包含Word、Excel、PowerPoint等。其中，Excel表格的功能尤其强大，其特色在函数和公式运算上得以体现。运用函数和公式进行统计，可使统计结果更加准确，工作效率大幅提高。本节重点讲述巧妙设计“花名册”的方法。 一、身份证系列 在Excel表格统计中，最广泛的一类表格是“花名册”。一般地，花名册包含如下项目：姓名、性别、出生日期、年龄、身份证号码、家庭住址（含乡镇、村/居委会、小组等信息）等。其中，姓名、身份证号码、家庭住址尤为重要，而其他信息均可根据函数/公式统计得出。[下列函数公式，可以直接复制到表格中应用] （一）判断身份证正误 1、公式 =IF(OR(LEN(E3)=18,LEN(E3)=15),IF(LEN(E3)=18,IF(OR(MONTH(DATE(1*(MID(E3,7,4)),1*(MID(E3,11,2)),1*(MID(E3,13,2))))1*(MID(E3,11,2)),DAY(DATE(1*(MID(E3,7,4)),1*(MID(E3,11,2)),1*(MID(E3,13,2))))1*(MID(E3,13,2))),【出生日期错误】,),IF(LEN(E3)=15,IF(OR(MONTH(DATE(1*(MID(E3,7,2))+1900,1*(MID(E3,9,2)),1*(MID(E3,11,2))))1*(MID(E3,9,2)),DAY(DATE(1*(MID(E3,7,2))+1900,1*(MID(E3,9,2)),1*(MID(E3,11,2))))1*(MID(E3,11,2))),【出生日期错误】,))),【身份证位数错误】)IF(OR(LEN(E3)=18,1*MID(E3,7,4)YEAR(TODAY())-6),IF(AND(LEN(E3)=18,1*MID(E3,7,4)YEAR(TODAY())-120),【出生年份错误】,),IF(OR(LEN(E3)=15,1*MID(E3,7,2)YEAR(TODAY())-2000-6),IF(AND(LEN(E3)=15,1*MID(E3,7,2)YEAR(TODAY())-1900-120),【出生年份错误】,)))IF(AND(LEN(E3)=18,1*MID(E3,7,4)YEAR(TODAY())-6),【出生年份错误】,) （1）公式中“2000”，“1900”， “120”，“6”均为常数，主要是便于统计身份证信息；（其中“2000”，“1900”，的作用是还原15位身份证；“120”——假定人的最大年龄为120岁；“6”——假定6岁以前不具有身份证）；[以下（二）、（三）、（四）中高级方法公式同理] （2）公式中“E3”是指Excel表格中身份证号码所在单元格，如果您的表格中身份证号码所在单元格不是E3，则需要在复制粘贴前修改这一参数；[下述所有公式同理] （3）公式中的引号必须为英文输入格式，而不能为中文输入格式“”；[下述所有公式同理] 2、演示表格[双击下表进入Excel] 3、操作说明 （1）将上述判断公式输入F3单元格中后，以后的部分可以通过下拉复制即可自动判断相应身份证号码是否正确； （2）优点：在出生年份、出生日期、身份证位数等信息错误时能自动提醒； （3）缺点：在现实统计中，单独判断身份证的情况较少见。 （二）