Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

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

UDF - Benutzerdefinierte Funktionen (auch Matrixfunktionen)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionTipp

Kategorien: Basics ▸ UDF und UDF ▸ Basics

(Tipp 164) Nachricht zum Beitrag an Autor Nach oben

Grundsätzliches

Benutzerdefinierte Funktionen, also UDF, sind Funktionen, die man sich selbst im VBA-Editor erstellt. Dazu benennt man sie nicht mit Sub, sondern mit Function. Der Name der Funktion liefert dabei den Rückgabewert. Heißt also eine Funktion "MeineFunktion(...)", schreibt man in die Zelle "=MeineFunktion(...)". Ebenfalls kann man Funktionen einsetzen, um durch Subs bestimmte Berechnungen durchführen zu lassen.

Beispiel: Eine Funktion, die immer das Datum zurückgibt, das in 14 Tagen liegt, Die Funktion braucht keine Parameter, sie wäre schlicht und einfach:

Function Datum_14Tage() Datum_14Tage = Date + 14 End Function

Darauf können wir einfach per Sub zugreifen:

Sub Datumstest() MsgBox Datum_14Tage End Sub

Oder in die Zelle eingetragen:

=Datum_14Tage()


Ergebnisse/Rückgaben

Prinzipiell können benutzerdefinierte Funktionen alle möglichen Datentypen als Rückgabewerte haben: Strings, Zahlen, Datumsangaben usw. Selbst Arrays können Ergebnisse sein, die dann von anderen Subs verarbeitet werden.


Dynamische Arrayformeln/Matrixfunktionen

Seit Excel 365 ist es sogar möglich, Arrays als Ergebnisse solcher (dann Matrix-) Funktionen in Zellen eintragen zu lassen. Nehmen wir folgende Funktion als Beispiel, die für einen Monat eine kleine Liste mit Wochentagen und Kalenderwochen erstellt:

Function Monatstabelle(ByVal intMonatszahl As Integer, ByVal intJahr As Integer) Dim datDatum As Date, arrS(), lngArr As LongPtr datDatum = CDate("1." & intMonatszahl & "." & intJahr) lngArr = 0 Do lngArr = lngArr + 1 ReDim Preserve arrS(1 To 3, 1 To lngArr) arrS(1, lngArr) = datDatum arrS(2, lngArr) = Format(datDatum, "DDD") arrS(3, lngArr) = Application.WorksheetFunction.IsoWeekNum(datDatum) datDatum = datDatum + 1 Loop While Month(datDatum) = intMonatszahl Monatstabelle = Application.WorksheetFunction.Transpose(arrS) End Function

Sie erwartet als Parameter die Zahl des Monats und das Jahr. Wenn diese Angaben in D1 und in E1 stehen, können wir in der Tabelle diese Arrayformel eintragen:

=Monatstabelle(D1;E1)

Tipp nebenbei: Mit Transpose oder in deutscher Syntax MTRANS kann eingestellt werden, in welcher Richtung ein Array ausgegeben wird. Diese (eigentlich Tabellenblatt-) Funktion transponiert den Array, so dass dieser wahlweise über Spalten oder über Zeilen ausgegeben wird.


Formel übergelaufen

Beim ersten Verwenden einer Matrixfunktion (also nicht nur einer eigenen) erscheint die Meldung:

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

Diese Meldung besagt nichts anderes, als dass sich die Zelle mit der Formel nun verhält, wie man es in CSS mit float: left; beschreiben würde. Die Ergebnisse der Formel fließen rechts und unterhalb von der Eingabezelle.

Mit dem Schnittmengenoperator @ nach dem Gleichheitszeichen können Sie übrigens einstellen, dass nur der erste Wert des Arrays in der Zelle erscheint.


Um zu gewährleisten, dass eine in einem Tabellenblatt eingesetzte UDF immer rechnet, sollte man an den Anfang der Funktion schreiben:

Application.Volatile

Verzeichnis auslesen (Makro und Funktion)Makro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorien: Dateien und Ordner ▸ Ordner und Dateien und Ordner ▸ Dateien

(Tipp 32) Nachricht zum Beitrag an Autor Nach oben

Wie kann man mit VBA ein Verzeichnis mit allen darin befindlichen Dateien auslesen?

An der Stelle des Sternchens in Dir(strOrdner & "*.*") können auch bestimmte Dateiendungen eingetragen wreden, so dass nur nach Dateien eines Typs gesucht wird.

Subs

Sollen Dateien nur direkt im Ordner, nicht aber in Unterordnern, gesucht werden, reicht dieser Code:

Sub Suchen_nur_Dateien() Dim strOrdner As String, strDatei As String Dim lngZ As LongPtr strOrdner = "C:\Eigene Dateien\" lngZ = 2 Range("a1:e50000").ClearContents strDatei = Dir(strOrdner & "*.*") Do While strDatei <> "" If strDatei <> "" Then lngZ = lngZ + 1 Cells(lngZ, 1) = strOrdner & strDatei 'Pfad Cells(lngZ, 2) = FileLen(strOrdner & strDatei) 'Größe Cells(lngZ, 3) = FileDateTime(strOrdner & strDatei) 'Datum/Zeit Cells(lngZ, 4) = strDatei 'nur Dateiname End If strDatei = Dir Loop End Sub

Soll auch in Unterordnern gesucht werden, ist dies eine Möglichkeit:

Private lngZ As LongPtr Sub Suchen_mit_Unterordnern() 'Aufruf lngZ = 2 Range("a1:e50000").ClearContents Dateisuche "C:\Eigene Dateien", "*.*" End Sub Sub Dateisuche(strOrdner As String, strDateien As String) Dim strTemp As String, strWdhlg As String strOrdner = strOrdner & IIf(Right(strOrdner, 1) <> "\", "\", "") strTemp = Dir(strOrdner & strDateien) Do While Len(strTemp) Cells(lngZ, 1) = strOrdner & strTemp 'Pfad Cells(lngZ, 2) = FileLen(strOrdner & strTemp) 'Größe Cells(lngZ, 3) = FileDateTime(strOrdner & strTemp) 'Datum/Zeit Cells(lngZ, 4) = strTemp 'nur Dateiname lngZ = lngZ + 1 strTemp = Dir() Loop strTemp = Dir(strOrdner, vbDirectory) Do While Len(strTemp) If (strTemp <> ".") And (strTemp <> "..") Then If (GetAttr(strOrdner & strTemp) And vbDirectory) = vbDirectory Then Dateisuche strOrdner & strTemp, strDateien lngZ = lngZ - 1 strWdhlg = Dir(strOrdner, vbDirectory) lngZ = lngZ + 1 Do While strWdhlg <> strTemp: strWdhlg = Dir(): Loop End If End If strTemp = Dir() Loop On Error GoTo 0 End Sub


Matrixfunktion für dynamische Arrayformel

Wenn es nicht zu viele Dateien sind, können die auch mit einer Formel, die in einer Zelle steht, ausgegeben werden (ab Excel 365). Dazu dient folgende benutzerdefinierte Funktion (UDF):

Function Suchen_nur_Dateien(strOrdner) Dim strDatei As String, arrTemp() Dim lngArr As LongPtr lngArr = 0 strDatei = Dir(strOrdner & "*.*") Do While strDatei <> "" If strDatei <> "" Then lngArr = lngArr + 1 ReDim Preserve arrTemp(1 To 4, 1 To lngArr) arrTemp(1, lngArr) = strOrdner & strDatei 'Pfad arrTemp(2, lngArr) = FileLen(strOrdner & strDatei) 'Größe arrTemp(3, lngArr) = FileDateTime(strOrdner & strDatei) 'Datum/Zeit arrTemp(4, lngArr) = strDatei 'nur Dateiname End If strDatei = Dir Loop Suchen_nur_Dateien = arrTemp End Function

Wenn in A1 der Ordner (z. B. C:\Eigene Dateien\) steht, kann in eine andere Zelle folgende Formel eingetragen werden:

=MTRANS(Suchen_nur_Dateien(A1))

Damit werden die gefundenen Dateien ab der Zelle mit der Formel gefloatet eingetragen (verschüttet). Auch die Suche mit den Unterordnern lässt sich in dieser Form gestalten - aber Vorsicht, damit die Formel nicht ewig zum Berechnen braucht.

Von Spalten zu ZeilenFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Tabelle ▸ Matrix

(Tipp 238) Nachricht zum Beitrag an Autor Nach oben

Wie können Daten in Spalten zu Daten in Zeilen umgewandelt werden?

  1. Die ganze Tabelle markieren,
  2. Kopieren,
  3. neue Tabelle aktivieren,
  4. A1 aktivieren,
  5. Inhalte einfügen,
  6. Transponieren ankreuzen.

Dynamische Arrayformel

Ab Excel 365 kann die Tabelle auch einfach per Formel gedreht werden. Dazu die Funktion MTRANS() verwenden und den Bereich der Tabelle angeben:

=MTRANS(E1:I3)

Zufallszahlen sortiert in einem bestimmten Bereich generieren (mit Arrayfunktion)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Filter/Sortieren

(Tipp 139) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich zwischen 6 und 15 Zufallszahlen zwischen 1 und 49 generieren? Die Zahlen sollen auf dem Blatt Tab1 in der Zeile 1 ab A1 stehen. Keine Zahl darf sich wiederholen.

Dazu gibt es verschiedene Möglichkeiten.

Direkt in Zellen eintragen

In der ersten Variante erfolgt die Arbeit direkt an den Zellen, weil hier die Funktionen Finden und Sortieren von Excel genutzt werden. Funktionen, die Excel zur Verfügung stellt, sind i. d. R. recht schnell, so dass das in diesem Fall sicher die kürzere und schnellere Variante ist:

Sub Zufall() Dim intWert As Integer, varWieviele, intI As Integer Dim bolVorhanden As Boolean, rngGef As Range varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub Sheets("Tab1").Range("A1:O1").ClearContents For intI = 1 To varWieviele intWert = Int((49 * Rnd) + 1) If intI = 1 Then Sheets("Tab1").Cells(1, intI) = intWert Else Do bolVorhanden = False Set rngGef = Range(Cells(1, 1), Cells(1, intI - 1)).Find(intWert) If Not rngGef Is Nothing Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) End If Loop While bolVorhanden = True Sheets("Tab1").Cells(1, intI) = intWert End If Next Sheets("Tab1").Range("A1:O1").Sort Key1:=Sheets("Tab1").Range("A1"), Order1:=xlAscending, Orientation:=xlLeftToRight End Sub

Kern des Codes ist eine Schleife, die so lange läuft, wie eine Zufallszahl nicht mehr in den bisherigen Zufallszahlen gefunden wird. Erst dann wird sie als neue Zufallszahl verwendet.

Weiteres Beispiel - Schleife statt Find:

Sub Zufall() Dim intAnzahl As Integer, intMax As Integer, intMin As Integer Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean intAnzahl = 10 intMax = 49 intMin = 1 'alte Zahlen löschen Range(Cells(1, 2), Cells(intAnzahl, 2)).ClearContents Cells(1, 2) = Int((intMax * Rnd) + intMin) '1. Zahl erzeugen For intI = 2 To intAnzahl 'nächste Zahlen erzeugen Do bolVorhanden = False intWert = Int((intMax * Rnd) + intMin) For intN = 1 To intI 'Kontrolle ob schon vorhanden If Cells(intN, 2) = intWert Then bolVorhanden = True Exit For End If Next Loop While bolVorhanden = True Cells(intI, 2) = intWert 'Zahl eintragen Next End Sub


Erst Array, zum Schluss in Zellen

Manchmal kann die Arbeit mit Zellen aber auch von Nachteil sein. Deshalb ist hier der Vollständigkeit halber noch ein Beispiel, in dem Herangehensweisen mit einem Array aufgezeigt werden:

Sub Zufall1() Dim intWert As Integer, intI As Integer, intN As Integer, varWieviele Dim bolVorhanden As Boolean, bolSortiert As Boolean Dim arrZahlen() varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub ReDim Preserve arrZahlen(varWieviele) For intN = 0 To varWieviele - 1 intWert = Int((49 * Rnd) + 1) If intN = 0 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 0 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Do bolSortiert = True For intN = 0 To varWieviele - 2 If arrZahlen(intN + 1) < arrZahlen(intN) Then bolSortiert = False intWert = arrZahlen(intN + 1) arrZahlen(intN + 1) = arrZahlen(intN) arrZahlen(intN) = intWert End If Next Loop While bolSortiert = False Sheets("Tab1").Range("A1:O1").ClearContents For intN = 0 To varWieviele - 1 Sheets("Tab1").Cells(1, intN + 1) = arrZahlen(intN) Next End Sub

Das gesamte Erstellen der Zufallszahlen - bis hin zum Sortieren - erfolgt zunächst in einem Array. Auch hier erfolgt die Prüfung auf Doppelungen. Aber es ist zu sehen, dass dafür eine weitere Schleife eingebaut ist. Eine Funktion wie in PHP in_array() wäre da natürlich günstiger.

Auch zum Sortieren wird die innere Schleife so lange durchlaufen, bis im Array das nächste Element nicht kleiner als das gerade durchlaufene Element ist.

Erst ganz zum Schluss wird der fertige Array in die Zellen übernommen.

Die zweite Variante hat den Vorteil, dass sie beliebig - und unabhängig von Zellen - eingesetzt werden kann. Sie kann auch als eigenständige Funktion erstellt werden, die dann den Array mit den Zufallszahlen zurückgibt. So könnte die Funktion von überall aufgerufen werden; in die Zellen würde dann das Ergebnis eingetragen.

Oft stößt man bei Schleifen, die Zellen lesen und schreiben, auf das Problem, dass die Laufzeit enorm steigt. Deshalb sollte zumindest in Betracht gezogen werden, das Ganze mit Arrays zu erledigen und die Zellzugriffe auf ein Minimum zu beschränken.


Dynamische Arrayformel mit Matrixfunktion (ab Excel 365)

Natürlich kann das auch als Matrixfunktion für eine dynamische Arrayformel erstellt werden. Die Funktion erstellt einen Array mit eindeutigen Zahlen. Zum Schluss wird mit der Arrayfunktion SORTIEREN() sortiert und der Array ausgegeben.

Function Zufallszahlen_Eindeutig(ByVal intAnzahl As Integer, ByVal intMin As Integer, ByVal intMax As Integer) Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean, arrZahlen() Application.Volatile ReDim Preserve arrZahlen(1 To intAnzahl) For intN = 1 To intAnzahl intWert = Int((intMax * Rnd) + intMin) If intN = 1 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 1 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((intMax * Rnd) + intMin) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Zufallszahlen_Eindeutig = Application.WorksheetFunction.Sort(arrZahlen, 1, 1, 1) End Function

In die Zelle kommt einfach:

=Zufallszahlen_Eindeutig(Anzahl;Minimum;Maximum)

=Zufallszahlen_Eindeutig(5;1;49)

Sollen die Zahlen untereinander erscheinen, kann die Funktion in MTRANS() gesetzt werden:

=MTRANS(Zufallszahlen_Eindeutig(5;1;49))

Die Funktion ZUFALLSMATRIX() klingt danach, als ob sie die Aufgabe auch erfüllen könnte. Jedoch hat sie bei dieser Aufgabenstellung den großen Nachteil, dass sie sehr häufig mehrfach vorhandene Zahlen erzeugt. Das heißt, dass man so oft berechnen lassen müsste, bis jede Zahl eindeutig ist - und das kann etwas dauern.

Apropos eindeutig: Mit der Funktion EINDEUTIG() könnte natürlich geprüft werden, ob eindeutige Zufallszahlen vorliegen. Allerdings ist die Funktion manchmal schneller als die Sortieren-Funktion, so dass letztere Funktion noch rechnet, wenn EINDEUTIG() schon fertig ist. Das führt dann zum bekannten Fehler ÜBERLAUF.