3.1　使用數(shù)據(jù)驗(yàn)證功能讓數(shù)據(jù)輸入規(guī)范化

Excel為用戶提供了靈活的數(shù)據(jù)輸入方式，用戶可以在工作表中輸入任意內(nèi)容。靈活輸入的同時(shí)也會(huì)帶來一些弊端，格式不規(guī)范的數(shù)據(jù)為以后的數(shù)據(jù)匯總和分析帶來麻煩。使用“數(shù)據(jù)驗(yàn)證”功能可以設(shè)置數(shù)據(jù)輸入的規(guī)則，只有符合規(guī)則的數(shù)據(jù)才能被輸入單元格中，從而避免輸入無效數(shù)據(jù)。在Excel 2013之前的版本中，“數(shù)據(jù)驗(yàn)證”功能的名稱為“數(shù)據(jù)有效性”。

3.1.1　了解數(shù)據(jù)驗(yàn)證

使用“數(shù)據(jù)驗(yàn)證”功能可以根據(jù)預(yù)先設(shè)置好的驗(yàn)證規(guī)則，對(duì)用戶輸入的數(shù)據(jù)進(jìn)行檢查，并將符合規(guī)則的數(shù)據(jù)輸入單元格中，而拒絕輸入不符合規(guī)則的數(shù)據(jù)。選擇要設(shè)置數(shù)據(jù)驗(yàn)證的一個(gè)或多個(gè)單元格，然后在功能區(qū)的“數(shù)據(jù)”選項(xiàng)卡中單擊“數(shù)據(jù)驗(yàn)證”按鈕，如圖3-1所示。打開如圖3-2所示的“數(shù)據(jù)驗(yàn)證”對(duì)話框，在“設(shè)置”“輸入信息”“出錯(cuò)警告”和“輸入法模式”4個(gè)選項(xiàng)卡中設(shè)置數(shù)據(jù)驗(yàn)證規(guī)則的相關(guān)選項(xiàng)，然后單擊“確定”按鈕，即可為選中的單元格設(shè)置數(shù)據(jù)驗(yàn)證規(guī)則。“數(shù)據(jù)驗(yàn)證”對(duì)話框中4個(gè)選項(xiàng)卡的功能如下：

·　“設(shè)置”選項(xiàng)卡：在該選項(xiàng)卡中設(shè)置數(shù)據(jù)的驗(yàn)證條件，在“允許”下拉列表中選擇一種驗(yàn)證條件，下方顯示所選驗(yàn)證條件的相關(guān)選項(xiàng)。“允許”下拉列表中包含8種數(shù)據(jù)驗(yàn)證條件功能如表3-1所示。如果選中“忽略空值”復(fù)選框，則無論為單元格設(shè)置哪種驗(yàn)證條件，空單元格都是有效的，否則在空單元格中按Enter鍵將顯示出錯(cuò)警告信息。

圖3-1　單擊“數(shù)據(jù)驗(yàn)證”按鈕

圖3-2　“數(shù)據(jù)驗(yàn)證”對(duì)話框

表3-1　8種數(shù)據(jù)驗(yàn)證條件的功能說明

·　“輸入信息”選項(xiàng)卡：在該選項(xiàng)卡中設(shè)置當(dāng)選擇包含數(shù)據(jù)驗(yàn)證規(guī)則的單元格時(shí)顯示的提示信息，以幫助用戶正確地輸入數(shù)據(jù)。

·　“出錯(cuò)警告”選項(xiàng)卡：在該選項(xiàng)卡中設(shè)置當(dāng)輸入不符合規(guī)則的數(shù)據(jù)時(shí)顯示的出錯(cuò)警告信息，以提醒用戶輸入正確的數(shù)據(jù)。

·　“輸入法模式”選項(xiàng)卡：在該選項(xiàng)卡中設(shè)置當(dāng)選擇特定的單元格時(shí)自動(dòng)切換到相應(yīng)的輸入法模式。

在“數(shù)據(jù)驗(yàn)證”對(duì)話框中的每個(gè)選項(xiàng)卡的左下角有一個(gè)“全部清除”按鈕，單擊該按鈕將清除所有選項(xiàng)卡中的設(shè)置。

3.1.2　只允許用戶從列表中選擇選項(xiàng)來輸入

使用“數(shù)據(jù)驗(yàn)證”功能中的“序列”數(shù)據(jù)驗(yàn)證條件，將為單元格提供包含指定選項(xiàng)的下拉列表，用戶通過選擇其中的選項(xiàng)輸入數(shù)據(jù)，從而達(dá)到限定輸入內(nèi)容的目的。

如圖3-3所示，需要根據(jù)A列中的商品名稱，在B列中輸入正確的商品類別（本例為“飲料”“果蔬”和“熟食”）。為了避免輸入無效的商品類別，可以為B列設(shè)置數(shù)據(jù)驗(yàn)證，操作步驟如下：

圖3-3　需要在B列中輸入商品的類別

（1）選擇要輸入商品類別的單元格區(qū)域，本例為B2:B6，然后在功能區(qū)的“數(shù)據(jù)”選項(xiàng)卡中單擊“數(shù)據(jù)驗(yàn)證”按鈕。

（2）打開“數(shù)據(jù)驗(yàn)證”對(duì)話框，在“設(shè)置”選項(xiàng)卡中進(jìn)行以下設(shè)置，如圖3-4所示。

·　在“允許”下拉列表中選擇“序列”。

·　在“來源”文本框中輸入“飲料，果蔬，熟食”，文字之間的逗號(hào)需要在英文半角狀態(tài)下輸入。如果要在文本框中移動(dòng)插入點(diǎn)的位置，需要按F2鍵進(jìn)入編輯模式。

·　選中“提供下拉箭頭”復(fù)選框。

提示：如果已將下拉列表包含的選項(xiàng)輸入到單元格區(qū)域中，則可以單擊“來源”文本框右側(cè)的折疊按鈕，在工作表中選擇該單元格區(qū)域，將其中的內(nèi)容導(dǎo)入“來源”文本框。

設(shè)置完成后單擊“確定”按鈕。當(dāng)選擇B2:B6區(qū)域中的任意一個(gè)單元格時(shí)，將自動(dòng)在單元格的右側(cè)顯示一個(gè)下拉按鈕，單擊該按鈕將打開一個(gè)下拉列表，其中包含“飲料”“果蔬”和“熟食”3個(gè)選項(xiàng)，選擇一個(gè)選項(xiàng)即可將其輸入到單元格中，如圖3-5所示。

圖3-4　設(shè)置數(shù)據(jù)驗(yàn)證條件

圖3-5　選擇下拉列表中的選項(xiàng)以將其輸入到單元格中

3.1.3　限制輸入的數(shù)值和日期范圍

在很多情況下，需要將輸入的內(nèi)容限制在一個(gè)有效的范圍內(nèi)，例如員工年齡、考試成績(jī)、發(fā)貨日期等。使用“整數(shù)”“小數(shù)”“日期”“時(shí)間”“文本長(zhǎng)度”等驗(yàn)證條件可以針對(duì)不同類型的數(shù)據(jù)設(shè)置輸入的限制范圍。

如圖3-6所示，需要在B列中輸入商品的發(fā)貨量，發(fā)貨量限制在1～30。為了避免輸入無效的數(shù)字，可以為B列設(shè)置數(shù)據(jù)驗(yàn)證，操作步驟如下：

圖3-6　需要在B列中輸入商品的發(fā)貨量

（1）選擇要輸入發(fā)貨量的單元格區(qū)域，本例為B2:B6，然后在功能區(qū)的“數(shù)據(jù)”選項(xiàng)卡中單擊“數(shù)據(jù)驗(yàn)證”按鈕。

（2）打開“數(shù)據(jù)驗(yàn)證”對(duì)話框，在“設(shè)置”選項(xiàng)卡中進(jìn)行以下設(shè)置，如圖3-7所示。

·　在“允許”下拉列表中選擇“整數(shù)”。

·　在“數(shù)據(jù)”下拉列表中選擇“介于”。

·　在“最小值”文本框中輸入1。

·　在“最大值”文本框中輸入30。

（3）切換到“輸入信息”選項(xiàng)卡，進(jìn)行以下設(shè)置，如圖3-8所示。

·　選中“選定單元格時(shí)顯示輸入信息”復(fù)選框。

·　在“標(biāo)題”文本框中輸入“輸入發(fā)貨量”。

·　在“輸入信息”文本框中輸入“請(qǐng)輸入1～30的數(shù)字”。

圖3-7　設(shè)置數(shù)據(jù)驗(yàn)證條件

圖3-8　設(shè)置提示信息

（4）切換到“出錯(cuò)警告”選項(xiàng)卡，進(jìn)行以下設(shè)置，如圖3-9所示。

·　選中“輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告”復(fù)選框。

·　在“樣式”下拉列表中選擇“停止”。

·　在“標(biāo)題”文本框中輸入“輸入有誤”。

·　在“錯(cuò)誤信息”文本框中輸入“輸入的數(shù)字超出有效范圍”。

（5）單擊“確定”按鈕，關(guān)閉“數(shù)據(jù)驗(yàn)證”對(duì)話框。

選擇B2:B6區(qū)域中的任意一個(gè)單元格時(shí)，將顯示如圖3-10所示的提示信息。如果輸入1～30的數(shù)字，該數(shù)字會(huì)被添加到單元格中。如果輸入其他數(shù)字，則在按下Enter鍵時(shí)將顯示預(yù)先定制好的出錯(cuò)警告信息，此時(shí)只能重新輸入或取消輸入，如圖3-11所示。

圖3-9　設(shè)置出錯(cuò)警告信息

圖3-10　選擇單元格時(shí)顯示提示信息

圖3-11　輸入無效數(shù)字時(shí)顯示的警告信息

3.1.4　禁止輸入重復(fù)內(nèi)容

在實(shí)際應(yīng)用中，可能需要輸入一些具有唯一性的內(nèi)容，例如員工編號(hào)或商品編號(hào)。為了避免輸入重復(fù)的內(nèi)容，可以使用“自定義”數(shù)據(jù)驗(yàn)證條件，通過設(shè)置公式和函數(shù)判斷輸入的數(shù)據(jù)是否發(fā)生重復(fù)并加以限制。

如圖3-12所示，需要在A列中輸入商品的編號(hào)。為了避免輸入重復(fù)的商品編號(hào)，可以為A列設(shè)置數(shù)據(jù)驗(yàn)證，操作步驟如下：

（1）選擇要輸入商品編號(hào)的單元格區(qū)域，本例為A2:A6，確保A2是活動(dòng)單元格，然后在功能區(qū)的“數(shù)據(jù)”選項(xiàng)卡中單擊“數(shù)據(jù)驗(yàn)證”按鈕。

（2）打開“數(shù)據(jù)驗(yàn)證”對(duì)話框，在“設(shè)置”選項(xiàng)卡中進(jìn)行以下設(shè)置，如圖3-13所示。

圖3-12　需要在A列中輸入不重復(fù)的商品編號(hào)

圖3-13　設(shè)置數(shù)據(jù)驗(yàn)證條件

·　在“允許”下拉列表中選擇“自定義”。

·　在“公式”文本框中輸入以下公式，其中的A2單元格需要使用相對(duì)引用。

     =COUNTIF($A$2:$A$6,A2)=1

提示：關(guān)于公式、相對(duì)引用和COUNTIF函數(shù)的更多內(nèi)容，請(qǐng)參考第4章。

（3）切換到“出錯(cuò)警告”選項(xiàng)卡，進(jìn)行以下設(shè)置，然后單擊“確定”按鈕，如圖3-14所示。

·　選中“輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告”復(fù)選框。

·　在“樣式”下拉列表中選擇“停止”。

·　在“標(biāo)題”文本框中輸入“編號(hào)有誤”。

·　在“錯(cuò)誤信息”文本框中輸入“輸入了重復(fù)的商品編號(hào)”。

在A2:A6區(qū)域中輸入商品編號(hào)時(shí)，如果輸入了重復(fù)的編號(hào)，則將顯示出錯(cuò)警告信息，此時(shí)只能重新輸入或取消輸入，如圖3-15所示。

圖3-14　設(shè)置出錯(cuò)警告信息

圖3-15　輸入重復(fù)編號(hào)時(shí)顯示出錯(cuò)警告信息

3.1.5　檢查并圈釋無效數(shù)據(jù)

如果在設(shè)置數(shù)據(jù)驗(yàn)證規(guī)則前，已經(jīng)在單元格中輸入了數(shù)據(jù)，則可以使用數(shù)據(jù)驗(yàn)證功能圈釋不符合規(guī)則的數(shù)據(jù)，以幫助用戶快速找到無效數(shù)據(jù)。圈釋數(shù)據(jù)前，需要先為數(shù)據(jù)區(qū)域設(shè)置數(shù)據(jù)驗(yàn)證規(guī)則，然后在功能區(qū)的“數(shù)據(jù)”選項(xiàng)卡中單擊“數(shù)據(jù)驗(yàn)證”按鈕上的下拉按鈕，在彈出的菜單中選擇“圈釋無效數(shù)據(jù)”命令，即可為選區(qū)中不符合驗(yàn)證規(guī)則的數(shù)據(jù)添加紅色標(biāo)識(shí)圈，如圖3-16所示。

圖3-16　圈釋無效數(shù)據(jù)

清除紅色標(biāo)識(shí)圈的一種方法是修改數(shù)據(jù)以使其符合驗(yàn)證規(guī)則，紅色標(biāo)識(shí)圈會(huì)自動(dòng)消失；另一種方法是在功能區(qū)的“數(shù)據(jù)”選項(xiàng)卡中單擊“數(shù)據(jù)驗(yàn)證”按鈕上的下拉按鈕，然后在彈出的菜單中選擇“清除驗(yàn)證標(biāo)識(shí)圈”命令。

3.1.6　管理數(shù)據(jù)驗(yàn)證

如果要修改現(xiàn)有的數(shù)據(jù)驗(yàn)證規(guī)則，需要先選擇包含數(shù)據(jù)驗(yàn)證規(guī)則的單元格，然后打開“數(shù)據(jù)驗(yàn)證”對(duì)話框并進(jìn)行所需的修改。

如果為多個(gè)單元格設(shè)置了相同的數(shù)據(jù)驗(yàn)證規(guī)則，則可以先修改其中任意一個(gè)單元格的數(shù)據(jù)驗(yàn)證規(guī)則，然后在關(guān)閉“數(shù)據(jù)驗(yàn)證”對(duì)話框前，在“設(shè)置”選項(xiàng)卡中選中“對(duì)有同樣設(shè)置的所有其他單元格應(yīng)用這些更改”復(fù)選框，即可將當(dāng)前設(shè)置結(jié)果應(yīng)用到其他包含相同數(shù)據(jù)驗(yàn)證規(guī)則的單元格中，如圖3-17所示。

當(dāng)復(fù)制包含數(shù)據(jù)驗(yàn)證規(guī)則的單元格時(shí)，將同時(shí)復(fù)制該單元格中包含的內(nèi)容和數(shù)據(jù)驗(yàn)證規(guī)則。如果只想復(fù)制單元格中的數(shù)據(jù)驗(yàn)證規(guī)則，則可以在執(zhí)行復(fù)制命令后，右擊要粘貼的單元格，然后在彈出的菜單中選擇“選擇性粘貼”命令，在打開的對(duì)話框中選中“驗(yàn)證”單選按鈕，最后單擊“確定”按鈕，如圖3-18所示。

圖3-17　批量修改數(shù)據(jù)驗(yàn)證規(guī)則的方法

圖3-18　只粘貼數(shù)據(jù)驗(yàn)證規(guī)則

注意：如果復(fù)制一個(gè)不包含數(shù)據(jù)驗(yàn)證規(guī)則的單元格，并將其粘貼到包含數(shù)據(jù)驗(yàn)證規(guī)則的單元格中，則將覆蓋目標(biāo)單元格中的數(shù)據(jù)驗(yàn)證規(guī)則。

如果要?jiǎng)h除單元格中的數(shù)據(jù)驗(yàn)證規(guī)則，可以打開“數(shù)據(jù)驗(yàn)證”對(duì)話框，然后在任意一個(gè)選項(xiàng)卡中單擊“全部清除”按鈕。當(dāng)工作表中包含不止一種數(shù)據(jù)驗(yàn)證規(guī)則時(shí)，刪除所有數(shù)據(jù)驗(yàn)證規(guī)則的操作步驟如下：

（1）單擊位于行號(hào)和列標(biāo)交叉位置處的全選按鈕（一個(gè)三角形標(biāo)記），選中工作表中的所有單元格，如圖3-19所示。

（2）在功能區(qū)的“數(shù)據(jù)”選項(xiàng)卡中單擊“數(shù)據(jù)驗(yàn)證”按鈕，將顯示如圖3-20所示的提示信息，單擊“確定”按鈕。

圖3-19　位于左上角的全選按鈕

圖3-20　刪除所有數(shù)據(jù)驗(yàn)證規(guī)則時(shí)的提示信息

（3）打開“數(shù)據(jù)驗(yàn)證”對(duì)話框，不做任何設(shè)置，直接單擊“確定”按鈕，即可刪除當(dāng)前工作表中包含的所有數(shù)據(jù)驗(yàn)證規(guī)則。