用条件格式把这样的查找结果变色

SCWYZJ

求助：用函数条件格式把查找结果变色（详见附件），请老师帮忙看看。谢谢

solextrade

=COUNTIF(INDEX($1:$8,,COLUMN()),$B$11)*COUNTIF(INDEX($1:$8,ROW(),),$C$11)

quqiyuan

1. =(ROW()=MIN(IF($B$2:$H$8=$C$11,ROW($2:$8))))*(COLUMN()=MIN(IF($B$2:$H$8=$B$11,COLUMN($B:$H))))

复制代码

沈默00

换个方式来参与一下

沈默00

试一下......

gadxjyang

=(COLUMN()=SUM(($B$11=$B$2:$H$8)*COLUMN($B:$H)))*(ROW()=SUM(($C$11=$B$2:$H$8)*ROW($2:$8)))

hcm19522

B14:H20{=(MIN(IF(COUNTIF($B$11:$C$11,$B$2:$H$8),COLUMN($A:$G)))=COLUMN(A1))*(MAX(IF(COUNTIF($B$11:$C$11,$B$2:$H$8),ROW($1:$7)))=ROW(A1))

有輔助 條件格式 B2:H8=B14

無輔助 條件格式 B2:H8=((SUMPRODUCT(MMULT(N($B$2:$H$8=$B$11),ROW(1:7)^0)*ROW($1:$7))=ROW(A1))*(SUMPRODUCT(MMULT(COLUMN(A:G)^0,N($B$2:$H$8=$C$11))*COLUMN($A:$G))=COLUMN(A1))+(SUMPRODUCT(MMULT(N($B$2:$H$8=$C$11),ROW(1:7)^0)*ROW($1:$7))=ROW(A1))*(SUMPRODUCT(MMULT(COLUMN(A:G)^0,N($B$2:$H$8=$B$11))*COLUMN($A:$G))=COLUMN(A1)))*(B2<>"")