求助大神：如何获取重复值、比对值

huangyanjian · 发表于 2018-3-7 23:17

求助大神：在B4、E3、G3、H3中需输入怎样的的函数？

jiangxiaoyun · 发表于 2018-3-8 08:16

重复值用countif函数

hcm19522 · 发表于 2018-3-8 10:53

本帖最后由 hcm19522 于 2018-3-8 12:59 编辑

G3{=INDEX(A:A,TEXT(MIN(IF((COUNTIF(D$3:D$8,A$3:A$8)=0)*(COUNTIF(G$2:G2,A$3:A$8)=0),ROW(A$3:A$8))),"0;;!999"))&""

H3{=INDEX(D:D,TEXT(MIN(IF((COUNTIF(A$3:A$8,D$3:D$8)=0)*(COUNTIF(H$2:H2,D$3:D$8)=0),ROW(A$3:A$8))),"0;;!999"))&""

B3:B8(同列有重复顯最上面)=IF(COUNTIF(A4:A8,A3),"重复","")

E2:E8(同列有重复顯最上面)=IF(COUNTIF(D4:D8,D3),"重复","")

樱花片雪2 · 发表于 2018-3-8 11:04

楼主，B3=IF(COUNTIF(A:A,A3)>1,"重复","")，E3=IF(COUNTIF(D:D,D3)>1,"重复","")，G3=IF(COUNTIF(D:D,A3)=0,A3,"")，H3=IF(COUNTIF(A:A,D3)=0,D3,"")

huangyanjian · 发表于 2018-3-8 11:21

hcm19522 发表于 2018-3-8 10:53
G3{=INDEX(A:A,TEXT(MIN(IF((COUNTIF(D$3:D$8,A$3:A$8)=0)*(COUNTIF(G$2:G2,A$3:A$8)=0),ROW(A$3:A$8))),"0 ...

谢谢大神的解答！

huangyanjian · 发表于 2018-3-8 11:23

樱花片雪2 发表于 2018-3-8 11:04
楼主，B3=IF(COUNTIF(A:A,A3)>1,"重复","")，E3=IF(COUNTIF(D:D,D3)>1,"重复","")，G3=IF(COUNTIF(D:D,A3)= ...

谢谢您的解答！

		自动登录	找回密码
密码			免费注册

[求助] 求助大神：如何获取重复值、比对值