套用資料驗證至儲存格

您使用資料驗證來控制使用者輸入儲存格中的資料或值類型,例如,您可能要將資料輸入限制於特定日期範圍、使用清單限制選項,或者確保只輸入正整數。

本文描述資料驗證在 Excel 中的運作方式,並簡要概述可供您使用的資料驗證技巧;但不探討儲存格保護,保護功能可以讓您「鎖定」或隱藏工作表中的特定儲存格,以避免被編輯或覆寫。如需有關如何保護儲存格的詳細資訊,請參考「請參閱」一節中的連結。

本文內容


什麼是資料驗證?

資料驗證這項 Excel 功能可以讓您用來定義儲存格中可以或應該輸入項目的限制。您可以設定資料驗證,防止使用者輸入無效的資料;您也可以選擇允許使用者輸入無效資料,但在使用者嘗試輸入時發出警告;您也可以提供訊息,定義儲存格中的預期輸入,以及協助使用者修正錯誤的指示。

例如,您可以在行銷工作表中設定儲存格,只允許輸入三個字元長的會計編號;當使用者選取儲存格時,您可以向他們顯示如下的訊息:

選取的儲存格和提示訊息

如果使用者忽略這則訊息,並在儲存格中輸入無效資料 (例如兩位數或五位數數字),您可以向他們顯示實際的錯誤訊息。

在稍微進階的情況下,您可以使用資料驗證,根據活頁簿他處的值來計算儲存格中所允許的最大值。在以下範例中,使用者已在儲存格 E7 中輸入 $4,000,這已經超過指定的佣金和紅利的最大極限。

無效資料和警告訊息

如果薪資預算增加或減少,E7 中允許的最大值也會自動隨之增加或減少。

資料驗證選項位於 [資料工具] 群組中。

Excel 功能區圖像

您在 [資料驗證] 對話方塊中設定資料驗證。

[資枓驗證] 對話方塊

頁首 頁首

資料驗證發揮作用的時機

當您要與組織中其他人共用活頁簿,而您希望輸入活頁簿中的資料要非常準確且一致時,資料驗證就非常寶貴。

除此之外,您也可以使用資料驗證來執行下列作業:

  • 只允許預先定義的清單項目資料    例如,您可以限制部門類型為「銷售」、「財務」、「研發」和「資訊科技」。同樣地,您也可以在活頁簿其他位置,從儲存格範圍建立值清單。如需詳細資訊,請參閱從儲存格範圍中建立下拉式清單

使用資料驗證建立的下拉式清單

  • 限制指定範圍外的數值    例如,您可以指定扣除額最低限制為特定儲存格中孩子數的兩倍。
  • 限制特定時間範圍外的日期    例如,您可以指定介於今天日期與三天後日期之間的時間範圍。
  • 限制特定時間範圍外的時間    例如,您可以指定供應早餐的時間範圍是從餐廳開始營業到營業 5 小時後之間。
  • 限制文字字元數    例如,您可以將一個儲存格中所允許的文字限制為 10 個以下字元,同樣地,您也可以設定全名欄位 (C1) 的特定長度為名字欄位 (A1) 和姓氏欄位 (B1) 的目前長度,加上 10 個字元。
  • 根據其他儲存格中的公式或值驗證資料    例如,您可以使用資料驗證,根據預測的總薪資值,設定佣金和紅利的最大限制是 $3,600。如果使用者在儲存格中輸入超過 $3,600,就會看到驗證訊息。

頁首 頁首

資料驗證訊息

使用者在儲存格中輸入無效資料時所看到的訊息取決於您設定資料驗證的方式。您可以選擇在使用者選取儲存格時顯示「提示訊息」,此類型訊息會顯示於儲存格附近,想要的話,您也可以移動此訊息;不想要的話,訊息會停留在原處直到您移到其他儲存格或按下 ESC 鍵。

提示訊息

提示訊息一般是用來為使用者提供有關您要允許輸入儲存格中的資料類型指引。

您也可以選擇,只有在使用者輸入無效資料後,才顯示「錯誤提醒」。

有自訂警告訊息的錯誤提醒

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

圖示 類型 用途
[停止] 圖示 停止 防止使用者在儲存格中輸入無效資料。[停止] 提醒訊息有兩個選項:[重試][取消]
[警告] 圖示 警告 警告使用者,他們輸入的資料無效,而不阻止使用者輸入資料。出現 [警告] 提醒訊息時,使用者可以按一下 [是] 以接受無效輸入、[否] 以編輯無效輸入,或 [取消] 以移除無效輸入。
[資訊] 圖示 資訊 告訴使用者,他們輸入的資料無效,而不阻止使用者輸入資料。這種錯誤提醒類型是最有彈性的,出現 [資訊] 提醒訊息時,使用者可以按一下 [確定],以接受無效值,或 [取消] 以拒絕無效值。

您可以自訂使用者在錯誤提醒訊息中看到的文字;如果您選擇不要自訂文字,使用者就會看到預設訊息。

只有在將資料直接輸入儲存格中時,才會顯示提示訊息和錯誤提醒,而不會在以下情況中出現:

頁首 頁首

使用資料驗證的秘訣

在以下清單中,您會找到在 Excel 中使用資料驗證的秘訣和竅門。

  • 如果打算要保護 (保護:對工作表或活頁簿進行設定,以防止使用者檢視或取得對指定工作表或活頁簿元素的存取權。)工作表或活頁簿,請在完成指定任何驗證設定值後,再進行保護。在您保護工作表之前,請先確認已解除鎖定任何已驗證的儲存格,否則使用者將無法在儲存格中輸入任何資料。
  • 如果打算要共用活頁簿,請在完成指定資料驗證和保護設定值之後,再進行共用。在您共用活頁簿之後,除非先停止共用,否則無法變更驗證設定,但活頁簿共用時,Excel 會繼續對已指定的儲存格進行驗證。
  • 您可以在已經輸入資料的儲存格中,套用資料驗證,但是 Excel 不會自動通知您現有的儲存格含有無效的資料,在此倩況下,您可以指示 Excel 在工作表上加圓圈,以標示無效的資料。當您標出無效資料以後,就可以再度隱藏圓圈;如果修正無效輸入,則圓圈會自動消失。

無效資料四周的紅色圓圈

  • 若要迅速移除儲存格的資料驗證,請選取儲存格,然後開啟 [資料驗證] 對話方塊 ([資料] 索引標籤,[資料工具] 群組),在 [設定] 索引標籤上按一下 [全部清除]
  • 若要在工作表上找出有資料驗證的儲存格,請在 [常用] 索引標籤上,按一下 [編輯] 群組中的 [尋找與選取],然後按一下 [資料驗證]。找出有資料驗證的儲存格之後,您可以變更、複製或移除驗證設定。
  • 建立下拉式清單時,您可以使用 [定義名稱] 命令 ([公式] 索引標籤,[定義名稱] 群組),定義包含清單的範圍名稱。在其他工作表上建立清單之後,您可以隱藏含清單的工作表,然後保護活頁簿,讓使用者無法存取該清單。

如果資料驗證無法運作,請確認:

使用者未複製或填滿資料    資料驗證的設計是只有在使用者直接在儲存格中輸入資料時,才顯示訊息並防止無效輸入。如果資料是透過複製或填滿輸入時,不會出現訊息。若要防止使用者拖曳儲存格以複製或填滿資料,請清除 [啟用填滿控點與儲存格拖放功能] 核取方塊 ([Excel 選項] 對話方塊,[進階] 選項),然後保護工作表。

已關閉手動重算    如果開啟手動重算,未計算的存格可能會使得資料無法正確驗證。若要關閉手動重算,請在 [公式] 索引標籤上,按一下 [計算] 群組中的 [計算選項],然後按一下 [自動]

公式沒有錯誤    確認已驗證儲存格中的公式不會產生諸如 #REF! 或 #DIV/0! 之類錯誤,Excel 會忽略資料驗證,直到您修正錯誤為止。

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

頁首 頁首

在儲存格或範圍中加入資料驗證

下一節內容中,您要學習不同的技巧,以便在工作表的儲存格中加入驗證。



以下拉式清單值限制資料輸入

您可以變更清單中項目的字型和字型大小。

  1. 選取要驗證的一個或多個儲存格。
  1. [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]

Excel 功能區圖像

顯示問題:無法使用 [資料驗證] 命令。

Excel 表格可能連結到 SharePoint 網站    您無法在連結至 SharePoint 網站中的 Excel 表格中加入資料驗證。若要加入資料驗證,必須取消 Excel 表格的連結,或將 Excel 表格轉換成範圍。

目前您可能是正在輸入資料    當您在儲存格中輸入資料時,無法使用 [資料] 索引標籤上的 [資料驗證] 命令。若要完成資料輸入,請按下 ENTER 鍵或 ESC 鍵。

工作表可能受保護或進行共用    如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。若要瞭解如何停止共用或保護活頁簿,請參考「請參閱」一節中的連結。

  1. [資料驗證] 對話方塊中,按一下 [設定] 索引標籤。
  1. 選取 [儲存格內允許] 方塊中的 [清單]。
  2. 按一下 [來源] 方塊,然後輸入以 Microsoft Windows 清單分隔符號字元 (預設為逗號) 隔開的清單值。

例如:

  • 若要將問題 (如「您有沒有小孩?」) 的答案項目限制為兩種選項,請輸入「有, 沒有」。
  • 若要將廠商的品質口碑限制為三種評等,請輸入「低, 普通, 高」。

另外,也可以參照活頁簿中其他位置的儲存格範圍來建立清單項目。如需詳細資訊,請參閱從儲存格範圍中建立下拉式清單

 附註   下拉式清單的寬度是根據含有資料驗證的儲存格寬度來決定。您可能需要調整該儲存格的寬度,以避免將大於下拉式清單寬度的有效項目寬度截斷。

  1. 確認已選取 [儲存格內的下拉式清單] 核取方塊,否則您看不見儲存格旁的下拉式箭號。
  2. 若要指定想要處理空白值 (Null) 的方式,請選取或清除 [忽略空白] 核取方塊。

 附註   如果您的允許值是以具有定義名稱的儲存格範圍為依據,而該範圍中有任何一處含空白儲存格,請選取 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值;這也適用於驗證公式所參照的任何儲存格:如果任何參照的儲存格為空白,請選取 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值。

  1. 按下儲存格時,選擇性顯示提示訊息。

顯示如何顯示提示訊息

  1. 按一下 [提示訊息] 索引標籤。
  2. 確定已經選取 [當儲存格被選取時,顯示提示訊息] 核取方塊。
  3. 填入訊息的標題及文字。
  1. 指定要 Microsoft Office Excel 在發現資料輸入錯誤時的回應方式。

顯示如何指定無效資料的回應

  1. 按一下 [錯誤提醒] 索引標籤,並確定已經選取 [輸入的資料不正確時顯示警訊] 核取方塊。

 附註   如果您要允許使用者輸入清單以外的項目,請改為清除 [輸入的資料不正確時顯示警訊] 核取方塊。

  1. 選取下列其中一個 [樣式] 方塊選項:
    • 若要顯示一個不阻止資料輸入錯誤的資訊訊息,請選取 [資訊]。
    • 若要顯示一個不阻止資料輸入錯誤的警告訊息,請選取 [警告]。
    • 若要避免輸入錯誤的資料,請選取 [停止]。
  2. 填入訊息的標題與文字 (最多 225 個字元)。

 附註   如果您不輸入標題或文字,標題會使用預設的字串 "Microsoft Excel" 和訊息 (依預設值):「您所輸入的值不正確。您必須在此儲存格內輸入符合資料驗證準則的資料。」

  1. 測試資料驗證,以確認運作正確無誤。

嘗試在儲存格中輸入有效和無效的資料,以確認您的設定確實如預期運作,而訊息也如您預期顯示。

秘訣    如果您變更儲存格的驗證設定,可以自動將變更套用至有相同設定的所有其他儲存格。若要執行這項作業,請開啟 [資料驗證] 對話方塊,然後在 [設定] 索引標籤上,選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

只允許限制內整數的資料輸入

  1. 選取要驗證的一個或多個儲存格。
  1. [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]

Excel 功能區圖像

顯示問題:無法使用 [資料驗證] 命令。

Excel 表格可能連結到 SharePoint 網站    您無法在連結至 SharePoint 網站中的 Excel 表格中加入資料驗證。若要加入資料驗證,必須取消 Excel 表格的連結,或將 Excel 表格轉換成範圍。

目前您可能是正在輸入資料    當您在儲存格中輸入資料時,無法使用 [資料] 索引標籤上的 [資料驗證] 命令。若要完成資料輸入,請按下 ENTER 鍵或 ESC 鍵。

工作表可能受保護或進行共用    如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。若要瞭解如何停止共用或保護活頁簿,請參考「請參閱」一節中的連結。

  1. [資料驗證] 對話方塊中,按一下 [設定] 索引標籤。
  1. 選取 [儲存格內允許] 方塊中的 [整數]
  2. 在 [資料] 方塊中選取所要的限制類型。例如,要設定上限及下限,請選取 [介於]。
  3. 輸入允許的最小值、最大值或特定值。另外,也可以輸入傳回數值的公式。

例如,若要將扣除額的最小限制設定為 F1 儲存格中孩子數的兩倍,請選取 [資料] 方塊中的 [大於或等於],然後在 [最小值] 方塊中輸入公式「=2*F1」。

  1. 若要指定您要處理空白 (null) 值的方式,請選取或清除 [忽略空白] 核取方塊。

 附註   如果您的允許值是以具有定義名稱的儲存格範圍為依據,而該範圍中有任何一處含空白儲存格,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值;這也適用於驗證公式所參照的任何儲存格:如果任何參照的儲存格為空白,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值。

  1. 也可以在按一下儲存格時顯示提示訊息。

顯示如何顯示提示訊息

  1. 按一下 [提示訊息] 索引標籤。
  2. 確認已經選取 [當儲存格被選取時,顯示提示訊息] 核取方塊。
  3. 填入訊息的標題和文字。
  1. 指定輸入無效資料時您要 Microsoft Office Excel 回應的方式。

顯示如何指定無效資料的回應

  1. 按一下 [錯誤提醒] 索引標籤,並確認已經選取 [輸入的資料不正確時顯示警訊] 核取方塊。
  2. 選取下列其中一個 [樣式] 方塊的選項:
    • 若要顯示不會阻止無效資料輸入的資訊訊息,請選取 [資訊]
    • 若要顯示不會阻止無效資料輸入的警告訊息,請選取 [警告]
    • 若要阻止輸入無效資料,請選取 [停止]
  3. 填入訊息的標題和文字 (最多 225 個字元)。

 附註   如果您不輸入標題或文字,標題會預設為 Microsoft Excel,而訊息預設為:您輸入的值不正確,使用者可輸入此儲存格的值會受到限制。

  1. 測試資料驗證,以確認運作正確無誤。

試著在這些儲存格中輸入有效及無效資料,確認設定值如預期執行,而且訊息也如預期出現。

秘訣    如果您變更儲存格的驗證設定,可以自動將變更套用至有相同設定的所有其他儲存格。若要執行這項作業,請開啟 [資料驗證] 對話方塊,然後在 [設定] 索引標籤上,選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

只允許限制內小數的資料輸入

  1. 選取要驗證的一個或多個儲存格。
  1. [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]

Excel 功能區圖像

顯示問題:無法使用 [資料驗證] 命令。

Excel 表格可能連結到 SharePoint 網站    您無法在連結至 SharePoint 網站中的 Excel 表格中加入資料驗證。若要加入資料驗證,必須取消 Excel 表格的連結,或將 Excel 表格轉換成範圍。

目前您可能是正在輸入資料    當您在儲存格中輸入資料時,無法使用 [資料] 索引標籤上的 [資料驗證] 命令。若要完成資料輸入,請按下 ENTER 鍵或 ESC 鍵。

工作表可能受保護或進行共用    如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。若要瞭解如何停止共用或保護活頁簿,請參考「請參閱」一節中的連結。

  1. [資料驗證] 對話方塊中,按一下 [設定] 索引標籤。
  1. 選取 [儲存格內允許] 方塊中的 [實數]。
  2. 在 [資料] 方塊中選取所要的限制類型。例如,要設定上限及下限,請選取 [介於]。
  3. 輸入允許的最小值、最大值或特定值。另外,也可以輸入傳回數值的公式。

例如,若要將佣金或獎金的最小限制設定為 E1 儲存格中銷售人員薪水的 6%,請選取 [資料] 方塊中的 [小於或等於],然後在 [最大值] 方塊中輸入公式「=E1*6%」。

 附註   若要讓使用者輸入百分比 (如 20%),請選取 [儲存格內允許] 方塊中的 [實數],然後在 [資料] 方塊中選取所要的限制類型,再以小數形式輸入最小值、最大值或指定值,例如 .2,然後選取儲存格並在 [常用] 索引標籤上的 [數值] 群組中按一下 [百分比樣式] 按鈕圖像 ,將資料驗證儲存格顯示成百分比。

  1. 若要指定您要處理空白 (null) 值的方式,請選取或清除 [忽略空白] 核取方塊。

 附註   如果您的允許值是以具有定義名稱的儲存格範圍為依據,而該範圍中有任何一處含空白儲存格,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值;這也適用於驗證公式所參照的任何儲存格:如果任何參照的儲存格為空白,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值。

  1. 也可以在按一下儲存格時顯示提示訊息。

顯示如何顯示提示訊息

  1. 按一下 [提示訊息] 索引標籤。
  2. 確認已經選取 [當儲存格被選取時,顯示提示訊息] 核取方塊。
  3. 填入訊息的標題和文字。
  1. 指定輸入無效資料時您要 Microsoft Office Excel 回應的方式。

顯示如何指定無效資料的回應

  1. 按一下 [錯誤提醒] 索引標籤,並確認已經選取 [輸入的資料不正確時顯示警訊] 核取方塊。
  2. 選取下列其中一個 [樣式] 方塊的選項:
    • 若要顯示不會阻止無效資料輸入的資訊訊息,請選取 [資訊]
    • 若要顯示不會阻止無效資料輸入的警告訊息,請選取 [警告]
    • 若要阻止輸入無效資料,請選取 [停止]
  3. 填入訊息的標題和文字 (最多 225 個字元)。

 附註   如果您不輸入標題或文字,標題會預設為 "Microsoft Excel" 和訊息預設為:「您所輸入的值不正確。您必須在此儲存格內輸入符合資料驗證準則的資料。」

  1. 測試資料驗證,以確認運作正確無誤。

嘗試在儲存格中輸入有效和無效的資料,以確認您的設定確實如預期運作,而訊息也如您預期顯示。

秘訣    如果您變更儲存格的驗證設定,可以自動將變更套用至有相同設定的所有其他儲存格。若要執行這項作業,請開啟 [資料驗證] 對話方塊,然後在 [設定] 索引標籤上,選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

只允許時間範圍內日期的資料輸入

  1. 選取要驗證的一個或多個儲存格。
  1. [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]

Excel 功能區圖像

顯示問題:無法使用 [資料驗證] 命令。

Excel 表格可能連結到 SharePoint 網站    您無法在連結至 SharePoint 網站中的 Excel 表格中加入資料驗證。若要加入資料驗證,必須取消 Excel 表格的連結,或將 Excel 表格轉換成範圍。

目前您可能是正在輸入資料    當您在儲存格中輸入資料時,無法使用 [資料] 索引標籤上的 [資料驗證] 命令。若要完成資料輸入,請按下 ENTER 鍵或 ESC 鍵。

工作表可能受保護或進行共用    如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。若要瞭解如何停止共用或保護活頁簿,請參考「請參閱」一節中的連結。

  1. [資料驗證] 對話方塊中,按一下 [設定] 索引標籤。
  1. 選取 [儲存格內允許] 方塊中的 [日期]。
  2. 在 [資料] 方塊中選取所要的限制類型。例如,若要允許某一天以後的日期,請選取 [大於]。
  3. 輸入允許的開始日期、結束日期或特定日期。另外,也可以輸入傳回日期的公式。

例如,若要設定今天日期及其往後三天之間的時間範圍,請在 [資料] 方塊中選取 [介於]、在 [最小值] 方塊中輸入「=TODAY()」,並在 [最大值] 方塊中輸入「=TODAY()+3」。

  1. 若要指定您要處理空白 (null) 值的方式,請選取或清除 [忽略空白] 核取方塊。

 附註   如果您的允許值是以具有定義名稱的儲存格範圍為依據,而該範圍中有任何一處含空白儲存格,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值;這也適用於驗證公式所參照的任何儲存格:如果任何參照的儲存格為空白,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值。

  1. 也可以在按一下儲存格時顯示提示訊息。

顯示如何顯示提示訊息

  1. 按一下 [提示訊息] 索引標籤。
  2. 確認已經選取 [當儲存格被選取時,顯示提示訊息] 核取方塊。
  3. 填入訊息的標題和文字。
  1. 指定輸入無效資料時您要 Microsoft Office Excel 回應的方式。

顯示如何指定無效資料的回應

  1. 按一下 [錯誤提醒] 索引標籤,並確認已經選取 [輸入的資料不正確時顯示警訊] 核取方塊。
  2. 選取下列其中一個 [樣式] 方塊的選項:
    • 若要顯示不會阻止無效資料輸入的資訊訊息,請選取 [資訊]
    • 若要顯示不會阻止無效資料輸入的警告訊息,請選取 [警告]
    • 若要阻止輸入無效資料,請選取 [停止]
  3. 填入訊息的標題和文字 (最多 225 個字元)。

 附註   如果您不輸入標題或文字,標題會預設為 "Microsoft Excel" 和訊息預設為:「您所輸入的值不正確。您必須在此儲存格內輸入符合資料驗證準則的資料。」

  1. 測試資料驗證,以確認運作正確無誤。

嘗試在儲存格中輸入有效和無效的資料,以確認您的設定確實如預期運作,而訊息也如您預期顯示。

秘訣    如果您變更儲存格的驗證設定,可以自動將變更套用至有相同設定的所有其他儲存格。若要執行這項作業,請開啟 [資料驗證] 對話方塊,然後在 [設定] 索引標籤上,選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

只允許時間範圍內時間的資料輸入

  1. 選取要驗證的一個或多個儲存格。
  1. [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]

Excel 功能區圖像

顯示問題:無法使用 [資料驗證] 命令。

Excel 表格可能連結到 SharePoint 網站    您無法在連結至 SharePoint 網站中的 Excel 表格中加入資料驗證。若要加入資料驗證,必須取消 Excel 表格的連結,或將 Excel 表格轉換成範圍。

目前您可能是正在輸入資料    當您在儲存格中輸入資料時,無法使用 [資料] 索引標籤上的 [資料驗證] 命令。若要完成資料輸入,請按下 ENTER 鍵或 ESC 鍵。

工作表可能受保護或進行共用    如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。若要瞭解如何停止共用或保護活頁簿,請參考「請參閱」一節中的連結。

  1. [資料驗證] 對話方塊中,按一下 [設定] 索引標籤。
  1. 選取 [儲存格內允許] 方塊中的 [時間]。
  2. 在 [資料] 方塊中選取所要的限制類型。例如,若要允許當天某一個時間以前的時間,請選取 [小於]。
  3. 輸入允許的開始時間、結束時間或特定時間。另外,也可以輸入傳回時間值的公式。

例如,若要將餐廳供應早餐的時間範圍設定為開始營業的時間 (位於儲存格 H1) 到營業 5 小時後之間,請在 [資料] 方塊中選取 [介於]、在 [最小值] 方塊中輸入=H1,並在 [最大值] 方塊中輸入=H1+"5:00"

  1. 若要指定您要處理空白 (null) 值的方式,請選取或清除 [忽略空白] 核取方塊。

 附註   如果您的允許值是以具有定義名稱的儲存格範圍為依據,而該範圍中有任何一處含空白儲存格,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值;這也適用於驗證公式所參照的任何儲存格:如果任何參照的儲存格為空白,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值。

  1. 也可以在按一下儲存格時顯示提示訊息。

顯示如何顯示提示訊息

  1. 按一下 [提示訊息] 索引標籤。
  2. 確認已經選取 [當儲存格被選取時,顯示提示訊息] 核取方塊。
  3. 填入訊息的標題和文字。
  1. 指定輸入無效資料時您要 Microsoft Office Excel 回應的方式。

顯示如何指定無效資料的回應

  1. 按一下 [錯誤提醒] 索引標籤,並確認已經選取 [輸入的資料不正確時顯示警訊] 核取方塊。
  2. 選取下列其中一個 [樣式] 方塊的選項:
    • 若要顯示不會阻止無效資料輸入的資訊訊息,請選取 [資訊]
    • 若要顯示不會阻止無效資料輸入的警告訊息,請選取 [警告]
    • 若要阻止輸入無效資料,請選取 [停止]
  3. 填入訊息的標題和文字 (最多 225 個字元)。

 附註   如果您不輸入標題或文字,標題會預設為 "Microsoft Excel" 和訊息預設為:「您所輸入的值不正確。您必須在此儲存格內輸入符合資料驗證準則的資料。」

  1. 測試資料驗證,以確認運作正確無誤。

嘗試在儲存格中輸入有效和無效的資料,以確認您的設定確實如預期運作,而訊息也如您預期顯示。

秘訣    如果您變更儲存格的驗證設定,可以自動將變更套用至有相同設定的所有其他儲存格。若要執行這項作業,請開啟 [資料驗證] 對話方塊,然後在 [設定] 索引標籤上,選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

只允許指定長度文字的資料輸入

  1. 選取要驗證的一個或多個儲存格。
  1. [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]

Excel 功能區圖像

顯示問題:無法使用 [資料驗證] 命令。

Excel 表格可能連結到 SharePoint 網站    您無法在連結至 SharePoint 網站中的 Excel 表格中加入資料驗證。若要加入資料驗證,必須取消 Excel 表格的連結,或將 Excel 表格轉換成範圍。

目前您可能是正在輸入資料    當您在儲存格中輸入資料時,無法使用 [資料] 索引標籤上的 [資料驗證] 命令。若要完成資料輸入,請按下 ENTER 鍵或 ESC 鍵。

工作表可能受保護或進行共用    如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。若要瞭解如何停止共用或保護活頁簿,請參考「請參閱」一節中的連結。

  1. [資料驗證] 對話方塊中,按一下 [設定] 索引標籤。
  1. 選取 [儲存格內允許] 方塊中的 [文字長度]。
  2. 在 [資料] 方塊中選取所要的限制類型。例如,若要允許最多某個字元數,請選取 [小於或等於]。
  3. 輸入文字的最小值、最大值或特定長度。另外,也可以輸入傳回數值的公式。

例如,若要將全名欄位 (C1) 的特定長度設定為名字欄位 (A1) 及姓氏欄位 (B1) 的目前長度加上 10,請選取 [資料] 方塊中的 [小於或等於],然後在 [最大值] 方塊中輸入「=SUM(LEN(A1),LEN(B1),10)」。

  1. 若要指定您要處理空白 (null) 值的方式,請選取或清除 [忽略空白] 核取方塊。

 附註   如果您的允許值是以具有定義名稱的儲存格範圍為依據,而該範圍中有任何一處含空白儲存格,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值;這也適用於驗證公式所參照的任何儲存格:如果任何參照的儲存格為空白,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值。

  1. 也可以在按一下儲存格時顯示提示訊息。

顯示如何顯示提示訊息

  1. 按一下 [提示訊息] 索引標籤。
  2. 確認已經選取 [當儲存格被選取時,顯示提示訊息] 核取方塊。
  3. 填入訊息的標題和文字。
  1. 指定輸入無效資料時您要 Microsoft Office Excel 回應的方式。

顯示如何指定無效資料的回應

  1. 按一下 [錯誤提醒] 索引標籤,並確認已經選取 [輸入的資料不正確時顯示警訊] 核取方塊。
  2. 選取下列其中一個 [樣式] 方塊的選項:
    • 若要顯示不會阻止無效資料輸入的資訊訊息,請選取 [資訊]
    • 若要顯示不會阻止無效資料輸入的警告訊息,請選取 [警告]
    • 若要阻止輸入無效資料,請選取 [停止]
  3. 填入訊息的標題和文字 (最多 225 個字元)。

 附註   如果您不輸入標題或文字,標題會預設為 "Microsoft Excel" 和訊息預設為:「您所輸入的值不正確。您必須在此儲存格內輸入符合資料驗證準則的資料。」

  1. 測試資料驗證,以確認運作正確無誤。

嘗試在儲存格中輸入有效和無效的資料,以確認您的設定確實如預期運作,而訊息也如您預期顯示。

秘訣    如果您變更儲存格的驗證設定,可以自動將變更套用至有相同設定的所有其他儲存格。若要執行這項作業,請開啟 [資料驗證] 對話方塊,然後在 [設定] 索引標籤上,選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

根據其他儲存格的內容計算允許的項目

  1. 選取要驗證的一個或多個儲存格。
  1. [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]

Excel 功能區圖像

顯示問題:無法使用 [資料驗證] 命令。

Excel 表格可能連結到 SharePoint 網站    您無法在連結至 SharePoint 網站中的 Excel 表格中加入資料驗證。若要加入資料驗證,必須取消 Excel 表格的連結,或將 Excel 表格轉換成範圍。

目前您可能是正在輸入資料    當您在儲存格中輸入資料時,無法使用 [資料] 索引標籤上的 [資料驗證] 命令。若要完成資料輸入,請按下 ENTER 鍵或 ESC 鍵。

工作表可能受保護或進行共用    如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。若要瞭解如何停止共用或保護活頁簿,請參考「請參閱」一節中的連結。

  1. [資料驗證] 對話方塊中,按一下 [設定] 索引標籤。
  1. 在 [儲存格內允許] 方塊中選取所要的資料類型。
  2. 在 [資料] 方塊中選取所要的限制類型。
  3. [資料] 方塊底下的一個或多個方塊中,按一下要用來指定允許項目的儲存格。

例如,若只有在結果未超過預算 (位於儲存格 E4) 時才允許科目輸入,請選取 [儲存格內允許] 下的 [實數],再選取 [資料] 的 [小於或等於],然後在 [最大值] 方塊中輸入「=E4」。

  1. 若要指定您要處理空白 (null) 值的方式,請選取或清除 [忽略空白] 核取方塊。

 附註   如果您的允許值是以具有定義名稱的儲存格範圍為依據,而該範圍中有任何一處含空白儲存格,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值;這也適用於驗證公式所參照的任何儲存格:如果任何參照的儲存格為空白,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值。

  1. 也可以在按一下儲存格時顯示提示訊息。

顯示如何顯示提示訊息

  1. 按一下 [提示訊息] 索引標籤。
  2. 確認已經選取 [當儲存格被選取時,顯示提示訊息] 核取方塊。
  3. 填入訊息的標題和文字。
  1. 指定輸入無效資料時您要 Microsoft Office Excel 回應的方式。

顯示如何指定無效資料的回應

  1. 按一下 [錯誤提醒] 索引標籤,並確認已經選取 [輸入的資料不正確時顯示警訊] 核取方塊。
  2. 選取下列其中一個 [樣式] 方塊的選項:
    • 若要顯示不會阻止無效資料輸入的資訊訊息,請選取 [資訊]
    • 若要顯示不會阻止無效資料輸入的警告訊息,請選取 [警告]
    • 若要阻止輸入無效資料,請選取 [停止]
  3. 填入訊息的標題和文字 (最多 225 個字元)。

 附註   如果您不輸入標題或文字,標題會預設為 "Microsoft Excel" 和訊息預設為:「您所輸入的值不正確。您必須在此儲存格內輸入符合資料驗證準則的資料。」

  1. 測試資料驗證,以確認運作正確無誤。

嘗試在儲存格中輸入有效和無效的資料,以確認您的設定確實如預期運作,而訊息也如您預期顯示。

秘訣    如果您變更儲存格的驗證設定,可以自動將變更套用至有相同設定的所有其他儲存格。若要執行這項作業,請開啟 [資料驗證] 對話方塊,然後在 [設定] 索引標籤上,選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

使用公式計算允許的項目

  1. 選取要驗證的一個或多個儲存格。
  1. [資料] 索引標籤的 [資料工具] 群組中,按一下 [資料驗證]

Excel 功能區圖像

顯示問題:無法使用 [資料驗證] 命令。

Excel 表格可能連結到 SharePoint 網站    您無法在連結至 SharePoint 網站中的 Excel 表格中加入資料驗證。若要加入資料驗證,必須取消 Excel 表格的連結,或將 Excel 表格轉換成範圍。

目前您可能是正在輸入資料    當您在儲存格中輸入資料時,無法使用 [資料] 索引標籤上的 [資料驗證] 命令。若要完成資料輸入,請按下 ENTER 鍵或 ESC 鍵。

工作表可能受保護或進行共用    如果活頁簿正在共用或受到保護,就無法變更資料驗證設定。若要瞭解如何停止共用或保護活頁簿,請參考「請參閱」一節中的連結。

  1. [資料驗證] 對話方塊中,按一下 [設定] 索引標籤。
  1. 選取 [儲存格內允許] 方塊中的 [自訂]。
  2. 在 [公式] 方塊中輸入計算邏輯值的公式 (TRUE 表示輸入正確,FALSE 表示輸入錯誤)。
要確定下列狀況 請輸入
只有在沒有為裁量科目 (D1) 編列預算且總預算 (D2) 小於 $40,000 攤銷成本時,才可以更新聚餐科目的儲存格 (B1)。 =AND(D1=0,D2<40000)
含產品說明的儲存格 (B2) 只包括了文字。 =ISTEXT(B2)
針對含預測廣告預算的儲存格 (B3),轉包商及服務的小計 (E1) 必須小於或等於 $800,而且預算總金額 (E2) 也必須小於或等於 $97,000。 =AND(E1<=800,E2<=97000)
含員工年齡的儲存格 (B4) 一定要大於服務年資 (F1) 加上 18 (工齡的最小值)。 =IF(B4>F1+18,TRUE,FALSE)
儲存格範圍 A1:A20 中的所有資料都包含唯一值。

=COUNTIF($A$1:$A$20,A1)=1

您應該要為儲存格 A1 輸入資料驗證的公式,然後向下填滿 A2 到 A20 儲存格,如此在這個範圍中每一個儲存格的資料驗證都有相似的公式,而 COUNTIF 函數的第二個引數會與目前儲存格相符。

含產品代碼名稱的儲存格 (B5) 會一直以標準的字首 ID- 為開頭,而其長度至少為 10 個字元。 =AND(LEFT(B5, 3) ="ID-",LEN(B5) > 9)
  1. 若要指定您要處理空白 (null) 值的方式,請選取或清除 [忽略空白] 核取方塊。

 附註   如果您的允許值是以具有定義名稱的儲存格範圍為依據,而該範圍中有任何一處含空白儲存格,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值;這也適用於驗證公式所參照的任何儲存格:如果任何參照的儲存格為空白,請設定 [忽略空白] 核取方塊,允許在已驗證儲存格中輸入任何值。

  1. 也可以在按一下儲存格時顯示提示訊息。

顯示如何顯示提示訊息

  1. 按一下 [提示訊息] 索引標籤。
  2. 確認已經選取 [當儲存格被選取時,顯示提示訊息] 核取方塊。
  3. 填入訊息的標題和文字。
  1. 指定輸入無效資料時您要 Microsoft Office Excel 回應的方式。

顯示如何指定無效資料的回應

  1. 按一下 [錯誤提醒] 索引標籤,並確認已經選取 [輸入的資料不正確時顯示警訊] 核取方塊。
  2. 選取下列其中一個 [樣式] 方塊的選項:
    • 若要顯示不會阻止無效資料輸入的資訊訊息,請選取 [資訊]
    • 若要顯示不會阻止無效資料輸入的警告訊息,請選取 [警告]
    • 若要阻止輸入無效資料,請選取 [停止]
  3. 填入訊息的標題和文字 (最多 225 個字元)。

 附註   如果您不輸入標題或文字,標題會預設為 "Microsoft Excel" 和訊息預設為:「您所輸入的值不正確。您必須在此儲存格內輸入符合資料驗證準則的資料。」

  1. 測試資料驗證,以確認運作正確無誤。

嘗試在儲存格中輸入有效和無效的資料,以確認您的設定確實如預期運作,而訊息也如您預期顯示。

秘訣    如果您變更儲存格的驗證設定,可以自動將變更套用至有相同設定的所有其他儲存格。若要執行這項作業,請開啟 [資料驗證] 對話方塊,然後在 [設定] 索引標籤上,選取 [將所做的改變套用至所有具有相同設定的儲存格] 核取方塊。

頁首 頁首

 
 
適用:
Excel 2007