EXCEL中,以下数组公式如何多增加一列进去?{=IF(AND(ISERROR(MATCH(1,(COUNTIF(L$1:L1,B$2:B$30)=0)*(B$2:B$30""),)),ISERROR(MATCH(1,(COUNTIF(L$1:L1,D$2:D$30)=0)*(D$2:D$30""),))),"",IF(ROW(A1)>SUM(IF($B$2:$B$30"",1/COUNTIF($B$2:$B$30,$B$2:$B$

Question

EXCEL中,以下数组公式如何多增加一列进去?
{=IF(AND(ISERROR(MATCH(1,(COUNTIF(L$1:L1,B$2:B$30)=0)*(B$2:B$30""),)),ISERROR(MATCH(1,(COUNTIF(L$1:L1,D$2:D$30)=0)*(D$2:D$30""),))),"",IF(ROW(A1)>SUM(IF($B$2:$B$30"",1/COUNTIF($B$2:$B$30,$B$2:$B$30))),INDEX(D$2:D$30,MATCH(1,(COUNTIF(L$1:L1,D$2:D$30)=0)*(D$2:D$30""),)),INDEX(B$2:B$30,MATCH(1,(COUNTIF(L$1:L1,B$2:B$30)=0)*(B$2:B$30""),))))}
如上公式,如何增加F2：F30单元格的内容进去判断

Answer 1

假设这些数据在B2到F10,选中这些数据,点菜单的格式,条件格式,条件格式一,公式,=countif($B$2:$F$10,B2)>1,格式设置为比如字体红色,确定.就可以了.
如果数据时C1到C100,选中这些数据,以上公式为 =countif($C$1:$C$100,C1)>1