怎样设置复杂的条件格式

dengjq2000

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

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

准提部林

定義名稱:
xName：=OFFSET(不计算!$A$1,1,,COUNTA(不计算!$A:$A)-1)
xDate1：=OFFSET(不计算!$A$1,1,1,COUNTA(不计算!$A:$A)-1)
xDate2：=OFFSET(不计算!$A$1,1,2,COUNTA(不计算!$A:$A)-1)

UYear：=OFFSET(值班测试!$J$1,1,,COUNTA(值班测试!$J:$J)-1)
UDay：=OFFSET(值班测试!$J$1,1,1,COUNTA(值班测试!$J:$J)-1)
UName：=OFFSET(值班测试!$B$1,1,,COUNTA(值班测试!$J:$J)-1,6)
========================================
條件公式:
條件１：=IF(B2="",,OR((xName=B2)*($A2-xDate1>=0)*($A2-xDate2<=0)))
條件２：=IF(B2="",,FIND(111,SUM((1-ISNA(MATCH($J2+COLUMN($A:$E)-3,(MMULT(N(B2=UName),ROW($A$1:$A$6)^0)>0)*(UDay="春节")*Uyear,)))*10^COLUMN($A:$E))))

如果"国庆"也要算進去:
=IF(B2="",,FIND(111,SUM((1-ISNA(MATCH($J2+COLUMN($A:$E)-3,(MMULT(N(B2=UName),ROW($A$1:$A$6)^0)>0)*((UDay="春节")+(UDay="国庆"))*UYear,)))*10^COLUMN($A:$E))))

TEST003.rar (8.76 KB, 下载次数: 6)

2017-1-17 17:12 上传

点击文件名下载附件

510437958

准提部林 发表于 2017-1-17 17:12
定義名稱:
xName：=OFFSET(不计算!$A$1,1,,COUNTA(不计算!$A:$A)-1)
xDate1：=OFFSET(不计算!$A$1,1,1,CO ...

准提老师，您的条件一我可以看懂；条件二是我没有说清楚，不能连续三年春节值班，不是三年春节超过或等于3次值班就报错。=IF(B2="",,FIND(111,SUM((1-ISNA(MATCH($J2+COLUMN($A:$E)-3,(MMULT(N(B2=UName),ROW($A$1:$A$6)^0)>0)*(UDay="春节")*Uyear,)))*10^COLUMN($A:$E))))看不懂哟，能否将SUM换成其他的sumproduct呢？您这个答案是最接近我的设想，拜托完善一下，谢谢！

510437958

准提部林 发表于 2017-1-17 17:12
定義名稱:
xName：=OFFSET(不计算!$A$1,1,,COUNTA(不计算!$A:$A)-1)
xDate1：=OFFSET(不计算!$A$1,1,1,CO ...

准提老师，不能连续三年春节值班是指同一人连续三年春节值班每年次数都大于或等于1。

准提部林

510437958 发表于 2017-1-17 21:38
准提老师，不能连续三年春节值班是指同一人连续三年春节值班每年次数都大于或等于1。

$J2+COLUMN($A:$E)-3
J2若等于2015 則檢查{2013,2014,2015,2016,2017}是否連續三年,
2016, 2017 則類推, 即當年度的前後二年,
春節執班2天以上, 也只算為當年一次, 可再檢測看看!!
=======================================
以檔案為例, 只有[王远平]及[邓秋平]符合連續三年,
可在D9及D10填入[邓秋平], 此時, [邓秋平]2015年執班3天,
然後將E36的[邓秋平]清除, 使其2016不執班, 即可看出其條件格式消失!!!
=======================================
簡化一下表格, 可參考右方的演示結果, 合計值出現包含"111", 即為連續三年執班:
TEST003_v1.rar (8.61 KB, 下载次数: 2)

2017-1-17 23:34 上传

点击文件名下载附件

510437958

准提部林 发表于 2017-1-17 23:03
$J2+COLUMN($A:$E)-3
J2若等于2015 則檢查{2013,2014,2015,2016,2017}是否連續三年,
2016, 2017 則類 ...

准提老师，C21单元格王远平在抗日值班条件格式也显示出来是否有问题？

510437958

准提部林 发表于 2017-1-17 23:03
$J2+COLUMN($A:$E)-3
J2若等于2015 則檢查{2013,2014,2015,2016,2017}是否連續三年,
2016, 2017 則類 ...

这个条件格式只是提醒，我明白了，我的意思是如果在不是春节的日期不报错提示就好了。谢谢准提老师这么晚还来指导我！

丢丢表格

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

格式1：
=AND(COUNTIF($A2,{">=","<="}&VLOOKUP(B2,不计算!$A:$D,{2,3},)))

格式2：
=COUNT(0/(MMULT(COUNTIF(OFFSET($B$1:$K$1,ROW($1:$200),),CHOOSE({1,2,3,4,5,6},C2,2015,2016,2017,"元旦","春节")),{2;10;10;10;1;1})=13))>2

准提部林

510437958 发表于 2017-1-17 23:36
准提老师，C21单元格王远平在抗日值班条件格式也显示出来是否有问题？

改這個：
=IF(OR(B2="",$J2="",$K2<>"春节"),,FIND(111,SUM((1-ISNA(MATCH($J2+COLUMN($A:$E)-3,(MMULT(N(B2=UName),ROW($A$1:$A$6)^0)>0)*(UDay="春节")*UYear,)))*10^COLUMN($A:$E))))