資料驗證的概觀與範例

資料驗證是什麼?

Microsoft Excel 資料驗證可讓您定義要在儲存格中輸入的資料種類。例如,您可以指定以字母評定等級的項目只允許字母 A 到 F。您可以設定資料驗證以防使用者輸入無效的資料,或允許無效的資料,但在使用者完成後能夠檢查出來。您也可以提供訊息,以定義儲存格中預期的輸入內容,以及幫助使用者改正任何錯誤的指示。

輸入不符合要求的資料後,Excel 會顯示您提供的訊息。

針對錯誤資料的警告訊息

在設計供其他人用來輸入資料的表單或工作表 ( 例如預算表或支出報表 ) 時,資料驗證特別有用。

本文說明如何設定資料驗證,包括可驗證的資料類型,以及可顯示的訊息,並提供活頁簿供您下載,以取得可修改的驗證範例,並用在您自己的工作表上。

可驗證的資料類型

Excel 可讓您為儲存格指定下列有效資料類型:

數字    指定輸入儲存格的資料必須是整數或小數。您可以設定最小值或最大值,排除某個數值範圍,或使用公式計算數字是否有效。

日期與時間    設定最小值或最大值,排除某些日期或時間,或使用公式計算日期或時間是否有效。

長度     限制儲存格中可鍵入多少字元,或要求字元數的最小值。

數值清單    為儲存格製作選擇清單—例如,大、中、小—而且儲存格中只允許這幾個值。當使用者按一下儲存格時您可以顯示下拉式箭號,以便挑選清單中的值。

可顯示的訊息類型

對於驗證的每個儲存格,您可以顯示兩種不同訊息:一種出現在使用者輸入資料前, 另一種出現在使用者嘗試輸入不符合要求的資料後。如果使用者已開啟 Office 小幫手,將由小幫手顯示訊息。

輸入訊息     當使用者按一下必須驗證的儲存格時會立刻出現這類訊息。利用這類訊息,可以指示使用者應該在儲存格中輸入哪一種資料。

錯誤訊息    當使用者鍵入無效的資料並按 ENTER 時就會出現這類訊息。可選擇的錯誤訊息有三種:

  • 資訊訊息    這類訊息不會阻止輸入錯誤的資料。除了您提供的文字外,它有一個資訊圖示、一個 [確定] 按鈕 (用來將無效的資料輸入儲存格中) 以及一個 [取消] 按鈕 (用來將儲存格還原成先前的值)。
  • 警告訊息    這類訊息不會阻止輸入錯誤的資料。它包含您提供的文字、警告圖示和三個按鈕:[是] 會將無效的資料輸入儲存格中,[否] 會回到儲存格做進一步的編輯,[取消] 則會將儲存格還原成先前的值。
  • 停止訊息     這類訊息將不允許輸入錯誤的資料。它包含您提供的文字、停止圖示和兩個按鈕:[重試] 會回到儲存格做進一步的編輯,[取消] 則會將儲存格還原成先前的值。請注意,這類訊息的目的並不是要做為安全措施:雖然使用者無法以鍵入資料再按 ENTER 的方式輸入錯誤的資料,但他們可以利用複製與貼上或在儲存格中填滿資料的方式設法規避驗證。

如果不指定任何訊息,Excel 會標示使用者輸入的資料是否正確,讓您可以在稍後進行檢查,但不會在輸入資料錯誤時通知使用者。

驗證範例

要瞭解資料驗證是什麼,最好的方法就是看看它的實際運作。如果下載本文附屬的活頁簿範例,您將取得每種驗證類型和所有訊息類型的範例。此活頁簿中還包含指示,告訴您如何檢視每個驗證儲存格所用的設定,以及如何將驗證設定複製到您自己的活頁簿上,讓您可以修改成符合您的用途。

設定資料驗證

當您知道要在工作表上使用哪一種驗證後,您可以使用 [資料] 功能表上的 [驗證] 指令進行設定。在從本文下載的活頁簿範例中,以及 Excel 2002 [說明] 中,您將可以看到指示。以下是整個程序的一般概況:

  1. 設定工作表    ;一開始要在工作表上輸入資料與公式。如果使用有效選擇清單,請輸入清單,並予以命名。
  2. 定義儲存格的設定    從要驗證的第一個儲存格開始,使用 [資料驗證] 對話方塊指定您要的驗證類型、輸入訊息和錯誤訊息。
  3. 設定其他儲存格的驗證     通常為了節省時間,您只須從第一個儲存格複製驗證資訊到其他儲存格,然後再修改設定即可。
  4. 測試驗證規則    嘗試在儲存格中輸入有效與無效的資料,以確定設定的確有預期的效果,而且訊息也會如預期般出現無誤。使用 [驗證] 指令修改任何設定。如果在一個儲存格中變更驗證方式,您可以對具有相同設定的其他所有儲存格自動套用所做的變更。
  5. 設定有效選擇清單    如果使用有效選擇清單,而且不希望使用者找到與變更清單,您可以在別的工作表中輸入清單、設定驗證方式、隱藏包含清單的工作表然後以密碼保護活頁簿。
  6. 依需要套用保護    如果計畫保護工作表或活頁簿,請在完成設定驗證後進行。在保護工作表之前必須確定 解除鎖定 任何驗證儲存格,否則使用者將無法在儲存格中鍵入資料。
  7. 依需要將活頁簿分享    如果計畫共用活頁簿,請在完成設定驗證與保護後進行。共用活頁簿之後,除非停止共用,否則將無法變更驗證設定,但在活頁簿共用後,Excel 仍將繼續驗證已指定的儲存格。
  8. 檢查錯誤資料的結果    使用者在工作表中輸入資料後,您可以依本文稍後介紹的方式檢查有沒有錯誤資料。

在驗證儲存格中輸入資料

以下為類似使用者輸入資料的過程。您可以使用輸入和錯誤訊息來提供指示,讓使用者瞭解為了確保資料的正確性您如何設定工作表。若要瞭解可能的狀況,請下載本文附屬的活頁簿範例,並查閱 [訊息] 工作表上的範例。

檢視輸入資訊    當使用者按一下驗證儲存格,或使用方向鍵移到儲存格時,您的輸入訊息會顯示在小幫手註解方塊或單獨的訊息方塊中。如果為儲存格提供下拉式清單,該儲存格右邊會出現下拉式箭號。

鍵入資料    當使用者鍵入資料或按一下下拉式箭號以選取清單中的值時,輸入訊息會留在螢幕上 (下拉式清單可能會蓋住部分訊息)。

輸入有效資料    如果使用者鍵入有效的資料再按 ENTER,資料將會輸入儲存格中,不會發生任何不尋常的狀況。

輸入錯誤資料    如果使用者鍵入不符合條件的資料,而您為錯誤資料指定錯誤訊息的話,您的錯誤訊息會顯示在小幫手註解方塊或單獨的訊息視窗中。然後使用者可以閱讀該訊息,並決定該怎麼做。

  • 若是資訊訊息,使用者可以按一下 [確定] 以輸入錯誤資料,或按一下 [取消] 以重新再來。
  • 若是警告訊息,使用者可以按一下 [是] 以輸入錯誤資料,或按一下 [否] 以進一步編輯儲存格,也可以按一下 [取消] 以重新再來。
  • 若是停止訊息,使用者可以按一下 [重試] 以編輯儲存格,或按一下 [取消] 以重新再來。

如果未提供訊息,對使用者而言,在驗證儲存格中輸入資料與一般的 Excel 資料輸入沒有什麼不同。但是 Excel 會標示包含無效資料的儲存格,讓您可以輕易找到這些項目。

檢查工作表的錯誤項目

收回工作表時,因為使用者可能曾輸入錯誤的資料,您可以讓 Excel 在不符合條件的任何資料四周顯示紅色圈圈,讓您可以比較容易掃視工作表,找出錯誤。[公式稽核] 工具列上的 [圈選錯誤資料] 和 [清除錯誤圈選] 按鈕即可用於此用途。

包含錯誤資料的儲存格

此儲存格中的值被圈出來,因為它不符合驗證規則。

改正儲存格中的資料後,圓圈就會消失。如需瞭解此功能的可能狀況,請下載本文附屬的活頁簿範例,並查閱 [錯誤資料] 工作表。