如果c3不能在g3至o5范围内找到，则用红色字体显示

LP.07

solextrade

=COUNTIF($G$3:$O$5,C3)=0

zwzc

=SUM(--($G$3:$O$20=C3))=0

LP.07

能否对应到班级，1班李欣雨对应1班成绩位次，以免名字重复

LP.07

zwzc 发表于 2024-11-11 16:10
=SUM(--($G$3:$O$20=C3))=0

你好，能否精确到某班某人，防止名字重复

LP.07

solextrade 发表于 2024-11-11 16:06
=COUNTIF($G$3:$O$5,C3)=0

能否精确到某人某班，避免姓名重复

Excel_muke

LP.07 发表于 2024-11-11 16:30
你好，能否精确到某班某人，防止名字重复

1. C3=IF(C$2="1班", COUNTIFS($G$3:$G$20, C$2, $H$3:$H$20, C3)=0, IF(C$2="2班", COUNTIFS($J$3:$J$20, C$2, $K$3:$K$20, C3)=0, COUNTIFS($M$3:$M$20, C$2, $N$3:$N$20, C3)=0))

复制代码

条件格式输入上式，范围选择C3：E20

zwzc

=SUM(--(VSTACK($G$3:$G$20&$H$3:$H$20,$J$3:$J$20&$K$3:$K$20,$M$3:$M$20&$N$3:$N$20)=C$2&C3))=0

你是那块小饼干

选中C3:E20  条件格式  =OR(C3=$G$3:$O$20)

mykitami

=LET(a,LOOKUP(ROW($A$3:$A$20),ROW($A$3:$A$20)/($A$3:$A$20<>""),$A$3:$A$20),b,MMULT(N(a=TOROW(a)),ROW($A$3:$A$20)^0),c,FILTER($G$3:$G$20&$H$3:$H$20&$J$3:$J$20&$K$3:$K$20&$M$3:$M$20&$N$3:$N$20,a=LOOKUP("座",$A$3:$A3)),COUNT(FIND(C$2&C3,c))=0)