使用EXCEL提取号中的生日

自动输入出生日期

（1）函数分解

CONCATENATE函数将多个文本字符串组合为一个文本字符串.

语法: CONCATENATE（text1，text2，...）

Text1，text2，...是1到30个文本项，可以组合为一个文本项. 文本项可以是文本字符串，数字或对单个单元格的引用.

（2）案例分析

与上述相同，我们可以输入公式“ = IF（LEN（C2）= 15，CONCATENATE（” 19“，MID（C2,7,2），” year“，MID（C2,9， 2），“月”，MID（C2,11,2），“日”），CONCCTENCTE（MID（C2,7,4），“年”，MID（C2,11,2），“”月“， MID（C2,13,2），“ Day”））“”其中，“ LEN（C2）= 15”仍用作逻辑判断语句，它可以判断ID号是15位还是18位，因此来调用相应的计算语句.

对于15位数的ID号，从左到右的第7至12个字符表示出生的年，月和日. 此时，您可以使用MID功能从ID编号的特定位置提取出生的年份，月份和月份. 天. 然后使用CONCATENATE函数合并提取的文本以获得相应的出生日期. 公式中的“ 19”旨在解决早期ID编号中的2000年问题. 可以在计算出的出生年份之前添加“ 19”. “ 18” ID卡号的计算方法相同，只是它没有2000问题. 该公式不需要在计算的出生年份之前加上“ 19”.

注意: CONCATENATE函数和MID函数的操作对象都是文本，因此存储ID号的单元格必须预先设置为文本格式，然后输入ID号.

自动计算服务时间

1. 首先将Word中的所有客户文件复制到Excel以生成文件. 然后在文件中创建一个新列，并将其命名为“ Birth Year”. 因为ID号有15位和18位数字，所以为了便于计算，ID号先进行排序.

2. 使用MID函数计算第一个客户的生日. 该函数表示如下: MID（E2，7，2），表示第一个客户的ID号在E列的第二行中，并且此位置的第7个文本中将返回2个长度的字符

在确认回车后，“ J2”中的值变为“ 62”，表示该雇员已出生62岁. 然后拉下J2公式以复制该单元格，快速查找每位雇员的出生日期（如果ID为18位数字，则该公式变为MID（E2，9，2）），然后将J列的格式更改为数字.

3. 在D2中输入计算机公式“ = 103-J2”以找到员工的实际年龄. 再次复制下拉菜单D2中的公式，只需单击一下即可确定将近一千名员工的年龄.

注意: 以上方法的出生年份和月份仅在2000年之前有效. 如果客户中有2000年之后出生的人，则需要进行一些计算.

补充

所谓的文本函数是可以处理公式中文本字符串的函数. 例如，您可以更改大小写或确定文本字符串的长度. 您可以替换某些字符或删除某些字符. 日期和时间功能可以在公式中分析和处理日期和时间值. 有关这两种功能的列表，请参阅下表，此处仅简要介绍一些常用功能.

一个，文本功能

（1）案例转换

小写-将文本字符串中的所有大写字母转换为小写字母.

UPPER-将文本转换为大写.

正确-将文本字符串的首字母和任何非字母字符之后的首字母转换为大写. 将其余字母转换为小写.

这三个函数的基本语法是函数名（文本）. 示例说明:

现有的字符串是: pLease ComE Here！您会发现此句子由于输入不规则而被滥用.

可以通过以上三个功能将文本转换为显示样式，使文本成为标准样式. 参见图1

降低（pLeCome Here！）=请过来！

上（请在这里请来！）=请在这里来！

正确的（pLease ComE Here！）=请过来！

图1

（2）删除字符串中的一些字符

Excel功能应用程序审查

●Excel函数应用程序的逻辑函数

●Excel函数的数学和三角函数

●Excel函数的功能介绍

您可以使用Mid，Left，Right和其他功能从长字符串中获取一些字符. 具体的语法格式为

LEFT函数: LEFT（文本，num_chars），其中Text是包含要提取的字符的文本字符串. Num_chars指定要由LEFT提取的字符数.

MID函数: MID（文本，s​​tart_num，num_chars），其中Text是包含要提取的字符的文本字符串. Start_num是要在文本中提取的第一个字符的位置.

RIGHT函数: RIGHT（text，num_chars），其中Text是包含要提取的字符的文本字符串. Num_chars指定要向RIGHT提取的字符数.

例如，从字符串“ This is a apple”中获取字符“ This”，“ apple”和“ is”. 具体功能写为.

LEFT（“这是一个苹果”，4）=这个

RIGHT（“这是一个苹果”，5）= apple

MID（“这是一个苹果”，6,2）=是

图2

（3）删除字符串的空白

以字符串的形式，空格也是有效字符，但是如果字符串中包含白色字符，则在判断或比较数据时很容易出错. 在Excel中，可以使用Trim函数清除字符串中的空白.

语法为: TRIM（text），其中Text是需要清除空格的文本.

应注意，“修剪”功能不会清除单词之间的单个空格. 如果甚至需要清理这部分空间，建议使用替换功能. 例如，从字符串“我的名字是玛丽”清除空格的功能是: TRIM（“我的名字是玛丽”）=我的名字是Mary参见图3

图3

（4）字符串比较

经常在数据表中比较不同的字符串. 这时，您可以使用EXACT函数比较两个字符串是否相同. 此函数测试两个字符串是否相同. 如果它们相同，则返回TRUE. 否则，返回FALSE. 函数EXACT区分大小写，但忽略格式差异. 使用功能EXACT测试输入文档中的文本. 语法为: EXACT（text1，text2）Text1是要比较的第一个字符串. Text2是要比较的第二个字符串. 例如: 参见图4

EXACT（“ China”，“ china”）=假

图4

两个日期和时间函数

在处理数据表期间，日期和时间的功能是处理的非常重要的基础. Excel在这方面还提供了非常丰富的功能供所有人使用.

（1）提取当前系统时间/日期信息

用于检索当前系统时间/日期信息的功能主要包括NOW和TODAY.

语法是所有函数名（）.

（2）获取日期/时间的某些字段值

如果需要单独的年，月，日或小时数据，则可以使用HOUR，DAY，MONTH，YEAR函数直接从日期/时间中提取所需的数据. 有关具体示例，请参见图5.

例如，您需要返回2001-5-30 12:30 PM的年，月，日和小时，则可以使用相应的函数来实现.

YEAR（E5）= 2001

MONTH（E5）= 5

DAY（E5）= 30

HOUR（E5）= 12

图5

此外，还有更多有用的日期/时间功能，您可以检查随附的表. 下面我们将使用一个特定的示例来说明Excel的文本和日期函数的用途.

3. 例子: 制作一个简洁明了的人员数据分析表

1. 示例说明

在如图6所示的公司人员信息表中，除了人数，员工姓名，号码和工作时间（这些都是手动添加的）之外，其余各项都是使用函数计算的.

图6

在此示例中，我们将详细说明如何通过函数查找它:

（1）自动从ID号中提取出生日期和性别信息.

（2）自动从工作时间中提取资历信息.

2. 有关ID号的知识

在了解如何自动从ID号中提取出生日期，性别和性别信息之前，您首先需要了解ID号的含义. 我们知道今天的ID号有15/18位数字. 早期发行的ID卡号为15位数字，由于年份的扩展（从两位数字到四位数）和验证码的末尾，现在发行的ID卡已变为18位数字. 这两个ID号将长期存在. 这两个ID号的含义如下:

（1）15位ID编号: 1〜6位数字是区号，7〜8位数字是出生年份（2位数字），9〜10位数字是出生月份，11〜12位数字是出生日期前13到15位数字是序列号，可以判断性别，奇数是男性，偶数是女性.

（2）18位ID号: 区号为1〜6位，出生年份（4位）为7〜10位，出生月份为11〜12位，数字为13〜14位. 出生日期的前15到17位数字是序列号，可以判断性别，奇数是男性，偶数是女性. 校验码为18位.

3. 应用功能

在此示例中，为了实现自动数据提取，应用了以下Excel函数.

（1）IF函数: 根据逻辑表达式测试的结果，返回相应的值. IF函数允许嵌套.

语法为: IF（逻辑测试，value_if_true，value_if_false）

（2）合并: 将多个文本项合并为一个文本项.

语法为: CONCATENATE（text1，text2 ......）

（3）MID: 从文本字符串中指定的起始位置开始，返回指定长度的字符.

语法为: MID（text，start_num，num_chars）

（4）TODAY: 返回计算机系统内部的当前日期.

语法为: TODAY（）

（5）日期: 计算两个日期之间的天数，月数或年数.

语法为: DATEDIF（开始日期，结束日期，单位）

（6）值: 将代表数字的文本字符串转换为数字.

语法为: VALUE（文本）

（7）右: 根据指定的字符数返回文本字符串中的最后一个字符.

语法为: RIGHT（text，num_chars）

（8）INT: 将实数舍入后返回整数值. 语法为: INT（数字）

4. 公式编写和解释（以员工Andy为例）

注意: 为避免在公式中过多嵌套，此处的ID号限制为15位数字. 如果您理解该公式，则可以进行简单的修改以应用于18位ID号，甚至可以应用于15、18共存.

（1）根据号寻找性别

= IF（VALUE（RIGHT（E4,3））/ 2 = INT（VALUE（RIGHT（E4,3））/ 2），“女”，“男”）

公式说明: RIGHT（E4,3）用于在ID号中找到代表性别的数字，实际获得的是代表数字的字符串

b. VALUE（RIGHT（E4,3）用于将表示在上一步中获得的数字的字符串转换为数字

c. VALUE（RIGHT（E4,3））/ 2 = INT（VALUE（RIGHT（E4,3））/ 2用于判断ID号是奇数还是偶数，当然，您也可以使用Mod函数判断.

d. = IF（VALUE（RIGHT（E4,3））/ 2 = INT（VALUE（RIGHT（E4,3））/ 2），“ Female”，“ Male”）并且如果上述公式判断该数字是否为偶数，显示“女性”；否则，如果数字为奇数，则返回“ Male”.

（2）根据ID号找到出生日期

= CONCATENATE（“ 19”，MID（E4,7,2），“ /”，MID（E4,9,2），“ /”，MID（E4,11,2））

公式说明: MID（E4,7,2）是一个字符串，用于获取表示ID号中年份的数字

b. MID（E4,9,2）是一个字符串，它获取代表ID号中月份的数字

c. MID（E4,11,2）是一个字符串，用于获取表示ID号中日期的数字

d. CONCATENATE（“ 19”，MID（E4,7,2），“ /”，MID（E4,9,2），“ /”，MID（E4,11,2））目的是将字符串一起显示.

（3）根据工作时间（即资历）寻求资历

= CONCATENATE（DATEDIF（F4，TODAY（），“ y”），“年份”，DATEDIF（F4，TODAY（），“ ym”），“月份”）

公式说明:

a. TODAY（）用于查找系统的当前时间

b. DATEDIF（F4，TODAY（），“ y”）用于计算当前系统时间与工作时间之间的时差

c. DATEDIF（F4，TODAY（），“ ym”）用于计算当前系统时间与工作时间之间的时差，而忽略日期的日期和年份.

d. = CONCATENATE（DATEDIF（F4，TODAY（），“ y”），“ Year”，DATEDIF（F4，TODAY（），“ ym”），“ Month”）目的是将多个字符串一起显示.

本文来自电脑杂谈，转载请注明本文网址：
https://www.pc-fly.com/a/jisuanjixue/article-247409-1.html