Excel | 条件格式——完全相同的行填充相同颜色

在一大堆数据里，用条件格式可以将完全相同的行标出相同的颜色，可以极大提高数据检查效率。

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

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

【方法实现】

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

在shee2中自定义条件格式

通过以上设置，两个工作表完全相同的行添加了颜色。

扩展：

如果两个工作表有三列数据，标出完全相同的行，公式可以变为：

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