Excel中如何用公式或VBA展开两种表格结构的范围序列？

针对两种表格结构，分别提供序列展开的实现方法：

一、Excel公式实现（适用于Excel 365/2021动态数组版本） #

结构1：前缀与范围在同一单元格（如A1内容为ABD(0-9;A-Z)） #

直接在空白单元格输入以下公式，会自动溢出生成完整序列：

=LET(
    prefix, LEFT(A1,FIND("(",A1)-1),
    range_str, MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),
    num_part, TEXTBEFORE(range_str,";"),
    alpha_part, TEXTAFTER(range_str,";"),
    num_start, VALUE(LEFT(num_part,FIND("-",num_part)-1)),
    num_end, VALUE(RIGHT(num_part,LEN(num_part)-FIND("-",num_part))),
    alpha_start, CODE(LEFT(alpha_part,FIND("-",alpha_part)-1)),
    alpha_end, CODE(RIGHT(alpha_part,LEN(alpha_part)-FIND("-",alpha_part))),
    num_seq, prefix & SEQUENCE(num_end - num_start + 1,1,num_start,1),
    alpha_seq, prefix & CHAR(SEQUENCE(alpha_end - alpha_start + 1,1,alpha_start,1)),
    VSTACK(num_seq, alpha_seq)
)

结构2：前缀与范围分两列（如A1=前缀ABD，B1=范围0-9;A-Z） #

调整引用后使用以下公式：

=LET(
    prefix, A1,
    range_str, B1,
    num_part, TEXTBEFORE(range_str,";"),
    alpha_part, TEXTAFTER(range_str,";"),
    num_start, VALUE(LEFT(num_part,FIND("-",num_part)-1)),
    num_end, VALUE(RIGHT(num_part,LEN(num_part)-FIND("-",num_part))),
    alpha_start, CODE(LEFT(alpha_part,FIND("-",alpha_part)-1)),
    alpha_end, CODE(RIGHT(alpha_part,LEN(alpha_part)-FIND("-",alpha_part))),
    num_seq, prefix & SEQUENCE(num_end - num_start + 1,1,num_start,1),
    alpha_seq, prefix & CHAR(SEQUENCE(alpha_end - alpha_start + 1,1,alpha_start,1)),
    VSTACK(num_seq, alpha_seq)
)

二、VBA代码实现（支持批量处理） #

结构1：前缀与范围在同一单元格（处理A列数据，结果输出到B列） #

1. 按Alt+F11打开VBA编辑器，插入新模块
2. 粘贴以下代码并运行：

Sub ExpandSequence_Struct1()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, j As Long
    Dim prefix As String, rangeStr As String
    Dim numPart As String, alphaPart As String
    Dim numStart As Integer, numEnd As Integer
    Dim alphaStart As Integer, alphaEnd As Integer
    Dim outputRow As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    outputRow = 1
    
    For i = 1 To lastRow
        If InStr(ws.Cells(i, "A").Value, "(") > 0 Then
            prefix = Left(ws.Cells(i, "A").Value, InStr(ws.Cells(i, "A").Value, "(") - 1)
            rangeStr = Mid(ws.Cells(i, "A").Value, InStr(ws.Cells(i, "A").Value, "(") + 1, _
                        InStr(ws.Cells(i, "A").Value, ")") - InStr(ws.Cells(i, "A").Value, "(") - 1)
            
            numPart = Split(rangeStr, ";")(0)
            alphaPart = Split(rangeStr, ";")(1)
            
            numStart = Val(Split(numPart, "-")(0))
            numEnd = Val(Split(numPart, "-")(1))
            For j = numStart To numEnd
                ws.Cells(outputRow, "B").Value = prefix & CStr(j)
                outputRow = outputRow + 1
            Next j
            
            alphaStart = Asc(Split(alphaPart, "-")(0))
            alphaEnd = Asc(Split(alphaPart, "-")(1))
            For j = alphaStart To alphaEnd
                ws.Cells(outputRow, "B").Value = prefix & Chr(j)
                outputRow = outputRow + 1
            Next j
        End If
    Next i
End Sub

结构2：前缀与范围分两列（A列前缀，B列范围，结果输出到C列） #

同样在VBA模块中粘贴以下代码并运行：

Sub ExpandSequence_Struct2()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, j As Long
    Dim prefix As String, rangeStr As String
    Dim numPart As String, alphaPart As String
    Dim numStart As Integer, numEnd As Integer
    Dim alphaStart As Integer, alphaEnd As Integer
    Dim outputRow As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    outputRow = 1
    
    For i = 1 To lastRow
        prefix = ws.Cells(i, "A").Value
        rangeStr = ws.Cells(i, "B").Value
        
        numPart = Split(rangeStr, ";")(0)
        alphaPart = Split(rangeStr, ";")(1)
        
        numStart = Val(Split(numPart, "-")(0))
        numEnd = Val(Split(numPart, "-")(1))
        For j = numStart To numEnd
            ws.Cells(outputRow, "C").Value = prefix & CStr(j)
            outputRow = outputRow + 1
        Next j
        
        alphaStart = Asc(Split(alphaPart, "-")(0))
        alphaEnd = Asc(Split(alphaPart, "-")(1))
        For j = alphaStart To alphaEnd
            ws.Cells(outputRow, "C").Value = prefix & Chr(j)
            outputRow = outputRow + 1
        Next j
    Next i
End Sub

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