5.5 提取教师个人信息
案例背景
在2.3节中曾涉及到组织天津市中小学教师信息技术考核的问题，当时各区县报上来的汇总表的信息也不尽相同，但是都有参加考试的教师姓名和身份证号这两项信息，我们就根据教师的身份证号码，提取出该教师的出生年月日，性别和原户籍所在地，由此统计出全市参加“中小学教师信息技术考核”的整体情况。
如何从身份证号码提取出生年月日、性别和户籍所在区呢？我国的公民身份证号码是按照《公民身份证号码》国家标准编制的，身份证号码由18位数字组成：前6位为行政区划分代码，第7位至14位为出生日期码，第15位至17位为顺序码，第18位为校验码。其中，男性的顺序码设为奇数，女性顺序码设为偶数。根据这些规则使用Excel函数设计公式就可以实现从身份证号码中提取上述信息了。鉴于可视界面区域的限制，我们选择部分参加“天津市中小学教师信息技术考核”的教师信息作为本案例样本原始信息。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识
●函数应用 IF函数，MOD函数，MID函数，LEFT函数， VLOOKUP函数
●函数应用 ISODD函数，ISEVEN函数，DATE函数
最终效果展示
5.5.1创建教师个人信息表
Step1新建工作簿，重命名工作表
单击文件“保持”，将工作簿命名为”教师个人信息.xls”。将“Sheet1”工作表重命名为“信息表”，将“Sheet2”工作表重命名为“对照表”。
Step2输入教师个人原始信息
①单击 “信息表”工作表标签，在单元格区域A1：G1，输入字段名称：“序号”，“姓名”，“身份证号码”，“性别”，“出生年月日”，“省/直辖市/自治区”和“区/县”。
②在单元格区域B2:C27输入教师姓名和身份证号码，在单元格A1输入“1”，单元格A2输入“2”，选中单元格区域：A1：A2，双击单元格A2右下角的填充柄即可完成A3：A27单元格区域的自然数字的填充。
Step 3用不同的嵌套函数提取“性别”
①选中单元格D2，在编辑栏输入以下公式，然后按键确认得到第1名教师的“性别“。
=IF(MOD(MID(C2,17,1),2)=1,"男","女")
②选中单元格D2，向下拖曳其右下角的填充柄至单元格D11，松开鼠标完成第2至第10个教师的性别提取。
③选中单元格D12，在编辑栏输入以下公式，然后按键确认得到第11名教师的“性别“。
“=IF(ISODD(MID(C12,17,1)),"男","女")”
④选中单元格D12，向下拖曳其右下角的填充柄至单元格D21，松开鼠标完成第12至第20个教师的性别提取。
⑤选中单元格D22，在编辑栏输入以下公式，然后按键确认得到第21名教师的“性别“。
“=IF(ISEVEN(MID(C22,17,1)),"女","男")”
⑥选中单元格D22，向下拖曳其右下角的填充柄至单元格D27，松开鼠标完成剩余教师的性别提取。
Step 4提取“出生年月日“
①选中单元格E2，在编辑栏输入以下公式，然后按键确认即可得到第1名教师的“出生年月日”。
“=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))
②双击单元格E2右下角的填充柄即可完成其余教师的出生年月日”的提取工作。
Step 4建立全国省份地区代码索引对照表
①切换到“对照表”工作表，在单元格区域A1:A4输入：“身份证前两位”，“省/直辖市/自治区”，“身份证前六位”和“区/县”。
②在A2:D2308输入全国省份地区代码索引对照表，如图12所示
Step5提取教师原户籍所在“省/直辖市/自治区”
①切换到“信息表”工作表，选中单元格F2，在编辑栏输入以下公式，然后按键确认即可得到第1个教师的原户籍所在“省/直辖市/自治区”。
“=VLOOKUP(LEFT(C2,2), 对照表!$A$2:$B$35,2,0) “
②双击单元格F2右下角的填充柄即可完成其余教师的原户籍所在“省/直辖市/自治区”的提取工作。
Step6提取教师原户籍所在“区/县“
①选中单元格G2，在编辑栏输入以下公式，然后按键确认即可得到第1个教师的原户籍所在“区/县“。
“= VLOOKUP(LEFT(C2,6),对照表!$C$2:$D$10000,2,0) “
②双击单元格G2右下角的填充柄即可完成其余教师的原户籍所在“区/县”的提取工作。
5.5.2美化修饰“教师个人信息表”
为表格设置边框，为字段名区域，教师原始信息区域和提取信息区域，分别设置不同颜色的底纹，以示区别。
至此教师个人信息提取工作全部完成。
关键知识点讲解：
1. ISODD函数
函数名称：ISODD
主要功能：如果参数 number 为奇数，返回 TRUE，否则返回 FALSE。
如果该函数不可用，并且返回错误值 #NAME?，请安装并加载“分析工具库”加载宏。
操作方法
1.在“工具”菜单上，单击“加载宏”。
2.在“可用加载宏”列表中，选中“分析工具库”框，再单击“确定”。
3.如果必要，请遵循安装程序中的指示。
使用格式：ISODD(number)
参数说明：
Number 待测试的数值。如果参数值不是整数，则截尾取整。
函数说明
如果参数 number 不是数值型，函数 ISODD 返回错误值 #VALUE!。
应用示例
2. ISEVEN函数
函数名称：ISEVEN
主要功能：如果参数 number 为偶数，返回 TRUE，否则返回 FALSE。
如果该函数不可用，并返回错误值 #NAME?，请安装并加载“分析工具库”加载宏。
操作方法
1.在“工具”菜单上，单击“加载宏”。
2.在“可用加载宏”列表中，选中“分析工具库”框，再单击“确定”。
3.如果必要，请遵循安装程序中的指示。
使用格式：ISEVEN(number)
参数说明：
Number 待测试的数值。如果参数值不是整数，则截尾取整。
函数说明
如果参数 number 为非数值型，函数 ISEVEN 返回错误值 #VALUE!。
应用示例
3.DATE函数
函数名称：DATE
主要功能：返回代表特定日期的序列号。如果在输入函数前，单元格格式为“常规”，则结果将设为日期格式。
使用格式：DATE(year,month,day)
参数说明：
Year 参数 year 可以为一到四位数字。Microsoft Excel 将根据所使用的日期系统来解释 year 参数。默认情况下，Microsoft Excel for Windows 将使用 1900 日期系统，而 Microsoft Excel for Macintosh 将使用 1904 日期系统。
对于 1900 年日期系统
●如果 year 位于 0（零）到 1899（包含）之间，则 Excel 会将该值加上 1900，再计算年份。例如：DATE(108,1,2) 将返回 2008 年 1 月 2 日 (1900+108)。
●如果 year 位于 1900 到 9999（包含）之间，则 Excel 将使用该数值作为年份。例如：DATE(2008,1,2) 将返回 2008 年 1 月 2 日。
●如果 year 小于 0 或大于等于 10000，则 Excel 将返回错误值 #NUM!。
对于 1904 年日期系统
●如果 year 位于 4 到 1899（含）之间，则 Excel 会将该值加上 1900，再计算年份。例如：DATE(108,1,2) 将返回 2008 年 1 月 2 日 (1900+108)。
●如果 year 位于 1904 到 9999（含）之间，则 Excel 将使用该数值作为年份。例如：DATE(2008,1,2) 将返回 2008 年 1 月 2 日。
●如果 year 小于 4 或大于等于 10000，或者位于 1900 到 1903（含）之间，则 Excel 将返回错误值 #NUM!。
Month 代表每年中月份的数字。如果所输入的月份大于 12，将从指定年份的一月份开始往上加算。例如：DATE(2008,14,2) 返回代表 2009 年 2 月 2 日的序列号。
Day 代表在该月份中第几天的数字。如果 day 大于该月份的最大天数，则将从指定月份的第一天开始往上累加。例如，DATE(2008,1,35) 返回代表 2008 年 2 月 4 日的序列号。
函数说明
●Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一个默认日期系统。
●函数 DATE 在年、月、日为变量的公式中非常有用。
应用示例
注释 若要将数字转换为日期形式，请选中单元格，在“格式”菜单上单击“单元格”，再单击“数字”选项卡，然后单击“分类”框中的“日期”，在单击“分类“框中的任意一种您想要的日期形式即可将数字转换为日期形式。
案例公式解析：
Step 3之①的单元格D2中的公式为 “=IF(MOD(MID(C2,17,1),2)=1,"男","女")”
其中MID(C2,17,1)=2，公式可化简为 “=IF(MOD(2,2)=1,"男","女")”
MOD(2,2)=1返回FALSE所以公式继续化简为 “=IF(FALSE,"男","女")”
最后单元格D2中的公式返回“女”。
Step 3之③的单元格D12中的公式为 “=IF(ISODD(MID(C12,17,1)),"男","女")”
其中MID(C12,17,1)=2，公式可化简为 =IF(ISODD(2),"男","女")”
公式可化简为 =IF(ISODD(2),"男","女")”
因为2不是奇数，所以ISODD(2)返回FALSE，公式继续化简为 =IF(FALSE,"男","女")”
最后单元格D12中的公式返回“女”。
Step 3之⑤的单元格D22中的公式为 “=IF(ISEVEN(MID(C22,17,1)),"女","男")”
其中MID(C12,17,1)=6，公式可化简为 “=IF(ISEVEN(6),"女","男")”
因为6是偶数，ISEVEN(6) 返回TRUE，公式继续化简为 “=IF(TRUE,"女","男")”
最后单元格D22中的公式返回“女”。
Step 4之①的单元格E2中的公式为 “=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))
因为MID(C2,7,4)=1980，MID(C2,11,2)=09,MID(C2,13,2)=19
公式可化简为 “=DATE(1980,09,19)
最后单元格E2中的公式返回第一个教师的出生年月日：“1980-9-19”
Step 5之①的单元格F2中的公式为
“= VLOOKUP(LEFT(C2,2),对照表!A2:B35,2,0) “
其中公式LEFT(C2,2)=12，公式可化简为
“=VLOOKUP(12,对照表!A2:B35,2,0) ”
在“对照表”中的单元格区域A2:B35中是省/直辖市/自治区索引表，而“12”对应着“天津市”所以最后单元格F2中的公式返回“天津市”。
Step 6之①的单元格G2中的公式为
= VLOOKUP(LEFT(C2,6),对照表!$C$2:$D$10000,2,0)
其中LEFT(C2,6)=120221，公式可化简为
= VLOOKUP(120221,对照表!$C$2:$D$2308,2,0)
在“对照表”中的单元格区域C2:D2308中是区/县索引表，而“122021”对应着“宁河县”所以最后单元格G2中的公式返回“宁河县”。
|
