Office: (Office 2019) Excel Anzahl Anhand Obergrenzen errechnen lassen?

Hallo,
ich frage mich gerade, ob Excel das auch könnte, ich wüsste allerdings nicht ob und wie das gehen sollte, deswegen sind meine Erwartungen sehr gering.

Wenn wir in unserem Betrieb Bargeld zur Bank bringen, sind wir da bei der Einzahlung eingeschränkt. Wir können Maximal 10.000 Euro und Maximal 200 Scheine abgeben.
Die Anzahl der Scheine, die wir in der Kasse haben ist natürlich auch immer Individuell.
Wir haben da eine kleine Excel-Tabelle, wo wir die Anzahl der Scheine eintragen und dann sehen wie viel Geld und wie viel Scheine es sind. Je nach Situation ist das dann immer eine Stapelschieberei bis es dann richtig klappt, weil man natürlich so viel Scheine wie möglich loswerden möchte und die Vorgaben nicht überschreiten darf.

Natürlich variiert die Maximal Summe auch manchmal, da mal jemand zwischendurch zur Bank fährt und die Kasse noch nicht "voll" ist.

Jetzt habe ich heute mal überlegt, ob Excel da vielleicht auch irgendwas machen könnte. Ich wüsste allerdings nicht wie.

Der "Wunsch" wäre also, dass man schnell die Kasse durchzählt wie viel Scheine man in der Kasse hat und trägt diese alle in die Liste ein. Nun gibt Excel (am besten von den kleinen Scheinen auf an, da man diese oft am meisten hat und froh ist, wenn man diese loswird, wenn man sie behalten möchte kann man ja immer noch "0" eingeben) die Maximale Menge der Scheine vor um auf die Eingetragene Maximalsumme zu kommen und die Anzahl nicht über 200.

Wahrscheinlich wäre das als Programmcode möglich, aber da fehlt mir leider die Erfahrung.

Eine Beispieltabelle habe ich angefügt.
Vielleicht hat ja jemand eine Idee und hat Spaß dran. Das würde einiges Erleichtern.

Vielen Dank

 

Bei 1800x5-Euro-Scheinen und 30x50-Euro-Scheinen:

Willst Du dann eher [200x5er] oder [180x5er + 20x50er] oder [170x5er + 30x50er] abgeben?

 

Hallo,

für dieses bzw. für ähnliche Probleme aus dem Dunstkreis der sogenannten "linearen Optimierung" gibt es in Excel seit vielen Versionen das "Solver"-Add-In.

Damit dieses sinnvoll einsetzbar ist, habe ich deine Spalte C für "Anzahl" (an Scheinen) zur Spalte "Mögliche Anzahl" im Beschriftungskopf umgewandelt und daneben die Spalte D mit Beschriftungskopf "Verfügbare Anzahl" (an Scheinen) eingefügt.
Unterhalb des Bereiches D3:D9 (verfügbare Anzahlen) habe ich in D11 die verfügbaren Gesamtanzahl und in D12 den verfügbaren Gesamtbetrag eingefügt.

Ich habe den "Solver" so eingestellt (siehe Bild), dass
1) in C11 die mögliche Gesamtanzahl (an Scheinen) maximal wird.
2) in C3:C9 die Primärvariablen enthalten sind (=die Zellen, die sich beliebig ändern dürfen, soweit es die einschränkenden Bedingungen erlauben) = mögliche Anzahl pro Scheinarten
3) die Primärvariablen ganzzahlig sein müssen und im Wertebereich 0 bis zur verfügbaren Anzahl liegen müssen
4) der mögliche Gesamtbetrag (in A11) im Wertebereich 0 bis 10000 liegen muss
5) die mögliche Gesamtanzahl (in C11) im Wertebereich 0 bis 200 liegen muss

Vor dem Start der Berechnungen durch das Solver-AddIn sind die blauen Anzahlen (D3:D9) einzugeben. Diese spiegeln den jeweiligen Stand der Kassa wider.
Wird das Solver-AddIn gestartet, zeigt es nach kurzer Rechendauer ein Resultat an, welches sich in den Zellen C3:C9 befindet (mögliche Anzahlen). Aufgrund der Nebenbedingungen ergibt sich in A11 ein Betrag, der 10000 nicht überschreitet und ebenso in C11 eine Anzahl, die 200 nicht überschreitet und dabei maximal wird.

Den Anhang 34965 betrachten

Datei liegt bei.

 

"Kleine Scheine zuerst weg" ergibt aber einen Zielkonflikt.

 

Hallo,

dann wird sich Andreas7611 entscheiden müssen, ob er den Betrag 10000 oder die Anzahl 200 besser ausschöpfen will.
Wenn ihm der ausgeschöpfte Betrag zu klein erscheint, weil er eine große Anzahl kleiner Scheine eingesetzt hat, dann wird er die Anzahl verfügbarer kleiner Scheine händisch reduzieren müssen, um dann mit dem Solver zu überprüfen, ob dadurch der Betrag 10000 besser ausgeschöpft wird.
Dafür ist die lineare Optimierung gedacht, experimentell zu prüfen, bis ein "gewünschtes" Optimum erreicht wird.

 

Danke für die Hilfe.
Das mit den "kleinen Scheinen zuerst" ist nicht das wichtigste. Aktuell häufen sie sich nicht mehr so an, bzw. kann es durch vermehrte Herausgabe von kleineren Scheinen schon steuern - oder bei Bedarf mal "Manuell" ausrechnen wie gehabt.

Das mit dem Solver habe ich leider gar nicht verstanden. Ich kann da zwar die Anzahl Scheine eingeben (D3-D9) und dann ändert sich auch der Kassenbestand, aber die Anzahl der "Möglichen Scheine" ändert sich ja nicht.. Und wenn ich das Solver-AddIn aufrufe, öffnet sich da auch nix..

 

Hallo,

in der beiliegenden Datei habe ich dir im Bereich G2:G9 (grau hinterlegter Bereich) die Solver-Konfiguration für dein Datenmodell abgespeichert.

1) Du markierst zuerst diesen Bereich G2:G9
2) Im Menüband > Tabulator "Daten" > Gruppe "Analyse" > Befehlsbutton "Solver" klickst du darauf.
3) Es erscheint das Dialogfenster "Solver-Parameter". Dort gibt es den Button "Laden/Speichern", welcher anzuklicken ist.
4) Es erscheint ein Bereichsauswahlfenster "Modell laden/speichern", wo deine Zellmarkierung aus Punkt 1) bereits eingetragen ist. Hier nur den Button "Laden" anklicken.
5) Daraufhin wird nochmals eine Befehlsauswahl "Modell laden" angezeigt, wo du auf den Button "Ersetzen" klicken sollst.

6) Es erscheint dann schlussendlich das Dialogfenster "Solver-Parameter", vollständig ausgefüllt mit dem Daten-Rechenmodell, das du benötigst. Es sieht so aus:

7a) Ganz oben wird das Rechenziel angegeben: In Zelle $A$11 soll ein maximaler Wert errechnet werden unter Beachtung der Einschränkungen, die noch folgen (Nebenbedingungen).
7b) Es wird der Variablenbereich (Primärvariable) angezeigt: $C$3:$C$9. Dort befinden sich die Zellen, deren Werte vom Solver-Programm abgeändert werden dürfen.

7c) Unterhalb davon befinden sich 4 Zeilen, die Nebenbedingungen sind. Diese müssen von den Werten aus Punkt 7b) eingehalten werden:
Zuerst wird festgelegt, dass A11<=A15 sein soll, also der Gesamtbetrag darf den Betrag von 10000 nicht überschreiten.
In der 4. Zeile ist das gleiche für die Geldschein-Stück enthalten: C11<=C15 (also maximal 200 Stück).
In der 2. Zeile C3:C9 <= D3:D9 wird festgelegt, dass die möglichen Stück die verfügbaren Stück nicht überschreiten dürfen.
In der 3. Zeile wird mit C3:C9 = Ganzzahlig festgelegt, dass der Solver für die möglichen Stück nur ganze Zahlen einsetzen darf (denn mit halben oder drittel Geldscheinen wirst nicht glücklich sein).
8) In der Checkbox unterhalb der Nebenbedingungen wird festgelegt, dass die möglichen Stück nicht negativ sein dürfen.
9) Schließlich wird noch die Lösungsmethode vorgegeben: Simplex-Linearprogramm.

10) Klickst du auf Button "Lösen", werden in den Zellbereich C3:C9 ganze Zahlen eingesetzt mit dem Ziel, dass der Betrag in C11 maximal wird - jedoch unter Beachtung der Nebenbedingungen aus Punkt 7c) und Punkt 8). Die Vorgabe, dass C11 maximal werden soll, veranlasst den Solver zuerst mit höherwertigen Gescheinen zu beginnen und dort die möglichen Stück auszunutzen und dann danach, wenn noch Stück frei sind (<=200), werden die geringwerten Scheine eingesetzt.
11) Hätten wir alternativ in der 1.Zeile (Ziel festlegen) die Zelle C11 eingesetzt (maximale Stück), so hätte das beim Solver bewirkt, dass er zuerst mit den geringwertigen Scheinen begonnen hätte und zum Schluss die höherwertigen Scheine eingesetzt hätte, soweit es die Nebenbedingungen zugelassen hätten.
12) Daraus (Punkt 10 und Punkt 11) kannst du ersehen, dass durch die Vorgabe der verfügbaren Anzahlen an Scheinen dem Solver-Bestreben entgegen gewirkt werden kann.

13) Nach dem Klicken auf Button "Lösen" (vgl. Punkt 10) erscheint vor der Anzeige des Resultates noch ein Info-Zwischenfenster "Solver-Ergebnisse". Das Wichtigste dort ist, die fett geschriebene Zeile zB. "Solver hat eine Lösung gefunden ...". Es könnte auch Fälle geben, in denen dem Solver verunmöglicht wird, eine Lösung zu finden. Mit Klicken auf Button "OK", wird das Resultat in C3:C9 eingeblendet.

 

Super, danke für die Erklärung. Dieses "Solver" kannte ich bisher noch nicht.

 

Sorry fürs "leichte" off topic:
Ich würde die Bank wechseln.
Ich habe ja schon viel mit Banken erlebt.
Bspw. dass das Einzahlen von Münzgeld eine Extragebühr kostet, obwohl dies ein Automat zählt und sortiert.
Maßnahme damals: siehe oben.
[/off topic]

Gruß Ralf

 

Moin Ralf,
das Limit bei 10.000 EUR hat meiner Kenntnis nach etwas mit der Rechtslage zur Geldwäsche zu tun, was bei der limitierten Anzahl der Scheine aber keine Rolle spielen dürfte. Meine Bank verlangt seit drei, vier Jahren ab 150.- EUR Münzen bei der Einzahlung am Automat eine Gebühr von 5%, ein letztens in einem Supermarkt entdeckter privater Wegelagerer will sogar gleich 10% und lässt die Kundschaft das Geld an der Supermarkt-Kasse holen.

Wie war das noch bei Bertholt Brecht? Was ist der Überfall auf eine Bank gegen die Gründung einer Bank!?

Grüße