Iteration über Zufallszahlen

[Schuhi]

Hallo Experten,

ich habe eine Tabelle, in der aus einer großen, konstanten Werte-Matrix (2000 Zeilen,4 Spalten) zufällig Werte für eine Berechnung aus jeder Zeiele ausgesucht werden müssen.
Dazu muss zu jeder Zeile ein anderer Zufallswert zwischen 1 und 3 gebildet werden. Bisher kein Problem.

Ich benötige aber den Satz von Zufallszahlen (also 2000 Zufallszahlen), der mir das optimale Ergebnis der komplexen Verrechnung aus der Tabelle liefert, was sich bei einer bestimmten Konstellation von Zufallszahlen ergibt.

Ich muss also iterativ (?) tausende Sätze von Zufallszahlen generieren lassen und jedesmal überprüfen, ob das Ergebnis besser ist als ein Vorhergehendes, und dann sowohl Ergebnis als auch den Zufallszahlensatz abspeichern, wenn etwas Besseres gefunden wurde.

Hat jemand einen Tipp bzw. kennt ein sehr ähnliches Problem und dessen Lösung?

Herzlichen Dank für eine Antwort!!!

[AhQ]

Hallo Schuhi,

das klingt amüsant, was Du vorhast. Leider geizt Du ein wenig mit Deinen Angaben. ich könnte mir vorstellen, daß deswegen Du bislang auch noch keine Antwort bekommen hast.

Also, die erste und entscheidende Frage ist eigentlich, willst Du das ganze in einer Zelle haben oder kannst Du auch z.B. 5000 Zahlenkombinationen untereinander setzen und dann einfach die beste Kombination raussuchen? Der Unterschied ist der, daß Du bei Alternative 1 meiner Meinung nach ein Makro brauchst, welches die Berechnung macht, intern das Ergebnis speichert und mit einer Schleife von vorne anfängt bis es die Schleife soundso oft durchgegangen ist und das Dir dann die besten Zahlen ausspuckt. Wenn das Dein Begehr ist, bin ich leider der falsche Ansprechpartner, aber dann solltest Du auch besser in die Basic-Abteilung hier schauen.

Wenn Du allerdings beliebig viele Rechnungen machen kannst, dann würde ich mir die Zufallszahlen mal untereinander schreiben (so viele halt, wie Du willst), damit jeweils pro Zeile mir das Ergebnis Deiner komplexen Berechnung anzeigen lassen und schauen, wie weit Du neben dem idealen Wert liegst (NeuesErgebnis-Idealwert). Das Ergebnis wenn du mit ABS() nimmst, hast Du quasi die Entfernung, egal ob der Wert drüber oder drunter lag. In dieser Ergebnisspalte sucht MIN() dann den kleinsten Wert. Und wenn Du die Ergebnisspalte als 1. Spalte gemacht hast, können jetzt 3 SVerweise Dir die tollen Zufallszahlen liefern. Wenn die Ergebnisspalte nicht die erste Spalte ist, kann Dir INDEX() weiterhelfen, aber der SVERWEIS geht leichter zum Handhaben.

Soweit meine Theorie.

Gerne helf ich Dir, das in die Tat umzusetzen, sofern Du Hilfe brauchst und oder willst und meine Idee überhaupt klappt. Da ich aber weder die Zahlen kenn noch Deine komplexe Rechnung, kann ich hier in den meisten Punkten nur raten, ob es Dir weiterhilft. Aber wie gesagt, sofern Du es mit Formeln und keinem Makro gelöst haben willst, helf ich Dir gerne.

Viele Grüße
AhQ

[Spatzenschnabel]

Hallo,

das finde ich auch spassig. Also hab ich mal versucht mit den Daten eine Tabelle zu kreieren.

Folgende Punkte habe ich zugrunde gelegt und bin gleich auf Fragen gestossen, die für eine weitere "Verrechnung" erforderlich sind.

konstanten Werte-Matrix (2000 Zeilen,4 Spalten)

Habe positive Zahlen von 1 - 12000 gewählt

zu jeder Zeile ein anderer Zufallswert zwischen 1 und 3 gebildet werden

Na ja, zwischen 1 und 3 liegt die 2. Ganz zufällig. Ich denke du willst zufällig 1, 2 oder 3 auswählen. Was soll mit dieser zufällig gewählten Zahl gemacht werden? Addition, Division, Formel, Logik mit allen Feldern der jeweiligen Spalte?
In meiner Tabelle habe ich dennoch einfach 3 Spalten mit jeweils 1, 2 und 3 angelegt. und weitere 12 Spalten genutzt um jeweils eine Mulitplikation durchzuführen.

das optimale Ergebnis.

Wann ist so ein Ergebnis für dich optimal? Das grösste, das kleinste, in einem bestimmten Zahlenbereich?

für eine Berechnung aus jeder Zeiele.

Was für eine Weiterberechnung ist das? Addition, Subtraktion, Formel, Logik?

Vielleicht habe ich dich ganz missverstanden? Aber ohne Antworten auf meine Fragen, werde ich hier nicht weiterkommen können. Was ist denn -in Textform- die eigentliche Aufgabe die zu lösen ist? Vielleicht gibt es dazu ja noch ganz andere Ideen?

Gruß,

Spatzenschnabel

[balu]

Hallo,

also das was Schuhi vor hat, versteh ich auch noch nicht so richtig. Die Beschreibung ist mir einfach zu abstrakt. Beispieldatei wäre nicht schlecht.

@Spatzenschnabel

Na ja, zwischen 1 und 3 liegt die 2. Ganz zufällig. Ich denke du willst zufällig 1, 2 oder 3 auswählen.

Rein zufällig liegen zwischen 1 und 3 Milliarden von Zahlen!! Unter der Bedingung, das die Nachkommastellen so tief eingestellt sind. Wenn man dann =ZUFALLSZAHL() eingibt, dann sieht man das ganz gut.
Aber selbst diese aussage von mir ist ja nur rein theoretisch, ohne genauere infos von Schuhi.

Und bevor ich hier noch irgendweelchen Müll erzähle, gehe ich wieder an meinen Kalender weiter basteln

Gruß
balu

[Spatzenschnabel]

N´Abend

Rein zufällig liegen zwischen 1 und 3 Milliarden von Zahlen!! Unter der Bedingung, das die Nachkommastellen so tief eingestellt sind. Wenn man dann =ZUFALLSZAHL() eingibt, dann sieht man das ganz gut.
Aber selbst diese aussage von mir ist ja nur rein theoretisch, ohne genauere infos von Schuhi.

Und bevor ich hier noch irgendweelchen Müll erzähle, gehe ich wieder an meinen Kalender weiter basteln

Gruß
balu

Natürliche Zahlen : N = {1,2,3,...}

Das Einfachste konnte und wollte ich mir vorstellen.

Rationale Zahlen : Q = {OO Math kann ich noch nicht )

waren mir erstmal viel zu kompliziert.

Ansonsten haste ja Recht. Vor allem was die infos von Schuhi angeht.

Viel Spass beim Basteln.

Gruß,

Spatzenschnabel

[Schuhi]

Hallo zusammen,

erst mal vielen Dank für die Zuschriften - und die (berechtigte) Kritik an der Knappheit meiner Darstellung, deshalb auch eine (vereinfachte) Version meines Problems anbei...

Fakt ist mitlerweile: ich habe etwas über 2400 Wahlen in Projekte zu verteilen, jedes Projekt kann eine unterschiedliche Anzahl von Teilnehmern aufnehmen (siehe Tabelle in q6-u34), jeder Teilnehmer hat vier Projekte gewählt.

Prinzip: Zunächst wird per Zufall (Spalte o) ein gewähltes Projekt von jedem Teilnehmer ausgesucht und eingetragen (Spalte N), daraus berechnet sich die Häufigkeit der Teilnehmer in den verschiedenen Projekten (Zellen r6...r34), die Anzahl der gesamten Überbelegungen steht in R4.
Durch mehrmaliges manuelles Starten der Neuberechnung kann ich diese Zahl zunächst einmal verkleinern, das ist meine Anfangssituation.

Wie finde ich nun am geschicktesten das Minimum dieses Zahlenwertes durch iteratives Berechnen??? Ich kann doch nicht tausende Male Shift-Strg-F9 drücken!

Wähle ich z.B. nun für jeden Teilnehmer das Projekt aus, was hiernach die meisten freien Plätze hat (Spalten K und L), und trage dies als gewähltes Projekt ein, so ändert sich natürlich wiederum die Verteilung in den Projektwahlen, damit ist das Maximum u.U. gar keins mehr: das gibt jedenfalls Chaos in der Tabelle...

Vielleicht fällt euch ja dazu etwas ein, die bisherigen geäußerten Ideen lassen ja auf einen kompetenten Leserkreis schließen!

Viele Grüße
Schuhi

P.S.: Die Tabelle ist umfangreicher, ich musste sie kürzen um sie hochladen zu können!

Demo.ods

Gekürzte Tabelle zu meinem Beitrag

(57.84 KiB) 136-mal heruntergeladen

[AhQ]

Hallo Schuhi,

ja, schick, was Du da machen willst... Ich vermute mal, mit einem Makro ist das keine größere Angelegenheit, mit Formeln dagegen (zumindest bei meinem Wissenstand), schon, da Formeln ja leider immer rechnen, es hier aber angebracht wäre, die Formeln irgendwie mal zum "Schweigen" zu bringen. Naja, wie auch immer, ich hab mal was probiert, um bißchen Handarbeit kommt man aber bei meiner Lösung letztlich nicht drumrum.

Also, meine Grundidee ist, daß ich eine bestimmte Gesamtheit an Plätzen hab, die dann anteilig über die einzelnen Projekte verteilt werden. Ich schau also zuerst, wieviel % der Plätze geht an welches Projekt. Diese addier ich, damit ich eine nette aufsteigende (!) %-Werte-Liste bekomm. Dann mach ich mir ein Feld, in die ich die Zufallszahlen in entsprechender Menge generier und diese laß ich nun durch einen SVerweis (mit 4. Parameter=1) den jeweiligen %-Wert-Grenzen und damit einem Projekt zuordnen.

Soweit so gut, leider hat diese Idee halt den kleinen Haken, daß der Zufallsbereich natürlich nicht stoppt, wenn es irgendwo voll ist, aber das kann / muß man dann halt von Hand nachbessern.

In die Datei hab ich mal 2 Szenarien reingebaut, zum Einen mit 363 freien Plätzen (wie in Deiner Demodatei), zum Anderen mit 2900 Plätzen, da da ZUFALLSZAHL() meiner Meinung nach versagt, weil die addierten %-Werte zu klein werden. da hab ich dann mit ZUFALLSBEREICH() gearbeitet.

Die Tabelle ist meiner Meinung nach ganz einfach afgebaut, Spalte B = Projektnr, Spalte C = Anzahl der Plätze, Spalte D = Anzahl der Plätze in Prozent von der Gesamtzahl an Plätzen, Spalte D = Addition der %-Werte und Multiplikation mit 1000, um Zahlen über 1 zu bekommen, Spalte E = Projektnr nochmal, damit der SVerweis sie findet, Spalte G = Anzahl der Projektbelegung, Spalte H = +/-, also Plätze - Belegung, Spalte J und K haben den Zufallsbereich bzw. den SVerweis.

In I1 und K1 findest Du ein rotes und ein grünes Feld. Das ist das Muster für die bedingte Formatierung, die ich Dir in die Spalte K gelegt hab. Du gibst einfach in das rote und grüne Feld jeweils eine Projektnr ein, die bed. Formatierung färbt Dir dann die jeweiligen Zellen entsprechend ein. Wenn Du z.B. bei Projekt 4 soundsoviele zu viel und bei Projekt 11 soundso viele zu wenig hast, gibst Du diese Projektnr. in die Felder, die Nummern werden in Spalte K eingefärbt und Du kannst aus soundsovielen Vierern dann Elfer machen, damit keine Überbelegung entsteht. In I2 und K2 steht die aktuelle Anzahl der jeweiligen Belegung von I1 und K1.

Neue Berechnung machst Du mit Strg+Shift+F9, aber daß weißt Du ja, wie ich gerade gesehen hab.

Ja, das wäre das, was mir so gerade dazu einfällt. Wenn es wirklich 2900 Projekte sind, dann wünsch ich mal viel Spaß bei der Handarbeit. Leider hab ich keine Ahnung im Moment, wie man das umgehen kann, aber so ist wenigstens mal schon die Masse richtig verteilt. Ich hab gerade mal ne ganze Reihe an Testrechnungen gemacht, da waren rund 22% der Plätze zum Nacharbeiten. Aber wie gesagt, ich hab keine Ahnung, wie man das besser machen soll. Außer mit einem Makro, wo ich mich aber nicht auskenn.

Vielleicht hilft es Dir ja weiter.

Viele Grüße
AhQ

[balu]

Hallo Schuhi,

ich verdünnesiere mich, mach mich hier (*) ganz gepflegt (frisch geduscht) aus dem Staub . Dein Problem ist mir momentan um mindestens 5 Etagen zu hoch.
Aber das eine kann ich dennoch sagen: "OHNE Makro wirst Du nicht weit kommen."

Allen anderen wünsche ich viel Spaß beim Knobeln.


Gruß
balu


(*) gemeint ist dieser Thread hier.

[Stephan]

Ich muss also iterativ (?) tausende Sätze von Zufallszahlen generieren lassen und jedesmal überprüfen, ob das Ergebnis besser ist als ein Vorhergehendes, und dann sowohl Ergebnis als auch den Zufallszahlensatz abspeichern, wenn etwas Besseres gefunden wurde.

Wenn Dir das als Arbeitsprinzip reicht mach das mit ein wenig Makrocode.
Beispieldatei habe ich angehangen, drücke dort auf Schaltfläche "Start" und lass rechnen solange Du willst. Der aktuelle Minimumswert und die Anzahl der Rechnungen werden oben in der Tabelle angezeigt und die zum Minimum gehörigen Werte in Spalte P geschrieben.
Drücke Schaltfläche "Stop" um die Rechnung zu beenden.

Ob das Ganze nun hinreichend schnell zu einem geeigneten Ergebnis führt weiß ich nicht, denn basic ist natürlich recht langsam. Mußt du ggf. halt einige Stunden rechnen lassen.



Gruß
Stephan

[Schuhi]

Hallo Stephan und AhQ,

schon mal vielen Dank für euere Mühe, ich habe mir eure Dateien heruntergeladen und werde sie morgen in aller Ruhe mal checken: denn da steckt ja eine Menge guter Ideen drin, und mit Makros hatte ich zwar bisher gar nichts am Hut, vielleicht wird's ja ein Einstieg.

Wenn ich das Ganze bis Ende Juni fertig habe - wie auch immer! - berichte ich noch einmal kurz!

Schönes Wochenende,

Schuhi

[Schuhi]

Hallo,

es ist mitlerweile alles fertig, ich mir habe dein Makro angesehen und verstanden, konnte es noch etwas für meine Zwecke erweitern.
Mein Rechner hat dann mal fast zwei Tage Spaß damit gemacht, die Zufallszahlen haben dann aber schon ein tolles Ergebnis geliefert,
was nur noch ein paar Stunden Handarbeit und Nachkorrektur benötigte!

Also, nochmal herzlichen Dank, nicht nur aber besonders an Stephan!

Schuhi