Excel 數據驗證：在 Excel 中添加、使用、複製和刪除數據驗證
在 Excel 中，數據驗證是一項有用的功能，您可以使用它來限制用戶可以在單元格中輸入的內容。 例如，數據驗證功能可以幫助您限製文本字符串的長度，或以特定字符開頭/結尾的文本，或要輸入的唯一值等。
在本教程中，我們將討論如何在 Excel 中添加、使用和刪除數據驗證，並將詳細演示該功能的一些基本和高級操作。
目錄：
- 4.1 數據驗證只允許數字或文本
- 4.2 數據驗證只允許字母數字值
- 4.3 數據驗證允許文本以特定字符開始或結束
- 4.4 數據驗證允許條目必須包含/不得包含特定文本
- 4.5 數據驗證只允許唯一值
- 4.6 數據驗證只允許大寫/小寫/正確大小寫
- 4.7 數據驗證允許存在/不存在於另一個列表中的值
- 4.8 數據驗證強制只輸入電話號碼格式
- 4.9 數據驗證強制只輸入電子郵件地址
- 4.10 數據驗證強制只輸入 IP 地址
- 4.11 數據驗證限制超過總值的值
- 4.12 數據驗證限制基於另一個單元格的單元格輸入
- 4.13 數據驗證只允許輸入工作日或週末
- 4.14 數據驗證允許根據今天的日期輸入日期
- 4.15 數據驗證允許根據當前時間輸入時間
- 4.16 數據驗證特定或當前年份的日期
- 4.17 數據驗證當前週或月的日期
1. Excel 中的數據驗證是什麼？
數據驗證 功能可以幫助您限制工作表中的輸入內容。 通常，您可以創建一些驗證規則來阻止或僅允許在選定單元格列表中輸入某種類型的數據。
數據驗證功能的一些基本用法：
 |
|
2.如何在Excel中添加數據驗證？
在 Excel 工作表中，您可以通過以下步驟添加數據驗證：
1. 選擇要設置數據驗證的單元格列表，然後單擊 數據 > 數據驗證 > 數據驗證，請參見屏幕截圖：
2。 在 數據驗證 對話框中的 設定 選項卡，請創建您自己的驗證規則。 在標準框中，您可以提供以下任何類型：
- 價值觀：直接在條件框中鍵入數字；
- 單元格參考：引用工作表或其他工作表中的單元格；
- 公式：創建更複雜的公式作為條件。
例如，我將創建一個規則，只允許輸入 100 到 1000 之間的整數，這裡設置的條件如下圖所示：
3. 配置好條件後，就可以進入 輸入信息。 or 錯誤警報 選項卡以根據需要為驗證單元格設置輸入消息或錯誤警報。 （如果您不想設置提醒，請點擊 OK 直接結束。）
3.1) 添加輸入消息（可選）：
您可以創建在選擇包含數據驗證的單元格時顯示的消息。 此消息有助於提醒用戶他們可以在單元格中輸入什麼。
轉到 輸入信息。 選項卡並執行以下操作：
 |
|
現在，當您選擇一個經過驗證的單元格時，將顯示一個消息框，如下所示：
3.2）創建有意義的錯誤消息（可選）：
除了創建輸入消息之外，您還可以在將無效數據輸入到帶有數據驗證的單元格中時顯示錯誤警報。
轉到 錯誤警報 的選項卡 數據驗證 對話框，請這樣做：
 |
|
現在，當輸入無效值時，將彈出消息警告框，如下圖所示：
停止 選項：您可以單擊 重試 鍵入另一個值或 取消 刪除條目。
警告 選項：點擊 是 輸入無效的條目， 沒有 修改它，或 取消 刪除條目。
資訊 選項：點擊 OK 輸入無效條目或 取消 刪除條目。
備註: 如果您沒有在 錯誤警報 框，默認 停止 將顯示如下圖所示的警報提示框：
3. 數據驗證的基本示例
使用此數據驗證功能時，有 8 個內置選項供您設置數據驗證。 如：任何值、整數和小數、日期和時間、列表、文本長度和自定義公式。 在本節中，我們將討論如何使用 Excel 中的一些內置選項？
3.1 整數和小數的數據驗證
1. 選擇您希望只允許整數或小數的單元格列表，然後單擊 數據 > 數據驗證 > 數據驗證.
2。 在 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇對應的項目 完整的號碼 or 十進制 ，在 讓 下拉框。
- 然後，選擇您需要的標準之一 數據 框（在這個例子中，我選擇 之間 選項）。
- 保養竅門：標準包含：介於、不介於、等於、不等於、大於、小於、大於或等於、小於或等於。
- 接下來，輸入 最低限度 和 最大值 您需要的值（我想要 0 到 1 00 之間的數字）。
- 最後點擊 OK 按鈕。
3. 現在，只允許在您選擇的單元格中輸入從 0 到 100 的整數。
3.2 日期和時間的數據驗證
要驗證要輸入的特定日期或時間，使用它很容易 數據驗證，請執行以下操作：
1. 選擇您只想允許特定日期或時間的單元格列表，然後單擊 數據 > 數據驗證 > 數據驗證.
2。 在 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇對應的項目 日期 or Time ，在 讓 下拉框。
- 然後，選擇您需要的標準之一 數據 框（這裡我選擇 大於 選項）。
- 保養竅門：標準包含：介於、不介於、等於、不等於、大於、小於、大於或等於、小於或等於。
- 接下來，輸入 開始日期 你需要（我想要大於 8/20/2021 的日期）。
- 最後點擊 OK 按鈕。
3. 現在，只允許在所選單元格中輸入大於 8/20/2021 的日期。
3.3 文本長度的數據驗證
如果您需要限制可以鍵入到單元格中的字符數。 例如，要將特定範圍的內容限制為不超過 10 個字符，這 數據驗證 也可以幫你一個忙。
1. 選擇要限製文本長度的單元格列表，然後單擊 數據 > 數據驗證 > 數據驗證.
2。 在 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 文字長度 來自 讓 下拉框。
- 然後，選擇您需要的標準之一 數據 框（在這個例子中，我選擇 小於 選項）。
- 保養竅門：標準包含：介於、不介於、等於、不等於、大於、小於、大於或等於、小於或等於。
- 接下來，輸入 最大值 您需要限制的數量（我希望文本長度不超過 10 個字符）。
- 最後點擊 OK 按鈕。
3. 現在，所選單元格只允許鍵入少於 10 個字符的文本字符串。
3.4 數據驗證列表（下拉列表）
有了這個強大的 數據驗證 功能，您也可以快速輕鬆地在單元格中創建下拉列表。 請這樣做：
1. 選擇要插入下拉列表的目標單元格，然後單擊 數據 > 數據驗證 > 數據驗證.
2。 在 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 名單 來自 讓 下拉列表。
- 在 資源 在文本框中，鍵入以逗號直接分隔的列表項。 例如，要將用戶輸入限制為三個選項，請鍵入 Not Started、In Progress、Completed，或者您可以選擇包含要插入下拉列表的值的單元格列表。
- 最後點擊 OK 按鈕。
3. 現在，下拉列表已創建到單元格中，如下圖所示：
4. 高級自定義數據驗證規則
本節，我將介紹如何制定一些高級的自定義數據驗證規則來解決您的各種問題，例如：創建驗證公式以僅允許數字或文本字符串、僅允許唯一值、僅指定電話號碼、電子郵件地址等.
4.1 數據驗證只允許數字或文本
僅允許使用數據驗證功能輸入數字
要僅允許一系列單元格中的數字，請執行以下操作：
1. 選擇您只想輸入數字的單元格範圍。
2。 點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，輸入這個公式： =編號(A2) 到 公式 文本框。 （A2 是要限制的所選範圍的第一個單元格）
- 點擊 OK 按鈕關閉此對話框。
3. 從現在開始，只能在選定的單元格中輸入數字。
備註：這 編號 函數允許驗證單元格中的任何數值，包括整數、小數、分數、日期和時間。
僅允許使用數據驗證功能輸入文本字符串
要將單元格條目限制為僅文本，您可以使用 數據驗證 具有基於自定義公式的功能 文本 功能，請這樣做：
1. 選擇您只想輸入文本字符串的單元格範圍。
2。 點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，輸入這個公式： =ISTEXT(A2) 到 公式 文本框。 （A2 是要限制的所選範圍的第一個單元格）
- 點擊 OK 按鈕關閉此對話框。
3. 現在，當向特定單元格輸入數據時，只能允許文本格式數據。
4.2 數據驗證只允許字母數字值
出於某些目的，您只想允許輸入字母和數字值，但限制 ~、%、$、空格等特殊字符，本節將為您介紹一些技巧。
數據驗證功能只允許字母數字值
為了防止特殊字符但只允許字母數字值，您應該在 數據驗證 功能，請執行以下操作：
1. 選擇您只想輸入字母數字值的單元格範圍。
2。 點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是要限制的選定範圍的第一個單元格。
3. 現在，只允許輸入字母和數字值，輸入時將限制特殊字符，如下圖所示：
僅允許具有驚人功能的字母數字值
可能上面的公式對於我們理解和記憶起來比較複雜，在這裡，我將介紹一個方便的功能—— 防止打字 of Excel的Kutools，借助此功能，您可以輕鬆快速地解決此工作。
安裝後 Excel的Kutools，請這樣做：
1. 選擇您只想輸入字母數字值的單元格範圍。
2。 然後，點擊 庫工具 > 防止打字 > 防止打字，請參見屏幕截圖：
3。 在彈出 防止打字 對話框中選擇 禁止輸入特殊字符 選項，請參見屏幕截圖：
4。 然後，點擊 Ok 按鈕，然後在以下提示框中，單擊 是 > OK 以完成操作。 現在，在選定的單元格中，只允許使用字母和數值，請看截圖：
4.3 數據驗證允許文本以特定字符開始或結束
如果某個範圍內的所有值都應以特定字符或子字符串開頭或結尾，您可以使用基於 EXACT、LEFT、RIGHT 或 COUNTIF 函數的自定義公式進行數據驗證。
允許文本以特定字符開頭或結尾，只有一個條件
例如，我希望在特定單元格中輸入文本字符串時，文本必須以“CN”開頭或結尾，請這樣做：
1. 選擇僅允許以特定字符開頭或結尾的文本的單元格範圍。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- Begin with: =EXACT(LEFT(A2,2),"CN")
End with: =EXACT(RIGHT(A2,2),"CN") - 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是所選範圍的第一個單元格，數字 2 是您指定的字符數， CN 是您想要開始或結束的文本。
3. 從現在開始，只有以指定字符開頭或結尾的文本字符串才能輸入到選定的單元格中。 否則，將彈出警告警報提醒您，如下圖所示：
提示： 上述公式區分大小寫，如果您不需要區分大小寫，請應用以下 CONTIF 公式：
Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")
備註: 星號 * 是匹配一個或多個字符的通配符。
允許文本以具有多個條件的特定字符開頭或結尾（OR 邏輯）
例如，如果您希望文本必須以“CN”或“UK”開頭或結尾，如下圖所示，您需要使用加號 (+) 添加另一個 EXACT 實例。 請按照以下步驟操作：
1. 選擇僅允許以多個條件開頭或結尾的文本的單元格範圍。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK") - 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是所選範圍的第一個單元格，數字 2 是您指定的字符數， CN 和 UK 是您想要開始或結束的特定文本。
3. 現在，只有以指定字符開頭或結尾的文本字符串才能輸入到所選單元格中。
提示： 要忽略大小寫敏感，請應用以下 CONTIF 公式：
Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")
備註: 星號 * 是匹配一個或多個字符的通配符。
4.4 數據驗證允許條目必須包含/不得包含特定文本
本節，我將討論如何應用數據驗證來允許值必須包含或不得包含 Excel 中的一個特定子字符串或多個子字符串之一。
允許條目必須包含一個或多個特定文本之一
允許條目必須包含一個特定文本
例如，要允許包含特定文本字符串的條目，所有輸入的值都應包含文本“KTE”，如下圖所示，您可以使用基於 FIND 和 ISNUMBER 函數的自定義公式應用數據驗證。 請這樣做：
1. 選擇僅允許包含特定文本的文本的單元格範圍。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 從允許下拉列表中。
- 然後，將以下公式之一輸入 公式 文本框。
- =ISNUMBER(FIND("KTE",A2)) (Case sensitive)
=ISNUMBER(SEARCH("KTE",A2)) (Non case sensitive) - 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是所選範圍的第一個單元格，文本 KTE 是條目必須包含的文本字符串。
3. 現在，當輸入的值不包含設計的文本時，會彈出警告提示框。
允許條目必須包含許多特定文本之一
上述公式僅適用於一個文本字符串，如果您需要如下圖所示的單元格中允許多個文本字符串中的任何一個，您應該一起使用 SUMPRODUCT、FIND 和 ISNUMBER 函數來創建一個公式。
1. 選擇一個單元格範圍，這些單元格只允許包含多個項目中的任何一個的文本。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，根據需要輸入以下公式之一 公式 文本框。
- =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0 (Non case sensitive) - 然後，單擊 OK 關閉對話框。
備註：在以上公式中， A2 是所選範圍的第一個單元格， C2：C4 是您希望允許條目包含其中任何一個的值列表。
3. 現在，只能輸入包含特定列表中任何一個值的條目。
允許條目不得包含一個或多個特定文本之一
允許條目不得包含一個特定文本
要驗證條目不得包含特定文本，例如，要允許單元格中不得包含文本“KTE”的值，您可以使用 ISERROR 和 FIND 函數來創建數據驗證規則。 請這樣做：
1. 選擇僅允許不包含特定文本的文本的單元格範圍。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將以下公式之一輸入 公式 文本框。
- =ISERROR(FIND("KTE",A2)) (Case sensitive)
=ISERROR(SEARCH("KTE",A2)) (Non case sensitive) - 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是所選範圍的第一個單元格，文本 KTE 是條目不得包含的文本字符串。
3. 現在，將禁止輸入包含特定文本的條目。
允許條目不得包含許多特定文本之一
為了防止列表中的許多文本字符串之一被輸入如下屏幕截圖所示，您應該這樣做：
1. 選擇要阻止某些文本的單元格範圍。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 標籤，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0 (Non case sensitive) - 然後，單擊 OK 關閉對話框。
備註：在以上公式中， A2 是所選範圍的第一個單元格， C2：C4 是您要阻止的值列表，如果條目包含其中任何一個。
3. 從現在開始，將禁止輸入包含任何特定文本的條目。
4.5 數據驗證只允許唯一值
如果您想防止在單元格範圍內輸入重複數據，本節將介紹一些在 Excel 中解決此任務的快速方法。
僅允許具有數據驗證功能的唯一值
通常，帶有基於 COUNTIF 函數的自定義公式的數據驗證功能可以幫助您，請執行以下步驟：
1. 選擇您只想輸入唯一值的單元格或列。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- =COUNTIF($A$2:$A$9,A2)=1
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2：A9 是您希望只允許唯一值的單元格範圍，以及 A2 是所選範圍的第一個單元格。
3. 現在，只允許輸入唯一值，並且在輸入重複數據時會彈出警告消息，請參見截圖：
僅允許具有 VBA 代碼的唯一值
以下 VBA 代碼也可以幫助您防止輸入重複值，請按以下步驟操作：
1. 右鍵單擊要僅允許唯一值的工作表選項卡，然後選擇 查看代碼 從上下文菜單中，彈出 Microsoft Visual Basic for Applications 窗口，請將以下代碼複製並粘貼到空白模塊中：
VBA 代碼：只允許單元格範圍內的唯一值：
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
Dim xRg As Range, iLong, fLong As Long
If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
Application.EnableEvents = False
For Each xRg In Target
With xRg
If (.Value <> "") Then
If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
iLong = .Interior.ColorIndex
fLong = .Font.ColorIndex
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
.ClearContents
.Interior.ColorIndex = iLong
.Font.ColorIndex = fLong
End If
End If
End With
Next
Application.EnableEvents = True
End If
End Sub
 |
 |
 |
備註：在上面的代碼中， A1：A100 和 答： 是要防止重複的列中的單元格，請根據需要更改它們。
2. 然後保存並關閉此代碼，現在，當在單元格 A1:A100 中輸入重複值時，會彈出警告提示框，如下圖所示：
僅允許具有方便功能的唯一值
如果你有 Excel的Kutools，其 防止重複 功能，您可以設置數據驗證以防止只需單擊幾下就可以防止一系列單元格重複。
安裝後 Excel的Kutools，請這樣做：
1. 選擇要防止重複值但只允許唯一數據的單元格範圍。
2。 然後，點擊 庫工具 > 防止打字 > 防止重複，請參見屏幕截圖：
3. 並且會彈出警告消息提醒您如果應用此功能將刪除數據驗證，單擊 是 並在以下提示框中，單擊 OK，請參見屏幕截圖：
 |
|
 |
4. 現在，當您在指定的單元格中輸入一些重複數據時，會顯示一個提示框，提醒您重複數據無效，請看截圖：
4.6 數據驗證只允許大寫/小寫/正確大小寫
此數據驗證是一項強大的功能，它還可以幫助用戶在單元格範圍內僅輸入大寫、小寫或正確的大小寫條目。 請按照以下步驟操作：
1. 選擇您只想輸入大寫、小寫或正確大小寫文本的單元格範圍。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將您需要的以下公式之一輸入 公式 文本框。
- =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2)) (only allow uppercase text)
=AND(EXACT(A2,LOWER(A2)),ISTEXT(A2)) (only allow lowercase text)
=AND(EXACT(A2,PROPER(A2)),ISTEXT(A2)) (only allow proper case text) - 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是您要使用的列的第一個單元格。
3. 現在，只有符合您創建的規則的條目才會被接受。
4.7 數據驗證允許存在/不存在於另一個列表中的值
允許將存在或不存在於另一個列表中的值輸入到一系列單元格中對我們大多數人來說可能是一個痛苦的問題。 事實上，您可以使用基於 COUNTIF 函數的簡單公式的數據驗證功能來處理它。
例如，我只想將 C2:C4 範圍內的值輸入到如下屏幕截圖所示的單元格範圍內，要解決此工作，請執行以下操作：
1. 選擇要應用數據驗證的單元格範圍。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將您需要的以下公式之一輸入 公式 文本框。
- =COUNTIF($C$2:$C$4,A2)>0 (only allow values exist in another column)
=COUNTIF($C$2:$C$4,A2)=0 (prevent values exist in another column) - 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是您要使用的列的第一個單元格， C2：C4 如果條目是其中之一，則是要阻止或允許的值列表。
3. 現在，只有符合您創建的規則的條目才能進入，其他條目將被阻止。
4.8 數據驗證強制只輸入電話號碼格式
輸入公司員工信息時，一欄需要輸入電話號碼，為保證輸入電話號碼快速準確，此時可以設置電話號碼數據校驗。 例如，我只希望允許在工作表中輸入這種格式的電話號碼 (123) 456-7890，本節將介紹解決此任務的兩個快速技巧。
使用數據驗證功能強制使用電話號碼格式
要只允許輸入特定的電話號碼格式，請執行以下操作：
1. 選擇要輸入特定電話號碼格式的單元格列表，然後右鍵單擊，選擇 單元格格式 從上下文菜單中，查看屏幕截圖：
2。 在 單元格格式 對話框中的 聯繫電話 標籤，選擇 習俗 在左邊 類別 列錶框，然後在Type文本框中輸入你需要的電話號碼格式，比如我就用這個 （###）###-#### 格式，請參見屏幕截圖：
3。 然後，點擊 OK 關閉對話框。
4. 格式化單元格後，繼續選擇單元格，然後打開 數據驗證 單擊對話框 數據 > 數據驗證 > 數據驗證, 在彈出的對話框中，在 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後輸入這個公式 = AND（ISNUMBER（A2），LEN（A2）= 10） 進入公式文本框。
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是要驗證電話號碼的列的第一個單元格。
5. 現在，當輸入 10 位號碼時，它會根據您的需要自動轉換為特定的電話號碼格式，請參見截圖：
 |
|
 |
備註：如果輸入的數字不是10位數字，會彈出一個警告信息框提醒您，請看截圖：
強制使用具有有用功能的電話號碼格式
Excel的Kutools“ 驗證電話號碼 功能還可以幫助您強制只輸入電話號碼格式，只需點擊幾下。
安裝後 Excel的Kutools，請這樣做：
1. 選擇只允許特定電話號碼的單元格列表，然後單擊 庫工具 > 防止打字 > 驗證電話號碼，請參見屏幕截圖：
2。 在 電話號碼 對話框中，選擇您需要的特定電話號碼格式，或者您可以通過單擊 加入 按鈕，請參見屏幕截圖：
3. 選擇或設置電話號碼格式後，單擊 OK，現在只能輸入特定格式的電話號碼，否則會彈出警告信息提醒，看截圖：
4.9 數據驗證強制只輸入電子郵件地址
假設您需要在工作表的一列中鍵入多個電子郵件地址，以防止輸入某些錯誤的電子郵件地址格式，通常您可以設置數據驗證規則，僅允許使用電子郵件地址格式。
僅使用數據驗證功能強制使用電子郵件地址格式
通過使用自定義公式的數據驗證功能，您可以創建一個規則以防止快速輸入無效的電子郵件地址，請執行以下操作：
1. 選擇您只想輸入電子郵件地址的單元格，然後單擊 數據 > 數據驗證 > 數據驗證.
2。 在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後輸入這個公式 = ISNUMBER（MATCH（“ *@*.?*”，A2,0）） 到 公式 文本框。
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是您要使用的列的第一個單元格。
3. 現在，如果輸入的文本不是電子郵件地址格式，則會彈出警告消息框提醒您，請看截圖：
使用方便的功能強制使用電子郵件地址格式
Excel的Kutools 支持一個驚人的功能—— 驗證電子郵件地址，使用此實用程序，您只需單擊一下即可防止無效的電子郵件地址。
安裝後 Excel的Kutools，請執行以下操作：
1. 選擇您只允許輸入電子郵件地址的單元格，然後單擊 庫工具 > 防止打字 > 驗證電子郵件地址。 看截圖：
2. 然後，只允許輸入電子郵件地址格式，否則會彈出警告消息框提醒您，請看截圖：
4.10 數據驗證強制只輸入 IP 地址
在本節中，我將介紹一些快速技巧來設置數據驗證以僅接受單元格範圍內的 IP 地址。
使用數據驗證功能強制使用 IP 地址格式
只允許在特定範圍的單元格中輸入 IP 地址，請執行以下操作：
1. 選擇您只想輸入 IP 地址的單元格，然後單擊 數據 > 數據驗證 > 數據驗證.
2。 在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
- 點擊 OK 按鈕關閉此對話框。
注意： 在上式中 A2 是您要使用的列的第一個單元格。
3. 現在，如果您在單元格中輸入無效的 IP 地址，則會彈出一個警告消息框提醒您，如下圖所示：
使用 VBA 代碼強制使用 IP 地址格式
這裡，下面的 VBA 代碼也可以幫助只允許輸入 IP 地址並限制其他輸入，請這樣做：
1。 右鍵單擊工作表標籤，然後單擊 查看代碼 從上下文菜單中，在開頭 Microsoft Visual Basic for Applications 窗口，將下面的VBA代碼複製到其中。
VBA 代碼：驗證單元格以僅接受 IP 地址
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
Exit Sub
Else
If Target = "" Then
Exit Sub
End If
xArrIp = Split(Target.Text, ".")
If UBound(xArrIp) <> 3 Then
GoTo EIP
Else
xIntIP1 = CInt(xArrIp(0))
xIntIP2 = CInt(xArrIp(1))
xIntIP3 = CInt(xArrIp(2))
xIntIP4 = CInt(xArrIp(3))
If (xIntIP1 < 1) Or (xIntIP1 > 255) _
Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
GoTo EIP
End If
End If
End If
Exit Sub
EIP:
MsgBox "Please enter correct IP address"
Target = ""
End Sub
 |
|
 |
備註：在上面的代碼中， A2：A10 是您只想接受 IP 地址的單元格範圍。
2. 然後保存並關閉此代碼，現在，只有正確的 IP 地址允許輸入到特定單元格中。
使用簡單的功能強制僅使用 IP 地址格式
如果你有 Excel的Kutools 安裝在您的工作簿中，其 驗證IP地址 功能也可以幫助您解決此任務。
安裝後 Excel的Kutools，請這樣做：
1. 選擇您只允許輸入 IP 地址的單元格，然後單擊 庫工具 > 防止打字 > 驗證IP地址。 看截圖：
2. 應用此功能後，現在只允許輸入IP地址，否則會彈出警告信息框提醒，看截圖：
4.11 數據驗證限制超過總值的值
假設您有一份每月費用報表，預算總額為 $18000，現在，您需要費用清單中的總金額不超過預設總額 $18000，如下圖所示。 在這種情況下，您可以使用 SUM 函數創建數據驗證規則，以防止值的總和超過預設的總數。
1. 選擇要限制值的單元格列表。
2。 然後點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- =SUM($B$2:$B$7)<=18000
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， B2：B7 是要限制條目的單元格範圍。
3. 現在，當輸入範圍 B2:B7 中的值時，如果值的總和小於 18000 美元，則驗證通過。 如果任何值使總金額超過 18000 美元，則會彈出警告消息框提醒您。
4.12 數據驗證限制基於另一個單元格的單元格輸入
當您想根據另一個單元格中的值限制單元格列表中的數據條目時，數據驗證功能也可以幫助解決此工作。 例如，如果單元格 C1 是文本“是”，則範圍 A2:A9 允許輸入任何內容，但是，如果單元格 C1 是另一個文本，則不允許在範圍 A2:A9 中輸入任何內容，如下圖所示：
 |
|
 |
要解決此問題，請執行以下操作：
1. 選擇要限制值的單元格列表。
2。 然後點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- =$C$1="Yes"
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， C1 是單元格包含您要使用的特定文本，並且文本“是” 是您想要限制單元格的文本，請根據您的需要進行更改。
3. 現在，如果單元格 C1 有文本“是”，則可以在 A2:A9 範圍內輸入任何內容，如果單元格 C1 有其他文本，您將無法輸入任何值，請參見下面的演示：
4.13 數據驗證只允許輸入工作日或週末
如果您只需要在單元格列表中輸入工作日（從星期一到星期五）或週末（星期六和星期日），則 數據驗證 也可以幫助您，請按照以下步驟操作：
1. 選擇要在其中輸入工作日或工作日的單元格列表。
2。 然後點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將以下公式之一輸入 公式 根據需要使用文本框。
- =WEEKDAY(A2,2)<6 (allow only weekdays)
=WEEKDAY(A2,2)>5 (allow only weekends) - 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是您要使用的列的第一個單元格。
3. 現在，您只能根據需要在特定單元格中輸入工作日或週末的日期。
4.14 數據驗證允許根據今天的日期輸入日期
有時，您可能只需要允許在單元格列表中輸入大於或小於今天的日期。 這 數據驗證 功能與 今天 功能可以幫你一個忙。 請這樣做：
1. 選擇您只想輸入未來日期（大於今天的日期）的單元格列表。
2。 然後點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- =A2>Today()
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是您要使用的列的第一個單元格。
3. 現在，單元格中只能輸入大於今天日期的日期，否則會彈出警告消息框提醒您，請看截圖：
提示：
1. 要允許輸入過去日期（日期小於今天），請將以下公式應用到數據驗證中：
=A2<Today()
2. 允許輸入特定日期範圍內的日期，例如未來 30 天內的日期，請在數據驗證中輸入以下公式：
=AND(A2>TODAY(),A2<=(TODAY()+30))
4.15 數據驗證允許根據當前時間輸入時間
例如，如果您想根據當前時間驗證數據，則只能在單元格中輸入當前時間之前或之後的時間。 您可以創建自己的數據驗證公式，請執行以下操作：
1. 選擇您只想輸入當前時間之前或之後的時間的單元格列表。
2。 然後點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 Time 來自 讓 下拉列表。
- 然後選擇 小於 只允許當前時間之前的時間，或 大於 允許您根據需要在當前時間之後的時間 數據 落下。
- 然後，在 時間結束 or 開始時間 框中，輸入以下公式：
- =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是您要使用的列的第一個單元格。
3. 現在，只能將當前時間之前或之後的時間輸入到特定單元格中。
4.16 數據驗證特定或當前年份的日期
要僅允許輸入特定年份或當前年份的日期，您可以使用基於 YEAR 函數的自定義公式進行數據驗證。
1. 選擇您只想輸入特定年份中的日期的單元格列表。
2。 然後點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 習俗 來自 讓 下拉列表。
- 然後，將下面的公式輸入到 公式 文本框。
- =YEAR(A2)=2020
- 點擊 OK 按鈕關閉此對話框。
備註：在以上公式中， A2 是您要使用的列的第一個單元格， 2020 是您要限制的年份編號。
3. 然後，只能輸入 2020 年的日期，否則，將彈出警告消息框，如下圖所示：
提示：
要僅允許當前年份中的日期，您可以將以下公式應用於數據驗證：
=YEAR(A2)=YEAR(TODAY())
4.17 數據驗證當前週或月的日期
如果您希望用戶能夠在特定單元格中輸入當前週或月的日期，本節將介紹一些用於在 Excel 中處理此任務的公式。
允許輸入當前週的日期
1. 選擇您只想輸入當前週日期的單元格列表。
2。 然後點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 日期 來自 讓 下拉列表。
- 然後選擇 之間 來自 數據 落下。
- 在 開始日期 文本框中，輸入以下公式： =今天（）-工作日（今天（），3）
- 在 結束日期 文本框中，輸入以下公式： =今天()-工作日(今天(),3)+6
- 最後點擊 OK 按鈕。
3. 然後，只能輸入當前週內的日期，其他日期將被阻止，如下圖所示：
允許輸入當月的日期
要只允許輸入當月的日期，請執行以下操作：
1. 選擇您只想輸入當月日期的單元格列表。
2。 然後點擊 數據 > 數據驗證 > 數據驗證，在彈出 數據驗證 對話框中的 設定 選項卡，請執行以下操作：
- 選擇 日期 來自 讓 下拉列表。
- 然後，從以下選項中進行選擇 數據 落下。
- 在 開始日期 文本框中，輸入以下公式： =日期（年（今天（）），月份（今天（）），1）
- 在 結束日期 文本框中，輸入以下公式： =日期(年(今天()),月(今天()),日(日期(年(今天()),月(今天())+1,1)-1))
- 最後點擊 OK 按鈕。
3. 從現在開始，只有當月的日期允許輸入到選定的單元格中。
5.如何在Excel中編輯數據驗證？
要編輯或更改現有的數據驗證規則，請按照以下步驟操作：
1. 選擇具有數據有效性規則的任一單元格。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證 去 數據驗證 對話框，在框中，根據需要編輯或更改規則，然後選中 將這些更改應用到具有相同設置的所有其他單元格 選項將此新規則應用於具有原始驗證標準的所有其他單元格。 看截圖：
3。 點擊 OK 保存更改。
6.如何在Excel中查找和選擇帶有數據驗證的單元格？
如果您在工作表中創建了多個數據驗證規則，現在，您需要查找並選擇應用數據驗證規則的單元格， 去特別 命令可以幫助您選擇各種數據驗證或特定類型的數據驗證。
1. 激活要查找的工作表並選擇具有數據驗證的單元格。
2。 然後，點擊 首頁 > 查找和選擇 > 去特別，請參見屏幕截圖：
3。 在 去特別 對話框中選擇 數據驗證 > 全部，請參見屏幕截圖：
4. 並且在當前工作表中一次選擇了所有具有數據驗證的單元格。
保養竅門: 如果您只想選擇一種特定類型的數據驗證，請首先選擇一個包含您要查找的特定數據驗證的單元格，然後轉到 去特別 對話框，然後選擇 數據驗證 > 同.
 |
|
 |
7.如何將數據驗證規則復製到其他單元格？
假設您已經為一個單元格列表創建了一個數據驗證規則，現在您需要將相同的數據驗證規則應用於其他單元格。 無需再次創建規則，您可以快速輕鬆地將現有規則復制並粘貼到其他單元格。
1. 單擊以選擇具有要使用的驗證規則的單元格，然後按 按Ctrl + C 複製它。
2. 然後，選擇要驗證的單元格，要選擇多個不相鄰的單元格，按住 按Ctrl 鍵，同時選擇單元格。
3. 然後，右鍵單擊選擇，選擇 選擇性粘貼 選項，請參見屏幕截圖：
4。 在 選擇性粘貼 對話框中選擇 驗證 選項，請參見屏幕截圖：
5。 點擊 OK 按鈕，現在驗證規則被複製到新單元格。
8.如何使用數據驗證在Excel中圈出無效條目？
有時，您可能需要為現有數據創建數據驗證規則，在這種情況下，單元格範圍內可能會出現一些無效數據。 如何檢查無效數據並修改它們？ 在 Excel 中，您可以使用 圈出無效數據 用紅色圓圈突出顯示無效數據的功能。
要圈出您需要的無效數據，您應該應用 數據驗證 功能來設置數據范圍的規則。 請按照以下步驟操作：
1. 選擇要圈出無效數據的數據范圍。
2。 然後，點擊 數據 > 數據驗證 > 數據驗證在 數據驗證 對話框，根據需要設置驗證規則，例如，這裡，我將驗證大於 500 的值，請參見截圖：
3。 然後，點擊 OK 關閉對話框。 設置好數據驗證規則後，點擊 數據 > 數據驗證 > 圈出無效數據，則所有小於 500 的無效值已用紅色橢圓圈起來。 看截圖：
 |
|
 |
筆記：
- 1.一旦您更正無效數據，紅圈將自動消失。
- 2。 此 圈出無效數據 功能最多只能圈出255個單元格。 保存當前工作簿時，所有紅色圓圈都將被刪除。
- 3. 這些圓圈不可打印。
- 4.您也可以通過單擊刪除紅色圓圈 數據 > 數據驗證 > 清除驗證圈.
9.如何去除Excel中的數據驗證？
要從一系列單元格、當前工作表或整個工作簿中刪除數據驗證規則，以下方法可以幫到您。
使用數據驗證功能刪除選定範圍內的數據驗證
1. 選擇要刪除的具有數據驗證的單元格。
2。 然後點擊 數據 > 數據驗證 > 數據驗證, 在彈出的對話框中，在 設定 標籤，點擊 全部清除 按鈕，請參見屏幕截圖：
3。 然後，點擊 OK 按鈕關閉此對話框。 並且立即刪除了應用於所選範圍的數據驗證規則。
保養竅門: 要從當前工作表中刪除數據驗證，請先選擇整個工作表，然後應用上述步驟。
使用方便的功能刪除選定範圍內的數據驗證
如果你有 Excel的Kutools，其 清除數據驗證限制 功能還可以幫助從選定範圍或整個工作表中刪除數據驗證規則。
安裝後 Excel的Kutools，請這樣做：
1. 選擇包含要刪除的數據驗證的單元格範圍或整個工作表。
2。 然後，點擊 庫工具 > 防止打字 > 清除數據驗證限制，請參見屏幕截圖：
3. 在彈出的提示框中，點擊 OK，並且已根據需要清除數據驗證規則。
使用 VBA 代碼從所有工作表中刪除數據驗證
要從整個工作簿中刪除數據驗證規則，如果工作表很多，上述方法會很耗時，這裡，下面的代碼可以幫助您快速處理此任務。
1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。
2。 然後，點擊 插入 > 模塊，然後將以下宏粘貼到 模塊 窗口。
VBA 代碼：刪除所有工作表中的數據驗證規則：
Sub RemoveDataValidation()
'Updateby Extendoffice
Dim xwsh As Worksheet
For Each xwsh In ActiveWorkbook.Worksheets
xwsh.Cells.Validation.Delete
Next xwsh
End Sub
3。 然後按 F5 鍵運行此代碼，所有數據驗證規則已立即從整個工作簿中刪除。
- 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
- 合併單元格/行/列 和保存數據； 拆分單元格內容； 合併重複的行和總和/平均值...防止細胞重複； 比較範圍...
- 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
- 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
- 收藏并快速插入公式，範圍，圖表和圖片； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
- 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
- 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
- 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
- 數據透視表分組依據 週號，週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
- 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
