編按：

相信在座的小伙伴都有錄錯數(shù)據(jù)的經(jīng)歷，當(dāng)時可能就是腦子走了下神，眼睛突然一花，就犯了錯。要是有什么東西能在我們犯錯的時候，提醒下我們就好了…不用擔(dān)心~今天小編就教大家做一個紅綠燈的提醒效果，數(shù)據(jù)錄錯亮紅燈，數(shù)據(jù)錄對就亮綠燈。是不是很神奇呢？趕緊和小編一起來看看吧~

哈嘍，大家好！我們平時人工錄入較長的文本數(shù)據(jù)時，稍不注意就容易出錯。為了避免出錯，通常我們會提前對單元格設(shè)置數(shù)據(jù)驗證。有些時候，我們還會考慮列與列之間的關(guān)系，根據(jù)列關(guān)系自動判定數(shù)據(jù)的對錯。

比如下表,款號、貨號、色號、條碼的信息均存在一定的關(guān)聯(lián)。貨號的前6位表示款號，從第8位開始的兩位表示色號；條碼的前6位表示款號，從第7位開始的兩位表示色號。是不是光聽著就頭大了(T ^ T)~

我們希望如果錄入的數(shù)據(jù)滿足列與列之間的關(guān)系，表格亮綠燈，表示數(shù)據(jù)錄入正確，反之亮紅燈，如下，應(yīng)該怎么實現(xiàn)呢？

首先我們可以根據(jù)各列之間的關(guān)系，設(shè)置公式分別判斷錄入的數(shù)據(jù)是否有誤。

① 貨號前6位=款號

在F2單元格輸入公式：=LEFT(B2,6)=A2，下拉填充公式。用LEFT函數(shù)在貨號列單元格左取6位，判斷是否等于款號。等于則返回TRUE，不等于則返回FALSE。

② 貨號從第8位開始的兩位=色號

在G2單元格輸入公式：=MID(B2,8,2)=D2&""，下拉填充公式。用MID函數(shù)從貨號中間的第8位開始截取兩位，判斷是否等于色號。等于則返回TRUE，不等于則返回FALSE。由于MID是文本函數(shù)，其輸出的結(jié)果都是文本，而色號列中既有文本數(shù)據(jù)又有數(shù)字數(shù)據(jù)。所以為了保證數(shù)據(jù)格式一致，我們在單元格D2后面連接了一個空，將D列（色號列）的數(shù)據(jù)統(tǒng)一轉(zhuǎn)換成文本。如果直接用=MID(B2,8,2)=D2，則可能會因為格式不匹配，出現(xiàn)錯誤判斷，如下圖：

③ 條碼前6位=款號

在H2單元格輸入公式：=LEFT(C2,6)=A2，下拉填充公式。用LEFT函數(shù)在條碼列單元格左取6位，判斷是否等于款號。等于則返回TRUE，不等于則返回FALSE。

④ 條碼從第7位開始的兩位=色號

在I2單元格輸入公式：=MID(C2,7,2)=D2&""，下拉填充公式。用MID函數(shù)從條碼中間第7位開始截取兩位，判斷是否等于色號?；谕瑯拥脑颍覀冊趩卧?span>D2后面連接了一個空,使D列（色號列）的數(shù)據(jù)轉(zhuǎn)換為文本數(shù)據(jù)。

根據(jù)需求，只有錄入的數(shù)據(jù)同時符合上述四種條件，錄入才算正確。對于判斷是否同時滿足多個條件，我們就要用上AND函數(shù)咯~

將這4個邏輯值作為AND函數(shù)的參數(shù)，代表著只有同時滿足這四種條件時，才算TRUE，只要有一個條件不滿足，那都是FALSE。

在J2單元格輸入公式：=AND(F2:I2)，下拉填充公式。

現(xiàn)在我們得到的數(shù)據(jù)是邏輯值，不方便我們后續(xù)的使用，所以我們需要乘以1，將邏輯值轉(zhuǎn)換成數(shù)字。此時TRUE相當(dāng)于1，FALSE相當(dāng)于0。

接著我們做紅綠燈提醒效果。

選中最后一列數(shù)據(jù)，在“開始”選項卡，點擊“條件格式”-“圖標(biāo)集”。在“圖標(biāo)集”中選擇紅綠燈樣式。

效果如下：

這樣看著似乎差不多了，但是這個1、0看著總覺得不是很美觀。我們設(shè)置一下圖標(biāo)集樣式。

選中J列，點擊“條件格式”-“管理規(guī)則”，點擊“編輯規(guī)則”，勾選“僅顯示圖標(biāo)”，點擊“確定”。

最后將圖標(biāo)居中顯示，效果如下：

到這里，基本上已經(jīng)實現(xiàn)我們開始時想要的效果了。但是細心的小伙伴此時發(fā)現(xiàn)了一個問題，當(dāng)對J列數(shù)據(jù)進行篩選的時候，顯示的是數(shù)字0、1。我們雖然能明白這里的0、1是啥意思，但其他同事看不懂??！該如何解決呢？

這里就要用到我們的自定義格式啦~

選中最后一列數(shù)據(jù)，右鍵，點擊“設(shè)置單元格格式”，點擊最下面一行的“自定義”，在“類型”一欄輸入“通過;;不通過”，點擊“確定”（注意通過和不通過中間是英文的分號哦~）

效果如下：

最后，我們將F-I列的數(shù)據(jù)隱藏，得到最終的表格。

小伙伴們都學(xué)會了嗎？

本文配套的練習(xí)課件請加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

數(shù)據(jù)驗證①《Excel小白的數(shù)據(jù)驗證課①用下拉菜單錄入的那些事兒》

數(shù)據(jù)驗證②《Excel小白的數(shù)據(jù)驗證課②身份證的雙重驗證設(shè)置等》

必會的數(shù)據(jù)驗證小技巧《數(shù)據(jù)有效性只能引用一列數(shù)據(jù)？但他這樣用1000列也行！》