在 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 或陣列/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