Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

Excel: Eigene Funktionen mit VBA erstellen

Funktionen, die mehrere Werte zurückgeben

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Matrixfunktionen tauchen in Excel zwar nur selten auf, dennoch kann man sie programmieren. Dazu werden allerdings die Technik der Datenfelder (Arrays) und einige andere Kleinigkeiten benötigt, die Sie beachten müssen.
12:01

Transkript

Zugegeben Matrixfunktionen sind relativ selten. Sie tauchen selten in Excel auf und auch versierte Excel-Profis kennen Sie häufig nicht. Matrixfunktion bedeutet, eine Funktion liefert nicht einen Wert, sondern mehrere Werte. Es gibt einige, zugegeben sehr wenige, Excel-Funktionen, die können mehrere Werte zurückgeben, andererseits können viele der vorhandenen Excel-Funktionen nicht nur einen Wert als Standard liefern, sondern eben mehrere Werte. Das Zauberwort dafür, das heißt Shift + Strg + Enter. Mit der Tastenkombination Großschreib + Strg + Enter, können Sie eine Funktion dazu bringen, dass sie eben mehrere Werte liefert. Ich möchte Ihnen in diesem Video zeigen, wie die Excel-Matrixfunktionen arbeiten, denken, funktionieren, wie man die Technik der Arrays, oder Datenfelder, in der Programmierung einsetzen kann, um mehrere Werte zu speichern und wie man so, mit dieser Hilfe, mehrere Werte in einer einzigen Funktion zurückgeben kann, das heißt, wie man eine Matrixfunktion erstellen kann aus normalen Funktionen. Ich habe hier so ein paar Jahreszahlen vorbereitet, mit einem zugehörigen Umsatz und möchte gerne wissen, wie sich der Umsatz die nächsten Jahre verhält. Sie sehen hier einen Trend, der Trend steigt, aber er steigt nicht linear. Wunderbar. Ich markiere die letzten zwei Jahreszahlen, ich ziehe sie runter, zum Beispiel bis zum Jahr 2020. Hier hätte ich ganz gerne über den Trend gewusst, angenommen der Trend geht so weiter, wie läuft er denn weiter. Die Funktion, die das leistet, finden Sie in Formeln Statistik und diese Funktion heißt natürlich Trend. Trend möchte gerne wissen, welche Y_Werte, das sind die alte Y_Werte. Sie möchte gerne die zugehörige alten X_Werte haben, das hier sind die X_Werte. Und sie hätte ganz gerne die neuen X_Werte. Das wären die neuen zugehörigen X_Werte. Ich kann noch eine Konstante einfügen, das möchte ich erstmal nicht, sondern ich will einfach nur den Trend berechnen, ich will Ihnen zeigen, wie man die Funktion, die normalerweise mit einem Klick auf Ok einen Wert zurückgeben würde, wie man die dazu bekommt, dass sie mehrere Werte zurückgibt. Nun die Antwort habe ich schon geliefert, die Antwort heißt Shift + Strg + Enter. Indem ich hier die Tastenkombination Großschreib + Strg + Enter drücke, bekomme ich eben mehrere Werte auf einmal geliefert. Und Sie sehen oben in der Zeile, der Eingabe Zeile in der Bearbeitungsleiste, um die Funktion herum eine geschweifte Klammer. Diese geschweifte Klammer gibt an, dass es sich um eine Matrixfunktion handelt, die mehrere Werte zurückgibt. Das heißt, diese geschweifte Klammer erzeugt Excel in dem Moment, wo ich die Funktion mit Shift + Strg + Enter abschließe. Achtung, die dürfen Sie nicht über die Tastatur eingeben, sondern Sie müssen die Funktion mit dieser Tastenkombination beenden. Prima! Damit wir so etwas programmieren können, erstelle ich mir eine eigene Funktion. Entwicklertools, ich wechsele rüber nach Virtual Basic in ein neues Modul und ich mache mal, bevor ich mit Funktionen anfange, zuerst wieder ein Makro, ein Sub, ein Test, ich nenne es mal Datenfeld. Das ist der deutsche Ausdruck für Array, falls Sie den englischen Begriff mal lesen. Ich definiere dim Jahreszeiten, Klammer auf, Klammer zu, das Schlüsselwort für ein Array sind diese beiden Klammern, sie geben an, dass nicht nur einen Wert in der Variable gespeichert wird, sondern mehrere Werte. As String. Her ist noch nicht klar, wie groß die Definition von diesen Jahreszeiten ist. Die kann im Nachhinein festlegen, die kann ich auch ganz am Anfang festlegen. Ich definiere jetzt neu redim Jahreszeiten Klammer auf von (3) Jahreszeiten hätte ich gerne und damit, es hört sich erstmal paradox an, damit erhalte ich jetzt 4 Jahreszeiten. Warum? Er beginnt in der Zählung bei 0, das heißt, ich fühle den ersten Wert, den 0-Wert, mit dem ersten Jahreszeit. Jetzt kann ich loslegen und kann hier Jahreszeiten (0) = mit dem Text "Frühling" belegen. Ich kann ebenso Jahreszeiten (1) = mit dem Text "Sommer" belegen und so weiter, das schenke ich mir jetzt, und kann am Ende mit einer messagebox da drauf zugreifen, Jahreszeiten (1), und wenn ich das Programm starten lasse, Sie ahnen es, liefert er natürlich den Sommer. Wunderbar! Damit ich so etwas in Excel verwenden kann, darf ich es leider nicht dieser relativ einfachen Schreibweise schreiben, sondern Excel verlangt, ich erkläre auch gleich warum, Excel verlangt dummerweise ein zweidimensionales Array. Er verlangt es, Jahreszeiten 3,0. Warum dieses ,0 erkläre ich gleich. Das heißt, ich muss eben schreiben Jahreszahl 0,0 ist dieser Wert, ich muss ändern 1,0. Ich muss dann wieder auslesen die Jahreszeit von 1,0. Testen wir das Ganze. Ich bekomme hier raus immer noch den Sommer. Wunderbar! Wenn Sie nun fragen: Warum will Excel das so? Nun die Antwort ist leicht gezeigt, indem ich eine Funktion mache. Ich schreibe mal Funktion, function, ich hätte ganz gerne ein paar Buchstaben. Klammer auf, Klammer zu. Beachten Sie, diese Klammer steht natürlich einerseits für eine Funktion, die einen Wert zurückgibt, aber auch für mehrere Werte. Und beachten Sie, jetzt darf ich nicht die Funktion Buchstaben As String schreiben, weil jetzt liefert ja meine Funktion nicht einen String, sondern mehrere. Das ist eins der ganz seltenen Fälle, in denen ich den Typ Variant verwenden muss, indem ich also nicht explizit As String, As Integer, As Date deklarieren darf, das würde nicht funktionieren, sondern ich darf hier nichts deklarieren oder könnte explizit As Variant hinter schreiben. Das schenke ich mir, ich schreibe das einfach so. So ich definiere, dim, Bereich Klammer auf (1,1 Damit habe ich in jeder der Positionen zwei Plätze, das heißt 2x2, habe ich vier Plätze, As String. Die Variable, dieses Datenfeld, das darf ich sehr wohl als String oder Integer definieren. Und jetzt kann ich Bereich (0,0) füllen mit dem Text zum Beispiel "A". Klasse, ich markiere das, ich kopiere das, füge es ein, füge es ein, füge es ein. Ich ändere das natürlich "A", "B", "C" und "D". Ich ändere auch die Positionen (0,0), (0,1), (1,0), (1,1). Damit haben wir unsere vier Plätze definiert. Ok. Die Funktion soll natürlich ein Ergebnis erhalten, das heißt, ich schreibe jetzt an die Funktion Buchstaben Zurück = mein Datenfeld Bereich. Wunderbar! Am besten sollten Sie schreiben Bereich (), müssen Sie aber nicht, VBA würde das verzeihen. Jetzt haben wir vier Felder und das können wir in Excel verwenden, das heißt, ich kann jetzt hier vier Zellen markieren. Achtung, zwei nach unten, zwei nach rechts. Ich kann meine Funktion =Buchstaben aufrufen, Klammer auf, Klammer zu und beende die natürlich wieder mit Shift + Strg + Enter. Wunderbar! Wenn ich nun wollte, dass die Buchstaben nicht nebeneinander, sondern beispielsweise untereinander stehen, nun das ist klar, dann muss ich es ändern in ,0 und machen wir noch ein paar mehr Buchstaben, machen wir zum Beispiel sechs Buchstaben, dann haben wir sieben Stück. Das heißt, ich schreibe jetzt Bereich (0,0), Bereich, löschen wir es raus, es ist vielleicht am schnellste, Bereich (1,0), ist der Buchstabe "B", Bereich (2,0) ist der Buchstabe "C" und das kopieren wir doch jetzt schnell diese Zeile "C", "D", "E", "F". Haben wir schon alle? Schauen wir nach. Drei, vier, fünf, nein, einer fehlt noch. Kriegst du noch den sechsten und du bist "C", "D", "E", "F" und "G". So probieren wir das. Jetzt müssen die Buchstaben natürlich untereinander stehen und jetzt kann ich in Excel mir sieben Zellen markieren, zwei, vier, sechs, sieben und kann mit meiner Funktion =Buchstaben (), Shift + Strg + Enter die Buchstaben untereinander schreiben. Sie sehen natürlich die alte Funktion wird auch geändert, die stimmt so natürlich nicht mehr, klar. Und jetzt ein Beispiel, was können wir damit machen. Ich habe ein kleines Beispiel vorbereitet und zwar in dem Modul MehrereWerteZurück. Die Funktion Teiler erhält eine Zahl und diese Funktion Teiler soll mir von dieser Zahl sämtlicher Teiler, sämtliche Primfaktoren zurückgeben, die in dieser Zahl stecken. Dazu deklariere ich ein Wert As Long, habe eine Zähler-Schleife As Integer und eine weitere Teiler As Integer. Ich überprüfe erstmal, wie viele Teiler er überhaupt hat, dazu rufe ich eine andere Funktion auf, die ich bereits erstellt habe, AnzahlDerTeiler, die liefert mit die Anzahl der Teiler. Da dummerweise bei einem Array die Zählung bei 0 beginnt, wenn ich also 5 Teiler habe, darf ich nicht bis 5 hochzählen, sondern muss eins vorher aufhören, -1. Da wir auch hier ein zweidimensionales Array haben, muss ich mit ,0 enden. Ich beginne bei 0, ich lasse hochzählen: Immer wenn du ein Zähler findest, dann futtere diese Variable, diesen Zähler, dieses Datenfeld, mit dem Zähler, den du gefunden hast und setze dein Teiler um 1 hoch. Am Ende wird das Ergebnis des Datenfeldes übergeben in diese Funktion und die Funktion liefert dann alle Teiler zurück. Das heißt, wenn ich in Excel jetzt wissen möchte, zum Beispiel von meiner Zahl 24: Sag mir mal bitte, =AnzahlDerTeiler, wie viele stecken da denn drin? Dann sagt er mir, 24 enthält 8 Teiler. Zwei, vier, sechs, acht. Wir haben sie untereinander geschrieben. Ich kann jetzt mit =Teiler auf diese Zahl losgehen, Klammer zu, und Achtung mit Shift + Strg + Enter natürlich, Großschreib + Strg + Enter, sämtlicher Teiler von meiner Zahl 24 bekommen. Das heißt also, der Zaubertrick oder die Technik um so eine Matrixfunktion zu programmieren heißt Datenfelder, die mit runden Klammern geschrieben werden, die müssen richtig dimensioniert werden, müssen zweidimensional dimensioniert werden. Damit er die Werte untereinander schreibt muss ich ,0 arbeiten, in der Spalte direkt neben dran, nämlich 0 Spalten neben dran. Ich schreibe die Werte eben untereinander, fülle das Array und kann dann die Werte in Excel damit wieder auslesen.

Excel: Eigene Funktionen mit VBA erstellen

Schreiben Sie Ihre eigenen Excel-Funktionen. Als erfahrener Excel-Anwender lernen Sie, wie Sie mithilfe von VBA Berechnungen mittels selbst erstellter Funktionen automatisieren.

3 Std. 4 min (33 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Exklusiv für Abo-Kunden
Ihr(e) Trainer:
Erscheinungsdatum:21.01.2015
Laufzeit:3 Std. 4 min (33 Videos)

Im Training wird Excel 2013 benutzt. Die Vorgehensweisen sind aber für alle Windows-Versionen von Excel identisch und leicht übertragbar.

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!