怎样设置复杂的条件格式

510437958

如图所示，如何对指定区域设定条件格式
条件格式一：当B2至G1000区域内当前单元格所在姓名对应时间在不计算表格中对应方人员姓名不计算值班起始时间内，则显示填充红色，字体黑色加粗倾斜；
条件格式二：当B2至G1000区域内当前单元格所在姓名统计对应时间在J列连续2015、2016、2017年且对应K列都为春节的单元格个数大于或等于1，则显示填充颜色紫色，字体黑色加粗倾斜。暨同一个人不能在连续三年春节或国庆值班，必须调整。
条件格式二的我写的公式为：在A63单元格=AND(SUMPRODUCT(($B$2:$G$1000=A63)*($J$2:$J$1000="2015")*($K$2:$K$1000="春节"))>=1,SUMPRODUCT(($B$2:$G$1000=A63)*($J$2:$J$1000="2016")*($K$2:$K$1000="春节"))>=1,SUMPRODUCT(($B$2:$G$1000=A63)*($J$2:$J$1000="2017")*($K$2:$K$1000="春节"))>=1)，结果却不能显示正确的结果。急求各位老师指导。谢谢！急！

dengjq2000

条件格式1的公式：

1. =SUMPRODUCT((不计算!$A$2:$A$13=$B2)*(不计算!$B$2:$B$13<=$A2)*(不计算!$C$2:$C$13>=$A2))

复制代码

在单元格B2创建好条件格式，然后B列进行格式化格式刷即可

510437958

dengjq2000 发表于 2017-1-17 15:07
条件格式1的公式：

格式二的呢？老师

510437958

dengjq2000 发表于 2017-1-17 15:07
条件格式1的公式：

老师，条件格式提示错误，在条件格式中不能对其他工作簿或工作表引用。晕死，我用的2003

dengjq2000

那你就创建辅助列。

510437958

dengjq2000 发表于 2017-1-17 15:20
那你就创建辅助列。

是否需要添加定义解决？当然VBA也可以，除辅助列以外是否可以？

dengjq2000

条件格式2的公式

1. =AND(SUMPRODUCT(($B$2:$B$1000=B2)*($J$2:$J$1000=2015)*($K$2:$K$1000="春节"))>0,SUMPRODUCT(($B$2:$B$1000=B2)*($J$2:$J$1000=2016)*($K$2:$K$1000="春节"))>0,SUMPRODUCT(($B$2:$B$1000=B2)*($J$2:$J$1000=2017)*($K$2:$K$1000="春节"))>0)

复制代码

前提需把J列统一成 文本型 或 数字型
=AND(SUMPRODUCT(($B$2:$G$1000=A63)*($J$2:$J$1000="2015")*($K$2:$K$1000="春节"))>=1,SUMPRODUCT(($B$2:$G$1000=A63)*($J$2:$J$1000="2016")*($K$2:$K$1000="春节"))>=1,SUMPRODUCT(($B$2:$G$1000=A63)*($J$2:$J$1000="2017")*($K$2:$K$1000="春节"))>=1)

dengjq2000

510437958 发表于 2017-1-17 15:22
是否需要添加定义解决？当然VBA也可以，除辅助列以外是否可以？

定义名称当然可以。

510437958

dengjq2000 发表于 2017-1-17 15:25
定义名称当然可以。

貌似添加了辅助列，而且不太实用。一是有多列（最少2列最多6列）；二是希望能找到学习定义跨表引用条件格式的办法。希望老师可以解决。谢谢！

dengjq2000

510437958 发表于 2017-1-17 15:34
貌似添加了辅助列，而且不太实用。一是有多列（最少2列最多6列）；二是希望能找到学习定义跨表引用条件格 ...

可以该对EXCEL软件升级了。
通过定义名称创建的条件格式。