Excel 資料驗證：在 Excel 中新增、使用、複製及移除資料驗證

在 Excel 中，「資料驗證」功能是一個強大的工具，可用來限制使用者在儲存格中輸入的內容。例如，您可以設定規則來限制文字長度、限制輸入特定格式、強制唯一值，或要求文字必須以特定字元開頭或結尾。這些功能有助於維護資料正確性並減少工作表中的錯誤。

本教學將說明如何在 Excel 中新增、使用及移除資料驗證。內容涵蓋基礎與進階操作，並提供詳細的步驟指引，協助您有效應用此功能於各項任務。

1.什麼是 Excel 的資料驗證？

「資料驗證」功能可協助您限制工作表中輸入的內容。通常，您可以為選定儲存格建立驗證規則，以防止或僅允許某類型的資料輸入。

資料驗證功能的一些基本用途：

1.「任何值」：不執行驗證，可輸入任何內容。 2.「整數」：僅允許輸入整數。 3.「小數」：允許輸入整數及小數。 4.「清單」：僅允許輸入或選擇預設清單中的值，這些值會以下拉列表顯示。 5.「日期」：僅允許輸入日期。 6.「時間」：僅允許輸入時間。 7.「文字長度」：僅允許輸入指定長度的文字。 8.「自訂」：建立自訂公式規則以驗證使用者輸入。

2. 如何在 Excel 中新增資料驗證？

在 Excel 工作表中，您可以依照下列步驟新增資料驗證：

1. 選取您要設定資料驗證的儲存格範圍，然後點擊「資料」>「資料驗證」>「資料驗證」，如圖所示：

2. 在「資料驗證」對話方塊的「設定」標籤下，請建立您自己的驗證規則。在條件欄位中，您可以輸入下列任一類型：

• 「值」：直接在條件欄位輸入數字；
• 「儲存格參照」：參照工作表或其他工作表中的儲存格；
• 「公式」：建立更複雜的條件公式。

舉例來說，若只允許輸入100 到1000之間的整數，請依下圖設定條件：

3. 設定條件後，您可以切換到「輸入訊息」或「錯誤提示」標籤，為驗證儲存格設定提示訊息或錯誤警示。（若不需設定，直接點擊「確定」完成。）

3.1）新增輸入訊息（可選）：

您可以建立一則訊息，當選取含有資料驗證的儲存格時顯示，以提醒使用者可輸入的內容。

切換到「輸入訊息」標籤，並執行下列操作：

勾選「選取儲存格時顯示輸入訊息」選項； 在對應欄位輸入標題與提醒訊息； 點擊「確定」關閉此對話方塊。

現在，當您選取已驗證的儲存格時，會顯示如下訊息方塊：

3.2）建立有意義的錯誤訊息（可選）：

除了輸入訊息外，您也可以在輸入無效資料時顯示錯誤提示。

切換到「資料驗證」對話方塊的「錯誤提示」標籤，請執行下列操作：

勾選「輸入無效資料後顯示錯誤提示」選項； 在「樣式」下拉清單中，選擇您需要的提示類型： 「停止（預設）」：此類型會阻止使用者輸入無效資料。 「警告」：警告使用者資料無效，但不阻止輸入。 「資訊」：僅通知使用者資料無效。 在對應欄位輸入標題與提示訊息； 點擊「確定」關閉對話方塊。

當輸入無效值時，會出現如下圖所示的提示方塊：

「停止」選項：點擊「重試」重新輸入，或點擊「取消」放棄輸入。

「警告」選項：點擊「是」接受無效輸入，「否」修改輸入，或「取消」放棄輸入。

「資訊」選項：點擊「確定」接受無效輸入，或「取消」放棄輸入。

注意：若未在「錯誤提示」中自訂訊息，則會顯示預設的「停止」提示方塊，如下圖所示：

3. 資料驗證的基本範例

使用資料驗證功能時，系統提供8 種內建選項供您設定資料驗證，例如：任何值、整數與小數、日期與時間、清單、文字長度及自訂公式。本節將說明如何在 Excel 中運用這些內建選項。

3.1 整數與小數的資料驗證

1. 選取您只允許輸入整數或小數的儲存格範圍，然後點擊「資料」>「資料驗證」>「資料驗證」。

2. 在「資料驗證」對話方塊的「設定」標籤下，執行下列操作：

• 在「允許」下拉選單中選擇「整數」或「小數」。
• 然後，在「資料」欄位選擇一個條件（本例選擇「介於」）。
• 提示：條件包含：介於、不介於、等於、不等於、大於、小於、大於或等於、小於或等於。
• 接著，輸入所需的「最小值」與「最大值」（例如0 到100）。
• 最後，點擊「確定」按鈕。

3. 現在，選取的儲存格僅允許輸入0 到100之間的整數。

3.2 日期與時間的資料驗證

若要限制只能輸入特定日期或時間，使用「資料驗證」功能即可，請依下列步驟操作：

1. 選取您只允許輸入特定日期或時間的儲存格範圍，然後點擊「資料」>「資料驗證」>「資料驗證」。

2. 在「資料驗證」對話方塊的「設定」標籤下，執行下列操作：

• 在「允許」下拉選單中選擇「日期」或「時間」。
• 然後，在「資料」欄位選擇一個條件（本例選擇「大於」）。
• 提示：條件包含：介於、不介於、等於、不等於、大於、小於、大於或等於、小於或等於。
• 接著，輸入所需的「開始日期」（例如大於8/20/2021）。
• 最後，點擊「確定」按鈕。

3. 現在，選取的儲存格僅允許輸入大於8/20/2021 的日期。

3.3文字長度的資料驗證

如果您需要限制儲存格可輸入的字元數，例如將內容限制為不超過10 個字元，這個「資料驗證」功能也能協助您。

1. 選取您要限制文字長度的儲存格範圍，然後點擊「資料」>「資料驗證」>「資料驗證」。

2. 在「資料驗證」對話方塊的「設定」標籤下，執行下列操作：

• 在「允許」下拉選單中選擇「文字長度」。
• 然後，在「資料」欄位選擇一個條件（本例選擇「小於」）。
• 提示：條件包含：介於、不介於、等於、不等於、大於、小於、大於或等於、小於或等於。
• 接著，輸入您要限制的「最大值」（如文字長度不超過10 個字元）。
• 最後，點擊「確定」按鈕。

3. 現在，選取的儲存格僅允許輸入少於10 個字元的文字字串。

3.4 資料驗證清單（下拉列表）

透過強大的「資料驗證」功能，您也可以快速建立儲存格下拉列表。請依下列步驟操作：

1. 選取要插入下拉列表的目標儲存格，然後點擊「資料」>「資料驗證」>「資料驗證」。

2. 在「資料驗證」對話方塊的「設定」標籤下，執行下列操作：

• 在「允許」下拉選單中選擇「清單」。
• 在「來源」文字方塊中，直接以逗號分隔輸入清單項目。例如，若要限制使用者只能選擇三個選項，請輸入 Not Started,In Progress,Completed，或選取包含這些值的儲存格作為下拉列表來源。
• 最後，點擊「確定」按鈕。

3. 現在，下拉列表已建立於儲存格中，如下圖所示：

點擊以瞭解更多下拉列表的詳細資訊…

4.進階自訂資料驗證規則

本節將介紹如何建立進階自訂資料驗證規則，解決各種需求，例如：建立僅允許數字或文字、唯一值、指定電話號碼、Email 地址等的驗證公式。

4.1 僅允許數字或文字的資料驗證

使用資料驗證功能僅允許輸入數字

若要在某區域僅允許輸入數字，請依下列步驟操作：

1. 選取您只允許輸入數字的儲存格區域。

2. 點擊「資料」>「資料驗證」>「資料驗證」。在出現的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。（「A2」為您要限制的區域第一個儲存格）

  =ISNUMBER(A2)

• 點擊「確定」關閉此對話方塊。

3. 從現在起，僅能在選取的儲存格中輸入數字。

注意：此「ISNUMBER」函數允許驗證儲存格內的任何數值，包括整數、小數、分數、日期與時間。

使用資料驗證功能僅允許輸入文字字串

若要僅允許輸入文字，可利用「ISTEXT」函數自訂公式搭配「資料驗證」功能，請依下列步驟操作：

1. 選取您只允許輸入文字字串的儲存格區域。

2. 點擊「資料」>「資料驗證」>「資料驗證」。在出現的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。（「A2」為您要限制的區域第一個儲存格）

  =ISTEXT(A2)

• 點擊「確定」關閉此對話方塊。

3. 現在，僅允許輸入文字格式資料於指定儲存格。

4.2 僅允許輸入英數字的資料驗證

有時您可能只想允許輸入英文字母與數字，並限制特殊字元（如 ~、%、$ 或空格）。本節將介紹幾種實用方法。

使用資料驗證功能僅允許輸入英數字

若要防止特殊字元並僅允許英數字，請在「資料驗證」功能中建立自訂公式，步驟如下：

1. 選取您只允許輸入英數字的儲存格區域。

2. 點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。

  =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要限制的區域第一個儲存格。

3. 現在，僅允許輸入英文字母與數字，輸入特殊字元時會被限制，如下圖所示：

利用強大功能僅允許輸入英數字

上述公式可能較難記憶與理解。這裡推薦「Kutools for Excel」的「限制輸入」功能，可大幅簡化操作。

1. 選取您只允許輸入英數字的儲存格區域。

2. 然後點擊「Kutools」>「限制輸入」>「限制輸入」，如圖所示：

3. 在彈出的「限制輸入」對話方塊中，選擇「禁止輸入特殊字符」選項，如圖所示：

4. 然後點擊「確定」按鈕，並在後續提示方塊中依序點擊「是」>「確定」完成操作。現在，選取儲存格僅允許輸入英文字母與數字，如圖所示：

4.3 僅允許文字以特定字元開頭或結尾的資料驗證

若某區域的所有值必須以特定字元或字串開頭或結尾，可利用 EXACT、LEFT、RIGHT 或 COUNTIF 函數自訂資料驗證公式。

僅用一個條件限制文字以特定字元開頭或結尾

例如，若您希望特定儲存格的文字輸入必須以「CN」開頭或結尾，請依下列步驟操作：

1. 選取僅允許以特定字元開頭或結尾的文字的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。
  僅允許輸入以 CN 開頭的文字：

  =EXACT(LEFT(A2,2),"CN")

  僅允許輸入以 CN 結尾的文字：

  =EXACT(RIGHT(A2,2),"CN")

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要限制的區域第一個儲存格，「2」為您指定的字元數，「CN」為您要求開頭或結尾的文字。

3. 從現在起，僅允許以指定字元開頭或結尾的文字字串輸入至選取儲存格，否則會出現警告提示，如下圖所示：

=COUNTIF(A2,"CN*")

=COUNTIF(A2,"*CN")

注意：星號 * 為萬用字元，可匹配一個或多個字元。

以多個條件（或邏輯）限制文字以特定字元開頭或結尾

例如，若您希望文字輸入必須以「CN」或「UK」開頭或結尾，如下圖所示，需使用加號（+）新增另一個 EXACT 條件。請依下列步驟操作：

1. 選取僅允許以多個條件開頭或結尾的文字的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。
  僅允許輸入以 CN 或 UK 開頭的文字：

  =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")

  僅允許輸入以 CN 或 UK 結尾的文字：

  =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要限制的區域第一個儲存格，「2」為您指定的字元數，「CN」與「UK」為您要求開頭或結尾的文字。

3. 現在，僅允許以指定字元開頭或結尾的文字字串輸入至選取儲存格。

=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")

=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

注意：星號 * 為萬用字元，可匹配一個或多個字元。

4.4 僅允許或禁止包含特定文字的資料驗證

本節將說明如何應用資料驗證，讓輸入值必須包含或必須不包含某個特定字串或多個字串。

必須包含一個或多個特定文字的輸入限制

必須包含一個特定文字的輸入限制

若要限制輸入值必須包含特定文字（如所有輸入值都需包含「KTE」），可利用 FIND 與 ISNUMBER 函數自訂資料驗證公式。請依下列步驟操作：

1. 選取僅允許包含特定文字的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列任一公式。
  區分大小寫：

  =ISNUMBER(FIND("KTE",A2)) 

  不區分大小寫：

  =ISNUMBER(SEARCH("KTE",A2))

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要限制的區域第一個儲存格，「KTE」為必須包含的文字字串。

3. 現在，若輸入值未包含所需文字，將會跳出警告提示方塊。

必須包含多個特定文字之一的輸入限制

上述公式僅適用於一個文字字串，若需允許多個字串中的任一者，請結合 SUMPRODUCT、FIND 與 ISNUMBER 函數建立公式，如下圖所示：

1. 選取僅允許包含多個項目之一的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，根據需求在「公式」文字方塊中輸入下列任一公式。
  區分大小寫：

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0

  不區分大小寫：

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0

• 然後點擊「確定」關閉對話方塊。

注意：上述公式中的「A2」為您要限制的區域第一個儲存格，「C2:C4」為允許包含任一值的清單。

3. 現在，僅允許輸入包含清單中任一值的內容。

必須不包含一個或多個特定文字的輸入限制

必須不包含一個特定文字的輸入限制

若要限制輸入值不得包含特定文字（如「KTE」），可利用 ISERROR 與 FIND 函數建立資料驗證規則。請依下列步驟操作：

1. 選取僅允許不包含特定文字的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列任一公式。
  區分大小寫：

  =ISERROR(FIND("KTE",A2))

  不區分大小寫：

  =ISERROR(SEARCH("KTE",A2))

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要限制的區域第一個儲存格，「KTE」為不得包含的文字字串。

3. 現在，包含特定文字的輸入將被禁止。

必須不包含多個特定文字之一的輸入限制

若要防止清單中任一文字字串被輸入，請依下列步驟操作：

1. 選取您要禁止部分文字輸入的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。
  區分大小寫：

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0

  不區分大小寫：

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0

• 然後點擊「確定」關閉對話方塊。

注意：上述公式中的「A2」為您要限制的區域第一個儲存格，「C2:C4」為禁止包含任一值的清單。

3. 從現在起，包含特定文字的輸入將被禁止。

4.5 僅允許唯一值的資料驗證

若要防止在某區域輸入重複資料，本節將介紹幾種快速解決方法。

使用資料驗證功能僅允許唯一值

通常可利用 COUNTIF 函數自訂公式搭配資料驗證功能，請依下列步驟操作：

1. 選取您只允許輸入唯一值的儲存格或列。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。

  =COUNTIF($A$2:$A$9,A2)=1

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「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」與「A:A」為您要防止重複的儲存格，請依需求修改。

2. 儲存並關閉程式碼。現在，當在 A1:A100 輸入重複值時，會跳出警告提示，如下圖所示：

利用便捷功能僅允許唯一值

若您有「Kutools for Excel」，可利用其「防止重複項」功能，僅需幾個步驟即可快速設定資料驗證，防止區域內重複輸入。

1. 選取您要防止重複、僅允許唯一值的儲存格區域。

2. 然後點擊「Kutools」>「限制輸入」>「防止重複項」，如圖所示：

3.會出現警告訊息，通知您套用此功能時資料驗證將被移除。點擊「是」，在後續提示方塊中點擊「確定」，如下圖所示：

4. 現在，當您在指定儲存格輸入重複資料時，會跳出提示方塊提醒該資料無效，如圖所示：

4.6 僅允許大寫／小寫／首字母大寫的資料驗證

資料驗證功能可協助使用者強制區域內僅輸入大寫、小寫或首字母大寫的內容。請依下列步驟操作：

1. 選取您只允許輸入大寫、小寫或首字母大寫的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列任一公式。
  僅允許輸入大寫文字：

  =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))

  僅允許輸入小寫文字

  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))

  僅允許輸入首字母大寫文字

  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要使用的欄位第一個儲存格。

3. 現在，僅允許符合您設定規則的內容輸入。

4.7 僅允許存在於另一清單或不存在於另一清單的值

根據值是否存在於另一清單來允許或禁止輸入，對許多使用者來說是個挑戰。其實，您可利用 COUNTIF 函數自訂公式搭配資料驗證功能來處理。

例如，若僅允許 C2:C4 區域中的值輸入至某區域，請依下列步驟操作：

1. 選取您要套用資料驗證的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊中，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列任一公式。
  僅允許輸入存在於另一列的值

  =COUNTIF($C$2:$C$4,A2)>0

  禁止輸入存在於另一列的值

  =COUNTIF($C$2:$C$4,A2)=0

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要使用的欄位第一個儲存格，「C2:C4」為您要允許或禁止的值清單。

3. 現在，僅允許符合規則的內容輸入，其他將被禁止。

4.8 僅允許輸入電話號碼格式的資料驗證

當您輸入公司員工資訊時，某欄需輸入電話號碼。為確保快速且正確輸入電話號碼，您可針對電話號碼設定資料驗證。例如，僅允許輸入 (123)456-7890 格式的電話號碼，本節將介紹兩種快速解決方法。

使用資料驗證功能強制僅允許電話號碼格式

若要僅允許輸入特定電話號碼格式，請依下列步驟操作：

1. 選取您要輸入特定電話號碼格式的儲存格，然後按右鍵，從選單選擇「設定儲存格格式」，如圖所示：

2. 在「設定儲存格格式」對話方塊的「數值」標籤下，於左側「分類」清單選擇「自訂」，並在類型欄位輸入所需電話號碼格式，例如「(###) ###-####」，如圖所示：

3. 點擊「確定」關閉對話方塊。

4. 格式化儲存格後，再次選取這些儲存格，點擊「資料」>「資料驗證」>「資料驗證」開啟對話方塊，於「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。

  =AND(ISNUMBER(A2),LEN(A2)=10)

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要驗證電話號碼的欄位第一個儲存格。

5. 現在，輸入10 位數字時，會自動轉換為指定電話號碼格式，如圖所示：

注意：若輸入的數字不是10 位數，會跳出警告訊息方塊提醒您，如圖所示：

利用實用功能強制僅允許電話號碼格式

「Kutools for Excel」的「僅能輸入電話號碼」功能也能協助您僅允許輸入電話號碼格式，只需幾個步驟即可完成。

1. 選取僅允許特定電話號碼的儲存格，然後點擊「Kutools」>「限制輸入」>「僅能輸入電話號碼」，如圖所示：

2. 在「電話號碼」對話方塊中，選擇所需電話號碼格式，或點擊「添加」自訂格式，如圖所示：

3. 選擇或設定電話號碼格式後，點擊「確定」，現在僅允許輸入指定格式的電話號碼，否則會跳出警告訊息，如圖所示：

4.9 僅允許輸入 Email 地址格式的資料驗證

假設您需要在某欄輸入多個 Email 地址，為防止輸入錯誤格式，可設定僅允許 Email 格式的資料驗證規則。

使用資料驗證功能強制僅允許 Email 地址格式

利用資料驗證功能搭配自訂公式，可快速建立規則，防止輸入無效 Email 地址。請依下列步驟操作：

1. 選取僅允許輸入 Email 地址的儲存格，然後點擊「資料」>「資料驗證」>「資料驗證」。

2. 在彈出的「資料驗證」對話方塊的「設定」標籤下，執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式：

  =ISNUMBER(MATCH("*@*.?*",A2,0))

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要使用的欄位第一個儲存格。

3. 現在，若輸入內容不符合 Email 格式，會跳出警告訊息方塊提醒您，如圖所示：

利用便捷功能強制僅允許 Email 地址格式

「Kutools for Excel」提供強大功能——「僅能輸入郵箱」，只需一鍵即可防止無效 Email 地址輸入。

1. 選取僅允許輸入 Email 地址的儲存格，然後點擊「Kutools」>「限制輸入」>「僅能輸入郵箱」。如圖所示：

2. 套用此功能後，僅允許輸入 Email 格式，否則會跳出警告訊息，如圖所示：

4.10 僅允許輸入 IP 地址格式的資料驗證

本節將介紹幾個快速技巧，設定資料驗證僅允許在區域內輸入 IP 地址。

使用資料驗證功能強制僅允許 IP 地址格式

若要僅允許在特定區域輸入 IP 地址，請依下列步驟操作：

1. 選取僅允許輸入 IP 地址的儲存格，然後點擊「資料」>「資料驗證」>「資料驗證」。

2. 在彈出的「資料驗證」對話方塊的「設定」標籤下，執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。

  =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「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 地址格式

若您的工作簿已安裝「Kutools for Excel」，其「僅能輸入 IP 地址」功能也可協助您完成此任務。

1. 選取僅允許輸入 IP 地址的儲存格，然後點擊「Kutools」>「限制輸入」>「僅能輸入 IP 地址」。如圖所示：

2. 套用此功能後，僅允許輸入 IP 地址，否則會跳出警告訊息，如圖所示：

4.11 限制超過總值的資料驗證

假設您有一份每月支出報表，預算總額為 $18000。您需確保支出清單總額不超過此預設值，如下圖所示。此時可利用 SUM 函數建立資料驗證規則，防止總和超過預算。

1. 選取您要限制數值的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊的「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。

  =SUM($B$2:$B$7)<=18000

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「B2:B7」為您要限制輸入的儲存格範圍。

3. 現在，當在 B2:B7 區域輸入數值時，若總和小於 $18000，則驗證通過。若任何數值導致總和超過 $18000，會跳出警告訊息提醒您。

4.12 根據另一儲存格限制輸入的資料驗證

當您想根據另一儲存格的值限制一系列儲存格的輸入時，也可利用資料驗證功能解決。例如，若 C1 儲存格為「Yes」，則 A2:A9 區域允許任何輸入；若 C1 含其他文字，則 A2:A9 區域將被限制，如下圖所示：

請依下列步驟操作：

1. 選取您要限制數值的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊的「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。

  =$C$1="Yes"

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「C1」為您要參照的儲存格，「Yes」為您要根據的文字，請依需求修改。

3. 現在，若 C1 為「Yes」，A2:A9 區域可輸入任何內容；若 C1 為其他文字，則無法輸入任何值，如下圖所示：

4.13 僅允許輸入工作日或週末的資料驗證

若您僅需允許在一系列儲存格輸入工作日（星期一至星期五）或週末（星期六、星期日），「資料驗證」功能也能協助您，請依下列步驟操作：

1. 選取您要輸入工作日或週末的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊的「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，根據需求在「公式」文字方塊中輸入下列任一公式。
  僅允許輸入工作日

  =WEEKDAY(A2,2)<6

  僅允許輸入週末

  =WEEKDAY(A2,2)>5

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要使用的欄位第一個儲存格。

3. 現在，根據您的選擇，僅允許在指定儲存格輸入工作日或週末日期。

4.14 根據今天日期限制輸入日期的資料驗證

有時您可能需要僅允許輸入大於或小於今天的日期。「資料驗證」功能搭配「TODAY」函數可協助您，請依下列步驟操作：

1. 選取僅允許輸入未來日期（大於今天）的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊的「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。

  =A2>Today()

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要使用的欄位第一個儲存格。

3. 現在，僅允許輸入晚於今天的日期，否則會跳出警告訊息，如圖所示：

=A2<Today()

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 根據目前時間限制輸入時間的資料驗證

若您想根據目前時間驗證資料，例如僅允許輸入早於或晚於目前時間的時間，可自訂資料驗證公式，請依下列步驟操作：

1. 選取僅允許輸入早於或晚於目前時間的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊的「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「時間」。
• 然後在「資料」下拉選單選擇「小於」以允許早於目前時間，或選擇「大於」以允許晚於目前時間。
• 然後，在「結束時間」或「開始時間」欄位輸入下列公式：

  =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要使用的欄位第一個儲存格。

3. 現在，僅允許輸入早於或晚於目前時間的時間。

4.16 限制輸入特定年份或當前年份日期的資料驗證

若要僅允許輸入某年份或當前年份的日期，可利用 YEAR 函數自訂資料驗證公式。

1. 選取僅允許輸入某年份日期的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊的「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「自訂」。
• 然後，在「公式」文字方塊中輸入下列公式。

  =YEAR(A2)=2020

• 點擊「確定」關閉此對話方塊。

注意：上述公式中的「A2」為您要使用的欄位第一個儲存格，「2020」為您要限制的年份。

3. 現在，僅允許輸入2020 年的日期，否則會跳出警告訊息，如下圖所示：

=YEAR(A2)=YEAR(TODAY())

4.17 限制輸入本週或本月日期的資料驗證

若您希望使用者僅能在特定儲存格輸入本週或本月的日期，本節將介紹相關公式協助您完成此任務。

允許輸入本週日期

1. 選取僅允許輸入本週日期的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊的「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「日期」。
• 然後在「資料」下拉選單選擇「介於」。
• 在「開始日期」文字方塊中輸入下列公式：

  =TODAY()-WEEKDAY(TODAY(),3)

• 在「結束日期」文字方塊中輸入下列公式：

  =TODAY()-WEEKDAY(TODAY(),3)+6

• 最後，點擊「確定」按鈕。

3. 現在，僅允許輸入本週日期，其他日期將被禁止，如下圖所示：

允許輸入本月日期

若僅允許輸入本月日期，請依下列步驟操作：

1. 選取僅允許輸入本月日期的儲存格區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的「資料驗證」對話方塊的「設定」標籤下執行下列操作：

• 在「允許」下拉選單中選擇「日期」。
• 然後在「資料」下拉選單選擇「介於」。
• 在「開始日期」文字方塊中輸入下列公式：

  =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

• 在「結束日期」文字方塊中輸入下列公式：

  =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))

• 最後，點擊「確定」按鈕。

3. 從現在起，僅允許輸入本月日期至選取儲存格。

5. 如何編輯 Excel 中的資料驗證？

若要編輯或變更現有資料驗證規則，請依下列步驟操作：

1. 選取任一含有資料驗證規則的儲存格。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」進入「資料驗證」對話方塊，於對話方塊中依需求編輯或變更規則，並勾選「將這些變更套用到所有具有相同設定的儲存格」以將新規則套用至所有原本驗證條件相同的儲存格。如圖所示：

3. 點擊「確定」儲存變更。

6. 如何在 Excel 中尋找並選取有資料驗證的儲存格？

若您在工作表中建立了多個資料驗證規則，現在需要尋找並選取已套用資料驗證規則的儲存格，可利用「定位條件」指令選取所有類型或特定類型的資料驗證。

1. 啟用您要尋找並選取資料驗證儲存格的工作表。

2. 然後點擊「常用」>「尋找與選取」>「定位條件」，如圖所示：

3. 在「定位條件」對話方塊中，選擇「資料驗證」>「全部」，如圖所示：

4. 現在，所有含有資料驗證的儲存格已在當前工作表中被選取。

提示：若要選取特定類型的資料驗證，請先選取含有該資料驗證的儲存格，再進入「定位條件」對話方塊，選擇「資料驗證」>「相同」。

7. 如何將資料驗證規則複製到其他儲存格？

假設您已為一組儲存格建立資料驗證規則，現在需要將相同規則套用至其他儲存格。您可快速複製並貼上現有規則，而無需重新建立。

1. 點選含有所需驗證規則的儲存格，然後按「Ctrl + C」複製。

2. 然後選取您要驗證的儲存格，若需選取多個不連續儲存格，請按住「Ctrl」鍵同時選取。

3. 然後右鍵點擊選取區域，選擇「選擇性貼上」選項，如圖所示：

4. 在「選擇性貼上」對話方塊中，選擇「驗證」選項，如圖所示：

5. 點擊「確定」按鈕，現在驗證規則已複製至新儲存格。

8. 如何利用資料驗證圈選 Excel 中的無效輸入？

有時您可能需針對現有資料建立資料驗證規則，此時區域內可能出現無效資料。如何檢查並修改這些無效資料？在 Excel 中，您可利用「圈選無效資料」功能，以紅色圓圈標示無效資料。

若要圈選所需的無效資料，請先利用「資料驗證」功能為資料區域設定規則。請依下列步驟操作：

1. 選取您要圈選無效資料的資料區域。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在「資料驗證」對話方塊中設定所需驗證規則，例如本例驗證大於500 的值，如圖所示：

3. 然後點擊「確定」關閉對話方塊。設定好資料驗證規則後，點擊「資料」>「資料驗證」>「圈選無效資料」，所有小於500 的無效值將以紅色橢圓圈出。如圖所示：

注意：

• 1.只要您修正無效資料，紅色圓圈會自動消失。
• 2. 此「圈選無效資料」功能最多僅能圈選255 個儲存格。儲存目前工作簿時，所有紅色圓圈將被移除。
• 3.這些圓圈不會列印出來。
• 4. 您也可點擊「資料」>「資料驗證」>「清除驗證圈」移除紅色圓圈。

9. 如何在 Excel 中移除資料驗證？

若要從區域、當前工作表或整個工作簿移除資料驗證規則，請參考以下方法。

使用資料驗證功能移除選擇區域的資料驗證

1. 選取您要移除資料驗證的儲存格。

2. 然後點擊「資料」>「資料驗證」>「資料驗證」，在彈出的對話方塊的「設定」標籤下，點擊「清空」按鈕，如圖所示：

3. 然後點擊「確定」關閉此對話方塊，選取區域的資料驗證規則即被移除。

提示：若要移除當前工作表的資料驗證，請先全選整張工作表，再依上述步驟操作。

利用便捷功能移除選擇區域的資料驗證

若您有「Kutools for Excel」，其「清除資料驗證限制」功能也可協助您移除選取區域或整張工作表的資料驗證規則。

1. 選取包含資料驗證的儲存格區域或整張工作表。

2. 然後點擊「Kutools」>「限制輸入」>「清除資料驗證限制」，如圖所示：

3. 在出現的提示方塊中點擊「確定」，資料驗證規則即被清除。

使用 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」執行程式碼，所有資料驗證規則將立即從整個工作簿刪除。