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

1.配合“超級(jí)表”做下拉列表

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

這個(gè)時(shí)候，多數(shù)人的做法可能是點(diǎn)開數(shù)據(jù)有效性對(duì)話框后，直接手動(dòng)輸入下拉列表中需要的內(nèi)容。

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

在基礎(chǔ)信息表的A1單元格處點(diǎn)擊“插入”-“表格”。

彈出“創(chuàng)建表”對(duì)話框，直接單擊“確定”即可在表格中可以看到超級(jí)表。

在表格中直接輸入公司已有的崗位，超級(jí)表會(huì)自動(dòng)擴(kuò)展，如下所示。

選中整個(gè)數(shù)據(jù)區(qū)域，在表格上方名稱框?yàn)檫@列數(shù)據(jù)設(shè)置一個(gè)名字，如“崗位”，按回車確定。

設(shè)置好名字后，點(diǎn)擊“公式”-“用于公式”，在下拉菜單中就可以看到我們剛才自定義的名稱，可以隨時(shí)調(diào)用。在以后的函數(shù)學(xué)習(xí)中也會(huì)用到這個(gè)。

回到最初的工資表中，選中E列數(shù)據(jù)，點(diǎn)擊“數(shù)據(jù)”-“數(shù)據(jù)有效性”。

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

在“來源”下方輸入框中單擊，然后點(diǎn)擊“公式”選項(xiàng)卡“用于公式”-“崗位”。此時(shí)就可以看到在“來源”中調(diào)用了“崗位”表格區(qū)域。

點(diǎn)擊“確定”后，E列數(shù)據(jù)后都出現(xiàn)了一個(gè)下拉按鈕，點(diǎn)擊按鈕，可在下拉列表中選擇崗位。

這時(shí)若有新增崗位，直接在基礎(chǔ)信息表中的“崗位”超級(jí)表里增加內(nèi)容。如下圖，我在A12單元格輸入新增的研發(fā)總監(jiān)的崗位。

回車后，可以看到表格自動(dòng)進(jìn)行了擴(kuò)展，新增崗位被收錄進(jìn)了“崗位”表格區(qū)域中。

此時(shí)我們?cè)倩氐焦べY表，查看E列任意單元格的下拉列表，可以看到列表末尾增加了“研發(fā)總監(jiān)”。

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

2.人性化警告信息

如下圖所示的表格中，選中C列數(shù)據(jù)，設(shè)置為文本格式。因?yàn)槲覀兊纳矸葑C號(hào)都是18位的超長(zhǎng)數(shù)字，不會(huì)參與數(shù)學(xué)運(yùn)算，所以可以提前設(shè)置整列為文本格式。

調(diào)出數(shù)據(jù)有效性對(duì)話框，輸入文本長(zhǎng)度-等于-18。

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

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

選中身份證列的數(shù)據(jù)后，調(diào)出數(shù)據(jù)有效性對(duì)話框，點(diǎn)擊“出錯(cuò)警告”選項(xiàng)卡，我么可以看到下方所示的對(duì)話框。

點(diǎn)擊“樣式”下拉列表，有“停止”、“警告”、“信息”三種方式?！巴Ｖ埂北硎井?dāng)用戶輸入信息錯(cuò)誤時(shí)，信息無法錄入單元格。“警告”表示用戶輸入信息錯(cuò)誤時(shí)，對(duì)用戶進(jìn)行提醒，用戶再次點(diǎn)擊確定后，信息可以錄入單元格。“信息”表示用戶輸入信息錯(cuò)誤時(shí)，僅僅給予提醒，信息已經(jīng)錄入了單元格。

在對(duì)話框右邊，我們可以設(shè)置警告信息的標(biāo)題和提示內(nèi)容，如下所示。

點(diǎn)擊確定后，在C2單元格輸入數(shù)字不是18位時(shí)，就會(huì)彈出我們自定義設(shè)置的提示框。

3．圈釋錯(cuò)誤數(shù)據(jù)和空單元格

如下圖所示，當(dāng)我把C列設(shè)置為“警告”類信息后，用戶輸入錯(cuò)誤數(shù)據(jù)，再次點(diǎn)擊確定后，還是保存了下來。這時(shí)我需要將錯(cuò)誤數(shù)據(jù)標(biāo)注出來。

選中C列數(shù)據(jù)，點(diǎn)擊“數(shù)據(jù)”-“數(shù)據(jù)有效性”-“圈釋無效數(shù)據(jù)”。

這時(shí)身份證列輸入的錯(cuò)誤數(shù)據(jù)都會(huì)被標(biāo)注出來。但是沒有錄入信息的空單元格卻沒有標(biāo)注出來。

若想將沒有錄入信息的空單元格也圈釋出來，我們點(diǎn)擊“數(shù)據(jù)有效性”下拉列表中的“清除無效數(shù)據(jù)標(biāo)識(shí)圈”先清除標(biāo)識(shí)圈。

然后選中數(shù)據(jù)區(qū)域，調(diào)出數(shù)據(jù)有效性對(duì)話框，在“設(shè)置”對(duì)話框中取消勾選“忽略空值”。

此時(shí)再進(jìn)行前面的圈釋操作，可以看到錄入錯(cuò)誤信息的單元格和空單元格都被圈釋出來了。

當(dāng)我們公司人數(shù)有幾百個(gè)，用這種方法就可以輕松找出其中被漏掉的單元格。

今天的教程就到這里，你學(xué)會(huì)了嗎？利用好數(shù)據(jù)有效性，在統(tǒng)計(jì)數(shù)據(jù)時(shí)可以節(jié)約很多時(shí)間。

＊＊＊＊部落窩教育－excel數(shù)據(jù)有效性應(yīng)用妙招＊＊＊＊

原創(chuàng)：胡萍/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）

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

微信公眾號(hào)：exceljiaocheng