BWA erstellen - Zusammenfassung einzelner Tabellenblätter

[andreasfuckert]

[
Bild bitte so als Datei hochladen: Klick mich!
]
Noch ein Hinweis: 
Die Kostenstellenbezeichnungen der einzelnen Tabellenblätter, die hinterher zusammenaddiert werden sollen: Zentrale beginnt mit 1XXX, München beginnt mir 2XXX, Frankfurt mit 3XXX. 
Es sollen dann jeweils die einzelnen Standorte zusammen gefasst werden und diese dann wiederum in einer Gesamt - Zusammenfassung.
Danke 
Andreas  



Hallo zusammen,
ich bin neu hier und gerade verzweifelt. Eigentlich sollte das Ganze machbar sein, aber ich stehe gerade auf der Leitung.
Ich habe verschiedene Tabellenblätter mit Auswertungen zu Kostenstellen. Hier bspw. 1100, 1110 etc. Diese Tabellen enthalten als Spaltenüberschrift die Zeilennummer und die Monate  01-12 /2022.
In den Zeilen aus der BWA nach Datev stehen die Werte.  Es sind aber nicht in jeder Kostenstelle alle Zeilen mit Werten versehen. Kostenstelle 1100 muss zum Beispiel keine Personalkosten haben, die KOstenstellen 1110 aber schon ... In der Zusammenfassung muss ich die einzelnen Kostenstellen zusammenführen, um hier einen Überblick zu haben. Mit SummeWenns habe ich es versucht, passt aber nicht. Mit der Funktion "konsololidieren" habe ich es versucht, sieht aber nicht schön aus. 
Monatlich kopiere ich aus Datev die neuen WErte ich die einzelnen Tabellenblätter. Das heißt, es könnte sich die Position der einzelnen Zeilen ändern. Damit meine ich, dass eine Zeilen hinzu kommen können. evtl. hatten diese in den Monaten Januar bis April keine Werte, ab Mai dann aber schon. Und erschwerend kommt noch hinzu, dass auch neue Tabellenblätter hinzugefügt werden könnten, wenn es eine neue Kostenstelle gibt. Kann mir jemand helfen? Welche Funktion wäre hier am sinnvollsten? 
Vielen Dank
Andreas

[Elex]

Moin

So wird das wieder ein drei Seiten Thema. Mach mal eine Datei in der auch die Wunsch Zusammenfassung zu sehen ist.

Gruß Elex

[Der Steuerfuzzi]

Hallo,

ich könnte mir vorstellen, dass das mit Power Query ganz gut machbar sein sollte.

Schau mal, ob Dir das weiter hilft:
http://www.excel-ist-sexy.de/blaetter-ei...nfassen-1/
https://www.xelplus.com/combine-excel-sh...wer-query/

[PIVPQ]

Hallo

Ja das gut mit PQ umsetzbar, aber nur durch sehr sehr viel Fleißarbeit.
Und zum anderen fehlen wichtige Infos welche Posten gehören zu den einzelnen Kostenstellen, ich suche sie mir nicht zusammen.
Und die ganzen Register müssen den 100% gleichen Aufbau in den Überschriften haben, was zur Zeit nicht der Fall ist.

[Elex]

Moin

Ich würde es per VBA machen.

Code:

Public Sub Konsolidieren()
Dim arrKonBe, j As Long, jj As Long, KPfad As String, Blatt As String
Dim strA As String, strB As String

KPfad = "'" & ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]"

For j = 1 To 5
Blatt = Choose(j, "Zentrale", "München", "Hamburg", "Frankfurt", "Gesamt") 'Reihenfolge Zentrale = alle Blätter mit 1xxx / München 2xxx / Frankfurt 4xxx / Gesamt zum Schluß
With Sheets(Blatt)
   If j < 5 Then
    strA = ""
    For jj = 1 To Sheets.Count
       If Left(Sheets(jj).Name, 1) = CStr(j) Then strA = strA & "*" & KPfad & Sheets(jj).Name & "'!" & Sheets(jj).Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
    Next jj
    arrKonBe = Split(Mid(strA, 2), "*")
    .Cells().Clear   'Alte Daten löschen
    .Range("A1").Consolidate Sources:=arrKonBe, Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
    .Range("D1:O1").NumberFormat = "[$-407]mmm/ yy;@"
    .Range("A1").CurrentRegion.Columns(2).Offset(1).FormulaLocal = "=WENN(C2="""";""Bezeichnung"";Zeile())"
    .Range("A1").CurrentRegion.RemoveDuplicates Columns:=2, Header:=xlNo
    .Range("A1").CurrentRegion.Columns(2).Offset(1).FormulaLocal = "=WENNFEHLER(SVERWEIS(A2;Kostenarten!$A$1:$B$25;2;0);"""")"
    .Range("A1").CurrentRegion.EntireColumn.AutoFit
    .Range("A1:O1").Font.Bold = True
    strB = strB & "*" & KPfad & .Name & "'!" & .Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
   Else
    arrKonBe = Split(Mid(strB, 2), "*")
    .Cells().Clear
    .Range("A1").Consolidate Sources:=arrKonBe, Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
    .Range("A1").CurrentRegion.Columns(2).Offset(1).FormulaLocal = "=WENNFEHLER(SVERWEIS(A2;Kostenarten!$A$1:$B$25;2;0);"""")"
    .Range("A1").CurrentRegion.EntireColumn.AutoFit
    .Range("A1:O1").Font.Bold = True
   End If
End With
Next j
End Sub

Gruß Elex

[andreasfuckert]

Vielen vielen Dank !!!!

[Elex]

Hallo

Und die ganzen Register müssen den 100% gleichen Aufbau in den Überschriften haben, was zur Zeit nicht der Fall ist.

War mir auch unsicher ob das mit PQ was wird, deshalb bin ich erst mal bei VBA geblieben.

Aber auf die Weise scheint es möglich zu sein. Beispiel für die Zusammenfassung "Zentrale".

Ertse Abfrage: Name -> DatenBasis als nur Verbindung

Code:

let
    Quelle = Excel.Workbook(File.Contents("C:\Users\Nutzer\Desktop\Konsolidieren.xlsm"), null, true),
    #"Gefilterte Zeilen" = Table.SelectRows(Quelle, each Text.StartsWith([Name], "1")),
    #"Erweiterte Data" = Table.ExpandTableColumn(#"Gefilterte Zeilen", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Erweiterte Data", each ([Column3] <> null and [Column3] <> " ")),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Gefilterte Zeilen1",{"Item", "Kind", "Hidden"}),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Entfernte Spalten", {"Name", "Column1", "Column2"}, "Attribut", "Wert")
in
    #"Entpivotierte andere Spalten"

Zweite Abfrage: Name -> SpaltenAbgleich als nur Verbindung

Code:

let
    Quelle = DatenBasis,
    #"Gefilterte Zeilen" = Table.SelectRows(Quelle, each ([Column2] = "Bezeichnung"))
in
    #"Gefilterte Zeilen"

Dritte Abfrage: Name -> Ergebnis 

Code:

let
    Quelle = Table.NestedJoin(DatenBasis,{"Name", "Attribut"},SpaltenAbgleich,{"Name", "Attribut"},"SpaltenAbgleich",JoinKind.LeftOuter),
    #"Erweiterte SpaltenAbgleich" = Table.ExpandTableColumn(Quelle, "SpaltenAbgleich", {"Wert"}, {"Wert.1"}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte SpaltenAbgleich",{"Attribut", "Name"}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Entfernte Spalten", each ([Wert] <> "Gesamt" and [Wert] <> " ") and ([Column2] <> "Bezeichnung")),
    #"Pivotierte Spalte" = Table.Pivot(Table.TransformColumnTypes(#"Gefilterte Zeilen", {{"Wert.1", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Gefilterte Zeilen", {{"Wert.1", type text}}, "de-DE")[Wert.1]), "Wert.1", "Wert", List.Sum)
in
    #"Pivotierte Spalte"

Gruß Elex