使用公式（Formula2）或值填充Excel表

• 丑陋的布局是用来强调table的位置并不重要。

公式（Microsoft 365）

• 第一代码基于以下公式：

=LET(c,[PropertyNumber],v,[@PropertyNumber],
    r,ROW(v)-ROW(RealProperty[#Headers]),
    i,INDEX(c,SEQUENCE(r)),
ROWS(FILTER(i,i=v)))

其中RealProperty是表名，PropertyNumber是要“sequenced”的列的标题。

Sub UpdateSequenceFormulas()

    Const SRC_TABLES As String = "RealProperty,RentalProperty,TaxableMajor" _
        & "Maintenance,RepairExpenses,SubTable"
    Const COL_NUM As String = "PropertyNumber"
    Const COL_SEQ As String = "Sequence"
    
    Dim ws As Worksheet: Set ws = RealPropertySht
    
    Dim tNames() As String: tNames = Split(SRC_TABLES, ",")
    
    Dim lo As ListObject, srg As Range, Data(), t As Long, Formula As String
        
    For t = 0 To UBound(tNames)
        On Error Resume Next
            Set lo = ws.ListObjects(tNames(t))
        On Error GoTo 0
        If Not lo Is Nothing Then
            Formula = "=LET(c,[" & COL_NUM & "],v,[@" & COL_NUM & "]," _
                & vbLf & "    r,ROW(v)-ROW(" & tNames(t) & "[#Headers])," _
                & vbLf & "    i,INDEX(c,SEQUENCE(r))," _
                & vbLf & "ROWS(FILTER(i,i=v)))"
            'Debug.Print Formula
            Set srg = lo.ListColumns(COL_SEQ).DataBodyRange
            srg.Formula2 = Formula
            Set lo = Nothing
        End If
    Next t
    
    MsgBox "Sequence formulas updated.", vbInformation

End Sub

数值

Sub UpdateSequenceValues()

    Const SRC_TABLES As String = "RealProperty,RentalProperty,TaxableMajor" _
        & "Maintenance,RepairExpenses,SubTable"
    Const COL_NUM As String = "PropertyNumber"
    Const COL_SEQ As String = "Sequence"
    
    Dim ws As Worksheet: Set ws = RealPropertySht
    
    Dim tNames() As String: tNames = Split(SRC_TABLES, ",")
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim lo As ListObject, nrg As Range, srg As Range, Data()
    Dim t As Long, r As Long, rCount As Long, rStr As String
        
    For t = 0 To UBound(tNames)
        On Error Resume Next
            Set lo = ws.ListObjects(tNames(t))
        On Error GoTo 0
        If Not lo Is Nothing Then
            Set nrg = lo.ListColumns(COL_NUM).DataBodyRange
            rCount = nrg.Rows.Count
            If rCount = 1 Then
                ReDim Data(1 To 1, 1 To 1)
            Else
                Data = nrg.Value
            End If
            For r = 1 To rCount
                rStr = CStr(Data(r, 1))
                dict(rStr) = dict(rStr) + 1
                Data(r, 1) = dict(rStr)
            Next r
            Set srg = lo.ListColumns(COL_SEQ).DataBodyRange
            srg.Value = Data
            dict.RemoveAll
            Set lo = Nothing
        End If
    Next t
    
    MsgBox "Sequences values updated.", vbInformation
    
End Sub

另一种可能性是创建一个“命名的”LAMBDA函数;然而，它确实使用了“volatile”函数，这可能会使重新计算陷入困境（根据@VBasic2008的评论）。不过，对于小数据集来说，这应该不是问题。
诀窍是为你的函数定义一个名称（例如，“ColumnSeq”），并将其定义设置为LAMBDA =LAMBDA(...)。然后，您可以在表中使用=ColumnSeq(...)。
LAMBDA将会是

=LAMBDA(COL,LET(Base,INDIRECT(CELL("address",COL)),
    Nbr,ROW()-ROW(Base)+1,
    Rng,OFFSET(Base,0,0,Nbr),
    COUNTIF(Rng,INDEX(COL,Nbr,))))

当您在 entire 表列上调用ColumnSeq（省略“@”，例如，[PropertyNumber]而不是[@PropertyNumber]）时，LAMBDA中的COL将表示整个表列。
CELL("address",COL)返回目标区域左上角单元格的地址，然后INDIRECT将“锁定”序列的起始区域，而无需知道表名。Base则是该列的左上角单元格。
ROW()返回列中每一行的行号，因此减去ROW(Base)并加上1将得到表中每行的相对位置。
然后，Rng使用OFFSET从Base开始，并包括Nbr行，导致“列中的第一个Nbr行”。
INDEX函数从列中返回行的值，因此COUNTIF对列的“前Nbr行”进行操作，仅计算与各行值匹配的行。或者列中每个值的序列。
同样，所有这些都在名称定义（CTRL + F3）中。每个表中的函数可以是=ColumnSeq([ColName])，其中“ColName”是要排序的列的名称。