例1 讓指定單元格中輸入數(shù)據(jù)滿足特定條件

在編輯工作表的過程中，通常會遇到某些單元格中只允許輸入特定值這種情況（不滿足條件時提示錯誤信息），如只允許輸入介于某兩個整數(shù)之間的數(shù)據(jù)、只允許輸入小數(shù)等。這種限制性條件的設(shè)置，可以使用“數(shù)據(jù)有效性”這一功能來實現(xiàn)，具體操作方法如下。

①選中需要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域，單擊“數(shù)據(jù)”標簽，在“數(shù)據(jù)工具”選項組中單擊“數(shù)據(jù)有效性”命令，如圖1所示。

圖1

②打開“數(shù)據(jù)有效性”對話框，選中“設(shè)置”選項卡，在“允許”框下拉列表中選擇“整數(shù)”選項，在“數(shù)據(jù)”框下拉列表中選擇“介于”選項，將“最小值”設(shè)置為“1”，最大值設(shè)置為“1000”，如圖2所示。

③單擊“確定”按鈕，回到文檔編輯狀態(tài)，當在選定單元格中輸入1-1000之外的整數(shù)、任意小數(shù)或文字時，則會彈出警告信息，如圖3所示。

圖2

圖3

提示:在“允許”下拉菜單中還有小數(shù)、日期、時間等幾個選項，這幾個選項的設(shè)置與本設(shè)置方法相同，只要根據(jù)實際需要進行選擇即可。

例2 輸入不滿足條件的數(shù)據(jù)時彈出提示信息

設(shè)置了特定單元格的數(shù)據(jù)有效性之后，如果輸入的數(shù)據(jù)不在所設(shè)置的有效性范圍之內(nèi)，程序則會彈出默認的錯誤提示信息。對于這一錯誤提示信息的內(nèi)容是可以自定義的，其定義方法如下。

①選中需要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域，單擊“數(shù)據(jù)”標簽，在“數(shù)據(jù)工具”選項組中單擊“數(shù)據(jù)有效性”命令，打開“數(shù)據(jù)有效性”對話框。

②在“設(shè)置”選項卡下，設(shè)置好數(shù)據(jù)的有效性。然后切換到“出錯警告”選項卡下，可以重新設(shè)置警告樣式，在“標題”框中輸入標題，在“錯誤信息”框中輸入關(guān)于數(shù)據(jù)有效性的具體設(shè)置信息，如圖4所示。

圖4

③設(shè)置完成后，單擊“確定”按鈕回到工作表編輯狀態(tài)。當在選定單元格中輸入小于1的整數(shù)、小數(shù)或文字時，則會彈出警告信息告知在該單元格中該輸入什么數(shù)據(jù)，如圖5所示。

圖5

例3 設(shè)置輸入數(shù)據(jù)為指定有效序列

有些單元格中需要輸入的數(shù)據(jù)有幾個固定值可選，此時不必手工輸入，可以通過數(shù)據(jù)有效性功能來設(shè)置可選擇序列，從而實現(xiàn)選擇輸入，其操作方法如下。

①選中需要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域，單擊“數(shù)據(jù)”標簽，在“數(shù)據(jù)工具”選項組中單擊“數(shù)據(jù)有效性”命令，打開“數(shù)據(jù)有效性”對話框。

②在“允許”框下拉菜單中選中“序列”選項，在“來源”框中輸入包含在下拉數(shù)據(jù)序列中的數(shù)據(jù)或文字，鍵入時要注意內(nèi)容之間必須用半角逗號隔開，如圖6所示。

圖6

③設(shè)置完成后，單擊“確定”按鈕回到表格編輯狀態(tài)。選中設(shè)置了數(shù)據(jù)序列的單元格，其右邊都會出現(xiàn)一個下拉箭頭，單擊即可打開下拉菜單，如圖7所示，從中選擇所需數(shù)據(jù)即可。

圖7

提示：在設(shè)置序列來源時，如果要作為填充序列的數(shù)據(jù)已輸入到工作表中，可以單擊“來源”框右側(cè)的拾取器按鈕，在工作表中選擇該下拉序列所包含的內(nèi)容。但是在選擇來源時只能從當前工作表中選擇，而無法從其他工作表中選擇，因此在設(shè)置來源時，可以事先將序列來源數(shù)據(jù)復制到當前工作表中以便選擇。

例4 當輸入重復的產(chǎn)品編碼時提示警告信息

數(shù)據(jù)有效性條件可以設(shè)置為公式，當在設(shè)置了數(shù)據(jù)有效性的單元格中輸入不滿足公式條件的值時，則會彈出錯誤提示。如通過本例有效性條件的設(shè)置可以實現(xiàn)避免輸入重復的產(chǎn)品編碼。

①選中需要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域（如本例選擇“編碼”列從C3單元格開始的單元格區(qū)域）。單擊“數(shù)據(jù)”標簽，在“數(shù)據(jù)工具”選項組中單擊“數(shù)據(jù)有效性”命令，打開“數(shù)據(jù)有效性”對話框。

②在“允許”框下拉菜單中選中“自定義”選項。在“公式”編輯欄中輸入公式：=COUNTIF(C:C,C3)=1，如圖8所示。

圖8

③切換到“出錯警告”選項卡下，設(shè)置警告信息，如圖9所示。

④設(shè)置完成后，單擊“確定”按鈕，當在設(shè)置了數(shù)據(jù)有效性的單元格區(qū)域中輸入了相同的產(chǎn)品編碼時則會彈出錯誤提示，如圖10所示。

提示:①=COUNTIF(C:C,C3)=1表示判斷C:C單元格區(qū)域中與C3單元格相同的值是否為1，如果不為1則會提示錯誤信息。

②選中的設(shè)置數(shù)據(jù)有效性的單元格區(qū)域中的第一個單元格公式為=COUNTIF(C:C,C3)=1（如本例中的C3單元格），第二個單元格公式為=COUNTIF(C:C,C4)=1（如本例中的C4單元格），依此類推。

圖9

圖10

例5 當輸入錯誤日期值時提示警告信息

數(shù)據(jù)有效性條件可以設(shè)置為公式，當在設(shè)置了數(shù)據(jù)有效性的單元格中輸入不滿足公式條件的值時，則會彈出錯誤提示。如通過本例數(shù)據(jù)有效性條件的設(shè)置可以實現(xiàn)在指定單元格中只能輸入正確的日期值。

①選中需要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域（如本例選擇“進貨日期”列從A2單元格開始的單元格區(qū)域）。單擊“數(shù)據(jù)”標簽，在“數(shù)據(jù)工具”選項組中單擊“數(shù)據(jù)有效性”命令，打開“數(shù)據(jù)有效性”對話框。

②在“允許”框下拉菜單中選中“自定義”選項。在“公式”編輯欄中輸入公式：=TYPE(A2)=1，如圖11所示。

圖11

③切換到“出錯警告”選項卡下，設(shè)置警告信息，如圖12所示。

圖12

④設(shè)置完成后，單擊“確定”按鈕，當在設(shè)置了數(shù)據(jù)有效性的單元格區(qū)域中輸入了錯誤格式的日期時會彈出錯誤提示，如圖13所示。

圖13

例6 當出貨數(shù)量大于進貨數(shù)量時提示警告信息

數(shù)據(jù)有效性條件可以設(shè)置為公式，當在設(shè)置了數(shù)據(jù)有效性的單元格中輸入不滿足公式條件的值時，則會彈出錯誤提示。如通過本例有效性條件的設(shè)置可以實現(xiàn)避免輸入的出貨數(shù)量大于進貨數(shù)量，具體操作方法如下。

①選中需要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域（如本例選擇“出貨數(shù)量”列從F2單元格開始的單元格區(qū)域）。單擊“數(shù)據(jù)”標簽，在“數(shù)據(jù)工具”選項組中單擊“數(shù)據(jù)有效性”命令，打開“數(shù)據(jù)有效性”對話框。

②在“允許”框下拉菜單中選中“自定義”選項，在“公式”編輯欄中輸入公式：=IF((F2＜E2),FALSE,TRUE)=FALSE，如圖14所示。

圖14

③切換到“出錯警告”選項卡下，設(shè)置出錯警告信息，如圖15所示。

圖15

④設(shè)置完成后，單擊“確定”按鈕，當在設(shè)置了數(shù)據(jù)有效性的單元格區(qū)域中輸入了大于進貨數(shù)量的數(shù)值時會彈出錯誤提示，如圖16所示。