“It all ends in tears anyway.” [Jack Kerouac]
Abstract
Es zirkulieren mehrere verschiedene naive Ansätze für das summenerhaltende Runden:
- (der schlechteste) Runde alle Werte mit Ausnahme des Letzten und ersetze dann den letzten Wert durch die Differenz der gerundeten Originalsumme minus der Summe der vorher gerundeten Werte (d.h. aggregiere alle Rundungsfehler im letzten Summanden):
| A | B | C | ||
|---|---|---|---|---|
| 1 | Originaldaten | Aggregiere Rundungsfehler | Formel in C | |
| 2 | Total | 2,594 | 2,59 | =SUMME(C4:C8) |
| 3 | ||||
| 4 | 0,875 | 0,88 | =RUNDEN(B4;2) | |
| 5 | 0,865 | 0,87 | =RUNDEN(B5;2) | |
| 6 | 0,344 | 0,34 | =RUNDEN(B6;2) | |
| 7 | 0,455 | 0,46 | =RUNDEN(B7;2) | |
| 8 | 0,055 | 0,04 | =RUNDEN(B$2;2)-SUMME(C$4:C7) |
- (besser, aber immer noch schlecht) Wende das hintereinandergeschaltete (gleitende) Runden an:
| A | B | C | ||
|---|---|---|---|---|
| 1 | Originaldaten | Hintereinander- geschaltetes Runden | Formel in C | |
| 2 | Total | 2,593 | 2,59 | =SUMME(C4:C8) |
| 3 | ||||
| 4 | 0,875 | 0,88 | =RUNDEN(SUMME($B$3:$B4);2)-SUMME($C$3:$C3) | |
| 5 | 0,865 | 0,86 | =RUNDEN(SUMME($B$3:$B5);2)-SUMME($C$3:$C4) | |
| 6 | 0,344 | 0,34 | =RUNDEN(SUMME($B$3:$B6);2)-SUMME($C$3:$C5) | |
| 7 | 0,454 | 0,46 | =RUNDEN(SUMME($B$3:$B7);2)-SUMME($C$3:$C6) | |
| 8 | 0,055 | 0,05 | =RUNDEN(SUMME($B$3:$B8);2)-SUMME($C$3:$C7) |
Vergleichen wir beide Ansätze mit RoundToSum.
Rechenbeispiel
[Dieses Beispiel ist auch in der Download Datei bei RoundToSum enthalten.]
Wir erzeugen 40 Zufallszahlen ZUFALLSZAHL() * 1000 und vergleichen wie folgt:
Bitte beachten Sie, dass die Formel in C3 beim hintereinandergeschalteten Runden die Titelzeile beinhaltet, damit sie einfach herunterkopiert werden konnte.
Wie man sieht, erhalten wir einen aggregierten Rundungsfehler in Höhe von -0,03, wenn wir alle Werte einfach einzeln runden. Spalte J (VIII) zeigt die Differenz des aggregierten Rundungsfehlers von -0,03 im letzten Summanden. Spalte F (IV) zeigt die korrespondierenden gerundeten Werte. Im schlimmsten Fall würden Sie hier einen aggregierten Rundungsfehler von n * 0,005 erhalten, wobei n die Anzahl der Zahlen ist. Beispiel: Nehmen Sie an Stelle der 40 Zufallszahlen 40-mal die Zahl 0,005.
Der Ansatz des hintereinandergeschalteten (gleitenden) Rundens in Spalte I (VII) zeigt 11 Rundungen zur falschen Seite. Spalte E (III) zeigt die korrespondierenden gerundeten Werte. Beim hintereinandergeschalteten Runden können Sie im schlimmsten Fall die Hälfte der Zahlen zur falschen Seite runden, obwohl alle Zahlen korrekt gerundet werden könnten. Beispiel: Nehmen Sie an Stelle der 40 Zufallszahlen 20-mal die Zahl -0,0049999 und dann 20-mal die Zahl 0,0049999.
Im Gegensatz dazu rundet das optimale RoundToSum lediglich 3 Werte zur falschen Seite und wendet die geringste Anzahl von Änderungen an, um die korrekte gerundete Gesamtsumme mit dem kleinsten absoluten Fehler zu erhalten. Im schlimmsten Fall würden Sie nun n/2 Male zur falschen Seite runden müssen, wobei n die Anzahl der Zahlen ist. Beispiel: Nehmen Sie an Stelle der 40 Zufallszahlen erneut 40-mal die Zahl 0,005. Es ist jedoch die beste Lösung mit dem kleinsten absoluten Rundungsfehler für jede Zahl und danach mit der geringsten Anzahl von Rundungen zur falschen Seite.
Zusammenfassung
Verwenden Sie RoundToSum. Es wendet die geringste Anzahl von Änderungen an, um die korrekte gerundete Gesamtsumme mit dem kleinsten absoluten (oder relativen) Fehler zu erhalten. Eine Matrixformel ist unabdingbar, weil sich alle n > 1 Eingabewerte auf alle n > 1 Ausgabewerte auswirken.
Ein hintereinandergeschaltetes Runden - wie oben in Zelle E6 mit der Formel =RUNDEN(SUMME($C$5:$C5);2)-SUMME($E$4:$E4) gezeigt - benötigt kein VBA und auch keine Matrixformel, aber es kann leicht wesentlich mehr unnatürliche Rundungen aufweisen, die man nur schwerlich erklären kann.
Am schlimmsten ist jedoch der dumme Ansatz, alle Rundungsdifferenzen in einer (hier: der letzten) Zelle zu aggregieren. Man stelle sich 1.000 Menschen mit je 49 Cent in der Tasche (also insgesamt 490 EUR) vor. Wenn Sie diese Beträge fair auf ganze Euro gerundet verteilen wollen, würde bei diesem Ansatz der Letzte die gesamten 490 Euro erhalten. RoundToSum gäbe den ersten 490 Personen je einen Euro und allen anderen Null Euro.
Download
Bitte den Haftungsausschluss im Impressum beachten.
roundtosum.xlsm [58 KB Excel Datei, ohne jegliche Gewährleistung]