Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1880to1884
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Tabelle konsolidieren

Tabelle konsolidieren
05.05.2022 10:27:25
Andreas
Hallo ich habe mehrere Tabellen, die ich aktuell händisch verwertbar mache. Das scheint sehr aufwendig.
Könnt ihr mir helfen das zu automatisieren?
Aufbau der Tabelle:
Es gibt mehrere Kennzahlen in den Zeilen, über Ländern in Spalte A. Leider sind die Daten mehrfach nebeneinander angebordnet, anstatt untereinander.
Den Aufbau habe ich hier mal in paint skizziert.
Userbild

Ziel:
Die aktuell horizontale Tabelle automatisch in eine senkrechte Variante auszugeben und jede Zeile mit der Jahreszahl zu ergänzen. Also eine Art Rohdatentabelle, die man dann mit Pivottabellen bearbeiten kann.
Danke vorab
VG Andreas

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Tabelle konsolidieren
05.05.2022 10:37:11
Oberschlumpf
bei mir in Excel sind alle Mal- und Zeichnen-Funktionen deaktiviert.
AW: Tabelle konsolidieren
05.05.2022 17:39:32
Andreas
Hallo Oberschlumpf. Im Kindergarten macht es manchmal Sinn die Zeichenfunktion zu sperren, sonst nimmt es schnell überhand. Aber du verstehst das bestimmt bald.
________________
Vielen Dank an die, die sich hier aufhalten, um zu helfen!
AW: Tabelle konsolidieren
05.05.2022 22:05:38
Oberschlumpf
und du kapierst einfach nicht, dass JPG-Dateien nicht zum Testen in Excel geeignet sind!
warum nur verstehst du nicht, dass eine EXCEL-Bsp-Datei von dir die viel bessere Wahl ist?
AW: Tabelle konsolidieren
05.05.2022 13:47:17
Yal
Hallo Andreas,
es ist ein Entpivotieren. Das kann man mit Power Query leicht erreichen. Jedoch mit doppelte Spaltenüberschrift ist es ein Bischen Bastelei.
Aber wenn Du bereit bist, Dich drauf einzulassen, kann ich Dir eine Anleitung schreiben. Ideal wäre eine Datei mit dem aktuelle Aufbau. Empfliedliche Daten müssten verfremdet werden.
VG
Yal
Anzeige
AW: Tabelle konsolidieren
05.05.2022 17:30:01
Andreas
Hi Yal, danke für die Info. Die Idee mit PQ würde mich schon interessieren. Damit kenne ich mich bisher wenig aus. Vermutlich macht dir das aber ne Menge Aufwand? Fraglich ist dann auch, ob es sich auf andere Tabellen duplizieren lässt, wenn in der anderen Liste zwar das gleiche Format ist, aber die Kennzahlen an sich andere sind. Also z.B. wenn bei dem einen steht Wasser in Liter und bei der nächsten Tabelle geht es um Muttererde in m³.
VG Andreas
AW: Tabelle konsolidieren
05.05.2022 20:19:27
Yal
Hallo Andreas,
der Aufwand mit Power Query hält sich in Grenzen. Es handelt sich ein "Low Code/No Code"-Umgebung, um Daten zu transformieren. Kompliziert ist es nicht.
_ Markiere den Bereich, wo deine Tabelle steht inkl. Zeilen- und beide Spaltenüberschrift,
_ Menü "Einfügen", "Tabelle". Der Bereich wird übernommen. Hat Überschrift: Nein (wichtig). Eine aktive Tabelle wird erzeugt.
_ es fügt sich einen eigene Überschrift "Spalte1, "Spalte2". Lassen wir stehen. Die Zeile kann man anschliessend ausblenden oder weiss auf weiss übermalen.
_ im Menü "Daten", "Aus Tabelle" (achten, das die aktive Zelle irgendwo in die Tabelle steht),
_ Du bist im Power Query Editor
_ der linke Bereich "Abfragen" öffnen und die Abfrage "Tabelle1" (es übernimmt zuerst die Name der Tabelle) rechtsklicken und "Duplizieren".
_ rechtsklicken, Umbenennen in "Gesamt"
_ im Menü "Start", "Zeilen beibehalten", "erste Zeilen beibehalten" und die Anzahl an Spaltenüberschrift eingeben.
_ Menü "Start", "erste Zeile als Überschriften verwenden", "Überschirften als erste Zeile verwenden". Es füllt sich eine Zeile mit "Spalte1", "Spalte2", ...
_ im Menü "Transformieren", "Vertauschen"
_ erste Überschriftsspalte markieren (es sollte jetzt die zweite sein, die erste hat "Spalte1", ...),
_ im Menü "Transformieren", "Ausfüllen", "Nach Unten" ausfüllen (auf deine Skizze war der überschrift nicht in allen Spalten),
(nebenbei beachten, wie diese Schritt in rechten Bereich "Angewendete Schritte" sich ansammeln. Man kann jederzeit eine Schritt anklicken oder anpassen)
_ die Überschirftsspalten markieren (also alle ausser die erste), "Transformieren", "Spalten zusammenführen", Trennzeichen "Semikolon" (es sei denn, es gibt iwo bereit eine Semiko, dann was anderes)
_ "Transfomieren", "Vertauschen". Durch den Vertauschen heissen die Spaltenüberschirft "Column1", "Column2", ...
_ Menü "Start", "erste Zeile als Überschriften verwenden", "erste Zeile als Überschriften verwenden". "Spalte1", "Spalte2", ... wird wieder Überschrift.
_ auf Abfrage "Tabelle1" wechseln,
_ "Start", "Zeilen entfernen", "erste Zeilen entfernen", die gleiche Anzahl Zeilen entfernen, wie vorher behalten eingeben
_ auf Abfrage "Gesamt" wechseln,
_ Menü "Start", "Abfragen anfügen", "Abfragen anfügen", Abfrage "Tabelle1" auswählen (bei dieser Schritt ist es wichtig, dass die Spalten der beiden Abfragen zueinander passenden Namen haben)
_ Menü "Start", "erste Zeile als Überschriften verwenden", "erste Zeile als Überschriften verwenden".
Jetzt haben beinahe die originale Tabelle, nur dass der mehrzeiligen Spaltenüberschirft jetzt nur eine Zeile ist.
Hätten wir nur eine einzeilige Überschrift, würden wir hier anfangen (vorausgesetzt Semikolon in jedem Spaltenname)
_ alle "nicht Wert"-Spalten markieren (auf deine Skizze sieht es nach "Land 1", .. aus)
_ Menü "Transformieren", "Spalten entpivotieren", "andere Spalten entpivotieren"
_ Die Spalte mit kombiniertem Überschrift heisst jetzt "Attribut", die Werte in "Wert". Die Spalte "Attribut" markieren,
_ Menü "Transformieren", "Spalte teilen", "Nach Trennzeichen", "Semikolon" verwenden,
_ Jetzt solltest Du die Jahre in einer "Attribut.1" und die "KZ" in "Attribut.2" haben. "Attribut.2" markieren,
_ Menü "Transformieren", "Spalte pivotieren", Wertespalte "Wert" auswahlen.
_ Die Name der ersten und zweiten Spalten ändern (Doppelklick)
_ Menü "Datei", "Schliessen & laden"
Fertig.
Viel? Wenn dir der VBA-Code im gleichen Art übergeben würde, wäre es noch mehr.
Kompliziert? Ich denke, jede einzelne Schritt ist logisch und nachvollziehbar. Der mehrzeilige Überschrift kostet was. Wenn nur eine, ist es nur ein Klick: entpivotieren.
Aufwändig? Jein. Weil das Beste noch kommt: ändert sich eine Werte in der originale Tabelle oder kommt eine Zeile dazu (die aktive Tabelle erweitert sich automatisch), kann man in die Ergebnis-Tabelle rechtklicken und aktualisieren. Ohne Formel, ohne VBA.
Jetzt bist Du dran. Nicht dass ich das alles um sonst eingetippt hätte.
VG
Yal
Anzeige
AW: Tabelle konsolidieren
09.05.2022 16:41:03
Andreas
Hi Yal,
zunächst vielen Dank. Was für eine Wahnsinnsantwort. Super nachvollziehbar, selbst für mich als absoluten Laien hinsichtlich der PQ.
Ich komme bei fast allem gut mit. Eventuell habe ich am Anfang gleich einen Fehler drin... da weiß ich nicht was mit Zeilen gemeint ist?
Später kommt es gut bis zu dem zusammenfügen mit Semikolon. Anschließend löst sich das aber nicht mehr auf?!
Das Ergebnis, nachdem ich die Abfragen zusammenführe (also die dann leere Tabelle 1 und die neu erstellte Gesamt" am Ende, ist eine Tabelle mit Spalte 1, Spalte 2,... als Überschrift. Darunter dann die mit Semikolon getrennten Informationen in einer Zeile "1" mit den Infos "Jahr; Überschrift der Kennzahl 1; Kennzahl 1".
Kannst du meinen Fehler damit erkennen?
...
Hey YAL. Echt nochmal vielen Dank. Das hat mich schon so viel weiter gebracht!
Anzeige
AW: Tabelle konsolidieren
09.05.2022 17:30:34
Andreas
Hi Yal, ich bin noch einen schritt weiter gekommen. ich habe bei der Semikolontrennung alle spalten und nicht nur die Überschriftsspalten getrennt. Daher kam der "quatsch raus. Mittlerweile habe ich das beschriebene Ergebnis erreicht.
Nun stellt sich aber die Frage, ob ich darauf die Pivots auch aufbauen kann? Mein Muster habe ich nochmal in eine Excel verpackt (siehe unten).
Ziel ist ja quasi eine Rohdatentabelle. Bei der jetzt erzielten steht aber weiterhin das Format wie vorher (also nicht zeile für zeile sonder zeilen / spalten Bezug).
Danke für deine Unterstützung...Gruß Andreas
https://www.herber.de/bbs/user/152961.xlsx
Anzeige
AW: interessehalber nachgefragt ...
09.05.2022 19:57:44
neopa
Hallo Yal,
... hast Du noch die Datei, für die Du Deine PQ-Lösungsvorschlag erstellt hast? Kannst Du diese einstellen. Ich hatte vor ein paar Tagen selbst kurzzeitig mal versucht eine PQ-Lösung aufzustellen. Aber wegen den offensichtlich verbundenen Zellen in den Kopfzeilen ist mir das nicht gelungen. Deshalb interessiert mich Deine.
Gruß Werner
.. , - ...
AW: interessehalber nachgefragt ...
09.05.2022 22:28:41
Yal
Hallo zusammen,
@Werner: ja, gebundene Zellen ist reine Schönheitsache und bei Datenbehandlung eine Krankheit. Weg damit, und zwar per Hand. Nicht per PQ.
Anbei dem Nachbau auf Basis der -endlich gelieferten- Beispieldatei von Andreas.
Ich habe mit =GANZZAHL(ZUFALLSZAHL()*10000) ein paar Testdaten erzeugt und mit kopieren-durch Wert ersetzen fixiert.
@Andreas: hättest Du von Anfang an diese Datei hochgeladen, die sicher nicht viel mehr Aufwand war als eine Zeichnung, hättest Du von Oberschlumpf Thorsten kein Spot sondern wahrscheinlich eine Lösung bekommen.
https://www.herber.de/bbs/user/152967.xlsx
VG
Yal
Anzeige
AW: merkenswerter Lösungsansatz ...
10.05.2022 09:14:49
neopa
Hallo Yal,
... den Du da aufgestellt hast. Vielen Dank Dir dafür.
Ich hatte bisher die Funktion "Überschriften als erste Zeile verwenden" noch nicht im Fokus, die in der Lösung eine Schlüsselrolle spielt.
Hab nun Deinen Ansatz noch so verkürzt, dass keine temp. Hilfsabfrage benötigt.wird. Die Datentabelle habe ich zuvor noch in Tabelle1 umbenannt.
Die allein nötige Abfrage ist nun noch so definiert:

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type any}, {"Spalte3", type any}, {"Spalte4", type any}, {"Spalte5", type any}, {"Spalte6", type any}, {"Spalte7", type any}, {"Spalte8", type any}, {"Spalte9", type any}, {"Spalte10", type any}}),
#"Tiefer gestufte Header" = Table.DemoteHeaders(#"Geänderter Typ"),
#"Transponierte Tabelle" = Table.Transpose(#"Tiefer gestufte Header"),
#"Nach unten gefüllt" = Table.FillDown(#"Transponierte Tabelle",{"Column2"}),
#"Zusammengeführte Spalten" = Table.CombineColumns(Table.TransformColumnTypes(#"Nach unten gefüllt", {{"Column2", type text}}, "de-DE"),{"Column2", "Column3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Zusammengeführt"),
#"Transponierte Tabelle1" = Table.Transpose(#"Zusammengeführte Spalten"),
#"Höher gestufte Header" = Table.PromoteHeaders(#"Transponierte Tabelle1", [PromoteAllScalars=true]),
#"Höher gestufte Header1" = Table.PromoteHeaders(#"Höher gestufte Header", [PromoteAllScalars=true]),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Höher gestufte Header1", {" Land"}, "Attribut", "Wert"),
#"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entpivotierte andere Spalten", "Attribut", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Jahr", "Kennzahl"}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Spalte nach Trennzeichen teilen",{{"Jahr", Int64.Type}, {"Wert", type number}})
in
#"Geänderter Typ1"
Gruß Werner
.. , - ...
Anzeige
Vielen Dank für die Rückmeldung
10.05.2022 09:54:49
Yal
Hallo Werner,
vielen Dank für die Rückmeldung und deinen Beitrag: es ist tatsächlich nicht notwendig, die zwei ersten Zeilen zu isolieren. Es geht auch, wenn man die ganz Tabelle transponiert. Auch mein Zusatz mit einem Zugriff auf dem Abfrage-Zustand nach "geänderter Typ" (und somit ohne Nebenabfrage) wirkt unnötig kompliziert. Das Thema Performance sollte nur -wenn schon- bei sehr grossen Datenmenge eine Rolle spielen.
Ohne Austausch bleibt man in seinem eigenen Gedanken-Korridor ;-)
VG
Yal
AW: letzteres sehe ich auch so owT
10.05.2022 10:23:15
neopa
Gruß Werner
.. , - ...
"Dummy-Header"
10.05.2022 10:03:32
Yal
da wir hier mit einem nichts sagende "Fremd" oder "Dummy"-Header "Spalte1", "Spalte2", ... arbeiten, ist es mit deiner Lösung nicht mehr notwendig, diese zu als Zeile herabzustufen:

#"Tiefer gestufte Header" = Table.DemoteHeaders(#"Geänderter Typ"),
und anschliessend zweimal hochzustufen:

#"Höher gestufte Header" = Table.PromoteHeaders(#"Transponierte Tabelle1", [PromoteAllScalars=true]),
#"Höher gestufte Header1" = Table.PromoteHeaders(#"Höher gestufte Header", [PromoteAllScalars=true]),
Diese Mitnahme der Dummy-Header ist nur für das Anfügen der Nebenabfrage, weil die Überschriften miteinander stimmen müssen. Ohne den Umweg über die Nebenabfrage entfällt die Notwendigkeit. Von den 3 Befehle oben bleibt nur noch den zweiten "PromoteHeaders" übrig.
VG
Yal
Anzeige
AW: das bestätigt unsere vorherige Aussagen ...
10.05.2022 10:28:28
neopa
Hallo Yal,
... dies hatte ich noch aus Deiner ursprünglichen Lösung übernommen ohne dies dann auch kritisch zu hinterfragen.
Einmal höher stufen erscheint mir momentan noch notwendig. Oder?
Die neue Abfrage lautet nun bei mir so:

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type any}, {"Spalte3", type any}, {"Spalte4", type any}, {"Spalte5", type any}, {"Spalte6", type any}, {"Spalte7", type any}, {"Spalte8", type any}, {"Spalte9", type any}, {"Spalte10", type any}}),
#"Transponierte Tabelle" = Table.Transpose(#"Geänderter Typ"),
#"Nach unten gefüllt" = Table.FillDown(#"Transponierte Tabelle",{"Column1"}),
#"Zusammengeführte Spalten" = Table.CombineColumns(Table.TransformColumnTypes(#"Nach unten gefüllt", {{"Column1", type text}}, "de-DE"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Zusammengeführt"),
#"Transponierte Tabelle1" = Table.Transpose(#"Zusammengeführte Spalten"),
#"Höher gestufte Header1" = Table.PromoteHeaders(#"Transponierte Tabelle1", [PromoteAllScalars=true]),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Höher gestufte Header1", {" Land"}, "Attribut", "Wert"),
#"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entpivotierte andere Spalten", "Attribut", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Jahr", "Kennzahl"}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Spalte nach Trennzeichen teilen",{{"Jahr", Int64.Type}, {"Wert", type number}})
in
#"Geänderter Typ1"
Gruß Werner
.. , - ...
Anzeige
Man kann...
10.05.2022 00:31:45
Yal
... genauso wie eine Programiersprache auf eine Variable zugreift, auf eine vorige Schritt zurückgereifen.
In dem Fall im 10te Schritt "Angefügte Abfrage" auf die 2te Schritt "Geänderter Typ" zugreifen und darauf den Table.Skip machen. Dann spart man sich die Nebenabfrage "Temp_Daten":
#"Angefügte Abfrage" = Table.Combine({#"Höher gestufte Header1", Table.Skip(#"Geänderter Typ",2)}),
Gute Nacht.
VG
Yal
AW: Man kann...
10.05.2022 10:16:50
Andreas
Yal. Wahnsinn. Wie auch immer du das machst. Ich bin begeistert und habe schon eine Vielzahl von Problemen im Sinn, die ich damit gelöst/ optimiert bekommen könnte. Ich setz mich da heute Abend mal ran... und versuche es nachzubauen. Bisher konnte ich nur nachvollziehen, dass in den "Pivot aus PQ" die Abfragen und Verbindungen "Ergebnis" und "Temp_Daten"hinterlegt sind (Wie auch immer du die dort versteckt hast habe ich noch nicht herausgefunden).
P.s. Die Datei war tatsächlich nicht der Riesenaufwand. Vielmehr hatte ich die Skizze aus anderen Gründen schon erstellt und konnte die einfach einfügen. Die Exceltabelle habe ich nun erstellt, da der Hinweis von Oberschlumpf schon gut ist. Werde ich zukünftig so machen. Aber wie meine Oma immer sagte: "der Ton macht die Musik". Auf die gleiche Message mit der Formulierung "Könntest du eine Beispieltabelle hochladen?" reagiere ich naturgemäß anders.
Anzeige
AW: dazu mehr ...
10.05.2022 10:41:17
neopa
Hallo Andreas,
... lies mal hier: https://excelhero.de/power-query/power-query-ganz-einfach-erklaert/
In Deiner XL-Version soll/muss eine direkte Funktionalität in PQ geben, wo man aus dem PQ-Ergebnis die Pivotauswertung vornehmen kann.
In meiner älteren XL-Version gibt es mit "Laden in" außer dies als "Tabelle" oder "Nur Verbindung erstellen" nur noch die Option "Dem Datenmodel diese Daten hinzufügen" aus der ich dann die Pivotauswertung über die Excel-Menüfunktion vornehmen kann. In Deiner Version sollte bei der Aktivierung der Funktion: "Laden in" eben noch die direkte Pivotauswertung möglich sein, welcher auch so performanter sein dürfte als über das Datenmodell.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige