excel應用技巧：如何高效應用資料有效性功能

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

1.配合“超級表”做下拉選單

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

這個時候，多數人的做法可能是點開資料有效性對話方塊後，直接手動輸入下拉選單中需要的內容。

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

在基礎資訊表的A1單元格處點選“插入”-“表格”。

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

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

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

設定好名字後，點選“公式”-“用於公式”，在下拉選單中就可以看到我們剛才自定義的名稱，可以隨時呼叫。在以後的函式學習中也會用到這個。

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

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

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

點選“確定”後，E列資料後都出現了一個下拉按鈕，點選按鈕，可在下拉選單中選擇崗位。

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

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

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

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

2.人性化警告資訊

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

調出資料有效性對話方塊，輸入文字長度-等於-18。

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

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

選中身份證列的資料後，調出資料有效性對話方塊，點選“出錯警告”選項卡，我麼可以看到下方所示的對話方塊。

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

在對話方塊右邊，我們可以設定警告資訊的標題和提示內容，如下所示。

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

3．圈釋錯誤資料和空單元格

如下圖所示，當我把C列設定為“警告”類資訊後，使用者輸入錯誤資料，再次點選確定後，還是儲存了下來。這時我需要將錯誤資料標註出來。

選中C列資料，點選“資料”-“資料有效性”-“圈釋無效資料”。

這時身份證列輸入的錯誤資料都會被標註出來。但是沒有錄入資訊的空單元格卻沒有標註出來。

若想將沒有錄入資訊的空單元格也圈釋出來，我們點選“資料有效性”下拉選單中的“清除無效資料標識圈”先清除標識圈。

然後選中資料區域，調出資料有效性對話方塊，在“設定”對話方塊中取消勾選“忽略空值”。

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

當我們公司人數有幾百個，用這種方法就可以輕鬆找出其中被漏掉的單元格。

今天的教程就到這裡，你學會了嗎？利用好資料有效性，在統計資料時可以節約很多時間。

＊＊＊＊部落窩教育－excel資料有效性應用妙招＊＊＊＊

原創：胡萍/部落窩教育（未經同意，請勿轉載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號：exceljiaocheng