Tabellen zusammenführen

[Dude81]

Hallo Zusammen,

ich habe folgendes Problem:

Ich habe
Tabelle A mit mehreren Spalten und unbekannter Zeilenzahl
Tabelle B mit einer Spalte und unbekannter Zeilenzahl 
Tabelle C mit einer Spalte und unbekannter Zeilenzahl

Ich möchte sie in folgendes Format bringen:

Tabelle A Zeile 1
                        Tabelle B Zeile 1
                                                  Tabelle C komplett
                        Tabelle B Zeile 2
                                                   Tabelle C komplett
                         Tabelle B Zeile ...
                                                   Tabelle C komplett
Tabelle A Zeile 2
                        Tabelle B Zeile 1
                                                  Tabelle C komplett
                        Tabelle B Zeile 2
                                                   Tabelle C komplett
                         Tabelle B Zeile ...
                                                   Tabelle C komplett
Tabelle A Zeile ...
usw

Bei allen Tabellen kann sich die Zeilenzahl ändern.
Die neue Tabelle soll sich entsprechend mit den Tabellen A,B und C ebenfalls in der Größe ändern.

Krieg ich das noch mit PQ hin oder sollte man da VBA verwenden oder gibt es da einen anderen einfachen Weg?

Gruß und Danke im Voraus.

[Gast 123]

Hallo

höfliche Frage:  Wozu soll das ganze gut sein?  Tabellen B+C ständig alle Zeilen wiederholen??
Da erschliesst sich mir keinen wirklichen Sinn bei dieser Aufgabe.

mfg Gast 123

[Dude81]

Tabelle A, B, C Spannen zusammen mit einem dynamischen Kalender eine neue Tabelle auf in dem die eigentliche Arbeit statt finden soll. Durch die Tabellen A, B, C wird im Prinzip die Zeilenbenennung generiert. Dier Kalender ist die Spaltenbeschriftung. Ich habe bei PQ keine Möglichkeit gefunden eine Tabelle in einzelnen Zeilen zu zerlegen. (vielleicht hab ich auch Tomaten auf den Augen)

Danke im Voraus.

[ws-53]

Ich habe bei PQ keine Möglichkeit gefunden ...

Und wie soll dir geholfen werden, wenn du keine Musterdatei zur Verfügung stellst, aus der auch hervorgeht, wie das Endergebnis aussehen soll?

[derHoepp]

Moin,

ich habe das mal frei so interpretiert, dass Tabelle1 Wochentage enthält, Tabelle2 Bezeichnungen von Teams und Tabelle3 Bezeichnungen von Schichten.
Deine Tabelle1 habe ich "Tage" genannt. Sie hat den folgenden Aufbau:

Code:

SortID|Tag     |Joiner
1     |Montag  |1
2     |Dienstag|1
...

Tabelle2 heißt "Teams":

Code:

SortID|Team|Joiner
1     |EK  |1
2     |VK  |1
...

Tabelle3 dann "Schicht":

Code:

SortID|Schicht|Joiner
1     |Früh   |1
2     |Spät   |1
...

Das ganze lässt sich mit folgender Powerquery-Abfrage in dein gewünschtes Format bringen:

Code:

let
    Quelle= Excel.CurrentWorkbook(){[Name="Tage"]}[Content],
    Teams= Excel.CurrentWorkbook(){[Name="Teams"]}[Content],
    Schicht= Excel.CurrentWorkbook(){[Name="Schicht"]}[Content],
    #"Zusammenführte Abfragen" = Table.NestedJoin(Quelle,{"Joiner"},Teams,{"Joiner"},"Teams",JoinKind.Inner),
    #"Erweiterte Teams" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Teams", {"SortID", "Team", "Joiner"}, {"Teams.SortID", "Teams.Team", "Teams.Joiner"}),
    #"Zusammenführte Abfragen1" = Table.NestedJoin(#"Erweiterte Teams",{"Teams.Joiner"},Schicht,{"Joiner"},"Schicht",JoinKind.Inner),
    #"Erweiterte Schicht" = Table.ExpandTableColumn(#"Zusammenführte Abfragen1", "Schicht", {"SortID", "Schicht", "Joiner"}, {"Schicht.SortID", "Schicht.Schicht", "Schicht.Joiner"}),
    #"Sortierte Zeilen" = Table.Sort(#"Erweiterte Schicht",{{"SortID", Order.Ascending}, {"Teams.SortID", Order.Ascending},{"Schicht.SortID", Order.Ascending}}),
    #"Andere entfernte Spalten" = Table.SelectColumns(#"Sortierte Zeilen",{"Tag", "Teams.Team", "Schicht.Schicht"})
in
    #"Andere entfernte Spalten"

Der Trick ist einfach, allen Daten den gleichen "Join"-Indikator zu geben, damit alle Inhalte aus allen Tabellen zusammengemischt werden.
Alternativ lassen sich die drei Tabellen auch in das Powerpivot-Datenmodell laden (markierte Zelle in einer Tabelle -> Ribbon PowerPivot -> Bereich "Tabellen" -> Zu Datenmodell hinzufügen). Anschließend kannst du eine Pivottabelle erstellen, die sich aus dem Datenmodell bedient. Dort fügst du nur die Bezeichnungsfelder aller drei Tabellen in die "Zeilen" und bist schon fertig. Dabei musst du allerdings entweder händisch sortieren, oder die SortID noch mit in die Pivottabelle aufnehmen.

Viele Grüße
derHöpp

[derHoepp]

Moin,

da mein Lösungsvorschlag in der Erstellung doch Recht aufwändig war (Beispieldatei basteln und zwei Lösungen entwickelt), wäre es sehr schade, wenn das für die Tonne war. 

Wie sieht's aus, Dude. Kommt noch ne Rückmeldung?

Viele Grüße 
derHöpp

[Dude81]

Hallo Zusammen,

erst mal Danke für die Vorschläge.

Ein Kollege von mir hat mir diesen Weg gezeigt. (-> Anhang: Example)
Der tut jetzt noch nicht genau was ich will, aber erst mal so weit so gut.

DAs "Orginal" file kann ich leider nicht hochladen da zu viele Interna drinstehen.

@ derHöpp: Ich bin mit dem ersten Versuch Dein PQ Code in das Orginal zu schreiben gescheitert. Ich bin aber noch dran. Also nix für die Tonne sondern vielen Dank.

Ich werd da nochmal rumprobieren.

Falls ich da doch noch Erfolg hab werd ich mich melden.

Gruß,
Dude

[juvee]

Hi,

teste mal folgendes Makro.

Füge in deine Datei ein Tabellenblatt "Ergebnis" ein. 
die Namen deiner IntelligentenTabellen jeweils anpassen

Code:

Option Explicit

Sub main()


'read the data

Dim arrsheetC As Variant
Dim arrsheetB As Variant
Dim arrsheetA As Variant

arrsheetA = Tabelle1.ListObjects("Tabelle2").DataBodyRange.Value2
arrsheetB = Tabelle2.ListObjects("Tabelle3").DataBodyRange.Value2
arrsheetC = Tabelle3.ListObjects("Tabelle4").DataBodyRange.Value2

'manage the data

Dim arrOut() As Variant
Dim zeilen As Long, spalten As Long

zeilen = UBound(arrsheetA, 1) * UBound(arrsheetB, 1) * UBound(arrsheetC, 1)
spalten = UBound(arrsheetA, 2) + UBound(arrsheetB, 2) + UBound(arrsheetC, 2)

ReDim arrOut(1 To zeilen, 1 To spalten)

If combineData(arrOut, arrsheetA, arrsheetB, arrsheetC) Then

'write the data
    With Worksheets("Ergebnis")
    .Range("A1").CurrentRegion.ClearContents
    .Range("A1").Resize(UBound(arrOut, 1), UBound(arrOut, 2)) = arrOut
    End With

Else
    MsgBox "Fehler, nicht alle Daten übertragen"
End If
End Sub



Function combineData(arrOut As Variant, arrA As Variant, arrB As Variant, arrC As Variant) As Long

Dim i As Long, j As Long, k As Long, s As Long, z As Long
z = 0
For i = LBound(arrA, 1) To UBound(arrA, 1)
    For j = LBound(arrB, 1) To UBound(arrB, 1)
        For k = LBound(arrC, 1) To UBound(arrC, 1)
            z = z + 1
            For s = LBound(arrA, 2) To UBound(arrA, 2)
              arrOut(z, s) = arrA(i, s)
            Next
              arrOut(z, s) = arrB(j, 1)
              arrOut(z, s + 1) = arrC(k, 1)
        Next
    Next
Next

  combineData = z = UBound(arrOut, 1)
End Function

VG Juvee

[Dude81]

Hallo JuVee, Hallo Zusammen,

ich hab gestern Abend auch ein bisschen Code generiert aber ich seh schon Du machst das geschickter. Du bastelst Dir am Ende ein ausgabe-Array zusammen und hüpfst nicht von Zelle zu Zelle. Mit Ubound bracht man weniger Variablen für die For-Schleifen, und mit Databodyrange bekommt man eine Tabelle ins Array ohne Spaltenbezeichnung. Ich werd Dein Code mal ausprobieren.

Code:

Public Sub Testtab()
Dim TabA() As Variant
Dim TabB() As Variant
Dim TabC() As Variant
Dim TA As Integer, TB As Integer, TC As Integer


Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim Z As Integer, S As Integer

TA = Tabelle1.ListObjects("Tabelle2").Range.Rows.Count
TB = Tabelle2.ListObjects("Tabelle3").Range.Rows.Count
TC = Tabelle3.ListObjects("Tabelle4").Range.Rows.Count

ReDim TabA(TA, 6)
ReDim TabB(TB)
ReDim TabC(TC)
TabA = Tabelle1.ListObjects("Tabelle2").Range.Value
TabB = Tabelle2.ListObjects("Tabelle3").Range.Value
TabC = Tabelle3.ListObjects("Tabelle4").Range.Value

Z = 1
S = 1
With Tabelle5
For i = 2 To TA
    For j = 1 To 6
        .Cells(Z, S) = TabA(i, j)
        S = S + 1
    Next
        For k = 2 To TB
         Z = Z + 1
        .Cells(Z, S) = TabB(k, 1)
            S = S + 1
            For l = 2 To TC
            Z = Z + 1
            .Cells(Z, S) = TabC(l, 1)
           
            Next
        S = S - 1
        Next
        Z = Z + 1
       S = S - 6
   Next


End With

End Sub

[EA1950]

Hallo,

ich habe den Vorschlag von @derHoepp aufgegriffen und die angehängte Excel-Datei damit erstellt.

Im Arbeitsblatt "Vorgabe" befinden sich die 3 Tabellen (Tage, Teams, Schichten). Die Zeilen dieser Tabellen können nach Belieben noch ergänzt werden.

Im Arbeitsblatt "Resultat" befindet sich die resultierende Tabelle. Führt man dort einen Rechtsklick aus und wählt den Befehl "Aktualisieren" aus, aktualisiert sich diese Tabelle - aufgrund der gespeicherten PowerQuery-Abfrage und der Vorgabetabellen.

Funktioniert super!