英文:
How to place 3 consecutive cell data in top 3 cells of a column
问题
在Excel移动版中使用。我需要一个能实现以下功能的公式：
在一个Excel列中，选择顶部的3个单元格，这些单元格连续地显示在同一列的任何位置。
我已经开始使用一个“if”条件来取消选择空白单元格。接下来，它只是复制下面的单元格的值。显然这不起作用。
有人建议我使用以下公式，但它没有返回我需要的结果：
=IF(ROW(A1)<=COUNTA(A:A)-2, INDEX(A:A, ROW(A1)) & ", " & INDEX(A:A, ROW(A1)+1) & ", " & INDEX(A:A, ROW(A1)+2), "")
英文:
I am using Mobile version of Excel. I need a formula which does the following...
In an excel column, select top 3 cells that read in set of 3 consecutive cell entries appearing in the same column anywhere.
I have started with "if" condition that unselects the blanks. Next it just copies the value from the cell just below it. Obviously it doesn't work.
I have been suggested following formula but its not returning what I need...
=IF(ROW(A1)<=COUNTA(A:A)-2, INDEX(A:A, ROW(A1)) & ", " & INDEX(A:A, ROW(A1)+1) & ", " & INDEX(A:A, ROW(A1)+2), "")
答案1
得分: 4
=LET(Data,A4:A20,ff,Data<>""",mf,VSTACK(DROP(ff,1),0),lf,VSTACK(DROP(mf,1),0),IFERROR(INDEX(Data,SEQUENCE(3,,XMATCH(1,ffmflf))),""""))
Edit
Reading the comment you posted
"...
lets assume we have three values at A10, A11 and A12 as 34, 35 and 45 respectively. All cells above and below these entries are empty. We just need top 3 cells to get filled with these values i.e. A1, A2, A3 having 34, 35 and 45 respectively."
the following simplification might be considered:
=LET(Data,A4:A20,IFERROR(INDEX(Data,SEQUENCE(3,,XMATCH(TRUE,Data<>""""))),""""))
1: https://i.stack.imgur.com/fxeRb.jpg
2: https://i.stack.imgur.com/jF88i.jpg
英文:
First (Top-Most) 3 Consecutive Non-Blank Cells in Column
=LET(Data,A4:A20,
ff,Data<>"",mf,VSTACK(DROP(ff,1),0),lf,VSTACK(DROP(mf,1),0),
IFERROR(INDEX(Data,SEQUENCE(3,,XMATCH(1,ff*mf*lf))),""))
Edit
Reading the comment you posted
> "... lets assume we have three values at A10, A11 and A12 as 34, 35 and 45 respectively. All cells above and below these entries are
> empty. We just need top 3 cells to get filled with these values i.e.
> A1, A2, A3 having 34, 35 and 45 respectively."
the following simplification might be considered:
=LET(Data,A4:A20,
IFERROR(INDEX(Data,SEQUENCE(3,,XMATCH(TRUE,Data<>""))),""))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论