387. Office技巧：如何控制Excel單元格手機(jī)錄入必須11位且唯一？

臥龍小築 2016-07-09

展開全文

小丑女

請教！Excel怎么又能限定輸入的長度又能限定不重復(fù)？公式應(yīng)該怎么寫？是在數(shù)據(jù)有效性里面輸入公式么？

這個(gè)問題非常使用，尤其是在定制Excel模板的使用，解決方法不算太難，分享給大家!

牛閃閃

Office社區(qū)群的小伙伴的提問一直很給力，提出來的問題也非常專業(yè)。比如下午這位美女的提問，牛閃閃把她的問題寫一遍：請教！Excel怎么又能限定輸入的長度又能限定不重復(fù)？公式應(yīng)該怎么寫？是在數(shù)據(jù)有效性里面輸入公式么？

不知道這個(gè)問題大家能不能幫忙解答呢？牛閃閃也一起來研究一下。

場景：公司HR，行政等需要對單元格錄入進(jìn)行控制的或設(shè)計(jì)Excel模板的辦公人士。

問題：如何控制單元格只能輸入規(guī)定長度和防止重復(fù)錄入？

解答：利用公式數(shù)據(jù)有效性搞定！

實(shí)際上這個(gè)問題涉及二個(gè)問題，一是控制錄入的長度，二是控制不允許重復(fù)錄入。

首先搞定錄入長度的控制的。具體操作方法如下：

選中下圖中手機(jī)號(hào)碼所在的區(qū)域，然后點(diǎn)擊“數(shù)據(jù)—數(shù)據(jù)驗(yàn)證”（2013版本以下稱之為數(shù)據(jù)有效性）

在新彈窗口中，選擇“允許—文本長度—等于—11 ”如下圖2 處。因?yàn)槭謾C(jī)號(hào)必須是11位數(shù)允許錄入進(jìn)去。點(diǎn)擊確定按鈕即搞定。

趕緊測試一下，錄入不是11位就報(bào)錯(cuò)。

但這樣顯示報(bào)錯(cuò)太不人性化了。所以改進(jìn)一下，在之前數(shù)據(jù)有效性的設(shè)置界面里，切換置“出錯(cuò)警告”（下圖3 處）。寫上出錯(cuò)警告信息即可。（下圖 4 處）

如果錄入的人未輸入到11位，就會(huì)彈如下報(bào)錯(cuò)!

搞定了位數(shù)控制后，接著來設(shè)置重復(fù)錄入的限制。還是選中對應(yīng)的手機(jī)號(hào)碼區(qū)域，點(diǎn)擊“數(shù)據(jù)有效性”進(jìn)入。(下圖5 處)

進(jìn)去后發(fā)現(xiàn)糟糕，這不是之前設(shè)置過的限制11位錄入的設(shè)置嗎？難道修改11位錄入的限制？也就說數(shù)據(jù)有效性不能像Excel的條件格式一樣可以在同一數(shù)據(jù)區(qū)域設(shè)置多個(gè)條件格式。也就說數(shù)據(jù)有效性在一個(gè)區(qū)域只能設(shè)置一次。這就麻煩了，如何能保證這樣雙重的數(shù)據(jù)有效性呢？

所以改變思路，雙重?cái)?shù)據(jù)有效性必須要利用公式。首先牛閃閃將設(shè)置可以搞定位數(shù)控制，用公式來代替。具體方法如下：

在“數(shù)據(jù)驗(yàn)證”設(shè)置界面，選擇允許“自定義—等于”，在公式中輸入 =LEN(E4)=11

公式很容易理解，len為單元格長度函數(shù)，判斷起始單元格E4單元的長度，只有等于11才允許錄入。否者報(bào)錯(cuò)。（下圖 6 處）

好！大家先把這個(gè)公式給記下來放在一邊，接著再用公式解決的思路，解決重復(fù)的問題。利用countif函數(shù)判斷手機(jī)號(hào)碼所在的區(qū)域的數(shù)值，如果每個(gè)手機(jī)號(hào)在該區(qū)域的號(hào)碼個(gè)數(shù)只有一個(gè)，則允許錄入。

所以限制重復(fù)錄入的公式寫成這樣。

=COUNTIF($E$4:$E$23,E4)=1

判斷每個(gè)錄入的手機(jī)號(hào)在該區(qū)域的個(gè)數(shù)只有一個(gè)，才允許錄入。（下圖 7 處）

測試一下！果然可以，順道把出錯(cuò)警告加一句，且唯一。

從前面的講解中，大家可以了解到，用len函數(shù)解決11位限制錄入。用countif函數(shù)解決限制重復(fù)錄入。但這兩個(gè)條件都要滿足怎么辦？所以and函數(shù)出場。把前面兩個(gè)函數(shù)用and函數(shù)合并一下。公式的位置可以寫成這樣：

=AND(LEN(E4)=11,COUNTIF($E$4:$E$23,E4)=1)

滿足當(dāng)單元格位數(shù)等于11位，且不重復(fù)，才允許錄入。（下圖 8 處）

大家可以自行測試一下，完美。

總結(jié)：公式數(shù)據(jù)有效性與公式條件格式一樣，都是工作中非常實(shí)用的辦公技巧，也算比較進(jìn)階的Excel技巧。大家平時(shí)一定要學(xué)會(huì)多積累Excel函數(shù)與數(shù)據(jù)有效性和條件格式的搭配使用。

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：臥龍小築 > 《我的圖書館》

舉報(bào)/認(rèn)領(lǐng)