英文:
In MS Excel, how can I VSTACK a Sequence generated from on start and end?
问题
| start | end |
|---|---|
| 1 | 5 |
| 10 | 13 |
| 20 | 22 |
我有一个包含日期范围的表格，如下所示：
我尝试创建一个包含在起始和结束日期/数字之间（包括起始和结束）的所有日期/数字的列表。所以，我的输出将是{1,2,3,4,5,10,11,12,13,20,21,22}。
对于只有一个起始和结束的情况，公式如下：
=SEQUENCE(B2-A2+1,,A2)
我认为这应该是一个SEQUENCES的垂直堆叠，因此我编写了以下公式，以范围作为输入：
=VSTACK(SEQUENCE($B$2:$B$3-$A$2:$A$3+1,,$A$2:$A$3))
然而，这个公式只会为每个序列获取第一个值，并生成输出{1,10,20}。
由于我真的不想将我的文件变成xlsm（因为我的组织不允许使用宏），是否有一种无需使用VBA就可以实现这一目标的方法？
谢谢，
Ashutosh Deshpande
英文:
I have a table with start and end of date-ranges, like this
| start | end |
|---|---|
| 1 | 5 |
| 10 | 13 |
| 20 | 22 |
I am trying to create a list of all dates/numbers falling between the start and end (both inclusive). So, my output will be {1,2,3,4,5,10,11,12,13,20,21,22}.
For only one start and end the formula would be
=SEQUENCE(B2-A2+1,,A2)
I thought it would be simply be a VSTACK of SEQUENCES, so I wrote this formula with ranges as an input
=VSTACK(SEQUENCE($B$2:$B$3-$A$2:$A$3+1,,$A$2:$A$3))
however it only takes first value for each sequence and generates output as {1,10,20}.
Since I really don't want to make my file an xlsm (as macros are not allowed in my org), is there some way to achieve this without VBA?
Thanks,
Ashutosh Deshpande
答案1
得分: 5
以下是已经翻译好的部分：
这是一种方法：
公式在 D1 中：
=LET(x,SEQUENCE(B4),FILTER(x,COUNTIFS(A2:A4,"<="&x,B2:B4,">="&x)))
以防范围不是从1开始，且最后一个单元格不包含最大值：
=LET(x,SEQUENCE(MAX(A2:B4),,MIN(A2:B4)),FILTER(x,COUNTIFS(A2:A4,"<="&x,B2:B4,">="&x)))
英文:
Here is one way:
Formula in D1:
=LET(x,SEQUENCE(B4),FILTER(x,COUNTIFS(A2:A4,"<="&x,B2:B4,">="&x)))
Just in case the range does not start at 1, and the last cell does not hold your max value:
=LET(x,SEQUENCE(MAX(A2:B4),,MIN(A2:B4)),FILTER(x,COUNTIFS(A2:A4,"<="&x,B2:B4,">="&x)))
答案2
得分: 3
这是一种不生成它们所有的方式，但有点长：
=DROP(REDUCE("",A2:A4&"|"&B2:B4,LAMBDA(a,c,LET(split,TEXTSPLIT(c,"|"),start,INDEX(split,1),end,INDEX(split,2),VSTACK(a,SEQUENCE(end-start+1,1,start)))),1)
@JvdV的建议通过行索引更好：
=DROP(REDUCE(0,ROW(A2:A4),LAMBDA(x,y,VSTACK(x,SEQUENCE(1+INDEX(B:B,y)-INDEX(A:A,y),,INDEX(A:A,y)))),1)
英文:
This is a way without generating all of them, but it's a bit long:
=DROP(REDUCE("",A2:A4&"|"&B2:B4,LAMBDA(a,c,LET(split,TEXTSPLIT(c,"|"),start,INDEX(split,1),end,INDEX(split,2),VSTACK(a,SEQUENCE(end-start+1,1,start))))),1)
@JvdV's suggestion of indexing through the rows is better:
=DROP(REDUCE(0,ROW(A2:A4),LAMBDA(x,y,VSTACK(x,SEQUENCE(1+INDEX(B:B,y)-INDEX(A:A,y),,INDEX(A:A,y))))),1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论