Yikes，合并单元格，Excel的（或者更确切地说，它的用户）最可怕的噩梦！既然我们知道只有左上角的单元格有值，我们可以尝试利用其余单元格为空的事实？
x1c 0d1x的数据
B2中的公式：

=SCAN(0,A2:A7,LAMBDA(x,y,IF(y="",x+1,1)))

字符串

使用VBA理解SCAN函数

• 下面是一个基本的低效代码。
• 我写它只是为了更好地理解上面的JvdV公式（如果熟悉VBA）。

Sub ScanVBA()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim cell As Range
    Dim x As Long                             ' '...(0,...,LAMBDA(x,' i.e. x = 0
    Dim y As Variant                          ' '...,A2:A7,LAMBDA(...,y,'

    For Each cell In ws.Range("A2:A7").Cells  ' '=SCAN(...,A2:A7,LAMBDA('
        y = cell.Value                        ' '...,y,'
        If y = "" Then                        ' 'IF(y=""'
            x = x + 1                         ' ',x+1'
        Else
            x = 1                             ' ',1)'
        End If
        cell.EntireRow.Columns("B").Value = x ' write on each iteration
        Debug.Print y, x ' return iteration result in Immediate window (Ctrl+G)
    Next cell

End Sub

字符串

有了合并的单元格，我可以想到一种在VBA中完成此操作的方法（也许有人可以将此逻辑转换为公式）。

Dim dates As Range
Set dates = Worksheets("YourSheet").Range("A2:A100")
Dim i As Integer
Dim cell As Range
' Starting point- set first index
i = 1
dates.Cells(1,1).Value = i
i = i + 1
' Loop through all cells, checking if next is equal or if you have jumped a merged cell
For Each cell in dates
    ' Normal case (next is same as current)
    If cell.Value = cell.Offset(1,0).Value Then
        cell.Offset(1,1).Value = i
    ' The second elseif ensures the jump due to merged cells does not occur
    ElseIf cell.Offset(1,1).Address <> cell.Offset(0,1).Offset(1,0).Address Then
        cell.Offset(0,1).Offset(1,0).Value = i
     ' End condition to stop & Date has changed now
    ElseIf cell.Offset(1,0).Value <> "" Then
        i = 1
        cell.Offset(1,1).Value = i
    End If
    i = i + 1
Next cell

字符串
链接的.Offset是这里的关键点，它检查如果你向下移动1行并移动1列，与如果你移动1列然后向下移动1行，单元格是否相同。