《2022年EXCEL电子表格用函数计算年龄、工龄及从身份证中算出周岁等技巧 .pdf》由会员分享，可在线阅读，更多相关《2022年EXCEL电子表格用函数计算年龄、工龄及从身份证中算出周岁等技巧 .pdf（7页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、电子表格常用函数汇总（潘世华2013 年版）注：(1) 如何截取身份证号第17位：MID(C2,17,1) Value（字符型数字）这个函数就是转换字符型数字转成数字N(value) 这个函数,将不是数值形式的值转成数值形式.日期转换成序列值,True 转换成1,False 转换成 0不需要函数，乘1 即可例如001 变数值=A1*1 即等于 11 、 用 “ 身 份 证 号 ” 提 起 出 生 年 月 日 第 一 种 公 式 ：=IF(LEN(C2)=15,19&MID(C2,7,2)&/&MID(C2,9,2)&/&MID(C2,11,2),IF(LEN(C2)=18,MID(C2,7,4
2、)&/&MID(C2,11,2)&/&MID(C2,13,2),) 说明：C2为身份证号码所在的单元格，在实践过程中，把“ C2 ”转换成实际表中的“身份证栏” （身份证栏的输入格式为“文本” ） 。2、 用 “ 身 份证号 ” 提 起出 生 年 月 日 第二 种 公 式 ： （ 很好 ）=CONCATENATE(MID(C2,7,4),年,MID( C2,11,2),月,MID( C2,13,2), 日)3 、“ 用 身 份 证 ” 号 算 出 性 别 第 一 种 公 式 ：=IF(LEN(C2)=15,IF(OR(RIGHT(C2,1)=0,RIGHT(C2,1)=2,RIGHT(C2,1
3、)=4,RIGHT(C2,1)=6,RIGHT(C2,1)=8),女,男),IF(LEN(C2)=18,IF(OR(MID(C2,17,1)=0,MID(C2,17,1)=2,MID(C2,17,1)=4,MID(C2,17,1)=6,MID(C2,17,1)=8),女,男),) 说明：C2为身份证号码所在的单元格，在实践过程中，把“ C2 ”转换成实际表中的“身份证栏” （身份证栏的输入格式为“文本” ） 。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - -
4、 - - - - - - - 4、“用身份证”号算出性别第二种公式：（很好）=IF(VALUE(RIGHT(B2,3)/2=INT(VALUE(RIGHT(B2,3)/2),女, 男)5、由上式算出“出生年月日”后，如何用该出生年月日计算周岁公式：=IF(C2=,INT(TODAY()-C2)/365.25) 说明：C2为身份证号码所在的单元格，在实践过程中，把“ C2 ”转换成实际表中的“身份证栏” （身份证栏的输入格式为“文本” ） 。6 、用“出生年月日”计算周岁第二种公式（很好）： =DATEDIF(C2,TODAY(),y) 说明：C2为出生“年月日”所在的单元格，在实践过程中，把“
5、 K4”转换成实际表中的出生“年月日”（出生“年月日”栏的输入格式为“*2013-3-28” ） 。7、用 “出生年月日”计算月份数题目： 计算日期为 1973-4-1和当前日期的间隔月份数. 公式： =DATEDIF(1973-4-1,TODAY(),M) 结果： 403 简要说明 当单位代码为M时, 计算结果是两个日期间隔的月份数. 1973-4-1 该格式可以手动输入第一格，以后可用填冲方式下拉即可。8、用 “出生年月日”计算天数题目： 计算日期为 1973-4-1和当前日期的间隔天数. 公式： =DATEDIF(1973-4-1,TODAY(),D) 结果： 12273 简要说明 当单
6、位代码为D时, 计算结果是两个日期间隔的天数. 1973-4-1 该格式可以手动输入第一格，以后可用填冲方式下拉即可。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - 9、根据参加工作时间求年资（即工龄） （很好） ：=CONCA TENATE(DATEDIF(F4,TODAY(),y),年,DATEDIF(F4,TODAY(),ym),个月 ) excel 用身份证号计算年龄截止到当前日期，要求精确一些。=DATEDIF(-TE
7、XT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),0-00-00),TODAY(),y)& 周岁零&DATEDIF(-TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),0-00-00),TODAY(),ym)& 月&DATEDIF(-TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),0-00-00),TODAY(),md)& 天 如果身份证号在A1，则在 B1 输入：=IF(LEN(A1)=18,YEAR(TODAY()-MID(A1,7,4),IF(LEN(A2)=
8、15,YEAR(TODAY()-MID(A2,7,2)-1900,) 上述公式是考虑了一代、二代身份证号共存的情况。如果全部是二代身份证，则可以简单点：=YEAR(TODAY()-MID(A1,7,4)，其中YEAR(TODAY()代表今年， MID(A1,7,4) 取身份证中的年份，两个相减就是年龄。1.首先选中表格中要输入身份证号的列，单击“格式”“单元格”“数字”选择“文本”。这样输入的身份证号就不会出现 “1.50428E+18 ”科学记数的情况。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - -
9、- - - - 第 3 页，共 7 页 - - - - - - - - - 2.求性别：身份证号的第 17 位表示性别信息。 单数为男，双数为女。 我样只要半数第 17位数的单双即可。性别的单元格下输入=IF(mod(mid(E2,15,3),2),男, 女)。其中“ E2”为身份证所在的单元格。可以根据自己需要设置为相应的单元格即可。3.求年龄：这个比较简单， 网上有很多公式， 但都很复杂， 我们的思路就是用当前年份减去出生年份即可。 在 Excel 中获取当前年份的函数是 =year(now()。所以我们的公式为： =YEAR(NOW()-MID(E2,7,4)。名师资料总结 - - -精
10、品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - 4.出生日期：出生日期我们用：=TEXT(LEN(E2)=15)*19&MID(E2,7,6+2*(LEN(E2)=18),#-00-00)即 可求出。5.基它人的相关信息， 我们只要把其身份证号输入完毕后。利用 Excel 的自动填充功能向下拉就可以了。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - -
11、 第 5 页，共 7 页 - - - - - - - - - 你的公式是错。请用ngjtyw | 十三级=datedif(text(mid(B3,7,len(B3)*2/3-4),00-00-00),today(),y) len(B3)*2/3-4的意思是15 位号码取6 位日期， 18 位号码取8 位日期。=IF(MID(C2,17,1)/2=INT(MID(C2,17,1)/2),女, 男)如何判断一个数是奇数还是偶数：比较该数与该数的向下取整得到的数是否相等，相等为偶系统自动生成了一个excel 文件，里面是一些数字。我想把这些数字sum 起来却发现结果总是 0。经检查后发现里面的数字是
12、以文本形式保存进去的。用设定单元格格式等方法均无法把文本的数字转换成真正的数字。请问有什么办法可以一批把文本型的单元格变成数字型的吗？数据 分列 完成：可以把文本型数字转换为数字型数字。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - 例如数据在B2:G10范围内 ,可以用下列公式: =SUM(VALUE(B2:G10) 然后同时按三键结束,形成数组公式 . 三键是指 Ctrl+Shift+Enter. 或者用 SUM 求和，那么
13、用数组公式=SUM(- 区域 )。在EXCEL 表中 输 入身份 证 号 码自 动 生 成 周 岁， ( 算 到日 )= （ AF4 TEXT（ TODAY ）（） ，”yyyymmdd “）-MID （G4，7，8） ）/10000-0.5确认下拉。注解表头：A1 姓名 B1性别C1 D1 岀生年月日 E1身份证号码 2 是电脑序号也是第一人所在的行。选中第一人的 周岁 单元格输入公式： =（AD2TEXT （ TODAY）（） ， ”yyyymmdd “） -MID（E2， 7， 8） ） /10000-0.5确认下拉。注：AD2、是 A 列到身份证号前的 列的区间，要输入两个字母；E2、是指身份证号所在的 列 和 行 的位置； 7，8、是指身份证号从左向右数第7 位和第 8 位；除10000-0.5 、是电子表计算周岁的系数；选中周岁列点击 格式栏 单元格数字数值小数为 0确认。 AD 列必须为空；你任何时候打开电脑，周岁都是自动生成的。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -