有關資料驗證的詳細資訊

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

您可以使用資料驗證來限制資料類型或使用者輸入到儲存格中的值。例如,您可以使用資料驗證,根據活頁簿他處的值來計算儲存格中允許的最大值。在以下範例中,使用者輸入 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 User Voice 中的主題。

請參閱

將資料驗證套用到儲存格

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×