大家在工作中使用“數據有效性”這個功能應該還挺多的吧？多數人常用它來做選擇下拉、數據輸入限制等。今天向大家介紹幾個看起來不起眼但實際很高效的功能。

1配合“超級表”做下拉列表

如下圖所示的表格，需要在E列制作一個下拉列表，這樣就不必手動輸入崗位，可直接在下拉列表中選擇。

?

這個時候，多數人的做法可能是點開數據有效性對話框后，直接手動輸入下拉列表中需要的內容。

?

這樣做有一個弊端，當新增加崗位時，需要重新設置數據有效性。我們可以建立專門的表作為此處的來源。在工作簿中建立一個基礎信息表。如下圖所示，將表2命名為基礎信息表。

?

在基礎信息表的A1單元格處點擊“插入”-“表格”。

?

彈出“創建表”對話框，直接單擊“確定”即可在表格中可以看到超級表。

?

在表格中直接輸入公司已有的崗位，超級表會自動擴展，如下所示。

?

選中整個數據區域，在表格上方名稱框為這列數據設置一個名字，如“崗位”，按回車確定。

?

設置好名字后，點擊“公式”-“用于公式”，在下拉菜單中就可以看到我們剛才自定義的名稱，可以隨時調用。在以后的函數學習中也會用到這個。

?

回到最初的工資表中，選中E列數據，點擊“數據”-“數據有效性”。

?

在彈出的對話框中，在“允許”下拉菜單中選擇“序列”。

?

在“來源”下方輸入框中單擊，然后點擊“公式”選項卡“用于公式”-“崗位”。此時就可以看到在“來源”中調用了“崗位”表格區域。

?

點擊“確定”后，E列數據后都出現了一個下拉按鈕，點擊按鈕，可在下拉列表中選擇崗位。

?

這時若有新增崗位，直接在基礎信息表中的“崗位”超級表里增加內容。如下圖，我在A12單元格輸入新增的研發總監的崗位。

?

回車后，可以看到表格自動進行了擴展，新增崗位被收錄進了“崗位”表格區域中。

?

此時我們再回到工資表，查看E列任意單元格的下拉列表，可以看到列表末尾增加了“研發總監”。

?

使用excel2010版本的伙伴注意，使用超級表后，可能會出現數據有效性“失效”的情況，這時只需要取消勾選“忽略空值”即可。

?

2人性化警告信息

如下圖所示的表格中，選中C列數據，設置為文本格式。因為我們的身份證號都是18位的超長數字，不會參與數學運算，所以可以提前設置整列為文本格式。

?

調出數據有效性對話框，輸入文本長度-等于-18。

?

在C2單元格輸入一串沒有18位的數字時，就會彈出警告信息如下圖所示。

?

此時的警告信息看起來很生硬，而且有可能使用表格的人看不懂，我們可以自定義一個人性化的警告信息讓人知道如何操作。

選中身份證列的數據后，調出數據有效性對話框，點擊“出錯警告”選項卡，我們可以看到下方所示的對話框。

?

點擊“樣式”下拉列表，有“停止”、“警告”、“信息”三種方式。“停止”表示當用戶輸入信息錯誤時，信息無法錄入單元格。“警告”表示用戶輸入信息錯誤時，對用戶進行提醒，用戶再次點擊確定后，信息可以錄入單元格。“信息”表示用戶輸入信息錯誤時，僅僅給予提醒，信息已經錄入了單元格。

?

在對話框右邊，我們可以設置信息的標題和提示內容，如下所示。

?

點擊確定后，在C2單元格輸入數字不是18位時，就會彈出我們自定義設置的提示框。

?

3圈釋錯誤數據和空單元格

如下圖所示，當我把C列設置為“警告”類信息后，用戶輸入錯誤數據，再次點擊確定后，還是保存了下來。這時我需要將錯誤數據標注出來。

?

選中C列數據，點擊“數據”-“數據有效性”-“圈釋無效數據”。

?

這時身份證列輸入的錯誤數據都會被標注出來。但是沒有錄入信息的空單元格卻沒有標注出來。

?

若想將沒有錄入信息的空單元格也圈釋出來，我們點擊“數據有效性”下拉列表中的“清除無效數據標識圈”先清除標識圈。

?

然后選中數據區域，調出數據有效性對話框，在“設置”對話框中取消勾選“忽略空值”。

?

此時再進行前面的圈釋操作，可以看到錄入錯誤信息的單元格和空單元格都被圈釋出來了。

?

當我們公司人數有幾百個，用這種方法就可以輕松找出其中被漏掉的單元格。今天的教程就到這里，你學會了嗎？利用好數據有效性，在統計數據時可以節約很多時間。