☰

如何在 Excel 中根据某些条件创建自动序列？

tag-icon • tag-icon microsoft-excel worksheet-function
如何在 Excel 中根据某些条件创建自动序列？

例如我有一个范围

开始于 : SF1A1 结束于 : SF23A8

预期范围如下

SF1A1
SF1A2
SF1A3
SF1A4
SF1A5
SF1A6
SF1A7
SF1A8

SF2A1
SF2A2
SF2A3
SF2A4
SF2A5
SF2A6
SF2A7
SF2A8
.
.
.
SF23A1
SF23A2
SF23A3
SF23A4
SF23A5
SF23A6
SF23A7
SF23A8

答案1

这个公式可以做到这一点：

="SF" & CEILING.MATH((ROW())/8) & "A" & (MOD(ROW()-1,8)+1)

将其放在工作表第一行的任意位置并向下展开。

编辑：如果您需要它自动从某个范围扩展到某个范围，我认为您必须使用 VBA。您可以尝试这个宏：

Sub sfa()
    Dim rng As String
    rng = InputBox("please enter range, example:" & vbCrLf & "SF5A2-SF7A7")
    Dim sfStart As Long, aStart As Long, sfEnd As Long, aEnd As Long
    
    On Error GoTo inputErrorHandling
        sfStart = Mid(Split(rng, "-")(0), 3, Len(Split(rng, "-")(0)) - 4)
        aStart = Right(Split(rng, "-")(0), 1)
        sfEnd = Mid(Split(rng, "-")(1), 3, Len(Split(rng, "-")(1)) - 4)
        aEnd = Right(Split(rng, "-")(1), 1)
    On Error GoTo 0
    
    
    Dim startVal As Long, endVal As Long
    startVal = (sfStart - 1) * 8 + aStart
    endVal = (sfEnd - 1) * 8 + aEnd
    
    If endVal - startVal > 1000 Then
        If MsgBox("WARNING, the range you are trying to create is " & (endVal - startVal) & " rows long. Do you want to proceed?", vbYesNo) = vbNo Then
            Exit Sub
        End If
    End If
    
    For i = startVal To endVal
        Dim x As String
        Cells(Selection.Row + i - startVal, Selection.Column) = "SF" & WorksheetFunction.Ceiling_Math(i / 8) & "A" & (((i - 1) Mod 8) + 1)
    Next i
    Exit Sub
inputErrorHandling:
    On Error GoTo 0
    MsgBox ("invalid input")
End Sub

使用方法：选择您希望序列开始的单元格并运行宏。它会要求输入一个范围，您可以使用您的示例，但用连字符分隔序列的开始和结束（即 SF1A1-SF23A8）

相关内容