Document
Cradear's blog
 
首页
归档
标签
关于
友链
Powered by Crazydear | Theme: Fog
载入天数...
载入时分秒...


2021-01-17
2 min read
Excel 笔记

Excel 公式技巧

Excle常用函数

身份证验证

=IF(A5="","",IF(LEN(A5)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(MID(A5,ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)=RIGHT(A5),"正确","错误"),"位数不为18位"))

年龄计算

=YEAR(TODAY())-MID(A3,7,4)

性别

=IF(ISODD(MID(A3,17,1)),"男","女")

出生日期

=TEXT(MID(A2,7,8),"0-00-00") 		# 2021-01-01
=TEXT(MID(A2,7,8),"0\/00\/00")		# 2021/01/01
=TEXT(MID(A2,7,8),"0年00月00日")	  # 2021年01月01日

省份

=LOOKUP(VALUE(LEFT(D2,2)),{11,"北京市";12,"天津市";13,"河北省";14,"山西省";15,"内蒙古自治区";21,"辽宁省";22,"吉林省";23,"黑龙江省";31,"上海市";32,"江苏省";33,"浙江省";34,"安徽省";35,"福建省";36,"江西省";37,"山东省";41,"河南省";42,"湖北省";43,"湖南省";44,"广东省";45,"广西壮族自治区";46,"海南省";50,"重庆市";51,"四川省";52,"贵州省";53,"云南省";54,"西藏自治区";61,"陕西省";62,"甘肃省";63,"青海省";64,"宁夏回族自治区";65,"新疆维吾尔自治区";71,"台湾省";81,"香港特别行政区";82,"澳门特别行政区";"","0"})
2021-01-17
2 min read
Excel 笔记

Excel 公式技巧

Excle常用函数

身份证验证

=IF(A5="","",IF(LEN(A5)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(MID(A5,ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)=RIGHT(A5),"正确","错误"),"位数不为18位"))

年龄计算

=YEAR(TODAY())-MID(A3,7,4)

性别

=IF(ISODD(MID(A3,17,1)),"男","女")

出生日期

=TEXT(MID(A2,7,8),"0-00-00") 		# 2021-01-01
=TEXT(MID(A2,7,8),"0\/00\/00")		# 2021/01/01
=TEXT(MID(A2,7,8),"0年00月00日")	  # 2021年01月01日

省份

=LOOKUP(VALUE(LEFT(D2,2)),{11,"北京市";12,"天津市";13,"河北省";14,"山西省";15,"内蒙古自治区";21,"辽宁省";22,"吉林省";23,"黑龙江省";31,"上海市";32,"江苏省";33,"浙江省";34,"安徽省";35,"福建省";36,"江西省";37,"山东省";41,"河南省";42,"湖北省";43,"湖南省";44,"广东省";45,"广西壮族自治区";46,"海南省";50,"重庆市";51,"四川省";52,"贵州省";53,"云南省";54,"西藏自治区";61,"陕西省";62,"甘肃省";63,"青海省";64,"宁夏回族自治区";65,"新疆维吾尔自治区";71,"台湾省";81,"香港特别行政区";82,"澳门特别行政区";"","0"})