Excel中如何将日期格式进行转换.docx
《Excel中如何将日期格式进行转换.docx》由会员分享,可在线阅读,更多相关《Excel中如何将日期格式进行转换.docx(10页珍藏版)》请在冰豆网上搜索。
![Excel中如何将日期格式进行转换.docx](https://file1.bdocx.com/fileroot1/2023-1/24/424ed380-0e02-4981-a831-b595038cfd60/424ed380-0e02-4981-a831-b595038cfd601.gif)
Excel中如何将日期格式进行转换
Excel中如何将日期格式进行转换在EXCEL中如何将日期格式转换如何将格式转换成1900-01-01的格式?
如果日期所在单元格在A列,并从A1开始。
1、插入一辅助列B列,
2、在B1输入:
=MID(A1,1,4)&-MID(A1,5z2)&-MID(A1/7,2)回车,
3、向下填充该公式。
4、选中B列一复制一粘贴一选择性粘贴一数值一确定
5、删除A列
在EXCEL中如何利用身份证号码提取出生时间
假设身份证号在A1,时间格式为1900-1-1
=IF(LEN(A1)=15/,,19M&MID(Ab7,2)&1」1&MID(A1,9,2)gigMID(A:
L1:
L2)・MID(A:
L74)&「&MID(A14^2)&〕&MID(Abl3・2))
在EXCEL中如何利用身份证号码提取性别
=IF(LEN(A1)=15」F(MOD(IVIID(A1J5男丁女“),男罗女”))
在EXCEL中如何利用身份证号码计算周岁
假设身份证号在C1,时间格式为1900-1-1,出生时间为X
答案1:
二DATEDIF(IF(LEN(Al)=15ri9“&MID(Al/7,2)&I,J,&MID(A1,9,2)&7&MID(AlJb2),MID(A47,4)&叮
&MID(AlJb2)&l,・1'&MID(Alg2));TODAY()/y")
简化之:
=DATEDIF(X,TODAY();,y/,)
答案2:
二TEXT(TODAY(HF(LEN(C1)=15/19“&MID(C172)&,,-,,&MID(Cl/9/2)&MID(ClJb2)NID(Cb7,4)&MID(Cl/ll,2)&"-"&MID(Cl/13/2))z"yy")
简化之:
二TEXT(TODAY()・X/yy”)
在EXCEL中如何利用身份证号码计算周岁
假设身份证号在ci:
=IF(C1=,,,,,,,,,/IF(IF(LEN(C1)=15/,,19,I&MID(C1,7Z2)&"」&MIDfCl^^)&,i-i,&MID(C1,11,2)/MID(C1/7/4)&'1-11&MID(ClJl,2)&i&MID(ClJ3,2))72009J2・3U「7rEXT(TODAY(HF(LEN(Cl)=15/19“&MID(Cl,7,2)&」&MID(UL92)&,1」1&MID(Clzll/2),MID(Cl/7/4)&,l-,,&MID(C141/2)&,,J,&MID(Cl/13/2))/,1yyH)))
按身份证号码显示18-65周岁年龄段的人
在Excel中如何利用身份证号码显示18-65周岁年龄段的人,超过这个年龄段的显示为'不符合规定”具体为E2为身份证号,B2显示年龄(显示18-65周岁年龄段的人实际年龄,超过这个年龄段的显示为“不符合规定”)
B2=IF(ABS(DATEDIF(TEXT((LEN(E2)=15)*19&MID(Bl/7,6+(LEN(E2)=18)*2)z,,#-00-00")/NOW()z"Y,,)-41.5)<24,DATEDIF(TEXT((LEN(E2)=15)*19&MID(E2/7/6+(LEN(Bl)=18)*2)/"#-00-00")/NOW()/"Y")/"不符合规定”)
注:
公式同时适用15位和18位身份证.
在一列中找出包含另一列数据的单元格
在A列中找出包含B列数据的单元格
用下面的公式:
=if(iserror(find(a1,b1包含”)
下拉复制公式。
或者用下面的公式:
=if(isnumber(find(a1,b1)),"包含”,"”)下拉复制公式。
如图。
A
B
C
D
1
村组
村组
2
指封山村但家院组
指封山村但家院组
包含
包含
3
真龙地村赴宴沖组
赴宴沖组
句含
句含
4
凡冲村程家湾组
凡冲村程家湾
包含
包含
5
百福庵村郑小家组
百福庵郑小家组
EXCEL两列数据对比,找出不重复的项
有两列数据A,B,A中4000条数据,B中3600条,如何挑选出在A中出现,在B中没有出现
的数据?
解答:
在C1单元格输入:
=IF(COUNTIF但:
B,A1)=0,A1,““);然后回车双击填充柄这样就出现想要的数据了;如图:
ABC
1
村组
村组
结果
2
百福庵村郑小家组
百福庵郑小家组
百福淹村郑小家组
3
大沙轨村曹家湾组
曹家湾组
大沙境村曹家湾组
4
凡冲村朱家湾组
凡冲村朱家湾
凡冲村朱家湾组
5
鸟观嘴村储家湾组
乌观嘴村储家灣组
r
在两列中査找重复的数据
如果有两列相似的数据,要在其中一列中找出在另一列中重复的内容,可以用条件格式,还可以使用公式来查找。
这里介绍用公式的方法,假如在Al:
A10和B1:
B14有两列名单,现在要找岀B列中与A列相同的数据,如图。
在Cl单元格中输入公式:
=ISERROR(VLOOKUP(B1,$AS1:
SAS10,1,0))
回车后双击填充柄,将公式填充到C2:
C14区域。
可以看到重复的数据在C列中都显示为
"FALSE",而不重复的数据都显示为“TRUE”。
这里用VLOOKUP函数在A列中查找B列中的数据,如果没有找到则VLOOKUP函数返回错误“#N/A”,然后用ISERROR函数返回“TRUE”。
如果找到则VLOOKUP函数返回所查找的数据,ISERROR返回“FALSE”。
还可以在Cl单元格使用下面的数组公式:
=IF(SUM(-(B1=SAS1:
$AS10));'重复",“”)
公式输入完毕后按Ctrl+Shift+Enter结束,对于重复的数据公式返回“重复”。
下面的这个公式对于重复的数据返回T”。
=COUNTIF($A$1:
$A$10,B1)
怎样从一列中提取不重复的数据
如果Excel工作表的某列中包含一些数据,其中有部分数据是重复的,要从该列中提取不重复的数据,即形成一列无重复的数据,除了使用高级筛选、数据透视表等方法外,还可以用公式来实现。
提取不重复值的公式非常多,下面就是两个比较简单的。
假如在A2:
A19中有一列名单,现在要将无重复的名单放在B列中。
公式一:
用INDEX、MATCH和COUNTIF函数组成的数组公式
在B2单元格中输入数组公式:
=INDEX($A$2:
$A$19,MATCH(O.COUNTIF($B$1:
B1,$A$2:
$A$19),0))
Excel中如何将日期格式进行转换
按Ctrl+Shift+Enter结束,然后拖动填充柄向下填充,直到出现缚N¥为止。
1
名单
无重复的名单
2
—灯大师
—灯大师
3
一灯大师
黄药师
4
黄药师
洪七公
5
洪七公
丘处机
6
丘处机
黄蓉
7
周伯通
8
黄蓉
梅超风
9
周伯通
郭靖
10
梅超风
欧阳1峰
11
郭靖
华筝
12
郭靖
杨康
13
歐阳帷
陆辺英
14
华筝
灵智上人
15
杨康
F#N/A
16
陆冠央
r#N/A
17
灵智上人
MA
IgWWWlU^XC
^112^Acn
19
周伯通
#N/A
公式二:
不显示巫复的数据的公式在B2单元格中输入公式:
=IF(COUNTIF($A$2:
A2,A2)=LA2:
M,),然后拖动填充柄到B19单元格。
AB
1
名单
无重夏的名单
2
—灯大师
—灯大师
3
一灯大师
4
黄药师
黄药师
5
洪七公
洪七公
6
丘处机
丘处机
7
黄茜
黄蓉
8
黄蓉
9
周伯通
周伯通
10
11
郭靖
郭靖
12
郭靖
13
欧阳i峰
欧阳嵯
14
华筝
华筝
15
杨康
杨康
16
陆冠英
陆冠英
17
灵智上人
灵智上人
IgwwwIP^xc
si123.cn
19
周伯通
该公式让重复的数据显示为空值,虽然这些数据看起来不连续,但也得到了不重复的数据。
判断同一区域中是否包含重复值的公式
如果需要用公式来判断某个区域中是否包含重复值,可以用下面的数组公式。
假如
要判断的区域为D1:
G5,在某个单元格中输入数组公式:
=MAX(COUNTIF(D1:
G5,D1:
G5))=1
公式输入完毕按Ctrl+Shift+Enter结束,如果区域中有重复的内容,则公式返回FALSE,
如果区域中没有重复内容,则返回TRUEo
Excel中如何将日期格式进行转换去[{司lAX(COinrHF(Dl:
G5,D1:
G5))=l}C~IFT""F"G
1
1FALSE!
WWW
•excell23.cn
1
公式说明:
本例在数组公式中COUNTIF函数的两个参数都是同一区域,这样就可以求出区域中每个单元格的重复次数,并返回一个5行4列数组。
在公式中选择COUNTIF(D1:
G5,D1:
G5),按F9键就可以看到:
{1,1,1,1;2,1,1,1;1,1,1,1;1,1,1,1;1,1,1,2}
然后用MAX函数返回数组中的最大值,如果不为“1”公式就返回FALSE,否则返回
TRUEo
如何删除Excel表里的重复数据
如何在Excel中删除重复记录或创建不重复记录的列表
当MicrosoftExcel导入或合并多条记录时,可能会创建重复的记录。
本文介绍如何创建不重复记录的汇总列表。
要创建不重复记录的汇总列表,请按照下列步骤操作:
1.选择要排序的行或记录上方的列标题。
2.在数据菜单中,指向筛选,然后单击高级筛选。
如果收到如下提示,请单击确定。
未检测到任何标题。
是否将选定区域的首行用作标题行?
3.在方式下,单击“将筛选结果复制到其他位置”。
4.单击以选中“选择不重复的记录”复选框。
5.在“列表区域”框中,键入或选择一个记录范围(如A3:
H10)。
6.在“条件区域”框中,键入或选择相同的记录范圉(即A3:
H10)
7.在“复制到”框中,键入用于存放信息的第一个单元格的地址,或直接选择该单元格。
请确保该区域有足够的空间,以免覆盖已存在的数据。
注意:
只能在活动的工作表中执行此操作。
8.单击确定。
注意
如果原始数据的第一条记录是重复的,则该记录在新列表中将出现两次。
只隐藏笫一行。
如果就地执行高级筛选命令,工作表仍包含所有记录。
重复的记录将被隐藏。
要变通解决这一问题,请指向“格式”菜单上的“行”,然后单击“取消隐藏”。
为两个函数“IF、COUNTIF"判断和统计来査找重复数据,方法:
如B列中B1-B218的数据查找或数组查找B1-E50,公式如下:
=IF(COUNTIF(Bl:
B218,Bl)=1,"无重复","有重复")
=IF(COUNTIF(Bl:
E50,Bl)=1,"无重复","有重复")
边输入时边查找重复数据方法:
1.使用“数据有效性”
选定我们需要检查的数据,如“Bl:
E20”,单击“数据一有效性”,出现“数据有效性”对话框(请见图1)。
在“允许”列表框处选择“自定义”,“公式”文本框中输入如下公式:
“二COUNTIF($B$1:
$E$2O,B1)二1”。
单击“出错警告”标签,勾选“输入无效数据时显示出错警告”,“样式”列表处选择“停止”,右侧为岀错提示信息,在“标题”处输入“数据重复”,“错误信息”处输入“数据重复,请重新输入
当我们在Bl:
E20区域中的D7单元格中输入“张三”后,因为与同在该区域的B4单元格内容重复,所以立刻会弹出一个警告对话框,阻止重复输入的数据。
只有取消或更换输入的内容后,输入操作才得到允许
2.使用“条件格式”
选定我们需要检查的数据区域,如Al:
E20,单击“格式一条件格式”,出现“条件格式”对话框(请见图4),“条件1”列表框处选择“公式”,在右侧的文本框中输入如下公式
“二COUNTIF($A$1:
$E$2O,E2O)>1”。
单击[格式]按钮,在“单元格格式”对话框中选择出现重复数据单元格的外观格式,如字体、边框和图案(本例中设置为浅绿色背景)。
当我们在Al:
E20区域中的CIO单元格中输入"张三”后,该区域中包含相同内容的C10和A6单元格底纹颜色立刻变为浅绿色
3.统计出现频率最高的数值
Excel中如何将日期格式进行转换
对于已经输入完毕的数据,我们还可以使用统计函数快速找出重复的数值。
假设我们需要检查的数据范围为“B2:
B200”,单击任意空白单元格,并输入下列公式“=mode(b2:
b200)”°该公式的返回值就是岀现频率最高的单元格数值。
当单元格的值为“N/A”时,表明没有数值重复的单元格。
此方法使用非常简单,但是它只适用于检查全部为数值类型的单元格,对其他类型的数据则无能为力,对以文本格式存储的数字也无效。