Microsoft Excel

Herbers Excel/VBA-Archiv

Zufällig auswählen mit VBA

Betrifft: Zufällig auswählen mit VBA von: mpb
Geschrieben am: 06.11.2014 13:18:42

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

  

Betrifft: AW: Zufällig auswählen mit VBA von: Michael
Geschrieben am: 06.11.2014 13:34:03

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


  

Betrifft: AW: Zufällig auswählen mit VBA von: mpb
Geschrieben am: 06.11.2014 16:54:32

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


  

Betrifft: AW: Zufällig auswählen mit VBA von: Christian
Geschrieben am: 06.11.2014 19:17:00

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


 

Beiträge aus den Excel-Beispielen zum Thema "Zufällig auswählen mit VBA"