文档库 最新最全的文档下载
当前位置:文档库 › Excel使用技巧----- 轻松输入身份证号码

Excel使用技巧----- 轻松输入身份证号码

Excel使用技巧----- 轻松输入身份证号码
Excel使用技巧----- 轻松输入身份证号码

Excel使用技巧

----- 轻松输入身份证号码

当我们在Excel中输入身份证号码时,由于身份证是18位数字,超过了Excel单元格的默认值,因此会自动改变记数格式而变成类似1.23457E+17的格式。那么,怎样才能保证输入的身份证号码是正确的呢?有没有什么简便方法呢?下面就告诉大家两个小技巧。

一、选中要输入身份证号码的单元格,然后点击右键选择设置单元格格式,选择其中的文本格式点击确定退出。怎么样?已经是正确的了。

二、大家知道,身份证的前六位代表的是地域信息,如果是批量输入身份证号,反复的去输入这六位数岂不是很麻烦。当你看过下面的技巧之后,你就会感到批量输入也轻松。

依然是选中要输入身份证号码的单元格,然后点击右键选择设置单元格格式,选择其中的自定义,在类型的空格当中输入六位地域代码如:123456,然后输入12个0(如果都是19××年出生的,还可以在123456后边输入19,再输入10个0就可以了),点击确定退出,再输入12(10)位个人代码。是不是完整的身份证号码出来了?方便很多吧!

看不懂的请看视频教程

https://www.wendangku.net/doc/48231819.html,/b/12708342-38716548.html

Excel使用技巧2008-10-03 11:23

分类:办公自动化

字号:大中小

在工作当中用电子表格来处理数据将会更加迅速、方便,而在各种电子表格处理软件中Excel以其功能强大、操作方便著称,赢得了广大用户的青睐。虽然Excel使用很简单,不过真正能用好Excel的用户并不多,很多人一直停留在录入数据的水平,本文将向你介绍一些非常使用的技巧,掌握这些技巧将大大提高你的工作效率.

⒈快速定义工作簿格式

首先选定需要定义格式的工作簿范围,单击“格式”菜单的“样式”命令,打开“样式”对话框;然后从“样式名”列表框中选择合适的“样式”种类,从“样式包括”列表框中选择是否使用该种样式的数字、字体、对齐、边框、图案、保护等格式内容;单击“确定”按钮,关闭“样式”对话框,Excel工作簿的格式就会按照用户指定的样式发生变化,从而满足了用户快速、大批定义格式的要求。

⒉快速复制公式

复制是将公式应用于其它单元格的操作,最常用的有以下几种方法:

一是拖动制复制。操作方法是:选中存放公式的单元格,移动空心十字光标至单元格右下角。待光标变成小实心十字时,按住鼠标左键沿列(对行计算时)或行(对列计算时)拖动,至数据结尾完成公式的复制和计算。公式复制的快慢可由小实心十字光标距虚框的远近来调节:小实心十字光标距虚框越远,复制越快;反之,复制越慢。

也可以输入复制。此法是在公式输入结束后立即完成公式的复制。操作方法:选中需要使用该公式的所有单元格,用上面介绍的方法输入公式,完成后按住Ctrl键并按回车键,该公式就被复制到已选中的所有单元格。

还可以选择性粘贴。操作方法是:选中存放公式的单元格,单击Excel 工具栏中的“复制”按钮。然后选中需要使用该公式的单元格,在选中区域内单击鼠标右键,选择快捷选单中的“选择性粘贴”命令。打开“选择性粘贴”对话框后选中“粘贴”命令,单击“确定”,公式就被复制到已选中的单元格。

⒊快速显示单元格中的公式

如果工作表中的数据多数是由公式生成的,如果想要快速知道每个单元格中的公式形式,可以这样做:用鼠标左键单击“工具”菜单,选取“选项”命令,出现“选项”对话框,单击“视图”选项卡,接着设置“窗口选项”栏下的“公式”项有

效,单击“确定”按钮。这时每个单元格中的公式就显示出来了。如果想恢复公式计算结果的显示,再设置“窗口选项”栏下的“公式”项失效即可。

⒋快速删除空行

有时为了删除Excel工作簿中的空行,你可能会将空行一一找出然后删除,这样做非常不方便。你可以利用“自动筛选”功能来简单实现。先在表中插入新的一行(全空),然后选择表中所有的行,选择“数据”菜单中的“筛选”,再选择“自动筛选”命令。在每一列的项部,从下拉列表中选择“空白”。在所有数据都被选中的情况下,选择“编辑”菜单中的“删除行”,然后按“确定”即可。所有的空行将被删去。插入一个空行是为了避免删除第一行数据。

⒌自动切换输入法

当你使用Excel 2000编辑文件时,在一张工作表中通常是既有汉字,又有字母和数字,于是对于不同的单元格,需要不断地切换中英文输入方式,这不仅降低了编辑效率,而且让人不胜其烦。在此,笔者介绍一种方法,让你在Excel 2000中对不同类型的单元格,实现输入法的自动切换。

新建或打开需要输入汉字的单元格区域,单击“数据”菜单中的“有效性”,再选择“输入法模式”选项卡,在“模式”下拉列表框中选择“打开”,单击“确定”按钮。

选择需要输入字母或数字的单元格区域,单击“数据”菜单中的“有效性”,再选择“输入法模式”选项卡,在“模式”下拉列表框中选择“关闭(英文模式)”,单击“确定”按钮。

之后,当插入点处于不同的单元格时,Excel 2000能够根据我们进行的设置,自动在中英文输入法间进行切换。就是说,当插入点处于刚才我们设置

为输入汉字的单元格时,系统自动切换到中文输入状态,当插入点处于刚才我们设置为输入数字或字母单元格时,系统又能自动关闭中文输入法。

⒍自动调整小数点

如果你有一大批小于1的数字要录入到Excel工作表中,如果录入前先进行下面的设置,将会使你的输入速度成倍提高。

单击“工具”菜单中的“选项”,然后单击“编辑”选项卡,选中“自动设置小数点”复选框,在“位数”微调编辑框中键入需要显示在小数点右面的位数。在此,我们键入“2”单击“确定”按钮。

完成之后,如果在工作表的某单元格中键入“4”,则在你按了回车键之后,该单元格的数字自动变为“0.04”。方便多了吧!此时如果你在单元格中键入的是“8888”,则在你结束输入之后,该单元格的数字自动变为“88.88”。

⒎用“记忆式输入”

有时我们需要在一个工作表中的某一列输入相同数值,这时如果采用“记忆式输入”会帮你很大的忙。如在职称统计表中要多次输入“助理工程师”,当第一次输入后,第二次又要输入这些文字时,只需要编辑框中输入“助”字,Excel2000会用“助”字与这一列所有的内容相匹配,若“助”字与该列已有的录入项相符,则Excel2000会将剩下的“助理工程师”四字自动填入。

按下列方法设置“记忆式输入”:选择“工具”中的“选项”命令,然后选择“选项”对话框中的“编辑”选项卡,选中其中的“记忆式键入”即可。

⒏用“自动更正”方式实现快速输入

使用该功能不仅可以更正输入中偶然的笔误,也可能把一段经常使用的文字定义为一条短语,当输入该条短语时,“自动更正”便会将它更换成所定义的文字。你也可以定义自己的“自动更正”项目:首先,选择“工具”中的“自动更正”命令;然后,在弹出的“自动更正”对话框中的“替换”框中键入短语“爱好者”,在“替换为”框中键入要替换的内容“电脑爱好者的读者”;最后,单击“确定”退出。以后只要输入“爱好者”,则整个名称就会输到表格中。

⒐用下拉列表快速输入数据

如果你希望减少手工录入的工作量,可以用下拉表来实现。创建下拉列表方法为:首先,选中需要显示下拉列表的单元格或单元格区域;接着,选择菜单“数据”菜单中的“有效性”命令,从有效数据对话框中选择“序列”,单击“来源”栏右侧的小图标,将打开一个新的“有效数据”小对话框;接着,在该对话框中输入下拉列表中所需要的数据,项目和项目之间用逗号隔开,比如输入“工程师,助工工程师,技术员”,然后回车。注意在对话框中选择“提供下拉箭头”复选框;最后单击“确定”即可。

10.两次选定单元格

有时,我们需要在某个单元格内连续输入多个测试值,以查看引用此单元格的其他单元格的效果。但每次输入一个值后按Enter键,活动单元格均默认下移一个单元格,非常不便。此时,你肯定会通过选择“工具”\“选项"\“编辑",取消“按Enter键移动活动单元格标识框"选项的选定来实现在同一单元格内输入许多测试值,但以后你还得将此选项选定,显得比较麻烦。其实,采用两次选定单元格方法就显得灵活、方便:

单击鼠标选定单元格,然后按住Ctrl键再次单击鼠标选定此单元格(此时,单元格周围将出现实线框)。

11.“Shift+拖放"的妙用

在拖放选定的一个或多个单元格至新的位置时,同时按住Shift键可以快速修改单元格内容的次序。具体方法为:选定单元格,按下Shift键,移动鼠标指针至单元格边缘,直至出现拖放指针箭头“?",然后进行拖放操作。上下拖拉时鼠标在单元格间边界处会变为一个水平“工"状标志,左右拖拉时会变为垂直“工"状标志,释放鼠标按钮完成操作后,单元格间的次序即发生了变化。这种简单的方法节省了几个剪切和粘贴或拖放操作,非常方便。

12.超越工作表保护的诀窍

如果你想使用一个保护了的工作表,但又不知道其口令,有办法吗?有。选定工作表,选择“编辑"\“复制"、“粘贴",将其拷贝到一个新的工作簿中(注意:一定要新工作簿),即可超越工作表保护。

13.巧用IF函数

(1).设有一工作表,C1单元格的计算公式为:=A1/B1,当A1、B1单元格没有输入数据时,C1单元格会出现“#DIV/0!”的错误信息。这不仅破坏了屏幕显示的美观,特别是在报表打印时出现“#DIV/0!”的信息更不是用户所希望的。此时,可用IF函数将C1单元格的计算公式更改为:=IF(B1=0,″″,A1/B1)。这样,只有当B1单元格的值是非零时,C1单元格的值才按A1/B1进行计算更新,从而有效地避免了上述情况的出现。

(2).设有C2单元格的计算公式为:=A2+B2,当A2、B2没有输入数值时,C2出现的结果是“0”,同样,利用IF函数把C2单元格的计算公式更改如下:=IF(AND(A2=″″,B2=″″),″″,A2+B2)。这样,如果A2与B2单元格均没有输入数值时,C2单元格就不进行A2+B2的计算更新,也就不会出现“0”值的提示。

(3).设C3单元格存放学生成绩的数据,D3单元格根据C3(学员成绩)情况给出相应的“及格”、“不及格”的信息。可用IF条件函数实现D3单元格的自动填充,D3的计算公式为:=IF(C3<60,″不及格″,″及格″=。

14.累加小技巧

我们在工作中常常需要在已有数值的单元格中再增加或减去另一个数。一般是在计算器中计算后再覆盖原有的数据。这样操作起来很不方便。这里有一个小技巧,可以有效地简化老式的工作过程。(1).创建一个宏:

选择Excel选单下的“工具→宏→录制新宏”选项;

宏名为:MyMacro;

快捷键为:Ctrl+Shift+J(只要不和Excel本身的快捷键重名就行);

保存在:个人宏工作簿(可以在所有Excel工作簿中使用)。

(2).用鼠标选择“停止录入”工具栏中的方块,停止录入宏。

(3).选择Excel选单下的“工具→宏→Visual Basic编辑器”选项。

(4).在“Visual Basic编辑器”左上角的VBA Project中用鼠标双击VBAProject(Personal.xls)打开“模块→Module1”。

注意:你的模块可能不是Module1 ,也许是Module2、Module3。

(5).在右侧的代码窗口中将Personal.xls-Module1(Code)中的代码更改为:

Sub MyMacro( )

OldValue = Val(ActiveCell.Value)

InputValue = InputBox(“输入数值,负数前输入减号”,“小小计算器”)

ActiveCell.Value = Val(OldValue+InputValue)

End Sub

(6).关闭Visual Basic编辑器。

编辑完毕,你可以试试刚刚编辑的宏,按下Shift+Ctrl+J键,输入数值并按下“确定”键。(这段代码只提供了加减运算,借以抛砖引玉。)

15.怎样保护表格中的数据

假设要实现在合计项和小计项不能输入数据,由公式自动计算。

首先,输入文字及数字,在合计项F4至F7单元格中依次输入公式:=SUM (B4∶E4)、=SUM(B5∶E5)、=SUM(B6∶E6)、=SUM(B7∶E7),在小计项B8至F8单元格中依次输入公式:=SUM(B4∶B7)、=SUM(C4∶C7)、=SUM(D4∶D7)、=SUM(E4∶E7)、=SUM(F4∶F7)。在默认情况下,整个表格的单元格都是锁定的,但是,由于工作表没有被保护,因此锁定不起作用。

选取单元格A1∶F8,点击“格式→单元格”选单,选择“保护”选项,消除锁定复选框前的对勾,单击确定。然后,再选取单元格F4∶F7和B8∶F8,点击“格式→单元格”选单,选择“保护”选项,使锁定复选框选中,单击确定,这样,就把这些单元格锁定了。接着,点击“工具→保护→保护工作表”选单,这时,会要求你输入密码,输入两次相同的密码后,点击确定,工作表就被保护起来了,单元格的锁定也就生效了。今后,可以放心地输入数据而不必担心破坏公式。如果要修改公式,则点击“工具→保护→撤消保护工作表”选单,这时,会要求你输入密码,输入正确的密码后,就可任意修改公式了。

16.如何避免Excel中的错误信息

在Excel中输入或编辑公式后,有可能不能正确计算出结果,Excel将显示一个错误信息,引起错误的原因并不都是由公式本身有错误产生的。下面我们将介绍五种在Excel中常出现的错误信息,以及如何纠正这些错误。

错误信息1—####

输入到单元格中的数据太长或单元格公式所产生的结果太大,在单元格中显示不下时,将在单元格中显示####。可以通过调整列标之间的边界来修改列的宽度。

如果对日期和时间做减法,请确认格式是否正确。Excel中的日期和时间必须为正值。如果日期或时间产生了负值,将在整个单元格中显示####。如果仍要显示这个数值,请单击“格式”菜单中的“单元格”命令,再单击“数字”选项卡,然后选定一个不是日期或时间的格式。

错误信息2—#DIV/0!

输入的公式中包含明显的除数0,例如-120/0,则会产生错误信息DIV/0!。

或在公式中除数使用了空单元格(当运算对象是空白单元格,Excel将此空值解释为零值)或包含零值单元格的单元格引用。解决办法是修改单元格引用,或者在用作除数的单元格中输入不为零的值。

错误信息3—#VALUE!

当使用不正确的参数或运算符时,或者当执行自动更正公式功能时不能更正公式,都将产生错误信息#VALUE!。

在需要数字或逻辑值时输入了文本,Excel不能将文本转换为正确的数据类型。这时应确认公式或函数所需的运算符或参数正确,并且公式引用的单元格中包含有效的数值。例如,单元格B3中有一个数字,而单元格B4包含文本,则公式=B3+B4将返回错误信息#VALUE!。

错误信息4—#NAME?

在公式中使用了Excel所不能识别的文本时将产生错误信息#NAME?。可以从以下几方面进行检查纠正错误:

(1)如果是使用了不存在的名称而产生这类错误,应确认使用的名称确实存在。在“插入”菜单中指向“名称”,再单击“定义”命令,如果所需名称没有被列出,请使用“定义”命令添加相应的名称。

(2)如果是名称,函数名拼写错误应修改拼写错误。

(3)确认公式中使用的所有区域引用都使用了冒号(:)。例如:SUM(A1:C10)。

注意将公式中的文本括在双引号中。

错误信息5—#NUM!

当公式或函数中使用了不正确的数字时将产生错误信息#NUM!。

要解决问题首先要确认函数中使用的参数类型正确。还有一种可能是由公式产生的数字太大或太小,Excel不能表示,如果是这种情况就要修改公式,使其结果在-1×10307和1×10307之间。

17. 不用编程--Excel公式也能计算个人所得税

个人所得税的计算看起来比较复杂,似乎不用VBA宏编程而只用公式来计算是一件不可能的事。其实,Excel提供的函数公式不但可以计算个人所得税,而且还有很大的灵活:可以随意改变不扣税基数,随意改变各扣税分段界限值及其扣税税率(说不定以后调整个人所得税时就可以用到。)

不管是编程还是使用公式,都得将个人所得税的方法转化为数学公式,并且最好将这个公式化简,为以后工作减少困难。以X代表你的应缴税(减去免税基数)的工薪收入(这里的个人所得税仅以工薪为例),Tax代表应缴所得税,那么:

当500<X≤2000则TAX=(X-500)*10+500*5 =>TAX=X*10-25

当2000<X≤5000则TAX=(X-2000)*15+2000*10 =>TAX=X*15-125

......

依此类推,通用公式为:个人所得税=应缴税工薪收入*该范围税率-扣除数

在此,扣除数=应缴税工薪收入上一范围上限*该范围税率-上一范围扣除数

其实只有四个公式,即绿色背景处。黄色背景处则为计算时输入数据的地方。各处公式设置即说明如下:

E3:=C3*D3-C3*D2+E2

E4-E10:根据E3填充得到,或者拷贝E3粘贴得到

C15:=IF(B15>$B$12,B15-$B$12,0)如果所得工薪大于不扣税基数,则应纳税工薪为工薪减去为零不扣税基数,否则,应纳税工薪零。

D15:=VLOOKUP(C15,$C$2:$C$10,1)查阅应纳税工薪属于哪个扣税范围。

E15:=C15*VLOOKUP(D15,$C$2:$E$10,2)-VLOOKUP(D15,$C$2:$E$10,3)查阅该扣税范围扣税税率和应减的扣除数。这里主要用到VLOOKUP函数,可查阅帮助获取更多信息。

C15,D15的公式可以合并到E15中,那样可读性会差很多,但表格会清晰一些。合并后公式:=IF(B15>$B$12,B15-$B$12,0)*VLOOKUP(VLOOKUP(IF(B15>$B$12,B15-$B $12,0),$C$2:$C$10,1),$C$2:$E$10,2)-VLOOKUP(VLOOKUP(IF(B15>$B$12, B15-$B$12,0),$C$2:$C$10,1),$C$2:$E$10,3)实际上是将公式中出现的C15,D15用其公式替代即可。

18. 用EXCEL轻松处理学生成绩

期末考试结束后,主任要求班主任自已统计本班成绩,尽快上报教导处。流程包括录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表等。有了EXCEL,我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了!

我迅速地打开电脑,启动EXCEL2000,录入学生的考试成绩。然后在J2单元格处输入公式"=sum(c2:i2)",然后拖动填充柄向下填充,便得到了每人的

总分。接着在k2单元格处输入公式"=average(c2:i2)",然后拖动填充柄向下填充,便得到了每人的平均分。

平均分只需保留一位小数,多了没用。所以选中第k列,用鼠标右键单击,从弹出的快捷菜单中选"设置单元格格式(F)…",在数字标签中选中"数值",小数位数设置为1位。

下面按总分给学生排出名次。

在L2单元格处输入公式"RANK(J2,J$2:J$77,0)",然后拖动填充柄向下填充,即可得到每人在班中的名次(请参考图1)。

说明:此处排名次用到了RANK函数,它的语法为:

RANK(number,ref,order)

其中number为需要找到排位的数字。

Ref为包含一组数字的数组或引用。Ref 中的非数值型参数将被忽略。

Order为一数字,指明排位的方式。

·如果order 为0 或省略,Microsoft Excel 将ref 当作按降序排列的数据清单进行排位。

·如果order 不为零,Microsoft Excel 将ref 当作按升序排列的数据清单进行排位。

最后,单击L1单元格,然后在“工具”菜单中选“排序”->“升序”,即可按照名次顺序显示各学生成绩。

另外,我们还希望把不及格的学科突出显示,最好用红色显示。于是拖拉选择C2:E78(即所有学生语、数、外三科成绩),然后执行"格式"菜单下"条件格式"命令,弹出"条件格式对话框"。我们把条件设为小于72分的用红色显示(因为这三科每科总分为120分),点击"格式"按钮,把颜色设为红色。再按"确定"按钮。然后用同样的方法把理、化、政、历四科小于60分的也用红色显示(因为这四科每科总分为100分)。

下面我们来统计各科的分数段以及及格率、优生率、综合指数等。

下面我们来统计各科的分数段以及及格率、优生率、综合指数等。

(1)60分以下人数:在C78单元格处输入公式"=COUNTIF(C2:C77,"<60")",拖动填充柄向右填充至I78单元格处;

(2)60分~69分人数:在C79单元格处输入公式"=COUNTIF(C2:C77,">=60")-COUNTIF(C2:C77,">=70")",拖动填充柄向右填充;

(3)70分~79分人数:在C80单元格处输入公式"=COUNTIF(C2:C77,">=70")-COUNTIF(C2:C77,">=80")",拖动填充柄向右填充;

(4)80分~89分人数:在C81单元格处输入公式"=COUNTIF(C2:C77,">=80")-COUNTIF(C2:C77,">=90")",拖动填充柄向右填充;

(5)90分以上人数:在C82单元格处输入公式"=COUNTIF(C2:C77,">=90")",拖动填充柄向右填充;

(6)平均分:在C83单元格处输入公式"=AVERAGE(C2:C77)",拖动填充柄向右填充至I83;

(7)最高分:在C84单元格处输入公式"=MAX(C2:C77)",拖动填充柄向右填充至I84;

(8)低分率:是指各科40分以下人数与总人数的比值。在C85单元格处输入公式"=COUNTIF(C2:C77,"<=40")/COUNT(C2:C77)*100",拖动填充柄向右填充至I85;

(9)及格率:语、数、外三科及格分为72分,所以在C86单元格处输入公式"=(COUNTIF(C2:C77,">=72")/COUNT(C2:C77))*100",并拖动填充柄向右填充至E86;而理、化、政、历等四科及格分60分,所以在F86单元格处输入公式"=(COUNTIF(F2:F77,">=60")/COUNT(F2:F77))*100",并拖动填充柄向右填充至I86;

(10)优生率:语、数、外三科96分以上为优生,所以在C87单元格处输入公式"=(COUNTIF(C2:C77,">=96")/COUNT(C2:C77))*100",拖动填充柄向右填充至E87;理、化、政、历等四科80分以上为优生,所以在F87单元格处输入公式"=(COUNTIF(F2:F77,">=80")/COUNT(F2:F77))*100",拖动填充柄向右填充至I87处;如图3所示。

(11)综合指数:我们学校的综合指数的计算公式为z=[(1+优生率-低分率)/2+及格率+平均分/该科总分]/3。所以在C88单元格处输入公式"=((1+C87/100-C85/100)/2+C86/100+C83/120)/3",拖动填充柄向右填充至E88;在F88单元格处输入公式"=((1+F87/100-F85/100)/2+F86/100+F83/100)/3",拖动填充柄向右填充至I88。

对了,为了让别人对各科的分数段有一个较直观的认识,可以考虑采用图表。单击“插入”菜单中“图表”命令,弹出“图表向导”对话框,在“图表类型”列表框中选择一种图型,如“饼图”,单击“下一步”,单击“数据区域”文本框右边的压缩列表框,拖拉选择B78:C82,再次点击该压缩列表框;单击“下一步”,输入图表标题,如“高一(1)班语文成绩分析图”;单击“下一步”,再单击“完成”。如图4所示。其它各科同样处理,但在拖拉选择数据区域时,因为是不连续的区域,所以要按住“Ctrl”键。好!一切OK!

且慢!为了以后的考试中不再重复上述繁琐的工作,最好把上述工作表另存为一个模板。于是我把上述工作表复制一份到另一工作簿中,然后删掉所有学生的单科成绩(即表中C2:I77部分),执行"文件"菜单中的"另存为"命令,在"保存类型"下拉列表框中选"模板(*.xlt)",把它保存为一个模板文件,这下可以一劳永逸了。

[此贴子已经被作者于2008-6-16 11:12:04编辑过]

19. 用EXCEL轻松准备考前工作

大考在即,主任要求“考务工作必须电子化”,为了万无一失,还特意提供给班主任一份考务工作流程图:

考前:考场编排→打印单科成绩册→打印考场记录单→打印准考证号

考后:录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表

既然任务已经明确,先把考前的准备工作做好吧!具体工作包括:考场编排→打印单科成绩册→打印考场记录单→打印准考证号

1、单科成绩册的编制和准考证号的自动编制

(1)我负责的班级这次考试共7门课程,为了区分科目,只需在EXCEL 表头处设置“科目”、“班级”和“任课教师姓名”等。考生的姓名可以从学生学籍表中提取,考号如何编制和处理呢?为了以后登分和查找方便,考号基本按学籍号顺序把整个年级的考号放在一起编码。编码由两部分组成:①考场编号(三位数字),②班内顺序号(三位数字)。为了加快录入速度,分别把它们放在两列中,这样可以分别进行填充。

(2)新建工作表“考场编号”,从学生学籍表中复制“姓名”列置于该表A 列,首先设置B(准考证号)、C(班内编号)、D(考场编号)列的数据格式为文本方式。

(3)在B2单元格输入公式“=D2&C2”(其中的“&”为连接运算,将“考场编号”和“班内编号”连接成一个字符串),用鼠标选中B2单元格,将鼠标指向该区域右下角的填充柄,双击鼠标(这可是本人总结出来的最快的填充方法,下称“双击填充法”)即可将该公式自动填充至最后一个考生(因为此时还没有在C、D 列输入数据,暂时B列中数据也没有出现)。在C2单元格中输入“001”,C3单元格输入“002”,用鼠标拖动区域C2:C3,将鼠标指向该区域右下角的填充柄,用上述“双击填充大法”即可对全班学生顺序编号。然后每隔30人(一个标准考场)插入一空行,在D2和D3单元格中全部输入“001”,同样用“双击填充大法”迅速将第一考场的编号全部填充。此时B2:B31单元格已经全部自动填上了考生的准考证号。对第二考场,只需在D33和D34格中输入“002”,并向下填充即可。

2、打印考场记录单

考场记录单是供监考教师监考时核对考生数目和身份时用的。在上述“考场编号”工作表中,利用Excel的“分类汇总”功能,可以快速方便的达到这一目的。操作步骤如下:

(1)在表中单击任一数据单元格,在“数据”菜单中,单击“分类汇总”命令。

(2)在“分类字段”下拉列表框中,单击需要用来分类汇总的数据列(“考场编号”列)。

(3)在“汇总方式”下拉列表框中,选择“计数”。

(4)在“选定汇总项(可有多个)”框中,复选“考场编号”框。

(5)复选“每组数据分页”选项,以便每个考场单打印一页。其他两项“替换当前分类汇总”和“汇兑结果显示在数据下方”可根据情况选定。

(6)单击[确定],考场记录单就已经做好了。

(7)为了使每一页都打印同样的表头:“2001年期末考试第X考场考场单”,单击菜单“文件→页面设置→页眉/页脚→自定义页眉(C)……”,进行相关设置。为了更加美观,在“页面设置”的“页边距”窗口复选“水平居中”和“垂直居中”,并定义好纸张的大小。

说明:

(1)“分类汇总”前,必须按“考场编号”列排序,本例中已经是排好顺序的。

(2)对“分类汇总结”的结果不满意时,可以清除“分类汇总”,Excel将同时清除分级显示和插入“分类汇总”时产生的所有自动分页符。方法是:单击任一单元格,在“数据”菜单中,选择“分类汇总”命令,单击[全部删除]按钮。

3、打印准考证号码条

准考证号码条一般粘贴在课桌左上角,供考生寻找自己的座位用,过去主要靠手工抄写的方法完成。这里我用Word的“邮件合并”功能巧妙地完成了这一任务。方法如下:

启动Word 2000,执行“工具”菜单下的“邮件合并”命令,弹出“邮件合并帮助器”,首先在当前窗口创建一个邮件标签。依次点击“创建→邮件标签→活动窗口”,然后点击[获取数据]按钮,在下拉列表中选“打开数据源”,在文件类型列

表框中把文件类型改为“MS Excel工作簿”,打开刚才“考场编号”工作表,按[确定]按钮。接着设置主文档,点击[新建标签]按钮,弹出“新建自定义标签”对话框。

在“标签名称”文本框中填上“座位号”,在“页面尺寸”下拉列表框中选“A4横向”。按[确定]按钮,出现“创建标签”对话框。点击“插入合并域”,插入“准考证号”、“姓名”两个域,按[确定]按钮,最后按[合并]按钮。

好,现在的考前准备工作基本完成,只等考试成绩出来了!

20. Excel的图表功能

Excel的图表转换功能具有更大的吸引力。Excel能够根据工作表中的数据创建图表(即将行、列数据转换成有意义的图象)。图表能帮助辨认数据变化的趋势,而在工作表中就很难辨别。

我们在Excel下先简单地制作一个记录正弦函数y=sin(x-a)数据的工作表:

然后根据工作表中的部分数据制作正弦曲线y2。其步骤如下:

1.通过拖动鼠标选中x栏的数据。按住Ctrl键不放,拖动鼠标再选中y2栏的数据。注意,栏目标题不要选,因为它们不是数据。

2.选择插入| 图表菜单项,或者直接点击工具栏?quot;图表向导"按钮,调出图表类型窗口。在该窗口的标准类型页面,列出了柱形图、条形图、折线图等图表类型可供选择。这些类型大多适用于一维数据,对于二维数据表,如果想转换成折线图,不能直接选折线图,而应先选xy散点图为主类型,然后在子图表类型中选折线散点图或平滑线散点图。

3.按"下一步"按钮,进入图表源数据窗口。此时,Excel已根据你所选的数据将正弦曲线y2显示在窗口中。

4.按"下一步"按钮,进入图表选项窗口。在该窗口标题页,你可以给图表标题框输入:正弦函数y=sin(x-a),给数值(x)轴框输入:x(度),给数值(y)轴框输入:y。在图例页,你还可以选择是否显示图例,等等。

5.按"下一步"按钮,进入图表位置窗口。我们选择选项:⊙作为新工作表插入,这样,Excel会为你新建一个图表页。如果选择选项:⊙作为其中的对象插入,则Excel会将新建的图表插入在原工作表页面。

6.按"完成"按钮,Excel就会按照你的设置将所选数据转换成图表。我们看到,一个新建的正弦曲线y2显示在整个屏幕上,同时,在下方工作表标签栏,新增加了图表1标签。通过鼠标点击这些标签,可以与Sheet1、Sheet2、Sheet3等工作表进行页面切换。

假如,你还想把y1、y2、y3三条正弦曲线都建在一个图表上,则可以点击Sheet1标签,回到原始的工作表页面,从工作表中选择全部的数据单元格,再重复以上步骤,即可又创建一个新图表,同时工作表标签栏新增图表2标签。这时点击文件| 保存,则工作表及其图表将作为一个Excel文档存盘。图表也是工作表,一个Excel文档最多可包含255个工作表。

图表建好后,如对选择的设置不满意,还可以通过图表菜单的子菜单回到以上的任一步骤进行修改。通过格式菜单的子菜单,则可以设置图表区、绘图区、坐标轴的图案、字体、刻度。或者直接用鼠标右键单击图表的图表区、绘图区或坐标轴,调出快捷菜单来设置修改它们。我们将x轴刻度最大值由400改为360,将刻度单位值由50改为30,这样设置更为合适。如果不显示图例,则应当为三条正弦曲线加注标识y1、y2、y3(通过添加文本框)。

人们在科学实验中经常需要对大量的实验数据进行处理,Excel的图表功能可以帮助我们观察和分析客观世界变量的内在规律和函数关系,特别是通过Excel的图表| 添加趋势线功能菜单还可以帮助趋势预测和回归分析,为科学工作者的工作提供了极大的便利。

21. 批量修改数据

在EXCEL表格数据都已被填好的情况下,如何方便地对任一列(行)的数据进行修改呢?

比如我们做好一个EXCEL表格,填好了数据,现在想修改其中的一列(行),例如:想在A列原来的数据的基础上加8,有没有这样的公式?是不是非得手工的一个一个数据地住上加?对于这个问题我们自然想到了利用公式,当你利用工式输入A1=A1+8时,你会得到EXCEL的一个警告:“MICROSOFT EXCEL不能计算该公式……”只有我们自己想办法了,这里介绍一种简单的方法:

第一步:

在想要修改的列(假设为A列)的旁边,插入一个临时的新列(为B 列),并在B列的第一个单元格(B1)里输入8。

第二步:

把鼠标放在B1的或下角,待其变成十字形后住下拉直到所需的数据长度,此时B列所有的数据都为8。

第三步:

在B列上单击鼠标右键,“复制” B列。

第四步:

在A列单击鼠标的右键,在弹出的对话框中单击“选择性粘贴”,在弹出的对话框中选择“运算”中的你所需要的运算符,在此我们选择“加”,这是本方法的关键所在。

第五步:

将B列删除。

怎么样?A列中的每个数据是不是都加上了8呢?同样的办法可以实现对一列(行)的乘,除,减等其它的运算操作。原表格的格式也没有改变。

此时整个工作结束,使用熟练后,将花费不到十秒钟

22. 将Excel数据导入Access

如果想将Excel中的数据转换到Access中,可以采取下面的直接导入法和建立链接法来完成。

一、直接导入法

1.启动Access,新建一数据库文件。

2.在“表”选项中,执行“文件→获取外部数据→导入”命令,打开“导入”对话框。

3.按“文件类型”右侧的下拉按钮,选中“Microsoft Excel(.xls)”选项,再定位到需要转换的工作簿文件所在的文件夹,选中相应的工作簿,按下“导入”按钮,进入“导入数据表向导”对话框。

4.选中需要导入的工作表(如“工程数据”),多次按“下一步”按钮作进一步的设置后,按“完成”按钮。

excel 怎样从身份证号码提取年龄和性别

excel 怎样从身份证号码提取年龄和性别- [电脑应用技巧] 版权声明:转载时请以超链接形式标明文章原始出处和作者信息及本声明 https://www.wendangku.net/doc/48231819.html,/logs/50218662.html 因为自己需要,在网上找来了这个教程,函数真是好用的东西。这个教程很详细,不过我偷懒,因为自己觉得只需要看公式,所以用红字标记方便自己。。。。 在EXCEL中如何利用身份证号码计算出生年月、年龄及性别 在学校的人事管理中经常会遇到需要统计教职工的年龄的问题,但案头的原始资料只有身份证号码,其实这足够了。在EXCEL中,引用其内置函数利用身份证号码达到此目的比较简单。1、身份证号码简介(18位): 1~6位为地区代码;7~10位为出生年份;11~12位为出生月份;13~14位为出生日期;15~17位为顺序号,并能够判断性别,奇数为男,偶数为男;第18位为校验码。 2、确定“出生日期”: 18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄” 应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。 ①正确输入了身份证号码。(假设在D2单元格中) ②将光标定位在“出生日期”单元格(E2)中,然后在单元格中输入函数公式 “=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。 关于这个函数公式的具体说明:MID函数用于从数据中间提取字符,它的格式是:MID (text,starl_num,num_chars)。 Text是指要提取字符的文本或单元格地址(上列公式中的D2单元格)。 starl_num是指要提取的第一个字符的位置(上列公式中依次为7、11、13)。 num_chars指定要由MID所提取的字符个数(上述公式中,提取年份为4,月份和日期为2)。 多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起,“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记,这样的数据以后就可以作为日期类型进行年龄计算。确定“年龄”:

Excel表中身份证号码提取出生年月、年龄、性别的使用技巧[1]

Excel表中身份证号码提取出生年月、性 别、年龄的使用技巧 excle中当一个序列号变更,下面序列号自动变更的方法。 浏览次数:298次悬赏分:0 |解决时间:2011-3-11 12:48 |提问者:kasure 问题补充: 比如我编制了序列号001,002,003。。。。,然后我要是中间插入一行,比如在002和003之间插入一行,我下面的编号都要变动,如何实现这样的功能? 最佳答案 那我想知道如果你需要删除一行的话,下面的编号是否需要变动?如果都需要变动的话,你可以试试这样: 1、把序号列的单元格格式改成"000"(在设置单元格格式--自定义--类型那里可以改) 2、把序列号的单元格填上公式=row() 。如果表格上面有表头的话,你数数表头有多少行,在公式后面减去行数,例如有5行表头,公式就是=row()-5 当你插入行的时候把公式填上就可以了 方法一: 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。

18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符(从左向右)。 对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取: 假如身份证号数据在A1单元格,在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&M ID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1, 13,2)) 回车确认即可。 如果只要“年-月”格式,公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A 1,7,4)&"-"&MID(A1,11,2))

用Excel从身份证号码中提取信息(年龄、性别、出生地)

用Excel从身份证号码中提取信息 (年龄、性别、出生地) 出生年月日信息提取: 方法一:在记录列中输入公式:=--TEXT(MID(B2,7,6+IF(LEN(B2)=15,0,2)),"#-00-00"),往下复制,无论15位还是18位身份证号码全部搞定,方法最简单。 方法二、在记录列中输入公式:=--IF(LEN(B2)=15,TEXT(MID(B2,7,6),"##-00-00"),TEXT(MID(B2,7,8),"####-00-00")),往下复制,无论15位还是18位身份证号码全部搞定,公式增加了几个字符,原理差不多,结果一致。 原理:使用函数text、if、mid、len。 注意:1、B列存放身份证号码。存放在其它列,则在公式中作相应调整。 2、计算出错(#V ALUE!),说明身份证号码有错。 3、日期显示格式,可在单元格格式中设置。 性别信息提取: 在记录列中输入公式:=IF(LEN(B2)=15,IF(MOD(RIGHT(B2),2)=0,"女","男"),IF(MOD(LEFT(RIGHT(B2,2)),2)=0,"女","男"))无论15位还是18位身份证号码全部轻松完成。 原理:使用函数IF、LEN、MOD、LEFT、RIGHT。 注意:1、B列存放身份证号码。存放在其它列,则在公式中作相应调整。 2、计算出错(#V ALUE!),说明身份证号码有错。 出生地信息提取:

在记录列中输入公式:=LEFT(B2,6),往下复制,然后根据代码用VLOOKUP查询发证地或者是出生地信息。 Excel文件模板: 从身份证号码中提取信息使用的模板 : 使用Excel从身份证 号码提取信息.xls 点击该图标,打 开该EXCEL文件,另存为××文件,即可使用。 谢谢你的使用。 水晶六彩

excel中用身份证号码生成性别、出生日期、计算年龄

excel中用身份证号码生成性别、出生日期、计算年龄 从身份证号码中自动生成性别和生日 生成性别:(其中B2是身份证号码所在列) 一性别双击性别所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可 1=CHOOSE(MOD(IF(LEN(B2)=18,MID(B2,17,1),IF(LEN(B2)=15,RIGHT(B2,1),"")),2)+1,"女","男") 2=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女") 3=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女")) 二出生日期提取出生日期:(其中B2是身份证号码所在列) 双击出生日期所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可 =DA TE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2)) 三计算年龄:(其中C3是出生日期所在列) 双击年龄所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可 =YEAR(NOW())-YEAR(C3) Excel自动从身份证中提取生日性别 出处:天空软件作者:佚名日期:2009-09-16 每年新入学的一年级学生,都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格,以备建立全省统一的电子学籍档案。数百个新生,就得输入数百行相应数据,这可不是个轻松活儿。有没有什么办法能减轻一下输入工作量、提高一下效率呢?其实,我们只需在Excel2003中将学生的身份证号完整地输入后,它就可以帮我们自动填好出生日期和性别。 现在学生的身份证号已经全部都是18位的新一代身份证了,里面的数字都是有规律的。前6位数字是户籍所在地的代码,7-14位就是出生日期。第17位“2”代表的是性别,偶数为女性,奇数为男性。我们要做的就是把其中的部分数字想法“提取出来”。 STEp1,转换身份证号码格式 我们先将学生的身份证号完整地输入到Excel2003表格中,这时默认为“数字”格式(单元格内显示的是科学记数法的格式),需要更改一下数字格式。选中该列中的所有身份证号后,右击鼠标,选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”,然后点击确定。 STEP2,“提取出”出生日期 将光标指针放到“出生日期”列的单元格内,这里以C2单元格为例。然后输入

Excel中根据身份证号码自动提取信息

Excel中根据身份证号码自动提取信息 在excel表格中,根据身份证号码就可以自动提取出生年月、性别、年龄。 第一,身份证号码的组成 当今的身份证号码由18位数字组成:前6位为地址码,第7至14位为出生日期码,第15至17位为顺序码,第18位为校验码。 15~17位为顺序号,其中第17位如果是单数为男性分配码,双数为女性分配码。即通过第17位数字,可以判断性别,奇数为男,偶数为女。 下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。首先,在excel中输入以下表格,实例如下面所示。 A BCD E姓名身份证号码出生日期性别年龄张三5103211972050307861972年05月03日女37第二,根据身份证号码自动提取出生年月 在目标单元格输入公式:=MID(B2,7,4)&"年"&MID(B2,11,2)&"月 "&MID(B2,13,2)&"日",回车确定,单元格内会出现与身份证号码相关的出生日期。其它单元格的填充,不必一一输入公式,只需选中该单元格复制,在其它单元格内粘贴即可。 提示:MID函数是提供的一个“从字符串中提取部分字符”的函数命令。 第三,根据身份证号码自动提取性别的方法 在目标单元格输入公式:=IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女")提示:IF 函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。 RIGHT函数:根据所指定的字符数返回文本串中最后一个或多个字符。 第四,根据身份证号码自动提取年龄的方法 在目标单元格输入公式:

=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),"#-00-00"),NOW(),"y"),回车确定。

Excel从身份证号中提取出生年月和年龄

假设数据在A1单元格 B1单元格输入公式 =right(a1,len(a1)-2) (公式中的“2”是指栓出前面的位数)或 =substitute(a2,"SF",) 双击填充柄填充或下拉填充 然后复制B列,选择性粘贴为数值,替换到A列中 如何利用Excel从身份证号中提取出生年月和年龄等信息? 2007年12月16日 07:41 P.M.一、分析身份证号码 其实,身份证号码与一个人的性别、出生年月、籍贯等信息 是紧密相连的,无论是15位还是18位的身份证号码,其中都保 存了相关的个人信息。 15位身份证号码:第7、8位为出生年份(两位数),第9、 10位为出生月份,第11、12位代表出生日期,第15位代表性别, 奇数为男,偶数为女。 18位身份证号码:第7、8、9、10位为出生年份(四位数), 第11、第12位为出生月份,第13、14位代表出生日期,第17 位代表性别,奇数为男,偶数为女。 例如,某员工的身份证号码(15位)是320521*********,那 么表示1972年8月7日出生,性别为女。如果能想办法从这些身

份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。 二、提取个人信息 这里,我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。如图1所示,其中员工的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B列。 图1 输入身份证号码 1.提取出生年月信息 由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“7208”这样的信息。在D2单元格中输入公式 “=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中: LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。 MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第7、8、9、10位数字。 MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、10、11、12位数字。 IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的

如何使用Excel从身份证号码中提取出生日期

如何使用Excel从身份证号码中提取出生日期2009-02-27 22:52例如:从身份证420821************中提取出生日期来,如何快速得出? 呵呵,只需使用语句: =DATE(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2)) 【A1是身份证号码所在单元格】 date()函数,地球人都知道,日期函数;如输入今天的日期=today() 那么,mid函数是什么东东呢? MID(text,start_num,num_chars) Text 为包含要提取字符的文本字符串;Start_num 为文本 中要提取的第一个字符的位置。文本中第一个字符的start_num 为1 ,以此类推;Num_chars指定希望MID 从文本中返回字符的个数。对身份证号码分析下就知道:420821************,出生日期是1992年2月6日;也就是 从字符串(420821************)的第7位开始的4位数字表示年,从字符串的第11位开始的2位数字表示月,字符串的第13位开始的2位数字表示日。呵呵,强悍吧! Excel中利用身份证号码(15或18位)提取出生日期和性别

需要的函数: LEN(C6)=15:检查C6单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位; INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数。 RIGHT:返回文本字符串最后一个字符开始指定个数的字符;MID:返回文本字符串指定起始位置起指定长度的字符,MID(C6,7,2)表示:在C3中从左边第七位起提取2位数; "19"&MID(C6,7,2)表示:在C3中从左边第七位起提取2位数的前面添加19; …… &""&表示:其左右两边所提取出来的数字不用任何符号连接;&"-"&表示:其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年mm月dd日,则可以把公式中的“-”分别用“年月日”进行替换就行了。 一、提取出生日期 如果我们要从一个人的身份证号码中批量提取其出生年月日,并表示成“yyyy-mm-dd”形式,可以这样做,假设身份证号码在C列,在D

Excel自动从身份证中提取生日

Excel自动从身份证中提取生日、性别、年龄 18位的新一代身份证里面的数字都是有规律的:前6位数字是户籍所在地的代码,7-14位就是出生日期。第17位“2”代表的是性别,偶数为女性,奇数为男性。我们要做的就是把其中的部分数字想法“提取出来”。 STEP1,转换身份证号码格式 我们先将学生的身份证号完整地输入到Excel2003表格中,这时默认为“数字”格式(单元格内显示的是科学记数法的格式),需要更改一下数字格式。选中该列中的所有身份证号后,右击鼠标,选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”,然后点击确定。 STEP2,“提取出”出生日期 将光标指针放到“出生日期”列的单元格内,这里以C2单元格为例。然后输入“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”(注意:外侧的双引号不用输入,函数式中的引号和逗号等符号应在英文状态下输入)。回车后,你会发现在C2单元格内已经出现了该学生的出生日期。然后,选中该单元格后拖动填充柄,其它单元格内就会出现相应的出生日期。如图1。 图1 通过上述方法,系统自动获取了出生年月日信息 小提示:MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令,具体使用格式在EXCEL中输入MID后会出现提示。

STEP3,判断性别“男女” 选中“性别”列的单元格,如D2。输入“=IF(MID(B2,17,1)/2=TRUNC (MID(B2,17,1)/2),"女","男")”(注意如上)后回车,该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入。如图2。 图2 性别被自动填入指定位置 STEP4,计算年龄:(其中C2是出生日期所在列) No.1双击年龄所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可=YEAR(NOW())-YEAR(C2) 如图3所示:

在Excel中输入身份证号码及提取信息【会计实务经验之谈】

从业二十年的老会计经验之谈,如果觉得有帮助请您打赏支持,谢谢! 在Excel中输入身份证号码及提取信息【会计实务经验之谈】 一、解决输入身份证号码的问题 默认情况下在Excel当前单元格中输入的数字位数如果超过11位(不含11位)时,系统将以“科学记数”格式显示输入的数字;当输入的数字位数超过15位(不含 15位)时,系统将15位以后的数字全部显示为“0”。这样一来,如果我们要输入身份证号码(15位或18位),身份证号码就不能正确显示出来了。但我们有两种办法解决此问题: 1、利用数字标签 选中需要输入身份证号码的单元格(或区域),打开“单元格格式”对话框,进入“数字”标签,在“分类”下面选中“文本”选项,确定退出即可。 2、在输入身份证号码时,在号码前面加上一个英文单引号“´”,即可让身份证号码完全显示出来(该单引号不显示)。 二、让单元格内自动添加身份证前面相同部分 身份证号码的前6位,是省、市、区(县)的代号(如资中县:511025),如果持证人都是资中县的,输入这样的的身份证号码,可以让单元格格式的“数字”标签帮你减轻输入的工作量:选中需要输入身份证号码的单元格区域,单击Excel上面菜单栏的格式打开“单元格”对话框(或者右键点击设置单元格格式),进入默认的“数字”标签,在“分类”下面选中最下面的“自定义”选项,然后在右侧“类型”下面的方框中输入:”511025”@,确定。以后输入身份证号码9位及以后的数字(如“197303246211”)后,系统会自动在前面加上51102519,形成完整的身份证号码(如“511025************”)。 其它需要输入数字含相同部分的也可按照这个方法输入。 Excel从身份证号码中提取个人信息 如18位身份证号码:前6位是公民户口登记地,第7到14位代表出生日期,第17位代表性别,奇数为男,偶数为女。 (15位身份证号码15位数代表性别) 比如,某员工的身份证号码(18位)是320521************,那么表示1972年8月17日出生,性

Excel表格身份证号码提取出生日期的公式

Excel表格身份证号码提取出生日期的公式 (B2表示身份证号码所在的列位置) =MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)回车→向下填充1. Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2. 15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。 3. 18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。 一、提取出生年月: A、15位身份证号码: =MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2) 回车确认即可。 B、18位身份证号码: =MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&”日”回车确认即可。 二、提取性别: 18位身份证号码: =IF(MOD(MID(B2,17,1),2)=1,"男","女")回车确认即可。 *excel公式中=IF(MOD(MID(E4,17,1),2)=0,"女","男")是什么意思? IF是选择函数,当MOD(MID(E4,17,1),2)=0成立时,单元格显示“女”,否则显示“男”。 MOD是取模函数,即是一个求余函数,求MID(E4,17,1)除以2的余数。实质是判断MID(E4,17,1)的奇偶性。 MID从一个文本字符串的指定位置开始,截取指定数目的字符。MID(E4,17,1)是从E4单元格的文本中的第17个字符开始,取一个字符。 三、提取年龄: =year(today())-value(right(left(B2,10),4)) 回车确认即可。

EXCEL表中身份证号码年月日提取公式

编辑日期:2009年7月28日 EXCEL表中身份证号码信息提取公式1、从身份证号码提取“年”份 如:342622************提取1994, 输入公式: =IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4)),回车确定。 2、从身份证号码提取“月”份 如:342622************提取11, 输入公式: =IF(LEN(H2)=18,IF(MID(H2,11,1)="0",MID(H2,12,1),MID(H2,11,2)),IF(MID( H2,9,1)="0",MID(H2,10,1),MID(H2,9,2))),回车确定。 3、从身份证号码提取“日” 如:342622************提取8(不是08形式), 输入公式: =IF(LEN(H2)=18,IF(MID(H2,13,1)="0",MID(H2,14,1),MID(H2,13,2)),IF(MID( H2,11,1)="0",MID(H2,12,1),MID(H2,11,2))),回车确定。 4、从身份证号码提取“年月日” 如:342622************提取形如1994-11-08, 输入公式: =IF(LEN(H2)=15,1900+MID(H2,7,2)&"-"&MID(H2,9,2)&"-"&MID(H2,11,2),MID( H2,7,4)&"-"&MID(H2,11,2)&"-"&MID(H2,13,2)),回车确定。 5、从身份证号码提取“年龄” 如:342622************提取年龄为15岁(针对2009年), 输入公式: =YEAR(NOW())-IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4)),回车确定。 6、从身份证号码提取“年月日” 如:342622************提取形如19941108, 输入公式: =IF(LEN(H2)=15,19000000+MID(H2,7,6),MID(H2,7,8)),回车确定。 注:○1式中的H2为身份证号码所在单元格。 ○2文中的公式可输入也可复制到excel单元格中,并按回车确定。 ○3只要一个公式,向下拖拉即可完成整列运算。 ④公式计算列,一定确保为数值型格式,不能是文本格式,否则不能执行公式计算。这就是有时虽有正确的计算公式,但没有得到想要的计算结果,即出错。

在excel中怎么在身份证号码中提取性别

在excel中怎么在身份证号码中提取性别 15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。 18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。这里,我们需要使用IF、LEN、MOD、 MID、DATE 等函数从身份证号码中提取个人信息。其中员工的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B列。 1. 提取出生年月信息由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中: LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第7、8、9、10位数字。 MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、10、11、12位数字。IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取自第9位开始的四位数字。如果需要显示为“70年12月”这样的格式,请使用DATE格式,并在“单元格格式→日期”中进行设置。 2. 提取性别信息由于报表中各位员工的序号编排是按照上级核定的编制进行的,因此不可能按照男、女固定的顺序进行编排,如果一个一个手工输入的话,既麻烦又容易出错。例如性别信息统一在B列填写,可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")”,其中: LEN(C2)=15:检查身份证号码的长度是否是15位。 MID(C2,15,1):如果身份证号码的长度是15位,那么提取第15位的数字。 MID(C2,17,1):如果身份证号码的长度不是15位,即18位身份证号码,那么应该提取第17位的数字。MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2):用于得到给出数字除以指定数字后的余数,本例表示对提出来的数值除以2以后所得到的余数。IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女"):如果除以2以后的余数是1,那么B2单元格显示为“男”,否则显示为“女”。

Excel中利用身份证号码提取个人信息

Excel中利用身份证号码提取个人信息 步骤/方法1 办公室的小秦向我求教,说最近需要上报一份材料,这 份材料是用Excel做的汇总表,其中必须输入每位员工 的姓名、性别、身份证号码、出生年月、籍贯、毕业学 校、职称等相关信息,她的要求是有无简单的办法对身 份证号码、性别、出生年月的资料进行核对。下面我们 就来看一下具体的核对方法。2 一、分析身份证号码 其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份 证号码,其中都保存了相关的个人信息。15位身份证 号码:第7、8位为出生年份(两位数),第9、10位为 出生月份,第11、12位代表出生日期,第15位代表性 别,奇数为男,偶数为女。18位身份证号码:第7、8、 9、10位为出生年份(四位数),第11、第12位为出生 月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。例如,某员工的身份证号码(15 位)是320521*********,那么表示1972年8月7日出 生,性别为女。如果能想办法从这些身份证号码中将上 述个人信息提取出来,不仅快速简便,而且不容易出错,

核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。 二、提取个人信息 这里,我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。如图1所示,其中员工的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B列。

1.4 1. 提取出生年月信息 IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF 是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取自第9位开始的四位数字。如果需要显示为“70年12月”这样的格式,请使用DATE格式,并在“单元格格式→日期”中进行设置。 2. 提取性别信息 由于报表中各位员工的序号编排是按照上级核定的编制进行的,因此不可能按照男、女固定的顺序进行编排,如果一个一个手工输入的话,既麻烦又容易出错。例如性别信息统一在B列填写,可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C 2,17,1)),2)=1,"男","女")”,其中:LEN(C2)=15:检查身份证号码的长度是否是15位。MID(C2,15,1):如果身份证号码的长度是15位,那么提取第15位的数字。MID(C2,17,1):如果身份证号码的长度不是15位,即18位身份证号码,那么应该提取第17位的数字。MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)), 2):用于得到给出数字除以指定数字后的余数,本例表

Excel身份证号码提取个人信息

Excel身份证号码提取个人信息 2008-01-05 01:49 A.M. 您正在看的Excel教程是:Excel身份证号码提取个人信息。一、分析身份证号码 其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。 15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。 18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12 位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。 例如,某员工的身份证号码(15位)是320521*********,那么表示1972年8月7日出生,性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。 二、提取个人信息 这里,我们需要使用IF、LEN、MOD、 MID、DATE等函数从身份证号码中提取个人信息。如图1所示,其中员工的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B列。 1. 提取出生年月信息 由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中: LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。 MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第7、8、9、10位数字。 MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、10、11、12位数字。 IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提

EXCEL根据身份证号码自动提取年龄

EXCEL根据身份证号码自动提取年龄、性别、出生年月的公式(15位、18位通用) EXCEL根据身份证号码自动提取年龄、性别、出生年月的公式(15位、18位通用) 说明:以身份证号输入到B2单元格为例,如果身份证号输入到F4单元格,请把公式中的B2替换为F4。 1、提取年龄公式(15位、18位通用) 公式1:返回虚岁,生日前后返回同样的虚岁年龄 =IF(LEN(B2)=0,"",IF(LEN(B2)=15,year(now())-1900-VALUE(MI D(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))) 公式2:返回周岁,生日前后返回不同的周岁年龄 =IF(LEN(B2)=0,"",IF(LEN(B2)=15,DATEDIF(DATE(CONCATE NATE("19",MID(B2,7,2)),MID(B2,9,2),MID(B2,11,2)),TODAY(),"Y") ,IF(LEN(B2)=18,DATEDIF(DATE(MID(B2,7,4),MID(B2,11,2),MID(B 2,13,2)),TODAY(),"Y"),"身份证错"))) 函数说明:LEN返回文本字符串中的字符个数。 DATEDIF()计算两个日期之间的天数、月数或年数。 DATE返回代表特定日期的序列号。

CONCATENATE将多个文本字符串合并成一个文本字符串。 MID从文本字符串中指定的位置开始,返回指定长度的字符串。 TODAY返回日期格式的当前日期。 2、提取性别的公式(15位、18位通用) =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),IF(LEN(B2)=0,"","身份证错"))) 函数说明:LEN返回文本字符串中的字符个数。 MOD返回两数相除的余数。结果的正负号与除数相同。 VALUE将代表数字的文本字符串转换成数字。 3、提取出生年月公式(15位、18位通用) =IF(LEN(B2)=0,"",IF(LEN(B2)=15,CONCATENATE("19",MID(B 2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7, 4),".",MID(B2,11,2)),"身份证错"))) 函数说明:LEN返回文本字符串中的字符个数。 CONCATENATE将多个文本字符串合并成一个文本字符串。 MID从文本字符串中指定的位置开始,返回指定长度的字符串。

Excel身份证号提取生日性别年龄

方法 I. Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15 位身份证号从第7位到第1 2位是出生年月日,年份用的是2位数。 18位身份证号从第7 位到第14位是出生的年月日,年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以 达到目的。 MID()――从指定位置开始提取指定个数的字符(从左向右)。 对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN(辅助使用可以完成。综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取: 假如身份证号数据在A1 单元格,在B1 单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"- "&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)) 回车确认即可。 如果只要“年-月”格式,公式可以修改为=IF(LEN(A1)=15,MID(A1,7,2)&"- "&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2)) 3.这是根据身份证号码( 15位和18位通用)自动提取性别的自编公式,供需要的朋友参考:说明:公式中的B2是身份证号根据身份证号码求性别: =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0";"男 "),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=女",”男"),"身份证错"))根据身份证号码求年龄: =IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007- VALUE(MID(B2,7,4))身份证错")) 4.Excel表中用Year\Month'Day函数取相应的年月日数据;

excel身份证中提取信息大全

如何从身份证号中提取出生年月 默认分类2008-11-17 10:26:39 阅读1174 评论0 字号:大中小订阅 引用:如何从身份证号中提取出生年月 其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18 位的身份证号码,其中都保存了相关的个人信息。 15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出 生日期,第15位代表性别,奇数为男,偶数为女。 18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。 例如,某员工的身份证号码(15位)是320521*********,那么表示1972年8月7日出生,性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。 二、提取个人信息 这里,我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。如图1所示,其中员工的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B列。 图1 输入身份证号码 1.提取出生年月信息 由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“7208”这样的信息。在D2单元格中输入公式 “=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中: LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是 15位。 MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证 号码的第7、8、9、10位数字。 MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证 号码的第9、10、11、12位数字。 IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示如果C2单元格是15 位,则提取第7位开始的四位数字,如果不是15位则提取自第9位开始的四位数字。 如果需要显示为“70年12月”这样的格式,请使用DATE格式,并在“单元格格式→日期”中进行设 置。 2.提取性别信息

excel身份证号码提取

更多课程传送门:点这里 excel中一些常用公式 文章来源:文章作者:发布时间:2009-08-28 一、年龄计算公式: =IF((YEAR(E2)=2008)*(MONTH(E2)>8),0,2008-YEAR(E2) IF(MONTH(E2)<=8,0,-1)) 其中e2为单元格,2008为当前年份 二、身份证号中提取出生年月= MID(J11, 7, 4) & "年" & MID(J11, 11, 2) & "月" & MID(J1 1, 13, 2)&"日" 三、班级平均分公式=IF(COUNTIF($C$2:$C$24,J26)=0,"",SUMIF($C$2:$C$24,J26,$D$2)/COUNT IF($C$2:$C$24,J26)) 四、Excel表中身份证号码提取出生年月、性别、年龄的使用技巧 如何从Excel表中的身份证号码中提取出生年月、性别、年龄呢?看了本文就知道了。 方法一: 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。 18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符(从左向右)。 对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()

辅助使用可以完成。综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取: 假如身份证号数据在A1单元格,在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1, 11,2)&"-"&MID(A1,13,2)) 回车确认即可。 如果只要“年-月”格式,公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2)) 3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式,供需要的朋友参考:说明:公式中的B2是身份证号 根据身份证号码求性别: =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALU E(MID(B2,15,1)),2)=0,"女","男"),"身份证错")) 根据身份证号码求年龄: =IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),"身份证错")) 4.Excel表中用Year\Month\Day函数取相应的年月日数据; 方法二: 这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考: 说明:公式中的B2是身份证号 1、根据身份证号码求性别: =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALU E(MID(B2,15,3)),2)=0,"女","男"),"身份证错")) 2、根据身份证号码求出生年月:

如何在EXCEL中提取身份证号码里的出生年月日

如何在EXCEL中提取身份证号码里的出生年月日、性别等,相关信息 在学校的人事管理中经常会遇到需要统计教职工的年龄的问题,但案头的原始资料只有身份证号码,其实这足够了。在EXCEL中,引用其内置函数利用身份证号码达到此目的比较简单。 1、身份证号码简介(18位): 1~6位为地区代码;7~10位为出生年份;11~12位为出生月份;13~14位为出生日期;15~17位为顺序号,并能够判断性别,奇数为男,偶数为男;第18位为校验码。 2、确定“出生日期”: 18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。 ①正确输入了身份证号码。(假设在D2单元格中) ②将光标定位在“出生日期”单元格(E2)中,然后在单元格中输入函数公式“=MI D(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。 关于这个函数公式的具体说明:MID函数用于从数据中间提取字符,它的格式是:MID(text,starl_num,num_chars)。 Text是指要提取字符的文本或单元格地址(上列公式中的D2单元格)。 starl_num是指要提取的第一个字符的位置(上列公式中依次为7、11、13)。

num_chars指定要由MID所提取的字符个数(上述公式中,提取年份为4,月份和日期为2)。 多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起,“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记,这样的数据以后就可以作为日期类型进行年龄计算。操作效果如下图: 3、确定“年龄”: “出生日期”确定后,年龄则可以利用一个简单的函数公式计算出来了:将光标定位在“年龄”单元格中,然后在单元格中输入函数公式“=INT((TODAY()-E2)/3 65)”即可计算出“年龄”。 关于这个函数公式的具体说明: ①TODAY函数用于计算当前系统日期。只要计算机的系统日期准确,就能立即计算出当前的日期,它无需参数。操作格式是TODAY()。 ②用TODAY()-E2,也就是用当前日期减去出生日期,就可以计算出这个人的出生天数。 ③再除以“365”减得到这个人的年龄。 ④计算以后可能有多位小数,可以用【减少小数位数】按钮,将年龄的数值变成“整数”,也可在公式= (TODAY()-E2)/365中再嵌套一个“INT”函数取整数,

相关文档
相关文档 最新文档