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.

Die Formel "SUCHEN" - nach Fragezeichen/Multiplikationszeichen suchen

Die Formel "=SUCHEN" ermöglicht die Suche nach einem bestimmten Zeichen oder einer Zeichenfolge innerhalb eines anderen Textes/einer anderen Zelle. Kombiniert mit beispielsweise der Formel "=TEIL" können so clever Textfragmente aus einer Zelle gezogen werden. Bei Platzhalter wie dem Fragezeichen und dem Multiplikationszeichen (*) funktioniert dies jedoch nicht auf anhin. Hier ein Beispiel mit der Formel "=SUCHEN("*";A1)":

suchenachplatzhalter1

Da es sich beim "*" wie erwähnt um einen Platzhalter handelt, wird ein beliebiges Zeichen bereits an erster Stelle im Text in Zelle "A1" gefunden - das Resultat der Formel ist demnach "1". Die Formel muss deshalb leicht angepasst werden: Es benötigt vor dem Platzhalter-Zeichen eine Tilde ("~"), um zu definieren, dass nach exakt diesem Zeichen gesucht werden soll. Die korrekte Formel lautet demzufolge:

=SUCHEN("~*";A1)

Das Resultat sieht nun wie folgt aus (das Zeichen steht an 11. Stelle im Text in Zelle A1):

suchenachplatzhalter2

Dieses Vorgehen deckt sich auch mit der herkömmlichen Suchfunktion in den Office-Programmen ("CTRL + F"), was im Beitrag "Suchen & Ersetzen eines Multiplikationszeichens/Platzhalters (*)" genauer aufgezeigt wird.

Dateipfad mit einer Formel darstellen

Den Pfad einer Datei darzustellen, kennen die meisten womöglich mittels der Kopf- und Fusszeilen-Funktion. Ich zeige Dir in diesem Beitrag auf, wie Du dies auch mittels einer Formel machen kannst, damit Du den Pfad der aktuellen Datei in einer beliebigen Zelle in Excel ausgeben kannst.

Kopf- und Fusszeile

Bekannter ist die Vorgehensweise, den aktuellen Dateipfad in die Kopf- oder Fusszeile zu integrieren. Dazu gibt es eine vorgefertigte Auswahlmöglichkeit in den Kopf- und Fusszeilentools, die folgendes Resultat liefert:

dateipfadkopfzeile

Dies bedeutet jedoch, dass Du den Pfad immer an einer bestimmten Stelle (eben zu oberst oder zu unterst im Dokument) angezeigt haben wirst. Im nächsten Abschnitt findest Du, mithilfe welcher Formel Du diese Information in jeder beliebigen Zelle hinterlegen kannst!

Die Formel =ZELLE

Wie die Überschrift bereits andeutet, bietet die Formel "=ZELLE" die entsprechende Möglichkeit. Wendet man die Formel an, kommt eine Auswahlmöglichkeit:

dateipfadzelle

Wähle hier "dateiname" und schliesse die Formel ab. Vollständig in einer beliebigen Zelle steht also:

=ZELLE("dateiname")

Und das Resultat ist der Pfad (inklusive Dateiname/Tabellenblatt). Beachte: Es wird nur etwas angezeigt, wenn Deine Excel-Datei auch einen Speicherort hat. Öffnest Du einfach die Applikation und versuchst dies aus (Mappe1), wird es nicht funktionieren. Speichere die Datei ab, aktualisiere die Formel und Du erhältst den Output (bspw.: "C:\Users\Roman\Desktop\[MeineDatei.xlsx]Tabelle1". Möchtest Du nun NUR den Dateipfad angezeigt bekommen, braucht es einige Ergänzungen. Die fertige Formel lautet:

=LINKS(ZELLE("dateiname");SUCHEN("[";ZELLE("dateiname"))-2)

Dies ergibt: "C:\Users\Roman\Desktop". Wir suchen also einerseits nach dem Zeichen "[" - dieses steht für den Dateinamen. Vom gesamten Konstrukt nehmen wir alle Zeichen von links bis zu dieser eckigen Klammer (abzüglich 2: Eben die Klammer und den letzten Backslash "\").

P.S.: Hängst Du hinter "dateiname" noch einen Bezug in eine andere Datei/Zelle, dann wird Dir davon der Pfad/Dateiname angezeigt!