張老師的課代表林林幫班主任錄入全班同學(xué)基本資料。在錄入身份證時(shí)遇到了問(wèn)題，于是來(lái)找張老師。
這是林林首先遇到的問(wèn)題：輸入的身份證號(hào)變成科學(xué)計(jì)數(shù)法的形式顯示，而且在編輯欄中看到最后的幾位數(shù)自動(dòng)變成了0。林林檢查了很多次，也重輸了很多次，結(jié)果都是如此。張老師告訴林林不要緊張，這個(gè)問(wèn)題很容易解決：將單元格格式改為文本，再輸入就可以了。為什么會(huì)出現(xiàn)這樣的問(wèn)題呢？張老師告訴林林：在Excel表格里面，輸入數(shù)字超過(guò)了11位，Excel則會(huì)自動(dòng)轉(zhuǎn)換為科學(xué)記數(shù)格式；輸入如果超出了15位數(shù)值，Excel則會(huì)自動(dòng)將15位以后的數(shù)值轉(zhuǎn)換為“0”。在錄入時(shí)可以先錄入一個(gè)英文單引號(hào)再錄入，或者先修改單元格格式為文本后再錄入就可以解決這個(gè)問(wèn)題了。數(shù)據(jù)輸入之后，林林擔(dān)心不小心輸入了重復(fù)的身份證，于是用COUNTIF函數(shù)統(tǒng)計(jì)每個(gè)身份證出現(xiàn)的次數(shù)，如果統(tǒng)計(jì)結(jié)果是“1”，身份證就是唯一“不重復(fù)”的，否則是“重復(fù)”。統(tǒng)計(jì)的結(jié)果出乎林林預(yù)料，B3和B4的身份證號(hào)明明只有一個(gè)，COUNTIF的結(jié)果卻是相同（重復(fù)）的？張老師告訴林林，COUNTIF函數(shù)在處理時(shí)，會(huì)將文本型數(shù)值識(shí)別為數(shù)值進(jìn)行統(tǒng)計(jì)。由于超過(guò)15位的數(shù)值只能保留15位有效數(shù)字，后3位全部視為0處理，因此COUNTIF函數(shù)會(huì)將B3、B4單元格中的身份證號(hào)碼都識(shí)別為相同，計(jì)數(shù)結(jié)果為2。解決這種誤判的方法是在第二參數(shù)后加“*”，查找的數(shù)據(jù)就是文本而非數(shù)值，就不會(huì)出現(xiàn)前15相同的身份證被判斷為相同了。=IF(COUNTIF(B:B,B3&"*")=1,"不重復(fù)","重復(fù)")林林提出了問(wèn)題：如果在輸入的時(shí)候就可以判斷是否輸入了重復(fù)的身份證，可以從源頭控制錯(cuò)誤，而且身份證都是長(zhǎng)度是18位，怎樣避免輸入的號(hào)碼不是18位，或者與其他人相同？張老師告訴林林，這個(gè)問(wèn)題可以通過(guò)數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）的方法解決。判斷長(zhǎng)度是否是18位：LEN(B3)=18判斷B3在B列中否是唯一的：COUNTIF(B:B,B3&"*")=1上面兩個(gè)要求都要滿足 ，所以用AND函數(shù)——=AND(LEN(B3)=18,COUNTIF(B:B,B3&"*")=1)注意，為了保證能正常錄入，一定要加上&“*”（截圖未加）這樣，當(dāng)再去錄入身份證號(hào)時(shí)，一旦錄入的不是18位就會(huì)提示錯(cuò)誤，或者錄入重復(fù)時(shí)也會(huì)提示錯(cuò)誤。同時(shí)，又保證了哪怕當(dāng)前17位都完全一樣的情況下，也能正常錄入。
|
