Multi-Split und transponieren

Zellen mehrfach teilen und transponieren

Die Aufgabe

In einem Forum wurde diese für mich reizvolle Anfrage bzw. Aufgabe eingestellt: Durch einen Import werden alle Werte in einem Arbeitsblatt in Spalte A geschrieben. Stellvertretend hier die ersten fünf Zeilen der Daten einschließlich Überschrift:

Die Roh-Daten (Ausschnitt)

Die Roh-Daten (Ausschnitt)

Diese Daten sollen gesplittet, in mehrere Spalten aufgeteilt werden. Anhand der Überschrift ist zu vermuten, dass es zwei Spalten werden sollen: BNR und PLZ. Das ist auch bedingt richtig. Der Wert BNR soll in jedem Fall nur in die erste Spalte geschrieben werden, PLZ beginnt in Spalte B. – Jeweils Zeile 2 und Zeile 3 bzw. Zeile 5 ist ja auch klar, eindeutig nur eine (3-stellige) PLZ. Zeile 4 allerdings zeichnet sich dadurch aus, dass dort offensichtlich mehrere PLZ -durch ein Pipe-Symbol (|) getrennt, drin stehen. Und die Forderung ist nun, dass auch diese Eintragungen bei Bedarf in mehrere Spalten aufgeteilt werden. Für später ist es angedacht, auch noch eine Auswertung bzw. gefilterte Aufstellung zu machen. Die ursprünglichen, unformatierten Daten finden Sie in dieser Datei, Sie sollten sie sich herunter laden.

▲ nach oben …

Scheinbar simple Lösung

Bis hierhin wäre es die einfachste Möglichkeit, mehrfach die Funktionalität Text in Spalten einzusetzen. Im ersten Schritt das Leerzeichen als Trenner, danach in Spalte B das |-Zeichen als Separator verwenden. So weit, so gut. Die eigentliche Aufgabe stellt sich aber so dar, dass bei mehreren PLZ auch mehrere Zeilen generiert werden sollen, die BNR wird also so oft in der Folgezeile wiederholt, wie PLZ vorhanden sind. Im Endeffekt soll sich das so darstellen (die gleichen Daten wie oben):

Ziel-Ansicht (Prinzip)

Ziel-Ansicht (Prinzip)

Lösung mit Plain Excel

Es ist gewiss möglich, eine Lösung auf reiner Formel-Basis zu erstellen. Das muss ich mir und möchte ich Ihnen aber nicht antun. Einer der Gründe ist die gewiss fehlende Transparenz. Ein weiterer Grund: Ganz zu Beginn ist das Stichwort „Import“ gefallen; und das bedeutet in den meisten Fällen, dass sich die Basis-Daten auch einmal ändern können, dass in Intervallen (beispielsweise wöchentlich) neue Auswertungen gemacht werden sollen. Und dann immer wieder das Formel-Konstrukt neu einfügen, das muss nicht sein.  😕 

▲ nach oben …

Lösung per VBA

Für immer wiederkehrende Vorgänge bietet sich VBA, also eine Makro-Lösung förmlich an. Insbesondere dann, wenn der gesamte Vorgang aus mehreren komplexen Schritten besteht. Ich selbst bin VBA-Fan, werde hier aber nur den gar nicht bzw. minimal kommentierten Code für diese Aufgabe vorstellen. Der Grund: Für die meisten Fälle scheint mir die Power Query Lösung geeigneter zu sein. Ausnahmen sind alle Versionen vor Excel 2010, da steht dieses Add-In noch nicht zur Verfügung und es muss ein Makro eingesetzt werden, wenn Automatisierung gefragt ist. Eine sehr schlanke Lösung wäre beispielsweise dieser Code:

Bis zu etwa 10.000 Zeilen sollte die Geschwindigkeit bei modernen Rechnern ausreichend sein. Bei sehr großen Datenmengen kann dieser Code etwas langatmig werden. In dieser Datei (DoppelSplit.xlsm) ist ein anderer Code enthalten, der um einiges umfangreicher dafür aber auch übersichtlicher ist. Mit AltF11 können Sie sich ihn ansehen (und auch ausführen), mit AltF8 direkt starten. Falls Sie ein anderes (zweites) Trennzeichen als das Pipe-Symbol haben, dann sollten Sie im Code (oben) die Const-Zuweisung anpassen. Außerdem können Sie in diesem File auch die von mir erarbeiteten Ergebnisse betrachten. Sie ind in versteckten Arbeitsblättern zu finden. Sie wissen gewiss, dass ein Rechtsklick auf ein sichtbares Blatt-Register das Einblenden ermöglicht.

▲ nach oben …

Lösung per Power Query

In manchen Unternehmen wird aus gutem Grund die Verwendung von Makros unterbunden bzw. untersagt. Derzeit treibt wieder einmal eine Schad-Software ihr Unwesen, die sich auch über Makros in Office-Anwendungen verbreitet. Einer der bekanntesten Vertreter ist „Locky“, welcher alle wichtigen Daten auf dem Rechner, dem Netzwerk, der Cloud verschlüsselt und anschließend ein nicht unerhebliches Lösegeld fordert, damit die Daten wieder entschlüsselt werden können und damit auch wieder verwendbar sind. In dem Rahmen unser Angebot an Sie: Wenn Sie lieber die Excel-Datei und den Code getrennt haben wollen, dann bitte einfach eine Mail an team@excel-ist-sexy.de. Wir kümmern uns dann darum.

Power Query ist ein Add-In, welches von Microsoft auf deren Website für die Excel-Versionen 2010 und 2013 zur Verfügung gestellt wird. Ab Excel 2016 ist es bereits integriert. Mehr dazu können Sie hier in unserem Blog nachlesen. Die wichtigsten Eckpunkte: Viele Möglichkeiten, hoher Automatismus, kein VBA/Makro, Aktualisierung veränderter Quelldaten mit einem Klick. Die meisten Anpassungen an neue Gegebenheiten sind mit wenigen Schritten innerhalb der grafischen Oberfläche machbar.

Ich gehe davon aus, dass bei Ihnen Power Query installiert ist (Excel 2010/13) und Sie die Excel-Datei mit den Daten bereits geladen haben. Im ersten Schritt klicken Sie irgendwo in die Daten und erstellen daraus eine Intelligente Tabelle/Liste. Das geht entweder über das Menü (Als Tabelle formatieren) oder per Tastenkombination StrgL bzw. StrgT.

Ich ziehe es vor, eine der beiden Tastenkombinationen zu drücken, um aus den Daten eine Liste bzw. Intelligente Tabelle zu generieren. In jedem Fall werden Sie darauf achten, dass bei Tabelle hat Überschriften das Häkchen gesetzt wird. Nach einem OK wird sich wahrscheinlich das Aussehen der Liste etwas geändert haben:

Eine "echte" Liste

Eine „echte“ Liste

Mindestens 1 Zelle in der Liste sollte markiert sein, dann im Menü den Punkt Power Query auswählen bzw. das Menü Daten. Die Beschreibung hier bezieht sich auf 2010/13, 2016 ist aber sehr ähnlich. In der Gruppe Excel-Daten wählen Sie die einzige Schaltfläche Von Tabelle. Sofort öffnet sich ein neues Fenster, der Abfrage-Editor:

Der Abfrage-Editor (OK)

Der Abfrage-Editor (OK)

Sollte die erste Zeile jedoch diesen Inhalt haben…:

Abfrage-Editor (Fehler bei der Überschrift)

Abfrage-Editor (Fehler bei der Überschrift)

…dann hatten Sie bei der Umwandlung in eine Intelligente Tabelle/Liste kein Häkchen bei Tabelle hat Überschriften gesetzt. Sie können in dem Fall entweder mittels mehrerer StrgZ bis zu dem Punkt zurückgehen, wo aus den „normalen“ Daten eine Liste erstellt wurde und dann das Häkchen setzen oder aber Sie bleiben in diesem Fenster und klicken in der Gruppe Transformieren auf die Auswahl Erste Zeile als Überschrift verwenden.

In diesem Fenster, dem Editor werden alle Modifikationen vorgenommen und später in einem neuen Arbeitsblatt gespeichert; die Originaldaten bleiben also unberührt. Im ersten Schritt soll die BNR vom Bereich der PLZ getrennt werden. Hier ist das Leerzeichen als Trenner zu verwenden.

Klicken Sie dazu in der Gruppe Transformieren auf das Symbol Spalte teilen und in der Unter-Auswahl auf Nach Trennzeichen:

Spalte nach Trennzeichen splitten

Spalte nach Trennzeichen splitten

Statt des vorselektierten Komma wählen Sie das Leerzeichen aus und nach einem OK sehen Sie sofort die beiden Spalten:

BNR und PLZ sind getrennt

BNR und PLZ sind getrennt

Die „angepassten“ Überschriften sind derzeit nicht relevant. Wichtig ist, dass nun BNR und PLZ in getrennten Spalten stehen. Das ist die erste Grundvoraussetzung. Im kommenden Schritt sollen auch die durch ein Pipe-Symbol | getrennten PLZ in je eine neue Spalte aufgeteilt werden. Prinzipiell ist das Vorgehen gleich; Sie markieren die zweite Spalte (BNR PLZ.2) und klicken wiederum auf Spalte teilen. Auch hier ist es ein Trennzeichen, welche als Marker gilt. Da aber das | nicht in der Liste erscheint, muss der unterste Punkt –Benutzerdefiniert– ausgewählt und in das Textfeld dieses Pipe-Symbol per Tastatur eingegeben werden.

Bevor Sie weiter machen, schauen Sie sich noch einmal die PLZ der ersten Datenzeile genau an. Vielleicht fällt Ihnen ja etwas auf … OK und die PLZ hat sich sehr schön aufgeteilt:

Alle PLZ sind in einzelne Spalten gesplittet

Alle PLZ sind in einzelne Spalten gesplittet

Vergleichen Sie jetzt einmal den Inhalt der PLZ in der ersten Datenzeile. Es ist durchaus denkbar, ja sogar wahrscheinlich, dass dort eine kleine Änderung stattgefunden hat. Als Tipp: Die Ausrichtung der Werte in der ersten PLZ-Spalte ist nun rechts.

Jetzt sind es prinzipiell nur noch zwei Schritte zum Ziel: Die gesplitteten PLZ müssen transformiert werden (Spalten zu Zeilen) und Bei BNR muss dann bei Bedarf die einzelne BNR so oft zeilenweise wiederholt werden, wie es zugehörige PLZ gibt. Und das geht mit Power Pivot recht einfach:

  • Sorgen Sie dafür, dass die erste Spalte mit den PLZ markiert ist.
  • Shift und ein Klick auf die letzte Spalte, damit alle Spalten mit Ausnahme der ersten markiert sind.
  • Wechseln Sie in das Menü Transformieren.
  • In der Gruppe Beliebige Spalte klicken Sie auf Spalten entpivotieren.

Aufruf zum entpivotisieren

Aufruf zum entpivotieren

Gleich danach stellt sich das Fenster so dar:

Ruck zuck stimmt der geforderte Aufbau (fast)

Ruck zuck stimmt der geforderte Aufbau (fast)

  • Danach erkennen Sie, dass die zweite Spalte (Attribut) nicht gebraucht wird, klicken Sie darum in die Überschrift der Spalte, damit nur diese markiert ist.
  • Rechtsklick in die Überschrift und dann Entfernen wählen.

Es scheint so, dass damit das Ziel erreicht ist. Aber es scheint nur so, denn das Zahlenwerk in der zweiten Spalte macht ja einen richtigen Zick-Zack-Kurs. Mal Zahl, mal Text, was ja an der Ausrichtung zu erkennen ist. Und was eigentlich viel wichtiger ist: Diese Spalte soll ja immer die drei ersten Ziffern einer 5-stelligen Postleitzahl enthalten. Und die Zeile 1 fällt natürlich auf, es sind nur 2 Ziffern. Darum sollten alle Werte dieser Spalte als Text dargestellt werden, da sind führende Nullen kein Problem.

Die zweite Spalte ist ja immer noch markiert und das Menü Transformieren ist gewiss auch noch aktiv. In der Gruppe Beliebige Spalte klicken Sie auf den Eintrag Datentyp: Beliebig DropDownPfeil und dann den Eintrag Text. Jetzt steht zwar alles schön linksbündig, aber es bleibt (wahrscheinlich) bei der 91 statt der erforderlichen 091.

Es gibt mehrere Möglichkeiten, zum Ziel zu gelangen. Ich versuche fast immer, den Weg des geringsten Widerstandes zu nehmen. 😉  Ganz rechts im Abfrage-Editor sehen Sie den Bereich der Abfrageeinstellungen. Und dort den Bereich Angewendete Schritte, wo alle einzelnen Schritte als eine Art Haltepunkt dokumentiert sind. Klicken Sie hier einmal auf die zweite Zeile Spalte nach Trennzeichen teile… und Sie erkennen im Daten-Fenster, dass zu der Zeit noch 091 in Zeile 1 steht:

Letzter Schritt mit Text-PLZ

Letzter Schritt mit Text-PLZ

 

Markieren Sie nun den nachfolgenden Eintrag Geänderter Typ2 und Sie sehen, dass genau hier die Änderung vorgenommen worden ist. Bequem, wie ich nun einmal bin, jetzt einfach nur StrgA und alle Spalten werden markiert. Strg und ein Klick in die Überschrift der ersten Spalte bewirkt, dass jetzt nur noch die Spalten mit den PLZ markiert sind. Und jetzt noch einmal den Datentyp auf Text setzen, wie eben schon gemacht. Die Nachfrage, ob Sie einen Schritt einfügen wollen, bestätigen Sie mit Einfügen.

Wenn Sie jetzt auf den vorletzten Eintrag Entfernte Spalten klicken werden Sie erkennen, dass die PLZ alle dreistellig sind (übrigens auch die am Ende der Intelligenten Tabelle, in den Zeilen 535 und folgende). Der letzte aufgezeichnete Schritt der Typänderung ist ja nun überflüssig, der Datentyp stimmt ja. Darum ein Rechtsklick auf Geänderter Typ3 und Löschen.

Geschafft. Das Ziel ist erreicht, zumindest sind Sie auf der Ziellinie. Der Vollständigkeit halber könnten Sie die beiden Überschriften noch anpassen. Entweder ein Doppelklick in das jeweilige Feld oder ein Rechtsklick und Umbenennen… und Sie schreiben dort BNR und PLZ hinein. Jetzt nur noch Menü Start und auf das erste Symbol Schließenn & laden klicken. Automatisch wird ein neues Tabellenblatt mit einer grün liniierten Liste angelegt. Und die ist exakt so, wie gefordert.

▲ nach oben …

Vielleicht fragen Sie sich, warum der ganze Aufwand. Wie schon zu Anfang erwähnt ist dieses wohl der beste Weg, wenn Sie keine Makros einsetzen können, dürfen oder wollen. Die wahre Stärke aber liegt darin, dass Sie bei wiederkehrenden Dateneingängen einfach nur die Ursprungsdaten (die Quelldaten) aktualisieren (überschreiben) und dann in (der jetzigen) Tabelle2 die Tabellentools (Menü) wählen, Aktualisieren anklicken und sich freuen. Das war’s nämlich schon! Und sollten einmal andere oder weitere Kriterien dazu kommen, dass beispielsweise das Trennzeichen ein anderes wird, dann einfach im rechten Bereich des Fensters ein Doppelklick auf die große, grünliche Schaltfläche mit dem Tabellennamen (es ist übrigens der Name der Liste, nicht des Arbeitsblatts) und schon sind Sie wieder im Abfrage-Editor und können gezielt Änderungen vornehmen.

Apropos „Weg des geringsten Widerstandes“: Bekanntlich macht ja nur Übung den Meister, darum vollziehen Sie das Ganze noch einmal von Anfang an. Benennen Sie am besten die Liste der Quelldaten erst einmal um, damit keine Namenskonflikte entstehen. Wie so etwas ganz effektiv geht, können Sie beispielsweise hier nachlesen. Ich verwende den Listen-Namen lst_Quelldaten. Sie arbeiten sich dann bis zum zweiten Teilen der Spalte vor:

Diese Zeile kann gelöscht werden

Diese Zeile kann gelöscht werden

Bei den Abfrageeinstellungen einfach den letzten Schritt (Geänderter Typ2) löschen. Das geht übrigens auch mit Entf. Dann sind die Werte schon einmal vierstellig. Anschließend ein Rechtsklick in die Überschrift der ersten Spalte und diese Auswahl treffen:

Alle anderen Spalten werden entpivotisiert

Alle anderen Spalten werden entpivotiert

Der Rest ist dann wieder wie gehabt. Sie sehen, dass der Rechtsklick an der richtigen Stelle eingesetzt, mitunter Vorteile bringt. Aber bekanntlich gilt auch immer wieder der Satz: „Wo viel Licht, da viel Schatten“. Sie haben jetzt ja zwei Abfragen im Bereich Arbeitsmappenabfragen. Und die obere zeigt sich mit einem kleinen Warnschild statt des Tabellensymbols. Ein Doppelklick darauf und Sie bekommen etwas mehr Informationen.

So verführerisch es auch sein mag, bei bzw. in den Abfrageeinstellungen bleibt es beim eingetragenen Namen, hier: Tabelle4. Nein, Sie müssen etwas tiefer in die Materie eintauchen. Im folgenden Bild sehen Sie, dass ich in der Code-Zeile den bisherigen Wert Tabelle4 markiert habe:

Der Bereichsname der Quelldaten ist falsch!

Der Bereichsname der Quelldaten ist falsch!

 

Das überschreiben Sie mit dem Namen, welchen Sie vorher der Liste mit den Quelldaten gegeben haben, also lst_Quelldaten. Einfach Eingabe und schon sind nach einem Klick auf Schließen & laden  beide Ergebnis-Tabellen gleichwertig. Natürlich können Sie jede der beiden Abfragen getrennt nach Ihren Wünschen ändern.

▲ nach oben …

Pivot-Auswertung

Zu Beginn dieses Beitrags wurde kurz angesprochen, dass eventuell eine Pivot-Auswertung angestrebt wird. Jetzt, wo Sie das Ergebnis vor Augen haben werden Sie auch erkennen, dass die ganze Umstrukturierung der Daten gewiss ganz genau diesem Zweck gedient hat.

Der Titel dieses Beitrages zeigt ja deutlich auf, dass die Neuanordnung der Daten die eigentliche Aufgabe darstellt. Das Problem ist gelöst. Um Ihnen einen kleinen Einblick in diese Fortführung zu bieten, habe ich hier einige wenige Stichworte (und wirklich nur diese) aufgelistet. Zum Thema Aufbau und Erstellung einer PivotTable gibt es reichlich Beiträge hier im Blog und natürlich auch im Netz.

  • Klicken Sie in die eben erstellte Tabelle.
  • Erstellen Sie daraus auf beliebige Weise eine PivotTable in einem neuen Arbeitsblatt.
  • Ziehen Sie nacheinander die Felder BNR und PLZ in den Bereich Zeilen.
  • Aktivieren Sie in den PivotTable-Tools den Menüpunkt Analysieren.
  • Klicken Sie im Bereich Filtern auf Datenschnitt einfügen.
  • Wählen Sie die PLZ als Datenschnitt.
  • Filtern Sie hier (mittels Strg) beispielsweise die Postleitbereiche 605 und 630:

Beispiel für eine Pivot-Auswertung

Beispiel für eine Pivot-Auswertung

„Spielen“ Sie gerne noch etwas mit den Daten herum, sei es in der Pivot-Auswertung oder fügen Sie auch gerne bei den Quelldaten eine weitere Spalte oder mehrere Zeilen dazu. Das stärkt Sie in der Handhabung der fortgeschrittenen Excel-Techniken.

▲ nach oben …

Dieser Beitrag wurde unter Daten-Import / -Export, Formatierung, Mit VBA/Makro, Musterlösungen, Ohne Makro/VBA, Pivot, Power Query, Spalten bearbeiten, Tabelle und Zelle, Transponieren, Wege nach Rom abgelegt und mit , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.