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.

(Dynamische) Formeln über mehrere Tabellenblätter

Bei dynamischen Dateien ist es wichtig, dass Formeln möglichst auf Veränderungen reagieren und diese ohne weiteren Aufwand durch den Benutzer stets die korrekten Daten anzeigen. Ein Unternehmen weist betriebsintern beispielsweise Zahlen für die jeweiligen Regionen und Unternehmenseinheiten aus. Unten ist ein Beispiel dargestellt, bei dem unterschiedliche Regionen ("Basel", "Bern" und "Zürich") vorhanden sind. Zudem werden die Daten auf Konzernebene aggregiert (Tabellenblatt "Total").

SheetIndexSheetsA

Die einzelnen Regionen weisen jeweils die gleiche Struktur auf, wobei auf dem Total-Tabellenblatt einfach auf die einzelnen Zellen Bezug genommen werden kann. Die Formel "=SUMME(Basel:Zürich!C8)" summiert die Zelle "C8" von sämtlichen Tabellenblättern (von "Basel" bis "Zürich"). Sollte nun eine weitere Region (beispielsweise "Genf") hinzugefügt werden, wird der Wert in Zelle "C8" zusätzlich berücksichtigt - sofern das Blatt "Genf" zwischen Basel und Zürich eingeordnet wird. Falls jedoch "Aargau" dazukommen sollte und an der alphabetischen Reihenfolge festgehalten wird, müsste die SUMME-Formel entsprechend angepasst werden. Eine weitere Möglichkeit ist jedoch die nachfolgende: Um die SUMME-Formel keineswegs anpassen zu müssen, fügen wir links und rechts der Regionen weitere leere Tabellenblätter ein ("Kanton_AA" und "Kanton_ZZ") und blenden diese aus. Unsere SUMME-Formel sollte sich nun über diese Hilfsblätter erstrecken:

=SUMME(Kanton_AA:Kanton_ZZ!C8)

SheetIndexSheetsZ

Da die Hilfsblätter leer sind, wird zwar "C8" ebenfalls aggregiert, aber dies entspricht jeweils dem Wert 0 und hat deshalb keinen Einfluss auf das Gesamttotal. Mit dieser Herangehensweise werden die unterschiedlichen Szenarien bereits von Anfang an aufgefangen und die Daten in "Total" basieren jederzeit auf allen Regionen.

Fehler ignorieren: Formel "AGGREGAT"

Viele Auswertungen laufen über grosse Datenmengen. Diese stammen nicht nur direkt aus einer Datenquelle, sondern werden teilweise um weitere Formeln und Informationen angereichert. Aufgrund eingebetteter Formeln kann es zu Fehlermeldungen kommen. Als Beispiel: Mittels der Formel "SVERWEIS" wird die Tabelle um weitere Daten aus einer zweiten Liste ergänzt. Falls zum Suchkriterium kein entsprechender Wert gefunden werden kann, wird der Fehlerwert "#NV" ausgegeben. Die nachfolgende Abbildung stellt dies beispielhaft dar:

AGGREGAT_Fehler1

Zum Kunde "30004" (Zeile 5) konnten keine Umsatz- und Absatzwerte zugewiesen werden. Möchte man diese Spalten mit einer herkömmlichen "SUMME"-Formel auswerten, wird das Resultat ebenfalls "#NV" sein. Eine Möglichkeit, um dies zu umgehen, wäre eine "WENNFEHLER"-Formel einzubauen, wie dies im Beitrag (Suchmaschine programmieren) erläutert wurde. In diesem Beitrag stelle ich jedoch eine direktere Variante einer Formel vor, mit welcher Fehler ignoriert werden können. Die Formel lautet "AGGREGAT". Ähnlich wie bei der Formel "TEILERGEBNIS" wird zuerst die "Funktion" verlangt. Kurzum: Der Benutzer wählt, ob die Formel die "Summe", den "Mittelwert" oder eine andere statistische Funktion ausführen soll.

AGGREGAT_Funktion

Daraufhin sind die "Optionen" zu wählen. Um Fehlerwerte zu ignorieren, wählst Du die 6. Funktion "6 - Fehlerwerte ignorieren", gefolgt von dem Bereich, den es zu summieren gilt.

AGGREGAT_Optionen

Diese Formel funktioniert auf sämtlichen Fehlermeldungen, die es in Microsoft Excel gibt.