將資料驗證套用到儲存格

Applies To

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016

使用資料驗證來限制資料類型或使用者輸入儲存格的值，例如下拉式清單。

選取要建立規則的儲存格。

選取 [ 資料 >資料驗證]。
在 [設定] 索引標籤的 [允許] 下方，選取其中一個選項：
- 整數 - 以限制儲存格只能接受整數。
- 實數 - 以限制儲存格只能接受實數。
- 清單 - 以從下拉式清單選擇資料。
- 日期 - 以限制儲存格只能接受日期。
- 時間 - 以限制儲存格只能接受時間。
- 文字長度 - 以限制文字的長度。
- 自訂 - 用於自訂公式。
在 [資料] 底下，選取條件。
根據您針對 [允許] 及 [資料] 所選取的選項，設定其他必要的值。
選取 [輸入訊息] 索引標籤，然後自訂使用者輸入資料時將看到的訊息。
選取 [當儲存格被選取時，顯示提示訊息] 核取方塊，以在使用者選取或暫留在選取的儲存格上方時顯示訊息。
選取 [錯誤提醒] 索引標籤以自訂錯誤訊息，並選擇 [樣式]。
選取 [確定]。
現在，如果使用者嘗試輸入無效的值，將顯示 [ 錯誤警示 ] 以及您的自訂訊息。

下載我們的範例

下載包含本文所有資料驗證範例的範例活頁簿

如果您要建立需要使用者輸入資料的工作表，您可能會想要將輸入限制為特定的日期範圍或數字範圍，或確認只輸入正整數。 Excel 可以使用資料驗證，將資料輸入限制為特定儲存格，當儲存格被選取時提示使用者輸入有效的資料，並在使用者輸入無效資料時顯示錯誤訊息。

限制資料輸入

選取您要限制資料輸入的儲存格。
在 [資料 ] 索引標籤上，選取 [資料驗證]。

注意

如果驗證命令無法使用，表示工作表可能受保護或活頁簿可能已共用。如果活頁簿已共用或工作表受保護，則無法變更資料驗證設定。如需有關活頁簿保護的詳細資訊，請參閱保護活頁簿。
在 [允許] 方塊中，選取您要允許的資料類型，然後填入限制條件和值。

注意

您輸入限制值的方塊會根據您選擇的資料和限制準則進行標籤。例如，如果您選擇 [日期] 做為資料類型，則可以在標示為 [開始日期 ] 和 [ 結束日期] 的最小值和最大值方塊中輸入限制值。

提示使用者有效輸入

當使用者選取有資料輸入需求的儲存格時，您可以顯示訊息，說明哪些資料有效。

選取您要提示使用者有效資料輸入的儲存格。
在 [資料 ] 索引標籤上，選取 [資料驗證]。

注意

如果驗證命令無法使用，表示工作表可能受保護或活頁簿可能已共用。如果活頁簿已共用或工作表受保護，則無法變更資料驗證設定。如需有關活頁簿保護的詳細資訊，請參閱保護活頁簿。
在 [輸入訊息] 索引標籤上，選取 [當儲存格被選取時，顯示輸入訊息] 核取方塊。
在 [標題] 方塊中，輸入您郵件的標題。
在 [輸入訊息] 方塊中，輸入您要顯示的訊息。

輸入無效資料時顯示錯誤訊息

如果您有資料限制且使用者在儲存格中輸入無效資料，您可以顯示一則說明錯誤的訊息。

選取您要顯示錯誤訊息的儲存格。
在 [資料 ] 索引標籤上，選取 [資料驗證 ]。

注意

如果驗證命令無法使用，表示工作表可能受保護或活頁簿可能已共用。如果活頁簿已共用或工作表受保護，則無法變更資料驗證設定。如需活頁簿保護的詳細資訊，請參閱保護活頁簿。
在 [錯誤提醒] 索引標籤上，輸入 [標題]中的郵件標題。
在 [錯誤訊息] 方塊中，輸入您要在輸入無效資料時顯示的訊息。

請執行下列其中一項操作：

若要	在 [樣式] 快顯功能表中，選取
在繼續進行前，要求使用者修正錯誤	停止
警告使用者資料無效，並要求他們選取 [是] 或 [否]，以表示他們是否要繼續	警告
警告使用者資料無效，但允許使用者在關閉警告訊息後繼續進行	重要

在儲存格或範圍中新增資料驗證

注意

本節的前兩個步驟是適用於新增任何類型的資料驗證。步驟 3 至 7 則是適用於建立下拉式清單。

選取一個或多個要驗證的儲存格。
在 [資料 ] 索引標籤的 [ 資料工具 ] 群組中，選取 [ 資料驗證]。
在 [設定] 索引標籤上，選取 [儲存格內允許] 方塊中的 [清單]。
在 [來源] 方塊中，輸入您的清單值，並以逗號分隔。例如，輸入 [低、平均、高]。
確認已選取 [儲存格內的下拉式清單] 核取方塊。否則，您將不會看到儲存格旁邊的下拉箭號。
若要指定如何處理空白 (null) 值，請選取或清除 [忽略空白] 核取方塊。
測試資料驗證以確保其正常運作。嘗試在儲存格中輸入有效和無效的資料，以確認您的設定確實如預期運作，訊息也如您預期顯示。

注意

在您建立下拉式清單後，請確認它可按照您希望的方式運作。例如，您也許想要檢查儲存格是否夠寬以顯示所有的輸入。
移除資料驗證 - 選取包含您要刪除之驗證的儲存格，移至 [資料>資料驗證]，然後在 [資料驗證] 對話方塊中按 [全部清除]，然後選取 [確定]。

新增其他類型的資料驗證

下表列出其他類型的資料驗證並顯示將其加入至您工作表的方式。

若要這麼做：	請遵循下列步驟：
將資料輸入限制為限制內的整數。	執行上述步驟 1 至 2。從 [允許] 清單中選取 [整數]。在 [資料] 方塊中選取所要的限制類型。例如，若要設定上限及下限，請選取 [介於]。輸入允許的最小值、最大值或特定值。您也可以輸入會傳回數值的公式。例如，假設您要驗證在儲存格 F1 中的資料。若要將扣除額下限設定為該儲存格中子系數目的兩倍，請選取 [資料] 方塊中的 [大於或等於]，然後在 [最小值] 方塊中輸入公式 *=2F1**。
將資料輸入限制為限制內的實數。	執行上述步驟 1 至 2。在 [儲存格內允許] 方塊中選取 [實數]。在 [資料] 方塊中選取所要的限制類型。例如，若要設定上限及下限，請選取 [介於]。輸入允許的最小值、最大值或特定值。您也可以輸入會傳回數值的公式。例如，若要在儲存格 E1 中設定銷售人員薪資的 6% 的佣金和獎金上限，請在 [資料] 方塊中選取 [小於或等於]，然後在 [最大值] 方塊中輸入公式 *=E16%。若要讓使用者輸入百分比，例如 20%，請在 [允許] 方塊中選取 [小數]，在 [資料] 方塊中選取您想要的限制類型，輸入最小值、最大值或特定值為小數，例如 .2，然後選取儲存格並按一下 [常用] 索引標籤上 [數字] 群組中的 [百分比樣式**，將資料驗證儲存格顯示為百分比。
將資料輸入限制為時間範圍內的日期。	執行上述步驟 1 至 2。在 [允許] 方塊中，選取 [日期 ]。在 [資料] 方塊中選取所要的限制類型。例如，若要允許某一天以後的日期，請選取 [大於]。輸入允許的開始日期、結束日期或特定日期。您也可以輸入會傳回日期的公式。例如，若要設定介於今天日期與 3 天後日期之間的時間範圍，請在 [資料] 方塊中選取 [介於]、在 [開始日期] 方塊中輸入 =TODAY()，然後在 [結束日期] 方塊中輸入 =TODAY()+3。
將資料輸入限制為時間範圍內的時間。	執行上述步驟 1 至 2。在 [儲存格內允許] 方塊中選取 [時間]。在 [資料] 方塊中選取所要的限制類型。例如，若要允許當天某一個時間以前的時間，請選取 [小於]。輸入允許的開始時間、結束時間或特定時間。如果要輸入特定時間，請使用 hh:mm 時間格式。例如，假設您將儲存格 E2 設定為開始時間 (上午 8:00)，並將儲存格 F2 設定為結束時間 (下午 5:00)，而您想要將會議時間限制為介於這段時間範圍之內，則請在 [資料] 方塊中選取 [介於]、在 [開始時間] 方塊中輸入 =E2，然後在 [結束時間] 方塊中輸入 =F2。
將資料輸入限制為指定長度的文字。	執行上述步驟 1 至 2。在 [儲存格內允許] 方塊中選取 [文字長度]。在 [資料] 方塊中選取所要的限制類型。例如，若要允許最多某個字元數，請選取 [小於或等於]。在此案例中，我們想要將輸入限制為 25 個字元，因此請在 [資料] 方塊中選取 [小於或等於]，然後在 [最大值] 方塊中輸入 25。
根據其他儲存格的內容計算允許的項目。	執行上述步驟 1 至 2。在 [儲存格內允許] 方塊中選取所要的資料類型。在 [資料] 方塊中選取所要的限制類型。在 [資料 ] 方塊下方的方塊中，選取您要用來指定允許的儲存格。例如，若要只在結果不會超過儲存格 E1 中的預算時才允許帳戶項目，請選取 [允許>整數]、[資料]、小於或等於和 [最大值> = =E1]。

資料驗證公式的範例

注意

下列範例會在您寫下公式的位置使用 [自訂] 選項以設定您的條件。您不需要擔心 [資料] 方塊顯示的任何內容，當您使用 [自訂] 選項時該方塊是停用的。
本文中的螢幕擷取畫面是在 Excel 2016 中進行示範;但功能與 Excel 網頁版中的相同。

若要確定下列狀況	請輸入此公式
含產品識別碼的儲存格 (C2) 一律以「ID-」的標準字首為開頭，且長度至少 10 (大於 9) 個字元。	=AND (LEFT (C2,3) =“ID-”，LEN (C2) >9)
含產品名稱的儲存格 (D2) 只包含文字。	=ISTEXT (D2)
含某人生日的儲存格 (B6) 必須大於儲存格 B4 設定的年份數字。	*=IF (B6<= (TODAY () - (365B4) ) ，TRUE，FALSE)** 將項目限制為
儲存格範圍 A2:A10 中的所有資料都包含唯一值。	=COUNTIF ($A$2：$A$10，A2) =1 注意：您必須先針對儲存格 A2 輸入資料驗證公式，然後將 A2 複製到 A3：A10，以使 COUNTIF 的第二個引數與目前的儲存格相符。也就是說，A2)=1 的部分將變更為 A3)=1, A4)=1，依此類推。
確定輸入於儲存格 B4 的電子郵件地址包含 @ 符號。	=ISNUMBER (FIND (“@”，B4) )