Kombiniere 2 Excel-Tabellen zu einem, der die Daten anfügt?

  

Diese Antwort bezieht sich auf Strukturierte Tabellen nach Interpretation durch Excel. Während die Methoden leicht in Rohdatenmatrizen ohne zugewiesene Tabellenstruktur umgeschrieben werden können, werden die Formeln und das VBA-Coding für diese Lösung auf echte strukturierte Tabellen ausgerichtet sein.

Präambel

Eine dritte Tabelle kann die kombinierten Daten zweier Tabellen mit einigen nativen Worksheet-Formeln verwalten, aber die dritte Tabelle korrekt formatieren, wenn Zeilen zu den abhängigen Tabellen hinzugefügt oder daraus gelöscht werden, erfordert entweder manuelle Größenänderungsoperationen oder einige VBA, die diese nachverfolgt ändert und passt die dritte Tabelle entsprechend an. Ich habe Optionen hinzugefügt, um sowohl den Arbeitsblattnamen der Quelltabelle als auch einen Tabellenwartungs-VBA-Code am Ende dieser Antwort hinzuzufügen.

  

Wenn Sie nur eine funktionsfähige Beispielarbeitsmappe ohne alle Erläuterungen wünschen, können Sie am Ende dieser Antwort einen Link zu der Arbeitsmappe anzeigen, die zum Erstellen dieser Prozedur verwendet wird.

Beispieldatentabellen

Ich habe die Beispieldaten des OP verwendet, um zwei Tabellen zu erstellen, die standardmäßig Table1 und Table2 in den Arbeitsblättern Sheet1 und Sheet2 genannt werden. Ich habe sie absichtlich durch unterschiedliche Grade von der A1-Zelle jedes Arbeitsblatts versetzt, um zu demonstrieren, dass eine strukturierte Tabelle entweder sich selbst oder eine andere strukturierte Tabelle in einer Formel als separate Entität unabhängig von ihrer Position im übergeordneten Arbeitsblatt ansprechen kann. Die dritte Tabelle wird in ähnlicher Weise aufgebaut sein. Diese Offsets dienen nur zu Demonstrationszwecken. Sie sind nicht erforderlich.

Schritt 1: Erstellen Sie die dritte Tabelle

Erstellen Sie die Kopfzeilen für die dritte Tabelle, und wählen Sie diese zukünftige Kopfzeile und mindestens eine Zeile darunter aus, um den Befehl Einfügen ► Tabellen ► Tabelle auf

Ihre neue leere dritte Tabelle im Tabellenblatt Sheet3 sollte der folgenden ähneln.

Schritt 2: Füllen Sie die dritte Tabelle

Beginnen Sie mit dem Auffüllen der ersten Zelle in der DataBodyRange <-Tabelle der dritten Tabelle / a>. In diesem Beispiel wäre das Blatt3! C6. Geben Sie die folgende Formel in C6 ein oder fügen Sie sie ein, wobei zu beachten ist, dass sie auf den Standardtabellennamen basiert. Wenn Sie Ihre Tabellennamen geändert haben, passen Sie sie entsprechend an.

Die Funktion INDEX ruft zuerst alle verfügbaren Elemente ab Zeile von Tabelle1 . Die tatsächlichen Zeilennummern werden mit der Funktion ROW abgeleitet bestimmte Teile des strukturierten Tisches zusammen mit etwas Mathe referenzieren. Wenn Tabelle 1 keine Zeilen mehr enthält, wird der Abruf an eine zweite INDEX-Funktion übergeben, die auf Tabelle2 durch die IFERROR-Funktion und ihre sequentiellen Zeilen werden mit der ROW abgerufen und ROWS-Funktionen mit ein bisschen mehr Mathematik. Die Funktion COLUMN wird als COLUMN(A:A) verwendet wird die erste Spalte der referenzierten Tabelle abrufen, unabhängig davon, wo sie sich auf dem Arbeitsblatt befindet. Dies wird zu der zweiten, dritten usw. Spalte fortschreiten, wenn die Formel richtig gefüllt ist.

Apropos Füllen rechts, füllen Sie die Formel rechts bis E6. Sie sollten etwas haben, das ungefähr dem folgenden entspricht.

Schritt 2.5: [optional] Fügen Sie den übergeordneten Arbeitsblattnamen der Quelltabelle

Ergreifen Sie in der unteren rechten Ecke den Grifftabellenbereich (angezeigt durch den orangefarbenen Pfeil im Beispielbild unten) und ziehen Sie ihn um eine Spalte nach rechts, um der Tabelle eine neue Spalte hinzuzufügen. Benennen Sie die Kopfzeilenbezeichnung in etwas passenderes als die Standardbezeichnung um. Ich habe Blatt als Spaltenbeschriftung verwendet.

Sie können den Arbeitsblattnamen der Quellentabelle nicht direkt abrufen, sondern Die CELL-Funktion kann den vollqualifizierten Pfad, den Dateinamen und das Arbeitsblatt einer Zelle in einer gespeicherten Arbeitsmappe¹ als eine davon abrufen optionale info_types .

Fügen Sie die folgende Formel in die leere Zelle von Table3 in der ersten Zeile der neuen Spalte ein, die Sie gerade erstellt haben.

Füllen Sie Table3 mit

Wenn Sie nicht planen, dieses kleine Projekt mit VBA abzuschließen, um die Dimensionen von Table3 beizubehalten, wenn Zeilen aus einer der beiden Quellentabellen hinzugefügt oder gelöscht werden, greifen Sie einfach den Größenänderungsgriff von Table3 und ziehen Sie nach unten, bis Sie alle Daten gesammelt haben von beiden Tabellen. Am Ende dieser Antwort finden Sie ein Beispielbild der erwarteten Ergebnisse.

Wenn Sie planen, einige VBA hinzuzufügen, überspringen Sie die vollständige Population von Tabelle3 und fahren Sie mit dem nächsten Schritt fort.

Schritt 3: Fügen Sie einige VBA hinzu, um die dritte Tabelle

Die vollständige Automatisierung eines Prozesses, der durch Änderungen an den Daten eines Arbeitsblatts ausgelöst wird, wird am besten durch die Worksheet_Change Ereignismakro. Da es drei Tabellen gibt, die jeweils auf einem eigenen Arbeitsblatt liegen, ist das Workbook_SheetChange-Ereignismakro eine bessere Methode zur Handhabung der Änderungsereignisse aus mehreren Arbeitsblättern.

Öffnen Sie die VBE mit Alt + F11 . Sobald Sie es geöffnet haben, suchen Sie oben links nach dem Projekt Explorer . Wenn es nicht sichtbar ist, tippen Sie auf Strg + R , um es zu öffnen. Suchen Sie ThisWorkbook und klicken Sie mit der rechten Maustaste, und wählen Sie dann Code anzeigen (oder doppelklicken Sie einfach auf ThisWorkbook).

Fügen Sie Folgendes in das neue Fenster mit dem Titel Buch1 - ThisWorkbook (Code) ein.

Diese beiden Routinen verwenden die .CodeName -Eigenschaft ausgiebig. Der CodeName eines Arbeitsblatts ist Tabelle1, Tabelle2, Tabelle3 usw. und ändert sich nicht, wenn ein Arbeitsblatt umbenannt wird. Tatsächlich werden sie selbst von fortgeschrittenen Benutzern selten geändert. Sie wurden verwendet, damit Sie Ihre Arbeitsblätter umbenennen können, ohne den Code ändern zu müssen. Sie sollten jetzt jedoch auf die richtigen Arbeitsblätter zeigen. Ändern Sie den Code, wenn Ihre Tabellen und Arbeitsblätter nicht mit den angegebenen übereinstimmen. Sie können die einzelnen Arbeitsblatt-Codenamen in Klammern neben ihrem Arbeitsblatt .Name-Eigenschaft sehen im obigen Bild, das den VBE-Projekt Explorer zeigt.

Tippen Sie auf Alt + Q , um zu Ihren Arbeitsblättern zurückzukehren. Sie müssen nur noch Tabelle3 ausfüllen, indem Sie eine beliebige Zelle in Tabelle1 oder Tabelle2 auswählen und so vorgehen, dass Sie sie ändern, indem Sie auf F2 tippen dann Geben Sie ein. Ihre Ergebnisse sollten den folgenden ähneln.

Wenn Sie den ganzen Weg bis hierhin verfolgt haben, sollten Sie eine ziemlich umfassende Sammlungstabelle haben, die aktiv die Daten von zwei Quell- "Kind" -Tabellen kombiniert. Wenn Sie die VBA ebenfalls hinzugefügt haben, ist die Wartung der dritten Sammlungstabelle praktisch nicht existent.

Umbenennen der Tabellen

Wenn Sie eine oder alle der drei Tabellen umbenennen, spiegeln die Arbeitsblattformeln die Änderungen sofort und automatisch wider. Wenn Sie sich entschieden haben, die Workbook_SheetChange- und die begleitende Hilfssubprozedur einzuschließen, müssen Sie zurück in das ThisWorkbook-Codeblatt gehen und Find & amp; Ersetzen, um die entsprechenden Änderungen vorzunehmen.

Beispielarbeitsbuch

Ich habe die voll funktionsfähige Beispielarbeitsmappe über meine öffentliche DropBox verfügbar gemacht.

Table_Collection_w_Sheetname.xlsb

¹ _{Die CELL-Funktion kann nur den Arbeitsblattnamen einer gespeicherten Arbeitsmappe abrufen. Wenn eine Arbeitsmappe nicht gespeichert wurde, hat sie keinen Dateinamen und die CELL-Funktion gibt eine leere Zeichenfolge zurück, wenn nach dem Dateinamen gefragt wird.}

Eine leichte Änderung an Jeepeds Code.

Wenn Sie einen ähnlichen Ansatz verwenden, aber mit mehreren Tabellen (z. B. mehr als 10), dann wird es ziemlich mühsam sein, manuell jeden Namen jeder Tabelle hinzuzufügen. Dies ist auch ein Problem, wenn Sie die Namen der Tabellen ändern, da die Namen in VBA festverdrahtet sind. Berücksichtigen Sie Folgendes, um zusätzliche Arbeit zu vermeiden:

Nehmen wir also Folgendes an:

• Auf jedem Arbeitsblatt gibt es eine oder mehrere Tabellen, aber sie haben eine ähnliche Struktur.
• Es gibt nur Tabellen in Arbeitsblättern - keine anderen Mitglieder der ListObjects-Sammlung sind vorhanden.
• Jedes Mal, wenn wir eine Tabelle auf einem Blatt bearbeiten, löst dies eine Aktualisierung in der Haupttabelle aus (Tabelle 3).

Dann könnte der Workbook_SheetChange Sub im obigen Beispiel wie folgt aussehen:

Bearbeiten. Die zweite Routine sieht dann so aus:

Diese Routine unterscheidet sich von der vorherigen, indem vorprogrammierte Fälle eliminiert werden. Wenn eine Änderung im aktiven Arbeitsblatt registriert ist, löst jede Tabelle in diesem Arbeitsblatt, die gerade geändert werden soll, die Prozedur update_Table aus.