常用的EXCEL公式
一、判断某个数据与某个区域数据是否重复
=IF(ISERROR(MATCH(A1,C1:C5,0)),”不重复”,”重复”)
二、批量删除单元格最后两位
=SUBSTITUTE(A2,RIGHTB(A2,2),””,1)
三、批量替换身份证后四位为*
=mid(c2,1,len(C2)-4)&”****”
从第7列开始替换4位。
=REPLACE(A1,7,4,”****”)
四、身份证号算男女
=IF(MOD(MID(E2,17,1),2),”男”,”女”)
五、计算年龄
=DATEDIF(TEXT(MID(E2,7,8),”#-00-00″),TODAY(),”Y”)
=(YEAR(NOW())-YEAR(A2))
六、提出出生日期
=IF(LEN(G2)=15,CONCATENATE(“19″,MID(G2,7,2),”-“,MID(G2,9,2),”-“,MID(G2,11,2)),CONCATENATE(MID(G2,7,4),”-“,MID(G2,11,2),”-“,MID(G2,13,2)))
七、身份证号码校验
=IF(LEN(A2)=0,”空”,IF(LEN(A2)=15,”老号”,IF(LEN(A2)<>18,”位数不对”,IF(CHOOSE(MOD(SUM(MID(A2,1,1)*7+MID(A2,2,1)*9+MID(A2,3,1)*10+MID(A2,4,1)*5+MID(A2,5,1)*8+MID(A2,6,1)*4+MID(A2,7,1)*2+MID(A2,8,1)*1+MID(A2,9,1)*6+MID(A2,10,1)*3+MID(A2,11,1)*7+MID(A2,12,1)*9+MID(A2,13,1)*10+MID(A2,14,1)*5+MID(A2,15,1)*8+MID(A2,16,1)*4+MID(A2,17,1)*2),11)+1,1,0,”X”,9,8,7,6,5,4,3,2)=IF(ISNUMBER(RIGHT(A2,1)*1),RIGHT(A2,1)*1,”X”),”正确”,”错误”))))
八、多条件判断公式
SWITCH 函数最简单的形式表示：
=SWITCH(要转换的值, 要匹配的值1…[2-126], 如存在匹配项1…[2-126]需返回的值, 如不存在匹配需返回的值)
可计算多达 126 个匹配的值和结果
查看以下公式：
-
要转换的值？在此示例中，工作日(A2) 等于 2。
-
要匹配什么值？在此示例中为 1、2 和 3。
-
如果存在匹配，希望结果返回什么？此示例中，为星期天返回 1，为星期一返回 2，为星期二返回 3。
-
如未找到匹配项则返回默认值。此示例中为“无匹配”这一文本。
九、获取工作表和工作簿名称工程
获取工作表名：=CELL(“filename”)
获取工作簿名：=CELL(“filename”,A1)
PS：工作表要先保存，确保有路径
十、生成随机密码函数
=CHAR(INT(RAND()*26+65))&INT(RAND()*9+1)&CHAR(INT(RAND()*26+97))&INT(RAND()*900+100)&CHAR(INT(RAND()*26+97))&INT(RAND()*9+1)&CHAR(INT(RANDBETWEEN(33,47)))
生成规则：大字英文字母 + 数字 + 小写英文字母 + 数字 + 数字 + 数字 + 小写英文字母 + 数字 + 特殊字符