Excel中如何将日期格式进行转换之欧阳治创编.docx

Excel中如何将日期格式进行转换之欧阳治创编.docx

《Excel中如何将日期格式进行转换之欧阳治创编.docx》由会员分享，可在线阅读，更多相关《Excel中如何将日期格式进行转换之欧阳治创编.docx（10页珍藏版）》请在冰豆网上搜索。

Excel中如何将日期格式进行转换之欧阳治创编

在EXCEL中如何将日期格式转换

时间2021.03.10

创作：

欧阳治

如何将19000101格式转换成1900-01-01的格式？

如果日期所在单元格在A列，并从A1开始。

1、插入一辅助列B列，

2、在B1输入：

=MID（A1,1,4）&-MID（A1,5,2）&-MID（A1,7,2）回车，

3、向下填充该公式。

4、选中B列→复制→粘贴→选择性粘贴→数值→确定

5、删除A列

在EXCEL中如何利用身份证号码提取出生时间

假设身份证号在A1，时间格式为1900-1-1

=IF（LEN（A1）=15,"19"&MID（A1,7,2）&"-"&MID（A1,9,2）&"-"&MID（A1,11,2）,MID（A1,7,4）&"-"&MID（A1,11,2）&"-"&MID（A1,13,2））

在EXCEL中如何利用身份证号码提取性别

=IF（LEN（A1）=15,IF（MOD（MID（A1,15,1）,2）=1,"男","女"）,IF（MOD（MID（A1,17,1）,2）=1,"男","女"））

在EXCEL中如何利用身份证号码计算周岁

假设身份证号在C1，时间格式为1900-1-1，出生时间为X

答案1：

=DATEDIF（IF（LEN（A1）=15,"19"&MID（A1,7,2）&"-"&MID（A1,9,2）&"-"&MID（A1,11,2）,MID（A1,7,4）&"-"&MID（A1,11,2）&"-"&MID（A1,13,2））,TODAY（）,”y”）

简化之：

=DATEDIF（X,TODAY（）,”y”）

答案2：

=TEXT（TODAY（）-IF（LEN（C1）=15,"19"&MID（C1,7,2）&"-"&MID（C1,9,2）&"-"&MID（C1,11,2）,MID（C1,7,4）&"-"&MID（C1,11,2）&"-"&MID（C1,13,2））,"yy"）

简化之：

=TEXT（TODAY（）-X,”yy”）

在EXCEL中如何利用身份证号码计算周岁

假设身份证号在C1:

=IF（C1="","",IF（IF（LEN（C1）=15,"19"&MID（C1,7,2）&"-"&MID（C1,9,2）&"-"&MID（C1,11,2）,MID（C1,7,4）&"-"&MID（C1,11,2）&"-"&MID（C1,13,2））>"2009-12-31","",TEXT（TODAY（）-IF（LEN（C1）=15,"19"&MID（C1,7,2）&"-"&MID（C1,9,2）&"-"&MID（C1,11,2）,MID（C1,7,4）&"-"&MID（C1,11,2）&"-"&MID（C1,13,2））,"yy"）））

按身份证号码显示18-65周岁年龄段的人

在Excel中如何利用身份证号码显示18-65周岁年龄段的人，超过这个年龄段的显示为“不符合规定”具体为E2为身份证号，B2显示年龄（显示18-65周岁年龄段的人实际年龄，超过这个年龄段的显示为“不符合规定”）

B2=IF（ABS（DATEDIF（TEXT（（LEN（E2）=15）*19&MID（B1,7,6+（LEN（E2）=18）*2）,"#-00-00"）,NOW（）,"Y"）-41.5）<24,DATEDIF（TEXT（（LEN（E2）=15）*19&MID（E2,7,6+（LEN（B1）=18）*2）,"#-00-00"）,NOW（）,"Y"）,"不符合规定"）

注:

公式同时适用15位和18位身份证.

在一列中找出包含另一列数据的单元格

在A列中找出包含B列数据的单元格

用下面的公式：

=if（iserror（find（a1,b1））,"","包含"）

下拉复制公式。

或者用下面的公式：

=if（isnumber（find（a1,b1））,"包含",""）

下拉复制公式。

如图。

EXCEL两列数据对比，找出不重复的项

有两列数据A,B，A中4000条数据，B中3600条，如何挑选出在A中出现，在B中没有出现的数据？

解答：

在C1单元格输入：

=IF（COUNTIF（B:

B,A1）=0,A1,""）；然后回车双击填充柄

这样就出现想要的数据了；如图：

在两列中查找重复的数据

如果有两列相似的数据，要在其中一列中找出在另一列中重复的内容，可以用条件格式，还可以使用公式来查找。

这里介绍用公式的方法，假如在A1:

A10和B1:

B14有两列名单，现在要找出B列中与A列相同的数据，如图。

在C1单元格中输入公式：

=ISERROR（VLOOKUP（B1,$A$1:

$A$10,1,0））

回车后双击填充柄，将公式填充到C2:

C14区域。

可以看到重复的数据在C列中都显示为“FALSE”，而不重复的数据都显示为“TRUE”。

这里用VLOOKUP函数在A列中查找B列中的数据，如果没有找到则VLOOKUP函数返回错误“#N/A”，然后用ISERROR函数返回“TRUE”。

如果找到则VLOOKUP函数返回所查找的数据，ISERROR返回“FALSE”。

还可以在C1单元格使用下面的数组公式：

=IF（SUM（--（B1=$A$1:

$A$10））,"重复",""）

公式输入完毕后按Ctrl+Shift+Enter结束，对于重复的数据公式返回“重复”。

下面的这个公式对于重复的数据返回“1”。

=COUNTIF（$A$1:

$A$10,B1）

怎样从一列中提取不重复的数据

如果Excel工作表的某列中包含一些数据，其中有部分数据是重复的，要从该列中提取不重复的数据，即形成一列无重复的数据，除了使用高级筛选、数据透视表等方法外，还可以用公式来实现。

提取不重复值的公式非常多，下面就是两个比较简单的。

假如在A2:

A19中有一列名单，现在要将无重复的名单放在B列中。

公式一：

用INDEX、MATCH和COUNTIF函数组成的数组公式

在B2单元格中输入数组公式：

=INDEX（$A$2:

$A$19,MATCH（0,COUNTIF（$B$1:

B1,$A$2:

$A$19）,0））

按Ctrl+Shift+Enter结束，然后拖动填充柄向下填充，直到出现“#NA”为止。

公式二：

不显示重复的数据的公式

在B2单元格中输入公式：

=IF（COUNTIF（$A$2:

A2,A2）=1,A2,""），然后拖动填充柄到B19单元格。

该公式让重复的数据显示为空值，虽然这些数据看起来不连续，但也得到了不重复的数据。

判断同一区域中是否包含重复值的公式

如果需要用公式来判断某个区域中是否包含重复值，可以用下面的数组公式。

假如要判断的区域为D1:

G5，在某个单元格中输入数组公式：

=MAX（COUNTIF（D1:

G5,D1:

G5））=1

公式输入完毕按Ctrl＋Shift＋Enter结束，如果区域中有重复的内容，则公式返回FALSE，如果区域中没有重复内容，则返回TRUE。

公式说明：

本例在数组公式中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，否则返回TRUE。

如何删除Excel表里的重复数据

如何在Excel中删除重复记录或创建不重复记录的列表

当MicrosoftExcel导入或合并多条记录时，可能会创建重复的记录。

本文介绍如何创建不重复记录的汇总列表。

要创建不重复记录的汇总列表，请按照下列步骤操作：

1.选择要排序的行或记录上方的列标题。

2.在数据菜单中，指向筛选，然后单击高级筛选。

如果收到如下提示，请单击确定。

未检测到任何标题。

是否将选定区域的首行用作标题行？

3.在方式下，单击“将筛选结果复制到其他位置”。

4.单击以选中“选择不重复的记录”复选框。

5.在“列表区域”框中，键入或选择一个记录范围（如A3:

H10）。

6.在“条件区域”框中，键入或选择相同的记录范围（即A3:

H10）

7.在“复制到”框中，键入用于存放信息的第一个单元格的地址，或直接选择该单元格。

请确保该区域有足够的空间，以免覆盖已存在的数据。

注意：

只能在活动的工作表中执行此操作。

8.单击确定。

注意

如果原始数据的第一条记录是重复的，则该记录在新列表中将出现两次。

只隐藏第一行。

如果就地执行高级筛选命令，工作表仍包含所有记录。

重复的记录将被隐藏。

要变通解决这一问题，请指向“格式”菜单上的“行”，然后单击“取消隐藏”。

为两个函数“IF、COUNTIF”判断和统计来查找重复数据，方法：

如B列中B1－B218的数据查找或数组查找B1－E50，公式如下:

=IF（COUNTIF（B1:

B218,B1）=1,"无重复","有重复"）

=IF（COUNTIF（B1:

E50,B1）=1,"无重复","有重复"）

边输入时边查找重复数据方法：

1．使用“数据有效性”

选定我们需要检查的数据，如“B1:

E20”，单击“数据→有效性”，出现“数据有效性”对话框（请见图1）。

在“允许”列表框处选择“自定义”，“公式”文本框中输入如下公式：

“=COUNTIF（$B$1:

$E$20,B1）=1”。

单击“出错警告”标签，勾选“输入无效数据时显示出错警告”，“样式”列表处选择“停止”，右侧为出错提示信息，在“标题”处输入“数据重复”，“错误信息”处输入“数据重复，请重新输入

当我们在B1:

E20区域中的D7单元格中输入“张三”后，因为与同在该区域的B4单元格内容重复，所以立刻会弹出一个警告对话框，阻止重复输入的数据。

只有取消或更换输入的内容后，输入操作才得到允许

2．使用“条件格式”

选定我们需要检查的数据区域，如A1:

E20，单击“格式→条件格式”，出现“条件格式”对话框（请见图4），“条件1”列表框处选择“公式”，在右侧的文本框中输入如下公式“=COUNTIF（$A$1:

$E$20,E20）>1”。

单击[格式]按钮，在“单元格格式”对话框中选择出现重复数据单元格的外观格式，如字体、边框和图案（本例中设置为浅绿色背景）。

当我们在A1:

E20区域中的C10单元格中输入“张三”后，该区域中包含相同内容的C10和A6单元格底纹颜色立刻变为浅绿色

3．统计出现频率最高的数值

对于已经输入完毕的数据，我们还可以使用统计函数快速找出重复的数值。

假设我们需要检查的数据范围为“B2:

B200”，单击任意空白单元格，并输入下列公式“=mode（b2:

b200）”。

该公式的返回值就是出现频率最高的单元格数值。

当单元格的值为“N/A”时，表明没有数值重复的单元格。

此方法使用非常简单，但是它只适用于检查全部为数值类型的单元格，对其他类型的数据则无能为力，对以文本格式存储的数字也无效。

时间2021.03.10

创作：

欧阳治