使用Excel 管理人事信息，具有无须编程、简便易行的特点，在各行各业中得到广泛了应用，但人事数据信息与其它数据计算相比，存在着数据重复和不便统计的问题。只在身份证号中，就隐含着公民的大部分个人信息，有性别、出生年月日和证件办理所在地。如何既尽可能减少数据录入的工作量，又能提取统计出所需人事信息呢，下面我们就利用Excel 函数，来一步一步实现身份证信息的自动化统计。
一、创建工作簿：
用Excel创建一个身份证信息函数统计实例工作簿，在A1、B1、C1、D1、E1、F1中，分别填入姓名、身份证号码、性别、出生日期、年龄、办证县市，自第2 行开始依次录入员工的姓名和身份证号，身份证号所在B列要选用“文本”格式。示例设定共24名员工，即A2~A25。
在下列图例中，我们为了方便大家识别，把需要手工录入的值字体设置为：”黑色”；用函数公式提取的值把它设置为：“蓝色”；用函数公式所统计出的值，设置为：“红色”。
二、提取性别：
根据现行的居民身份证号码编码规定，正在使用的18 位的身份证编码。它的第17 位为性别(奇 数为男，偶数为女)，第18 位为效验位。而早期使用的是15 位的身份证编码，它的第15 位是性别(奇数为男，偶数为女)。
(1)函数分解
LEN 函数返回文本字符串中的字符数。 语法：LEN(text) Text 是要查找其长度的文本。空格将作为字符进行计数。
MOD 函数返回两数相除的余数。结果的正负号与除数相同。 语法：MOD(number,divisor) Number 为被除数；Divisor为除数。 MID 函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。 语法：MID(text,start_num,num_chars) Text 为包含要提取字符的文本字符串；Start_num 为文本中要提取的第一个字符的位置。文本中第一个字符的start_num 为1 ，以此类推；Num_chars指定希望MID 从文本中返回字符的个数。
(2)实例分析
为了适应上述情况，必须设计一个能够适应两种身份编码的性别计算公式，在C2 单元格中输入“=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女"))”。回车后即可在单元格获得该职工的性别，而后只要把公式拖动复制到C3、C4等单元格，即可得到其他职工的性别。
为了便于大家了解上述公式的设计思路，下面简单介绍一下它的工作原理：该公式由三个IF 函数构成，其中“IF(MOD(MID(B2,15,1),2)=1,"男","女")”和“IF(MOD(MID(B2,17,1),2)=1,"男","女")”作为第一个函数的参数。公式中“LEN(B2)=15”是一个逻辑判断语句，LEN 函数提取B2 等单元格中的字符长度，如果该字符的长度等于15， 则执行参数中的第一个IF 函数，否则就执行第二个IF 函数。
在参数“IF(MOD(MID(B2,15,1),2)=1,"男","女")”中。MID 函数从B2 的指定位置(第15 位)提取1 个字符，而MOD 函数将该字符与2 相除，获取两者的余数。如果两者能够除尽，说明提取出来的字符是0(否则就是1)。逻辑条件“MOD(MID(B2,15,1),2)=1”不成立，这时就会在D2 单元格中填入“女”，反之则会填入“男”。 如果LEN 函数提取的B2 等单元格中的字符长度不等于15， 则会执行第2个IF函数。除了MID 函数从B2 的指定位置(第17 位，即倒数第2 位)提取1 个字符以外，其他运算过程与上面的介绍相同。
注意：录入函数公式时所使用的 “，”“：”“；”全部为英文半角输入。
三、提取出生日期
(1)函数分解
CONCATENATE 函数将几个文本字符串合并为一个文本字符串。 语法：CONCATENATE(text1,text2,...) Text1,text2,...为1～30 个要合并成单个文本项的文本项。文本项可以为文本字符串、数字或对单个单元格的引用。
(2)实例分析
与上面思路基本相同，我们可以在D2 单元格中输入函数公式=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),"年",MID(B2,9,2),"月",MID(B2,11,2),"日"),CONCATENATE(MID(B2,7,4),"年",MID(B2,11,2),"月",MID(B2,13,2),"日")) 回车，可得到A2员工出生日期，下拉C2单元格进行拖动复制，即可得到全部员工的出生年月日。
其中“LEN(B2)=15”仍然作为逻辑判断语句使用，它可以判断身份证号码是15 位的还是18 位的，从而调用相应的计算语句。 对15 位的身份证号码来说，左起第7 至12 个字符表示出生年、月、日，此时可以使用MID 函数从身份证号码的特定位置，分别提取出生年、月、日。然后用CONCATENATE 函数将提取出来的文字合并起来，就能得到对应的出生年月日。公式中“19”是针对早期身份证号码中存在2000 年问题设计的，它可以在计算出来的出生年份前加上“19”。对“18”位的身份证号码不存在2000 年问题，公式中不用给计算出来的出生年份前加上“19”。
注意：CONCATENATE 函数和MID 函数的操作对象均为文本，所以存放身份证号码的单元格必须事先设为文本格式，然后再输入身份证号。
四、提取年龄
(1)函数分解：
YEAR用途：返回某日期的年份。其结果为1900 到9999 之间的一个整数。 语法：YEAR(serial_number) 参数：Serial_number 是一个日期值，其中包含要查找的年份。日期有多种输入方式：带引号的文本串(例如 "1998/01/30")、序列号(例如，如果使用 1900 日期系统则 35825 表示 1998 年 1 月 30 日)或其他公式或函数的结果 (例如 DATEVALUE("1998/1/30"))。
（2）实例分析：
，基本思路是用当前日期(NOW())，减去提取的身份证日期，用YEAR返回年份差，我们就可获得年龄。基于此思路，我们在E2 单元格中输入公式
=YEAR(NOW())-IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2)),CONCATENATE(MID(B2,7,4)))
其中“LEN(B2)=15”仍然作为逻辑判断语句使用，它可以判断身份证号码是15 位的还是18 位的，从而调用相应的计算语句。 对15 位的身份证号码来说，左起第7 至12 个字符表示出生年、月、日，此时可以使用MID 函数从身份证号码的特定位置，分别提取出生年、月、日。然后用CONCATENATE 函数将提取出来的文字合并起来，就能得到身份证的出生年月日。
另外，对表中已经有出生日期的，我们可以用两个简便函数公式来计算年龄：
（1）根据当前日期和出生日期，自动计算年龄公式：
=SUM(YEAR(NOW())-YEAR(D2))
（2）由于跨年度的时候，我们可能不需要当前日期的，需手动输入时间（如2007）计算年龄：
=2007-YEAR(D2)
注意：添加公式后把当前格设为：“常规”格式
五、提取证件办理地：
公民身份证号的前6位是办证地的城市代码，通过它可以大略了解员工的籍贯和户籍情况。下面我们可以通过网上公布的城市代码和提取函数的使用，来实现证件办理地的查询：
(1) 函数分解：
VLOOKUP 用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数VLOOKUP 代替函数HLOOKUP。 语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup) 参数：Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。Table_array 为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。Col_index_num 为table_array 中待返回的匹配值的列序号。Col_index_num为1 时，返回table_array 第一列中的数值； col_index_num 为2，返回table_array 第二列中的数值，以此类推。Range_lookup为一逻辑值，指明函数VLOOKUP 返回时是精确匹配还是近似匹配。如果为TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值；如果range_value 为FALSE， 函数VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。
LEFT 或LEFTB 用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。 语法：LEFT(text，num_chars)或LEFTB(text， num_bytes)。 参数：Text 是包含要提取字符的文本串；Num_chars 指定函数要提取的字符数，它必须大于或等于0。Num_bytes按字节数指定由LEFTB 提取的字符数。
（2）实例分析：
基于VLOOKUP 和LEFT函数用途，我们的思路为，截取身份证号的前6位，与城市代码（需另建）表中的代码区域（如：城市代码表中A2：An）比照查找，匹配后，返回城市名称（如第二列 B）对应得值。得出公式：
=VLOOKUP(LEFT(B2,6),城市代码!$A$2:$B$3525,2,FALSE)
这里的关键是建立城市代码表，其位置、格式如下：
最新城市代码可以从国家统计局网http://www.stats.gov.cn/tjbz/index.htm下载，复制后，直接粘入城市代码表，利用我们上面学到的MID、VLOOKUP 、CONCATENATE函数，进行数据分类提取和提取后的合并，删除空行。 并对完成的数值列要进行固化处理。
方法为： 在A列前新插入1列，复制数值列的数值，然后，点鼠标右键——选择性粘贴——在粘贴对话框中选数值后确定——右键点新增列的首行——粘贴，即可。
函数公式中：“城市代码!$A$2:$B$3525,2,FALSE”!，为跨页计算符号；$，为绝对引用；“2”为返回第二列的对应数；“FALSE”为返回精确匹配值；如果，身份证号容错的话，可省略或填入TRUE，也可用另这个函数“=LOOKUP(MID(B3,1,6),城市代码!$A$1:$B$3525)” 则返回近似匹配值。
六、员工性别统计：
通过上述制作演示，我们已经在身份证号中提取出了相关的全部人事信息，但站在人事管理统计的角度，这还没有达到汇总的要求，下面我们就用提取出的性别，对男女人数的进行统计：
(1)函数分解
COUNTIF用途：计算区域中满足给定条件的单元格的个数。语法：COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域；Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。
(2)实例分析
根据COUNTIF函数用途和上面性别C列的24 条记录，统计职工中男性和女性人数的方法是：选中单元格H2 (或其他用不上的空白单元格均可)，统计男性职工人数可以在其中输入公式“="男"&COUNTIF(C2:C25,"男")&"人"”；接着选中单元格H3，在其中输入公式“="女"&COUNTIF(C2:C25,"女")&"人"”。回车后即可得到“男19 人”、“女5 人”。
上式中C2:C25 是对“性别”列数据区域的引用，实际使用时必须根据数据个数进行修改。“男”或“女”则是条件判断语句，用来判断区域中符合条件的数据然后进行统计。“&” 则是字符连接符，可以在统计结果的前后加上“男”、“人”字样，使其更具有可读性。
七、年龄分段统计：
在人事管理工作中，统计分布在各个年龄段中的职工人数也是一项经常性工作。现根据我们创建的人员信息
工作表的E2:E25 单元格存放职工的年龄，我们要以5 年为一段分别统计年龄小于20 岁、21 至25 岁之间，一直到56 至60 岁之间的各年龄段的人数，采用下面的操作方法。
(1)函数分解
FREQUENCY 函数以一列垂直数组返回某个区域中数据的频率分布。 语法：FREQUENCY(data_array,bins_array)
Data_array 为一数组或对一组数值的引用，用来计算频率。如果data_array 中不包含任何数值，函数FREQUENCY 返回零数组；Bins_array为间隔的数组或对间隔的引用，该间隔用于对data_array 中的数值进行分组。如果bins_array 中不包含任何数值，函数FREQUENCY 返回data_array 中元素的个数。
(2)实例分析
首先在工作表中找到空白的I列(或其他列)在 I 1填入年龄段，自I2 单元格开始依次输入20、25、30 、35、40...60， 分别表示统计年龄小于20、21至25 之间、26 至30 之间等的人数。并在该列旁边选中与年龄段相同个数的单元格，如J2：J10 准备存放各年龄段的统计结果。然后在编辑栏输入公式“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter 组合键即可在选中单元格中看到计算结果。
特别注意：计算结果为数组格式，不能改动，如年龄有变或误操作后，可按Ctrl+Shift+Enter 组合键重新计算还原。
八、年龄排次查询：
对我们查出的员工年龄，你想知道最大的是几岁、其次是几岁、最小的是几岁吗？当然在此24人的实例中，你看一眼可能就知道了，但如果人数在300、800、1000以上，恐怕就不好找了。现在我们就学习一种简单方法，让你想查第几名，就查出第几名。这在工资、成绩统计等场合会很有用的，其操作方法如下。
(1) 函数分解
LARGE用途：返回某一数据集中的某个最大值。使用LARGE 函数可以查询到数据集中特定位置上的数值。语法：LARGE(array,k) Array 为需要从中选择第K 个最大值的数组或数据区域； K 为返回值在数组或数据单元格区域中的位置(从大到小排)。
SMALL用途：返回数据集中第k 个最小值，从而得到数据集中特定位置上的数值。 语法：SMALL(array，k) 参数：Array 是需要找到第k 个最小值的数组或数字型数据区域，K为返回的数据在数组或数据区域里的位置(从小到大)。
(2)实例分析
在我们的实例中E2:E25 区域存放着员工的年龄，首先在K列选取空白单元格K10中输入公式“=LARGE(E2:E25,K4)”。其次是把K4 作为我们输入名次变量的单元格，如果你在其中输入1，公式就可以返回E2:E25 区域中第一大的数值；以此类推，当输入24时，当然，就是最小的了。
如果我们需要从小到大，把最小的当作第一，则上述公式修改为“=SMALL(E2:E25,K4)”后，在K4 单元格中输入1，就可以获得E2:E25 区域中最小的数值。
为方便起见，你也可以给E2:E25 区域定义一个名称“年龄”。然后把上述公式修改为“=LARGE(年龄,K4)”或“=SMALL(年龄,K4)”，可实现同样效果。 定义方法为：用鼠标选定E2:E25 区域——点击插入——名称——定义，在弹出的对话中填入年龄，确定即可。
九、年龄排名统计:
根据实际需要，如果我们不满足上面的单一查询，要查看全员的年龄排名，那也不难，用下列方式其即可实现。
另：由于，我们前面以定义了“$E$2:$E$25”的名称为“年龄”，所以，把公式改为“=RANK (E2, 年龄)，可实现同样结果。
注意：如果数据清单已经排过序了，则数值的排位就是它当前的位置；函数RANK 对重复数值的排位相同。但重复数的存在将影响后续数值的排位。如在一列整数中，若年龄31 出现两次，其排位为7，则32 的排位则为9 ，就没有排位为8名的数值了。
十、年龄条件查询：
上面，我们已经介绍了三种年龄的查询方式，但实际工作中，我们的需求还不止这些，比如要查询32~50岁的人数或37~48岁的人数，上述方式就不能满足了。下面我们就用一个新的函数即可来实现这一条件查询。
（1）函数分解
DCOUNT函数用途：返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。语法：DCOUNT(database,field,criteria) 参数：Database表示需要统计的单元格区域；Field表示函数所使用的数据列（在第一行必须要有标志项）；Criteria包含条件的单元格区域。
（2）实例分析：
在上面实例中E列为年龄，这里我们把C1:E25设为查询区域（寓意是可以在多列中选出我们所需的E列）；把H14:I15作为条件区，录入两个年龄标志项和查询条件，（切记在两个区域中必须都包含“年龄”标志项）；在J15空单元格中输入公式：=DCOUNT(C1:E25,"年龄",H14:I15)，确认后即可求出此条件查询的结果。
另：如果将上述公式修改为：=DCOUNT(C1:E25,,H14:I15)，也可以达到相同目的。
联系客服
