期货价格公式Excel之一在Excel中根据身份证号码利用公式自动生成出生年月日

2020年9月21日发(作者：解学龙)
文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.
【Excel之一】在Excel中根据身份证号码利用公式自动生成出生年月日
一、先给实例，不用搞懂原理

假设：A1栏内有身份证号码，要在B1栏中自动生成 年月日，那么就在
B1栏中输入以下公式。

（由于A1是假定的，可以任意替换，所以在下面的公式中用红色醒目
标出）

【情况1】

公式如下：

=TEXT((LEN(A1)=15) *19&MID(F2,7,6+(LEN(A1)=18)*2),

显示的效果是：

年年年年-月月-日日，如1984-04-28

【情况2】

公式如下：

=IF(LEN(A1)=18,MID(A1,7,8),19&MID(A1,7,6) )

显示的效果是：

年年年年月月日日，如

1
文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.
【情况3】

公式如下：

=IF(LEN(A1)=18,MID(A1,7,4)&年A1, 11,2)&月
A1,13,2)&日A1)=15,MID(A1,7,2)&年
A1,9, 2)&月A1,11,2)&日无效身份证号码

显示的效果如下：

XXXX年XX月XX日，如1984年04月28日

【情况4】

公式如下：

=IF(LEN(A1)=18,MID(A1,7,4)&A1,1 1,2),IF(LEN(A1)=15,MID
(A1,7,2)&A1,9,2),无效身份证号码

显示的效果如下：

年年年年.月月，如1984.04


二、有兴趣就拓展一下

根据以上四种情况，在不需要理解公式含义的 情况下，就可以派生出其
他几种不同的显示格式出来。

2
文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.
【情况5】

公式如下：

=IF(LEN(A1)=18,MID(A1,7,4)&A1,11 ,2),IF(LEN(A1)=15,MID
(A1,7,2)&A1,9,2),无效身份证号码< br>
显示的效果如下：

年年年年-月月，如1984-04

…………

依次类推，基本上需要的格式都能改出来。


三、还有兴趣就总结一下

以上运用到的函数

1. Len函数

（1）函数作用：用来对单元格内容的长度进行测量。

（2）使用语法：

LEN(text)

说明：text 是要查找其长度的文本。空格将作为字符进行计数。

3
文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.
2. MID函数

（1）函数作用：根据用户自行指定的开始位置和字符长度对某单元格
进行字符提取。

（2）使用语法：

MID(text,start_num,num_chars)

说明：

Text 是包含要提取字符的文本字符串，可以直接输入含有目标文字的
单元格名称。

Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的
start_num 为 1，以此类推。

Num_chars 指定希望 MID 从文本中返回字符的个数。

注意：

如果 start_num 大于文本长度，则 MID 返回空文本 ()。

如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过
了文本的长度，则 MID 只返回至多直到文本末尾的字符。

如果 start_num 小于 1，则 MID 返回错误值 #VALUE!。

如果 num_chars 是负数，则 MID 返回错误值 #VALUE!。

4
文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.
3. IF函数

（1）函数作用：根据指定的条件来计算结果为 TRUE 或 FALSE，返回
不同的结果。可以使用 IF 对数值和公式执行条件检测。

（2）使用语法：

IF(logical_test,value_if_true ,value_if_false)

说明：

Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达
式。

Value_if_true 是 logical_test 为 TRUE 时返回的值。

Value_if_false 是 logical_test 为 FALSE 时返回的值。


======================分 割线======================

一、以上大部分内容来源于网络，并非原创，我只是按照自己的爱好做
了整理。
二、最近写“类型文”写烦了，想写写日志，却囿于内容限制，只好以
此充数，骗骗自己。

三、有可能写二，也可能永远只有一。反正不重要。
5
文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.


说明：公式中的A1是身份证号


身份证号码为15位的,最后一位号码 能决定性别,奇数为男性,偶数为女性;身份证号码为18
位的,倒数第二位号码能决定性别,奇数为男 性,偶数为女性。


1、根据身份证号码求性别：

=IF( LEN(A1)=15,IF(MOD(VALUE(RIGHT(A1,1)),2)=0,女
女男身 份证错


男
身份证号码为15位的,第七、八位为出生年，第九、十位为出 生月,第十一、十二位为出生日;
身份证号码为18位的,第七、八、九、十位为出生年，第十一、十二 位为出生月,第十三、十
四位为出生日。


2、根据身份证号码求出生年月：

=IF(LEN(A1)=15,CONCAT ENATE(
MID(A1,7,4),身份证错


3、根据身份证号码求年龄：

=IF(LEN(A1)=15,year(now ())-1900-VALUE(MID(A1,7,2)),if(LEN(A1)=18,year(now ())-VALUE(MID(A1,
7,4)),身份证错


叶子 11:04:27

=MID(A2,3,6)
叶子 11:04:55
考生号中截取其中的生源地代码：

=MID(A2,3,6)
六然学童 11:05:06
谢谢
叶子 11:06:37
生源地代码与生源地名称查找比对命令：
VLOOKUP(B2,'生源地代码表'!A:B ,2,FALSE)用syszddm在“生源地代码表”取SYSZDMC。
叶子 11:06:47
6
文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.


在excel使用哪个函数能在同一个工作簿下的一个工作表中
引用另一 个工作表中的数据,以及函数ISNA,VLOOKUP的使
用方法？
直接写=sheet1！A1即可
ISNA(value)判断值是否为错误值 #NA（值不存在）。
VLOOKUP(lookup_value,table_array,co l_index_num,range_lookup)
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数
值。
Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或
文本字符串。

Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例
如数据库或列表。

Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1
时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列
中的数值，以此类推。如果 col_index_num 小于 1，函数 VLOOKUP 返回错误值值
#VALUE!；如果 col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值
#REF!。

Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。
如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小
7
文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.
于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精
确匹配值。如果找不到，则返回错误值 #NA。

例如=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

8