Start Hilfe und Support Microsoft Office EXCEL 2010 VBA: Matrixformel selbst gebaut - am Beispiel von MTRANS()

EXCEL 2010 VBA: Matrixformel selbst gebaut - am Beispiel von MTRANS()

E-Mail PDF

Eine Matrixfunktion im Selbstbau - einfacher als man denkt

Eine Matrixfunktion ist in vielen Fällen eine gute Möglichkeit, komplexere Berechnungen durchzuführen und dann auch gleich einen Set von Ergebnissen auf dem Tabellenblatt bereitzustellen. VBA lässt es zu, dass man eine Matrixfunktion selbst erstellt und diese so verwendet, wie die schon mitgelieferten Matrixfunktionen wie z.B. MTRANS().

Aufgabenstellung

MTRANS() soll in diesem Beispiel nachentwickelt werden, um zu zeigen, wie die Entwicklung von Matrixfunktionen vorgenommen werden kann. Was man dann alles damit machen kann, ist Ihrer Phantasie überlassen. Ich selbst habe diese Funktionalität schon an vielen Stellen eingesetz, immer dann, wenn man in einem Rutsch viele Ergebnisse ausgeben möchte, die in Zusammenhang stehen und für die man nicht jeweils eine eigene Funktion schreiben möchte.

Aber zurück zu MTRANS(). Der Name der Funktion verrät schon den Sinn der Funktion. Er transponiert eine Matrix. Wenn man also eine 2 x 3-Matrix transponiert (2 Zeilen und 3 Spalten), wird daraus eine 3 x 2-Matrix (3 Zeilen und 2 Spalten), dabei finden Sie dann die ursprünglichen Inhalte der Zeilen in den Spalten der neuen Matrix:

vba_matrixfunktion

Programmcode

Function myMTRANS(ByVal vBereich As Range) As Variant

  Dim lAnzSpalten As Long
  Dim lAnzZeilen As Long
  Dim lZaehlerSpalten As Long
  Dim lZaehlerZeilen As Long
  Dim arrErgebnis() As Variant  ' Das Ergebnisarray wird as Variant definiert, da der übergebene Bereich
                                ' verschiedene Datentypen enthalten kann. Diese verschiedenen Datentypen
                                ' innerhalb des Arrays werden dann als Ergebnis auch zurückgegeben.


  ' Die Größe des übergebenen Bereichs (Range-Objekt) wir bestimmt
  lAnzZeilen = vBereich.Rows.Count  ' Anzahl der Zeilen
  lAnzSpalten = vBereich.Columns.Count  ' Anzahl der Spalten

  ' Nun wird per ReDim die Größe des ErgebnisArrays definiert.
  ' Da der übergebene Bereich transponiert wird, entsprechen die Anzahl der Spalten des Rangeobjektes
  ' der Anzahl der Zeilen des ErgebnisArrays und die Anzahl der Zeilen des Rangeobjektes
  ' der Anzahl der Spalten des ErgebnisArrays.
  ' Im Regelfall beginnt der Index bei den Arrays bei 0, die 0 wird jedoch nicht benötigt, daher
  ' wird mit der Deklaraton "1 to x" definiert, dass der Index bei 1 beginnen soll.
  ReDim arrErgebnis(1 To lAnzSpalten, 1 To lAnzZeilen)

  ' In den folgenden verschachtelten Schleifen werden die Spalten und Zeilten ausgetauscht.
  For lZaehlerSpalten = 1 To lAnzSpalten
    For lZaehlerZeilen = 1 To lAnzZeilen
      arrErgebnis(lZaehlerSpalten, lZaehlerZeilen) = vBereich.Cells(lZaehlerZeilen, lZaehlerSpalten)
    Next lZaehlerZeilen
  Next lZaehlerSpalten

  ' Das ermittelte Ergebnis wird als Rückgabewert der Funktion myMTRANS übergeben.
  myMTRANS = arrErgebnis
End Function

Besonderheiten bei der Benutzung einer Matrixfunktion in EXCEL

Eine Matrixfunktion muss als sogenannte Arrayformel auf dem EXCEL-Arbeitsblatt erfasst werden. Dazu ist es notwendig, dass zunächst der Bereich, in dem das Ergebnis angezeigt werden soll, markiert werden.

ergebnisbereich_markieren

Dann ist die Formel zu erfassen, in unserem Fall die eben entwickelte Formel myMTRANS(). Am Ende ist die Eingabe abzuschließen mit der Tastenkombination STRG+UMSCHALT+RETURN bzw. STRG+UMSCHALT+ENTER. Dass Sie die Erfassung der Arrayformel richtig vorgenommen haben, erkennen Sie daran, dass die Formel in der geschweiften Klammer {} dargestellt wird, in unserem Beispiel ={myMTRANS(BEREICH)}.

Wird der Bereich zu klein gewählt, wird nur ein Teil der Ergebnisse angezeigt, wird der Ergebnisbereich zu groß gwählt, werden in den Zellen, die nicht berechnet werden können, Feher ausgewiesen.

bereich_falsch_definiert

Schlussbetrachtung

Vielleicht haben Sie nun schon viele eigene Ideen, wie man die Arrayfunktion/Matrixfunktion für weitere Anwendungsbereiche nutzen kann. Auch das hier vorgestellt Beispiel können Sie hier herunterladen.


Zuletzt aktualisiert am Dienstag, den 11. Juni 2013 um 10:31 Uhr  


Machen Sie mit

Auf dieser Seite sollen die Termine in Happerschoß bekannt gemacht werden. Haben Sie einen Termin, der Ihnen wichtig ist und auf dieser Seite fehlt (schauen Sie nach unter Termine)?. Dann melden Sie sich bitte und teilen den Termin mit. Wichtig dabe ist eine Beschreibung des Anlasses, Beginn und Ende des Termins (Datum und Uhrzeit), der Ort, wo der Termin stattfindet und wer den Termin veranstaltet. Melden Sie diese Termin-Informationen über den Kontaktbereich oder natürlich auch über Info@happerschoss.net.