儲存格

將資料驗證套用到儲存格

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

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

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

  1. 取 [數據 >數據驗證]

    資料驗證

  2. 在 [ 設定] 索引標籤的 [ 允許] 底下,選取一個選項:

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

    • 十進位 - 以限制儲存格只能接受小數。

    • 清單 - 從下拉式清單中挑選數據。

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

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

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

    • 自訂 – 用於自定義公式。

  3. 在 [ 數據] 底下,選取條件。

  4. 根據您選擇的 [ 允許 ] 和 [數據] 設定其他必要

  5. 選取 [ 輸入訊息] 索引標籤,並自定義使用者在輸入數據時會看到的訊息。

  6. 選取 [ 選取單元格時顯示提示訊 息] 複選框,以在用戶選取或暫留在所選單元格 (的) 上時顯示訊息。

  7. 選取 [錯誤通知] 索 引標籤以自訂錯誤訊息,並選擇 [樣 式]

  8. 選取 [確定]

    現在,如果使用者嘗試輸入無效的值,就會在您的自定義訊息中顯示 錯誤通知

下載我們的範例

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

如果您要建立需要使用者輸入資料的工作表,您可能會想要將輸入限制為特定的日期範圍或數字範圍,或確認只輸入正整數。 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 個字元,然後在 [最大值] 方塊中輸入 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))

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

提示: 如果您是小型企業擁有者,且想深入了解如何設定 Microsoft 365,請造訪小型企業說明與學習

想要更多資訊嗎?

建立下拉式清單

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

有關資料驗證的詳細資訊

Need more help?

Want more options?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。