请教提取新增人员和离职人员

syren2019

各路大神：

       老板要我根据两个月的名单，提取新入职和离职的人员，求助！

混沌音符

G3=FILTER($D$3:$D$6,COUNTIFS($A$3:$A$7,$D$3:$D$6)=0)
I3=FILTER($A$3:$A$7,COUNTIFS($D$3:$D$6,$A$3:$A$7)=0)

良良

G3=IFERROR(INDEX(D:D,SMALL(IF(COUNTIF(A$3:A$7,D$3:D$6)=0,ROW($3:$6)),ROW(A1))),"")
I3=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(D$3:D$6,A$3:A$7)=0,ROW($3:$7)),ROW(A1))),"")
三键，下拉。

ovxyz

G3=FILTER(D3:D6,NOT(ISNUMBER(MATCH(D3:D6,A3:A7,0))))
I3=FILTER(A3:A7,NOT(ISNUMBER(MATCH(A3:A7,D3:D6,0))))

飞天篮球猪

供参考...

一杯绿茶来了

=FILTER(D3:D6,IFNA(MATCH(D3:D6,A3:A7,),)=0)

part98

=FILTER(D3:D6,COUNTIF(A3:A7,D3:D6)=0)
=FILTER(A3:A7,COUNTIF(D3:D6,A3:A7)=0)

syren2019

混沌音符 发表于 2024-10-18 17:37
G3=FILTER($D$3:$D$6,COUNTIFS($A$3:$A$7,$D$3:$D$6)=0)
I3=FILTER($A$3:$A$7,COUNTIFS($D$3:$D$6,$A$3:$A ...

谢谢

你是那块小饼干

新增=FILTER(D3:D6,ISERROR(MATCH(D3:D6,A3:A7,0)),"")
离职=FILTER(A3:A7,ISERROR(MATCH(A3:A7,D3:D6,0)),"")

mjm04

3月新增人员
=FILTER(D3:D6,COUNTIF(A3:A7,D3:D6)=0)

3月离职人员
=FILTER(A3:A7,COUNTIF(D3:D6,A3:A7)=0)