Rundungsdifferenzen vermeiden

Forumthread: Rundungsdifferenzen vermeiden

08.02.2018 14:12:09

Hallo zusammen,
bei der Verteilung von Gesamtkosten (F7) nach Mitarbeiter-Köpfen entstehen durch "RUNDEN" Rundungsdifferenzen, d.h. der gesamt verteilte Betrag (F14) ist um einen Cent höher als der zu verteilende Betrag (F7).
Durch "ABRUNDEN" verschiebt sich das Problem lediglich (2 Cent niedriger); bei "AUFRUNDEN" dito.
Wie kann ich vorgehen, um diese Differenzen zu vermeiden?
(das manuelle Hinzufügen von einem Cent wäre natürlich eine Lösung, ist aber 1. sehr un-elegant und 2. bei sehr großen Datenmengen eher keine Lösung)
Hier die Datei:
https://www.herber.de/bbs/user/119653.xlsx
Danke, viele Grüße

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Einfach aussitzen

08.02.2018 14:27:14

lupo1
AW: Einfach aussitzen

08.02.2018 15:46:21

F_
Es gibt übrigens eine bestimmte Methode ...

08.02.2018 17:13:37

lupo1
AW: Rundungsdifferenzen vermeiden

08.02.2018 20:25:55

Sulprobil

Einfach aussitzen

08.02.2018 14:27:14

lupo1

A1: Aufteilbetrag
C1:F1: 4 von 5 Beteiligten gerundet: =RUNDEN($A1/5;2)
B1: =A1-SUMME(C1:F1)
Wenn Du die Rundung immer bei B1 stattfinden lässt, nähert er sich insgesamt den anderen in C1:F1 wieder an.

AW: Einfach aussitzen

08.02.2018 15:46:21

Hm, hätte ich auch selbst drauf kommen können. Ich hatte an sowas kompliziertes gedacht wie eine RUNDEN-Formel, die (wie auch immer) den Betrag vollständig exakt verteilt.
Dank Dir

Es gibt übrigens eine bestimmte Methode ...

08.02.2018 17:13:37

lupo1

... vorgegebener Korrektur der Aufteilung, die zu einem Minimum an Ungerechtigkeit führt:
A) Gleichaufteilung
Nehmen wir 5 gleich Beteiligte ABCDE, die zusammen Zinsen/ZASt/SolZ haben. ZASt und SolZ müssen auf sie einzeln centgenau erklärt und aufgeteilt werden.
Bei einem Teiler /5 ergibt sich alle 5 Cent eine restlose Aufteilung. Bei den anderen 4 Cent muss man manuell korrigieren. Es kommt uns zugute, dass wir eine ungerade Anzahl Beteiligter haben. Ich bilde mit AB und CDE eine 2er- und eine 3er-Gruppe aus ABCDE.
Nun korrigiere ich die Cent der Gruppen wie folgt:
2er-Gruppe: 0;-1;1;0;2
3er-Gruppe: 0;1;0;1;0
für die Reste zu 5 Cent von 0;1;2;3;4
Die Summe der Aufteilungsbeträge führt nun zu der gleichen Zuordnung auf die Beteiligten, wie es die Summe der Zuordnungen der Einzelbeträge tun.
B) Zwei verschiedene Beteiligungshöhen
Die Gleichbeteiligungen der 2er und 3er wandeln wir nun ab in 2x35% und 3x10%. Der größte gemeinsamer Teiler ist 5%. 5% ist 1/20 von 1. Somit wiederholt sich alle 20 Cent der verbleibende aufzuteilende Rest, mit 0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19
Für die Korrekturen der Cents der beiden Gruppen ergibt sich nun:
2er-Gruppe: 0;-1;1;-1;1;0;1;0;2;0;-1;1;-1;1;0;1;0;2;0;2
3er-Gruppe: 0;1;0;1;0;1;0;1;0;1;1;0;1;0;1;0;1;0;1;0
Die Summe der Aufteilungsbeträge führt nun zu fast der gleichen Zuordnung auf die Beteiligten, wie es die Summe der Zuordnungen der Einzelbeträge tun.
_________________________________________________________________________
Hier noch mal an der Datei nachzuvollziehen:
https://www.herber.de/bbs/user/119662.xlsx

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um Rundungsdifferenzen in Excel zu vermeiden, folge diesen Schritten:

Berechnung der Gesamtkosten: Setze den Gesamtbetrag in eine Zelle, z.B. F7.
Verteile die Kosten: Verwende die Formel =RUNDEN($F$7/Anzahl_Mitarbeiter;2) in den Zellen, wo die einzelnen Beträge stehen sollen (z.B. C1:F1 für 4 Mitarbeiter).
Korrektur der Restbeträge: Berechne den Unterschied zwischen dem Gesamtbetrag und der Summe der verteilten Beträge in einer separaten Zelle, z.B. in B1: =F7-SUMME(C1:F1).
Verteile den Rest: Addiere den Restbetrag manuell oder mithilfe einer weiteren Formel, um die Gesamtsumme wieder auf den ursprünglichen Betrag zu bringen.

Diese Methode hilft, Rundungsfehler in Excel zu vermeiden, indem sie die Rundung nur bei der letzten Berechnung anwendet.

Häufige Fehler und Lösungen

Fehler: Gesamtbetrag stimmt nicht mit der Summe überein.
- Lösung: Überprüfe die Rundungsformeln in den Zellen C1:F1. Stelle sicher, dass die Anzahl der Mitarbeiter korrekt ist und die Berechnung in der letzten Zelle (z.B. B1) erfolgt.
Fehler: Rundungsdifferenzen bleiben bestehen.
- Lösung: Stelle sicher, dass du die Formel für den Restbetrag korrekt anwendest. Verteile den Restbetrag gleichmäßig auf die Beteiligten.

Alternative Methoden

Gleichaufteilung: Wenn du eine ungerade Anzahl an Beteiligten hast, kannst du die Beträge in Gruppen aufteilen und manuell anpassen.
Verwendung von VBA: Erstelle ein Makro, das die Verteilung automatisiert und Rundungsfehler in Excel ausgleicht, indem es die Beträge gleichmäßig verteilt.

Praktische Beispiele

Beispiel 1: Du hast einen Gesamtbetrag von 100 Euro, der auf 3 Mitarbeiter verteilt werden soll. Verwende die Formel:
```
=RUNDEN(100/3;2)
```
Dies gibt dir die gerundeten Beträge für jeden Mitarbeiter. Überprüfe den Restbetrag in einer separaten Zelle.
Beispiel 2: Wenn du eine Liste von Transaktionen hast und die Gesamtbeträge aufteilen möchtest, kannst du die oben beschriebenen Schritte für jede Transaktion wiederholen und die Differenzen ausgleichen.

Tipps für Profis

Nutze die Funktion RUNDEN sparsam. Versuche, die Rundung erst am Ende der Berechnungen anzuwenden.
Überlege, ob die Verwendung von ABRUNDEN oder AUFRUNDEN an geeigneten Stellen sinnvoll sein könnte, um Rundungsfehler in Excel zu minimieren.
Experimentiere mit der Kombination von Formeln und VBA, um größere Datenmengen effizient zu verarbeiten und Rundungsdifferenzen zu vermeiden.

FAQ: Häufige Fragen

1. Was sind Rundungsdifferenzen in Excel?
Rundungsdifferenzen treten auf, wenn die Summe gerundeter Werte nicht mit dem ursprünglichen Betrag übereinstimmt.

2. Wie kann ich Rundungsfehler in Excel vermeiden?
Verwende die Rundungsformeln strategisch und berechne den Restbetrag erst am Ende der Verteilung.

3. Gibt es eine einfache Methode, um Rundungsdifferenzen auszugleichen?
Ja, berechne den Unterschied zwischen dem Gesamtbetrag und der Summe der gerundeten Beträge und verteile diesen Rest gleichmäßig auf die Beteiligten.

Beliebteste Forumthreads (12 Monate)