excel - 是否有收缩单元格中数字系列或范围的功能？

Question

我有一个电子表格，其中包括以逗号分隔的年份范围，在其他用途​​中可能难以阅读，例如:

1900, 1901, 1902, 1904, 1905, 1906, 1910, 1911, 1912, 1913, 1914, 1915, etc.

理想情况下，我会将范围缩小/截断以更清晰:

1900–1902, 1904–1906, 1910–1915, etc.

我怎样才能最好地在 Excel 中实现这一点？

Answer 1

你可以把它放到一个函数中，这样你就可以在 VBA 甚至公式中使用它:

因此，您需要做的就是将输入序列拆分为 ", "并循环遍历数字以测试当前数字是否 f(i)与之前的编号 f(i-1) 连续.如果它们不连续，您需要结束当前范围并开始一个新范围。

只有一个棘手的部分，那就是避免单年范围显示为 …, 1918-1918, …并输出 …, 1918, …反而。

Option Explicit

Public Function ReduceConsecutiveNumbersToRanges(ByVal InputSequence As String) As String
    Dim InputArr() As String
    InputArr = Split(InputSequence, ", ")

    Dim OutputSeqStart As Long  'current range start
    OutputSeqStart = InputArr(0)

    Dim OutputSequence As String

    'start output sequence
    OutputSequence = OutputSeqStart

    'find gaps to write ranges
    Dim i As Long
    For i = LBound(InputArr) + 1 To UBound(InputArr)
        If Not InputArr(i) = InputArr(i - 1) + 1 Then
            'current number is not in sequence so we need to end current sequence and start a new one
            If InputArr(i - 1) = OutputSeqStart Then 'check if it is a single-year-range only to avoid outputs like `…, 1918-1918, …` and output `…, 1918, …` instead
                OutputSequence = OutputSequence & ", " & InputArr(i)
                OutputSeqStart = InputArr(i)
            Else
                OutputSequence = OutputSequence & "-" & InputArr(i - 1) & ", " & InputArr(i)
                OutputSeqStart = InputArr(i)
            End If
        End If
    Next i

    'end output sequence if it is not a single-year range
    If Not InputArr(i - 1) = OutputSeqStart Then
        OutputSequence = OutputSequence & "-" & InputArr(i - 1)
    End If

    ReduceConsecutiveNumbersToRanges = OutputSequence
End Function

Public Sub TestFunction()
    Debug.Print ReduceConsecutiveNumbersToRanges("1900, 1901, 1902, 1904, 1905, 1906, 1910, 1911, 1912, 1913, 1914, 1915, 1918, 1920, 1921")
    'output will be: 1900-1902, 1904-1906, 1910-1915, 1918, 1920-1921
End Sub

会减少

1900, 1901, 1902, 1904, 1905, 1906, 1910, 1911, 1912, 1913, 1914, 1915, 1918, 1920, 1921

至

1900-1902, 1904-1906, 1910-1915, 1918, 1920-1921