Eigene Funktionen erstellen (Meter in Meilen & Celsius in Fahrenheit)

Eine Vielzahl an Funktionen und Formeln sind bereits standardmässig in Excel verfügbar. Dem Standarduser genügt dieses Angebot grundsätzlich, doch können auch da eigene Funktionen den Umgang mit Excel erleichtern. Dieser Blog zeigt auf, wie benutzerdefinierte Funktionen und Formeln angelegt werden können, die danach jederzeit verfügbar sind. Als Beispiel werden wir hier Meter in Meilen sowie Grad Celsius in Fahrenheit umwandeln.

Meter in Meilen

Die erste Funktion, die angelegt werden soll, ist die Umwandlung von Meter in Meilen (eine englische Meile beträgt 1'609.344 Meter). Öffne hierzu eine neue Arbeitsmappe und gehe in die VBA-Umgebung (Alt + F11 oder Rechtsklick auf ein Tabellenblatt → "Code anzeigen"). Auf der linken Seite befindet sich die Projekt-Übersicht - dort sind alle geöffneten Excel-Dateien zu sehen. Mittels Rechtsklick auf die neue Arbeitsmappe lässt sich ein Modul einfügen, wo die Funktion eingepflegt werden kann:

NeuesModulEinfuegen

Im angelegten Modul1 wird nun die Funktion eingetragen. Für die Umrechnung von Meter in Meilen gilt folgender Code:

Public Function MeterInMeilen(ByVal X As Double)

MeterInMeilen = X / 1609.344

End Function

Die Formel nennen wir "MeterInMeilen". Achte bei der Vergabe der Namen darauf, dass diese nicht bereits vorhanden sind. In unserer Excel-Datei kann alsdann mit der Umwandlung begonnen werden. Die Funktion findet sich unter sämtlichen anderen - gebe in Excel einfach "=M" ein und die Vorschläge erscheinen in einer Auswahlliste, dort ist auch "MeterInMeilen" aufgeführt:

EigeneFunktionenAuswahl

Um die Umwandlung der Massangaben zu veranschaulichen, habe ich eine kleine Tabelle angelegt:

MeterInMeilen

In Zelle "B3" sind 1000 Meter eingetragen, dieser Wert kann selbst gewählt werden. In Zelle "C3" kommt unsere benutzerdefinierte Funktion zum Einsatz. Füge da nun die folgende Formel ein:

=MeterInMeilen(B3)

Ein simpler Bezug auf die Zelle "B3" reicht bereits aus, um die Meterangabe in Meilen zu konvertieren. Uns interessiert zudem, wie wir die Rückkonvertierung vornehmen können. Dazu wird folgender Programmcode eingetragen, um die Funktion "MeilenInMeter" anzulegen:

Public Function MeilenInMeter(ByVal X As Double)

MeilenInMeter = X * 1609.344

End Function

Diese Zeilen können direkt unterhalb von "End Function" der ersten Funktion in Modul1 eingetragen werden. In der Excel-Beispieltabelle kann für die Meter-Rückkonvertierung die neue Formel eingegeben werden:

=MeilenInMeter(C3)

Das Resultat davon ist wiederum exakt 1'000 Meter.

Celsius in Fahrenheit

Analog der Konvertierung oben soll nun zudem Grad Celsius in Fahrenheit umgewandelt werden können. Folgender Code erfüllt diese Anforderung:

Public Function CelsiusInFahrenheit(ByVal X As Double)

CelsiusInFahrenheit= X * 9 / 5 + 32

End Function

Wiederum als Beispieltabelle in Excel wird in Spalte C die Berechnung von diversen Temperaturangaben vorgenommen.

CelsiusInFahrenheit

Die angegebenen Werte sind korrekt, man könnte zudem die Dezimalstellen einblenden (beispielsweise 22 Grad Celsius entsprechen 71.6 Grad Fahrenheit). Die angelegte Funktion kann damit ebenfalls umgehen, da der Variablentyp "Double" gewählt wurde.

Auf die Rückkonvertierung wird hier nicht eingegangen, wir lassen dies als mögliche "Übungsaufgabe" offen. Bei Fragen kann stets die Kommentar-Sektion verwendet werden.

Zuletzt ist anzumerken, dass neben komplett neuen Funktionen wie den oben gezeigten Umwandlungs-Formeln auch bestehende Standard-Formeln individuell optimiert werden könnten. Ein Beispiel ist der SVERWEIS, bei dem Benutzer je nach dem immer wieder Probleme bekunden - bei dem Verweis gibt es diverse Fehlerursachen. Mit einer eigenen Funktion (z.B. analog der INDEX-VERGLEICH-Kombination) kann der SVERWEIS benutzerfreundlicher gestaltet werden und zugleich auch Daten links von dem Suchkriterium ausgeben.

Textfragmente aus einer Zelle ziehen

Von einer dritten Person erhaltene Daten können oftmals nicht gleich in dieser Form verwendet und weiterverarbeitet werden – die Excel-Tabellen müssen erst noch angepasst werden. Mögliche Beispiele sind: Artikelinformationen von Lieferanten, Kassendaten, Personalien, Interpret und Songtitel, etc.

Die Funktion “Text in Spalten“

Wenn die einzelnen Einträge durch ein Sonderzeichen wie beispielsweise ein Minus (-), ein Semikolon (;) oder einen Doppelpunkt (:) getrennt sind, kann man die Zelle mittels der Funktion “Text in Spalten“ aufsplitten. Du findest diese unter dem Reiter “Daten“. Nachfolgend siehst Du ein Beispiel von möglichen durch Minuszeichen getrennten Artikeldaten.

Rohdaten_Textfragmente

Nachdem die Zellen A1 bis A4 markiert wurden, kannst du auf den Reiter “Daten“ und auf “Text in Spalten“ klicken. Die Daten sind “Getrennt“ durch ein bestimmtes Zeichen (-). Eine feste Breite liegt möglicherweise nur bei der Artikelnummer vor, welche im Normallfall stets gleich lang ist – dieses Fragment könnte dadurch vom Rest getrennt werden.

Text in Spalten1

Im nächsten Bild ist das Trennzeichen anzugeben. Es sind bereits einige Zeichen aufgeführt. Falls – wie im Falle des Minuszeichens – das Vorhandene nicht gegeben ist, kannst Du es bei “Andere“ eingeben. Den Haken bei “Tabstopp“ kann man sogar ignorieren. Sobald das Zeichen gesetzt oder ausgewählt worden ist, wird unten die Vorschau der Aufsplittung auf verschiedene Spalten angezeigt. Mit einem Klick auf “Fertig stellen“ hast Du Deine Daten auf einzelne Spalten aufgeteilt.

Text in Spalten2

Formeln LINKS und SUCHEN

Es kann auch sein, dass entsprechende Werte nicht mit der Text in Spalten-Funktion sondern via Excel-Formeln ermittelt werden müssen. Ich möchte Dir hier eine Kombination von zwei Formeln zeigen, womit man Textfragmente aus einer Zelle ziehen kann: Die Formel LINKS und SUCHEN.

Mit der Formel LINKS werden aus einer Zelle X Zeichen von links rausgeholt. Die Artikelnummer aus obigem Beispiel (Zelle A2) kann also mit folgender Formel herausgezogen werden:

=LINKS(A2;7)

Sind nun jedoch die Angaben nicht immer gleich lang, muss zuerst nach einem bestimmten Zeichen gesucht werden. In den nachfolgenden Daten liegt das Minuszeichen nämlich stets an einer anderen Stelle.

SUCHEN-LINKS1

Für diesen Zweck verwendet man die Formel “SUCHEN“. Man kann damit die Stelle eines bestimmten Zeichens oder Buchstabens ermitteln. Die Formel für das erste Beispiel lautet demzufolge:

=SUCHEN("-";A2)

Da es sich beim Trennzeichen um Text handelt, ist dieser in Anführungs- und Schlusszeichen (“ “) zu packen.

Kombinieren wir nun die beiden Formeln miteinander, können wir die Namen der Berge aus dem zweiten Beispiel herausholen. Wichtig zu beachten ist, dass die durch SUCHEN ermittelten Anzahl Stellen minus 1 gerechnet werden müssen, damit das Trennzeichen nicht auch herausgeholt wird. Die fertige Formel lautet demzufolge:

=LINKS(A2;SUCHEN("-";A2)-1)

Das Endergebnis präsentiert sich wie folgt:

SUCHEN-LINKS2

Was sind weitere Möglichkeiten?

Analog von LINKS gibt es auch die Formel RECHTS. Die Formel TEIL hingegen nimmt ein Fragment ab einer beliebigen Stelle für X Zeichen – so kannst Du aus der Mitte einer Zelle Textfragmente entnehmen. Eine Kombination dieser Formeln erlaubt es Dir, auch komplexere und unterschiedlich lange Texte aus einer Zelle mit mehreren Trennzeichen zu ziehen.

Falls Daten in einer Textdatei übermittelt werden, können diese via Register "Daten" und "Aus Text" importiert werden. Dieser Ablauf ist identisch mit der Funktion "Text in Spalten" - es erscheint auch der Textkonvertierungs-Assistent, bei dem das Trennzeichen angegeben werden kann.

Wie Du hingegen Daten aus auseinanderliegenden Zellen zusammenfügen kannst, findest Du in diesem Beitrag: Inhalte aus Zellen miteinander verketten