Wie kann ich in Excel eine dynamische Liste der unterschiedlichen Werte aus derselben Spalte in drei Tabellen erhalten?

Question

Ich habe 3 Tabellen in derselben Excel-Arbeitsmappe, die alle dieselben Spaltenüberschriften verwenden.

Ich möchte eine Spalte in einer vierten Tabelle haben, diedynamischAktualisieren Sie mit allen unterschiedlichen Werten, die in allen drei Tabellen in derselben Spalte gefunden werden (z. B. Table01[MyCol], Table02[MyCol], Table03[MyCol]).

Eine VBA-Lösung ist in Ordnung. Ich weiß nur nicht, wo ich anfangen soll.

Answer 1

Eine VBA-Lösung ist in Ordnung. Ich weiß nur nicht, wo ich anfangen soll.

Wenn du etwas willst„das dynamisch aktualisiert werden kann“, suchen Sie wahrscheinlich nach einer Lösung, die auf einem „Ereignis“ basiert. Es gibt Ereignisse, die durch Aktualisierungen von Tabellen aus anderen Quellen sowie durch Änderungen an Arbeitsblättern ausgelöst werden.

Wenn sich alle Ihre Tabellen auf demselben Arbeitsblatt befinden, sollte ein Arbeitsblatt-Ereignisauslöser ausreichen. Wenn sich die Tabellen auf unterschiedlichen Arbeitsblättern befinden, benötigen Sie einen Arbeitsmappen-Ereignisauslöser.

Jedes Mal, wenn ich die Begriffe höre„unterschiedliche Werte“Und'VBA'im selben Absatz denke ich sofort an ein VBA-Skriptwörterbuch. Das WörterbuchSchlüsselsind eindeutig (optional entweder Groß-/Kleinschreibung beachtend oder nicht).

Da die betreffende Spalte„dieselbe Spalte in allen 3 Tabellen“, sollten Sie wahrscheinlich eine öffentliche oder private Konstantenvariable verwenden, um ihren Namen zu bestimmen. Wenn sich der Name der Spalten jemals ändern sollte, müssen Sie ihn nur an einer Stelle ändern.

Der Umgang mit strukturierten Tabellen in VBA kann mühsam sein. Verschiedene Methoden haben unterschiedliche Vor- und Nachteile. Ich habe festgestellt, dass die Methode mit den wenigsten Nachteilen Range("Table01").ListObjecteine ListObject-Tabelle ist, insbesondere beim Umgang mit den Tabellen einer Arbeitsmappe und nicht nur mit den Tabellen eines einzelnen Arbeitsblatts.

Durchlaufen Sie nicht Zelle für Zelle die Spalten jeder Tabelle. Lesen Sie die Spalten jeder Tabelle in ein Variantenarray ein und durchlaufen Sie das Array. Das ist schneller und weniger rechenintensiv.

Hier ist ein Code, der eine eindeutige Werteliste aus drei Tabellen auf zwei Arbeitsblättern in eine vierte Tabelle auf einem dritten Arbeitsblatt ableitet. Er gehört in dieDiesesArbeitsbuchCodeblatt. Zu Ihrem Nutzen ausführlich kommentiert.

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

Gerne beantworte ich Ihnen auch Fragen, zu denen Sie bereits recherchiert haben und auf die Sie keine Antwort finden konnten.

Answer 2

Anstelle von VBA-Code würde ich eine Array(CSE)-Formel vorschlagen, die eine eindeutige Liste in einer anderen Spalte erstellt.

Wie es funktioniert:

• Erstellen Sie drei Tabellen und benennen Sie sie als NameTBL, NameTBL1& NameTBL2.

• Formel in Zelle 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))), "")}
  

• Beenden Sie die Formel mitStrg+Umschalt+Eingabeund füllen Sie es nach unten aus (bis auf ein paar zusätzliche Reihen).

---

• Neue Daten wurden zu Tabelle 1 hinzugefügt

---

• Neue Daten in Tabelle 3 hinzugefügt.

Achtung

• Da Excel die zugehörigen Tabellen automatisch aktualisiert, sobald neue Daten vorliegen, werden in die Formel Column Jdynamisch neue Werte eingefügt.

• Sie können eindeutige Werte in column Jeine Tabelle umwandeln.

Answer 3

Ein guter Ausgangspunkt ist meiner Meinung nachhttps://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/, das Variationen zur Groß- und Kleinschreibung sowie zum Ignorieren von Zahlen und Leerzeichen enthält.

Anstelle von VBA oder einer Array/CSE-Formel würde ich diese normale Formel bevorzugen. Beachten Sie, dass sie einen relativen Verweis auf die Zelle direkt darüber enthält. Beispielsweise bezieht sich die Formel in Zelle D5 auf den Bereich von TableHeader bis D4 und nicht auf D1, wie unten gezeigt.

=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)),
                                   "")
                 )
)

Diese Formel COUNTIFkonvertiert zunächst die Liste der Quellwerte {A,B,B,C} in eine Liste von Einsen und Nullen, basierend auf den Werten, die bereits in der eindeutigen Liste gefunden wurden. Wenn die Werte „A“ und „B“ bereits in der eindeutigen Liste enthalten sind, „C“ jedoch nicht, würde in diesem Beispiel {A,B,B,C} in {1,1,1,0} umgewandelt.

Zweitens wird MATCHdas binäre Array verwendet, um den ersten „0“-Wert zu finden. Dies ist die Position einer Quelllistenzelle mit einem Wert, der noch nicht in der eindeutigen Liste enthalten ist. Das ist Position 4 für den obigen „C“-Wert.

Drittens wird es verwendet, INDEXum den mit der gefundenen Position verknüpften Wert abzurufen MATCH. In unserem Beispiel gibt es „C“ zurück.

Zuletzt wird (ein leerer Zellenwert) IFERRORzurückgegeben , wenn keine weiteren Übereinstimmungen gefunden wurden."""

Answer 4

Wenn Ihre Excel-Version über die Funktion „Dynamische Arrays“ verfügt, UNIQUEkönnen Sie mit der Funktion Folgendes verwenden:

=UNIQUE(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(",",TRUE,Table01[MyCol],Table02[MyCol],Table03[MyCol]),",","</s><s>")& "</s></t>","//s"))

Algorithmus

• Fügen Sie den gesamten Text mithilfe TEXTJOINder Funktion zusammen, um eine durch Kommas getrennte Liste zu erstellen(oder verwenden Sie ein anderes Trennzeichen, wenn Ihre Zeichenfolgen Kommas enthalten)

   TEXTJOIN(",",TRUE,Table01[MyCol],Table02[MyCol],Table03[MyCol])`
  

• Erstellen Sie eine XML, wobei jeder Knoten eines der durch Kommas getrennten Elemente ist
• Wird verwendet FILTERXML, um die Knoten als Array zurückzugeben.
• Verwenden Sie die UNIQUEFunktion, um nur die eindeutigen Einträge zurückzugeben.