Excel中數據驗證的進階功能——7種自定義數據驗證設置

2020-12-13 疏木職場辦公

在Excel中，利用數據驗證可以對數據的錄入添加一定的限制條件。比如我們可以通過數據驗證的基本設置使單元格只能錄入整數、小數、時間、日期等，也可以創建下拉菜單選項。數據驗證的基本功能在前面的文章已進行介紹，連結地址：excel不得不了解的數據驗證基本功能。本文給大家介紹的是7種自定義數據驗證的設置方法，即下面圖中右側綠色的部分。

一：限制輸入空格。

如下動圖所示，如果要對A列的數據限制輸入空格，那麼選中A列數據後，在【數據】選項卡——【數據驗證】——【允許】——【自定義】中輸入函數公式=ISERROR(FIND(" ",A1))。

此處find函數中雙引號之間有一個空格。函數公式表示查找單元格中的空值，找到就可以輸入內容，找不到會報錯提示。

然後我在A10單元格輸入「劉備」，無論是文字前、文字中間、還是文字後，只要有空格都會彈出錯誤提醒。

二：限制輸入重複值。

限制輸入重複值選中A列後，在【數據驗證】——【自定義】中輸入的函數公式為=COUNTIF(A:A,A1)<2。這個函數表示統計A列中當前單元格重複值個數，如果小於2，即僅有一個時可以正常輸入，否則會報錯。

如下圖所示，在A10單元格輸入劉備時，可以正常輸入，輸入趙雲時因為和前面A3單元格重複，會出現錯誤提示。

三：只允許輸入小寫字母。

此處在數據驗證——自定義中輸入的公式是=EXACT(C1, LOWER(C1))。lower函數表示將大寫轉化為小寫的函數。exact函數表示兩個參數對比。此處利用這兩個函數組合。

如果輸入的是小寫，lower函數轉化後仍然是小寫，exact函數兩個參數一致，可以輸入內容；如果第一個參數大寫，第二個參數用lower函數轉化為小寫，此時exact函數兩個參數不一致，就會報錯。

在C2、C3單元格輸入小寫字母沒有問題，輸入大寫字母就會出現錯誤提醒。

四：只允許輸入大寫字母。

與只允許輸入小寫字母同理，只允許輸入大寫字母的函數公式是= EXACT(C1, UPPER(C1))，upper函數表示將小寫字母轉化成大寫字母。exact函數與上述用法相同。

五：輸入時以固定字符開頭。

此處選中B列後，在自定義中的函數公式為=LEFT(B1, 3) ="132"。表示提取當前單元格中的前3個字符，如果是132，可以正常輸入，如果不是132，就會彈出出錯提醒。

此處的固定字符不僅局限於數字，也可以是字母、中文等。

如果要同時滿足132開頭，長度為8位，且為數字三個條件，則函數公式改為= AND(LEFT(B1,3) ="132",LEN(B1)=8,ISNUMBER(INT(B1)))即可。

六：只允許錄入文本。

只允許錄入文本時，選中C列數據，然後在自定義數據驗證中輸入函數=ISTEXT(C1)即可。

istext函數表示判斷是否為文本。如果要添加更多的限制條件，可以參考上一條學號的設置，用and函數嵌套即可。

七：禁止修改已有內容。

如下圖所示，對於已有內容設置禁止修改時，首先選中禁止修改的內容，然後在自定義數據驗證數輸入函數公式=ISBLANK(A1:B9)。

isblank本來是判斷是否為空值的函數，但是用在數據驗證用也可以實現防止修改已有內容的目的。

這就是本文介紹的7種自定義數據驗證，你掌握了嗎？

相關焦點

  • excel不得不了解的數據驗證基本功能
    excel中，我們有時候會需要在單元格進行添加一些限制條件，或者是創建下拉列表等操作，這時就用到了數據驗證（2013版本之前叫數據有效性），今天給大家介紹一下數據驗證的基本功能。我們點擊數據——數據驗證的功能選項，會彈出數據驗證的對話框，這個對話框包含四部分內容：設置、輸入信息、出錯警告、輸入法模式。下面給大家介紹一下這些基本功能。
  • excel中禁止錄入重複的數據,你不會,同事使用數據驗證輕鬆搞定
    Hello,大家好，今天你跟大家分享下在excel中如何禁止錄入重複的數據，設置這樣的效果我們使用數據驗證僅需2分鐘即可搞定,下面就讓我們來一起操作下吧首先我們選擇想要設置的數據區域，比如在這我們選擇A列，然後點擊數據找到數據驗證
  • Excel函數公式:數據驗證設置技巧,必須掌握
    實際的工作中，我們在處理數據時往往對數據有所限定，例如只能輸入大於等於0的數，只能數據某一時間段內的日期等等，類似的功能在Excel中該如何去實現了？一、數值空值：大於等於0。2、【數據】-【數據驗證】，選擇【允許】中的【自定義】，在【公式】中輸入：=COUNTIF(B$3:B3,B3)=1。3、單擊【出錯警告】標籤，錄入警告信息。4、【確定】並驗證。
  • Excel設置數據有效性(數據驗證)！
    數據有效性不僅能夠在單元格中創建下拉列表菜單方便用戶選擇輸入，還可以對單元格的今天和大家分享一組數據有效性的應用技巧，希望能對你有所幫助。本文通過準確錄入手機號碼案例來講解Excel如何設置有效性。　　為了保證手機號碼的規範錄入，限制手機號錄入只能錄入11位整數，我們可以使用數據驗證來規範。
  • Excel設置數據驗證—即限制表格中輸入的內容
    滑鼠拖動單元格，可多選也可單選Step2：點擊【數據】選項卡下【數據工具】組中的【數據驗證】，再選擇下拉中的【數據驗證】選項；驗證條件選擇允許「整數」、「小數」、「日期」和「時間」後，還需要設置【數據】的範圍及臨界值，則Excel單元格中輸入的內容將受到該類型及臨界值的限制；
  • excel數據處理:多列數據驗證的操作技巧
    用數據有效性（新版本叫數據驗證）製作下拉選項是大家非常喜歡的一個功能，操作方法也非常簡單：但是最近收到網友提問，說他製作下拉選項時總是報錯，不能設置有效性了。一起來看看：相信大家都明白了，他要製作下拉選項的數據不在一列，而是分別存放在三列中。彈出的提示很明確，數據必須是一行或者一列的引用：那麼對於這種情況，真的就沒有解決辦法了嗎？肯定不是，下面就告訴大家一個如何使用多列數據作為下拉選項的方法。這個方法只需要非常簡單的三步就能完成。
  • Excel裡的數據驗證,也太好用了叭
    什麼是數據驗證數據驗證位於 Excel 中「數據」選項卡下的「數據工具」功能組，根據 Excel 中的提示，它用於限制數據類型或用戶輸入單元格的值，最常見用法之一是創建下拉列表。對方不愛認真看填寫規範，那我們就讓他們在填寫時按照我們的指示來填寫。
  • 學會Excel中數據驗證的技巧,讓你事半功倍!
    如下圖所示，數據驗證在數據選項卡下，可以用來規範數據輸入、提高數據錄入速度、檢驗異常數據等。選擇任意單元格區域，點擊數據驗證，可以允許整數、小數、日期、時間、文本長度，這些可以用來規範我們要輸入的數據；可以允許自定義，輸入公式限制輸入的內容；可以允許序列，快速錄入數據。
  • 幾個簡單設置讓你的數據不再出錯——Excel 數據驗證
    在日常辦公中，我們經常需要使用Excel表格來輸入身份證號、電話號碼、成績等數據的時候，儘管操作很熟練，但是數據多了還是免不了出錯的；怎麼辦呢？有沒有什麼解決方法？有的，我們可以通過EXCEL的數據驗證工具來設置一些設定，讓數據不再出錯。
  • Excel中下拉菜單的製作-定義名稱、indirect函數和數據驗證功能
    經常用Excel的同學想必都碰到過下圖中的下拉菜單，今天就通過Excel中的數據驗證功能、定義名稱和indirect函數來實現下拉菜單的製作。選擇性別欄所有單元格，然後在菜單欄中選擇數據，再選擇數據驗證，在彈出的窗口允許下拉菜單中選擇序列，來源一欄中輸入「男,女」，注意：男和女中間的逗號是英文字符中的逗號。點擊確定就完成了性別欄下拉菜單的製作，點擊性別欄都有了下拉菜單的選項。請下方參考動圖。
  • Excel:如何通過設置數據驗證條件,來限制單元格輸入範圍?
    不是輸入的身份證號碼位數不對，就是輸入的數據重複……各種問題層次不窮。我想說：為什麼不同數據驗證呢？它不僅可以將輸入的數字限制在指定範圍內，也可以限制文本的字符數，還可以將日期或時間限制在某一時間範圍之外，甚至可以將數據限制在列表中的預定義項範圍內，對於複雜的設置也可以通過自定義完成。這麼好用的功能，為啥不用呢？
  • excel數據驗證:用下拉菜單讓錄入規範化
    點開數據選項卡，就可以看到啦～數據驗證作用：根據輸入的規則，限制單元格中數據輸入的類型。對於不符合規則的數據輸入，會進行提示和警告。不明白？沒關係，我們實驗一下就知道了！步驟：① 選擇需要設置數據驗證的區域，這裡選擇區域C2:C8② 點擊「數據驗證」按鈕，進入「設置」選卡③ 在「允許」下拉菜單中，選擇「序列」④ 在「來源」一欄中，
  • Excel催化劑數據有效性驗證增強版,補足Excel原生功能天生不足
    添加數據驗證選擇要添加數據驗證的區域後，再點擊【添加數據驗證】按鈕，不使用原生的數據驗證添加按鈕，改用此處的，然後再調用原生的數據驗證功能設置。智能表格僅需選定某列的一個單元格設置數據有效性驗證即可增強版數據有效性驗證作用方式使用以上的方式設置過的數據有效性，不單有原生的有效性功能存在，亦可對複製粘貼過後的數據有效性得到保留。
  • Excel如何通過數據驗證將不滿足的數據圈出來和刪除標記？
    我們有時候可以使用excel的數據驗證，將不滿足條件的數據框選出來，如何來操作呢，一起來看看吧。1.首先我們給excel 表格中的數據全部選中。2.然後點擊上方的數據驗證，設置數據小於五，點擊確定。3.下面可以看到有一些數據是不符合數據驗證中的設置。
  • Excel中不起眼的數據驗證,解決工作數據錄入難問題
    Excel數據驗證在日常工作中很實用的，我們直接舉三個應用的場景1、限制條件輸入下表是一份工資表，這個表格發給各部門經理，他們可以進行相應的調整，但是調整的金額只能是-1000到1000之間>我們可以用數據驗證來進行設置然後在彈出的窗口中選擇整數，介入-1000和1000之間填入進去，然後我們在表格裡面輸入超過這個範圍外的數據就會報錯，只能輸入這之間的數據如果每個員工的可調整的幅度都不一樣，可以搭配VLOOKUP函數來進行使用，這裡不做過多的介紹
  • Excel如何通過數據驗證提高數據輸入效率，如何取消數據驗證？
    我們有時候需要在數據中輸入重複的幾個信息，如何來提高效率呢？進行小編就來給大家提供一種數據驗證輸入的技巧來提高效率，趕緊來圍觀吧！不過有兩種情況的數值輸入，一種是常規的性別輸入，一種是日期的範圍輸入，下面先來看看性別的輸入吧！
  • excel數據驗證：用下拉菜單讓錄入規範化
    點開數據選項卡，就可以看到啦～數據驗證作用：根據輸入的規則，限制單元格中數據輸入的類型。對於不符合規則的數據輸入，會進行提示和警告。不明白？沒關係，我們實驗一下就知道了！步驟：①      選擇需要設置數據驗證的區域，這裡選擇區域C2:C8②      點擊「數據驗證」按鈕，進入「設置」選卡③      在「允許」下拉菜單中，
  • excel自定義格式數據與真實數據的轉換
    excel自定義格式數據與真實數據的轉換工廠對於產品會有編號的前綴需求例如：「ZL2020」，但是在統計產品數量方面需求的時候，又需要自定義格式的顯示結果轉化為真實數據，解決方法：就是使用剪貼板強大的粘貼功能實現。
  • 用數據驗證功能限制表格相同內容重複出現的次數
    在日常工作中，小夥伴們有沒有碰到過這種情況：表格中的相同內容不允許重複出現，或者只允許出現兩次，輸入完成再一個個檢查工作量相當大。今天小編教大家利用Excel的數據驗證功能限制相同內容重複次數，當超過設定的次數，表格自動提醒，並不允許再輸入。
  • 用數據驗證功能一鍵圈出Excel表格中所有學生的不及格成績
    在日常使用Excel表格時，有時需要圈出一些不符合要求的內容，也就是無效數據，在數據量非常大的時候，密密麻麻的數字讓人看得眼睛疼。如果使用公式的話，也是需要花費一點時間的，另外如果要圈出來，在找到無效數據後，還得一個一個加紅圈，那會非常麻煩。下面就以圈出學生不及格成績為例，來介紹一鍵圈出不及格成績的方法。