Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema Werkzeug
BildScreenshot zu Werkzeug Werkzeug-Seite mit Beispielarbeitsmappe aufrufen

Daten miit PQ spez. umstrukturieren ....

Betrifft: Daten miit PQ spez. umstrukturieren .... von: neopa C
Geschrieben am: 31.10.2020 13:05:25

Hallo an die PQ-Spezialisten,

ein Freund und ehemaliger Kollege bat mich ihm zu helfen eine DOS-Datendatei in eine Datenbank ähnliche Struktur in Excel zu wandeln.

Die vorhandenen DOS-Daten konnte ich schon in eine Exceldatei konvertieren. Die Daten stehen nun alle hintereinander in einer Spalte mit da weit über 200T -Datensätzen

Ziel ist es nun diese Daten in eine Tabelle zu wandeln, wie ich es in beigefügter Datei aufgezeigt habe. Mit meinen bescheidenen PQ-Kenntnissen finde ich leider keinen erfolgversprechenden Weg, wie man das realisieren könnte.

Die Datei: https://www.herber.de/bbs/user/141202.xlsx

Das Problem für mich sind vor allem die Datenstruktur zu Kat. 1 und 2 sowie zu 5 und 7. Bei letzteren (Kat 7) können auch mehr als 3 (wahrscheinlich bis zu max 9) verschiedene Datenwerte anfallen.

Bei evtl. Rükfragen könnte ich allerdings nicht gleich antworten, sondern wohl erst am späteren Nachmittag bzw. gegen Abend.

Wer könnte mir einen Lösungsweg (mit PQ) aufzeigen. Eine Dankeschön schon mal vorab. Würde mich sehr freuen, wieder etwas mehr PQ zu lernen.

Gruß Werner
.. , - ...

Betrifft: AW: Daten miit PQ spez. umstrukturieren ....
von: Daniel
Geschrieben am: 31.10.2020 13:43:32

Hi
Ich vermute mal, dass du da aufgrund der vielen Sonderfälle um eine Programmierung nicht herum kommst.
Ich sehe VBA hier als das geeignetere Werkzeug an, obwohl das auch nicht einfach wird.

Die nächste Frage wäre, welche Teile deiner Beispieldatei sind real und was sind Dummy-Werte, die in der Realität anders heißen, insbesondere bei den Kategoriebezeichnungen wära das wichtig zu wissen, weil Teilweise ja Text und nächste Kategorie in eine Zelle direkt nebeneinander stehen und getrennt werden müssten.

Gruß Daniel

Betrifft: Ausschnitt aus der DOS-Datei wäre hilfreich (owT)
von: EtoPHG
Geschrieben am: 31.10.2020 15:03:22



Betrifft: AW: diese könnte ich zur Verfügung stellen, ...
von: neopa C
Geschrieben am: 31.10.2020 20:20:43

Hallo Hansueli,

... wenn Du meinst damit eher eine PQ-Lösung erstellen zu können. (M)ein Hauptziel ist nämlich aus einer solchen wieder etwas zu lernen. Siehe auch mein Beitrag an Daniel.

Wünsche Dir/Euch noch einen schönen Abend und schönen Sonntag und bleibt gesund.

Gruß Werner
.. , - ...

Betrifft: AW: ja PQ eignet sich eventuell...
von: EtoPHG
Geschrieben am: 31.10.2020 21:05:00

Hallo Werner,

...aber das kann ich erst beurteilen, wenn ich einen signifikanten Ausschnitt aus den Daten bekomme.
Ich möchte die Datenstruktur analysieren können, um zu beurteilen, was das geeigneste Mittel wäre.
Meine e-mail Adresse hast Du ja. Und ich werde die Daten natürlich vertraulich behandeln und nach der Analyse und meiner Antwort wieder vernichten.

Gruess Hansueli

P.S. Gratulation zur kommenden Kulturhauptstadt. Ich wurde aus Deiner Formel (Post) als Schweizer nicht schlau (jetzt natürlich schon ;-) und hab darauf geantwortet um den Beitrag nicht immer in den offenen zu sehen. Auch ein Gruss an Deine Frau.

Betrifft: AW: Mail ist unterwegs owT
von: neopa C
Geschrieben am: 01.11.2020 14:49:55

Gruß Werner
.. , - ...

Betrifft: AW: kann es selbst nicht einschätzen ...
von: neopa C
Geschrieben am: 31.10.2020 20:16:12

Hallo Daniel,

... ob VBA oder PQ das geeignetere Werkzeug für die Lösung ist. Gesucht ist meinerseits jedoch eine PQ-Lösung.

Ich danke Dir auf jeden Fall für Dein Angebot mir zu helfen. Die angegebenen Texte sind natürlich Dummy-Werte. Diese sind in Wirklichkeit unterschiedlich lang, min 1 Zeichen und max. 132 Zeichen. Die von mir angegebenen 11 Kategorie-Bezeichnungen lauten in Wirklichkeit natürlich auch anders. Diese wollte und könnte ich aber mit PQ problemlos in genau diese wandeln und dann in der Ergebnisliste leicht zurück wandeln.

Mich interessiert nun, ob das Problem mit PQ lösbar ist und wenn ja wie, denn mein Freund wäre auch schon mit meiner bisherigen Lösung zufrieden.

Einen schönen Abend Dir noch.

Gruß Werner
.. , - ...

Betrifft: AW: kann es selbst nicht einschätzen ...
von: Daniel
Geschrieben am: 31.10.2020 21:20:22

Nungut
Solltest du eine PQ-Lösung bekommen, würde es natürlich dem Geist des Forums entsprechen, wenn du diese dann auch veröffentlichst und für alle verfügbar machst.

Gruß Daniel

Betrifft: Ansatz mit PQ
von: ChrisL
Geschrieben am: 02.11.2020 15:23:00

Hi Werner

https://www.herber.de/bbs/user/141250.xlsx

Das angesprochene Problem mit den Dummy-Texten müsstest du noch lösen.
Diese wollte und könnte ich aber mit PQ problemlos in genau diese wandeln und dann in der Ergebnisliste leicht zurück wandeln.

Die abschliessende Pivotierung fällt nicht ganz genau wie gewünscht aus, aber ich denke es geht hier mehr um die Datenstrukturierung/-bereinigung.

Die Kommagetrennte Gruppierung habe ich von hier abgekupfert (siehe erweiterter Editor):
https://svens-excel-welt.blogspot.com/2017/01/power-query-mehrere-werte-in-einer.html

cu
Chris

Betrifft: AW:... damit ist schon mal ein Haupteil gelöst ...
von: neopa C
Geschrieben am: 02.11.2020 16:17:32

Hallo Chris,

... zumindest prinzipiell, nämlich die Aufsplittung der Kat1- und Kat2- Daten sowie die Zusammenfassung der Daten der Kat5 und Kat7.

Sehr schön! Vielen Dank Dir dafür schon mal, wieder etwas gelernt :-)

Über eine einfache INDEX() und VERGLEICH()-Formellösung könnte ich jetzt die so von Dir ermittelte Datenliste in Tabelle2!A:C in das von mir angegebene Ergebnislisting gemäß Tablle1!D9:O12 übrführen und hätte damit das angestrebte Ergebnis.

Doch das könnte/sollte doch auch mit PQ möglich sein. Oder? Doch leider fällt mir auch dazu kein passende Lösungsansatz ein. Aber vielleicht oder gar bestimmt Dir!?

Gruß Werner
.. , - ...

Betrifft: AW: AW:... damit ist schon mal ein Haupteil gelöst ...
von: ChrisL
Geschrieben am: 02.11.2020 17:49:28

Hi Werner

Gerne...

Bis vor 2 Sekunden dachte ich, PQ kennt keine pivotieren. Aber guckst du hier:
http://www.excel-ist-sexy.de/umsatz-liste-zu-kreuztabelle-mit-power-query-01/

cu
Chris

Betrifft: AW: auf den ersten Blick ...
von: neopa C
Geschrieben am: 02.11.2020 19:26:38

Hallo Chris,

... sieht es so aus, als könnte es ein Lösungsweg für den 2. Hauptteil meiner Zielstellung sein. Mal sehen, ob ich damit morgen zum von mir angestrebten komme.

Für den Link Dir vielen Dank. Wünsche Dir einen schönen Abend und bleib gesund.

Gruß Werner
.. , - ...

Betrifft: AW: der zweite "Blick", leider ...
von: neopa C
Geschrieben am: 03.11.2020 11:31:36

Hallo Chris,

... hat mir nun gezeigt, was ich gestern noch verdrängt habe, was aber für mich durchaus logisch ist. Mit pivotieren tut man Zahlenwerte auswerten bzw. auch Textwerte zählen, aber keine Texte einer anderen Datenstruktur neu zuordnen. Das hat mir meine kleine sicherlich unvollständige Recherche auf ein paar deutschsprachigen Internetseiten "bestätigt". Leider bin ich auch der engl. Sprache nicht mächtig und das Translaterprogramm hilft mir da nur bedingt.

Das was ich anstrebe, ist zwar pivotähnlich. Es ist ein "transformieren von Daten", die jedoch fast ausschließlich Texte sind. Somit aber sollte dies mE auch mit PQ funktionieren, zumal ich das mit einer einfachen INDEX()und VERGLEICH() -Standardformel realisieren kann. Die PQ-Funktionsgruppe wird ja schließlich seitens MS mit "Abrufen und transformieren" bezeichnet.

Hast Du noch eine Idee?

Gruß Werner
.. , - ...

Betrifft: passt schon...
von: ChrisL
Geschrieben am: 03.11.2020 12:46:46

Hi Werner

Geht ohne Probleme. Du darfst nicht alles glauben, was im Internet steht :-)

https://www.herber.de/bbs/user/141271.xlsx

cu
Chris

Betrifft: AW: prima, ...
von: neopa C
Geschrieben am: 03.11.2020 16:07:24

Hallo Chris,

... oh Mann, war/ist ja wirklich nur noch der eine Schritt zu Deiner bisherigen Lösung zu ergänzen.
Genau so einfach hatte ich mir den letzten Schritt auch vorgestellt. Ich muss da heute Vormittag wirklich große Scheuklappen vor meinem Augen gehabt haben. Das ist die Lösung.

Dir, vielen Dank. Hab wieder einiges lernen können.

Gruß Werner
.. , - ...

Betrifft: gerne, freut mich owT.
von: ChrisL
Geschrieben am: 03.11.2020 16:31:58

.

Betrifft: AW: mich aber viel mehr owT
von: neopa C
Geschrieben am: 03.11.2020 16:48:18

Gruß Werner
.. , - ...

Betrifft: AW: mich aber viel mehr owT
von: Herbert_Grom
Geschrieben am: 03.11.2020 17:58:55

Hallo Werner,

hast Du evtl. eine Idee, warum bei meinem Excel 2019 Power-Query nicht angezeigt wird?



Servus

Betrifft: AW: die Funktionalität von PowerQuery ...
von: neopa C
Geschrieben am: 03.11.2020 19:00:39

Hallo Herbert,

... solltest Du in Deiner XL-Version sicherlich ähnlich wie in meiner XL2016 eingebunden ins Standardexcelmenü vorfinden. Dazu braucht man kein zusätzliches Add-In einzubinden.

Und zwar gibt es im Menü Daten gibt es die Funktionsgruppe "Abrufen und transformieren".
Erst wenn Du da z.B. die Funktion "Aus Tabelle" aktivierst tut sich eine zusätzliches Fenster auf, welcher mit PowerQuery-Editor bezeichnet ist.

Mehr dazu sieh z.B. mal hier: http://www.excel-ist-sexy.de/power-query-das-add-in/

Gruß Werner
.. , - ...

Betrifft: AW: die Funktionalität von PowerQuery ...
von: Herbert_Grom
Geschrieben am: 04.11.2020 09:38:05

Hallo Werner,

OK, das habe ich gefunden. Aber, siehe Bild?



Servus

Betrifft: AW: hierzu ...
von: neopa C
Geschrieben am: 04.11.2020 10:45:13

Hallo Herbert,

... öffne die Datei die Chris eingestellt hat. Dann aktiviere im Menü Daten "Abfragen anzeigen" und dann dort die Abfrage "Vor_List". Damit öffnet sich das PowerQuery-Editor Fenster in dem auf der rechten Seite alle Schritte gelistet sind aktuelle steht dies auf dem letzten Schritt. Stell Dich auf den ersten und bewege Dich Schritt für Schritt vorwärts. So kannst Du zumindest nachverfolgen, was Chris getan hat.

Gruß Werner
.. , - ...

Betrifft: AW: hierzu ...
von: Herbert_Grom
Geschrieben am: 04.11.2020 10:57:14

Hallo Werner,

vielen Dank, so wird es klar!

Servus

Betrifft: Kat in Überschrift nicht sortiert-geht das?
von: Helmut
Geschrieben am: 04.11.2020 12:58:26

Gruß Helmut

Betrifft: ja...
von: ChrisL
Geschrieben am: 04.11.2020 13:29:53

Hi Helmut

https://www.herber.de/bbs/user/141307.xlsx

Im Prinzip einfach noch sortieren, bevor pivotiert wird.

Da es aber Texte sind, würde die Sortierung Kat1, Kat10, Kat11, Kat2, Kat3 ergeben. Darum erst "Kat" entfernen, Text in Zahl konvertieren, sortieren und Kat wieder anfügen.

N.b. PQ ist in Bezug auf Datentypen etwas heikler wie Excel/VBA. Textverkettungen gehen nur mit Texten d.h. die Zahl muss erst zurück in einen Text konvertiert werden.

cu
Chris

Betrifft: AW: Danke, Methode ist "gespeichert" ;-) Gruß
von: Helmut
Geschrieben am: 04.11.2020 13:35:11



Betrifft: AW: im Original so nicht notwendig ...
von: neopa C
Geschrieben am: 04.11.2020 14:41:20

Hallo Chris, hallo Helmut,

... wäre auch nicht so möglich, da die Kat# ja lediglich von mir vergebenen Dummy-Bezeichnungen sind. Im Original stehen da Textwerte (ohne Nummerierung), die auch nicht alphabetisch sortiert sind. Die erforderliche Reihenfolge kann ich somit einfach nach dem Pivotieren in durch Spaltenverschiebung vornehmen.

Gruß Werner
.. , - ...

Betrifft: AW: die Sortierung ist kein Problem ...
von: neopa C
Geschrieben am: 04.11.2020 14:24:43

Hallo Helmut,

... das ist lediglich ein kleiner zusätzlicher Schritt in PQ

Gruß Werner
.. , - ...

Betrifft: Danke, alles klar :-) Gruß
von: Helmut
Geschrieben am: 04.11.2020 15:25:32



Betrifft: AW: VBA?
von: Fennek
Geschrieben am: 31.10.2020 13:45:52

Hallo,

gestern kam in CEF eine ähnliche Frage:

https://www.clever-excel-forum.de/Thread-Tabelle-in-Zeilen-aufteilen

Mit etwas Anpassung könnte das hier auch gehen.

mfg

(heute werde ich keine Zeit für einen angepassten Vorschlag haben)

Betrifft: AW: Beispiel
von: Fennek
Geschrieben am: 31.10.2020 17:26:57

Hallo,

ein Test mit LibreOffice ging schief und den PC mit Excel werde ich nicht hochfahren. Aber so ähnlich sollte es gehen:

In Spalte B die Formel eingeben: =WENN(LINKS(A2;3)="Nr:";1;"a")
Dann:
Sub Main

rr=15 ' ab Zeile
for each ar in columns(2).specialcells(2,2).areas
	ar.offset(,-1).copy
	cells(rr, 4).pastespecial transpose:=true
	rr=rr+1
next ar

End Sub
mfg

Betrifft: AW: nein, nicht wirklich ...
von: neopa C
Geschrieben am: 31.10.2020 20:17:20

Hallo Fennek,

... dazu siehe bitte auch mein Beitrag an Daniel. Aber danke für Dein Angebot mir zu helfen.

Einen schönen Abend Dir noch.

Gruß Werner
.. , - ...

Betrifft: AW: Daten miit PQ spez. umstrukturieren ....
von: Herbert_Grom
Geschrieben am: 31.10.2020 16:05:34

Hallo Werner,

ich nehme mich deines Problems an. Ich schicke dir eine eMail!

Servus

Betrifft: AW: Antwort ist unterwegs owT
von: neopa C
Geschrieben am: 31.10.2020 20:21:03

Gruß Werner
.. , - ...