將資料驗證套用到儲存格

下載我們的範例

您可以下載含有會在本文中找到的所有資料驗證範例之範例活頁簿。您可以照做或建立自己的資料驗證案例。

下載 Excel 資料驗證範例

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

附註: 本節的前三個步驟是適用於新增任何類型的資料驗證。步驟 4 至 8 則是適用於建立下拉式清單。

選取一個或多個要驗證的儲存格。
在 [資料] 索引標籤的 [資料工具] 群組中，按一下 [資料驗證]。
在 [設定] 索引標籤上，選取 [儲存格內允許] 方塊中的 [清單]。
在 [來源] 方塊中，輸入您的清單值，並以逗號分隔。例如：
1. 若要將答案限制為兩種選擇 (例如「您是否有小孩？」)，請輸入「是, 否」。
2. 若要將廠商的品質口碑限制為三種評等，請輸入「低, 普通, 高」。
  
  附註: 一般只建議將這些步驟用於不可能變更的清單項目。如果您有可能會變更清單，或者如果您需要在一段時間後新增或移除項目，那麼按照下列的最佳做法進行會比較理想。
  
  最佳做法：您也可以參照活頁簿中其他位置的儲存格範圍，來建立清單項目。最有效率的方式是建立您自己的清單，然後將它的格式設定為「Excel 表格」 (從 [常用] 索引標籤中選取 [樣式] > [格式化為表格] > 選擇最適合您的表格樣式)。接下來，選取表格的資料內文範圍，也就是只含有您的清單之表格部分而不是表格標題 (在此案例中是「部門」)，然後在欄 A 上方的 [名稱方塊] 中為它取一個有意義的名稱。
現在，您可新增剛定義的名稱並在前面加上等號 (=)，而不用在資料驗證 [來源] 方塊中輸入您的清單值。

使用表格最好的一點是，當您在清單中新增或移除項目時，您的資料驗證清單將會自動更新。

附註: 最好的做法是將您的清單放在一個單獨的工作表 (如果有需要可隱藏)，這樣就沒有任何人可以編輯。
確認已選取 [儲存格內的下拉式清單] 核取方塊。否則您將無法看見儲存格旁的下拉式箭號。
若要指定如何處理空白 (null) 值，請選取或清除 [忽略空白] 核取方塊。

附註: 如果您允許值是基於含有定義名稱的儲存格範圍，而且該範圍中的任何位置有空白儲存格，選取 [忽略空白] 核取方塊會允許在驗證的儲存格中輸入任何值。這在驗證公式參照的任何儲存格也成立：如果有任何參照的儲存格是空白的，選取 [忽略空白] 核取方塊會允許在驗證的儲存格中輸入任何值。
測試資料驗證，以確認運作正確無誤。嘗試在儲存格中輸入有效和無效的資料，以確認您的設定確實如預期運作，訊息也如您預期顯示。

附註:

在您建立下拉式清單後，請確認它可按照您希望的方式運作。例如，建議您確認欄位寬度是否足夠以顯示所有項目。
如果下拉式清單的項目清單是在另一個工作表上，而您不想要讓使用者看到這個驗證清單或是對它進行變更，則可以考慮隱藏或保護該工作表。如需如何保護工作表的詳細資訊，請參閱鎖定儲存格以進行保護。
移除資料驗證 - 選取儲存格或包含您要刪除的驗證儲存格，然後移至 [資料] > [資料驗證]，在 [資料驗證] 對話方塊中按 [全部清除] 按鈕，再按 [確定]。

新增其他類型的資料驗證

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

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

資料驗證公式的範例

附註: 下列範例會在您寫下公式的位置使用 [自訂] 選項以設定您的條件。您不需要擔心 [資料] 方塊顯示的任何內容，當您使用 [自訂] 選項時該方塊是停用的。

若要確定下列狀況	請輸入此公式
含產品識別碼的儲存格 (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 的電子郵件地址包含 @ 符號。	=ISUMBER(FIND("@",B4)

常見問題集

功能區上的 [資料驗證] 命令為何未啟用？此命令可能無法使用，因為：
- Microsoft Excel 表格可能連結到 SharePoint 網站：您無法在連結到 SharePoint 網站的 Excel 表格中新增資料驗證。若要新增資料驗證，您必須取消連結 Excel 表格，或是將 Excel 表格轉換成一個範圍。
- 您可能正在輸入資料：您在儲存格中輸入資料時，[資料] 索引標籤上的 [資料驗證] 命令就不會啟用。若要完成資料輸入，請按 ENTER 或 ESC。
- 工作表可能受保護或已共用：如果活頁簿正在共用或受到保護，就無法變更資料驗證設定。若要了解如何停止共用或保護活頁簿，請參閱保護活頁簿。
我是否能變更字型大小嗎？否。字型大小是固定的。變更顯示大小的唯一方式是在 Excel 視窗的右下角調整畫面縮放。不過，您可以使用 ActiveX 下拉式方塊。請參閱在工作表中新增清單方塊或下拉式方塊。
是否有任何方式能夠讓資料驗證隨著我輸入的內容而自動填滿或自動選取？否，但如果您使用 [ActiveX 下拉式方塊]，就的確會有此功能。
我是否可以在資料驗證清單中選取多個選項嗎？否，除非您使用 [ActiveX 下拉式方塊] 或 [清單方塊]。
我是否可以選取資料驗證清單中的項目並將它填入另一個清單嗎？是！這稱為從屬資料驗證。如需詳細資訊，請參閱建立從屬下拉式清單。
如何移除工作表中的所有資料驗證？您可以使用 [移至] > [特殊目標] 對話方塊。在 [常用] 索引標籤 > [編輯] > [尋找與選取] (或按鍵盤上的 F5 或 Ctrl+G)，然後 [特殊目標] > [資料驗證]，選取 [全部] (使用資料驗證以尋找全部的儲存格) 或 [相同時才做] (以尋找符合特定資料驗證設定的儲存格)。

接下來在出現的 [資料驗證] 對話方塊 ([資料] 索引標籤 > [資料驗證])，按 [全部清除] 按鈕，然後再按 [確定]。
我是否可以透過資料驗證強制他人在一或多個儲存格中輸入嗎？否，但您可以使用 VBA (Visual Basic for Applications) 檢查他人是否已在某些情況下輸入，例如 [儲存] 或 [關閉] 活頁簿前。如果他人尚未選取任何項目，您可以取消事件，直到已選取項目後再讓他人繼續進行。
如何根據資料驗證清單選擇來為儲存格設定顏色？您可以使用條件式格式設定。在此情況下，您會想要使用 [只格式化包含下列的儲存格] 選項。
如何驗證電子郵件地址？您可以使用 [自訂] > [公式] 方法，然後檢查項目中是否有 @ 符號存在。在此情況下，使用的公式為 =ISNUMBER(FIND(“@”,D2))。FIND 函數會尋找 @ 符號，且如果找到的話，就會傳回它在文字字串中的數值位置並允許項目。如果找不到，FIND 會傳回錯誤並避免項目。