Table of contents
×

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

您的瀏覽器不支援影片。

試試看!

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

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

    資料驗證

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

    • 整數 - 以限制儲存格只能接受整數。

    • 實數 - 以限制儲存格只能接受實數。

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

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

    • 時間 - 以限制儲存格只能接受時間。

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

    • 自訂 - 用於自訂公式。

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

  5. 根據您針對 [允許] 及 [資料] 所選取的選項,設定其他必要的值。

  6. 選取 [輸入訊息] 索引標籤,然後自訂使用者輸入資料時將看到的訊息。

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

  8. 選取 [錯誤提醒] 索引標籤以自訂錯誤訊息,並選擇 [樣式]。

  9. 選取 [確定]。

    現在,如果使用者嘗試輸入的值無效,您的自訂訊息就會出現 [錯誤提醒]。

下載我們的範例

下載本文內容中所有資料驗證範例的範例活頁簿。

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

限制資料輸入

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

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

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

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

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

提示使用者有效輸入

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

  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))

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

想要更多資訊嗎?

有關資料驗證的詳細資訊

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

移除下拉式清單

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×