Die Aufgabe

In einem Forum wurde diese für mich reizvolle Anfrage bzw. Auf­gabe eingestellt: Durch einen Import wer­den alle Werte in einem Arbeits­blatt in Spalte A geschrieben. Stel­lvertre­tend hier die ersten fünf Zeilen der Dat­en ein­schließlich Über­schrift:

Die Roh-Dat­en (Auss­chnitt)

Diese Dat­en sollen ges­plit­tet, in mehrere Spal­ten aufgeteilt wer­den. Anhand der Über­schrift ist zu ver­muten, dass es zwei Spal­ten wer­den sollen: BNR und PLZ. Das ist auch bed­ingt richtig. Der Wert BNR soll in jedem Fall nur in die erste Spalte geschrieben wer­den, PLZ begin­nt in Spalte B. – Jew­eils Zeile 2 und Zeile 3 bzw. Zeile 5 ist ja auch klar, ein­deutig nur eine (3‑stellige) PLZ. Zeile 4 allerd­ings zeich­net sich dadurch aus, dass dort offen­sichtlich mehrere PLZ ‑durch ein Pipe-Sym­bol (|) getren­nt, drin ste­hen. Und die Forderung ist nun, dass auch diese Ein­tra­gun­gen bei Bedarf in mehrere Spal­ten aufgeteilt wer­den. Für später ist es angedacht, auch noch eine Auswer­tung bzw. gefilterte Auf­stel­lung zu machen. Die ursprünglichen, unfor­matierten Dat­en find­en Sie in dieser Datei, Sie soll­ten sie sich herunter laden.

▲ nach oben …

Scheinbar simple Lösung

Bis hier­hin wäre es die ein­fach­ste Möglichkeit, mehrfach die Funk­tion­al­ität Text in Spal­ten einzuset­zen. Im ersten Schritt das Leerze­ichen als Tren­ner, danach in Spalte B das |-Zeichen als Sep­a­ra­tor ver­wen­den. So weit, so gut. Die eigentliche Auf­gabe stellt sich aber so dar, dass bei mehreren PLZ auch mehrere Zeilen gener­iert wer­den sollen, die BNR wird also so oft in der Fol­gezeile wieder­holt, wie PLZ vorhan­den sind. Im End­ef­fekt soll sich das so darstellen (die gle­ichen Dat­en wie oben):

Ziel-Ansicht (Prinzip)

Lösung mit Plain Excel

Es ist gewiss möglich, eine Lösung auf rein­er Formel-Basis zu erstellen. Das muss ich mir und möchte ich Ihnen aber nicht antun. Ein­er der Gründe ist die gewiss fehlende Trans­parenz. Ein weit­er­er Grund: Ganz zu Beginn ist das Stich­wort „Import“ gefall­en; und das bedeutet in den meis­ten Fällen, dass sich die Basis-Dat­en auch ein­mal ändern kön­nen, dass in Inter­vallen (beispiel­sweise wöchentlich) neue Auswer­tun­gen gemacht wer­den sollen. Und dann immer wieder das Formel-Kon­strukt neu ein­fü­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örm­lich an. Ins­beson­dere dann, wenn der gesamte Vor­gang aus mehreren kom­plex­en Schrit­ten beste­ht. Ich selb­st bin VBA-Fan, werde hier aber nur den gar nicht bzw. min­i­mal kom­men­tierten Code für diese Auf­gabe vorstellen. Der Grund: Für die meis­ten Fälle scheint mir die Pow­er Query Lösung geeigneter zu sein. Aus­nah­men sind alle Ver­sio­nen vor Excel 2010, da ste­ht dieses Add-In noch nicht zur Ver­fü­gung und es muss ein Makro einge­set­zt wer­den, wenn Automa­tisierung gefragt ist. Eine sehr schlanke Lösung wäre beispiel­sweise dieser Code:

Sub MultiSplit()
   Dim lRowS As Integer, lRowD As Integer
   Dim ZeSrc As Integer, ZeDst As Integer, i As Integer
   Dim Data As String, Teil As String, Split1, Split2
 
   Columns("B:C").NumberFormat = "@"
   Application.ScreenUpdating = False
   lRowS = Cells(Rows.Count, 1).End(xlUp).Row
   For ZeSrc = 1 To lRowS
      Data = Cells(ZeSrc, 1)
      lRowD = Cells(Rows.Count, 2).End(xlUp).Row + 1
      Split1 = Split(Data, " ")
      Split2 = Split(Split1(1), "|")
      Cells(lRowD, 2) = Split1(0)
      If UBound(Split2) = 0 Then
         Cells(lRowD, 3) = Format(Split1(1), "000")
      Else
         For i = 0 To UBound(Split2)
            Cells(lRowD + i, 2) = Split1(0)
            Cells(lRowD + i, 3) = Format(Split2(i), "000")
         Next i
      End If
   Next ZeSrc
   Columns(1).Delete
   Rows(1).Delete
End Sub

Bis zu etwa 10.000 Zeilen sollte die Geschwindigkeit bei mod­er­nen Rech­n­ern aus­re­ichend sein. Bei sehr großen Daten­men­gen kann dieser Code etwas lan­gat­mig wer­den. In dieser Datei (DoppelSplit.xlsm) ist ein ander­er Code enthal­ten, der um einiges umfan­gre­ich­er dafür aber auch über­sichtlich­er ist. Mit AltF11 kön­nen Sie sich ihn anse­hen (und auch aus­führen), mit AltF8 direkt starten. Falls Sie ein anderes (zweites) Trennze­ichen als das Pipe-Sym­bol haben, dann soll­ten Sie im Code (oben) die Con­st-Zuweisung anpassen. Außer­dem kön­nen Sie in diesem File auch die von mir erar­beit­eten Ergeb­nisse betra­cht­en. Sie ind in ver­steck­ten Arbeits­blät­tern zu find­en. Sie wis­sen gewiss, dass ein Recht­sklick auf ein sicht­bares Blatt-Reg­is­ter das Ein­blenden ermöglicht.

▲ nach oben …

Lösung per Power Query

In manchen Unternehmen wird aus gutem Grund die Ver­wen­dung von Makros unter­bun­den bzw. unter­sagt. Derzeit treibt wieder ein­mal eine Schad-Soft­ware ihr Unwe­sen, die sich auch über Makros in Office-Anwen­dun­gen ver­bre­it­et. Ein­er der bekan­ntesten Vertreter ist „Locky”, welch­er alle wichti­gen Dat­en auf dem Rech­n­er, dem Net­zw­erk, der Cloud ver­schlüs­selt und anschließend ein nicht uner­he­blich­es Lösegeld fordert, damit die Dat­en wieder entschlüs­selt wer­den kön­nen und damit auch wieder ver­wend­bar sind. In dem Rah­men unser Ange­bot an Sie: Wenn Sie lieber die Excel-Datei und den Code getren­nt haben wollen, dann bitte ein­fach eine Mail an team@excel-ist-sexy.de. Wir küm­mern uns dann darum.

Pow­er Query ist ein Add-In, welch­es von Microsoft auf deren Web­site für die Excel-Ver­sio­nen 2010 und 2013 zur Ver­fü­gung gestellt wird. Ab Excel 2016 ist es bere­its inte­gri­ert. Mehr dazu kön­nen Sie hier in unserem Blog nach­le­sen. Die wichtig­sten Eck­punk­te: Viele Möglichkeit­en, hoher Automa­tismus, kein VBA/Makro, Aktu­al­isierung verän­dert­er Quell­dat­en mit einem Klick. Die meis­ten Anpas­sun­gen an neue Gegeben­heit­en sind mit weni­gen Schrit­ten inner­halb der grafis­chen Ober­fläche mach­bar.

Ich gehe davon aus, dass bei Ihnen Pow­er Query instal­liert ist (Excel ²⁰¹⁰⁄₁₃) und Sie die Excel-Datei mit den Dat­en bere­its geladen haben. Im ersten Schritt Klick­en Sie irgend­wo in die Dat­en und erstellen daraus eine Intel­li­gente Tabelle/Liste. Das geht entwed­er über das Menü (Als Tabelle for­matieren) oder per Tas­tenkom­bi­na­tion StrgL bzw. StrgT.

Ich ziehe es vor, eine der bei­den Tas­tenkom­bi­na­tio­nen zu drück­en, um aus den Dat­en eine Liste bzw. Intel­li­gente Tabelle zu gener­ieren. In jedem Fall wer­den Sie darauf acht­en, dass bei Tabelle hat Über­schriften das Häkchen geset­zt wird. Nach einem OK wird sich wahrschein­lich das Ausse­hen der Liste etwas geän­dert haben:

Eine „echte” Liste

Min­destens 1 Zelle in der Liste sollte markiert sein, dann im Menü den Punkt Pow­er Query auswählen bzw. das Menü Dat­en. Die Beschrei­bung hier bezieht sich auf ²⁰¹⁰⁄₁₃, 2016 ist aber sehr ähn­lich. In der Gruppe Excel-Dat­en wählen Sie die einzige Schalt­fläche Von Tabelle. Sofort öffnet sich ein neues Fen­ster, der Abfrage-Edi­tor:

Der Abfrage-Edi­tor (OK)

Sollte die erste Zeile jedoch diesen Inhalt haben…:

Abfrage-Edi­tor (Fehler bei der Über­schrift)

…dann hat­ten Sie bei der Umwand­lung in eine Intel­li­gente Tabelle/Liste kein Häkchen bei Tabelle hat Über­schriften geset­zt. Sie kön­nen in dem Fall entwed­er mit­tels mehrerer StrgZ bis zu dem Punkt zurück­ge­hen, wo aus den „nor­malen” Dat­en eine Liste erstellt wurde und dann das Häkchen set­zen oder aber Sie bleiben in diesem Fen­ster und Klick­en in der Gruppe Trans­formieren auf die Auswahl Erste Zeile als Über­schrift ver­wen­den.

In diesem Fen­ster, dem Edi­tor wer­den alle Mod­i­fika­tio­nen vorgenom­men und später in einem neuen Arbeits­blatt gespe­ichert; die Orig­i­nal­dat­en bleiben also unberührt. Im ersten Schritt soll die BNR vom Bere­ich der PLZ getren­nt wer­den. Hier ist das Leerze­ichen als Tren­ner zu ver­wen­den.

Klick­en Sie dazu in der Gruppe Trans­formieren auf das Sym­bol Spalte teilen und in der Unter-Auswahl auf Nach Trennze­ichen:

Spalte nach Trennze­ichen split­ten

Statt des vorse­lek­tierten Kom­ma wählen Sie das Leerze­ichen aus und nach einem OK sehen Sie sofort die bei­den Spal­ten:

BNR und PLZ sind getren­nt

Die „angepassten” Über­schriften sind derzeit nicht rel­e­vant. Wichtig ist, dass nun BNR und PLZ in getren­nten Spal­ten ste­hen. Das ist die erste Grund­vo­raus­set­zung. Im kom­menden Schritt sollen auch die durch ein Pipe-Sym­bol | getren­nten PLZ in je eine neue Spalte aufgeteilt wer­den. Prinzip­iell ist das Vorge­hen gle­ich; Sie markieren die zweite Spalte (BNR PLZ.2) und Klick­en wiederum auf Spalte teilen. Auch hier ist es ein Trennze­ichen, welche als Mark­er gilt. Da aber das | nicht in der Liste erscheint, muss der unter­ste Punkt –Benutzerdefiniert– aus­gewählt und in das Textfeld dieses Pipe-Sym­bol per Tas­tatur eingegeben wer­den.

Bevor Sie weit­er machen, schauen Sie sich noch ein­mal die PLZ der ersten Daten­zeile genau an. Vielle­icht fällt Ihnen ja etwas auf … OK und die PLZ hat sich sehr schön aufgeteilt:

Alle PLZ sind in einzelne Spal­ten ges­plit­tet

Ver­gle­ichen Sie jet­zt ein­mal den Inhalt der PLZ in der ersten Daten­zeile. Es ist dur­chaus denkbar, ja sog­ar wahrschein­lich, dass dort eine kleine Änderung stattge­fun­den hat. Als Tipp: Die Aus­rich­tung der Werte in der ersten PLZ-Spalte ist nun rechts.

Jet­zt sind es prinzip­iell nur noch zwei Schritte zum Ziel: Die ges­plit­teten PLZ müssen trans­formiert wer­den (Spal­ten zu Zeilen) und Bei BNR muss dann bei Bedarf die einzelne BNR so oft zeilen­weise wieder­holt wer­den, wie es zuge­hörige PLZ gibt. Und das geht mit Pow­er Piv­ot recht ein­fach:

• Sor­gen Sie dafür, dass die erste Spalte mit den PLZ markiert ist.
• Shift und ein Klick auf die let­zte Spalte, damit alle Spal­ten mit Aus­nahme der ersten markiert sind.
• Wech­seln Sie in das Menü Trans­formieren.
• In der Gruppe Beliebige Spalte Klick­en Sie auf Spal­ten ent­piv­otieren.

Aufruf zum ent­piv­otieren

Gle­ich danach stellt sich das Fen­ster so dar:

Ruck zuck stimmt der geforderte Auf­bau (fast)

• Danach erken­nen Sie, dass die zweite Spalte (Attrib­ut) nicht gebraucht wird, Klick­en Sie darum in die Über­schrift der Spalte, damit nur diese markiert ist.
• Recht­sklick in die Über­schrift und dann Ent­fer­nen wählen.

Es scheint so, dass damit das Ziel erre­icht ist. Aber es scheint nur so, denn das Zahlen­werk in der zweit­en Spalte macht ja einen richti­gen Zick-Zack-Kurs. Mal Zahl, mal Text, was ja an der Aus­rich­tung zu erken­nen ist. Und was eigentlich viel wichtiger ist: Diese Spalte soll ja immer die drei ersten Zif­fern ein­er 5‑stelligen Postleitzahl enthal­ten. Und die Zeile 1 fällt natür­lich auf, es sind nur 2 Zif­fern. Darum soll­ten alle Werte dieser Spalte als Text dargestellt wer­den, da sind führende Nullen kein Prob­lem.

Die zweite Spalte ist ja immer noch markiert und das Menü Trans­formieren ist gewiss auch noch aktiv. In der Gruppe Beliebige Spalte Klick­en Sie auf den Ein­trag Daten­typ: Beliebig  und dann den Ein­trag Text. Jet­zt ste­ht zwar alles schön links­bündig, aber es bleibt (wahrschein­lich) bei der 91 statt der erforder­lichen 091.

Es gibt mehrere Möglichkeit­en, zum Ziel zu gelan­gen. Ich ver­suche fast immer, den Weg des ger­ing­sten Wider­standes zu nehmen. 😉  Ganz rechts im Abfrage-Edi­tor sehen Sie den Bere­ich der Abfragee­in­stel­lun­gen. Und dort den Bere­ich Angewen­dete Schritte, wo alle einzel­nen Schritte als eine Art Hal­tepunkt doku­men­tiert sind. Klick­en Sie hier ein­mal auf die zweite Zeile Spalte nach Trennze­ichen teile… und Sie erken­nen im Dat­en-Fen­ster, dass zu der Zeit noch 091 in Zeile 1 ste­ht:

Let­zter Schritt mit Text-PLZ

Markieren Sie nun den nach­fol­gen­den Ein­trag Geän­dert­er Typ2 und Sie sehen, dass genau hier die Änderung vorgenom­men wor­den ist. Bequem, wie ich nun ein­mal bin, jet­zt ein­fach nur StrgA und alle Spal­ten wer­den markiert. Strg und ein Klick in die Über­schrift der ersten Spalte bewirkt, dass jet­zt nur noch die Spal­ten mit den PLZ markiert sind. Und jet­zt noch ein­mal den Daten­typ auf Text set­zen, wie eben schon gemacht. Die Nach­frage, ob Sie einen Schritt ein­fü­gen wollen, bestäti­gen Sie mit einem Klick auf  Ein­fü­gen.

Wenn Sie jet­zt auf den vor­let­zten Ein­trag Ent­fer­nte Spal­ten Klick­en wer­den Sie erken­nen, dass die PLZ alle dreis­tel­lig sind (übri­gens auch die am Ende der Intel­li­gen­ten Tabelle, in den Zeilen 535 und fol­gende). Der let­zte aufgeze­ich­nete Schritt der Typän­derung ist ja nun über­flüs­sig, der Daten­typ stimmt ja. Darum ein Recht­sklick auf Geän­dert­er Typ3 und Löschen.

Geschafft. Das Ziel ist erre­icht, zumin­d­est sind Sie auf der Ziellinie. Der Voll­ständigkeit hal­ber kön­nten Sie die bei­den Über­schriften noch anpassen. Entwed­er ein Dop­pelk­lick in das jew­eilige Feld oder ein Recht­sklick und Umbe­nen­nen… und Sie schreiben dort BNR und PLZ hinein. Jet­zt nur noch Menü Start und auf das erste Sym­bol Schließen & laden Klick­en. Automa­tisch wird ein neues Tabel­len­blatt mit ein­er grün lini­ierten Liste angelegt. Und die ist exakt so, wie gefordert.

▲ nach oben …

Vielle­icht fra­gen Sie sich, warum der ganze Aufwand. Wie schon zu Anfang erwäh­nt ist dieses wohl der beste Weg, wenn Sie keine Makros ein­set­zen kön­nen, dür­fen oder wollen. Die wahre Stärke aber liegt darin, dass Sie bei wiederkehren­den Datene­ingän­gen ein­fach nur die Ursprungs­dat­en (die Quell­dat­en) aktu­al­isieren (über­schreiben) und dann in (der jet­zi­gen) Tabelle2 die Tabel­len­tools (Menü) wählen, Aktu­al­isieren anklick­en und sich freuen. Das war’s näm­lich schon! Und soll­ten ein­mal andere oder weit­ere Kri­te­rien dazu kom­men, dass beispiel­sweise das Trennze­ichen ein anderes wird, dann ein­fach im recht­en Bere­ich des Fen­sters ein Dop­pelk­lick auf die große, grün­liche Schalt­fläche mit dem Tabel­len­na­men (es ist übri­gens der Name der Liste, nicht des Arbeits­blatts) und schon sind Sie wieder im Abfrage-Edi­tor und kön­nen gezielt Änderun­gen vornehmen.

Apro­pos „Weg des ger­ing­sten Wider­standes”: Bekan­ntlich macht ja nur Übung den Meis­ter, darum vol­lziehen Sie das Ganze noch ein­mal von Anfang an. Benen­nen Sie am besten die Liste der Quell­dat­en erst ein­mal um, damit keine Namen­skon­flik­te entste­hen. Wie so etwas ganz effek­tiv geht, kön­nen Sie beispiel­sweise hier nach­le­sen. Ich ver­wende den Lis­ten-Namen lst_Quelldaten. Sie arbeit­en sich dann bis zum zweit­en Teilen der Spalte vor:

Diese Zeile kann gelöscht wer­den

Bei den Abfragee­in­stel­lun­gen ein­fach den let­zten Schritt (Geän­dert­er Typ2) löschen. Das geht übri­gens auch mit Entf. Dann sind die Werte schon ein­mal vier­stel­lig. Anschließend ein Recht­sklick in die Über­schrift der ersten Spalte und diese Auswahl tre­f­fen:

Alle anderen Spal­ten wer­den ent­piv­otiert

Der Rest ist dann wieder wie gehabt. Sie sehen, dass der Recht­sklick an der richti­gen Stelle einge­set­zt, mitunter Vorteile bringt. Aber bekan­ntlich gilt auch immer wieder der Satz: „Wo viel Licht, da viel Schat­ten”. Sie haben jet­zt ja zwei Abfra­gen im Bere­ich Arbeitsmap­pen­abfra­gen. Und die obere zeigt sich mit einem kleinen Warn­schild statt des Tabel­len­sym­bols. Ein Dop­pelk­lick darauf und Sie bekom­men etwas mehr Infor­ma­tio­nen.

So ver­führerisch es auch sein mag, bei bzw. in den Abfragee­in­stel­lun­gen bleibt es beim einge­tra­ge­nen Namen, hier: Tabelle4. Nein, Sie müssen etwas tiefer in die Materie ein­tauchen. Im fol­gen­den Bild sehen Sie, dass ich in der Code-Zeile den bish­eri­gen Wert Tabelle4 markiert habe:

Der Bere­ich­sname der Quell­dat­en ist falsch!

Das über­schreiben Sie mit dem Namen, welchen Sie vorher der Liste mit den Quell­dat­en gegeben haben, also lst_Quelldaten. Ein­fach Eingabe und schon sind nach einem Klick auf Schließen & laden  bei­de Ergeb­nis-Tabellen gle­ich­w­er­tig. Natür­lich kön­nen Sie jede der bei­den Abfra­gen getren­nt nach Ihren Wün­schen ändern.

▲ nach oben …

Pivot-Auswertung

Zu Beginn dieses Beitrags wurde kurz ange­sprochen, dass eventuell eine Piv­ot-Auswer­tung angestrebt wird. Jet­zt, wo Sie das Ergeb­nis vor Augen haben wer­den Sie auch erken­nen, dass die ganze Umstruk­turierung der Dat­en gewiss ganz genau diesem Zweck gedi­ent hat.

Der Titel dieses Beitrages zeigt ja deut­lich auf, dass die Neuanord­nung der Dat­en die eigentliche Auf­gabe darstellt. Das Prob­lem ist gelöst. Um Ihnen einen kleinen Ein­blick in diese Fort­führung zu bieten, habe ich hier einige wenige Stich­worte (und wirk­lich nur diese) aufge­lis­tet. Zum The­ma Auf­bau und Erstel­lung ein­er Piv­ot­Table gibt es reich­lich Beiträge hier im Blog und natür­lich auch im Netz.

• Klick­en Sie in die eben erstellte Tabelle.
• Erstellen Sie daraus auf beliebige Weise eine Piv­ot­Table in einem neuen Arbeits­blatt.
• Ziehen Sie nacheinan­der die Felder BNR und PLZ in den Bere­ich Zeilen.
• Aktivieren Sie in den Piv­ot­Table-Tools den Menüpunkt Analysieren.
• Klick­en Sie im Bere­ich Fil­tern auf Daten­schnitt ein­fü­gen.
• Wählen Sie die PLZ als Daten­schnitt.
• Fil­tern Sie hier (mit­tels Strg) beispiel­sweise die Postleit­bere­iche 605 und 630:

Beispiel für eine Piv­ot-Auswer­tung

„Spie­len” Sie doch noch etwas mit den Dat­en herum, sei es in der Piv­ot-Auswer­tung oder fügen Sie auch gerne bei den Quell­dat­en eine weit­ere Spalte oder mehrere Zeilen dazu. Das stärkt Sie in der Hand­habung der fort­geschrit­te­nen Excel-Tech­niken.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 3,00 € freuen …