# 判断身份证号格式是否正确
=IF(MID("10X98765432",MOD(SUMPRODUCT(MID(C2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(C2,18,18),"正确","错误")
# 判断表A中的单元格是否在表B中有重合
=IF(ISERROR(MATCH(A2,Sheet4!$A$2:$A$15159,0)),"不相同","相同")
# 判断18位身份证是否有重复。Excel自带的判断重复的功能，只能比较前15位。
=IF(COUNTIF(C:C,C2&"*")>1,"重复","不重复")
# 通过18位身份证号获取属相
=CHOOSE(MOD(MID(B3,7,4)-1900,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")
# 从Sheet2表W列的第3行-第33行，随机取值字典取值
=INDIRECT("Sheet2!W"&RANDBETWEEN(3,33))
# 生成金额随机数
=ROUND(RAND()*1000000,2)
# 生成随机整数
=RANDBETWEEN(1000,90000)
# 通过生出日期，算年龄
=ROUND((TODAY()-D6)/365,0)
# 将后两位向上取整，得到的结果示例：300，1200
=CEILING.MATH(D5, 100)
上一篇 下一篇