时间筛选

linsh7310

网络平台下载的数据，某一天可能有N个行次，但只要筛选到每一天的最后一笔业务，要如何筛选？最好是用公式，V一点都不会。谢谢！

袁勇

1. =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:26)/((0&LEFT($A$3:$A26,8))-LEFT($A$2:$A25,8)<>0),ROW(A1))),"")

复制代码

右拉下拉。

part98

=REDUCE(A1:D1,UNIQUE(LEFT($A$2:$A$25,8)),LAMBDA(x,y,VSTACK(x,XLOOKUP(y,LEFT($A$2:$A$25,8),A2:D25,,,-1))))

hxj119

=LET(a,UNIQUE(LEFT(A2:A25,8)),REDUCE(A1:D1,a,LAMBDA(x,y,VSTACK(x,FILTER(A2:D25,A2:A25=LOOKUP(1,0/(LEFT(A2:A25,8)=y),A2:A25))))))

hugeinfo

=DROP(REDUCE("",UNIQUE(LEFT(A2:A25,8)),LAMBDA(x,y,VSTACK(x,CHOOSEROWS(A:D,LOOKUP(1,0/ISNUMBER(FIND(y,$A$2:$A$25)),ROW($2:$25)))))),1)

无关岁月

1. =LET(_x,LEFT(A2:A25,8),REDUCE(A1:D1,UNIQUE(_x),LAMBDA(_a,_b,VSTACK(_a,TAKE(FILTER(A2:D25,_x=_b),-1)))))

复制代码

ww87725244

=OFFSET(A$1,SMALL(TOCOL(BYCOL(IF(TOROW(UNIQUE(LEFT($A$2:$A$25,8)))=LEFT($A$2:$A$25,8),ROW($A$2:$A$25),""),MAX)),ROW(A1))-1,)

ww87725244

=REDUCE(A1:D1,UNIQUE(LEFT(A2:A25,8)),LAMBDA(x,y,VSTACK(x,CHOOSEROWS(A1:D25,MAX(IF(y=LEFT(A2:A25,8),ROW(A2:A25),""))))))

博爱风雨同舟

=LET(A,MMULT(--TEXT(IMREAL(IMDIV(SUBSTITUTE(A2:A25," ","-")&"i",{1,"-i"})),{"0000-00-00","0.0000"}),{1;1}),FILTER(A2:D26,IFNA(SIGN(FREQUENCY(-ROW(1:100)-0.9,-MATCH(INT(A),INT(A),)-A%%%%))*A,)>0))

hcm19522

{=INDEX(A:A,SMALL(IF(LEFT($A$2:$A$25,8)<>LEFT($A$3:$A$26,8),ROW($2:$25),99),ROW(A1)))&""