求差异人数，谢谢

狗剩呵呵哒

求差异人数，谢谢

良良

=NPV(-2,N(OFFSET(A$1,MATCH(A7,C$2:C$4,),MATCH(B7,$1:$1,)-{2,1})))

mykitami

既然是M365，就连同左侧组别、日期一起用一个公式完成更好些

=LET(a,TOCOL(IF(COLUMN(D2:O2),C2:C4,x),2),b,TOCOL(IF(ROW(C2:C4),D1:O1,x),2),c,TOCOL(D2:O4),VSTACK({"组别","日期","本月较上月人数增长"},HSTACK(a,b,IF(b-VSTACK(9^9,DROP(b,-1))>0,c-VSTACK(9^9,DROP(c,-1)),c))))

leepoison

=DROP(REDUCE("",C2:C4,LAMBDA(x,y,VSTACK(x,VSTACK(IFNA(HSTACK(y,TRANSPOSE($D$1:$O$1),TRANSPOSE(OFFSET(y,,1,,12)-HSTACK(0,OFFSET(y,,1,,11)))),y))))),1)

gadxjyang

=IF(B7=D$1,VLOOKUP(A7,C$2:D$4,2,),SUM(INDEX(D$2:O$4,MATCH(A7,C$2:C$4,),MATCH(N(OFFSET(B7,1-ROW(A$1:A$2),)),D$1:O$1,))*{1;-1}))

龙神西南电磁王

=INDEX($D$2:$O$4,MATCH(A7,$C$2:$C$4,),MATCH(B7,$D$1:$O$1,))-N(OFFSET($D$2,MATCH(A7,$C$2:$C$4,)-1,MATCH(B7,$D$1:$O$1,)-2))

龙神西南电磁王

=SUM(IFERROR(OFFSET($D$2:$E$2,MATCH(A7,$C$2:$C$4,)-1,MATCH(B7,$D$1:$O$1,)-2)*{-1,1},0))

你是那块小饼干

=DROP(REDUCE("",C2:C4,LAMBDA(X,Y,LET(T,FILTER(D2:O4,Y=C2:C4,""),VSTACK(X,TRANSPOSE(T-HSTACK(0,DROP(T,,-1))))))),1)

pgp5866

1. =TOCOL(SCAN(0,D2:O4,LAMBDA(x,y,NPV(-2,N(+OFFSET(y,,,,-2))))))

复制代码

邹永鸿

1. =LET(a,INDEX($D$2:$O$4,MATCH(A7,$C$2:$C$4,0),MATCH(B7,$D$1:$O$1,0)),IF(B7=45292,a,a-INDEX($D$2:$O$4,MATCH(A6,$C$2:$C$4,0),MATCH(B6,$D$1:$O$1,0))))

复制代码