身份证号码在Excel中的妙用.docx

身份证号码在Excel中的妙用.docx

《身份证号码在Excel中的妙用.docx》由会员分享，可在线阅读，更多相关《身份证号码在Excel中的妙用.docx（10页珍藏版）》请在冰豆网上搜索。

身份证号码在Excel中的妙用

身份证号码在Excel中的妙用

妙用Excel快速恢复误录入的身份证号码两招

　学校每年年初都要对在职教工情况向人事部门报送材料，用Excel将教工情况逐一录入，可身份证号这一栏录入的老师并没有将先列的单元格格式设置为文本，导致身份证号在录入后被系统自动转换了格式，与实际报送要求不符（正确、快速地录入身份证号的方法许多资料曾多次介绍过，这里不再赘述）。

　　录入的老师向我求助，从图一中可以看出，被选中的身份证号在编辑栏中被完整地显示出来了（如图1），如果一个一个地复制再粘贴出来，费时费力。

后来笔者尝试用两种简单的方法很顺利地恢复了误录入的身份证号。

图1

　　1.用TEXT函数

　　在“身份证号”列后插入一个空白列，单击F2单元格，输入公式“=TEXT（E2,"000000000000000"）”，公式中将E2单元格的内容用指定的格式（15位“0”）替换，按回车键后，即得到了正确的身份证号。

用填充柄工具将公式复制到其他单元格中（如图2）。

图2

　　操作完成后，不能直接删除原身份证号所在的E列，否则F列数据将不能正常显示，若要对表格进行打印操作，可将E列隐藏或将F列数据选定，单击“复制”按钮，再右击鼠标选择“选择性粘贴”命令，从弹出的“选择性粘贴”对话框中选择“数值”即可，然后就可以安全地删除原身份证号所在的E列数据了。

根据身份证号码求性别：

=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,"女","男"）,"身份证错"））

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

=IF（LEN（B2）=15,CONCATENATE（"19",MID（B2,7,2）,"-",MID（B2,9,2）,"-",MID（B2,11,2））,IF（LEN（B2）=18,CONCATENATE（MID（B2,7,4）,"-",MID（B2,11,2）,"-",MID（B2,13,2））,"身份证错"））

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

=IF（LEN（B2）=15,year（now（））-1900-VALUE（MID（B2,7,2））,if（LEN（B2）=18,year（now（））-VALUE（MID（B2,7,4））,"身份证错"））

所属省份地区，需要有编号及省份地区对照表才能解决

实用技巧：

巧用Excel批处理实现自动化操作

信息技术课教学过程中经常要做一些重复的工作，如建立一个班级的学生文件夹，把作业分发到学生文件夹中等等。

《中国电脑教育报》2004第5期F6版《批量自动生成文件夹》一文构思巧妙，它通过VBScript来实现文件夹的批量建立。

但需要IISWeb服务器的支持，且动态网页Createfolder.asp内的源代码有“望而生畏”之感。

如果采用Excel来操作，简单、方便、快捷，且不需要特殊的操作平台，一起来看看吧。

　　1.建立工作表

　　如图1，在Excel中建立学生名册和批处理工作表，在学生名册工作表A、B两列中分别输入学号、姓名。

在批处理工作表A1单元格中输入“md”，A2单元输入“=学生名册!

A1&&学生名册!

B1”，拖动自动填充句柄到没有学生名单为止。

图1

　　2.导出批处理

　　在新建的Excel工作簿中，选择批处理工作表，单击“另存为”，在对话框的“保存类型”中选择“文本文件（制表符分隔）”，文件名任取（这里取Createdir.txt）。

单击[确定]后，由于存为TXT文件后可能含有不兼容的功能，会出现警告窗口，按[确定]直到生成TXT文件。

图2

　　3.运行批处理

把Createdir.txt（如图2）改名成Createdir.bat，运行它就可以建立相应的文件夹。

图3

　　在实际操作中，我们可以把批处理命令中可变的量（如要建立的学生文件夹、要复制文件的目标学生文件夹）、不变的量（如上述中的命令md、命令参数）在Excel中各设为单独的一列，利用Excel的自动填充功能来实现重复操作，从而实现作业分发、上交等功能。

图3所示就是把c:

\test下的作业（包含子文件夹和空子文件夹）复制到各考生文件夹。

自动识别中英文将Excel数据巧分列妙招三则

一天朋友向我请教一个问题：

有一个如图1所示的工作表，如何将其中B列的英文和中文分开成两列？

图1

　　我给他提供了下面三种方法，供其选择使用。

文章末尾提供.xls文件供大家下载参考。

　　方法一、直接分列法

　　1、启动Excel2003（其他版本请仿照操作），打开相应的工作表，在B列右侧插入一个空白列，用于保存分列的结果。

　　2、选中B列，执行“数据→分列”命令，打开“文本分列向导-3步骤之1”对话框（如图2），选中其中的“分隔符号-……”选项（通常是默认选项）。

图2

　　3、按“下一步”按钮，进入“文本分列向导-3步骤之2”对话框（如图3），

图3

　　选中“其他”选项，并在后面的方框中输入间隔“-”号（因为B列数据中都有一个间隔“-”号，参见图1），单击“完成”按钮，分列完成（参见图4）。

图4

　　注意：

大家可以通过图3中的“数据预览”窗口查看分列后的效果。

提高效率—Excel中验证数据的惟一性

在Excel中录入数据时，我们常常需要保证某些数据的惟一性，这些数据不能重复，如公司代码、商品编号、公司员工编号以及身份证号码等等，在录入这些资料时，我们可以设置数据的有效性验证来确保这些数据的惟一性，这样即保证了数据的正确性，同时也提高了数据的录入效率。

　　下面以录入员工身份证号码为例介绍一下操作的具体步骤。

　　设置有效性条件验证

　　假设G列为员工“身份证号”字段，G2单元格为第一个员工的身份证号码所在的单元格。

在未输入之前，我们可先设置该列的有效性条件来确保该列数据的惟一性。

　　选中G2单元格，单击“数据”菜单中的“有效性”命令，弹出“数据有效性”对话框，选择“设置”选项卡，在“允许”下拉列表中选择“自定义”，在“公式”框内输入“=COUNTIF（G:

G,G2）=1”（公式内所有的字符使用半角英文，不包括双引号，如图1所示）。

图1输入公式

　　设置出错警告提示信息

　　设置出错警告提示信息的目的在于提醒用户正确输入数据。

具体步骤是：

单击“数据有效性”对话框中的“出错警告”选项卡，在“标题”框内输入“数据输入错误”，在“错误信息”框内输入“你刚才输入的数据已经存在，请检查数据的惟一性！

”。

设置完之后，单击“确定”按钮（如图2所示）。

图2设置提示信息

　　至此，已经设置了G2单元格的有效性条件验证和出错提示信息。

为了将这个设置应用到整个G列（除了字段名称所在的单元格即G1单元格），可用填充柄工具向下拖动将公式复制到G列其他的单元格。

实用技巧：

Excel中实现数据最快速录入

新学期开始，学校要对上学期在德、智、体各方面表现出色的学生进行奖励，教务主任把各班的学生推荐表交给了我，让我用Excel整理并打印出来交学校审核。

我心想这还不是件简单的事情？

一个一个输入到电脑里，再排序、排版不就行了。

说起来简单，可干起来就快不了了。

表格项目很多，光录入一张表格的数据就得用几分钟，可是有一百多份表格呢！

我遇事喜欢琢磨，总想看看有没有可以“偷懒”的地方，只有巧干才能提高工作效率嘛！

　　对照表格我发现，很多表格项目如“性别”、“所属年级”、“是否团员”等项目，其填写的内容都是相同的几个值，能不能像上网时填写网页表单那样，将这些内容做成下拉式菜单，这样这些项目录入时就可以不需输入而可以直接选择，不就能大大提高录入速度了吗？

下面就以录入“所属年级”这一表格项目为例，介绍一下具体实现的过程。

　　1.设置序列的有效值

　　这里所指的“序列”即某字段（如“所属年级”）内可能会填写的内容，这些内容是固定的，一般个数不会太多。

首先选定“所属年级”字段所在的列（除了字段名称），单击“数据”菜单中的“有效性”命令，弹出“数据有效性”对话框，选中“设置”选项卡，在“允许”下拉菜单中选择“序列”，在数据“来源”中输入“初一,初二,初三,高一,高二,高三”（一定要在英文状态下输入逗号作为分隔符！

），其他设置按系统默认不变（如图1）。

图1设置序列的有效值

　　2.设置输入提示信息

　　输入提示信息可以使录入过程更加清晰明了。

具体制作步骤是：

在图1中单击“输入信息”选项卡，选中“选定单元格时显示输入信息”复选框，在“输入信息”框内输入“请选择该生所属年级！

”（如图2）。

设置完成后，单击[确定]按钮。

图2设置输入提示信息

　　3.数据的录入

　　在以上设置完成之后就可以对所有学生的年级进行选择录入了，若要录入某学生的“所属年级”，只需单击单元格右侧的下拉菜单（如图3），选择该生所属的年级就可以了。

图3数据的录入

　　其他许多表格项目的录入也可以进行类似的设置，以提高录入效率。

排序并不只针对数字，对于文本也可以，你的问题关键是要把X村、X社分列出来，作为两个不同的关键字进行排序即可，做法如下：

1.先另起两列，比如，D、E列，分别存储“X村”、“x社”，并假设你的文字存于A列：

在D列中输入函数=LEFT（A1,SEARCH（"村",A1,1）），在E列中输入函数=MID（A1,SEARCH（"村",A1,1）+1,SEARCH（"社",A1,1）-SEARCH（"村",A1,1））

2.以D、E两列为关键词进行排序，即可得到你要的的结果。

对于所使用函数的介绍如下：

1.Left函数

函数：

LEFT

说明：

LEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符。

语法：

LEFT（text,num_chars）

参数说明：

Text是包含要提取字符的文本字符串。

Num_chars指定要由LEFT所提取的字符数。

Num_chars必须大于或等于0。

如果num_chars大于文本长度，则LEFT返回所有文本。

如果省略num_chars，则假定其为1。

2.MID函数

函数：

MID

说明：

返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

语法：

MID（text,start_num,num_chars）

参数说明：

Text是包含要提取字符的文本字符串。

Start_num是文本中要提取的第一个字符的位置。

文本中第一个字符的start_num为1，以此类推。

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

3.Search函数

函数：

SEARCH

说明：

SEARCH返回从start_num开始首次找到特定字符或文本字符串的位置上特定字符的编号。

使用SEARCH可确定字符或文本字符串在其他文本字符串中的位置

这样就可使用MID或REPLACE函数更改文本。

语法：

SEARCH（find_text,within_text,start_num）

参数说明：

Find_text是要查找的文本。

可以在find_text中使用通配符，包括问号（?

）和星号（*）。

问号可匹配任意的单个字符，星号可匹

配任意一串字符。

如果要查找真正的问号或星号，请在该字符前键入波形符（~）。

Within_text是要在其中查找find_text的文本。

Start_num是within_text中开始查找的字符的编号。

如何合并两列数据

如果数据分别在两个单元格中，而您希望将它们合并到一个单元格内，就可以使用一个带"&"操作符的公式。

例如，A列是姓氏列表，B列是名字列表，那么可以在储存格C1中输入以下公式：

=A1&B1

   如果A1中是“李”，而B1中是“小明”，则C1将显示“李小明”。

若要在姓和名中间添加一个空格，可以使用以下公式，并在引号之间键入一个空格：

=A1&""&B1

   如果您希望C1显示为“李,小明”，以便能按姓氏对合并数据进行排序，可以使用以下公式，并在引号之间输入一个半角逗号和一个空格：

=B1&","&A1

   若要合并列表中的所有姓名，请将该公式拷贝到C列的其它单元格。

然后以文本格式替换公式格式存储该合并姓名：

单击C列，单击“编辑”菜单上的“拷贝”，单击“编辑”菜单上的“选择性粘贴”，然后单击“数值”。