Kombinieren Sie 2 Excel-Tabellen in die man die Daten anfügen?

Diese Antwort beschäftigt sich mit Strukturierte Tabellen wie interpretiert von Excel. Während die Methoden könnten leicht abgelesen werden, zu Rohdaten-Matrizen zugewiesen, ohne die Struktur einer Tabelle, die Formeln und VBA-Codierung für diese Lösung wird anvisiert werden wahr strukturierten Tabellen.

Präambel

Einer Dritten Tabelle können die kombinierten Daten aus zwei Tabellen mit einigen native-Arbeitsblatt Formeln, aber halten die Dritte Tabelle richtig bemessen, wenn Zeilen Hinzugefügt oder gelöscht werden zu/aus den abhängigen Tabellen erfordern entweder eine manuelle Größenänderung oder VBA, die verfolgt diese änderungen und entspricht der Dritten Tabelle zu entsprechen. Ich habe enthalten Optionen zum hinzufügen die Quell-Tabelle Arbeitsblatt name sowie eine Tabelle zur Wartung von VBA-code am Ende dieser Antwort.

Wenn alle Sie wollen, ist eine operative Beispiel-Arbeitsmappe, ohne alle Erklärung, überspringen, um das Ende der Antwort einen link zu der Arbeitsmappe verwendet, um erstellen Sie diese Prozedur.

Sample-Daten-Tabellen

Habe ich die OP-sample-Daten zu konstruieren, die zwei Tabellen mit dem Namen (standardmäßig) Tabelle1 und Tabelle2 auf Arbeitsblätter, Tabelle1 und Tabelle2 jeweils. Ich habe absichtlich die Aufrechnung durch unterschiedliche Grade von jedem Arbeitsblatt die Zelle A1, um zu zeigen, einer strukturierten Tabelle, die Fähigkeit zur Bewältigung entweder sich selbst oder einem anderen strukturierten Tabelle in einer Formel als ein separates Element unabhängig von seiner position auf der übergeordneten Arbeitsblatt. Die Dritte Tabelle werden in ähnlicher Weise aufgebaut. Diese offsets sind für Demo-Zwecke nur, Sie sind nicht erforderlich.

Schritt 1: Erstellen der Dritten Tabelle

Build-Header für die Dritte Tabelle, und wählen Sie, die Zukunft Kopfzeile und mindestens eine Zeile unter ihm zu base Einfügen ► Tabellen ► Table-Befehl auf.

Ihre neue leere Dritten Tabelle auf dem Arbeitsblatt Tabelle3 sollte die folgenden ähneln.

Schritt 2: Füllen Sie die Dritte Tabelle

Starten Sie durch ausfüllen der ersten Zelle in der Dritten Tabelle DataBodyRange. In diesem Beispiel, das wäre Sheet3!C6. Geben oder fügen Sie die folgende Formel in C6 im Auge zu behalten, dass es auf der Basis der Standard-Tabelle Namen. Wenn Sie Ihre Tabellen-Namen entsprechend anpassen.

=IFERROR(INDEX(Table1, ROW([@[no.]])-ROW(Table3[#Headers]),COLUMN(A:A)), INDEX(Table2, ROW([@[no.]])-ROW(Table3[#Headers])-ROWS(Table1),COLUMN(A:A)))

Den INDEX-Funktion erste ruft jeden verfügbaren Zeile aus "Tabelle1". Die konkrete Zeile, die zahlen sind abgeleitet mit der ZEILE-Funktion verweisen auf definierte Teile des strukturierten Tabelle zusammen mit ein wenig Mathematik. Wenn Tabelle1 läuft aus Zeilen, Datenbankabfrage übergeben wird, um eine zweite INDEX-Funktion verweisen auf Tabelle2 durch die WENNFEHLER-Funktion und seine sequentielle Zeilen abgerufen werden mit den ZEILEN-und REIHEN-Funktionen mit ein bisschen mehr Mathematik. Die SPALTE Funktion ist als COLUMN(A:A) die mit dem abrufen der ersten Spalte der referenzierten Tabelle, unabhängig davon, wo es auf dem Arbeitsblatt. Dies wird den Fortschritt, den zweiten, Dritten, usw. Spalte als Formel gefüllt.

Apropos füllen rechts füllen Sie die Formel rechts auf E6. Sollten Sie etwas ähnlich dem folgenden.

Schritt 2.5: [optional] Fügen Sie die Quell-Tabelle parent-Arbeitsblatt name

Greifen Tabelle3 ' s Ziehpunkt (gekennzeichnet durch den Orangen Pfeil in der Beispiel-Bild unten) in der unteren rechten Ecke und ziehen Sie es rechts eine Spalte hinzufügen, um eine neue Spalte zu der Tabelle. Benennen Sie die header-label zu etwas besser geeignet als die Vorgabe. Ich habe Blatt als eine Spalte label.

Während Sie abrufen können die Arbeitsblatt-Namen der Quell-Tabelle direkt, die ZELLE-Funktion abrufen können, die vollständig qualifizierten Pfad, Dateiname und Tabellenblatt der jede Zelle in einem gespeicherten workbook1, wie man die optionalen info_types.

Setzen Sie die folgende Formel in Tabelle3 die leere Zelle in der ersten Zeile der neuen Spalte, die Sie gerade erstellt haben.

=TRIM(RIGHT(SUBSTITUTE(CELL("filename", IF((ROW([@[no.]])-ROW(Table3[#Headers]))>ROWS(Table1), Table2, Table1)), CHAR(93), REPT(CHAR(32), 999)), 255))

Komplette Auffüllen Tabelle3

Wenn Sie nicht Vorhaben, auf die Fertigstellung dieses kleinen Projekts mit einigen VBA zu pflegen Tabelle3 Dimensionen, wenn Zeilen Hinzugefügt oder gelöscht aus den beiden Quell-Tabellen dann einfach nehmen, Table3 die Größenänderung Griff und ziehen Sie Sie nach unten, bis Sie gesammelt haben, alle Daten aus beiden Tabellen. Siehe unten auf dieser Antwort für ein Beispiel-Bild der erwarteten Ergebnisse.

Wenn Sie planen, fügen Sie einige VBA, dann überspringen Sie die vollständige Bevölkerung von Tabelle3 und gehen Sie zum nächsten Schritt.

Schritt 3: Fügen Sie einige VBA zu pflegen, die Dritte Tabelle

Vollständigen Automatisierung ist ein Prozess, der, ausgelöst durch änderungen an einem Arbeitsblatt die Daten am besten behandelt, indem das Arbeitsblatt ist Worksheet_Change Ereignis-makro. Da gibt es die drei beteiligten Tabellen, die jeweils auf Ihr eigenes Arbeitsblatt, das Workbook_SheetChange-Ereignis-makro ist eine bessere Methode der Behandlung des change-events aus mehreren Arbeitsblättern.

Öffnen der VBE mit Alt+F11. Sobald Sie es öffnen, sehen Sie für die Projekt-Explorer in der oberen linken. Wenn es nicht sichtbar ist, Tippen Sie dann auf Strg+R, um es zu öffnen. Suchen Sie ThisWorkbook und mit der rechten Maustaste wählen Sie dann Code Anzeigen (oder klicken Sie einfach doppelt auf "DieseArbeitsmappe").

Fügen Sie den folgenden in den neuen Bereich mit dem Titel so etwas wie Mappe1 - DieseArbeitsmappe (Code).

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        Case Sheet1.Name
            If Not Intersect(Target, Sheet1.ListObjects("Table1").Range.Offset(1, 0)) Is Nothing Then
                On Error GoTo bm_Safe_Exit
                Application.EnableEvents = False
                Call update_Table3
            End If
        Case Sheet2.Name
            If Not Intersect(Target, Sheet2.ListObjects("Table2").Range.Offset(1, 0)) Is Nothing Then
                On Error GoTo bm_Safe_Exit
                Application.EnableEvents = False
                Call update_Table3
            End If
    End Select

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Private Sub update_Table3()
    Dim iTBL3rws As Long, rng As Range, rngOLDBDY As Range
    iTBL3rws = Sheet1.ListObjects("Table1").DataBodyRange.Rows.Count
    iTBL3rws = iTBL3rws + Sheet2.ListObjects("Table2").DataBodyRange.Rows.Count
    iTBL3rws = iTBL3rws + Sheet3.ListObjects("Table3").DataBodyRange.Cells(1, 1).Row - _
                          Sheet3.ListObjects("Table3").Range.Cells(1, 1).Row
    With Sheet3.ListObjects("Table3")
        Set rngOLDBDY = .DataBodyRange
        .Resize .Range.Cells(1, 1).Resize(iTBL3rws, .DataBodyRange.Columns.Count)
        If rngOLDBDY.Rows.Count > .DataBodyRange.Rows.Count Then
            For Each rng In rngOLDBDY
                If Intersect(rng, .DataBodyRange) Is Nothing Then
                    rng.Clear
                End If
            Next rng
        End If
    End With
End Sub

Diese beiden Routinen, die umfangreiche Verwendung von Arbeitsblatt .CodeName Eigenschaft. Ein Arbeitsblatt ist CodeName ist Tabelle1, Tabelle2, Tabelle3, usw. und ändert sich nicht, wenn Sie ein Arbeitsblatt umbenannt. In der Tat, werden Sie selten geändert werden, auch von den fortgeschrittenen Benutzer. Sie wurden verwendet, so dass können Sie Ihre Arbeitsblätter, ohne den code ändern. Allerdings sollten Sie zeigen auf die richtigen Arbeitsblätter jetzt. Ändern Sie den code, wenn Sie Ihre Tabellen und Arbeitsblätter, die sind nicht gleich. Sehen Sie die einzelnen Arbeitsblatt-Kodenamen in Klammern neben Ihrem Arbeitsblatt .Name-Eigenschaft im Bild oben zeigt der VBE Project Explorer.

Tippen Sie auf Alt+Q, um zu Ihrer Arbeitsblätter. Alles, was übrig ist wäre die Oberfläche bevölkern Tabelle3 durch auswählen einer Zelle in "Tabelle1" oder Tabelle2 und vorgibt, es zu ändern durch Tippen auf F2, dann Enter↵. Ihre Ergebnisse sollten wie folgt Aussehen.

Wenn Sie gefolgt sind zusammen den ganzen Weg bis hier, dann sollten Sie haben eine ziemlich umfangreiche Sammlung-Tabelle, die aktiv kombiniert die Daten aus beiden Quellen 'Kind' Tabellen. Wenn Sie das VBA wie gut dann die Wartung der Dritte Sammlung-Tabelle ist praktisch nicht existent.

Umbenennen der Tabellen

Wenn Sie wählen, umbenennen oder alle drei Tabellen, die Arbeitsblatt-Formeln werden sofort und automatisch die änderungen. Wenn Sie sich entschieden haben, gehören die Workbook_SheetChange und begleitenden Helfer sub-Prozedur, die Sie haben zu gehen zurück in die ThisWorkbook-code-Blatt und verwenden, Finden & Ersetzen, um die entsprechenden änderungen vorzunehmen.

Beispiel-Arbeitsmappe

Habe ich den voll funktionsfähigen Beispiel-Arbeitsmappe zur Verfügung, die aus meinem public DropBox.

Table_Collection_w_Sheetname.xlsb

1 _{Der ZELLE-Funktion kann nur abrufen, wird der name Arbeitsblatt von einer Arbeitsmappe gespeichert. Wenn eine Arbeitsmappe nicht gespeichert wurde, dann hat er keinen mit dem Namen und der ZELLE-Funktion eine leere Zeichenfolge zurück, wenn gefragt, für die Dateinamen.}

lori_m ein wirklich guter Beitrag, den ich baute auf die mithilfe von Microsoft Excel-Tabellen und strukturierte Verweise.

Machen Sie zuerst eine Spalte in der output-Tabelle namens RowID enthält die Nummer der Zeile in der Tabelle, und verwenden Sie dann dieses, um zu füllen das Daten-Werte.

=IF( INDIRECT("Table3[RowId]")<=ROWS(Table1)
    ,INDEX(Table1[column1],INDIRECT("Table3[RowId]"))
    ,INDEX(Table2[Column1],INDIRECT("Table3[RowId]")-ROWS(Table1)))

Es ist ein detaillierte Erklärung, wie dies funktioniert auf meinem blog, wie es war zu lang um Sie hier zu zitieren.

Im mit diesem code/Formel. funktioniert gut für meine Bedürfnisse, nur was ich gerne wissen würde ist, wie mache ich eine bessere Zelle, Formel so kann ich mit 3+ Tabellen als Referenz. zurzeit im, nur Schachteln ein paar WENNFEHLER Aussagen im WENNFEHLER

HTML:

=IFERROR(INDEX(Table1, ROW([@Date])-ROW(Table3[#Headers]),COLUMN(A:A)),IFERROR( INDEX(Table2, ROW([@Date])-ROW(Table3[#Headers])-ROWS(Table1),COLUMN(A:A)), IFERROR(INDEX(Table4, ROW([@Date])-ROW(Table3[#Headers])-ROWS(Table2)-ROWS(Table1),COLUMN(A:A)),INDEX(Table5, ROW([@Date])-ROW(Table3[#Headers])-ROWS(Table2)-ROWS(Table1)-ROWS(Table4),COLUMN(A:A)))))

Im auch mit der