mid函数公式,mid函数公式用法 收藏 0

EXCEL一组和身份证有关的函数公式，拿走不谢

作者：祝洪忠 转自：Excel之家ExcelHome

1、查找重复身份证号

=IF(COUNTIF(A:A,A2&\”*\”)>1,\”重复\”,\”\”)

COUNTIF函数在计算文本型数字时，会默认按数值型进行处理，但是Excel中的数字精度只有15位，并且身份证号码是18位，因此会把前15位相同的身份证全部识别为相同的内容。

在第二参数后加上一个星号 &\”*\”，就是告诉Excel，要查找包含A2单元格内容的文本，通过这样变通的手段，COUTNIF函数就听话啦。

2、计算出生年月

=–TEXT(MID(A2,7,8),\”0-00-00\”)

身份证号码中的第8位到15位是出生年月信息。

首先使用MID函数从A2单元格的第7位开始，提取出8个字符，得到类似 19840727的结果。

再使用TEXT函数将这个字符串变成日期样式的文本，得到类似1984/07/27的结果。

由于TEXT函数的结果是文本型的内容，所以加上两个小减号 — 就是计算负数的负数，通过四则运算，变成真正的日期序列。

如果计算后显示为5位数字，别忘了将单元格格式设置为日期。

3、提取性别

=IF(MOD(MID(A2,15,3),2),\”男\”,\”女\”)

身份证号码中的第17位是性别信息，单数表示男，双数表示女。

首先使用MID函数从A2单元格的第15位开始，提取出3个字符。再使用MOD函数计算这个字符与2相除的余数。

最后使用IF函数判断，如果余数大于0，返回男，如果余数是0，则返回女。

4、计算退休日期

=EDATE(TEXT(MID(A2,7,8),\”0-00-00\”),MOD(MID(A2,15,3),2)*120+600)

先用TEXT(MID(A2,7,8),\”0-00-00\”)，计算出出生年月信息。

再使用MOD(MID(A2,15,3),2)计算性别码和2相除的余数，结果是1或是0。

如果性别码和2相除的余数是1，MOD(MID(A2,15,3),2)*120+600部分就相当于1*120+600，结果是720。

否则就是0*120+600，结果是600。

EDATE函数返回一个日期经过指定月份后的日期，也就是以出生年月为准，男性要经过720个月（60年），女性要经过600个月（50年）就是退休年龄了。

如果要考虑行政级别，比如女干部55岁，那就要根据性别和级别两个元素来进行判断了，你知道怎么判断吗？

好了，咱们今天的内容就先这样，光说不练假把式，动手试试吧。

图文制作：祝洪忠

职场人必备的15个excel函数公式，简单易懂，快速提高工作效率

Hello.大家好，今天跟大家分享15个Excel函数公式，都是我们工作中经常用到的公式，工作中遇到类似的问题，可直接套用，快速提高工作效率，话不多说，下面就让我们来一起学习下吧

1. 身份证号码提取出生日期

公式：=–TEXT(MID(B3,7,8),\”0000-00-00\”)

在这里我们使用mid函数提取身份中号码中的出生日期，然后使用text函数设置数字的格式，因为text是一个文本函数，所以它输出的结果是一个文本，我们在公式的最前面输入两个减号，将文本格式的数值转换为常规格式的设置

2. 计算年龄

公式：=DATEDIF(C3,TODAY(),\”Y\”)

Datedif函数的作用是计算两个时间的差值，

第一参数：开始时间，就是我们提取身份证号码的时间

第二参数：结束时间，我们使用today函数获取今天的日期

第三参数：返回类型，在这里我们设置为Y，代表年份

3. 根据身份证号码提取性别

公式：=IF(MOD(MID(B3,17,1),2)=1,\”男\”,\”女\”)

身份证号码中也包含着每个人的性别信息，它只与身份证号码的第17位有关，第17位是计数性别为男，为偶数性别则为女，所以我们使用mid提取第17位的数值，然后使用mod函数计算这个数的奇偶性，最后使用if函数判断输出结果

4. 合并单元格求和

公式：=SUM(C3:C14)-SUM(D4:D14)

在这里我们需要先选择想要求和的区域，然后在编辑栏输入公式，最后按Ctrl+回车批量填充公式

5. 合并单元格计数

公式：=COUNTA(C3:C14)-SUM(D4:D14)

在这里我们需要先选择想要计数的区域，然后在编辑栏输入公式，最后按Ctrl+回车批量填充公式

6. 合并单元格填充序号

公式：=MAX($A$2:A2)+1

在这里我们需要先选择想要添加序号的数据区域，然后在编辑栏输入公式，最后按Ctrl+回车批量填充公式

7. 按照类别排序

公式：=COUNTIF($B$3:B3,B3)

8. 查找重复内容

公式：=IF(COUNTIF(C:C,C3)>1,\”重复\”,\”\”)

在这里我们首先使用countif函数对C列进行计数，如果结果大于1代表有重复值，然后我们使用if函数来判断结果是不是大于1，大于1就返回重复，如果不大于1就返回空值

9. 条件计数

公式：=COUNTIF(B:B,E2)

Countif函数的作用：条件计数，

语法：=countif（要统计的数据区域，统计条件）

10. 条件求和

公式：=SUMIF(B3:B15,F3,D3:D15)

Sumif函数的作用：条件求和

语法：=sumif（要统计的数据区域，求和条件，求和区域）

11. 条件判断

公式：=IF(C3>=60,\”通过\”,\”不通过\”)

If函数是一个条件判断函数，他能根据条件来返回对应的结果

语法：=IF（判断条件，条件正确时返回的结果，条件错误时返回的结果）

12. 生成随机数

随机小数公式：=RAND()

这个函数不需要参数，选择函数后直接点击回车即可生成小数

随机整数：=RANDBETWEEN(1,100)

在这里我们生成的是一个1到100之间的随机整数，

语法：=RANDBETWEEN（最小值，最大值）

13. 隔行求和

公式：=SUMPRODUCT((MOD(ROW(C3:G8),2)=0)*C3:G8)

在这里我们先使用row函数获取行号，然后使用mod函数判断每一行的奇偶性，最后使用SUMPRODUCT函数进行求和

14. 隔列求和

公式：=SUMPRODUCT((MOD(COLUMN(B4:G8),2)=1)*B4:G8)

与隔行求和相似，使用COLUMN函数获取列号，然后使用mod判断奇偶性，最后求和

15. 排名

公式：=RANK(C3,$C$3:$C$15)

语法：RANK(数值，要名字的数值区域)

以上这些公式你都知道吗？

我是excel从零到一，关注我持续分享更多excel技巧

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com