在 Excel 中，如何获取 3 个表中同一列的不同值的动态列表？

Question 1

VBA 解决方案很好；我只是不知道从哪里开始。

如果你想要一些东西“可以动态更新”，您可能正在寻找基于“事件”的解决方案。其他来源对表格的更新以及对任何工作表的任何更改都会触发事件。

如果您的表格都在同一张工作表上，那么您应该能够使用工作表事件触发器；如果它们位于不同的工作表上，那么您需要一个工作簿事件触发器。

每次我听到“独特价值观”和‘VBA’在同一段中，我立即开始考虑 VBA 脚本词典。该词典的键是唯一的（可以选择区分大小写或不区分大小写）。

由于所讨论的列是“3 个表中的同一列”，您可能应该使用公共或私有常量变量来确定其名称。如果列的名称要更改，则只需在一个地方进行更改。

在 VBA 中处理结构化表格可能很麻烦。不同的方法有不同的优缺点。我发现缺点最少的方法是使用Range("Table01").ListObjectListObject 表，尤其是在处理工作簿的表格而不仅仅是单个工作表的表格时。

不要逐个单元格地循环遍历每个表的列。将每个表的列读入变体数组并循环遍历该数组。这样速度更快，计算量更少。

以下代码将两个工作表上的三个表中的唯一值列表导出到第三个工作表上的第四个表中。它属于本工作簿代码表。为方便您阅读，这里进行了大量的注释。

Option Explicit

'declare the common column's name available to all sub procedures within this code sheet
Private Const col As String = "col2"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'the Intersect function sees if you have changed anything within the tables
    'you cannot Intersect across worksheets so you need to know what worksheet contains which table
    'in this case, Table01 and Table02 are on Sheet1, Table03 is on Sheet2
    'note the use of the LCase function
    Select Case LCase(Sh.Name)
        Case "sheet1"
            'did the change event (add/update/remove) occur on Sheet1.Table01 or Sheet1.Table02
            If Not Intersect(Target, Range("Table01").ListObject.ListColumns(col).DataBodyRange, _
                                     Range("Table02").ListObject.ListColumns(col).DataBodyRange) Is Nothing Then
                'set error control
                On Error GoTo byebye
                'disable events so this doesn't run on top of itself when Table04 is updated
                Application.EnableEvents = False
                'run the Table04 update procedure
                UpDate_Table04
            End If
        Case "sheet2"
            'did the change event (add/update/remove) occur on Sheet2.Table03
            If Not Intersect(Target, Range("Table03").ListObject.ListColumns(col).DataBodyRange) Is Nothing Then
                'see above
                On Error GoTo byebye
                'see above
                Application.EnableEvents = False
                'see above
                UpDate_Table04
            End If
        Case Else
            'do nothing (placeholder for other considerations)
    End Select

'error control 'catcher'
byebye:
    'reenable event handling for future operations
    Application.EnableEvents = True

End Sub

Private Sub UpDate_Table04()

    'declare variables
    Dim t As Long, v As Long, tbls As Variant, vals As Variant
    'declare a reusable variable for the dictionary
    Static d As Object

    'assign the source table names to a variant array
    tbls = Array("Table01", "Table02", "Table03")

    'determine if the dictionary has been created
    'if so remove all previous entries; if not create one and make it non-case-sensitive
    If d Is Nothing Then
        'dictionary does not exist; create one and make it non-case-sensitive
        Set d = CreateObject("scripting.dictionary")
        d.CompareMode = vbTextCompare
    Else
        'dictionary exists; remove all previous entries
        d.RemoveAll
    End If

    'loop through the three source tables
    For t = LBound(tbls) To UBound(tbls)

        'retrieve the values from this table's common column
        vals = Application.Transpose(Range(tbls(t)).ListObject.ListColumns(col).DataBodyRange.Value2)

        'loop through the values and create create unique dictionary keys using the faster Add/Overwrite method
        'this method does not require checking for identical previous additions
        For v = LBound(vals) To UBound(vals)
            'Add/Overwrite method
            d(vals(v)) = vbNullString
        Next v

    Next t

    'work with the destination table
    'this reference method does not require worksheet reference within ThisWorkbook
    With Range("Table04").ListObject
        'make room/remove rows on the destination table
        .Resize .HeaderRowRange.Cells(1).Resize(d.Count + 1, .ListColumns.Count)
        'put the dictionary keys into the destination table
        .ListColumns(col).DataBodyRange = Application.Transpose(d.keys)
    End With


End Sub

我愿意回答您已经研究过但无法找到答案的任何问题。

Answer

VBA 解决方案很好；我只是不知道从哪里开始。

如果你想要一些东西“可以动态更新”，您可能正在寻找基于“事件”的解决方案。其他来源对表格的更新以及对任何工作表的任何更改都会触发事件。

如果您的表格都在同一张工作表上，那么您应该能够使用工作表事件触发器；如果它们位于不同的工作表上，那么您需要一个工作簿事件触发器。

每次我听到“独特价值观”和‘VBA’在同一段中，我立即开始考虑 VBA 脚本词典。该词典的键是唯一的（可以选择区分大小写或不区分大小写）。

由于所讨论的列是“3 个表中的同一列”，您可能应该使用公共或私有常量变量来确定其名称。如果列的名称要更改，则只需在一个地方进行更改。

在 VBA 中处理结构化表格可能很麻烦。不同的方法有不同的优缺点。我发现缺点最少的方法是使用Range("Table01").ListObjectListObject 表，尤其是在处理工作簿的表格而不仅仅是单个工作表的表格时。

不要逐个单元格地循环遍历每个表的列。将每个表的列读入变体数组并循环遍历该数组。这样速度更快，计算量更少。

以下代码将两个工作表上的三个表中的唯一值列表导出到第三个工作表上的第四个表中。它属于本工作簿代码表。为方便您阅读，这里进行了大量的注释。

Option Explicit

'declare the common column's name available to all sub procedures within this code sheet
Private Const col As String = "col2"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'the Intersect function sees if you have changed anything within the tables
    'you cannot Intersect across worksheets so you need to know what worksheet contains which table
    'in this case, Table01 and Table02 are on Sheet1, Table03 is on Sheet2
    'note the use of the LCase function
    Select Case LCase(Sh.Name)
        Case "sheet1"
            'did the change event (add/update/remove) occur on Sheet1.Table01 or Sheet1.Table02
            If Not Intersect(Target, Range("Table01").ListObject.ListColumns(col).DataBodyRange, _
                                     Range("Table02").ListObject.ListColumns(col).DataBodyRange) Is Nothing Then
                'set error control
                On Error GoTo byebye
                'disable events so this doesn't run on top of itself when Table04 is updated
                Application.EnableEvents = False
                'run the Table04 update procedure
                UpDate_Table04
            End If
        Case "sheet2"
            'did the change event (add/update/remove) occur on Sheet2.Table03
            If Not Intersect(Target, Range("Table03").ListObject.ListColumns(col).DataBodyRange) Is Nothing Then
                'see above
                On Error GoTo byebye
                'see above
                Application.EnableEvents = False
                'see above
                UpDate_Table04
            End If
        Case Else
            'do nothing (placeholder for other considerations)
    End Select

'error control 'catcher'
byebye:
    'reenable event handling for future operations
    Application.EnableEvents = True

End Sub

Private Sub UpDate_Table04()

    'declare variables
    Dim t As Long, v As Long, tbls As Variant, vals As Variant
    'declare a reusable variable for the dictionary
    Static d As Object

    'assign the source table names to a variant array
    tbls = Array("Table01", "Table02", "Table03")

    'determine if the dictionary has been created
    'if so remove all previous entries; if not create one and make it non-case-sensitive
    If d Is Nothing Then
        'dictionary does not exist; create one and make it non-case-sensitive
        Set d = CreateObject("scripting.dictionary")
        d.CompareMode = vbTextCompare
    Else
        'dictionary exists; remove all previous entries
        d.RemoveAll
    End If

    'loop through the three source tables
    For t = LBound(tbls) To UBound(tbls)

        'retrieve the values from this table's common column
        vals = Application.Transpose(Range(tbls(t)).ListObject.ListColumns(col).DataBodyRange.Value2)

        'loop through the values and create create unique dictionary keys using the faster Add/Overwrite method
        'this method does not require checking for identical previous additions
        For v = LBound(vals) To UBound(vals)
            'Add/Overwrite method
            d(vals(v)) = vbNullString
        Next v

    Next t

    'work with the destination table
    'this reference method does not require worksheet reference within ThisWorkbook
    With Range("Table04").ListObject
        'make room/remove rows on the destination table
        .Resize .HeaderRowRange.Cells(1).Resize(d.Count + 1, .ListColumns.Count)
        'put the dictionary keys into the destination table
        .ListColumns(col).DataBodyRange = Application.Transpose(d.keys)
    End With


End Sub

我愿意回答您已经研究过但无法找到答案的任何问题。

Question 2

我建议使用数组（CSE）公式而不是 VBA 代码，它将在另一列中创建唯一列表。

怎么运行的：

创建三个表并将其命名为NameTBL, NameTBL1& NameTBL2。

单元格中的公式J36：

{=IFERROR(IFERROR (IFERROR(INDEX(NameTBL[City1],MATCH(0, COUNTIF($J$35:J35, NameTBL[City1])+(NameTBL[City1]=""), 0)), INDEX(NameTBL1[City2], MATCH(0, COUNTIF($J$35:J35, NameTBL1[City2])+(NameTBL1[City2]=""), 0))), INDEX(NameTBL2[City3], MATCH(0, COUNTIF($J$35:J35, NameTBL2[City3])+(NameTBL2[City3]=""), 0))), "")}

完成配方Ctrl+Shift+Enter并向下填充（直到多出几行）。

表 1 中添加了新数据

表 3 中添加了新数据。

注意：

由于 Excel 在获得新数据时会自动更新相关表，因此公式中Column J会动态包含新值。
您可以将唯一值转换为column J表中的值。

Answer

我建议使用数组（CSE）公式而不是 VBA 代码，它将在另一列中创建唯一列表。

怎么运行的：

创建三个表并将其命名为NameTBL, NameTBL1& NameTBL2。

单元格中的公式J36：

{=IFERROR(IFERROR (IFERROR(INDEX(NameTBL[City1],MATCH(0, COUNTIF($J$35:J35, NameTBL[City1])+(NameTBL[City1]=""), 0)), INDEX(NameTBL1[City2], MATCH(0, COUNTIF($J$35:J35, NameTBL1[City2])+(NameTBL1[City2]=""), 0))), INDEX(NameTBL2[City3], MATCH(0, COUNTIF($J$35:J35, NameTBL2[City3])+(NameTBL2[City3]=""), 0))), "")}

完成配方Ctrl+Shift+Enter并向下填充（直到多出几行）。

表 1 中添加了新数据

表 3 中添加了新数据。

注意：

由于 Excel 在获得新数据时会自动更新相关表，因此公式中Column J会动态包含新值。
您可以将唯一值转换为column J表中的值。

Question 3

我发现一个很好的起点是https://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/，其中包括区分大小写以及忽略数字和空格的变体。

与 VBA 或 Array/CSE 公式相比，我更喜欢使用这个常规公式。请注意，它包含对正上方单元格的相对引用，例如单元格 D5 中的公式引用从 TableHeader 到 D4 的范围，而不是如下所示的 D1。

=IFERROR( INDEX( Table1[MyCol], MATCH(0, INDEX( COUNTIF(TableDistinct[[#Headers],[DistinctVals]]:D1,Table1[MyCol]),0,0),0)),
                 IFERROR( INDEX( Table2[MyCol], MATCH(0, INDEX( COUNTIF(TableDistinct[[#Headers],[DistinctVals]]:D1,Table2[MyCol]),0,0),0)),
                          IFERROR( INDEX( Table3[MyCol], MATCH(0,INDEX(COUNTIF(TableDistinct[[#Headers],[DistinctVals]]:D1,Table3[MyCol]),0,0),0)),
                                   "")
                 )
)

此公式首先COUNTIF根据不同列表中已找到的值将源值列表 {A,B,B,C} 转换为 1 和 0 的列表。如果“A”和“B”值已存在于不同列表中，但“C”不存在于其中，则在此示例中，它会将 {A,B,B,C} 转换为 {1,1,1,0}

其次，它使用MATCH二进制数组来查找第一个“0”值，即不同列表中尚未包含值的源列表单元格的位置。即上面的“C”值的位置 4。

第三，它用于INDEX获取与找到的位置相关的值MATCH，在我们的示例中它返回“C”。

最后，当没有找到更多匹配项时，它会IFERROR返回（空白单元格值）。"""

Answer