求生成上下限间整数列表的Excel公式或VBA函数

Got it, let's tackle this problem where you need to output all integers from a lower limit (column A) to an upper limit (column B) — including both endpoints — into a list (column C). The number of values will vary depending on the difference between A and B, so we need flexible solutions that adapt automatically. Here are two reliable approaches:

Excel Formula Approaches #

For Excel 365/2021 (Dynamic Array Support) #

If you're using a modern Excel version with dynamic arrays, this is the simplest method. In cell C2, enter this formula:

=SEQUENCE(B2-A2+1, 1, A2, 1)

• How it works:
  • B2-A2+1: Calculates the total number of integers to generate (adding 1 ensures we include both the lower and upper limits)
  • 1: Sets the output to a single column
  • A2: The starting value of the sequence
  • 1: The step between each number (we want consecutive integers)
• The formula will automatically "spill" into the cells below C2 to display the full list — no need to drag or copy it down!

For Older Excel Versions (No Dynamic Arrays) #

If you're on an older version without spill functionality, use this array formula. In cell C2, enter:

=IF(ROW()-ROW($C$2)+1<=B$2-A$2+1, A$2+ROW()-ROW($C$2), "")

Then press Ctrl+Shift+Enter (this tells Excel it's an array formula) and drag the fill handle down C2 to cover enough rows for your largest possible range.

• How it works:
  • ROW()-ROW($C$2)+1: Tracks the position of the current cell relative to the starting cell C2
  • The IF statement checks if we're still within the total number of integers needed; if yes, it calculates the corresponding value, otherwise returns an empty string

VBA Custom Function Approach #

If you want a reusable, version-agnostic solution (or just prefer VBA), create a custom function:

1. Press Alt+F11 to open the VBA Editor
2. Right-click your workbook in the Project Explorer > Insert > Module
3. Paste this code into the module:

Function IntRange(lower As Integer, upper As Integer) As Variant
    Dim result() As Integer
    Dim i As Integer
    Dim count As Integer
    
    ' Handle cases where lower > upper (optional, but adds robustness)
    If lower > upper Then
        Dim temp As Integer
        temp = lower
        lower = upper
        upper = temp
    End If
    
    count = upper - lower + 1
    ReDim result(1 To count)
    
    For i = 1 To count
        result(i) = lower + i - 1
    Next i
    
    ' Transpose to return a vertical array
    IntRange = Application.Transpose(result)
End Function

4. Close the VBA Editor and save your workbook as a .xlsm (macro-enabled) file
5. In cell C2, use the function like this:

=IntRange(A2, B2)

• Bonus: The function includes a check to swap lower/upper if they're reversed, so it works even if your limits are accidentally flipped!

Example Output #

If A2=3 and B2=7, column C will show:

• 3
• 4
• 5
• 6
• 7

If A2=10 and B2=12, column C will show:

• 10
• 11
• 12

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