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

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

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

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

我們希望如果錄入的數據滿足列與列之間的關系，表格亮綠燈，表示數據錄入正确，反之亮紅燈，如下，應該怎麼實現呢？

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

1、 貨号前6位=款号

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

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

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

3、 條碼前6位=款号

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

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

在I2單元格輸入公式：=MID(C2,7,2)=D2&""，下拉填充公式。用MID函數從條碼中間第7位開始截取兩位，判斷是否等于色号。基于同樣的原因，我們在單元格D2後面連接了一個空,使D列（色号列）的數據轉換為文本數據。

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

将這4個邏輯值作為AND函數的參數，代表着隻有同時滿足這四種條件時，才算TRUE，隻要有一個條件不滿足，那都是FALSE。

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

現在我們得到的數據是邏輯值，不方便我們後續的使用，所以我們需要乘以1，将邏輯值轉換成數字。此時TRUE相當于1，FALSE相當于0。

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

選中最後一列數據，在“開始”選項卡，點擊“條件格式”-“圖标集”。在“圖标集”中選擇紅綠燈樣式。

效果如下：

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

選中J列，點擊“條件格式”-“管理規則”，點擊“編輯規則”，勾選“僅顯示圖标”，點擊“确定”。

最後将圖标居中顯示，效果如下：

到這裡，基本上已經實現我們開始時想要的效果了。但是細心的小夥伴此時發現了一個問題，當對J列數據進行篩選的時候，顯示的是數字0、1。我們雖然能明白這裡的0、1是啥意思，但其他同事看不懂啊！該如何解決呢？

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

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

效果如下：

最後，我們将F-I列的數據隐藏，得到最終的表格。

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

＊＊＊＊部落窩教育－excel數據對比标識＊＊＊＊

原創：壹仟伍佰萬/部落窩教育（未經同意，請勿轉載）