如何高效生成基于整数列表的堆叠整数序列（Excel等方案）

问题描述 #

给定一列整数（如A2:A4），需为每个整数生成从1到该数的连续序列，并将所有序列按顺序堆叠成单列。例如输入2、5、3，输出为1、2、1、2、3、4、5、1、2、3。

原使用的DROP/REDUCE/VSTACK组合公式在处理10k量级数据时速度极慢，需更高效的实现方案。

一、Excel 365 高效动态数组公式 #

利用向量运算替代迭代堆叠，避免循环开销，大幅提升大数据量处理速度：

=LET(
    data, A2:A4,
    cumulative_sum, SCAN(0, data, LAMBDA(prev, curr, prev + curr)),
    total_seq, SEQUENCE(cumulative_sum@),
    total_seq - XLOOKUP(total_seq - 1, cumulative_sum, cumulative_sum, 0, 1)
)

原理说明 #

1. SCAN计算整数列的累计和，确定每个序列的结束位置；
2. SEQUENCE生成总长度的连续序列；
3. XLOOKUP匹配每个序列位置对应的上一个累计和，用当前位置值减去该值得到组内序号。

二、Legacy Excel 方案（无动态数组） #

若使用旧版Excel，可通过辅助列+下拉公式实现：

1. 辅助列（如B列）：计算累计和，B2=A2，B3=B2+A3，依此类推；
2. 结果列（如C列）：在C2输入公式并下拉：

   =IF(ROW(A1)<=B$4,ROW(A1)-IFERROR(INDEX(B$2:B$4,MATCH(ROW(A1)-1,B$2:B$4,1)),0),"")
   

三、Power Query 方案（批量处理最优） #

适合大数据量批量转换，操作步骤：

1. 选中整数列，点击「数据」→「从表格/区域」导入Power Query；
2. 添加自定义列，公式：= {1..[Size]}（将Size替换为你的列名）；
3. 点击自定义列右侧的展开按钮，选择「到新行」；
4. 关闭并上载到Excel。

对应的M代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Sequence", each {1..[Size]}),
    #"Expanded Sequence" = Table.ExpandListColumn(#"Added Custom", "Sequence")
in
    #"Expanded Sequence"

四、VBA 方案（高效数组操作） #

通过内存数组直接生成结果，速度优于公式方案：

Sub GenerateStackedSequences()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim dataArr As Variant
    Dim resultArr() As Long
    Dim totalRows As Long
    Dim i As Long, j As Long, k As Long
    
    Set ws = ActiveSheet
    Set dataRange = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    dataArr = dataRange.Value
    
    '计算总输出行数
    totalRows = Application.Sum(dataRange)
    ReDim resultArr(1 To totalRows, 1 To 1)
    
    k = 1
    '遍历生成序列
    For i = 1 To UBound(dataArr)
        For j = 1 To dataArr(i, 1)
            resultArr(k, 1) = j
            k = k + 1
        Next j
    Next i
    
    '写入结果（从C2开始，可自行调整）
    ws.Range("C2").Resize(totalRows, 1).Value = resultArr
End Sub

内容的提问来源于stack exchange，提问作者VBasic2008