在一大堆數據裡,用條件格式可以將完全相同的行標出相同的顏色,可以極大提高數據檢查效率。
完全相同行填充相同顏色,在同一工作表和不同工作表中的方法,稍有不同。
同一工作表完全相同行填充相同顏色
【方法實現】
1、選中數據,在【開始】菜單中選擇【條件格式】中的【新建規則】,如下圖所示:
2、【新建格式規則】——【使用公式確定要設置格式的單元格】,輸入公式:
=SUMPRODUCT(($A$1:$A$15=$A1)*($B$1:$B$15=$B1)*($C$1:$C$15=$C1)*($D$1:$D$15=$D1)*($E$1:$E$15=$E1))>1,如下圖所示:
3、設置單元格格式填充為你想要的顏色,如下圖 所示:
即可使完全相同的行填充同種顏色。
【公式解析】
=SUMPRODUCT(($A$1:$A$15=$A1)*($B$1:$B$15=$B1)*($C$1:$C$15=$C1)*($D$1:$D$15=$D1)*($E$1:$E$15=$E1))>1
此公式表示四個邏輯表達式的乘積。
第一個邏輯表達式:$A$1:$A$15=$A1,將區域A1:A15中的所有值與A1比較,凡是等於A1的,返回值是1,不等於A1的返回值是0,所以此部分返回值是由15個1和0組成的數組。
其他四個邏輯表達式同理。
用SUMPRODUCT函數,對以上五個數組對應位置值相乘再相加,如果和大於1,說明有行完全相同。
不同工作表完全相同行填充相同顏色
如果是跨工作表來突出顯示完全相同的行,怎麼用條件格式?如下圖所示:
【方法實現】
一、在shee1中自定義條件格式:
1、選中數據sheet1的A2:B15區域,在【開始】菜單中選擇【條件格式】中的【新建規則】,如下圖 所示:
2、【新建格式規則】——【使用公式確定要設置格式的單元格】,輸入公式:
=SUMPRODUCT((Sheet2!$A$2:$A$12=$A2)*(Sheet2!$B$2:$B$12=$B2))>=1:
3、設置單元格格式填充為你想要的顏色。
二、在shee2中自定義條件格式:
與shee1中條件格式的公式思路一致,只不過改變公式中相應的單元格區域,公式變為:
=SUMPRODUCT((Sheet1!$A$2:$A$15=$A2)*(Sheet1!$B$2:$B$15=$B2))>=1,如下圖所示:
通過以上設置,兩個工作表完全相同的行添加了顏色。
擴展:
如果兩個工作表有三列數據,標出完全相同的行,公式可以變為:
sheet1中:
=SUMPRODUCT((Sheet2!$A$2:$A$12=$A2)*(Sheet2!$B$2:$B$12=$B2)*(Sheet2!$C$2:$C$12=$C2))>=1
sheet2中:
=SUMPRODUCT((Sheet1!$A$2:$A$15=$A2)*(Sheet1!$B$2:$B$15=$B2)*(Sheet1!$C$2:$C$15=$C2))>=1
閱讀更多 韓老師講office 的文章