附註: 此為資料驗證中的進階主題。 如需資料驗證的簡介,以及如何驗證儲存格或範圍的方式,請參閱在儲存格或範圍中新增資料驗證

您可以使用資料驗證來限制資料類型或使用者輸入到儲存格中的值。 例如,您可以使用資料驗證,根據活頁簿他處的值來計算儲存格中允許的最大值。 在以下範例中,使用者輸入 abc,而這並非該儲存格可接受的值。

無效提示訊息範例

使用者選取儲存格時,就會顯示提示訊息

資料驗證發揮作用的時機

當您要與其他人共用活頁簿,且希望輸入的資料要非常準確且一致時,資料驗證就是一項寶貴的功能。 除此之外,您也可以在下列情況中使用資料驗證:

  • 將輸入項目限制為清單中預先定義的值:例如,您可以將使用者的部門選項限制為「會計」、「薪資」、「人力資源」等等。

  • 限制指定範圍外的值:例如,您可以指定輸入員工年度績效成長時的最大百分比 (比方說 3%),或者只允許介於 1 到 100 之間的整數。

  • 限制特定時間範圍外的日期:例如,您可以防止某人在員工休假要求中選取今天以前的日期。

  • 限制特定時間範圍外的時間:例如,您可以將會議排程指定在上午 8:00 到下午 5:00 之間。

  • 限制文字字元數:例如,您可以將一個儲存格中允許的文字限制為 10 或 10 個以下的字元。

  • 根據其他儲存格中的公式或值驗證資料:例如,您可以使用資料驗證,來根據預測的總薪資值設定佣金和紅利的上限。 如果使用者在儲存格中輸入超過限制的金額,就會看到錯誤訊息。

資料驗證輸入與錯誤訊息

您可以選擇在使用者選取儲存格時顯示提示訊息。 一般會使用提示訊息,以提供使用者有關您希望在儲存格中輸入的資料類型之指引。 這種訊息會顯示在儲存格旁邊。 您可以視需要移動訊息,而訊息會持續顯示直到您移至另一個儲存格或按 Esc 為止。

針對儲存格顯示的提示訊息

請在第二個資料驗證索引標籤中設定您的提示訊息。

在 [資料驗證] 對話方塊中的 [提示訊息] 設定

當使用者習慣了您的提示訊息後,您就能取消選取 [當儲存格被選取時,顯示提示訊息] 選項。

您也可以僅在使用者輸入無效資料後顯示錯誤提醒。

警告訊息表示無效的資料

您有三種類型的錯誤提醒可以選擇:

圖示

類型

用途

[停止] 圖示

停止

防止使用者在儲存格中輸入無效資料。

[停止] 通知訊息有兩個選項:[重試] 或 [取消]。

[警告] 圖示

警告

警告使用者其輸入的資料無效,但不阻止使用者輸入資料。

出現 [警告] 通知訊息時,使用者可以按一下 [是] 接受無效的輸入、按一下 [否] 編輯無效的輸入,或按一下 [取消] 移除無效的輸入。

[資訊] 圖示

資訊

通知使用者其輸入的資料無效,但不阻止使用者輸入資料。 這種類型的錯誤提醒是最有彈性的。

出現 [資訊] 通知訊息時,使用者可以按一下 [確定] 接受無效值,或按一下 [取消] 拒絕無效值。

資料驗證使用祕訣

使用這些祕訣和訣竅在 Excel 中使用資料驗證。

附註: 如果想要對 Excel Services 或 Excel Web App 中的活頁簿使用資料驗證,您需要先在 Excel 電腦版中建立資料驗證。

  • 下拉式清單的寬度是根據含有資料驗證之儲存格的寬度來決定。 您可能需要調整該儲存格的寬度,以避免寬度超過下拉式清單的有效輸入遭到截斷。

  • 如果您打算保護工作表或活頁簿,請在指定任何驗證設定完畢後,再進行保護。 在您保護工作表之前,請先確認已解除鎖定所有已驗證的儲存格。 否則,使用者將無法在儲存格中輸入任何資料。 請參閱保護工作表

  • 如果您打算共用活頁簿,請在您指定資料驗證和保護設定完畢後,再進行共用。 在您共用活頁簿之後,除非先停止共用,否則無法變更驗證設定。

  • 您可以將資料驗證套用到已輸入資料的儲存格。 不過,Excel 不會自動通知您現有的儲存格包含不正確的資料。 在此案例中,您可以透過指引 Excel 在工作表上圈起無效的資料以醒目提示。 識別無效的資料後,您可以再次隱藏該圓圈。 如果您更正無效的項目,圓圈會自動消失。

    圓圈表示無效的資料

    若要套用圓圈,選取您想要評估的儲存格,並移至 [資料] > [資料工具] > [資料驗證] > [圈選錯誤資料]。

    功能區上的圈選錯誤資料
  • 若要迅速移除儲存格的資料驗證,請選取儲存格,然後移至 [資料] > [資料工具] > [資料驗證] > [設定] > [全部清除]。

  • 若要在工作表上找出有資料驗證的儲存格,請在 [常用] 索引標籤上,按一下 [編輯] 群組中的 [尋找與選取],然後按一下 [資料驗證]。 找到有資料驗證的儲存格後,您可以變更、複製或移除驗證設定。

  • 您建立下拉式清單時,可以使用 [定義名稱] 命令 ([公式] 索引標籤,[已定義之名稱] 群組),定義包含清單之範圍的名稱。 您在其他工作表上建立清單之後,可以隱藏內含清單的工作表,然後保護活頁簿,讓使用者無法存取該清單。

  • 如果您變更儲存格的驗證設定,您可以將變更自動套用到有相同設定的所有其他儲存格。 若要這麼做,在 [設定] 索引標籤,請選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

  • 如果資料驗證未運作,請確認:

    • 使用者未複製或填滿資料:資料驗證的設計是僅在使用者直接於儲存格中輸入資料時,才顯示訊息並防止無效的輸入。 若資料是以複製或填滿方式輸入,就不會出現訊息。 若要防止使用者以拖放儲存格的方式複製並填滿資料,請移至 [檔案] > [選項] > [進階] > [編輯選項] > 清除 [啟用填滿控點與儲存格拖放功能] 核取方塊,然後保護工作表。

    • 已關閉手動重算功能:如果已開啟手動重算功能,未計算的儲存格可能會造成資料無法正確驗證。 若要關閉手動重算功能,請移至 [公式] 索引標籤 > [計算] 群組 > [計算選項] > 按一下 [自動]。

    • 公式沒有錯誤:確認已驗證儲存格中的公式不會造成錯誤,例如 #REF!#DIV/0!。 Excel 會忽略資料驗證,直到您修正錯誤為止。

    • 公式中參照的儲存格正確:如果參照的儲存格變更,以致已驗證儲存格中的公式計算出無效的結果,儲存格的驗證訊息就不會出現。

    • Excel 表格可能連結到 SharePoint 網站:您無法在連結到 SharePoint 網站的 Excel 表格中新增資料驗證。 若要新增資料驗證,您必須取消連結 Excel 表格,或是將 Excel 表格轉換成一個範圍。

    • 您可能正在輸入資料:您在儲存格中輸入資料時,無法使用 [資料驗證] 命令。 若要完成資料輸入,請按 Enter 或 ESC 以關閉。

    • 工作表可能受保護或已共用:如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。 您必須先取消共用或取消保護活頁簿。

如何更新或移除繼承之活頁簿中的資料驗證

如果您繼承了擁有資料驗證的活頁簿,除非工作表受到保護,否則您可以修改或移除。 如果工作表受到密碼保護,且您不知道密碼,建議您先嘗試連絡先前的擁有者來協助您取消工作表的保護,因為 Excel 無法復原未知或遺失的密碼。 您也可以將資料複製至另一張工作表,然後移除資料驗證。

如果您在嘗試輸入或變更儲存格中的資料時看到資料驗證提醒,但是不確定可輸入什麼資料,請連絡活頁簿的擁有者。

對特定函數有任何問題嗎?

在 Excel 社群論壇張貼問題

協助我們改進 Excel

您是否有任何關於下一版 Excel 的改善方式的建議? 如果有的話,請參閱 Excel 使用者意見中的主題。

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?

Any additional feedback? (Optional)

Thank you for your feedback!

×