当前位置: 首页 > Excel > Excel常用函数 > ExcelExcel技巧 > Excel条件格式的灵活应用——完全相同的行填充相同颜色

Excel条件格式的灵活应用——完全相同的行填充相同颜色

发布时间:2020年09月27日 06:39:13 来源: 点击量:623

【摘要】在一大堆数据里,用条件格式可以将完全相同的行标出相同的颜色,可以极大提高数据检查效率。完全相同行填充相同颜色,在同一工作表和不同工

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

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

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

【方法实现】

1、选中数据,在【开始】菜单中选择【条件格式】中的【新建规则】,如下图所示:

<a style=Excel条件格式的灵活应用——完全相同的行填充相同颜色" />

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,如下图所示:

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,将区域A1:A15中的所有值与A1比较,凡是等于A1的,返回值是1,不等于A1的返回值是0,所以此部分返回值是由15个1和0组成的数组。

其他四个逻辑表达式同理。

用SUMPRODUCT函数,对以上五个数组对应位置值相乘再相加,如果和大于1,说明有行完全相同。

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

如果是跨工作表来突出显示完全相同的行,怎么用条件格式?如下图所示:

Excel条件格式的灵活应用——完全相同的行填充相同颜色

【方法实现】

一、在shee1中自定义条件格式:

1、选中数据sheet1的A2:B15区域,在【开始】菜单中选择【条件格式】中的【新建规则】,如下图 所示:

Excel条件格式的灵活应用——完全相同的行填充相同颜色

2、【新建格式规则】——【使用公式确定要设置格式的单元格】,输入公式:

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

Excel条件格式的灵活应用——完全相同的行填充相同颜色

3、设置单元格格式填充为你想要的颜色。

二、在shee2中自定义条件格式:

与shee1中条件格式的公式思路一致,只不过改变公式中相应的单元格区域,公式变为:

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

Excel条件格式的灵活应用——完全相同的行填充相同颜色

在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

分享到: 编辑:wangmin

就业培训申请领取
您的姓名
您的电话
意向课程
点击领取

环球青藤

官方QQ

扫描上方二维码或点击一键加群,免费领取大礼包,加群暗号:青藤。 一键加群

绑定手机号

应《中华人民共和国网络安全法》加强实名认证机制要求,同时为更加全面的体验产品服务,烦请您绑定手机号.

预约成功

本直播为付费学员的直播课节

请您购买课程后再预约

环球青藤移动课堂APP 直播、听课。职达未来!

安卓版

下载

iPhone版

下载
环球青藤官方微信服务平台

刷题看课 APP下载

免费直播 一键购课

代报名等人工服务

课程咨询 学员服务 公众号

扫描关注微信公众号

APP

扫描下载APP

返回顶部