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

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

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

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

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

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

1、 貨號前6位=款號

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

2、?貨號從第8位開始的兩位=色號

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

3、 條碼前6位=款號

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

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

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

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

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

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

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

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

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

效果如下：

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

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

最后將圖標居中顯示，效果如下：

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

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

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

?效果如下：

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

小伙伴們都學會了嗎？是不是覺得復雜，先按步驟做一遍，了解操作原理吧！

＊＊＊＊部落窩教育－excel數(shù)據(jù)對比標識＊＊＊＊

原創(chuàng)：壹仟伍佰萬/部落窩教育（未經(jīng)同意，請勿轉載）

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

微信公眾號：exceljiaocheng