Zufällig auswählen mit VBA

Forumthread: Zufällig auswählen mit VBA

06.11.2014 13:18:42

mpb

Hallo,
folgendes stark vereinfachtes Beispiel:
In den Zellen A1 bis B10 stehen folgende Werte:
10 20
11 23
12 13
13 7
14 13
15 19
16 1
17 0
18 10
19 10
In Spalte A steht die Beschäftigtenzahl, in Spalte B die Anzahl der Betriebe mit 10, 11, usw. Beschäftigten. In meinem Besipiel wären das 116 Betriebe. Ich möchte nun mit VBA von diesen 116 Betrieben 3 (kann auch eine andere Zahl sein) zufällig auswählen, und zwar ohne zurücklegen. Für die 3 ausgewählten Betriebe möchte ich die Zahl der Beschäftigten als Summe zurückgeben.
Bespiel: Die Zufallsauswahl zieht je einen Betrieb aus B1, B4 und B9. Gewünschtes Ergebnis: 41.
Es ist selbstverständlich möglich, dass mehr als ein Betrieb aus der gleichen Zelle gezogen wird. Aus B7 kann aber in meinem Beispiel nur ein Betrieb gezogen werden, da es nur einen gibt. Es müsste also kontrolliert werden, dass noch "genügend" Betriebe in einer Zelle vorhanden sind.
Ich könnte theoretisch meine Tabelle auch so aufbauen, dass ich die Daten nicht in 10 Zeilen aggregiert eintrage, sondern (im Beispiel) für die 116 Betriebe in 116 Zellen die Beschäftigtenzahl eintrage und daraus zufällig 3 Zellen auswähle. Da ich aber insgesamt ca. 8000 solcher "Blöcke" wie in meinem Bespiel habe, wäre der Aufwand unverhältnismäßig.
Gruß
Martin

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Zufällig auswählen mit VBA

06.11.2014 13:34:03

Michael
AW: Zufällig auswählen mit VBA

06.11.2014 16:54:32

mpb
AW: Zufällig auswählen mit VBA

06.11.2014 19:17:00

Christian

AW: Zufällig auswählen mit VBA

06.11.2014 13:34:03

Michael

Hallo mpb!
Ich möchte Dir gerne helfen, da ich mich unlängst auch mit Zufallsauswahl per VBA beschäftigt habe, aber mir ist nicht klar, was Du machen möchtest. Kannst Du Deine Frage präzisieren bzw. einmal anders formulieren?
Aktuell glaube ich folgendes zu verstehen: Du willst aus einer endlichen Anzahl von Nummern (Betriebe 1 bis X) zufällig drei Nummern (= drei Betriebe gemäß ihrer Positionsnummer in der Liste) auswählen. Dann soll angezeigt werden, wieviele Mitarbeiter die drei gewählten Betriebe für sich/bzw. in Summe haben.
Da mir nicht klar ist, wie Deine Daten aufgebaut sind, nur eine Idee: Alle Betriebe in einer Liste erfassen und laufend durchnummerieren, in der Nebenspalte die jeweilige Mitarbeiterzahl anführen. Dann ließe sich mittels "=Zufallsbereich()" und "=SVerweis" und "=Summe" vermutlich bald eine Lösung finden.
Lg
Michael

AW: Zufällig auswählen mit VBA

06.11.2014 16:54:32

mpb

Hallo Michael,
die Anordnung der Daten kann/möchte ich nicht ändern (siehe Eingangspost).
Was ich erreichen möchte, ist Folgendes:
1. Zunächst müsste ich die Zahl der Betriebe ermitteln, also so etwas wie =SUMME(B1:B10). Ergebnis im Beispiel: 116.
2. Dann muss ich z.B. nacheinander 3 im Bereich 1 bis 116 gleichverteilte Zufallszahlen ziehen. Ergebnis z.B. 50, 7 und 81.
3. Nun gehe ich die Zellen B1:B10 durch und schaue nach, in welche Zelle der 50., 7. und 81. Betrieb fällt und wieviele Beschäftigte er hat.
50. Betrieb: Zelle B3, 12 Beschäftigte (aus Zelle A3)
7. Betrieb: Zelle B1, 10 Beschäftigte (aus Zelle A1)
81. Betrieb: Zelle B6, 15 Beschäftigte (aus Zelle A6)
Die mich interessierende Summe ist also 37 Beschäftigte.
Und das ganze per VBA und bei ca. 8000 "Datenblöcken" mit unterschiedlichen Zeilenanzahlen.
Ich hoffe, es ist jetzt klarer.
Gruß
Martin

AW: Zufällig auswählen mit VBA

06.11.2014 19:17:00

Christian

Hallo Martin,
vermutlich stehst du ja einfach nur auf dem Schlauch, denn bei "VBA gut" sollte man IMO darüber nicht mehr nachdenken müssen...
Mein Vorschlag:
- schreib die 116 Werte in eine Collection
- wähle nacheinander per Rnd 3 Werte und lösche den Wert direkt danach aus der Collection.
- rechne den Wert zur Gesamtsumme dazu
Bsp:

Option Explicit
Sub TestIt()
Dim i As Long, j As Long
Dim lngRnd As Long, lngItm As Long, lngSum As Long
Dim lngRes(2) As Long
Dim col As New Collection
Randomize
With Sheets("Tabelle1")
For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
For j = 1 To .Cells(i, 2)
col.Add .Cells(i, 1).Value
Next
Next
For i = 0 To UBound(lngRes)
lngRnd = Int(col.Count * Rnd + 1)
lngItm = col(lngRnd)
col.Remove lngRnd
lngSum = lngSum + lngItm
Next
Debug.Print lngSum
End With
End Sub

Kommst du damit klar?
Gruß
Christian

Infobox / Tutorial

Zufällige Auswahl von Werten mit VBA in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Daten vorbereiten: Stelle sicher, dass deine Daten in zwei Spalten angeordnet sind. In Spalte A sollten die Beschäftigtenzahlen stehen und in Spalte B die Anzahl der Betriebe mit den jeweiligen Beschäftigten.
VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Modul hinzufügen: Klicke mit der rechten Maustaste auf „VBAProject (DeineDatei.xlsm)“ und wähle Einfügen > Modul.

Code einfügen: Kopiere den folgenden VBA-Code und füge ihn in das Modul ein:

Option Explicit
Sub Zufallsauswahl()
   Dim i As Long, j As Long
   Dim lngRnd As Long, lngItm As Long, lngSum As Long
   Dim col As New Collection
   Randomize

   With Sheets("Tabelle1")
       For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
           For j = 1 To .Cells(i, 2)
               col.Add .Cells(i, 1).Value
           Next
       Next

       For i = 1 To 3 ' Anzahl der zufälligen Auswahlen
           lngRnd = Int(col.Count * Rnd + 1)
           lngItm = col(lngRnd)
           col.Remove lngRnd
           lngSum = lngSum + lngItm
       Next
       Debug.Print lngSum
   End With
End Sub

Makro ausführen: Schließe den VBA-Editor und gehe zurück zu Excel. Drücke ALT + F8, wähle Zufallsauswahl und klicke auf „Ausführen“.
Ergebnisse ansehen: Die Summe der zufällig ausgewählten Beschäftigten wird im Direktfenster des VBA-Editors angezeigt. Du kannst das Fenster mit STRG + G öffnen.

Häufige Fehler und Lösungen

Fehler: „Index außerhalb des gültigen Bereichs“
Lösung: Stelle sicher, dass die Anzahl der Betriebe in Spalte B korrekt ist und die Werte in Spalte A vorhanden sind.
Fehler: „Objekt erforderlich“
Lösung: Überprüfe, ob du die richtige Arbeitsblattnamen verwendest. Ersetze „Tabelle1“ durch den tatsächlichen Namen deines Arbeitsblatts.

Alternative Methoden

Wenn du keinen VBA-Code verwenden möchtest, kannst du die Excel-Funktion ZUFALLSBEREICH() nutzen, um zufällige Zahlen zu generieren und dann SVERWEIS() verwenden, um die entsprechenden Beschäftigtenzahlen zu erhalten. Diese Methode ist jedoch weniger effizient, wenn du viele Datenblöcke hast.

Praktische Beispiele

Angenommen, du hast die folgenden Daten:

A (Beschäftigtenzahl)	B (Anzahl Betriebe)
10	20
11	23
12	13
13	7
14	13
15	19
16	1
17	0
18	10
19	10

Mit dem obigen VBA-Code kannst du drei Betriebe zufällig auswählen und ihre Beschäftigtenanzahl summieren. Zum Beispiel könnten die zufällig ausgewählten Betriebe 10, 12 und 15 sein, was eine Summe von 37 ergibt.

Tipps für Profis

Verwende Randomize, um sicherzustellen, dass die Zufallszahlen bei jedem Durchlauf unterschiedlich sind.
Du kannst die Anzahl der zufällig auszuwählenden Betriebe anpassen, indem du die Zahl in der Schleife For i = 1 To 3 änderst.
Wenn du die Ergebnisse nicht nur im Direktfenster anzeigen, sondern auch in einer Zelle in Excel ausgeben möchtest, kannst du Folgendes hinzufügen:
```
Range("C1").Value = lngSum ' Gibt die Summe in Zelle C1 aus
```

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass keine Betriebe doppelt ausgewählt werden?
Die im Code verwendete Collection entfernt bereits die ausgewählten Werte, sodass jede Auswahl einzigartig ist.

2. Funktioniert dieser Code auch in Excel 365?
Ja, der Code ist mit allen modernen Excel-Versionen kompatibel, die VBA unterstützen.

Beliebteste Forumthreads (12 Monate)

Forumthread: Zufällig auswählen mit VBA

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Zufällige Auswahl von Werten mit VBA in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread