Question to say "I can!"

如何利用Excel的“數據有效性”來確保數據輸入的有效性

2013-04-03

如何利用Excel的“數據有效性”來確保數據輸入的唯一性

為了避免重復輸入,可以利用Excel的數據有效性來確保數據唯一性。

在Excel數據錄入的過程中,有些數據是需要確保唯一性的,比如說職員ID,商品編號等,憑人工判斷是非常麻煩的,有時候難免會輸入重復。為了避免重復輸入,可以利用Excel的數據有效性來對數據進行驗證,當輸入已有的內容時,讓Excel自動提示。其具體做法如下:

設置有效性條件

假如A列要輸入的是職員ID,必須具有唯一性,在輸入之前對其進行設置。選擇單元格A1(假設第一個職員ID輸入到A1),執行“數據”菜單中的“有效性”命令,彈出“數據有效性”對話框,選擇“設置”選項卡,在“允許”下拉列表中選擇“自定義”,在“公式”框內輸入“=COUNTIF(A:A,A2)=1”

設置出錯警告

通過上面的方法,把判斷條件已經設置好了,在滿足條件即出現重復項是怎么辦?那就要設置出錯警告。選擇“數據有效性”對話框中的“出錯警告”選項卡,選中“輸入無效數據是顯示出錯警告”復選框,選擇“終止”樣式,在“標題”框內輸入“輸入錯誤”,在“錯誤信息”框內輸入“你輸入的數據已經存在,必須唯一”,最后單擊“確定”按鈕。

復制函數

到此,我們只是設置了最上面的一個單元格,還不能起任何作用,必須把這個設置應用到這一列的所有單元格。選擇單元格A1,鼠標移動到A1的右下角,當鼠標變為“十”字形狀時,拖動鼠標向下覆蓋A列的其它單元格。

效果

這樣,當我們在A列中輸入同列中已有的信息時,Excel就會彈出對話框,提示輸入錯誤,并終止輸入。需要對剛才輸入的數據進行修改光標方可移到下一個單元格。

 

Excel表格中使用數據有效性判斷輸入的身份證號碼是否正確

1、身份證位數(是否為15位或18位)

2、日期是否合法(主要是判斷月份是否在1-12之間,日期是否超出當月的天數等)

3、身份證號是否重復。
數據有效性公式如下:
=NOT(OR(AND(LEN(A1)<>15,LEN(A1)<>18),COUNTIF(A:A,A1)<>1,ISERROR(1*TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))))
可以設置提示內容為:
“身份證位數或者日期有誤,或者身份證號有重復,請核準后重新輸入!”

這樣設置好后有以上三錯誤就不能輸入了。
但還有個缺點,就是不知道和上面那個身份證號重復了。

所以建議采用如下方法:
在數據有效性中只判斷位數和日期問題,公式如下:
=NOT(OR(AND(LEN(A1)<>15,LEN(A1)<>18),ISERROR(1*TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))))
提示內容為:
“身份證位數或者日期有誤,請核準后重新輸入!”
然后在A列用條件格式顯示重復的身份證號碼,條件格式的條件設為:
公式=COUNTIF(A:A,A1)>1
將字體設為紅色。
這樣設置后,位數和日期有問題不能輸入,重復的可以輸入,但會顯示為紅色字體,以便你檢查是這個單元格錯了,還是先輸入的那個單元格(紅色字體)錯了。

作者:admin | Categories:軟件使用 | Tags:

發表評論

電子郵件地址不會被公開。 必填項已用*標注

*

澳洲f1赛车b盘开奖套路 真正高手 十大期货配资公司排名电话 甘肃十一选五走势图 上海期货配资利息 三人麻将游戏单机版 体彩排列三开奖走势 姬野爱AV播放 湖北十一选五彩票 3d近100开奖号 打麻将怎么作弊视频 广西十一选五前三直选技巧 黑龙江22选5中奖 河北十一选五任五一 理财前十名 广东快乐十分20选 成熟的股票指数