如何用EXCEL公式提取身份z信息

方法/步骤

打开“公司员工信息表”

鼠标定位到D3单元格，输入公式=TEXT(MID(C3,7,8),"0000年00月00日")

然后拖动D3单元格右下角的填充柄向下填充公式，可以提取到所有员工的生日信息。

鼠标定位到E3单元格，输入公式=TEXT(MOD(LEFT(RIGHT(C3,2)),2),"[=0]女;[=1]男")

然后拖动E3单元格右下角的填充柄向下填充公式，可以提取到所有员工的性别信息。

总结：1，提取生日信息时使用MID()函数提取身份z号码从第7位数开始的连续8个数字，再使用TEXT()函数在提取的8个数字中插入“年”，“月”，“日”以表示日期

总结：2，提取性别信息时使用RIGHT()函数取身份z最后两位，再使用LEFT()函数取得这两位数中的第一位，然后使用MOD()函数对2取余数，最后使用TEXT()函数将余数转换为员工的性别，"[=0]女;[=1]男"是一个条件格式，表示前面的值为0时输出文本“女”，为1时输出文本“男”。

1、在B列有三个身份z号码，需要在C列中让excel自动填充出生年月日，主要使用的是MID函数，如下图：

2、在C列对应的单元格中我们输入公式

“=MID(B3,7,4)&"年"&MID(B3,11,2)&"月"&MID(B3,13,2)&"日"”

其中MID函数作用为从指定的字符串中截取出指定数量字符，例如公式中的“MID(B3,7,4)”,第一个参数表示B3单元格中的身份z号，7表示从第7位开始计算，4表示从第7位开始共截取几位数字，其中公式中的“&”表示拼接字符串，如下图：

3、回车之后我们得到使用MID函数和&拼接符自动计算得出的出生年月日，如下图：

4、然后我们将公式填充到其他单元格，得到所有身份z的出生年月，如下图：

Excel是办公室自动化中非常重要的一款软件，Excel函数则是Excel中的内置函数。Excel函数共包含11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

术语说明：

1、参数

参数可以是数字、文本、形如 TRUE 或FALSE的逻辑值、数组、形如 #N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。

参数不仅仅是常量、公式或函数，还可以是数组、单元格引用等。

2、数组

用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。在Microsoft Excel有两类数组：区域数组和常量数组。区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式；常量数组将一组给定的常量用作某个公式中的参数。

3、单元格引用

用于表示单元格在工作表所处位置的坐标值。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为"B3"。

4、常量

常量是直接键入到单元格或公式中的数字或文本值，或由名称所代表的数字或文本值。例如，日期 10/9/96、数字 210 和文本"Quarterly Earnings"都是常量。公式或由公式得出的数值都不是常量。

5、其他

函数是否可以是多重的呢？也就是说一个函数是否可以是另一个函数的参数呢？当然可以，这就是嵌套函数的含义。所谓嵌套函数，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。例如图1中所示的公式使用了嵌套的 AVERAGE 函数，并将结果与 50 相比较。这个公式的含义是：如果单元格F2到F5的平均值大于50，则求F2到F5的和，否则显示数值0。

嵌6、套函数

在学习Excel函数之前，我们需要对于函数的结构做以必要的了解。如图2所示，函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时，公式选项板将提供相关的帮助。

7、函数的结构

公式选项板--帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的"编辑公式"按钮，或是单击"常用"工具栏中的"粘贴函数" 按钮之后，就会在编辑栏下面出现公式选项板。

假设B2是一个412801198702230834的18位身份z号码，用=mid(b2,7,8)就可以取出就是身份z号码上的出生年月日公式中的7带表第7位的1，8则代表从1开始取出8个号码，也就是出生年月日不知道这个答案是否详细,希望杯采纳

从海量数据中提取指定字段（以身份z号码提取出生日期为例）

从而有效帮助提高基层大量数据提取工作效率

在我们基层工作中，有时会面对海量的数据，如何从海量数据中提取我们所需要的内容，这就是今天要和大家分享的一些办法

如图中所示，数据量有1250个身份z号（身份z号均为随机生成），现在我们需要从这1250个身份z号码中提取出生日期并输出显示在B列，我们需要用到的函数是mid函数。那么关于mid函数的用法我来给大家介绍一下，如图，在B1中键入“=mid（）”后，显示text为需要从什么地方提取，start_num为提取起始位置，num_chars为提取长度。

那么在此例中，我们需要从字段A中提取出生年月字段，因为身份z号码中从第七位到第14位为出生年月，字段长度共计8位，所以我们在B1中键入“=MID(A1,7,8)”

效果如下图所示

接下来，我们双击B1右下方小点，如下图

1250个数据批量提取完毕，见下图

当然，我们还可以提取出生年月，只需要将B1中的公式改为“=MID(A1,7,6)”即可，效果如下图

我们也可以用一些方法，可以使提取的数据符合我们格式的需要，如年月日，那么公式就修改为=MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日"

值得注意的是，键入符号的时候一定要在英文输入环境下。

效果如下

也可以根据需要调整一下，如需要提取格式为年/月/日，我们只要稍稍改动一下公式就可以，公式如下=MID(A1,7,4)&"/"&MID(A1,11,2)&"/"&MID(A1,13,2)

效果如下

Excel中函数功能十分强大，用好MID函数可以快速帮助完成很多工作，鉴于本人水平有限，更多功能还需要大家继续探索。

身份z号判断性别的方法\x0d\\x0d\18位的看倒数第二位，奇男偶女；\x0d\\x0d\15位的看倒数第一位，奇男偶女。\x0d\\x0d\假如：身份z所在的列为C2\x0d\\x0d\首先要判断该身份z的长度，在excel中使用len函数来获取len(C2)\x0d\\x0d\如果是18位的就取第17位，即倒数第二位，Excel中使用mid函数还获取指定位置开始，指定长度的字符串，如果是15位的就去最后一位也就是第15位\x0d\\x0d\=If(len(C2)=15,mid(C2,15,1),mid(C2,17,1))\x0d\\x0d\获取到这个数后，再判断该数是奇数还是偶数就可以得出性别了，excel中使用mod函数实现\x0d\\x0d\=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")\x0d\\x0d\获取年龄的函数如下：\x0d\\x0d\=DATEDIF(TEXT((LEN(C2)=15)19&MID(C2,7,6+(LEN(C2)=18)2),"00-00-00"),TODAY(),"y" )\x0d\\x0d\在Excel中根据身份z号码自动生成出生生日期和性别\x0d\\x0d\根据身份z号码自动生成出生生日期\x0d\\x0d\方法一：\x0d\\x0d\15位身份z号：410881790605552 输出出生日期1979/06/05 =CONCATENATE("19",MID(E2,7,2),"/",MID(E2,9,2),"/",MID(E2,11,2))\x0d\\x0d\公式解释：\x0d\\x0d\aMID(E2,7,2)为在身份z号码中获取表示年份的数字的字符串\x0d\\x0d\b MID(E2,9,2) 为在身份z号码中获取表示月份的数字的字符串\x0d\c MID(E2,11,2) 为在身份z号码中获取表示日期的数字的字符串\x0d\dCONCATENATE("19",MID(E2,7,2),"/",MID(E2,9,2),"/",MID(E2,11,2))目的就是将多个字符串合并在一起显示。\x0d\\x0d\18位身份z号：410881197906055521 输出出生日期1979/06/05 =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))\x0d\\x0d\方法二：\x0d\\x0d\15位身份z号：410881790605552 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))\x0d\\x0d\18位身份z号：410881197906055521 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))\x0d\\x0d\公式解释：\x0d\\x0d\LEN(E2)=15：检查E2单元格中字符串的字符数目，本例的含义是检查身份z号码的长度是否是15位。\x0d\\x0d\MID(E2,7,6)：从E2单元格中字符串的第7位开始提取6位数字，本例中表示提取15位身份z号码的第7、8、9、10、11、12位数字。\x0d\\x0d\MID(E2,9,6)：从C2单元格中字符串的第9位开始提取6位数字，本例中表示提取18位身份z号码的第9、10、11、12、13、14位数字。\x0d\\x0d\=IF(LEN(E2)=15,MID(E2,7,6),MID(E2,9,6))：IF是一个逻辑判断函数，表示如果额E2单元格是15位，则提取第7位开始的6位数字，如果不是15位则提取自第9位开始的6位数字。\x0d\\x0d\根据身份z号码自动生成性别：\x0d\\x0d\方法一：\x0d\\x0d\在C列输入身份z号，在B列填写性别，可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")”，其中：\x0d\\x0d\LEN(C2)=15：检查身份z号码的长度是否是15位。\x0d\\x0d\MID(C2,15,1)：如果身份z号码的长度是15位，那么提取第15位的数字。\x0d\\x0d\MID(C2,17,1)：如果身份z号码的长度不是15位，即18位身份z号码，那么应该提取第17位的数字。\x0d\\x0d\MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。\x0d\\x0d\IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。\x0d\\x0d\15位身份z，看最后一位，奇男偶女；18位的，看第17位数，也是奇男偶女。\x0d\\x0d\方法二：\x0d\\x0d\如果你是想在Excel表格中，从输入的身份z号码内让系统自动提取性别，可以输入以下公式：\x0d\\x0d\=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))\x0d\\x0d\公式内的“C2”代表的是输入身份z号码的单元格。