Automatische Tabellenerzeugung mit VBA

Hallo Zusammen,
ich stehe vor einem Problem das in Excel meiner Einschätzung nach nur durch VBA gelöst werden kann. Leider bin setze ich mich nur sporadisch mit VBA auseinander und dieses Mal scheint meine Google-Recherche nicht weiter zu helfen. Zusätzlich ist die gestellte Aufgabe auch noch sehr komplex und soll natürlich per Knopfdruck funktionieren.
Ich versuche mal die gewünschten Funktionen der Excel an einem Beispiel darzustellen.
Ausgangssituation:
- 3 Projekte
- Jedes Projekt weist eine andere Projektlaufzeit auf
- Jedes Projekt durchläuft 3 Phasen (Vorentwicklung, Serie, MAKE/BUY) wobei die letzte Phase sich entweder in MAKE oder BUY pro Projekt aufteilt
- Jedes Projekt weist unabhängig von den Phasen einen unterschiedlichen Geschäftsfall
(Groß-, Mittel-, Kleinprojekt) auf.
- In Jedem Projekt arbeiten Personen aus 2 Teams (Team1, Team 2).
Die Personenanzahl die in den Projekten arbeiten ist abhängig von der Phase und dem vorliegenden Geschäftsfall
Funktionen:
- Pivot-Diagramm Anzahl der Personen nach Projekten
- Pivot-Diagramm Anzahl der Personen nach Phasen
- Pivot-Diagramm Anzahl der Personen nach Geschäftsfällen
- Projektterminplan (Thinkcell)
- Automatische Generierung der Tabelle Datenbank im Reiter Datenbank
- Aktualisierung der Pivot-Tabellen/Diagramme per Knopfdruck
- Einfache Erweiterung von neuen Projekten
- Eingabe der Parameter durch eine VBA-Maske
Mein Problem:
Aktuell erstelle ich die Datenbank noch Manuell per Hand Da es sich anders wie dem Beispiel nicht nur um 3 Projekte und 2 Teams, sondern um 27 Projekte und 5 Teams handelt könnt ihr euch vorstellen was das für eine Arbeit ist und wie Fehleranfällig das Ganze ist.
Meine Frage:
Kann ich per VBA automatische eine Tabelle mit entsprechenden Spalten und vor allem Zeilen generieren? Oder kommt Excel in diesem Fall an seine Grenzen?
https://www.herber.de/bbs/user/132845.xlsx
Danke und Gruß
Tobi

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hi Tobi
... aber leider habe ich nicht berücksichtigt (zu spät gesehen), dass Beginn und Ende der einzelnen Projektphasen zu berücksichtigen ist. Ich habe Beginn/Ende aus der 3. Tabelle (Blatt Parameter) bezogen und jetzt fehlt mir die Zeit, alles noch einmal zu ändern.
Die Lösung ist jedoch sowieso ziemlich komplex und erfordert eine Einarbeitung deinerseits in das Thema Power Query (PQ). Insofern würde ich dir die Lösung erst einmal so überlassen und wenn du diese nachvollziehen kannst und wenn der Lösungsweg passt, können wir den Feinschliff immer noch vornehmen.
https://www.herber.de/bbs/user/132849.xlsx
Ich interpretiere die Aufgabe so, dass alle Inputdaten auf dem Blatt "Parameter" sind. Datenbank!B:G entspricht dem Soll-Ergebnis, worauf alle weiteren Auswertungen aufgebaut sind.
Auf dem Blatt Parameter die Hilfstabelle ist manuell zu erstellen.
Die nachfolgende Aufzeichnung ist ziemlich umfangreich, allerdings ist vieles auf die Transformation von Datum in eine Zahl z.B. 20182 (zweite Hälfte 18) zurückzuführen. Die Zahl dient dazu eine grösser/kleiner Bedingung einzufügen, was mit Textwerten nicht möglich ist. PQ bietet standardmässig die Transformation in Quartale an, nicht aber in Halbjahre, weshalb das Ergebnis mit diversen benutzerdefinierten Spalten und Verkettungen erzielt wurde.
cu
Chris
Aufzeichnung (Hauptabfrage):
let
Quelle = Excel.CurrentWorkbook(){[Name="Parameter_Geschäftsfall"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Projekt", type text}, {"Geschäftsfall", type text}, {"Von", type date}, {"Bis", type date}}),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each 1),
#"Zusammenführte Abfragen" = Table.NestedJoin(#"Hinzugefügte benutzerdefinierte Spalte",{"Benutzerdefiniert"},Hilfstabelle,{"Benutzerdefiniert"},"Tabelle7",JoinKind.LeftOuter),
#"Erweiterte Tabelle7" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Tabelle7", {"Hilfstabelle"}, {"Hilfstabelle"}),
#"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Tabelle7",{"Benutzerdefiniert"}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten",{{"Hilfstabelle", "Halbjahr"}}),
#"Duplizierte Spalte" = Table.DuplicateColumn(#"Umbenannte Spalten", "Von", "Von - Kopie"),
#"Duplizierte Spalte1" = Table.DuplicateColumn(#"Duplizierte Spalte", "Von", "Von - Kopie.1"),
#"Duplizierte Spalte2" = Table.DuplicateColumn(#"Duplizierte Spalte1", "Bis", "Bis - Kopie"),
#"Duplizierte Spalte3" = Table.DuplicateColumn(#"Duplizierte Spalte2", "Bis", "Bis - Kopie.1"),
#"Umbenannte Spalten1" = Table.RenameColumns(#"Duplizierte Spalte3",{{"Von - Kopie", "VonMonat"}, {"Von - Kopie.1", "VonJahr"}, {"Bis - Kopie", "BisMonat"}, {"Bis - Kopie.1", "BisJahr"}}),
#"Extrahierter Monat" = Table.TransformColumns(#"Umbenannte Spalten1",{{"VonMonat", Date.Month, Int64.Type}}),
#"Extrahiertes Jahr" = Table.TransformColumns(#"Extrahierter Monat",{{"VonJahr", Date.Year, Int64.Type}}),
#"Extrahierter Monat1" = Table.TransformColumns(#"Extrahiertes Jahr",{{"BisMonat", Date.Month, Int64.Type}}),
#"Extrahiertes Jahr1" = Table.TransformColumns(#"Extrahierter Monat1",{{"BisJahr", Date.Year, Int64.Type}}),
#"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Extrahiertes Jahr1", "HalbjahrJahr", each Text.Range([Halbjahr],0,4)),
#"Hinzugefügte benutzerdefinierte Spalte5" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "HalbjahrHälfte", each Text.Range([Halbjahr],5,1)),
#"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte5", "VonHälfte", each if [VonMonat] < 7 then 1 else 2),
#"Hinzugefügte benutzerdefinierte Spalte4" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte3", "BisHälfte", each if [BisMonat] <= 7 then 1 else 2),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte4",{{"VonJahr", type text}, {"VonHälfte", type text}, {"BisJahr", type text}, {"BisHälfte", type text}, {"HalbjahrHälfte", type text}, {"HalbjahrJahr", type text}}),
#"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ1", "VonZahl", each [VonJahr] & [VonHälfte]),
#"Hinzugefügte benutzerdefinierte Spalte6" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte2", "BisZahl", each [BisJahr] & [BisHälfte]),
#"Hinzugefügte benutzerdefinierte Spalte7" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte6", "HalbjahrZahl", each [HalbjahrJahr] & [HalbjahrHälfte]),
#"Entfernte Spalten1" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte7",{"VonMonat", "VonJahr", "BisMonat", "BisJahr", "HalbjahrJahr", "HalbjahrHälfte", "VonHälfte", "BisHälfte"}),
#"Geänderter Typ2" = Table.TransformColumnTypes(#"Entfernte Spalten1",{{"VonZahl", Int64.Type}, {"BisZahl", Int64.Type}, {"HalbjahrZahl", Int64.Type}}),
#"Hinzugefügte bedingte Spalte" = Table.AddColumn(#"Geänderter Typ2", "FilterKriterium", each if [HalbjahrZahl] >= [VonZahl] and [HalbjahrZahl] <= [BisZahl] then 1 else 0),
#"Gefilterte Zeilen" = Table.SelectRows(#"Hinzugefügte bedingte Spalte", each ([FilterKriterium] = 1)),
#"Entfernte Spalten2" = Table.RemoveColumns(#"Gefilterte Zeilen",{"VonZahl", "BisZahl", "HalbjahrZahl", "FilterKriterium"}),
#"Hinzugefügte benutzerdefinierte Spalte8" = Table.AddColumn(#"Entfernte Spalten2", "Benutzerdefiniert", each 1),
#"Zusammenführte Abfragen1" = Table.NestedJoin(#"Hinzugefügte benutzerdefinierte Spalte8",{"Benutzerdefiniert"},Parameter_Team,{"Benutzerdefiniert"},"Parameter_Team",JoinKind.LeftOuter),
#"Erweiterte Parameter_Team" = Table.ExpandTableColumn(#"Zusammenführte Abfragen1", "Parameter_Team", {"Team"}, {"Parameter_Team.Team"}),
#"Neu angeordnete Spalten" = Table.ReorderColumns(#"Erweiterte Parameter_Team",{"Projekt", "Parameter_Team.Team", "Geschäftsfall", "Von", "Bis", "Halbjahr", "Benutzerdefiniert"}),
#"Umbenannte Spalten2" = Table.RenameColumns(#"Neu angeordnete Spalten",{{"Parameter_Team.Team", "Team"}}),
#"Entfernte Spalten3" = Table.RemoveColumns(#"Umbenannte Spalten2",{"Benutzerdefiniert", "Von", "Bis"}),
#"Sortierte Zeilen" = Table.Sort(#"Entfernte Spalten3",{{"Geschäftsfall", Order.Ascending}, {"Team", Order.Ascending}, {"Projekt", Order.Ascending}}),
#"Zusammenführte Abfragen3" = Table.NestedJoin(#"Sortierte Zeilen",{"Team"},#"Parameter_Team (2)",{"Team"},"Parameter_Team (2)",JoinKind.LeftOuter),
#"Erweiterte Parameter_Team (2)" = Table.ExpandTableColumn(#"Zusammenführte Abfragen3", "Parameter_Team (2)", {"Attribut"}, {"Parameter_Team (2).Attribut"}),
#"Umbenannte Spalten3" = Table.RenameColumns(#"Erweiterte Parameter_Team (2)",{{"Parameter_Team (2).Attribut", "Phase"}}),
#"Neu angeordnete Spalten1" = Table.ReorderColumns(#"Umbenannte Spalten3",{"Projekt", "Team", "Geschäftsfall", "Phase", "Halbjahr"}),
#"Zusammenführte Abfragen2" = Table.NestedJoin(#"Neu angeordnete Spalten1",{"Team", "Geschäftsfall", "Phase"},#"Parameter_Team (2)",{"Team", "Geschäftsfall", "Attribut"},"Parameter_Team (2)",JoinKind.LeftOuter),
#"Aggregierte Parameter_Team (2)" = Table.AggregateTableColumn(#"Zusammenführte Abfragen2", "Parameter_Team (2)", {{"Wert", List.Sum, "Summe von Parameter_Team (2).Wert"}}),
#"Umbenannte Spalten4" = Table.RenameColumns(#"Aggregierte Parameter_Team (2)",{{"Summe von Parameter_Team (2).Wert", "Personen"}}),
#"Gruppierte Zeilen" = Table.Group(#"Umbenannte Spalten4", {"Projekt", "Team", "Geschäftsfall", "Phase", "Halbjahr", "Personen"}, {{"Anzahl", each Table.RowCount(_), type number}}),
#"Entfernte Spalten4" = Table.RemoveColumns(#"Gruppierte Zeilen",{"Anzahl"}),
#"Sortierte Zeilen1" = Table.Sort(#"Entfernte Spalten4",{{"Projekt", Order.Ascending}, {"Team", Order.Ascending}, {"Geschäftsfall", Order.Ascending}, {"Phase", Order.Ascending}, {"Halbjahr", Order.Ascending}})
in
#"Sortierte Zeilen1"

Beliebteste Forumthreads (12 Monate)

Automatische Tabellenerzeugung mit VBA

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread