多条件求解

helloexcel2014

通过区分是否还在公司，求相关的司龄及人数

helloexcel2014

要计算司龄而且按年月日的话，确实很麻烦

inrever

半失能=COUNTIFS($A$3:$A$103,"供养",$G$3:$G$103,U3)
失能=COUNTIFS($A$3:$A$103,"供养",$G$3:$G$103,U5)
全自理=COUNTIFS($A$3:$A$103,"供养",$G$3:$G$103,U7)
年龄大于85岁人数=COUNTIFS($A$3:$A$103,"供养",$F$3:$F$103,">85")
年龄小于60岁人数=COUNTIFS($A$3:$A$103,"供养",$F$3:$F$103,"<60")
年龄最大=MAXIFS($F$3:$F$103,$A$3:$A$103,"供养")
年龄最小=MINIFS($F$3:$F$103,$A$3:$A$103,"供养")
司龄最大（数组三键）=DATEDIF(LOOKUP(,0/((A3:A103="供养")*(--I3:I103=MIN(IF(A3:A103="供养",--I3:I103)))),I3:I103),TODAY(),"Y")&"年"&DATEDIF(LOOKUP(,0/((A3:A103="供养")*(--I3:I103=MIN(IF(A3:A103="供养",--I3:I103)))),I3:I103),TODAY(),"YM")&"月"&DATEDIF(LOOKUP(,0/((A3:A103="供养")*(--I3:I103=MIN(IF(A3:A103="供养",--I3:I103)))),I3:I103),TODAY(),"MD")&"日"
司龄最小（数组三键）=DATEDIF(LOOKUP(,0/((A3:A103="供养")*(--I3:I103=MAX(IF(A3:A103="供养",--I3:I103)))),I3:I103),TODAY(),"Y")&"年"&DATEDIF(LOOKUP(,0/((A3:A103="供养")*(--I3:I103=MAX(IF(A3:A103="供养",--I3:I103)))),I3:I103),TODAY(),"YM")&"月"&DATEDIF(LOOKUP(,0/((A3:A103="供养")*(--I3:I103=MAX(IF(A3:A103="供养",--I3:I103)))),I3:I103),TODAY(),"MD")&"日"
去世人中司龄最小（数组三键）=DATEDIF(LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MIN(IF(Q3:Q103="去世",R3:R103-I3:I103))),I3:I103),LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MIN(IF(Q3:Q103="去世",R3:R103-I3:I103))),R3:R103),"Y")&"年"&DATEDIF(LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MIN(IF(Q3:Q103="去世",R3:R103-I3:I103))),I3:I103),LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MIN(IF(Q3:Q103="去世",R3:R103-I3:I103))),R3:R103),"YM")&"月"&DATEDIF(LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MIN(IF(Q3:Q103="去世",R3:R103-I3:I103))),I3:I103),LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MIN(IF(Q3:Q103="去世",R3:R103-I3:I103))),R3:R103),"MD")&"日"
去世人中司龄最大（数组三键）=DATEDIF(LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MAX(IF(Q3:Q103="去世",R3:R103-I3:I103))),I3:I103),LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MAX(IF(Q3:Q103="去世",R3:R103-I3:I103))),R3:R103),"Y")&"年"&DATEDIF(LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MAX(IF(Q3:Q103="去世",R3:R103-I3:I103))),I3:I103),LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MAX(IF(Q3:Q103="去世",R3:R103-I3:I103))),R3:R103),"YM")&"月"&DATEDIF(LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MAX(IF(Q3:Q103="去世",R3:R103-I3:I103))),I3:I103),LOOKUP(,0/(IF(Q3:Q103="去世",R3:R103-I3:I103)=MAX(IF(Q3:Q103="去世",R3:R103-I3:I103))),R3:R103),"MD")&"日"

pxy0409

helloexcel2014 发表于 2024-8-2 16:28
要计算司龄而且按年月日的话，确实很麻烦

工龄可以用这个函数:DATEDIF(I3, R3, "Y")&"年"&DATEDIF(I3, R3, "YM")&"月"&DATEDIF(I3, R3, "YD")&"日"

helloexcel2014

inrever 发表于 2024-8-2 16:50
半失能=COUNTIFS($A$3:$A$103,"供养",$G$3:$G$103,U3)
失能=COUNTIFS($A$3:$A$103,"供养",$G$3:$G$103,U5) ...

谢谢，没想到公式这么长

helloexcel2014

pxy0409 发表于 2024-8-2 16:55
工龄可以用这个函数:DATEDIF(I3, R3, "Y")&"年"&DATEDIF(I3, R3, "YM")&"月"&DATEDIF(I3, R3, "YD")&"日"

谢谢回答！！！

helloexcel2014

inrever 发表于 2024-8-2 16:50
半失能=COUNTIFS($A$3:$A$103,"供养",$G$3:$G$103,U3)
失能=COUNTIFS($A$3:$A$103,"供养",$G$3:$G$103,U5) ...

非常感谢，都一一代入应用了，而且不卡