Excel 2016︱數(shù)據(jù)驗證的典型應(yīng)用
如圖 35-3 所示，需要在員工信息表中輸入員工年齡。因為員工年齡不會小于 18 歲，也不會大于 60 歲，因此輸入員工年齡的區(qū)間應(yīng)該是 18~60 之間的整數(shù)。通過設(shè)置數(shù)據(jù)驗證，可以限制輸入數(shù)據(jù)的區(qū)間范圍。 選中 B2:B10 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。 在【數(shù)據(jù)驗證】對話框的【設(shè)置】選項卡下： （1）單擊【允許】下拉按鈕，在下拉列表中選擇【整數(shù)】選項。 （2）單擊【數(shù)據(jù)】下拉按鈕，在下拉列表中選擇【介于】選項。 也可以單擊【最小值】和【最大值】右側(cè)的折疊按鈕選擇單元格地址，以單元格中的數(shù)據(jù)作為參照，最后單擊【確定】按鈕，如圖 35-4 所示。 設(shè)置完成后，如果在 B2:B10 單元格區(qū)域輸入 18~60 之外的內(nèi)容，將彈出如圖 35-5 所示的警告對話框，拒絕用戶輸入數(shù)據(jù)。 如果單擊對話框中的【重試】按鈕，當(dāng)前單元格進(jìn)入編輯狀態(tài)，用戶可繼續(xù)嘗試輸入其他內(nèi)容。如果單擊【取消】按鈕，則結(jié)束當(dāng)前的輸入操作。 如圖 35-6 所示，需要在員工信息表中輸入身份證號碼。使用數(shù)據(jù)驗證功能，可以避免信息重復(fù)輸入。 選中需要輸入身份證號的 C2:C10 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。 在【設(shè)置】選項卡下單擊【允許】下拉按鈕，在下拉列表中選擇【自定義】選項，在【公式】編輯框中輸入以下公式，最后單擊【確定】按鈕，如圖35-7 所示。 因為身份證號碼是 18 位數(shù)字，而 Excel 的數(shù)字處理精度是 15 位，因此對身份證號碼中 15 位以后的數(shù)字都視為 0 處理。這種情況下，COUNTIF 函數(shù)對前 15 位相同的身份證號碼，無論后 3 位是否一致，都會判斷為相同。 COUNTIF 函數(shù)的第二參數(shù)使用 C2&'*'，表示查找以 C2 單元格內(nèi)容開始的文本，最終返回 C列單元格區(qū)域中該身份證號碼的個數(shù)，如果大于 1，則表示該身份證號碼重復(fù)。 如圖 35-8 所示，需要在員工信息表中輸入性別。使用數(shù)據(jù)驗證功能，可以實現(xiàn)下拉菜單式輸入。 選中需要輸入性別信息的 D2:D10 單元格區(qū)域。依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。 在【設(shè)置】選項卡下單擊【允許】下拉按鈕，在下拉列表中選擇【序列】選項。 保留右側(cè)的【提供下拉箭頭】復(fù)選框的選中，在【來源】編輯框中輸入“男 , 女”，最后單擊【確定】按鈕，如圖 35-9 所示。 設(shè)置完成后，單擊單元格右側(cè)的下拉按鈕，即可在下拉菜單中選擇輸入內(nèi)容。 結(jié)合自定義名稱和 INDIRECT 函數(shù)，可以方便地創(chuàng)建二級下拉列表，二級下拉列表的選項能夠根據(jù)第一個下拉列表輸入的內(nèi)容調(diào)整范圍。 如圖 35-10 所示，在客戶信息表的 B 列使用下拉菜單選擇不同的省份，C 列的下拉菜單中就會出現(xiàn)對應(yīng)省份的部分城市名稱。 準(zhǔn)備一個包含客戶所在省份和對應(yīng)城市名稱的對照表，如圖 35-11 所示。 切換到“客戶區(qū)域?qū)φ毡怼惫ぷ鞅?，?<F5> 功能鍵調(diào)出【定位】對話框，單擊【定位條件】按鈕，在彈出的【定位條件】對話框中選中【常量】單選按鈕，然后單擊【確定】按鈕。此時表格中的常量全部被選中，如圖 35-12 所示。 依次單擊【公式】→【根據(jù)所選內(nèi)容創(chuàng)建】按鈕，在彈出的【以選定區(qū)域創(chuàng)建名稱】對話框中選中【首行】復(fù)選框，然后單擊【確定】按鈕，完成創(chuàng)建定義名稱，如圖 35-13 所示。 切換到 Sheet1 工作表，選中要輸入省份的 B2:B8 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。在【允許】下拉列表中選擇【序列】選項，單擊【來源】編輯框右側(cè)的折疊按鈕，選中“客戶區(qū)域?qū)φ毡怼惫ぷ鞅淼?A1:F1 單元格區(qū)域，單擊【確定】按鈕關(guān)閉對話框，如圖 35-14 所示。 選中要輸入城市名稱的 C2:C8 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。在【允許】下拉列表中選擇【序列】，在【來源】編輯框輸入以下公式，單擊【確定】按鈕關(guān)閉對話框，如圖 35-15 所示。 此時會彈出【源當(dāng)前包含錯誤。是否繼續(xù)？】的警告對話框，這是因為 B2 單元格還沒有輸入省份內(nèi)容，INDIRECT 函數(shù)無法返回正確的引用結(jié)果，單擊【確定】按鈕即可，如圖 35-16 所示。 設(shè)置完成后，在 B 列單元格選擇不同的省份，C 列的城市下拉列表就會動態(tài)變化。 但是通過這樣設(shè)置的數(shù)據(jù)驗證，在 B 列沒有輸入省份的情況下，C 列可以手工輸入任意內(nèi)容，且不會有任何錯誤提示，如圖 35-17 所示。 可以選中 C2 單元格，打開【數(shù)據(jù)驗證】對話框。取消【忽略空值】復(fù)選框的選中，再選中【對有同樣設(shè)置的所有其他單元格應(yīng)用這些更改】復(fù)選框，單擊【確定】按鈕關(guān)閉對話框，如圖 35-18所示。 設(shè)置完成后，再次嘗試在 C 列手工輸入不符合項的內(nèi)容，Excel 就會彈出警告對話框，拒絕用戶輸入。 使用 35.2.4 小節(jié)的方法創(chuàng)建二級下拉菜單，其優(yōu)點是操作簡單，缺點是不能隨著數(shù)據(jù)的變化實現(xiàn)動態(tài)引用。如果在“客戶區(qū)域?qū)φ毡怼敝刑砑涌蛻粜畔?，Sheet1 中的下拉菜單將無法自動更新。 示例35-5 制作可動態(tài)擴(kuò)展的二級下拉菜單 仍以 35.2.4 小節(jié)中的數(shù)據(jù)為例，學(xué)習(xí)制作動態(tài)二級下拉菜單的方法，操作步驟如下。 切換到“客戶區(qū)域?qū)φ毡怼惫ぷ鞅?。依次?zhí)行【公式】→【定義名稱】命令，彈出【新建名稱】對話框，在【名稱】文本框中輸入“客戶區(qū)域”，在【引用位置】編輯框內(nèi)輸入以下公式，單擊【確定】按鈕，如圖 35-19 所示。 =OFFSET($A$1,,,,COUNTA($1:$1)) 切換到 Sheet1 工作表，選中要輸入省份的 B2:B10 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。將【允許】類型設(shè)置為【序列】，在【來源】編輯框內(nèi)輸入以下公式，如圖 35-20 所示。 選中 C2 單元格，依次執(zhí)行【公式】→【定義名稱】命令，彈出【新建名稱】對話框，在【名稱】文本框中輸入“客戶城市”，在【引用位置】編輯框內(nèi)輸入以下公式，單擊【確定】按鈕，如圖 35-21 所示。 =OFFSET( 客 戶 區(qū) 域 對 照 表 !$A$2,,MATCH($B2, 客 戶 區(qū) 域 對 照 表 !$1:$1,)-1,COUNTA(OFFSET(客戶區(qū)域?qū)φ毡?$A$2,,MATCH($B2,客戶區(qū)域?qū)φ毡?$1:$1,)-1,100))) 選中要輸入城市的 C2:C10 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。將【允許】類型設(shè)置為【序列】，在【來源】編輯框內(nèi)輸入以下公式，如圖 35-22 所示。 設(shè)置完成后，在 B 列選擇客戶省份，單擊 C 列單元格 中的下拉按鈕，就可以選擇對應(yīng)的城市選項。如果“客戶區(qū)域?qū)φ毡怼敝械臄?shù)據(jù)發(fā)生變化，下拉列表中的選項會自動更新。 在【數(shù)據(jù)驗證】的【輸入法模式】選項卡下，有【隨意】【打開】和【關(guān)閉（英文模式）】3 個模式選項，默認(rèn)選項為【隨意】，如圖 35-23 所示。 在使用此選項時，需要計算機(jī)內(nèi)安裝有英文鍵盤輸入法，并且在計算機(jī)的【設(shè)置】→【時間和語言】→【區(qū)域和語言】選項中，將中文設(shè)置為系統(tǒng)默認(rèn)語言，如圖 35-24 所示。 如圖 35-25 所示，需要在設(shè)備登記表中分別輸入設(shè)備名稱和規(guī)格型號。設(shè)備名稱為中文，規(guī)格型號為英文字母和數(shù)字的組合。設(shè)置輸入法模式后，在不同列輸入內(nèi)容時，系統(tǒng)能夠自動切換輸入法模式。 選中需要輸入中文的 A2:A6 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。切換到【輸入法模式】選項卡下，在【模式】下拉列表中選擇【打開】選項，單擊【確定】按鈕，關(guān)閉對話框。 選中需要輸入英文 + 數(shù)字的 B2:B6 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。切換到【輸入法模式】選項卡下，在【模式】下拉列表中選擇【關(guān)閉（英文模式）】選項，最后單擊【確定】按鈕完成設(shè)置。 通常情況下，用戶使用數(shù)據(jù)驗證來限制輸入的內(nèi)容。對于已經(jīng)輸入的內(nèi)容，也可以使用圈釋無效數(shù)據(jù)功能，方便地查找出不符合要求的數(shù)據(jù)。 如圖 35-26 所示，是某單位員工信息表的部分內(nèi)容，利用【圈釋無效數(shù)據(jù)】功能，能夠檢查工號是否為重復(fù)輸入。 選中 B2:B9 單元格區(qū)域，依次執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)驗證】命令，打開【數(shù)據(jù)驗證】對話框。設(shè)置數(shù)據(jù)驗證的【允許】規(guī)則為【自定義】，在公式編輯框中輸入以下公式，限制 B 列輸入的內(nèi)容不能有重復(fù)項，如圖 35-27 所示。 依次選擇【數(shù)據(jù)】→【數(shù)據(jù)驗證】→【圈釋無效數(shù)據(jù)】，如圖 35-28 所示。 設(shè)置完成后，在不符合要求的有重復(fù)數(shù)據(jù)單元格上，都添加了紅色的標(biāo)識圈。將單元格修改為符合規(guī)則的數(shù)據(jù)后，標(biāo)識圈將不再顯示。 如需清除標(biāo)識圈，可以在【數(shù)據(jù)驗證】下拉菜單中選擇【清除驗證標(biāo)識圈】命令或按 <Ctrl+S> 組合鍵。
---------------------------------------------------------------------
本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點擊舉報 。
