Vorletzte Woche habe ich ein kleines Excel Rätsel für Euch erstellt. Diejenigen welche das Rätsel angepackt haben können nun Ihre Antwort mit meiner Lösung vergleichen.

Rätsel Lösung und Erklärung

Das notwendige Wissen um dieses Rätsel richtig lösen zu können beruht natürlich darin verschieden Text Formeln zu kennen. Die wichtigsten Text Formeln könnt Ihr übrigens in diesem Beitrag nachlesen. Sämtliche Formeln die ich hier eingesetzt habe werden dort genauer erläutert.

Zur Erinnerung hier nochmals ein Screenshot der Daten welche in einer Daten Tabelle enthalten sind:

Ihr könnt Euch die Lösungen natürlich auch in Excel anschauen:

Formel der Mitarbeiter Spalte:

=WENNFEHLER(GROSS(GLÄTTEN(TEIL([@Beschreibung];FINDEN(“@”;[@Beschreibung])+1;3)));”kein Eintrag”)

Damit wir die Formel verstehen fangen wir in der Mitte, bei der ersten Auswertung die Excel vornimmt, an. Wie finden wir diese Stelle? Mit dem Formelauswertungs Werkzeug! Dort wo das Formelauswertungs Werkzeug zuerst anfängt zu berechnen ist “das Herz” der Formel. An der Stelle setzen wir an!

Das Herzstück dieser Formel ist die FINDEN()  Funktion. Mit FINDEN(“@”;[@Beschreibung]) stellen wir fest an welcher Stelle im Text das @ Zeichen sich befindet. In unserem Rätsel wurde das @ Zeichen als Code benutzt um das Mitarbeiterkürzel zu erfassen. Finden liefert uns im Beispiel auf der ersten Zeile die Zahl 23, welches bedeutet dass das @ Symbol das 23. Zeichen im Text ist.

In dem wir uns die Daten anschauen, erkennen wir dass die Namens Kürzel der Mitarbeiter maximal drei Buchstaben enthalten nach dem @ Zeichen. Somit genügt es uns wenn wir jeweils drei Stellen nach den @ Zeichen zurückgeben. Die TEIL() Funktion ist dafür bestens geeignet!

TEIL([@Beschreibung];FINDEN(“@”;[@Beschreibung])+1;3)

Zum besseren Verständnis werde ich die bereits erklärte Funktion FINDEN in grau und bereits ausgewertet anzeigen.

TEIL([@Beschreibung];23+1;3)

Nun sehen wir dass die TEIL Funktion ab dem 24. Zeichen im Text 3 Zeichen wiedergeben wird. Wenn wir die Funktion auswerten bekommen wir folgendes Resultat: “hjm”

Nun bleiben noch die Formeln GLÄTTEN(), GROSS(), und WENNFEHLER() zum erklären.

=WENNFEHLER(GROSS(GLÄTTEN(“hjm”));”kein Eintrag”)

Weil die Anleitung des Rätsels ausdrücklich erwähnte dass alle Auswertungen immer gleich sein sollen, löschen wir führende oder nachfolgende Leerzeichen mit der Formel GLÄTTEN(). Im Beispiel in der ersten Zeile haben wir keine Leerzeichen, weshalb uns in diesem spezifischen Beispiel, die Formel keine Veränderung herbeiführt.

GROSS verwandelt alle Buchstaben in der Zeichen Folge in Großbuchstaben. Dies ist nun auch unser Endresultat: HJM

Die WENNFEHLER() Formel wird zuletzt um die ganze Formel herumgepackt damit, im Falle das gar kein Text vorhanden ist, wir eine vernünftige Fehlermeldung bekommen (“kein Eintrag”) anstelle von #WERT!

Formel der Kategorie Spalte:

Der Trick um die Kategorie Spalte zu bewältigen ist dass man einen Weg finden muss um die Zeichenstelle der beiden # ausfindig zu machen, so dass man im Anschluss das dazwischenliegende zurückgeben kann. Die TEIL() und FINDEN() Formeln sind hier am hilfreichsten!

Die ganze Lösungsformel sieht so aus:

=WENNFEHLER(GROSS2(GLÄTTEN(TEIL([@Beschreibung];FINDEN(“#”;[@Beschreibung])+1;FINDEN(“#”;[@Beschreibung];FINDEN(“#”;[@Beschreibung])+1)-FINDEN(“#”;[@Beschreibung])-1)));”kein Eintrag”)

Die drei äußeren Formeln, GLÄTTEN, GROSS, und WENNFEHLER sind die selben wie bei der Mitarbeiter Spalte und dienen auch dem selben Zweck, weswegen ich sie hier nicht nochmals weiter behandeln werde. Schneiden wir diese Formeln also raus und betrachten nur das Übriggebliebene.

=TEIL([@Beschreibung];FINDEN(“#”;[@Beschreibung])+1;FINDEN(“#”;[@Beschreibung];FINDEN(“#”;[@Beschreibung])+1)-FINDEN(“#”;[@Beschreibung])-1)

Das schwierige an dieser Formel ist dass wir zwei stellen im Text ermitteln müssen. Das erste Doppelkreuz (# Zeichen) und das zweite. Nur so können wir mit der TEIL() Funktion das dazwischenliegende zurückgeben!

Als erstes fängt Excel an die erste FINDEN Formel auszuwerten (in rot):

=TEIL([@Beschreibung];FINDEN(“#”;[@Beschreibung])+1;FINDEN(“#”;[@Beschreibung];FINDEN(“#”;[@Beschreibung])+1)-FINDEN(“#”;[@Beschreibung])-1)

So ermitteln wir die erste Stelle des # Zeichen.

Damit wir aber die zweite Instanz des Zeichens finden können, müssen wir eine verschachtelte FINDEN Funktion aufbauen. Die Formel soll nächste # Zeichen erst ab der Stelle suchen an dem das erste # Zeichen steht, sonst bekommen wir zweimal den selben Wert. Hier nun die verschachtelte FINDEN() Funktion.

Weil dies in Worten zu erklären schwierig ist…. hier ein kleines Diagramm:

Die letzte Finden Funktion ist lediglich dazu da die Differenz zwischen dem ersten und letzten # zu ermitteln für die TEIL() Funktion. Das in Grau geschriebene liefert die Stelle des zweiten Doppelkreuzes. Also müssen noch ermitteln wo das erste Zeichen steht damit wir die Differenz der Anzahl Zeichen erhalten.

FINDEN(“#”;[@Beschreibung];FINDEN(“#”;[@Beschreibung])+1)-FINDEN(“#”;[@Beschreibung])

Das Resultat ist nun “Cont” in der ersten Spalte.

Anmerkung

Das komplizierte an diesen Art Formeln ist dass wir Excel sehr oft bitten müssen das selbe mehrmals auszuwerten. Im konkreten Fall die erste Position des Doppelkreuzes. In der VBA Programmierung zum Beispiel können wir die erste Position einmal auswerten und dann anhand einer Variabel speichern. Dies können wir in einer Formel nicht. Würden wir jedoch Hilfsspalten zulassen, könnten wir diesen Variabel Speicher so zu sagen nachahmen, was die Formel sehr viel übersichtlicher machen würde.

Ich hoffe Ihr hattet Spaß bei der kleinen Übung!