Matrixformeln und Arrays

Home

Inhalt

Matrixformeln
  Was ist eine Matrixformel?
  So geben Sie eine Matrixformel ein
  Wissenswertes über Matrixformeln

Arrays (Datenfelder)
  Was ist ein Array?
  Statische und dynamische Arrays
  Deklaration und Dimensionierung von Arrays

Arrays in der Praxis
  Arrays löschen
  Zellbereich in Array speichern

Limitationen von Arrays
  Berechnen der Speicherbeanspruchung eines Arrays

VBA-Beispiele

Links und Downloads
  Bugs und Probleme
  Weitere Informationen
  Downloads

Matrixformeln

Was ist eine Matrixformel?

- Eine Matrixformel führt mehrere Berechnungen durch und gibt dann entweder ein einzelnes Ergebnis oder mehrere Ergebnisse zurück.
- Matrixformeln arbeiten mit zwei oder mehreren Gruppen von Werten, die als Matrixargumente bezeichnet werden.
- Jedes Matrixargument muss dieselbe Anzahl von Reihen und Spalten besitzen.
- Soll die Formel mehrere Ergebnisse zurückgeben, muss diese in mehreren Zellen eingegeben werden.
- Um eine Matrixformel einzugeben, drücken Sie STRG+UMSCHALT+EINGABE.
- Excel setzt Matrixformeln in geschweifte Klammern ({}).

So geben Sie eine Matrixformel ein

Wenn Sie eine Matrixformel eingeben, schliesst Microsoft Excel die Formel automatisch in geschweifte Klammern ({}) ein. So geben Sie eine Matrixformel ein:
1. Soll die Matrixformel nur ein einzelnes Ergebnis liefern, klicken Sie auf die Zelle, in die Sie die Matrixformel eingeben möchten. Soll die Matrixformel mehrere Ergebnisse liefern, markieren Sie den Zellbereich, in den Sie die Matrixformel eingeben möchten.
2. Geben Sie die Matrixformel ein.
3. Drücken Sie STRG+UMSCHALT+EINGABE.

Um per VBA die Editierung und Eingabe einer Matrixformel zu simulieren, kann folgender VBA-Code eingesetzt werden:
Public Sub ReenterArrayFormula()
     SendKeys "{F2}"
     SendKeys "^+{Enter}"
  End Sub

Wissenswertes über Matrixformeln

Eine Matrixformel kann mehrere Berechnungen durchführen und dann entweder ein einzelnes Ergebnis oder mehrere Ergebnisse liefern. Matrixformeln bearbeiten zwei oder mehr Wertesätze, die als Matrixargumente bezeichnet werden. Alle Matrixargumente müssen über dieselbe Anzahl von Zeilen und Spalten verfügen. Sie erstellen eine Matrixformel auf dieselbe Weise wie eine elementare Formel, die nur einen Wert liefert. Markieren Sie die Zelle oder die Zellen, in die die Formel eingegeben werden soll, erstellen Sie die Formel, und drücken Sie dann STRG+UMSCHALT+EINGABE, um die Formel einzugeben.

Wenn die Matrixformel nur ein einzelnes Ergebnis liefern soll, muß Microsoft Excel unter Umständen mehrere Berechnungen durchführen, um das Ergebnis zu erzeugen. Beispielsweise berechnet die folgende Formel nur für jene Zellen im Bereich D5:D15 den Mittelwert, bei denen die entsprechenden Zellen in derselben Zeile in Spalte A den Text "Albatros Fluggesellschaft" enthalten. Die Funktion WENN ermittelt im Bereich A5:A15 alle Zellen, die "Albatros Fluggesellschaft" enthalten, und gibt dann den Wert der entsprechenden Zellen im Bereich D5:D15 an die Funktion MITTELWERT zurück.

  {=MITTELWERT(WENN(A5:A15="Albatros Fluggesellschaft";D5:D15))}

Um mit Hilfe einer Matrixformel mehrere Ergebnisse zu berechnen, müssen Sie die Matrix in einen Zellbereich eingeben, der über dieselbe Anzahl von Zeilen und Spalten verfügt wie die Matrixargumente. In dem folgenden Beispiel ermittelt die Funktion TREND für eine Reihe von fünf Verkaufszahlen (in Spalte B) und eine Reihe von fünf Datumsangaben (in Spalte A) die Werte für die Verkaufszahlen, die sich aus dem linearen Trend ergeben. Damit alle Ergebnisse der Formel angezeigt werden, wird diese in fünf Zellen in Spalte C eingegeben (C10:C15).

  {=TREND(B10:B15;A10:A15)}

Sie können eine Matrixformel auch dazu verwenden, um einzelne und mehrere Ergebnisse für eine Reihe von Werten zu berechnen, die nicht in das Tabellenblatt eingegeben wurden. Matrixformeln können genau wie einfache Formeln Konstanten enthalten; die Matrixkonstanten müssen jedoch in einem bestimmten Format eingegeben werden. Beispielsweise können Sie anhand der fünf Werte und der fünf Datumsangaben aus dem vorherigen Beispiel die Verkaufszahlen für zwei weitere, zukünftige Daten prognostizieren. Da Formeln oder Funktionen nicht als Matrixkonstanten verwendet werden können, werden im folgenden Beispiel für die zusätzlichen Datumsangaben fortlaufende Zahlen für das dritte Argument der Funktion TREND verwendet:

  {=TREND(B10:B15;A10:A15;{35246;35261})}

Beachten Sie bitte, dass nicht alle Excel-Funktion in der Lage sind, Matrixkonstanten zu verwenden.

Arrays (Datenfelder)

Was ist ein Array?

Statische und dynamische Arrays

Deklaration und Dimensionierung von Arrays

Meldung: Datenfeldargument muß vom Typ ByRef sein

Datenfelder, die mit Dim, ReDim oder Static deklariert wurden, können nicht mit ByVal übergeben werden. Für diesen Fehler gibt es die folgende Ursache und Lösung:

• Sie haben versucht, ein ganzes Datenfeld mit ByVal zu übergeben.

  Ein einzelnes Element in einem Datenfeld kann mit ByVal (als Wert) übergeben werden, aber ein ganzes Datenfeld muß mit ByRef (als Referenz) übergeben werden. ByRef ist die Voreinstellung. Wenn Sie ein Datenfeld mit ByVal übergeben müssen, weil sie verhindern möchten, daß Änderungen an den Datenfeldelementen in der aufrufenden Prozedur nach dem Aufruf übernommen werden, können Sie das Datenfeldargument in einem eigenen Klammernpaar übergeben, oder Sie können es in einer Variablen vom Typ Variant ablegen und diese Variable dann als ByVal-Parameter übergeben, wie im folgenden Beispiel:

  Dim Var1 As Variant
Var1 = FeldAlt()


Arrays in der Praxis

Arrays löschen

Grösse einer Array-Dimension ändern (Redimensionierung)

Bei mehrdimensionalen Arrays ist normalerweise nur Änderung der letzten Dimension möglich.

Diese VBA-Prozedur redimensioniert bei einem zweidimensionalen Array beide Dimensionen:

Sub FreeArray()
   Dim intCol As Integer, intRow As Integer, intCounter As Integer
   Dim arr()
   Application.ScreenUpdating = False
   ReDim arr(1 To 2, 1 To 10)
   For intRow = 1 To 2
      For intCol = 1 To 10
         arr(intRow, intCol) = "Spalte " & intCol & " - " & "Zeile " & intRow
      Next intCol
   Next intRow
   MsgBox "Letzter Wert:" & Chr(10) & arr(2, 10)
   Workbooks.Add
   Range("A1:J2") = arr
   For intCounter = 1 To 11
      Cells(3, intCounter) = "Spalte " & intCounter & " - " & "Zeile 3"
   Next intCounter
   For intCounter = 1 To 3
      Cells(intCounter, 11) = "Zeile " & intCounter & " - " & "Spalte 11"
   Next intCounter
   arr = Range("A1").CurrentRegion
   ActiveWorkbook.Close savechanges:=False
   MsgBox "Letzter Wert:" & Chr(10) & arr(3, 11)
End Sub

Anzahl Array-Elemente ermitteln

Zwei Arrays miteinander vergleichen

Array-Elemente einem anderen Array zuweisen

Zellbereich in Array speichern

Sie werden jetzt vielleicht denken, dass es wirklich nicht so schwierig sein kann, einen Zellbereich in ein Array zu speichern.

Anzahl Parameter in ParamArray ermitteln

Function ArrayCount(ParamArray arr() As Variant) As Integer
  ArrayCount = UBound(arr) + 1
End Function

Sub Aufruf()
  MsgBox ArrayCount(5, 7, 9, 11, 13)
End Sub

Description

Code

Private Declare Function CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, src As Any, ByVal length As Long) As Long
  
Public Function IsArraySafe(ArrayVal As Variant) As Boolean
   If (VarType(ArrayVal) And vbArray) Then
      Dim lpSafeArray As Long
      Dim nDimensions As Long
     
      CopyMemory lpSafeArray, ByVal VarPtr(ArrayVal) + 8, 4
      CopyMemory nDimensions, ByVal lpSafeArray, 2
     
      If nDimensions > 0 Then
         IsArraySafe = True
      Else
         IsArraySafe = False
      End If
   Else
      Debug.Assert False
      'ArrayVal doesn't look to contain an Array
   End If
End Function

Sample Usage

If Not IsArraySafe(nArray()) Then
   Redim nArray(100)
End If

Limitationen von Arrays

Wenn man in der Excel-Dokumentation (Online-Hilfe) nachschaut, dann erfährt man ziemlich wenig über die Limitationen und Grenzen von Excel-Arrays.

Berechnen der Speicherbeanspruchung eines Arrays

Ein Zellbereich mit mehreren Spalten und Zeilen wird als zweidimensionales Datenfeld abgebildet. Die Untergrenze für die Indizes der beiden Array-Dimensionen ist 1 (für Excel-Zellbereiche).

Da der Wert einer Zelle (Value-Eigenschaft) nicht zwingend eine Zahl enthalten muss, sondern auch Buchstaben bzw. längere Texte oder Fehlerwerte vorkommen können, kann ein Bereich-Array weder als Zahl noch Text deklariert werden. Stattdessen erfolgt die Deklaration mit dem Datentyp Variant (der seinerseits intern den benötigten Datentyp verwendet).

Dieses Beispiel deklariert ein statisches Datenfeld fester Grösse für einen Zellbereich von 5'000 Zeilen und 26 Spalten (Spalten A-Z):
   Dim avarBereich(1 To 5000, 1 To 26) As Variant

Der beanspruchte Speicherbedarf wird so berechnet:
  Speicherbedarf = Anzahl Zeilen * Anzahl Spalten * Bytes des Datentyps
  Speicherbedarf = 5'000 * 26 * 16
  Speicherbedarf = 2'080'000 Bytes

Anmerkung
Die Default-Einstellung für VBA-Arrays ist 0. Bei Bedarf sollte daher die Anweisung "Option Base 1" verwendet werden. Die Basis eines mit der Array-Funktion oder dem Schlüsselwort "ParamArray" erstellten Datenfeldes ist Null. Die Option Base-Anweisung hat keinen Einfluss auf "Array" oder "ParamArray".

VBA-Beispiele

Links und Downloads

Bugs und Probleme

XL97: Run-Time Error Using FormulaArray Property
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q192989

XL97: Cannot Paste Array Formula in Its Original Cell
http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q158080

XL2000: Benutzerdefinierte Funktion gibt bei großem Array "#WERT!" zurück
http://support.microsoft.com/default.aspx?scid=kb;de;d43187

XL2000: Run-Time Error Using FormulaArray Property
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q213181

XL2000: Excel May Fault in MSO9.DLL When Calling an XLL Function that Takes an Array
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304442
Dieses Problem lässt sich mit einem Hotfix beheben.

Weitere Informationen

XL97: Maximum Array Size in Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q166342

XL2000: Maximum Array Size in Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q166342

XL2000: How to Use Dynamic Arrays in a Macro
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q213619

XL2000: Custom Function to Turn Nonadjacent Cells into an Array
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q213403

EXCEL: Returning Arrays
http://www.cpearson.com/excel/returnin.htm
Kurzer aber sehr guter Artikel zum Thema "Arrays" von 'Pearson Software Consulting' mit VBA-Codebeispielen.

Downloads

Haben Sie Fragen, Anregungen oder einen Fehler entdeckt?
Mail senden an philipp_von_wartburg@yahoo.de

Zuletzt aktualisiert am 27.01.2006 / 21:30 Uhr
© 2002-2006 by Philipp von Wartburg, CH-8916 Jonen
Alle Rechte vorbehalten