根据某个值将对应的数字进行筛选

aleaflin

=IF(SUM((A$2:A$1000=A2)*(B$2:B$1000="Finance Department"))*SUM((A$2:A$1000=A2)*(B$2:B$1000="Admin Department")),AND(C2=MAX((A$2:A$1000=A2)*C$2:C$1000),COUNTIFS(A$1:A1,A2,D$1:D1,TRUE)=0),TRUE)

Elsaaaa

aleaflin 发表于 2019-6-24 16:42
=IF(SUM((A$2:A$1000=A2)*(B$2:B$1000="Finance Department"))*SUM((A$2:A$1000=A2)*(B$2:B$1000="Admin De ...

ok了~谢谢大神~想麻烦问下(A$2:A$1000=A2)*(B$2:B$1000="Finance Department")这种结构是什么意思？生成的是1*1000的list吗？

aleaflin

Elsaaaa 发表于 2019-6-25 13:40
ok了~谢谢大神~想麻烦问下(A$2:A$1000=A2)*(B$2:B$1000="Finance Department")这种结构是什么意思？生成 ...

你随便找个单元格，输入=(A$2:A$10=A2)*(B$2:B$10="Finance Department")，然后鼠标选中等号以后的部分，按F9看结果，比较直观

Elsaaaa

aleaflin 发表于 2019-6-25 13:54
你随便找个单元格，输入=(A$2:A$10=A2)*(B$2:B$10="Finance Department")，然后鼠标选中等号以后的部分， ...

哈哈，学到了！~拜谢~~