求取某个值的上两行和下两行的值

飞天篮球猪

插件解法...制定上下行数...仅供参考

cjc209

=HSTACK(INDEX(A1:A7114,TOCOL(TOCOL(IF($A$3:$A$7114*1=$E$7116,ROW($A$3:$A$7114),X),3)-{2,1})),INDEX(A1:A7114,TOCOL(TOCOL(IF($A$3:$A$7114*1=$E$7116,ROW($A$3:$A$7114),X),3)+{1,2})))

leepoison

=LET(b,A3:A7114,a,TOCOL(IF(1*(b)=155,SEQUENCE(COUNTA(b)),1/0),3),VSTACK(INDEX(b,a+{-1,1}),INDEX(b,a+{-2,2})))

不可说7625

zwzc 发表于 2024-12-24 10:52
=FILTER(A3:A7114,(--A4:A7115=E7116)+(--A5:A7116=E7116))

=FILTER(A3:A7114,(--A2:A7113=E7116)+(--A1 ...

E7116 是文本格式，会出现#CALC。要怎么修改下

我要成为专家

1. =LET(k,FILTER(ROW(A:A),A:A=E7116&""),s,OFFSET(A1,k,,2),p,OFFSET(A1,k-3,,2),HSTACK(REDUCE(,p,VSTACK),REDUCE(,s,VSTACK)))

复制代码

1. =LET(s,A3:A7114,HSTACK(FILTER(s,(--A4:A7115=E7116)+(--A5:A7116=E7116)),FILTER(s,(--A1:A7112=E7116)+(--A2:A7113=E7116))))

复制代码

1. =LET(s,A3:A7114=E7116&"",HSTACK(TOCOL(HSTACK(FILTER(A1:A7112,s),FILTER(A2:A7113,s))),TOCOL(HSTACK(FILTER(A4:A7115,s),FILTER(A5:A7116,s)))))

复制代码

我要成为专家

solextrade 发表于 2024-12-24 11:06
右拖下拖
=INDEX($A:$A,SMALL(IF(--$A$3:$A$7114=$E$7116,ROW($3:$7114)-IF(COLUMN(A1)=1,1,-1)*{1,2},4^8 ...

最简洁牛掰的函数