Excel: Geldbeträge ungleich verteilen möglich?

[ich_auch]

Hallo,

ich suche eine Möglichkeit Geldbeträge auf verschiedene Personen mit der kleinst möglichen Abweichung zu verteilen. Denn nicht jede Konstellation ergibt 2 stellen nach dem Komma.

Z.B. wenn ich 20,06 € auf 6 Personen aufteilen muss. Einfaches teilen durch 6 was 3,3433333333.. ergibt nutzt mir buchhalterich ja garnichts. Ich suche also etwas was mir statt dessen sagen würde "20,06 € = 4 x 3,34 € und 2 x 3,35 €". Wobei die kleinst mögliche Abweichung eben nur einen Cent beträgt.

Kann Excel das?

 

[little_skunk]

Das ist einfache Mathematik.

20,6*100 DIV 6 /100 erledigt das Runden. Die Funktion Runden darfst du nicht verwenden.
20,6*100 mod 6 ergibt dann die fehlenden Cent. In diesem Fall wären das 2 Cent.

Jetzt musst du dir nur noch die passenden Excel Formel raussuchen.

 

[Polarcat]

Einfach unter Format => Zellen formatieren und dann "Buchhaltung" auswählen. So sollte es passen.

 

[C4rtm4n]

mit Buchhaltung funktioniert das nicht wirklich (bei mir jedenfalls nicht)... hab jetzt mal den komplizierten Weg genommen, geht aber bestimmt einfacher (glaub aber nicht, dass das in einer Formel möglich ist)...

gruß

 

[C4rtm4n]

Kann Excel das?

Excel kann alles

 

[ich_auch]

Wow danke..das scheint ja super zu funktionieren...muss mir das morgen mal in Ruhe anschauen um die Logik dahinter zu verstehen.

 

[little_skunk]

mit Buchhaltung funktioniert das nicht wirklich (bei mir jedenfalls nicht)...

Buchhaltung ist dafür auch völlig ungeeignet. Die Formatierung Buchhaltung sorgt nur für die 2 Nachkommastellen und die Währung. Da werden noch nicht mal die Rundungscent beachtet. Einfacher Test: A1=1 A2=3 A3=A1/A2 A4=A3+A3 Ergebniss: Richtig für die Buchhaltung wäre 0,66. Dank Rundungscent sind es aber 0,67. Bricht einem in der Buchhaltung das Genick. Soviel zur Formatierung Bruchhaltung...
(Hinweis: Um den Rundungscent loszuwerden muss man entweder selber runden oder in den Optionen von Excel "genauigkeit wie angezeigt" aktivieren. Für das vorliegende Problem aber völlig unerheblich.)

So nun nochmal zu meiner Lösung. Hab die Excel Funktionen mal eben rausgesucht.

A1=20,6
B1=6
A3=REST(A1*100;B1)
B3=ABRUNDEN(A1/B1;2)+0,01
A4=A2-A3
B4=ABRUNDEN(A1/B1;2)

Ließt sich dann folgendermaßen: A3 (2) Personen bekommen B3 ausgezahlt und alle anderen (A4) bekommen B4 ausgezahlt.

 

[Polarcat]

Okay mit dem Tests fällts auf. Bei den Beispielen dich ich probiert habe gings...

 

[C4rtm4n]

So nun nochmal zu meiner Lösung. Hab die Excel Funktionen mal eben rausgesucht.

A1=20,6
B1=6
A3=REST(A1*100;B1)
B3=ABRUNDEN(A1/B1;2)+0,01
A4=A2-A3
B4=ABRUNDEN(A1/B1;2)

Ließt sich dann folgendermaßen: A3 (2) Personen bekommen B3 ausgezahlt und alle anderen (A4) bekommen B4 ausgezahlt.

ok, jetzt bin ich ausgestiegen (funktionieren tut's auf jeden Fall) das mit der "REST"-Formel is ja mal top, wusst ich garnicht, dass es sowas gibt

 

[little_skunk]

Du hast selber gesagt, dass Excel alles kann. Nur leider haben die Funktionen eben deutsche Namen. "Rest" heißt eigentlich "Modulo" bzw kurz "MOD". DIV wäre "Ganzzahlige Division". Da man das gleiche auch mit Abrunden erreichen kann, gibt es dafür keine extra Funktion in in Excel (und diversen anderen Programmen). Der Rest ist wie gesagt einfache Mathematik. Hat eigentlich jeder von uns in der Grundschule gelernt. 14 geteilt durch 5 ist 2 Rest 4.

 

[ich_auch]

Da mir hier so kompetent geholfen wurde hab ich gleich noch eine Frage für eine Funktion die mir den Alltag erleichtern würde:

Ich gebe einen bestimmten Wert vor und möchte das Excel sich diesem Betrag so nah wie möglich nährt (nicht höher als der vorgegebene Wert) aber das nur in der Größe von 2 festen Werten. Ich würde also z. B eingeben 8€ und Excel sagt mir z.B. das 7,50€ der nächst mögliche Wert ist und zu wievielen Teilen dieser aus Wert 1 und Wert 2 brechnet ist.

 

[little_skunk]

Das ist wieder einfache Mathematik. Das sind nicht zufällig deine Hausaufgaben?

Nehmen wir mal als Betrag 18€ und die 2 vorgegeben Werte sind 5€ und 7€.

Günstigerweise sollte man mit dem höheren Wert anfangen um sich Arbeit zu ersparen.
18€ DIV 7€ sind 2. Das sagt uns, dass 2*7, 1*7 und 0*7 möglich wären. 3*7 wären dagegen schon zuviel.
Jetzt kommt der 2. Wert ins Spiel.
(18€ - eine der Varianten) MOD 5€. Die kleinste Zahl gewinnt. Das wäre in diesem Fall Variante 2. (18€ - 7€) MOD 5€ hat als Rest nur 1€. Näher kommt man nicht an die 18€ ran. Dann noch (18€ - 7€) DIV 5€ und schon ergibt sich die Lösung 1*7€ + 2*5€ mit einem Rest von nur 1€.

 

[ich_auch]

Die Mathematik ist ja nicht zwingend das Problem...sondern wie erkläre ich es Excel. In meiner Schule und Ausbildung kamen wir leider nie über die primitivsten Funktionen hinaus. Und MOD gehört defintiv nicht dazu.

Das das am Anfang =ABRUNDEN(A11/A12;0) sein muss um auf 2 zu kommen konnte ich mir ja noch erbasteln. Aber wie soll ich Excel erklären das es mir sagt welche der 3 möglichen am sinnvollsten ist?

 

[little_skunk]

Die Funktionen hab ich ja schon genannt.
MOD=Rest()
DIV lässt sich mit Abrunden erledigen.

Bedenke dabei, dass mein Beispiel keine Nachkommastellen hat aber deine Werte später vermutlich schon.

Excel kann nur die 3 Varianten durchrechnen und dann die Variante mit dem kleinsten Rest wählen. In nur einer Formel bekommst du das nicht hin. Also in die 1. Zeile Variante 1, in die 2. Zeile Variante 2 usw. Alternativ kannst du auch ein VBA Makro schreiben.

 

[ich_auch]

Also die Logik des kleinsten Rests ist denk ich nicht immer optimal. Manchmal ist ja auch die richtige Anwort Wert1 garnicht zu benutzen weil mit die alleinige Benutzung von Wert2dem Zielwert am nächsten kommt.

Und VBA Makros kann ich definitiv nicht schreiben.

 

[little_skunk]

Also die Logik des kleinsten Rests ist denk ich nicht immer optimal. Manchmal ist ja auch die richtige Anwort Wert1 garnicht zu benutzen weil mit die alleinige Benutzung von Wert2dem Zielwert am nächsten kommt.

Dazu sag ich jetzt mal nichts. Die 0 ist oben eigentlich mehr als deutlich...

Und VBA Makros kann ich definitiv nicht schreiben.

Das war nur eine Alternative. Kannst wie gesagt auch pro Zeile eine der Varianten durchrechnen und am Ende die Zeile mit dem kleinsten Rest auswählen.

 

[ich_auch]

und dann die Variante mit dem kleinsten Rest wählen.

Das klingt für mich auch deutlich...jedenfalls nicht danach 0 zu nehmen aber kann natürlich auch ein Missverständnis sein..

 

[little_skunk]

Irgendwie wird mir das jetzt zu blöd. Du hast deine Antwort. Ich mach dir gern noch ein anderes Beispiel.
9
5 und 3

9 DIV 5 = 1 -> 1*5; 0*5

(9-(1*5)) MOD 3 = 1
(9-(0*5)) MOD 3 = 0

2. Variante ist ein Volltreffer weil der Rest am kleinsten ist. Nur falls du es noch nicht wusstest 0<1....
Zum Schluss noch (9-(0*5)) DIV 3 = 3. Damit ergibt sich die Lösung 0*5 + 3*3 = 9

Sry für den Tonfall. Ist noch früh am Morgen und ich hatte meinen Kaffee noch nicht.

 

[ich_auch]

Und ich hatte glaub ich schonmal erwähnt das mein Hauptproblem darin liegt es Excel zu erklären. Ich bin weder Programmierer noch gingen meine Schulungen in Excel über Tabellen und einfach Summen hinaus.

Es nutzt mir nicht viel etwas wie 9 DIV 5 = 1 zu schreiben wenn es in Excel =9/5 heißt, zumal das nicht mal 1 ergibt sondern noch mit der Funktion ABRUNDEN kombiniert werden muss, worauf ich wie vorher schon geschrieben ja gerade noch selber komme. Aber wenn der Hinweis schon fehlt weiß ich nicht was für Funktionen noch unerwähnt sind die ich eigentlich nutzen müsste.

Wie dem auch sei..ich versuch es momentan noch mal die Funktionen in die Sprache von Excel zu übersetzen..

Edit: momentag ergoogle ich mir wie man MOD richtig benutzt bzw eingeben muss..

mehr Edit: Kann ja nicht funktionieren wenn die Funktion im deutschen Excel REST heißt? Hätte ich sagen müssen das ich ein deutsches Office Packet nutze?

 

[little_skunk]

Da musst du jetzt durch. Ich helfe gern bei Hausaufgaben aber ich löse sie ungern komplett. Du hast alles was du brauchst. Wenn du noch Fragen hast kannst du sie gern stellen. Bitte aber nicht nach einer Komplettlösung fragen.

9 DIV 5 wäre in Excel =Abrunden(9/5;0)
9 MOD 5 wäre in Excel =Rest(9;5)
Alles schon von der 1. Aufgabe bekannt. Es sei denn du hast meine Lösung der 1. Aufgabe nur abgeschrieben ohne sie zu verstehen.

 

[ich_auch]

Ich hatte mich an die Lösung von Cartman gehalten die genau die Ausgabe brachte wie ich sie mir vorstellte. Die Funktionen dort sind anders.

So ich hab jetzt ein funktionierende Formel. Warum eigentich Rest verwenden? Was übrigbleibt wenn der große Wert so oft wie möglich dividiert wird ist ja schon durch (9-(1*5)) ermittelt. Im Prinzip wäre doch eine Wiederholung der ersten Formel mit Abrunden richtiger?


Wenn ich das richtig verstanden habe funktioniert die Rechnung die nur den kleinsten Wert verwendet nur indem ich einen weiteren Formel Block erstelle in dem der kleinste Wert zuerst dividiert wird? Ist natürlich in der Ausgabe unkomfortabel. Aber da ließe sich ja vielleicht etwas machen wie: WENN Ergebnis Formelblock 2 größer als Ergebnis Formelblock 1 dann schreibe Ergebnis Formelblock 2. Was ich dann natürlich auch noch auf die Ausgabe der Anzahl der verwendeten Werte ausdehnen muss. Ja muss ich mal probieren.

---------- Beitrag hinzugefügt um 11:47 ---------- Vorheriger Beitrag war um 10:55 ----------

Aber im Prinzip funktioniert das ganze so nicht. Diese Art Formel sorgt ja im Prinzip immer dafür das der zweite Wert nur einmal oder keinmal vorkommt.

Aber es gibt natürlich Fälle in denen es besser wäre den höheren Wert nicht so oft wie möglich zu verwenden sondern lieber einmal weniger und dafür den niedrigen öfter um der eingegebenen Zahl am nächsten zu kommen.

Aber gut das ließe sich vielleicht auch mit einem weitern Formelblock lösen indem beim Ergbnis der ersten Divison einfach noch eine 1 abgezogen wird, da würde der Rest ja automatisch mit dem kleineren Wert aufgefüllt. Und ich muss ja eh schon abfragen welcher Block dem Ergebnis am nächsten kommt.

 

[little_skunk]

Ich hatte mich an die Lösung von Cartman gehalten die genau die Ausgabe brachte wie ich sie mir vorstellte. Die Funktionen dort sind anders.

Heißt für mich: Du willst eine fertige Lösung egal wie sie funktioniert. Hauptsache nicht selber nachdenken müssen. Vieleicht ist Cartman so nett und löst auch die Aufgabe für dich komplett. Ich werd es nicht machen.

So ich hab jetzt ein funktionierende Formel. Warum eigentich Rest verwenden? Was übrigbleibt wenn der große Wert so oft wie möglich dividiert wird ist ja schon durch (9-(1*5)) ermittelt. Im Prinzip wäre doch eine Wiederholung der ersten Formel mit Abrunden richtiger?

Können wir gern auch für die Variante 1*5 durchrechnen.
9 DIV 5 = 1 -> 0*5 und 1*5 wären möglich.
Damit ergeben sich 2 Varianten.
(9-1*5) MOD 3 = 1
(9-0*5) MOD 3 = 0
(9-1*5) DIV 3 = 1 -> 1*5 + 1*3=8 Rest 1
(9-0*5) DIV 3 = 3 -> 0*5 + 3*3=9 Rest 0

Variante 2 ist damit der Gewinner weil der Rest 0 ist. Damit wäre deine Theorie wiederlegt. Es ist nicht richtig die Zahl durch eine der beiden Werte so oft wie möglich zu dividieren.

 

[ich_auch]

Ok das ganze besteht bei mir jetzt aus 4 Formelblöcken. Der Erste schaut ob der größere Wert = dem Betrag ist, wenn nicht wird abgerundet und wenn möglich mit dem kleineren aufgefüllt.

Die Zweite schaut ob der Betrag mit dem kleineren Wert zu erreichen ist, wenn nicht abgerundet und nicht aufgefüllt, mit dem größeren Wert wäre ja quatsch.

Dritte wie 1 nur das von der maximal möglichen Anzahl 1 abezogen wird wodurch der kleinere Wert genutzt werden kann um dem Betrag in bestimmten Situationen näher zu kommen als mit dem ersten Formelblock

Vierte das ganze mit dem kleineren Wert zu erst, auch wieder -1 und Rest mit dem größeren Wert auffüllen, was hier wieder Sinn macht (beim entsprechenden Verhältnis der beiden Werte).

Jetzt noch ein "wenn der Wert größer dann schreibe das und das in meine Ausgabefelder" Konstrukt und ich sollte meine Lösung haben.

---------- Beitrag hinzugefügt um 12:05 ---------- Vorheriger Beitrag war um 12:02 ----------

Nochmal zu der Verwendung von REST, ich musste natürlich noch das Runden integrieren da meine Werte ja nicht aufteilbar sind. Die Nutzung hat dann mitunter meine Summe überschritten, was ja nicht passieren solle, es soll ja in jedemfall darunter sein.

---------- Beitrag hinzugefügt um 12:14 ---------- Vorheriger Beitrag war um 12:02 ----------

Ich danke schonmal zur Hilfe zur Selbsthilfe..

 

[little_skunk]

Nochmal zu der Verwendung von REST, ich musste natürlich noch das Runden integrieren da meine Werte ja nicht aufteilbar sind. Die Nutzung hat dann mitunter meine Summe überschritten, was ja nicht passieren solle, es soll ja in jedemfall darunter sein.

Nur zur Sicherheit: Du verwendest abrunden und nicht runden für DIV oder?

Wenn du alles richtig gemacht hast, kannst du die Summe eigentlich nie überschreiten. Bei welchen Werten passiert das?

 

[ich_auch]

Ja ich hatte Abrunden verwendet. Welche Werte es waren weiß ich nicht mehr da ich die Rest Funktion inzwischen aus meinem Aufbau entfernt habe und mit verschiedenen Werten experimentiert hatte. Ich nutze in jedem Fall Werte mit 2 Stellen nach dem Komma die es zu verarbeiten gilt.

---------- Beitrag hinzugefügt um 14:22 ---------- Vorheriger Beitrag war um 12:28 ----------

Ok um es abzuschließen. Mit MAX ermittle ich welche der Formelblöcke das höchste Ergebnis hat (also meinem Ursprungswert am nächsten kommt).

Für die Ausgabe der der Anteile erfolgt dann eine größere Verkettung von "WENN Zelle mit MAX = dem was Formelblock 1 ergibt dann trage Anzahl Wert von Formelblock 1 ein SONST..." und dann werden die Formelblöcke halt abgeklappert bis es passt und die dazugehörigen Werte ins Ausgabegeld eingetagen.

---------- Beitrag hinzugefügt um 16:22 ---------- Vorheriger Beitrag war um 12:28 ----------

Verdammt, die Funktion ABRUNDEN scheint Excel Mobile nicht zu mögen, die Formeln damit werden gelöscht...kann ich das anderweitig simulieren?