儲存格

將資料驗證套用到儲存格

將資料驗證套用到儲存格

您可以使用資料驗證來限制資料類型或者使用者輸入到儲存格中的值。 其中一個最常見的資料驗證方式就是建立下拉式清單

您的瀏覽器不支援視訊。 請安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

下載我們的範例

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

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

  2. 選取 [資料 >資料驗證]。

    資料驗證

  3. 在 [設定] 索引標籤的 [允許] 下方,選取其中一個選項:

    • [整數]-將儲存格限制為只接受整個數位。

    • [十進位-] 可將儲存格限制為只接受小數位。

    • 清單 - 以從下拉式清單選擇資料。

    • 日期 - 以限制儲存格只能接受日期。

    • 時間-將儲存格限制為只接受時間。

    • 文字長度 - 以限制文字的長度。

    • 自訂 - 用於自訂公式。

  4.  在 [資料] 底下,選取其中一個條件:

    • 介於

    • 不介於

    • 等於

    • 不等於

    • 大於

    • 小於

    • 大於或等於

    • 小於或等於

  5. 在 [設定] 索引標籤的 [允許] 下方,選取其中一個選項:

  6. 根據您針對 [允許] 及 [資料] 所選取的選項,設定其他必要的值。 例如, 如果您選取 [介於], 請選取 [介於], 然後選取儲存格的 [最值] 和 [最大值: ] 值。

  7. 若您想要忽略空格,請選取 [忽略空白] 核取方塊。

  8. 如果您想要新增規則的標題和訊息, 請選取 [輸入訊息] 索引標籤, 然後輸入標題和輸入訊息。

  9. 選取 [當儲存格被選取時,顯示提示訊息] 核取方塊,以在使用者選取或暫留在選取的儲存格上方時顯示訊息。

  10. 選取 [確定]。

    現在,當使用者嘗試輸入無效的值時,就會出現顯示以下訊息的快顯訊息:「此值不符合此儲存格定義的資料驗證限制。」

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

限制資料輸入

  1. 選取您要限制資料輸入的儲存格。

  2. 在 [資料] 索引標籤上, 按一下 [資料驗證] >資料驗證]。

    附註: 如果無法使用 [驗證] 命令, 可能是因為工作表受到保護, 或活頁簿可能已共用。 如果活頁簿已共用或工作表受保護,則無法變更資料驗證設定。 如需有關活頁簿保護的詳細資訊,請參閱保護活頁簿

  3. 在 [允許] 方塊中, 選取您要允許的資料類型, 然後填入 [限制準則] 和 [值]。

    附註: 您在其中輸入限制值的方塊, 會根據您所選取的資料與限制準則來標示。 例如, 如果您選擇 [日期] 做為您的資料類型, 就可以在 [最小值] 和 [最大值] 方塊中輸入限制值, 標示為 [開始日期] 和 [結束日期]

提示使用者輸入有效的專案

當使用者在含有資料輸入需求的儲存格中按一下時, 您可以顯示一則訊息, 說明哪些資料有效。

  1. 選取您要提示使用者輸入有效資料的儲存格。

  2. 在 [資料] 索引標籤上, 按一下 [資料驗證] >資料驗證]。

    附註: 如果無法使用 [驗證] 命令, 可能是因為工作表受到保護, 或活頁簿可能已共用。 如果活頁簿已共用或工作表受保護,則無法變更資料驗證設定。 如需有關活頁簿保護的詳細資訊,請參閱保護活頁簿

  3. 在 [輸入訊息] 索引標籤上, 選取 [當儲存格被選取時, 顯示輸入訊息] 核取方塊。

  4. 在 [標題] 方塊中, 輸入郵件的標題。

  5. 在 [輸入訊息] 方塊中, 輸入您要顯示的訊息。

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

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

  1. 選取您要顯示錯誤訊息的儲存格。

  2. 在 [資料] 索引標籤上, 按一下 [資料驗證] >資料驗證]。

    附註: 如果無法使用 [驗證] 命令, 可能是因為工作表受到保護, 或活頁簿可能已共用。 如果活頁簿已共用或工作表受保護,則無法變更資料驗證設定。 如需有關活頁簿保護的詳細資訊,請參閱保護活頁簿

  3. 在 [錯誤提醒] 索引標籤的 [標題] 方塊中, 輸入您郵件的標題。

  4. 在 [錯誤訊息] 方塊中, 輸入輸入無效資料時要顯示的訊息。

  5. 執行下列其中一個動作:

    若要

    樣式 快顯功能表上, 選取

    需要使用者修正錯誤才能繼續進行

    停止

    警告使用者資料無效, 並要求他們選取[是]或 [], 以指出是否要繼續進行

    警告

    警告使用者資料無效, 但在解除警告訊息後允許他們繼續進行

    重要

限制資料輸入

  1. 選取您要限制資料輸入的儲存格。

  2. 在 [資料] 索引標籤的 [工具] 底下, 按一下 [驗證]。

    [資料] 索引標籤、[工具] 群組

    附註: 如果驗證命令無法使用,表示工作表可能受保護或活頁簿可能已共用。 如果活頁簿已共用或工作表受保護,則無法變更資料驗證設定。 如需有關活頁簿保護的詳細資訊,請參閱保護活頁簿

  3. 在 [允許] 快顯功能表上, 選取您要允許的資料類型。

  4. 在 [資料] 快顯功能表中, 選取所要的限制準則類型, 然後輸入 [限制值]。

    附註: 您在其中輸入限制值的方塊, 會根據您所選取的資料與限制準則來標示。 例如, 如果您選擇 [日期] 做為您的資料類型, 就可以在 [最小值] 和 [最大值] 方塊中輸入限制值, 標示為 [開始日期] 和 [結束日期]

提示使用者輸入有效的專案

當使用者在含有資料輸入需求的儲存格中按一下時, 您可以顯示一則訊息, 說明哪些資料有效。

  1. 選取您要提示使用者輸入有效資料的儲存格。

  2. 在 [資料] 索引標籤的 [工具] 底下, 按一下 [驗證]。

    [資料] 索引標籤、[工具] 群組

    附註: 如果驗證命令無法使用,表示工作表可能受保護或活頁簿可能已共用。 如果活頁簿已共用或工作表受保護,則無法變更資料驗證設定。 如需有關活頁簿保護的詳細資訊,請參閱保護活頁簿

  3. 在 [輸入訊息] 索引標籤上, 選取 [當儲存格被選取時, 顯示輸入訊息] 核取方塊。

  4. 在 [標題] 方塊中, 輸入郵件的標題。

  5. 在 [輸入訊息] 方塊中, 輸入您要顯示的訊息。

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

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

  1. 選取您要顯示錯誤訊息的儲存格。

  2. 在 [資料] 索引標籤的 [工具] 底下, 按一下 [驗證]。

    [資料] 索引標籤、[工具] 群組

    附註: 如果驗證命令無法使用,表示工作表可能受保護或活頁簿可能已共用。 如果活頁簿已共用或工作表受保護,則無法變更資料驗證設定。 如需有關活頁簿保護的詳細資訊,請參閱保護活頁簿

  3. 在 [錯誤提醒] 索引標籤的 [標題] 方塊中, 輸入您郵件的標題。

  4. 在 [錯誤訊息] 方塊中, 輸入輸入無效資料時要顯示的訊息。

  5. 執行下列其中一個動作:

    若要

    樣式 快顯功能表上, 選取

    需要使用者修正錯誤才能繼續進行

    停止

    警告使用者資料無效, 並要求他們選取[是]或 [], 以指出是否要繼續進行

    警告

    警告使用者資料無效, 但在解除警告訊息後允許他們繼續進行

    重要

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

附註: 本節前兩個步驟是用於新增任何類型的資料驗證。 步驟3-7 是專門用於建立下拉式清單。 

  1. 選取一個或多個要驗證的儲存格。

  2. 在 [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]。

  3. 在 [設定] 索引標籤上,選取 [儲存格內允許] 方塊中的 [清單]。

  4. 在 [來源] 方塊中,輸入您的清單值,並以逗號分隔。 例如, 輸入 [低]、[平均]、[高]。

  5. 確認已選取 [儲存格內的下拉式清單] 核取方塊。 否則您將無法看見儲存格旁的下拉式箭號。

  6. 若要指定如何處理空白 (null) 值,請選取或清除 [忽略空白] 核取方塊。

  7. 測試資料驗證,以確認運作正確無誤。 嘗試在儲存格中輸入有效和無效的資料,以確認您的設定確實如預期運作,訊息也如您預期顯示。

附註: 

  • 在您建立下拉式清單後,請確認它可按照您希望的方式運作。 例如, 您可能會想要查看儲存格寬度是否足夠, 以顯示所有專案。

  • 移除資料驗證-選取包含您要刪除之驗證的一個或多個儲存格, 然後移至資料 > 資料驗證, 然後在 [資料驗證] 對話方塊中, 按下 [全部清除] 按鈕, 然後按一下 [確定]

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

若要這麼做:

請遵循下列步驟:

將資料輸入限制為限制內的整數。

  1. 依照上述步驟1-2。

  2. 從 [允許] 清單中選取 [整數]。

  3. [資料] 方塊中選取所要的限制類型。 例如,若要設定上限及下限,請選取 [介於]

  4. 輸入允許的最小值、最大值或特定值。

    您也可以輸入會傳回數值的公式。

    例如,假設您要驗證在儲存格 F1 中的資料。 若要將扣除的下限設定為此儲存格中子項數的兩倍,請選取 [資料] 方塊中的 [大於或等於],然後在 [最小值] 方塊中輸入公式 =2*F1。

將資料輸入限制為限制內的實數。

  1. 依照上述步驟1-2。

  2. [儲存格內允許] 方塊中選取 [實數]

  3. [資料] 方塊中選取所要的限制類型。 例如,若要設定上限及下限,請選取 [介於]

  4. 輸入允許的最小值、最大值或特定值。

    您也可以輸入會傳回數值的公式。 例如,若要將佣金或紅利的上限設定為 E1 儲存格中銷售人員薪水的 6%,請選取 [資料] 方塊中的 [小於或等於],然後在 [最大值] 方塊中輸入公式 =E1*6%

    附註: 若要讓使用者輸入百分比 (例如 20%), 請選取 [允許] 方塊中的 [小數], 然後在 [資料] 方塊中選取您想要的限制類型, 輸入最小值、最大值或特定值做為小數 (例如2), 然後選取儲存格, 然後在 [常用] 索引標籤上的 [數值] 群組中, 按一下 [百分比樣式] 按鈕影像 , 即可將資料驗證儲存格顯示為百分比。

將資料輸入限制為時間範圍內的日期。

  1. 依照上述步驟1-2。

  2. [儲存格內允許] 方塊中選取 [日期]

  3. [資料] 方塊中選取所要的限制類型。 例如,若要允許某一天以後的日期,請選取 [大於]

  4. 輸入允許的開始日期、結束日期或特定日期。

    您也可以輸入會傳回日期的公式。 例如,若要設定介於今天日期與 3 天後日期之間的時間範圍,請在 [資料] 方塊中選取 [介於]、在 [開始日期] 方塊中輸入 =TODAY(),然後在 [結束日期] 方塊中輸入 =TODAY()+3

將資料輸入限制為時間範圍內的時間。

  1. 依照上述步驟1-2。

  2. [儲存格內允許] 方塊中選取 [時間]

  3. [資料] 方塊中選取所要的限制類型。 例如,若要允許當天某一個時間以前的時間,請選取 [小於]

  4. 輸入允許的開始時間、結束時間或特定時間。 如果要輸入特定時間,請使用 hh:mm 時間格式。

    例如,假設您將儲存格 E2 設定為開始時間 (上午 8:00),並將儲存格 F2 設定為結束時間 (下午 5:00),而您想要將會議時間限制為介於這段時間範圍之內,則請在 [資料] 方塊中選取 [介於]、在 [開始時間] 方塊中輸入 =E2,然後在 [結束時間] 方塊中輸入 =F2

將資料輸入限制為指定長度的文字。

  1. 依照上述步驟1-2。

  2. [儲存格內允許] 方塊中選取 [文字長度]

  3. [資料] 方塊中選取所要的限制類型。 例如,若要允許最多某個字元數,請選取 [小於或等於]

  4. 在此情況下, 我們想要將輸入限制為25個字元, 因此請在 [資料] 方塊中選取 [小於或等於], 然後在 [最大值] 方塊中輸入25

根據其他儲存格的內容計算允許的項目。

  1. 依照上述步驟1-2。

  2. 在 [儲存格內允許] 方塊中選取所要的資料類型。

  3. 在 [資料] 方塊中選取所要的限制類型。

  4. 在 [資料] 方塊下的一或多個方塊中,按一下要用來指定允許內容的儲存格。

    例如,若僅在結果不超過預算 (儲存格 E1) 時才允許科目輸入,請選取 [允許] > [整數, 資料]、[小於或等於],以及 [最大值] >= =E1

附註: 

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

  • 本文中的螢幕擷取畫面是在 Excel 2016中取得;但在 Excel 網頁版中的功能是一樣的。

若要確定下列狀況

請輸入此公式

含產品識別碼的儲存格 (C2) 一律以「ID-」的標準字首為開頭,且長度至少 10 (大於 9) 個字元。

= AND (LEFT (C2, 3) = "ID-", LEN (C2) >9)

範例 6:資料驗證中的公式

包含產品名稱的儲存格 (D2) 只包含文字。

=ISTEXT(D2)

範例 2:資料驗證中的公式

含某人生日的儲存格 (B6) 必須大於儲存格 B4 設定的年份數字。

=IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)

以最低年齡限制進入的資料驗證範例

儲存格範圍 A2:A10 中的所有資料都包含唯一值。

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

範例 4:資料驗證中的公式

附註: 您必須先針對儲存格 A2 輸入資料驗證公式,然後將 A2 複製到 A3:A10,以使 COUNTIF 的第二個引數與目前的儲存格相符。 也就是說,A2)=1 的部分將變更為 A3)=1, A4)=1,依此類推。

如需詳細資訊

確定輸入於儲存格 B4 的電子郵件地址包含 @ 符號。

= ISNUMBER (FIND ("@", B4))

確定電子郵件地址包含 @ 符號的資料驗證範例

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

另請參閱

有關資料驗證的詳細資訊

新增或移除下拉式清單中的項目

移除下拉式清單

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×