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

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

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

,"

男"

）,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”，拖动自动填充句柄到没有学生名单为止。

　　2.导出批处理

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

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

　　3.运行批处理

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

图3

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

图3所示就是把c:

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

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

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

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

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

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

　　方法一、直接分列法

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

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

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

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将显示“李小明”。

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

"

&

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

=B1&

"

A1

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

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

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