在之前的文章《實用Excel技巧分享:如何提取數字?在》中,我們講解了幾類比較常見的資料擷取情況。而今天我們來聊聊Excel的「資料有效性」功能,分享3個小招讓資料有效性更有高效,快來學習學習!
大家在工作中使用「數據有效性」這個功能應該還蠻多的吧?多數人常用它來做選擇下拉、資料輸入限制等。今天瓶子向大家介紹幾個看起來不起眼但實際上很有效率、很溫情的功能。
1、配合「超級表」做下拉列表
如下圖所示的表格,需要在E列製作一個下拉列表,這樣就不必手動輸入崗位,可直接在下拉清單中選擇。
這個時候,多數人的做法可能是在點開資料有效性對話方塊後,直接手動輸入下拉清單中需要的內容。
這樣做有一個弊端,當新增加職位時,需要重新設定資料有效性。我們可以建立專門的表格作為此處的來源。在工作簿中建立一個基礎資訊表。如下圖所示,將表2命名為基礎資訊表。
在基礎資訊表的A1儲存格點選「插入」-「表格」。
彈出「建立表」對話框,直接按一下「確定」即可在表格中可以看到超級表。
在表格中直接輸入公司已有的職位,超級表會自動擴展,如下圖所示。
選取整個資料區域,在表格上方名稱方塊為這列資料設定一個名字,如“崗位”,按回車決定。
設定好名字後,點擊“公式”-“用於公式”,在下拉式選單中就可以看到我們剛才自訂的名稱,可以隨時呼叫。在以後的函數學習中也會用到這個。
回到最初的薪資表中,選取E列數據,點選「數據」-「數據有效性」。
在彈出的對話方塊中,在「允許」下拉式選單中選擇「序列」。
在「來源」下方輸入方塊中按一下,然後點擊「公式」標籤「用於公式」-「崗位」。此時就可以看到在「來源」中呼叫了「崗位」表格區域。
點選「確定」後,E列資料後都出現了下拉按鈕,點選按鈕,可在下拉清單中選擇崗位。
這時若有新增職位,直接在基礎資訊表中的「職位」超級表裡增加內容。如下圖,我在A12單元格輸入新增的研發總監的職位。
回車後,可以看到表格自動進行了擴展,新增職位被收錄進了「崗位」表格區域。
此時我們再回到薪資表,查看E列任意單元格的下拉列表,可以看到列表末尾增加了「研發總監」。
使用excel2010版本的夥伴注意,使用超級表後,可能會出現資料有效性「失效」的情況,這時只需要取消勾選「忽略空值」即可。
2、人性化警告訊息
如下圖所示的表格中,選取C列數據,設定為文字格式。因為我們的身分證號碼都是18位元的超長數字,不會參與數學運算,所以可以事先設定整列為文字格式。
調出資料有效性對話框,輸入文字長度-等於-18。
在C2儲存格輸入一串沒有18位元的數字時,就會彈出警告訊息如下圖所示。
此時的警告訊息看起來很生硬,而且有可能使用表格的人看不懂,我們可以自訂一個人性化的警告訊息讓人們知道如何操作。
選取身分證列的資料後,調出資料有效性對話框,點選「出錯警告」選項卡,我麼可以看到下方所示的對話框。
點擊「樣式」下拉列表,有「停止」、「警告」、「訊息」三種方式。 「停止」表示當使用者輸入訊息錯誤時,訊息無法輸入儲存格。 「警告」表示使用者輸入訊息錯誤時,對使用者進行提醒,使用者再次點選確定後,訊息可以輸入儲存格。 「訊息」表示使用者輸入訊息錯誤時,僅給予提醒,訊息已經輸入了儲存格。
在對話方塊右邊,我們可以設定警告訊息的標題和提示內容,如下所示。
點擊確定後,在C2儲存格輸入數字不是18位元時,就會跳出我們自訂設定的提示框。
3、圈釋錯誤資料與空白儲存格
如下圖所示,當我把C列設定為「警告」類別資訊後,使用者輸入錯誤數據,再次點選確定後,還是儲存了下來。這時我需要將錯誤資料標註出來。
選取C列數據,點選「數據」-「數據有效性」-「圈釋無效數據」。
這時身分證列輸入的錯誤資料都會被標註出來。但是沒有錄入訊息的空白儲存格卻沒有標註出來。
若想將沒有輸入訊息的空白儲存格也圈釋出來,我們點選「資料有效性」下拉清單中的「清除無效資料標識圈」先清除標識圈。
然後選取資料區域,調出資料有效性對話框,在「設定」對話框中取消勾選「忽略空值」。
此時再進行前面的圈釋操作,可以看到錄入錯誤訊息的儲存格和空白儲存格都被圈釋出來了。
當我們公司人數有幾百個,用這個方法就可以輕鬆找出其中被遺漏的儲存格。
今天的教學就到這裡,你學會了嗎?利用好數據有效性,統計數據時可以節省很多時間。
相關學習推薦:excel教學
以上是實用Excel技巧分享:「資料有效性」可以這樣用!的詳細內容。更多資訊請關注PHP中文網其他相關文章!