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

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

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

圖1

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

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

圖2

圖3

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

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

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

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

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

圖4

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

圖5

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

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

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

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

圖6

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

圖7

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

例4 當(dāng)輸入重復(fù)的產(chǎn)品編碼時(shí)提示警告信息

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

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

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

圖8

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

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

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

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

圖9

圖10

例5 當(dāng)輸入錯(cuò)誤日期值時(shí)提示警告信息

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

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

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

圖11

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

圖12

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

圖13

例6 當(dāng)出貨數(shù)量大于進(jìn)貨數(shù)量時(shí)提示警告信息

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

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

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

圖14

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

圖15

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