Excelformel: Gegeben sind 10.000€ - aufteilen in Scheine (500,200,100, ..etc)?

Hallo,

ich möchte mir das "zur Bank gehen" erleichtern.

Ich habe Budget X (Beispiel: 10.000€), diese sollen nun verteilt werden auf die jeweilig verfügbaren Euro-Scheine.

Die Summe muss insgesamt 100% ergeben.

Also:

Gegeben 10.000€

5x 500€

6x 200€

5x 100€ etc. - Bis zum 5€ Schein.

== 10.000€

Hat der wer eine Excelformel parat?

Danke und liebe Grüße

Folgendes habe ich mir gebastelt:

VBA Code sieht aus wie folgt:

Sub Schaltfläche_Klicken()

    Dim totalBudget As Double
    Dim billValue As Double
    Dim billCount As Integer
    Dim billWeight As Double
    Dim remainingBudget As Double

    'Prompt user for total budget
    totalBudget = InputBox("Enter total budget:")
    remainingBudget = totalBudget
    ActiveSheet.Range("B9").Value = totalBudget

    'Array of bill types and weighting
    billTypes = Array(500, 200, 100, 50, 20, 10, 5)
    billWeights = Array(0.1, 0.2, 0.3, 0.2, 0.1, 0.05, 0.05)

    'Distribute budget based on weighting
    For i = 0 To 6
        billValue = billTypes(i)
        billWeight = billWeights(i)
        billCount = billWeight * (totalBudget / billValue)
        remainingBudget = remainingBudget - (billCount * billValue)
        ActiveSheet.Range("A" & i + 1).Value = billCount
    Next i
End Sub

Leider finde ich meinen Denkfehler noch nicht, da nicht der gesamteingegebene Wert verarbeitet wird. Häufig bleibt ein unlogisch Rest (bspw: User: 1450€, berechneter Geldwert der Scheine jedoch 1185€, sprich man hätte nochmal 2x 100€ mindestens ausgeben lassen können.

Answer 1

[hannes1806]

Hallo, ja hab ich:
in B3-B9 kommet die verfügbare Stückzahl, in D2 der aufzuteilende Betrag

Formel in D3 und runter kopieren:
=B3-D3
Formel in D3 und runter kopieren:
=MIN(ABRUNDEN(($D$2-SUMMENPRODUKT(A4:A$11;D4:D$11))/A3;0);B3)
Formel in D3 und runter kopieren::
=D3*A3

Wenn der Betrag rausgegeben ist, kannst du C3:C9 als Werte in B3:B9 kopieren.

Sehr interessante Aufgabe, ich habe die Lösung jetzt so gebaut:

In Spalte B kommen die Stück und in D eine beliebige Gewichtung.

Alle folgenden Formeln sind nach unten kopierbar.

In E3 wird die Ideale Stückzahl berechnet:
=ABRUNDEN($C$2/SUMMENPRODUKT($A$3:$A$9;$D$3:$D$9)*D3;0)

In F3 ein erster Wurf, wieviel entnommen werden sollte, um auf die ideale Stückzahl zu kommen: =MAX(B3-E3;0)

In G3 ein 1. Wurf, was dafür gewechselt werden soll: =MAX(E3-B3;0)

Wegen der Rundungen und Stückelungen ergibt sich dabei ein Fehler in H3:
=GANZZAHL(((H$2-SUMMENPRODUKT(H4:H16;A4:A16))/A3))

In I3 wird der Fehler korrigiert, also so viel entnimmst du tatsächlich:
=WENN(H3*F3=0;F3;F3-H3)

In J3 wird der Sack zugemacht :
=G3+H3-F3+I3

In K3 eine Kontrolle:
=B3-I3+J3

In F2 kommt dann noch diese Formel, welche du auf G2 und I2:K2 kopieren kannst
=SUMMENPRODUKT(F3:F9;$A$3:$A$9)

Und in H2 steht =F2-G2

So schaut's jetzt aus:

C1: =SUMMENPRODUKT(C3:C9;B3:B9)
C3: =RUNDEN($B$1/SUMMENPRODUKT($B$3:$B$9;$A$3:$A$9)*A3;0)
D1: =B1-C1
D3:
=WENNFEHLER(GANZZAHL(($D$1-SUMMENPRODUKT(D$2:D2;B$2:B2))/B3);0)
E1: =SUMMENPRODUKT(E3:E9;$B3:$B9)
E3: =D3+C3

Woher ich das weiß: eigene Erfahrung – Faulheit >> Neugier >> Wissen

Comments

[PixL86]

Ich habe meinen Eingangspost etwas editiert. Evtl. wird es hiermit etwas klarer :)

Nochmals: vielen Dank für Deinen Einsatz! :)

LG

[hannes1806]

@PixL86

ich habe meine Antwort auch nochmal ergänzt, kein VBA. nur Formeln.

[PixL86]

Hallo hannes1806,

erst einmal: vielen Dank für dein Hirnschmalz. Ist nicht ganz so trivial wie ursprünglich vermutet. Habe auch bereits eine KI damit beauftragt, die aber gar nicht versteht was ich erreichen möchte.

So auch bei Dir: "Verfügbare Stückzahl"? Wovon!?

Ich schätze, ich habe mich unglücklich ausgedrückt. Was ich erreichen möchte, ganz simpel ausgedrückt:

Ich habe 10.000€ Bar in der Hand, gehe zur Bank und möchte die 10.000€ quasi wechseln in kleinere Scheine für die Kasse. Sprich: gleichmäßig die 10.000€ auf alle Geldscheine verteilen. Ideal wäre sogar eine Gewichtung. (Ich stelle fest, ich habe noch n 500€ Scheine, benötige also hiervon relativ weniger, dafür evtl. mehr 200€ Scheine da keine mehr vorhanden.)

Liebe Grüße und vielen Dank!

[hannes1806]

@PixL86

Hallo, ich habe meine Antwort ergänzt, siehe ab "Sehr interessante Aufgabe,..."

Answer 2

[GutenTag2003]

Wenn es keine Vorgaben gibt, welche und wie viele davon präferiert werden, würde ich mir so helfen und vortasten (Auswahl/Anzahl entsprechend B14:B20) auswählen.

In B11 =+B1*A1+B2*A2+B3*A3+B4*A4+B5*A5+B6*A6+B7*A7

Comments

[PixL86]

Hallo GutenTag2003,

auch eine gute Idee, vielen Dank. Ich schaue mal, dass ich das in mein Makro geschubst bekomme.

LG

Answer 3

[IchMalWiederXY]

Hier ne Lösung in Java, die man auch in VBA nachbauen kann.
Geldstückelung ♨󠄂‍󠆷 Java - Hilfe | Java-Forum.org

Answer 4

[AnnukaSi]

Du müsstest erst einmal eine Hilfstabelle erstellen wo deine verfügbaren Anzahl an Geldscheinen gezeigt wird. Daraus errechnet du dann mit dem ABRUNDEN-Befehl wieviel Höhe Geldscheine du haben willst. Mit dem Wert was übrig bleibt erhält du die Summ der anderen

Comments

[PixL86]

Danke für deinen Ansatz. :)

Die Hilfstabelle ist klar. Aber wie würde die Formel dazu dann aussehen? Hättest Du hier vielleicht einen Ansatz?

LG

[AnnukaSi]

@PixL86

Das müsste man im Programm machen. Wenn ich vor so Excel-Problemen stehe zeichne ich mit die Logig erstmal auf Papier. Dann kommt die Lösung wie ich was berechnen muss fast von alleine.