excel中提取不重复值(唯一值)方法大全
下面介绍两种方法提取不重复值(唯一值);希望本指南能帮到大家。
操作方法1
以下图中A10:A18的数据为例演示操作方法。
选中A10:A18的数据;按下CTRL+C,执行复制;再在B10单元格点击一下,按下CTRL+V,执行粘贴命令。
保持粘贴这些数据在选中的状态下,我们再点开数据选项卡,再点:删除重复项
弹出对话框,如图所示,点选下面这一项,再点:删除重复项
再弹出删除重复项的对话框,再点确定。
弹出对话框,点确定。
那么,数据的唯一值就提取出来了。
操作方法2
用上述的数值为例。把A10:A18的数据全选中;鼠标再点击数据选项卡,在下面再点:高级
即时调出了高级筛选的操作框。
如图所示,在方式这里点选第二项;
在复制到这里输入:B10:B18;再勾选:选择不重复的记录;再点确定;那么,唯一值即提取出来了。
excel中提取省市区用啥函数
巧用EXCEL提取身份证个人行政区域和基本信息
在日常工作和生活中,我们应用到身份证的场景很多,比如:出差乘坐公共交通工具时,需要用身份证实名购票,住宿酒店需要用身份证进行实名登记,办理银行卡需要用身份证进行实名验证等等;工作中,求职时需要用身份证进行入职登记和备案等。因为身份证的唯一性,为我们的工作和生活提供了很多便利。
我们都知道,现在都用第二代身份证,身份证号码由18位构成,下面以532622198311191034为例介绍各个位置的定义:
53:第1~2位代表所在省(直辖市、自治区)的编码;
26:第3~4位代表所在地级市(自治州)的编码;
22:第5~6位代表所在区(县、自治县、县级市)的编码;
19831119:第7~14位代表出生年月日;
10:第15~16位代表所在地派出所的编码;
3:第17位代表性别,单数表示男性,双数表示女性;
4:第18位代表个人信息码,该信息码为随机生产,主要用于校验身份证的正确性。校检码可以是0—9的数字,也可用X表示。
知道了身份证号码各个位置的定义,我们就可以用EXCEL电子表格通过身份证号码提取个人基本信息,在拥有上百人甚至上千人的公司,批量提取个人信息,能大幅度提升工作效率。本文运用到多个EXCEL函数,下面就各个位置的提取作详细解答。
一、提取省市区
要提取省市区信息,首先要知道身份证前六位编码对应的省市区,下图为本人在网上收集的全国行政区域身份证代码,区域编码依次对应行政区域、长途区号、邮政编码信息。
第一步:提取身份证号码前六位
首先用LEFT函数提取身份证号码前六位,函数如下图所示:
LEFT函数:LEFT(需要提取字符串的单元格,提取几位)
LEFT函数有两个参数,如上图:第一个参数表示需要提取字符串的单元格为B3单元格,第二个参数6表示要在指定的单元格从左向右提取6位数,按键盘上的回车键后我们就得到了B3单元格的前六位110103。
第二步:利用VLOOKUP函数和LEFT函数提取我们需要的行政区域
VLOOKUP函数:VLOOKUP(要找谁,在哪里找,要找第几列,精确查找还是模糊查找)
VLOOKUP函数有四个参数,如上图:第一个参数“LEFT(B3,6)”表示我们提取的身份证号码前6位;第二个参数“全国行政区域身份证代码表!B:H”表示要查找同一个工作簿里“全国行政区域身份证代码表”这个表里B列到H列这个范围;第三个参数“5”表示我们从B列(区域为第1列)开始往后第5列,刚好是行政区域所在的列,即F列;第四个参数0表示精确查找,可以留空也可以写成0,如果写成1则表示模糊查找,查找结果将显示与实际值最接近的值。
上述公式为:
=VLOOKUP(LEFT(B3,6),全国行政区域身份证代码表!B:H,5,0)
回车后,我们发现一个问题,单元格值显示#N/A,这时候只需要在LEFT(B3,6)前面加两根横线“--”就可以了。每一根横线代表一次减法运算,两次减法运算就把文本数字或者逻辑值转变成了数值!
在“全国行政区域身份证代码表”里,我们已经通过身份证号码前6位提取了行政区域,如果想分别提取省、市、县和区号、邮编等,则把VLOOKUP函数里第三个参数修改为要提取的内容所在的列数字即可。
二、提取出生年月日
刚才我们说到,身份证号码里第7~14位代表出生年月日,要提取一串字符中间的字符,需要用到MID函数。
MID函数:MID(需要提取字符串的单元格,从第几位提取,提取几位)
在E3单元格输入公式:=MID(D3,7,8),表示从D3单元格的身份证号码第7位开始往后取取8位数,即为完整的出生年月日,如下图所示:
我们发现这样提取的出生日期不是标准的日期格式,只需要用TEXT函数进行规范即可,函数如下:
=TEXT(MID(D3,7,8),"0000-00-00")
这时候日期将显示为:1983-11-19
三、提取性别
刚才我们说到,身份证号码里第17位代表性别,单数表示男性,双数表示女性,同样需要用MID函数把第17位数提取出来,然后用MOD余数函数判断该数是单数还是双数,用IF判断这个数字后显示性别。
MOD函数:MOD(需判断的数字,2)
MOD函数共有两个参数,第一个参数表示需要判断的数字,第二个参数“2”表示用需判断的数字除以2,整个函数表示除以2后剩余几,等于0说明被除尽,表示双数,等于1说明未被除尽,表示单数。
IF函数:IF(条件,满足条件时的值,不满足条件时的值)
IF函数是用来判断对错的,可以理解为:如果这样,那么这样,否则那样,共有三个参数,第一个参数表示我们要设置的条件,如:D3=1;第二参数表示满足条件时的值,如果D3刚好等于1,说明满足条件,那么我们的第二参数就可以写你要显示的值,如:“男”(可以写你想显示的任何内容);第三个参数显示的是不符合指定条件的值,如“女”。
下面我们把IF、MOD、MID三个函数组合起来,就能得出我们想要的性别。
在C3单元格输入公式:=IF(MOD(MID(D3,17,1),2)=1,"男","女"),表示先用从MID(D3,17,1)把身份证号码第17位提取出来,再用MOD函数得出第17位数字是单数还是双数,最后嵌套IF函数判断经过处理的这个数字是否等于1,如果等于1,则显示“男”,如果不等于1,则显示“女”。如下图所示:
四、计算虚岁和周岁
刚才,我们利用函数轻松提取了出生日期,那计算年龄就容易多了,对于虚岁,我们直接用今年的年度数减去出生的年度即可,公式为:=2021-MID(D3,7,4),这里的4表示只提取出生的年度,对于周岁,我们需要用到DATEDIF函数。
DATEDIF函数:DATEDIF(开始日期,终止日期,比较单位)
DATEDIF函数共有三个参数,第一个参数表示开始日期,我们需要提取身份证号码的出生年月日,第二个参数表示终止日期,也就是今天的日期,用TODAY()表示,第三参数为单位,这里我们计算的是年龄,所以用“Y”表示,EXCEL中的日期,Y表示年,M表示月,D表示日。
在G3单元格输入公式:
=DATEDIF(TEXT(MID(D3,7,8),"0000-00-00"),TODAY(),"Y"),用TEXT和MID函数提取并规范出生日期,作为第一参数,用TODAY()表示今天的日期,作为第二参数,第三参数用“Y”,表示年。如下图所示:
想不到一个身份证号码竟能延伸出这么多信息,对批量处理信息、提升工作效率起到了很大作用,再也不用面对上百人信息时用计算器一个一个计算年龄了,也不用再问籍贯是哪里,通过一个身份证号码,所有基本信息统统搞定。
提示:
一、文中所用到的“全国行政区域身份证代码表”为本人在网上收集并通过长时间校对、完善、修正所得,演示用的身份证号码为随意编写。
二、文中所有函数里的符号如()、""等均为半角符号。
- 11-29生活
暴殄天物打一最佳生肖
- 01-28教育
公司租车合同模板
- 12-27科技
中国ccie在哪里考试
- 08-21教育
澳洲八大,这些你一定不知道的八卦!
- 03-23生活
盐是什么
- 02-17教育
erp实习报告范文精选
- 02-17教育
2022护士实习鉴定表自我鉴定
- 06-05生活
太白的准确位置在哪里
推荐
- 1鼠标里有电池可以上飞机吗387
- 2Access如何设置在屏幕提示中显示功能说明479
- 3买羽绒背心什么牌子好420
- 4大学生干部自我鉴定范文232
- 5青岛啤酒是日本的还是中国的?447
- 6酒店管理专业实习报告范文226