Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins
Excel/VBA

Excel-Beispiele:
Erklärungen, Formeln, VBA-Code und mehr

Hinweise zu den Beispielen finden Sie hier: Beispiele


Arrayformeln (01)FormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 18) Nachricht zum Beitrag an Autor Nach oben

Was sind die Arrayformeln, die ab Excel 365 eingeführt wurden?

Mit Excel 365 wurde das System der Formeln neu erarbeitet, wie mehrfach berichtet wurde. Allerdings handelt es sich dabei eigentlich nur um eine Ergänzung, wenn auch eine sehr sinnvolle und nützliche. Die alten Formeln arbeiten ganz normal weiter - hier muss nicht befürchtet werden, dass irgendetwas nicht mehr funktioniert. Neu sind hingegen die Arrayformeln. Zum Vergleich: Bisher konnte in Formeln mit kompletten Bereichen gearbeitet werden, zum Beispiel mit Summewenn. Die Eingabe der Formeln musste dazu mit Strg + Umschalt + Enter abgeschlossen werden, woraufhin Excel um die Formel geschweifte Klammern {} legte. Die Formel wurde somit als Matrixformel erkannt und es wurde ein kompletter Bereich verarbeitet. Das ERgebnis hingegen stand als ein Wert in einer Zelle.
Mit den Arrayformeln wurde dies erweitert und auch vereinfacht. Sollen komplette Bereiche verarbeitet werden, wird die Eingabe der Formel ganz normal beendet; die geschweiften Klammern werden nicht mehr benötigt. So zum Beispiel diese Formel, die in B2:B13 Nach "Juni" sucht und aus C2:C13 die Werte addiert:

=SUMMEWENN(B2:B13;"Juni";C2:C13)

Auch hier haben wir in einer Zelle ein Ergebnis, nämlich die Summe. Der Unterschied bis hierher liegt nur in den fehlenden geschweiften Klammern.


Überlaufende bzw. verschüttete Formeln

Der größte Unterschied wird jedoch deutlich, wenn in eine Zelle folgende einfache Formel eingegeben wird:

=A2:C13

Geschieht dies bei einer Excelinstallation zum ersten Mal, erscheint eine Meldung:

Formel übergelaufen: Ihre Formel hat mehrere Werte zurückgegeben, weshalb wir sie in die benachbarten leeren Zellen haben überlaufen lassen.

Das Ergebnis wird dann auch deutlich: Die Formel liefert alle Werte, die sich im Bereich befinden, der in der Formel angegeben ist - von der 1 in A2 bis zur 600 in C13. Der erste Wert steht dabei in der Zelle mit der Formel, die anderen Werte rechts neben und unter dieser Zelle - die Formel ist übergelaufen. Das Verhalten ist vergleichbar mit der CSS-Eigenschaft float: left;. Allerdings steht die Formel tatsächlich nur in der einen Zelle; sie kann auch nur dort bearbeitet werden. Sichtbar wird das, wenn im Ergebnis der Inhalt einer anderen Zelle gelöscht werden soll - es geht nicht.

Dieses Verhalten, dass mehrere Ergebnisse ausgegeben werden sollen, kann vielfältig genutzt werden. Beispiel für eine weitere einfache Formel:

=WENN(C2:C13>400;B2:B13;"")

In Zeile 2 neben der Basistabelle liefert sie in den entsprechenden Zeilen die Monate, bei denen die Beträge größer als 400 sind. Das Gleiche passiert natürlich auch, wenn die Formel in eine andere Zeile eingetragen wird - nur hat man dann den Offset, weil die Ergebnisse ab der Zelle mit der Formel angezeigt werden.

Diese Formel würde die Zahlen in den entsprechenden Zeilen anzeigen, die zum "Juni" gehören:

=WENN(B2:B13="Juni";C2:C13;"")

Natürlich kann die als Arrayfunktion verwendete Wenn-Funktion auch in anderen Funktionen eingesetzt werden. Hier werden alle Zahlen aus C addiert, wenn sie größer als 400 sind:

=SUMME(WENN(C2:C13>400;C2:C13))

In dem Fall haben wir natürlich wieder nur ein Ergebnis, nicht eine Matrix aus mehreren Werten


Fehler: #ÜBERLAUF! bzw. #SPILL!

Dieser Fehler erscheint, wenn Excel eine Arrayformel nicht berechnen oder deren Ergebnisse nicht darstellen kann.

Meist wird dies der Fall sein, wenn schlicht zu wenig Platz für die Ausgabe ist. Dann erscheint in der Zelle mit der Formel die Meldung und es wird mit einem Rahmen dargestellt, wie viel Platz benötigt würde. Im Weg können dabei Zellinhalte sein, aber auch das Ende der Tabelle, verbundene Zellen usw.

Auch wenn in einer Arrayfunktion Zufallszahlen oder andere Ergebnisse verwendet werden, kann der Fehler erscheinen. Dann ist die innere Funktion (zum Beispiel eine für Zufallszahlen) noch nicht fertig, während die äußere aber schon rechnen möchte. Da aber die Ergebnisse der inneren Funktion fehlen bzw. unvollständig sind, kommt diese Meldung.


Schnittmengenoperator @

Möglicherweise haben Sie schon die Meldung bekommen:

Warum ist der @-Operator hier? Wir haben ein Upgrade der Formelsprache von Excel durchgeführt. Hieraus resultiert, dass Ihnen vielleicht in manchen Formeln der @-Operator auffallen wird. Ihre Formeln verhalten sich auf dieselbe Weise wie immer.

Gleichzeitig kann es sein, dass sich in Ihren Formeln auf einmal @-Zeichen nach den Gleichheitszeichen befinden, die Sie gar nicht eingetragen haben.

Dieses Verhalten hängt unmittelbar mit den Arrayformeln bzw. Arrayfunktionen zusammen. Nehmen wir obige Formel:

=WENN(C2:C13>400;C2:C13;"")

Sie liefert, wie wir gesehen haben, alle Werte, die größer als 400 sind, in und unter der Zelle mit der Formel. Was nun aber, wenn wir nur ein Ergebnis benötigen, das erste? Dieser Fall kann auftreten, wenn Formeln über mehrere Zellen gezogen oder wenn Funktionen verschachtelt werden sollen. In diesem Fall setzen wir direkt hinter das Gleichheitszeichen das @-Zeichen:

=@WENN(C2:C13>400;C2:C13;"")

Nun haben wir nur noch ein Ergebnis; in diesem Fall das erste, die 500.

Um bei diesem Beispiel zu bleiben: Setzen wir in der Formel die letzte Zeile absolut (durch das $-Zeichen), kann die Formel nach unten gezogen werden und wir haben als Ergebnis immer den Wert aus den Zellen ab der Zeile mit der Formel:

=@WENN(C2:C$13>400;C2:C$13;"")

Arrayformeln (02): Eigene Arrayformeln, MTRANS/TRANSPOSEUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 27) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich eigene Arrayformeln erstellen und nutzen?

Vor Excel 365 konnten natürlich auch einfach benutzerdefinierte Funktionen (UDF) erstellt werden. Wurden sie als Tabellenblattformeln verwendet, galt natürlich das, was generell für Formeln zählte: Es konnte ein Ergebnis pro Zelle erscheinen. Brauchte man mehr Ergebnisse, musste eine Sub() ran, die die Eintragungen in die Zellen vornahm.

Mit den Arrayformeln ab Excel 365 gab und gibt es nun weitaus mehr und vor allem vielfältige Möglichkeiten. So kann eine eigene Funktion einen kompletten Array als Ergebnis liefern - dieser wird dann als übergelaufene Formel in die Zellen neben und unterhalb der Zelle mit der Formel eingetragen.

Als Beispiel eine Funktion, die einen einfachen Monatskalender erstellt:

Function MonatsKalender(ByVal intMonat As Integer, ByVal intJahr As Integer) Dim intArr As Integer, intI As Integer, datDatum As Date Dim arrRet() MonatsKalender = "" intArr = 0 datDatum = CDate("1." & intMonat & "." & intJahr) Do intArr = intArr + 1 ReDim Preserve arrRet(1 To 3, 1 To intArr) arrRet(1, intArr) = Format(datDatum, "DD.MM.YYYY") arrRet(2, intArr) = Format(datDatum, "DDDD") arrRet(3, intArr) = IIf(Weekday(datDatum, vbMonday) = 7, "Frei!", "Arbeiten!") datDatum = datDatum + 1 Loop While Month(datDatum) = intMonat MonatsKalender = arrRet End Function

An die Funktion werden als Parameter die Zahlen für Monat und Jahr übergeben. Die Funktion erhöht dann das Datum so lange, wie der Monat des aktuellen Datums gleich dem übergebenen Monat ist. Bei jedem Datum werden Datumsangabe, Wochentag und ein Eintrag ("Frei!" oder "Arbeiten!") in einen Array eingetragen.

Da wir im Voraus nicht wissen, wie viele Tage der Monat hat, wird der Array „unterwegs“ immer neu dimensioniert. Dies ist nur für die letzte Dimension möglich, also erfolgt dies auch hier so. Die einzelnen Tage stecken somit in der zweiten Dimension (1 To intArr), während die drei Angaben zum Datum in der ersten Dimension sind (1 To 3). Für Tabellenblattfunktionen sollte das Zählen mit 1 statt der 0 beginnen, wozu Option Base 1 gesetzt oder - wie hier - die Deklaration entsprechend erfolgen kann.

In die Zelle kann nun eingetragen werden, wobei in B1 die Monatszahl und in B2 die Jahreszahl steht:

=monatskalender(B1;B2)

Das Ergebnis ist sofort sichtbar: An der Zelle erscheint der Kalender. Nun können einfach in B1 bzw. B2 Monat oder Jahr geändert werden - der Kalender passt sich sofort an.


Transponieren: MTRANS oder TRANSPOSE

Allerdings wird es in vielen Fällen so sein, dass die Richtung der Ergebnisse nicht wie gewünscht ist. Wir haben im Array die Spalten redimensioniert und die Datumsangaben dort eingetragen, also erscheinen die Datumsangaben auch auf die Spalten verteilt.

Dies ist jedoch kein Problem - das Verhalten des Eintragens der Arrayelemente kann mit der integrierten Tabellenblattfunktion MTRANS() geändert werden. Dazu wird die eigene Funktion (oder bei Bedarf auch andere Funktionen) in Mtrans gesetzt:

=MTRANS(monatskalender(B1;B2))

Schon haben wir den Kalender so, wie wir ihn wahrscheinlich erwartet haben.

Eine Alternative ist, diese Tabellenblattfunktion MTRANS() gleich in der Funktion einzusetzen und den Array bereits vor der Ausgabe zu drehen:

MonatsKalender = Application.WorksheetFunction.Transpose(arrRet)

Zu sehen ist, dass im VBA-Code die englischsprachige Variante genutzt werden muss, die hier Transpose ist.

Arrayformeln (03): Arrayformeln in VBA, EVALUATEMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 91) Nachricht zum Beitrag an Autor Nach oben

Können Ergebnisse von Arrayformeln in VBA verwendet werden?

Integrierte Möglichkeiten von Excel (Funktionen, Methoden) können um ein Vielfaches schneller sein, als wenn man diese selbst per VBA erstellt bzw. nachgestaltet. So war es schon immer ein großer Unterschied, ob man die Find-Methode von Excel nutzte oder eine Schleife über viele Zellen laufen ließ, um einen bestimmten Wert zu finden. Insofern kann auch in Betracht gezogen werden, mit VBA Arrayformeln (bzw. integrierte Arrayfunktionen) zu nutzen und deren Ergebnisse zu verarbeiten.

Evaluate()

Vor den Erläuterungen zu den Arrayfunktionen in VBA eine Vorbemerkung zu EVALUATE(), einer Methode, die recht unbekannt, hier aber oft nützlich ist.

Von anderen Sprachen kennt man eval() is evil - in Excel/VBA macht die Methode genau das, was hinter dem Spruch steckt, ist aber bei richtiger Verwendung ungefährlich. Eine Beschreibung ist bei Microsoft zu finden; wir nutzen diese Methode hier, um Formeln/Funktionen interpretieren zu lassen und zu Ergebnissen zu führen.

Dieser Hinweis war wichtig, weil hier in der Folge eine Formel ohne Funktion (also auch ohne Application.WorksheetFunction) verwendet wird, um Arrayformeln in VBA möglichst einfach zu demonstrieren:

Rückgabe: Array

Basis zum Verwenden von Arrayformeln in VBA ist, dass sie Arrays zurückgeben. Schließlich ist dies auch beim Einsatz auf dem Tabellenblatt der Fall, nur dass wir dort die Ergebnisse in Zellen sehen. Entsprechend ist auch wichtig zu wissen, dass Bereiche in Arrayergebnissen zwei Dimensionen haben können, strukturiert in Zeilen und Spalten sind.

Die Bereiche in den Arrays aus Arrayformeln sind grundsätzlich in Zeilen gegliedert, sie sind die erste Dimension. Wenn wir nun also in C2:C7 Einträge haben und mit arr = Evaluate("=C2:C7") (statt Evaluate wäre auch Range("C2:C7") möglich) darauf zugreifen, wird als Ubound folgerichtig die 6 ausgegeben. Entsprechend kann mit einer Schleife For intI = 1 To UBound(arr) über die einzelnen Elemente des Arrays gegangen werden.

Allerdings muss jetzt beachtet werden, dass die einzelnen Werte in der ersten Spalte des Arrays stehen - das erste (und in diesem Beispiel einzige) Element in der zweiten Dimension. Ein Zugriff auf arr(intI) würde einen Fehler erzeugen, da hier die Angabe der Spalte fehlt. Erst mit dieser Angabe wird der gewünschte Eintrag geliefert: arr(intI, 1).

Der Aufbau des Codes wäre also:

Sub Test() Dim arr, intI As Integer, strTemp As String arr = Evaluate("=C2:C7") MsgBox "Einträge: " & UBound(arr) For intI = 1 To UBound(arr) strTemp = arr(intI, 1) ' Einträge in Zeilen! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub

Im Beispiel standen die Werte untereinander in den Zellen. Was nun aber, wenn die Werte nebeneinander stehen, zum Beispiel in D1:I1?

In diesem Fall besteht der Array nur aus einer Zeile, der Ubound wird somit 1 sein.

Um an die einzelnen Elemente zu gelangen, gibt es nun die erste Möglichkeit, per Schleife die Elemente dieser einen Zeile zu liefern, wobei allerdings bekannt sein muss, wie viele Elemente die Zeile hat:

For intI = 1 To 6 strTemp = arr(1, intI) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next

Eine Alternative dazu ist, den Array zu transponieren. Dies geht mit der Funktion MTRANS() in der englischen Schreibweise bereits beim Übergeben an die Variable:

arr = Application.WorksheetFunction.Transpose(Evaluate("=D1:I1"))

Damit sind die ehemaligen Spalten nun die Zeilen und der Array kann wie vorher mit Schleife bis zum Ubound (der jetzt im Beispiel wieder 6 ist) durchlaufen werden.

Der Code zum Testen:

Sub Test1() Dim arr, intI As Integer, strTemp As String arr = Evaluate("=D1:I1") MsgBox "Einträge: " & UBound(arr) ' 1, weil nur 1 Zeile For intI = 1 To 6 strTemp = arr(1, intI) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub Sub Test2() Dim arr, intI As Integer, strTemp As String arr = Application.WorksheetFunction.Transpose(Evaluate("=D1:I1")) MsgBox "Einträge: " & UBound(arr) For intI = 1 To 6 strTemp = arr(intI, 1) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub

Arrayformeln (04): EINDEUTIG/UNIQUE (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 116) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Funktion EINDEUTIG() (in VBA) nutzen?

Ab Excel 365 gibt es neben der Möglichkeit, Duplikate zu entfernen, auch eine Funktion zum Einsatz in einer Formel: EINDEUTIG(). Die Funktion sucht in einer Tabelle nach doppelten Datensätzen und gibt in der einfachen Variante jeden nur einmal aus. Weitere Informationen zu Parametern der Formel gibt es bei Microsoft: EINDEUTIG-Funktion.

Im Beispiel ist zu sehen, dass die Monate Februar und zweimal Mai im Ergebnis nur jeweils einmal erscheinen, weil diese Monate auch jeweils die gleichen Zahlen haben. Der Juni ist jedoch zweimal im Ergebnis enthalten, weil diese Datensätze unterschiedliche Zahlen haben und somit insgesamt unterschiedlich sind.

Verwendung in VBA

Auch mit VBA kann diese Funktion doppelte Datensätze ausfiltern, indem die englische Schreibweise zum Einsatz kommt:

Application.WorksheetFunction.Unique(Array)

Im Beispiel wird die Tabelle aus der Abbildung im Bereich A2:E15 verwendet. Hier sind die Datensätze bei Frau Linz identisch und zwei Datensätze bei Frau Herzig. Aus dieser Tabelle erstellen wir den Array:

arr = Range("A2:E15")

Zum Herausfiltern der doppelten Datensätze wird die Funktion eingesetzt:

arr = Application.WorksheetFunction.Unique(arr)

Weiterverarbeitung des Ergebnisarrays

Das Ergebnis ist nun in der Variablen arr der Array mit den eindeutigen Datensätzen. Dabei gibt es jedoch zwei verschiedene mögliche Fälle:

Es können (wie im Beispiel) mehrere Zeilen sein. Dann kann der Array von 1 bis zum Ubound (der hier die Anzahl der Zeilen im Ergebnis ist) mit arr(Zeile, Spalte) durchlaufen werden:

For intI = 1 To UBound(arr) MsgBox arr(intI, 1) & " " & arr(intI, 2) & ", " & arr(intI, 3) Next

Hier würde für jede Zeile eine MsgBox mit Anrede Name, Vorname erscheinen.

Es kann aber auch der Fall eintreten, dass im Ergebnis nur eine Zeile übrig bleibt, die nun als Array vorliegt. Hier enthält der Array jedoch nicht die einzelne Zeile als Arrayelement der ersten Dimension, sondern bereits die einzelnen Elemente in der ersten Ebene. In dem Fall würde ein Zugriff mit arr(Zeile, Spalte) zu einem Fehler führen, weil das Auslesen nur mit arr(Spalte) erfolgen darf.

Wenn wir im VBA-Code also beide Fälle berücksichtigen wollen, müssen wir prüfen, ob der Array aus mehreren Zeilen zu mehreren Spalten oder nur aus mehreren Spalten ohne Zeile besteht. Dazu bietet sich an, die Anzahl aller Elemente des Arrays festzustellen:

intAnzahlEl = Application.WorksheetFunction.CountA(arr)

Wenn diese Zahl gleich dem Ubound des Arrays ist, muss es sich um einen eindimensionalen handeln, weil das dann die einzelnen Spalten sind. Wenn nicht, handelt es sich um einen mehrzeiligen Array, weil es dann immer mehr Elemente als der Ubound sind (Zeilen * Spalten = intAnzahlEl).

Und so können wir in unserem Code gut die Weiche stellen - an der Stelle der MsgBoxen müsste die eigentliche Verarbeitung der Daten rein:

Sub Eindeutig_vba() Dim arr, intI As Integer, intAnzahlEl As Integer arr = Range("A2:E15") 'Zur Ausgabe von mehreren Zeilen 'arr = Range("A2:E2") 'Zur Testausgabe einer Zeile arr = Application.WorksheetFunction.Unique(arr) 'Anzahl aller(!) Elemente im Array: intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(1) & " " & arr(2) & ", " & arr(3) Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 1) & " " & arr(intI, 2) & ", " & arr(intI, 3) Next End If End Sub

Tipp - Sortieren:

Wenn das Ganze sortiert werden soll, kann das auch gleich am Anfang mit der integrierten Funktion erledigt werden:

arr = Application.WorksheetFunction.Unique(arr) arr = Application.WorksheetFunction.Sort(arr, 2)

In dem Beispiel wäre der frische Array nach den Namen sortiert.

Arrayformeln (05): SORTIEREN/SORTIERENNACH (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 117) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Funktionen SORTIEREN()/SOERIERENNCH() (in VBA) nutzen?

Sortieren

Die Funktion SORTIEREN(), die ebenfalls ab Excel 365 zu Verfügung steht, ist vom Prinzip her identisch mit EINDEUTIG(), nur dass sie nicht filtert, sondern sortiert. In der Funktion übergibt man den Bereich und als zweiten Parameter den Index der Spalte, ausgegeben wird der sortierte Bereich.

Beispiel für eine Formel mit dieser Funktion, die nach der zweiten Spalte sortiert:

=SORTIEREN(A2:E15;2)

Mehr Informationen dazu gibt es bei Microsoft: SORTIEREN-Funktion

Auch die Verwendung in VBA ist identisch; die Rückgaben der Arrays und deren Auswertung unterscheiden sich nicht.


Sortierennach

Verfeinerte Sortiermöglichkeiten bietet diese Funktion, weil sie nach verschiedenen Spalten sortieren kann und auf diese Spalten auch andere Funktionen angewendet werden können (siehe auch SORTIERENNACH-Funktion). So sortiert diese Formel den Bereich A2:E15:

=SORTIERENNACH(A2:E15;JAHR(D2:D15);1;E2:E15;1)

Sortiert wird dabei zunächst nach dem Jahr in D2:D15 und zwar aufsteigend: JAHR(D2:D15);1. Anschließend wird nach den Beträgen in E2:E15 sortiert, ebenfalls aufsteigend: E2:E15;1.

Diese Formel sortiert nach den Wochentagen in D2:D15 aufsteigend und anschließend ebenfalls nach den Beträgen:

=SORTIERENNACH(A2:E15;WOCHENTAG(D2:D15);1;E2:E15;1)


VBA

In VBA kann es beim Einsatz leicht zu Fehlermeldungen kommen, wenn Funktionen auf die Bereiche angwendet werden, nach denen sortiert werden soll. Soll z. B. einfach nur nach den Datumsangaben in D2:D15 sortiert werden, reicht folgende Anwendung:

Dim a, b a = Range("A2:E15") b = Range("D2:D15") arr = Application.WorksheetFunction.SortBy(a, b, 1)

Wird jedoch bei b eine Funktion verwendet, schimpft Excel schnell. Abhilfe schafft hier, die Formel als String zusammenzustellen und diesen mit EVALUATE() berechnen zu lassen:

Dim strFormel As String strFormel = "=sortby(A2:E15, year(D2:D15), 1, E2:E15,1)" arr = Evaluate(strFormel)

Dieses Beispiel entspricht dem ersten Formelbeispiel. Die Auswertung bzw. weitere Verarbeitung des Ergebnisarrays würde dann wie bei EINDEUTIG() erfolgen; an die Stellen der MsgBoxen müsste der Code, der das Weitere erledigen soll:

Sub Sortierennach_vba() Dim arr, intI As Integer, intAnzahlEl As Integer Dim strFormel As String strFormel = "=sortby(A2:E15, year(D2:D15), 1, E2:E15,1)" arr = Evaluate(strFormel) intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(2) & ", " & arr(3) & ": " & Format(arr(4), "DD.MM.YYYY") Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 2) & ", " & arr(intI, 3) & ": " & Format(arr(intI, 4), "DD.MM.YYYY") Next End If End Sub

Arrayformeln (06): FILTER (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 127) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Daten per Funktion filtern (und mit VBA auf Ergebnisse zugreifen)?

Datenfilter, Autofilter und seit Excel 365 nun auch eine Funktion FILTER() - verschiedene Möglichkeiten gibt es ja. Was konkret verwendet wird, wird sich immer nach den konkreten Umständen richten müssen. Mit der Funktion haben wir nun eine schnelle und sehr flexible Variante, die vor allem auch bei der Nutzung durch VBA dank reduzierter Ergebnismengen zu besseren Laufzeiten führen kann. Ausführliche Informationen gibt es natürlich bei Microsoft: FILTER-Funktion.

Der Einsatz in einer Formel ist sehr einfach: Das folgende Beispiel nimmt den Bereich A2:F15, sieht dort in D2:D15 nach, wo "Berlin" enthalten ist und gibt diese Zeilen als Bereich aus:

=FILTER(A2:F15;D2:D15="Berlin";"Nichts gefunden")

Mehrere Suchkriterien können durch das +-Zeichen für Oder bzw. das *-Zeichen für Und verknüpft werden. So sucht die folgende Formel nach Einträgen, die "Berlin" oder "Hamburg" enthalten:

=FILTER(A2:F15;(D2:D15="Berlin")+(D2:D15="Hamburg");"Nix gefunden!")

Diese Formel sucht nach Zeilen, die als Ort "Berlin" enthalten und ein Datum aus dem Jahr 2015 haben und deren Beträge größer als 200 sind:

=FILTER(A2:F15;(D2:D15="Berlin")*(JAHR(E2:E15)=2015)*(F2:F15>200);"Nix gefunden!")

Verknüpfungen von Und und Oder sind natürlich auch möglich. Dabei muss allerdings auf die richtige Klammersetzung geachtet werden. Die nächste Formel sucht Einträge mit ("Berlin oder "Hamburg") und aus dem Jahr 2015 und mit Beträgen größer als 200:

=FILTER(A2:F15;((D2:D15="Berlin")+(D2:D15="Hamburg"))*(JAHR(E2:E15)=2015)*(F2:F15>200);"Nix gefunden!")


VBA

Der Einsatz in VBA ist nahezu identisch, wenn mit EVALUATE() gearbeitet wird. Die Formel wird dazu als String zusammengesetzt und mit Evaluate wird diese Berechnung durchgeführt:

strFormel = "=filter(A2:E15, D2:D15=""Berlin"", """")" arr = Evaluate(strFormel)

Die weitere Verarbeitung des Ergebnisarrays erfolgt wie bei der Funktion EINDEUTIG() unter Weiterverarbeitung des Ergebnisarrays beschrieben. Hier ein Beispiel im Ganzen, das die letzte Formel nutzt:

Sub Filter_vba() Dim arr, intI As Integer, intAnzahlEl As Integer Dim strFormel As String strFormel = "=FILTER(A2:F15,((D2:D15=""Berlin"")+(D2:D15=""Hamburg""))*(year(E2:E15)=2015)*(F2:F15>200),""Nix gefunden!"")" arr = Evaluate(strFormel) intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(2) & ", " & arr(3) & ": " & Format(arr(4), "DD.MM.YYYY") Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 2) & ", " & arr(intI, 3) & ": " & Format(arr(intI, 4), "DD.MM.YYYY") Next End If End Sub



Arrayformeln (07): SEQUENZ (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 130) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich eine Reihe von Zahlen mit bestimmten Abständen erhalten?

Das Erstellen von Zahlenreihen mit bestimmten Abständen ist seit Excel 365 denkbar einfach - es gibt ja die Funktion SEQUENZ(). Die folgende Formel liefert eine Liste über 15 Zeilen in einer Spalte, beginnend mit 1 und mit einem Abstand von 1:

=SEQUENZ(15;1;1;1)

Benötigen Sie eine Reihe von Datumsangaben für Ihren 14-tägigen Urlaub, können Sie das verwenden:

=SEQUENZ(14;1;"05.02.2019";1)

Natürlich müssen die Zahlen als Datum formatiert werden.

Auch Uhrzeiten sind möglich - hier ab 08:00 mit einem Abstand von einer halben Stunde (als Zeit formatieren):

=SEQUENZ(17;1;"08:00";"00:30")


Verwendung in VBA

In VBA wird die Funktion am besten mit Application.WorksheetFunction.Sequence genutzt, um den Array mit den Zahlen zu erhalten. Beispiele:

  • arr = Application.WorksheetFunction.Sequence(10, 1, "08:00", "00:30")
  • arr = Application.WorksheetFunction.Sequence(10, 1, CDate("05.02.2019"), 1)

Verwendung/Verarbeitung des Ergebnisarrays

Bei der Verarbeitung des Ergebnisarrays ist darauf zu achten, dass die einzelnen Zahlen als Zeilen vorliegen - Ubound ist also die Zeilenzahl. Wird nur eine Spalte verwendet (immer die 1 in der Funktion), ist der Ubound auch gleichzeitig die Anzahl der Zahlen. In diesem Fall muss also auf die erste Spalte jeder Zeile zugegriffen werden:

For intI = 1 To UBound(arr) MsgBox intI & ": " & arr(intI, 1) Next

Hinweis: Bei Ausgaben von Datumsangaben und Zeiten müssen diese noch formatiert werden, zum Beispiel Format(arr(intI, 1), "hh:nn") für die Zeitangaben.

Etwas anders verhält es sich, wenn die Sequenz mehrspaltig erstellt wurde, zum Beispiel mit zwei Spalten:

arr = Application.WorksheetFunction.Sequence(10, 2, "08:00", "00:30")

Dann wird mit arr(intI, 1) nur die erste Spalte verarbeitet; bei einer Ausgabe wären das nur die vollen Stunden. In dem Fall muss dann auch auf die zweite Spalte zugegriffen werden:

MsgBox intI & ": " & Format(arr(intI, 1), "hh:nn") & vbTab & Format(arr(intI, 2), "hh:nn")

Das letzte Beispiel im Ganzen zum Testen:

Sub Sequenz_vba() Dim arr, intI As Integer arr = Application.WorksheetFunction.Sequence(10, 2, "08:00", "00:30") For intI = 1 To UBound(arr) MsgBox intI & ": " & Format(arr(intI, 1), "hh:nn") & vbTab & Format(arr(intI, 2), "hh:nn") Next End Sub

Arrayformeln (08): ZUFALLSMATRIX (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 135) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich zufällige Zahlen erzeugen (und in VBA verarbeiten)?

Funktionen für Zufallszahlen bzw. Möglichkeiten, sie errechnen zu lassen, gab es ja schon vor Excel 365. Seit dieser Excelversion gibt es nun auch ZUFALLSMATRIX (), die den Umgang damit etwas vereinfacht. Statt mit Rnd() und * Obergrenze und + Obergrenze zu rechnen, notiert man diese Anforderungen einfach als Parameter in der Funktion selbst. Eine Beschreibung im Detail gibt es bei Microsoft: ZUFALLSMATRIX-Funktion.

Bei dieser Funktion ist man nicht nur auf Zahlen beschränkt, die als solche aussehen, sondern es können auch zufällige Datumsangaben und Uhrzeiten erstellt werden:

  • ZUFALLSMATRIX(15;1;"1.1.2020";"15.06.2020";WAHR)
  • ZUFALLSMATRIX(15;1;"08:00";"16:00")

Zwei Dinge müssen hier jedoch beachtet werden:

Der letzte Parameter [ganze_zahl] muss bei Uhrzeiten weggelassen oder auf FALSCH bzw. 0 gesetzt werden, da die reinen Zahlen bei Uhrzeiten immer zwischen 0 und 1 sind.

Wie bei allen Arrayformeln nimmt Excel dabei keine Formate mit. Datumsangaben und Zeiten müssen also i. d. R. im Ergebnis neu formatiert werden. Hilfreich kann da der Pinsel Format übertragen sein.

Das Sortieren der Zahlen geht einfach, indem die Funktion SORTIEREN() verwendet wird:

=SORTIEREN(ZUFALLSMATRIX(15;1;"1.1.2020";"15.06.2020";1))


Verwendung in VBA

In VBA wird ein Array durch Nutzung der Funktion so erzeugt, wie es bei der reinen Formel ist. Beispiele:

  • arr = Application.WorksheetFunction.RandArray(10, 1, CDate("1.1.2020"), CDate("15.06.2020"))
  • arr = Application.WorksheetFunction.RandArray(10, 1, CDate("08:00"), CDate("16:00"))

Die Verarbeitung des Ergebnisarrays erfolgt so, wie es bei SEQUENZ() beschrieben ist. Die einzelnen Daten liegen also in Zeilen vor; bei einer Spalte ist dies der Ubound. Also muss auch auf die erste Spalte zugegriffen werden:

For intI = 1 To UBound(arr) MsgBox intI & ": " & Format(arr(intI, 1), "DD:MM.YYYY") Next

Im Beispiel ist auch schon zu sehen, dass etwaige Ausgaben formatiert werden müssen.

Werden mehrere Spalten erzeugt, muss das beim Zugreifen auf die Arrayelemente beachtet werden:

For intI = 1 To UBound(arr) MsgBox intI & ": " & Format(arr(intI, 1), "hh:nn") & vbTab & Format(arr(intI, 2), "hh:nn") Next

Wie bei der Formellösung kann der Array auch sortiert werden:

arr = Application.WorksheetFunction.Sort(arr)

Auch hier muss natürlich beachtet werden, dass die Daten zweispaltig sein können, wenn der zweite Parameter in der Funktion entsprechend gesetzt ist. Nach der zweiten Spalte würde dann so sortiert:

arr = Application.WorksheetFunction.Sort(arr, 2)

Ob das Sinn macht, ist allerdings eine andere Frage.

Arrayformeln (09): Application.WorksheetFunction zu lang?Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/MatrixfunktionTipp

Kategorien: Arrays ▸ Arrayformeln und VBE

(Tipp 143) Nachricht zum Beitrag an Autor Nach oben

Muss ich bei den Arrayfunktionen immer Application.WorksheetFunction schreiben?

Beim häufigen Einsatz von Arrayfunktionen in VBA kann es durch die ständige Verwendung von Application.WorksheetFunction natürlich auch schnell unübersichtlich werden. Abhilfe schafft, das als Object zu deklarieren und sich in der Folge immer darauf zu beziehen.

So könnte man wksFkt als Objektvariable verwenden und so deklarieren:

Dim wksFkt As Object Set wksFkt = Application.WorksheetFunction

In der Folge kann das so verwendet werden:

arr = wksFkt.RandArray(5, 1, CDate("08:00"), CDate("16:00")) arr = wksFkt.Sort(arr)

Der Vollständigkeit halber sollte der Speicher aber auch wieder freigegeben werden:

Set wksFkt = Nothing

Arrayformeln (10): Raute # - Verwenden des ErgebnisarraysMakro/Sub/ProzedurFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 144) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich (auch per VBA) auf das Ergebnis einer Arrayformel auf dem Tabellenblatt zugreifen?

Das Ergebnis einer Arrayformel (seit Excel 365) auf dem Blatt wird rechts und unterhalb der Zelle mit der Formel eingetragen. Es fließt also um diese eine Zelle, während die Formel nur in einer Zelle steht.

Soll nun bei weiteren Berechnungen auf das komplette Ergebnis der Arrayformel zugegriffen werden, reicht es nicht, sich auf die Zelle mit der Formel zu beziehen. Die Folge wäre, dass nur der Wert (also das Ergebnis) aus dieser einen Zelle - der mit der Formel - verwendet würde, nicht aber das gesamte Ergebnis.

Das Beziehen auf den Bereich mit dem Ergebnis, zum Beispiel F3:G14, würde zunächst einen Erfolg liefern. Allerdings wird das Problem ersichtlich, wenn sich im Ergebnis, das man verwenden will, die Ergebnismenge ändert, indem zum Beispiel eine Zeile mehr in der Basistabelle plötzlich das Filterkriterium erfüllt. In dem Fall bliebe der Bezug auf den Bereich F3:G14 bestehen - das zusätzliche Ergebnis aus der Berechnung bliebe unberücksichtigt.

Aus diesem Grund arbeitet Excel mit Referenzen auf komplette Ergebnisse. Eine solche Adresse sieht man, wenn man irgendwo ein Gleichheitszeichen eingibt und anschließend über den Bereich mit dem Ergebnis der Berechnung zieht - Excel trägt dann in die Formel einen Bezug wie E3# ein. Dabei handelt es sich um die Zelle mit der Formel und das Rautezeichen signalisiert, dass das Ergebnis dieser Berechnung für weitere Berechnungen gewünscht ist.

Im Beispiel auf der Grafik ist die Basistabelle in A3:C14. In E3 befindet sich die Formel zum Sortieren dieser Tabelle:

=SORTIEREN(A3:C14)

Das Ergebnis fließt nun als Ergebnis um die Zelle E3.

Nun soll diese sortierte Ergebnismenge In I3 nach dem Monat Juni gefiltert werden. Es soll sich dabei also auf das vorherige Ergebnis bezogen werden. In die FILTER-Funktion wird deshalb nicht E3 als Matrix eingetragen, auch nicht E3:G14, wo sich das komplette Ergebnis befindet. Verwendet wird als Matrix E3#, damit die Filter-Funktion weiß, dass es sich um ein dynamisches Ergebnis handelt. Der Rest bleibt wie gehabt:

=FILTER(E3#;F3:F14="Juni")

Wenn nun in der Basistabelle ein weiterer Monat auf Juni geändert wird, passt sich das Ergebnis auch beim Filtern dynamisch an und wird erweitert.


Verwenden eines Arrayergebnisses in VBA

In VBA kann dieser Bezug ebenfalls verwendet werden, indem er im Range-Objekt als Adresse angegeben wird:

Dim arr, intI As Integer arr = Range("I3#") For intI = 1 To UBound(arr) MsgBox intI & ": " & arr(intI, 1) & vbTab & arr(intI, 2) & vbTab & arr(intI, 3) Next

Auch mit EVALUATE ist es möglich:

Dim arr, intI As Integer arr = Evaluate("I3#") For intI = 1 To UBound(arr) MsgBox intI & ": " & arr(intI, 1) & vbTab & arr(intI, 2) & vbTab & arr(intI, 3) Next