Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
988to992
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
988to992
988to992
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Verteilungsproblem

Verteilungsproblem
06.07.2008 18:38:30
Burghard
Hallo,
ich bastle schon zwei Tage an einer Lösung für ein Problem herum, komme aber nicht zu einer vernünftigen Lösung.
Ich habe schon vieles ausprobiert (z.B. prozentuale Aufteilung), dabei gab es dann das Problem mit den gleichen Rängen.
Auch über inkrementes Verringern der Teilbeträge bin ich nicht weitergekommen, da auch hier die gleichen Ränge nicht berücksichtigt wurden.
Ich habe mein Verteilungsproblem in Form einer Datei hochgeladen.
https://www.herber.de/bbs/user/53629.xls
Tabelle1 zeigt mein Problem (siehe Texthinweise dort). In der Tabelle2 habe ich in der „Konstellation A“ per Hand Teilbeträge eingetragen, um eine Lösung darzustellen.
Für mich ist eine allgemeine Lösung per Formeln oder per VBA-Code wünschenswert, da die manuelle Hin- und Her-Rechnerei doch sehr zeit- und arbeitsaufwendig ist.
Ich befürchte, dass mein Problem sehr anspruchsvoll ist, aber vielleicht hat doch jemand eine gute Idee. :-)
Schönen Gruß
Burghard

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Verteilungsproblem
06.07.2008 23:46:00
Roland
Hallo Burghard,
in der Tat ein nettes Problem, das sich aber ohne weitere Informationen nicht lösen lässt:
1. Was soll bei mehreren Rängen denn genau passieren; z.B. Maximalbetrag mit 13% pro Maximalbetrag oder insgesamt auf alle ersten Ränge?
2. Dto. beim Minimalbetrag
3. Hat die Häufigkeit der angezeigten Ränge was mit dem Verteilungsschlüssel zu tun (wenn ja wie?)
4. Hat die Bezeichnung der Ränge was mit dem Verteilungsschlüssel zu tun (wenn ja wie?)
Aus deiner Beispieltabelle ist leider nicht ersichtlich, wie Du auf die Ergebnisse gekommen bist. Die Rechenstruktur müsste man schon wissen, von alleine kommt Excel da nicht drauf. Wenn Du nur schreibst "Der nachgeordnete Rang soll weniger als die gleichen Ränge vor ihm erhalten", kann man dies natürlich auch über einen Zufallsgenerator ermitteln.
5. Last but not least: wie soll denn gerundet werden (alle Ränge auf volle Euro oder "die kleineren" auf 50 Cent?)
Gruß
Roland Hochhäuser

Anzeige
Re: Antworten auf Fragen
07.07.2008 12:11:00
Burghard
Hallo Roland,
erst mal vielen Dank, dass Du auf meine Frage geantwortet hast. Ich will mal versuchen, ob ich Deine Fragen beantworten kann.
Ich hatte mir dieses Vorgehen gedacht:
1. Die drei dargestellten Konstellationen A,B und C sind völlig unabhängig voneinander und sollen nur mehrere mögliche Fälle darstellen, die im Ablauf der nächsten Zeit in ähnlicher Form passieren könnten.
Beim Tabellenblatt 1 und Tabellenblatt 2 habe ich drei mögliche Konstellationen dargestellt. Die Absicht war zu überprüfen, ob eine Programmierung, die bei der ersten Konstellation A funktioniert, dies bei anderen Konstellationen auch richtig machen würde (Beispiele Konstellation B und Konstellation C).
2. Im Tabellenblatt 1 bei der Konstellation A gibt es ja 13 verschiedene Ränge.
Die Anzahl der Ränge müsste wohl bei der Programmierung zuerst festgestellt werden.
Danach soll der erste Rang 13% (ist eine variable Eingabe per Inputbox möglich?) der Gesamtsumme erhalten. Im nächsten Schritt soll der letzte Rang 10,50€ erhalten.
Danach soll das übrig gebliebene Geld möglichst gerecht und gleichmäßig auf die anderen Ränge aufgeteilt werden.
Im Tabellenblatt 2 habe ich das bei der ersten Tabelle in etwa per manueller Eingabe der Beträge für die Ränge 2 bis 12 versucht. Die Teilbeträge sind von mir in etwa abgeschätzt worden, per Taschenrechner und Pi mal Daumen (ist leider nicht ganz gleichmäßig/gerecht verteilt).
Die Programmierung soll eine gerechtere/gleichmäßige Aufteilung bewirken.
3. Ich habe die Datei neu hochgeladen (Verteilungsproblem-B.xls).
https://www.herber.de/bbs/user/53634.xls
Im Tabellenblatt 2 habe bei den Tabellen B und C den Maximalbetrag (13% der Gesamtsumme) und den Mindestbetrag von 10,50€ auf den letzten Rang per Hand eingetragen.
Insbesondere die gleichmäßige Aufteilung der Restsumme auf die anderen Ränge dazwischen soll die Programmierung erledigen.
4. Nein, die Häufigkeit der Ränge ist zufällig und kann wechseln und hat mit dem Verteilungsschlüssel nur in der Form zu tun, dass mehrere gleiche Ränge denselben Betrag erhalten sollen (siehe Tabellenblatt 2, Konstellation A).
Das Problem bei der Programmierung wird m.E. sein, dass nach der Verteilung von 13% auf den ersten Rang und 10,50€ auf den letzten Rang die Verteilung der Restsumme für die restlichen Ränge berechnet werden muss (hier müsste m.E. festgestellt werden, wie viele Plätze auf den jeweiligen Rang entfallen).
Ich kann mir vorstellen, dass man so möglicherweise das Problem, dass ein nachfolgender Rang nicht mehr Geld bekommt als ein vorhergehender, lösen könnte.
5. Du hast Recht, dass es letztendlich ein Rundungsproblem durch die mehrfache Teilung von Beträgen geben wird.
Ich hatte mir die Rundung bei allen Beträgen auf zwei Nachkommastellen vorgestellt.
Wahrscheinlich wird die Gesamtsumme bei der Kontrolle aller Teilbeträge (siehe Tabelle unten) mit dem Gesamtbetrag/der Ausgangsumme nicht hundertprozentig übereinstimmen (Rundungsproblematik). Da könnte ich dann per Hand nachbessern.
Deine Idee Teilungsbeträge auf 0,50 Cents zu runden, könnte diese Rundungsproblematik möglicherweise umgehen.
Eine Rundung aller Beträge auf 0,50 Cents genau, wäre auch in Ordnung.
Ich hoffe, ich konnte Dir einigermaßen helfen.
Schönen Gruß
Burghard

Anzeige
AW: Re: Antworten auf Fragen
07.07.2008 12:42:12
Roland
Hallo Burghard,
soweit klar, begriffen und für den 1. und letzten Rang machbar, ABER was ist "gerecht" in deinem Sinne, m.a.W. um welches Mass sollen die "Zwischenränge" aufeinander aufbauen?
Zwischen Rang 25 und 22 liegen 50% Differenz, zwischen Rang 17 und 12 33% und zwischen Rang 2 und 1 nur noch etwa 20%. Welche Regel hast Du da im Auge?
Gruß
Roland Hochhäuser

AW: Bin am Überlegen
08.07.2008 15:45:35
Burghard
Hallo Roland,
sorry, ich hatte gestern keine Zeit mehr, um zu antworten.
Du legst mit Deiner Frage den Finger in die Wunde, darüber habe ich mir auch schon seit längerer Zeit den Kopf zerbrochen.
Ich habe schon in alle Richtungen überlegt, aber keine richtige Lösung gefunden. Zum Thema habe ich auch schon etliche Stunden „gegoogelt“.
Diesen Link habe ich gefunden, der das Thema streift:
http://www.bigboardzz.de/cgi-bin/forum/forum/cutecast.pl?forum=44&thread=6291
Dort kann man sich die Datei „Werte.xls“ herunterladen.
Die Programmierung durchschaue ich nicht.
Anderer Link:
https://www.herber.de/forum/archiv/376to380/t376201.htm
Ich habe auch mit den Funktionen „Zufallsbereich“ und „Zufallszahl“ gespielt. Aber m.E. kommt man dann nicht auf den zur Verfügung stehenden Maximalbetrag.
Ich bin auch schon am Überlegen mit den Funktionen „ZINS“ und „ZINSZ“. Vielleicht lässt sich damit etwas machen.
Ich schlage vor, dass ich mich noch eine Woche mit dem Thema auseinandersetze und Dich dann noch einmal neu anschreiben, inwieweit ich einer Lösung näher gekommen bin.
Schönen Gruß
Burghard

Anzeige
AW: Bin am Überlegen
08.07.2008 17:16:00
Roland
Hallo Burghard,
ich glaube, wir reden aneinander vorbei. Du hast doch bereits ein Beispiel manuell ausgerechnet; ich will / muss nur wissen, wie du auf die Ergebnisse gekommen bist. Die anschliessende Programmierung kriege ich schon gebacken.
Gruß
Roland Hochhäuser

AW: Bin am Überlegen
08.07.2008 21:03:02
Caruso
Hallo,
mal als Vorschlag
Formel für Zelle C3

=RUNDEN(WENN(A3=1;$C$2*13%;MAX(10,5;$C$2*0,5%)+POTENZ((MAX($A$3:$A$28)-A3);2) /SUMMENPRODUKT(POTENZ((MAX($A$3:$A$28)-$A$3:$A$28)*($A$3:$A$281);2))*($C$2*87%-MAX(10,5;$C$2*0,5%) *(ANZAHL($A$3:$A$28)-SUMMENPRODUKT(--($A$3:$A$28=1)))));2) 


für eventuelle Anpassungen zu beachten:
$C$2*13% = 13% für Rang 1 korrespondiert mit $C$2*87%
MAX(10,5;$C$2*0,5%) = 0,5% für letzten Rang, mindestens aber 10,50
POTENZ(...;2) beeinflusst Verlauf der Verteilung, taucht 2x in der Formal auf, muss jeweils identisch angegeben werden
$A$3:$A$28 ist an tatsächliche Liste immer anzupassen
Gruß
Carlo

Anzeige
AW: Formel geht!
09.07.2008 19:43:23
Burghard
Hallo Carlo,
vielen Dank für die Funktion. Es funktioniert! Ich will noch mal an Ronald schreiben, ob er mir die Funktion in einen VBA-Code umwandeln kann.
Habe dazu eine Datei neu hochgeladen.
Schönen Gruß
Burghard

AW: Teillösung von Carlo
09.07.2008 19:45:00
Carlo
Hallo Ronald,
vielen Dank für Deine Mail.
Carlo hat eine Lösung als Funktion geschrieben. Die funktioniert in meinem Sinn.
Ich habe die Funktion mal in ein Makro eingelesen.
Meine Fragen zur Anpassung des Makros:
1. Läßt sich die Variable "letzte" (Feststellung der letzten Zeile) in das Makro einpassen?
So braucht man nicht manuell jeweils die letzte Zeile anpassen.
2. Läßt sich eigentlich auch der Prozentsatz der Zelle "C3" per Inputbox eingeben bzw. variieren? Lt. Carlo korresponiert der erste Wert (13 Prozent) mit dem nachfolgenden Restwert.
Ich habe eine Datei mit dem Makro hochgeladen (siehe dort Tabelle1, Tabelle2, Tabelle3)
https://www.herber.de/bbs/user/53700.xls
Schönen Gruß
Burghard
=========================================
Text von Carlo:
Hallo,
mal als Vorschlag
Formel für Zelle C3
=RUNDEN(WENN(A3=1;$C$2*13%;MAX(10,5;$C$2*0,5%)+POTENZ((MAX($A$3:$A$28)-A3);2) /SUMMENPRODUKT(POTENZ((MAX($A$3:$A$28)-$A$3:$A$28)*($A$3:$A$281);2))*($C$2*87%-MAX(10,5;$C$2*0,5%) *(ANZAHL($A$3:$A$28)-SUMMENPRODUKT(--($A$3:$A$28=1)))));2)
für eventuelle Anpassungen zu beachten:
$C$2*13% = 13% für Rang 1 korrespondiert mit $C$2*87%
MAX(10,5;$C$2*0,5%) = 0,5% für letzten Rang, mindestens aber 10,50
POTENZ(...;2) beeinflusst Verlauf der Verteilung, taucht 2x in der Formal auf, muss jeweils identisch angegeben werden
$A$3:$A$28 ist an tatsächliche Liste immer anzupassen
Gruß
Carlo

Anzeige
AW: Probleme gelöst!
11.07.2008 12:10:19
Burghard
Hallo Ronald,
vielen Dank für Deine Hilfe! Ich konnte mittlerweile alle Probleme lösen.
Schönen Gruß
Burghard

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige