英文:
How to create a blank row under specific cells?
问题
我正在尝试创建一个公式，以在下面的范围中每次日期变化时创建一行空白行。
这将是最终目标：
| 日期 | 字母 | |||
|---|---|---|---|---|
| 06/12 | b | |||
| 06/12 | c | |||
| 06/12 | d | |||
| 06/13 | e | |||
| 06/13 | f | |||
| 06/13 | g | |||
| 06/14 | h | |||
| 06/14 | i | |||
| 06/14 | j |
英文:
I'm trying to create a formula to read and transform the range bellow creating a blank row every time the date changes.
This would be the end goal:
| Date | Letter | |||
|---|---|---|---|---|
| 06/12 | b | |||
| 06/12 | c | |||
| 06/12 | d | |||
| 06/13 | e | |||
| 06/13 | f | |||
| 06/13 | g | |||
| 06/14 | h | |||
| 06/14 | i | |||
| 06/14 | j |
答案1
得分: 1
=假设您的日期在A2:A范围内，您可以使用：
=REDUCE(TOCOL(,1),A2:A,LAMBDA(a,c,{a;IF(c=OFFSET(c,1,),c,{c;""})}))
更新
=REDUCE(TOCOL(,1),SEQUENCE(COUNT(A2:A)),LAMBDA(a,c,LET(d,INDEX(A2:A,c),l,INDEX(B2:B,c),VSTACK(a,IF(d=OFFSET(d,1,),{d,l},{d,l;""})))))
或：
=LET(_,LAMBDA(o,REDUCE(TOCOL(,1),A2:A,LAMBDA(a,c,{a;IF(c<>OFFSET(c,1,),{OFFSET(c,,o);""},OFFSET(c,,o))}))),{_(0),_(1)})
英文:
Assuming you have the dates in the range A2:A, you can use:
=REDUCE(TOCOL(,1),A2:A,LAMBDA(a,c,{a;IF(c=OFFSET(c,1,),c,{c;""})}))
Update
=REDUCE(TOCOL(,1),SEQUENCE(COUNT(A2:A)),LAMBDA(a,c,LET(d,INDEX(A2:A,c),l,INDEX(B2:B,c),VSTACK(a,IF(d=OFFSET(d,1,),{d,l},{d,l;"",""})))))
Or:
=LET(_,LAMBDA(o,REDUCE(TOCOL(,1),A2:A,LAMBDA(a,c,{a;IF(c<>OFFSET(c,1,),{OFFSET(c,,o);""},OFFSET(c,,o))}))),{_(0),_(1)})
答案2
得分: 1
尝试以下公式-
=REDUCE(A1:B1,A2:INDEX(A2:A,COUNTA(A2:A)), LAMBDA(a,x, VSTACK(a, IF(x=INDEX(A:A,ROW(x)+1), INDEX(A:B,ROW(x)), VSTACK(INDEX(A:B,ROW(x)),HSTACK(" "," ")) ) ) ) )
英文:
Try the following formula-
=REDUCE(A1:B1,A2:INDEX(A2:A,COUNTA(A2:A)),
LAMBDA(a,x,
VSTACK(a,
IF(x=INDEX(A:A,ROW(x)+1),
INDEX(A:B,ROW(x)),
VSTACK(INDEX(A:B,ROW(x)),HSTACK(" "," "))
)
)
)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论