Question

excel怎么将下面重复的第一个名字也标记为红色，已使用函数=COUNTIF(B$1:B1,B1)>1

Answer 1

白爱英 =IF(COUNTIF(B$1:B1,B1)>1,"repeat","")
白爱英 repeat =IF(COUNTIF(B$1:B2,B2)>1,"repeat","")
白爱英 repeat =IF(COUNTIF(B$1:B3,B3)>1,"repeat","")
白爱英 repeat =IF(COUNTIF(B$1:B4,B4)>1,"repeat","")
白爱英 repeat =IF(COUNTIF(B$1:B5,B5)>1,"repeat","")
白爱珍 =IF(COUNTIF(B$1:B6,B6)>1,"repeat","")
白宝宝 =IF(COUNTIF(B$1:B7,B7)>1,"repeat","")
白宝军 =IF(COUNTIF(B$1:B8,B8)>1,"repeat","")
白宝连 =IF(COUNTIF(B$1:B9,B9)>1,"repeat","")
白宝平 =IF(COUNTIF(B$1:B10,B10)>1,"repeat","")
白宝平 repeat =IF(COUNTIF(B$1:B11,B11)>1,"repeat","")

在C1单元输入公式,
=IF(COUNTIF(B$1:B1,B1)>1,"repeat","")
如果重复的姓名, 会在C列显示"repeat"字眼, 然後下拉公式.
光标点住C1单元格, 按住向左向下拉至B11, "格式"-->"设定格式化条件", 将"单元格的值"改选为"公式", 然後在加边输入: =$C1="repeat"
然後选格式, 设定图案色彩即可.

Answer 2

选中B列
格式——条件格式——公式——改成
=COUNTIF(B:B,B1)>1
图案：红色
确定即可

Answer 3

条件格式能够解决，可将所有重复项目用颜色标记出来

Answer 4

公式改为: =COUNTIF(B:B,B1)>1

Answer 5

用条件格式。

Answer 6

路过