Ich weiß, ich weiß, das ist jetzt ein paar Tage sehr RegEx-lastig. Excel bietet aber nun einmal ein paar Funktionen dazu an (neuerdings).
Heute ist nun das Extrahieren dran. Ein einfaches Beispiel: ich habe eine Liste von irgendwelchen Zeichenkombinationen, und möchte zum Beispiel die ganz links stehende Zahl haben (falls eine da ist). Mit herkömmlichen Funktionen lösbar – aber müüüüühsam.
=REGEXEXTRAHIEREN(A2;“[0-9]*“;0)
… und schon sind wir fertig. Die neue Funktion REGEXEXTRAHIEREN erwartet eine Zeichenkette, eine Regular Expression, und dann wird – mit 0 als drittem Parameter – extrahiert.
Probieren Sie’s aus. Vielleicht auch, um Vorwahlen oder Artikelgruppen herauszukratzen?
Und wenn Sie das geschafft haben: was passiert mit 1 und 2 als drittem Parameter? Hausnummern, … jetzt wird es richtig interessant!
So, Schluss mit RegEx. Morgen kommt wieder was anderes.
Ersetzen ist was Feines – man braucht was nicht, und macht einfach was anderes draus. Neben den Funktionen WECHSELN und ERSETZEN, die Excel dafür zur Verfügung stellt, gibt es auch – ganz fortgeschritten – die Variante, Regular Expressions zu verwenden: REGEXERSETZEN.
=REGEXERSETZEN(A2;“[^0-9]“;““)
ersetzt zum Beispiel alles, was keine Ziffer ist, mit „nichts“ – entfernt also alle Nicht-Ziffern aus der gegebenen Zeichenkette. Damit kommt sowas heraus:
Ich kann das natürlich auch verwenden, um mir nicht nur ein X für ein U vormachen zu lassen, sondern zum Beispiel jeden Vokal, der kein U ist – und das ohne verschachtelte Funktionen. Wie? Probieren Sie’s!
Also, ich MAG diese Funktion – und habe sie inzwischen schon oft brauchen können. Vor allem, um Benutzereingaben zu bereinigen oder zu standardisieren.
Wir haben ein neues Problem, eines wie dieses hier:
Wie soll ich mir aus so etwas zum Beispiel eine Liste aller Gruppen machen, in denen AUT, GER, NOR oder Kombinationen daraus vorkommen?
Ha – schon wieder kommt mir da die RegEx in den Sinn. Da gibt’s doch die Möglichkeit, auf bestimmte Vorkommen zu testen – hier muss ich nur AUT|GER||NOR als RegEx verwenden, und die Textwurst aus meinen Ländern auf Vorkommen prüfen.
Das ist auch schon der Lösungsansatz 🙂 Setzen wir’s um:
liefert mir diese – ich hab da in TEXTVERKETTEN immer ein Leerzeichen dazwischen gestellt, damit sich nicht zufällig Länderkürzel ergeben (z.B. steckt in ISLATA auch LAT drin, aber das will ich nicht, sondern ISL und ATA sind die beiden Länder).
Meine Wunschländer, die ich prüfen möchte, stecke ich nun in eine Tabelle – damit kann ich bei Bedarf auch zwei oder sieben prüfen:
Ich verwende noch ein TEXTVERKETTEN, um meine Auswahlländer mit einem | dazwischen aneinanderzuhängen, prüfe die Textwurst mit der sich daraus ergebenden RegEx AUT|GER|NOR – fertig!
Vielleicht arbeite ich dann wieder mil FILTERN, so wie gestern, … da fällt Ihnen sicher auch was dazu ein.
Na, was soll ich sagen? Die beiden Funktionen mögen einander – warum? Ich kann mit REGEXTESTEN alle Zeilen herausfinden, die zu meiner RegEx passen, und dann mit FILTER nur die passenden Einträge anzeigen.
So zum Beispiel:
Das klingt doch brauchbar.
Vor allem, wenn Sie fließend RegEx „sprechen“, und bestimmte Muster überprüfen wollen – da werden Sie nun dynamisch wie noch nie, wenn Sie – wie ich hier – die RegEx einfach in eine Zelle eingeben, und dort herumprobieren, ganz nach Lust und Laune.
Das führt uns jetzt zu weit – heute wende ich mich explizit an die Spezialist:innen, die damit was anfangen können – alle anderen: bitte hier entlang https://de.wikipedia.org/wiki/Regul%C3%A4rer_Ausdruck für Details, und dann hier https://regexe.de/ für erste Selbstversuche. Vielleicht ganz kurz noch als Appetizer: damit kann man Muster definieren, die eine Zeichenkette aufweisen soll, oder eben nicht, und dann Texte prüfen, ob sie zu diesem Muster passen. Anwendung also: „Ist das eine gültige Mailadresse?“, „Ist das eine brauchbare Telefonnummer“, oder auch, und das zeig ich heute her: „Ist dieser Nachname eine Variation von SCHMIED?“
Excel kann das nämlich neuerdings. Geduld, Geduld. Wir brauchen nämlich zuerst die RegEx (gängige Abkürzung für Regular Expression, und man macht super Eindruck, wenn man das einfach in ein Gespräch wirft) für diese Namensmuster. Ich hab vorgearbeitet, und folgendes ermittelt:
\bschm[iy]e?([dt]+)\b
Das bedeutet:
wir beginnen mit schm
dann kommt zwingend i oder y
nun optional ein e
zu guter Letzt beliebig viele und mindestens ein d und t
hier ist das Wort zu Ende
Das ist auch die eigentliche Arbeit. In Excel ist nicht mehr viel zu tun. Die RegEx in eine Zelle schreiben, zum Beispiel, und darauf nun in einer Funktion referenzieren:
=REGEXTESTEN([@Nachname];$F$1;1)
Die Funktion REGEXTESTEN erwartet sich: was wird getestet, welche RegEx, und zu guter Letzt: ist Groß- und Kleinschreibung wichtig, oder nicht – wie üblich 1 für JA, 0 für neine.
Im Bild sieht man schön: das klappt. Das klappt gut. Das klappt viel viel besser, als das mit Funktionen irgendwie hinzubasteln, mit tausend geschachtelten WENN.
Weil es mir ein Anliegen ist, das zu teilen: Sie kennen das sicher: da macht man ein schönes Diagramm, überlegt sich, wo man es hinstellt, richtet vielleicht gar ein nettes Dashboard her, mit weiteren Diagrammen und Pivottabellen, dann ändert sich was an den Daten, und zack! Alles hässlich 😦 😦 😦
Warum?
Weil Diagramme (und alle anderen Objekte, die man in Excel so herumliegen lassen kann) „von Natur aus“ abhängig von der Zellposition und -größe sind. Also: WO sie sind, entscheidet ihre linke obere Ecke, die sich an die Zelle klebt, von der aus sie erstellt wurden, und WIE GROß sie sind, hängt von der Größe ebendieser Zelle ab.
Beispiel: Hier ein Diagramm:
Es „gehört“ zu Zelle E1, weiß aufgrund dieser Zelle, wo es ist, und wie groß es ist. Zum besseren Verständnis verändere ich nun die Größe von E1, und auch die Position, indem ich die Spalte A breiter mache. Das Ergebnis:
Gleiche relative Position zu E1, andere Höhe, andere Position auf dem Blatt insgesamt (weiter rechts, weil A mehr Platz einnimmt).
Na ja, Sie kennen das wahrscheinlich, wenn Sie mir bis hierher gefolgt sind. Nun kommt die Gretchenfrage: MUSS das so sein? Und die Hexenantwort: natürlich nicht 🙂 Sie können Folgendes tun:
Wenn der Aufgabenbereich geöffnet ist, und Sie das Diagramm anklicken, dann sehen Sie sofort die Optionen in Diagrammbereich formatieren. Sonst: Doppelklick auf das Diagramm (als ein Weg dorthin), und Sie sind da, wo ich Sie haben möchte.
In diesem Aufgabenbereich brauchen Sie jetzt das dritte Icon (das mit den vielen Pfeilen), und die Details zu den Eigenschaften. Und da werden Sie fündig: Neben der Standardoption Von Zellposition und -größe abhängig finden Sie hier nämlich auch noch Nur von Zellposition abhängig Von Zellposition und -größe unabhängig
Hier können Sie also das Standardverhalten unterdrücken, und entweder das Diagramm mit der Zelle „mitschwimmen“ lassen, ohne, dass es auch seine Größe verändert, wenn jemand an der Zellgröße schraubt (das tun ja Pivottabellen beispielsweise besonders gern); oder Sie entscheiden sich für völlige Unabhängigkeit, und Ihr Objekt bleibt dann da liegen (in absoluter Position vom Blattrand), wo Sie es hintun, und die Größe bleibt auch so, wie Sie das einstellen.
Probieren Sie’s aus. Ihr Dashboard sieht nun nachhaltig besser aus 🙂
Wir haben in den letzten Tagen die ERWEITERN Funktion kennengelernt, die eine Matrix um neue Zeilen und Spalten ergänzt – und das leider mit #NV auffüllt. Das mag ja manchmal sinnvoll sein – aber eben nicht immer.
=ERWEITERN($A1#;9;12;0)
Daher bietet die ERWEITERN-Funktion an, noch ein letztes Argument zu übergeben: das „Pad“ (komisches Wort, aber ich jammere ja häufig über die Wortfindungsstörungen der Microsoft-Übersetzer:innen). Damit werden nun etwaige neue Bereiche gefüllt, hier habe ich 0 dafür verwendet, und nun sieht das richtig brauchbar aus.
(Statt 0 darf es natürlich auch ein beliebiger anderer Ausdruck sein, der einen Wert zurückgibt – eine Zufallszahl zum Beispiel, oder eine andere Berechnung. Dieser Wert wird dann überall verwendet.
Gestern haben wir #NV an einen Bereich angeklebt, um mehr Zeilen zu bekommen. Heute ergänzen wir auch noch Spalten – ein optionales Argument der ERWEITERN-Funktion.
=ERWEITERN(A1#;10;10)
Hier habe ich als drittes Argument die Anzahl der gewünschten Spalten angegeben.
(Und als Eingabematrix einen der schönen neuen Bezüge verwendet – der Ausgangsbereich ist hier nämlich dynamisch mit SEQUENZ generiert, und ich verweise mit A1# einfach auf das Bereichsergebnis der Funktion in A1.)
Wenn Sie irgendeine Matrix haben, und eigentlich mehr brauchen, dann hilft Ihnen die ERWEITERN-Funktion dabei, die richtige Größe zu erreichen.
Sagen wir einmal, Sie sind (wie ich) aus Österreich, 9 Bundesländer, und Ihre Folgeauswertung erwartet 9 Zeilen – Sie haben aber nur 5 bekommen. (Ich weiß, das Beispiel ist ein wenig an den Haaren herbeigezogen, aber egal – es geht uns einmal ums Grundverständnis. Und: ich bin schon laaaaaang in der IT, und derlei Anforderungen sind mir durchaus schon untergekommen.)
=ERWEITERN(A1:H5;9)
ERWEITERN(Bereich;Zeilenanzahl) schnappt sich also einen Bereich, und hängt noch so viele Zeilen dran, die mit #NV gefüllt sind, bis die gewünschte Zeilenanzahl erreicht ist.
(Geben Sie weniger Zeilen an als der ursprüngliche Bereich aufweist, dann bekommen Sie einen #WERT! Fehler. Es wird also NICHTS unterschlagen.)
Gestern war SEQUENZ-Tag. Heute zeig ich Ihnen, wie das aussehen kann, wenn die Argumente nicht als Literale eingegeben werden, sondern aus Zellen kommen – hier das Beispiel mit drei relativen Zellbezügen und einer benannten Zelle als Parameter:
Sieht genauso aus wie gestern, aber nun können Sie damit „herumspielen“, und die Werte in den Eingabezellen verändern. Da sieht man sehr schön, was mit „dynamisch“ gemeint ist.
=SEQUENZ(C2;D2;A2;par_Schritt)
Hier zum Beispiel hab ich die Schrittgröße abhängig vom Anfangswert gemacht, nur so zum Ausprobieren, und andere Zeilen- und Spaltenanzahl gewählt. An der Formel in A5 hingegen ist nichts verändert:
Der Nutzen?
Abgesehen von der klassischen „Malreihe“, die ich mir so aufzeichnen kann, kann man damit sehr viel anfangen, wenn es beispielsweise um die Einteilung von Schichtbetrieb geht, von wiederkehrenden Frei-Tagen, vom Generieren von Abfahrtszeiten der Fähre, Erzeugen von Artikelnummern, …
Wenn Sie die SEQUENZ verinnerlicht haben, wird Ihnen die Anwendung mit Sicherheit im Alltag begegnen 😉
katharinakanns
Soprani Software 