英文:
Formula to identify the first blank in a column and start the list over again
问题
目标是识别列中的第一个空白，然后从该空白行开始在另一列重新开始列表。
。
在C列中的我的公式是：
=IFNA(XLOOKUP(IF(MATCH(TRUE,ISBLANK(B:B),0)<=A5+1,A5+2-MATCH(TRUE,ISBLANK(B:B),0),""),A:A,B:B),"")
尽管这样做可以实现效果，但感觉相对于它所做的事情来说过于复杂。有没有更简单的方法来实现这个？这个Excel表格不会处理大量数据，因此不需要过于高效。
英文:
Objective is to identify the first blank in a column, and then start the list again in another column, starting from that blank row.
.
My formula in column C is:
=IFNA(XLOOKUP(IF(MATCH(TRUE,ISBLANK(B:B),0)<=A5+1,A5+2-MATCH(TRUE,ISBLANK(B:B),0),""),A:A,B:B),"")
Although this works, it feels overly complicated for what it is doing. Can this be done in a simpler way? This Excel sheet won't be dealing with large quantities of data, so does not need to be overly efficient.
答案1
得分: 4
Formula in C2:
=TOCOL(IF({1,0}, "", DROP(TOCOL(B:B,1),1)),,1)
Or, a character shorter:
=TOCOL(REPT(DROP(TOCOL(B:B,1),1), {0,1}),,1)
英文:
Alternatively:
Formula in C2:
=TOCOL(IF({1,0},"",DROP(TOCOL(B:B,1),1)),,1)
Or, a character shorter:
=TOCOL(REPT(DROP(TOCOL(B:B,1),1),{0,1}),,1)
答案2
得分: 3
使用VSTACK来堆叠空白和数值。
=LET(
RNG,DROP(B:B,1),
FLT,FILTER(RNG,RNG<>""),
VSTACK(INDEX("",SEQUENCE(COUNTA(FLT),,1,0)),FLT))
LET允许我们使用变量表示数据。因此，DROP(B:B,1)将将列B中除顶部行之外的所有值放入变量RNG中。
然后，我们筛选RNG以排除空白，并将该数组放入FLT中。然后使用INDEX("",SEQUENCE(COUNTA(FLT),,1,0))创建一个与筛选范围大小相同的空字符串数组。然后，VSTACK将筛选后的范围放在底部。
英文:
UseVSTACK to stack blanks and the values.
=LET(
RNG,DROP(B:B,1),
FLT,FILTER(RNG,RNG<>""),
VSTACK(INDEX("",SEQUENCE(COUNTA(FLT),,1,0)),FLT))
LET allows us to use variables to represent data. so DROP(B:B,1) will put the values of all of column B minus the top row in the variable RNG.
From there we filter RNG to those that are not blank and put that array in FLT. Then using INDEX("",SEQUENCE(COUNTA(FLT),,1,0)) we create an array of empty strings the size of the filtered range. Then VSTACK puts the filtered range on the bottom.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论