在一大堆數(shù)據(jù)里，用條件格式可以將完全相同的行標出相同的顏色，可以極大提高數(shù)據(jù)檢查效率。

完全相同行填充相同顏色，在同一工作表和不同工作表中的方法，稍有不同。

同一工作表完全相同行填充相同顏色

【方法實現(xiàn)】

1、選中數(shù)據(jù)，在【開始】菜單中選擇【條件格式】中的【新建規(guī)則】，如下圖所示：

Excel條件格式的靈活應用——完全相同的行填充相同顏色

2、【新建格式規(guī)則】——【使用公式確定要設置格式的單元格】，輸入公式：

=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，如下圖所示：

Excel條件格式的靈活應用——完全相同的行填充相同顏色

3、設置單元格格式填充為你想要的顏色，如下圖 所示：

Excel條件格式的靈活應用——完全相同的行填充相同顏色

即可使完全相同的行填充同種顏色。

【公式解析】

=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，將區(qū)域A1:A15中的所有值與A1比較，凡是等于A1的，返回值是1，不等于A1的返回值是0，所以此部分返回值是由15個1和0組成的數(shù)組。

其他四個邏輯表達式同理。

用SUMPRODUCT函數(shù)，對以上五個數(shù)組對應位置值相乘再相加，如果和大于1，說明有行完全相同。

不同工作表完全相同行填充相同顏色

如果是跨工作表來突出顯示完全相同的行，怎么用條件格式？如下圖所示：

Excel條件格式的靈活應用——完全相同的行填充相同顏色

【方法實現(xiàn)】

一、在shee1中自定義條件格式：

1、選中數(shù)據(jù)sheet1的A2：B15區(qū)域，在【開始】菜單中選擇【條件格式】中的【新建規(guī)則】，如下圖 所示：

Excel條件格式的靈活應用——完全相同的行填充相同顏色

2、【新建格式規(guī)則】——【使用公式確定要設置格式的單元格】，輸入公式：

=SUMPRODUCT((Sheet2!$A$2:$A$12=$A2)*(Sheet2!$B$2:$B$12=$B2))>=1：

Excel條件格式的靈活應用——完全相同的行填充相同顏色

3、設置單元格格式填充為你想要的顏色。

二、在shee2中自定義條件格式：

與shee1中條件格式的公式思路一致，只不過改變公式中相應的單元格區(qū)域，公式變?yōu)椋?/p>

=SUMPRODUCT((Sheet1!$A$2:$A$15=$A2)*(Sheet1!$B$2:$B$15=$B2))>=1，如下圖所示：

Excel條件格式的靈活應用——完全相同的行填充相同顏色

在shee2中自定義條件格式

通過以上設置，兩個工作表完全相同的行添加了顏色。

擴展：

如果兩個工作表有三列數(shù)據(jù)，標出完全相同的行，公式可以變?yōu)椋?/p>

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