最近在貼吧中學(xué)習(xí)Excel，很多問(wèn)題自己不能回答纬朝。只能看別人的答復(fù)收叶。看過(guò)之后覺(jué)得看懂了玄组，但不久之后可能就忘了滔驾，收益甚少。今天突然有了一個(gè)想法俄讹，分析別人的解答結(jié)果哆致，記錄推測(cè)解答思路。也許這樣自己記憶更深刻患膛，理解更透徹摊阀。說(shuō)做就做。
問(wèn)題：如何通過(guò)設(shè)置數(shù)據(jù)有效性踪蹬，避免身份證錄入的錯(cuò)誤胞此？
答案：數(shù)據(jù)-有效性-自定義
式將公式中IF的條件作為有效性檢驗(yàn)條件：
=NOT(iSERR(--TEXT(--MID(A2,7,8),"#-00-00")))*(MID(A2,7,8)<TEXT(NOW(),"emmdd"))*(RIGHT(A2)=MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1))
解析1,關(guān)于數(shù)據(jù)有效性，如果條件公式結(jié)果不是0或是true跃捣，便可以正常錄入數(shù)據(jù)漱牵；條件公式得到的結(jié)果為0或是false，在錄入數(shù)據(jù)之后Excel會(huì)彈出非有效數(shù)據(jù)的提示疚漆，數(shù)據(jù)無(wú)法正常輸入酣胀。
解析2：證個(gè)公式分為三個(gè)判斷，用“*”連接娶聘，說(shuō)明這三個(gè)條件必須要同時(shí)成立闻镶。
解析3第一個(gè)判斷：NOT(ISERR(--TEXT(--MID(A2,7,8),“#-00-00”)))，判斷身份證的第7位到第15位是否為日期丸升。
(1)--MID(A2,7,8),去除身份證號(hào)碼中的日期铆农，將其轉(zhuǎn)化為數(shù)字。
(2)函數(shù)text狡耻，降數(shù)字轉(zhuǎn)化為日期墩剖，及19230203，表示的是1923年2月3日夷狰，而非一九二三千萬(wàn)零二百零三涛碑，函數(shù)text前的“--”挥萌，將日期轉(zhuǎn)化為所代表的數(shù)字既：1923年2月3日=8435荒澡，如果月份大于13，或日期大于當(dāng)月最大天數(shù)萎战，都會(huì)出現(xiàn)#value瘫证！
(3)iserr()揉阎，檢測(cè)返回時(shí)是否是#N/A以外的錯(cuò)誤只，是返回true（真）背捌，否則返回false（假）
所以第一部分判斷身份證出生日期部分是否是一個(gè)日期毙籽，如果是返回true(參與*運(yùn)算時(shí)相當(dāng)于1)，否則返回false（參與*運(yùn)算時(shí)相當(dāng)于0）毡庆。
解析4第二個(gè)判斷：mid(A2,7,8)<text(now(),"emmdd")
(1)now()返回坑赡，現(xiàn)在時(shí)間的數(shù)值烙如，
(2)text中，“emmdd”,是將8位日期形式文本
故第二個(gè)判斷是身份證日期不得大于當(dāng)天日期
解析5第三個(gè)判斷：(RIGHT(A2)=MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1))
這是身份證驗(yàn)證碼算法用Excel中的公式表示毅否。
