Excel: Gleiche Zahlen angeblich ungleich!?

[Tarkoon]

Hallo,

ich habe ein ziemlich seltsames Problem in Excel (2016).
In meinem selbst erstellten Kassenbuch arbeite ich durchgehend mit Eurowerten mit zwei Nachkommastellen.
Daraus werden auf einem Übersichtsblatt mit komplizierten (Matrix-)Formeln Werte ausgewählt und verrechnet - allerdings nur mit Additionen.
Jetzt wollte ich als Kontrollanzeige ne bedingte Formatierung machen, die zwei Werte auf Gleichheit überprüft, die auf verschiedenen Rechenwegen entstanden sind.
Die Werte sehen auch im Tabellenblatt gleich aus, aber die Prüfung als Formel ergibt immer Ungleichheit...
Wenn ich als Experiment die Werte jeweils mit =TEXT() in einen String im Format "0,00" umwandel und diese Strings vergleiche, dann zeigt die Formel auch Gleichheit an.
Ich habe mir die beiden Zahlen schon mal mit 20 Nachkommastellen anzeigen lassen, aber kein Unterschied zu sehen.
Es gibt ja auch nur Summen...
Ich verstehe das ganze absolut nicht!
Das Umwandeln in Text ist natürlich ein Workaround, aber das kann es ja nicht sein.
Was könnte ich noch testen???
Habt ihr eine Idee?

Danke und Grüße
Tobias

 

[mac4life]

Benutze doch einfach:

Runden(Zahl;2) = Runden(Zahl2;2)

in der bedingten Formatierung.
Das hilft bei mir immer, bei solchen Fällen.

 

[Tunguska]

... mit komplizierten (Matrix-)Formeln Werte ausgewählt und verrechnet ...

Das Problem dabei ist, dass niemand deine Berechnungen nachvollziehen kann, solange sie geheim sind. Und da man bei Excel an jeder Ecke durch Rundungs- oder Formatfehler etwas falsch machen kann, wird das nur "Glaskugel"-Leserei.

Als Beispiel: 4 Zahlen runden und dann addieren ergibt nicht zwingend das gleiche Ergebnis wie 4 Zahlen addieren und dann runden.

 

[ella_one]

Hallo Tobias,

Die Option (siehe Bild) "Genauigkeit wie angezeigt festlegen" stellt sicher, dass kaufmännisch korrekt gerechnet wird.

Viel Erfolg

 

[steve1da]

Hola,

das nennt sich Gleitkommaproblematik. Runden() hilft.

Gruß,
steve1da

 

[Tarkoon]

{=SUMME(WENN((INDIREKT("Giro"&RECHTS($B$2;2)&"!$J$5:$J$204")=J$11) *(INDIREKT("Giro"&RECHTS($B$2;2)&"!$K$5:$K$204")=K12); (INDIREKT("Giro"&RECHTS($B$2;2)&"!$F$5:$F$204")))) +SUMME(WENN((INDIREKT("Bar"&RECHTS($B$2;2)&"!$J$5:$J$204")=J$11) *(INDIREKT("Bar"&RECHTS($B$2;2)&"!$K$5:$K$204")=K12); (INDIREKT("Bar"&RECHTS($B$2;2)&"!$F$5:$F$204")))) +SUMME(WENN((INDIREKT("Spar"&RECHTS($B$2;2)&"!$J$5:$J$204")=J$11) *(INDIREKT("Spar"&RECHTS($B$2;2)&"!$K$5:$K$204")=K12); (INDIREKT("Spar"&RECHTS($B$2;2)&"!$F$5:$F$204")))) +SUMME(WENN((INDIREKT("Beach"&RECHTS($B$2;2)&"!$J$5:$J$204")=J$11) *(INDIREKT("Beach"&RECHTS($B$2;2)&"!$K$5:$K$204")=K12); (INDIREKT("Beach"&RECHTS($B$2;2)&"!$F$5:$F$204"))))}

Die einzelnen Werte auf der Übersichtsseite werden mit dieser und bis auf Felder identische Formeln erstellt - danach nur noch addiert. Muss also wenn dann dort irgendwo liegen.

das nennt sich Gleitkommaproblematik. Runden() hilft.

Wie kommt es dazu, wenn nur addiert wird???

 

[steve1da]

Hola,

die Gleitkommaproblematik gilt bei jeder Rechenoperation. Das hat auch nichts mit excel zu tun, sondern wie der PC mit Zahlen umgeht.

Gruß,
steve1da

 

[Tarkoon]

Ok, der Ansatz mit der Einstellung auf "Genauigkeit wie angezeigt festlegen" ist für mich wohl der sinnvollste - danke, die Einstellung kannte ich noch garnicht. ;-)

Lösung hab ich ja jetzt, aber ich muss gestehen, ich verstehe immer noch nicht ganz, warum es bei reinen Additionen von Zahlen in der maximalen Größenordnung XXXX,XX zu Gleitkommafehlern kommt - dafür sollten die Register doch bei weitem groß genug sein...

EDIT1:
Ein Artikel dazu bei Chip sagt:
"Das größere Problem, das Excel häufig bei der Addition macht, sind Rundungsfehler. Dabei ergeben die einzelnen Werte eine andere Summe als Excel ausgerechnet hat. Excel rundet zunächst jeden einzelnen Wert ab der 15. Stelle und addiert die gerundeten Werte anschließend. Dadurch kommt ein erheblicher Rundungsfehler in der Summe zustande."
Aber wie gesagt, "ab der 15. Stelle" sollte doch bei mir eigentlich egal sein, würde ich jetzt denken...

EDIT2: !!! TOTAL KURIOS !!!
Jetzt hab ich die Einstellung mit der Genauigkeit gemacht, aber in einem Fall klappt es trotzdem noch nicht und das bei einer der simpelsten Formeln in der ganzen Datei:
!!! Zahl A - Zahl B = Zahl C sei FALSCH, obwohl Zahl C = Zahl A - Zahl B !!!
Falls sich das ggf. mal jemand mit der passenden Ahnung anschauen möchte, könnte ich die Datei schicken, aber ich möchte sie nicht gerne hier für alle online stellen...

 

[Scientist]

Das Problem kann man recht anschaulich reproduzieren.
Gebe einfach mal in Zelle A2 "=A1+0,01" ein und "zieh" die Formel ein paar 100 Zellen nach unten.
Wenn du anschließend die Zellen kopierst und die Ergebnisse einfuegst, wirst du irgendwann sehen, dass die Zahlen nicht mehr nur auf 2 Stellen beschraenkt sind.

Die Loesung von mac4life sollte da eigentlich Abhilfe schaffen oder das Runden der Ergebnisse im allgemeinen.

 

[Tarkoon]

Was mich verrückt macht ist mein EDIT2:

Ich habe in ZelleA eine Zahl.
Ich habe in ZelleB eine Zahl.
In ZelleC steht "= ZelleA - ZelleB"
Die Formel "= ZelleA - ZelleB = ZelleC" ergibt aber FALSE.
Und das ganze bei aktivierter Einstellung mit der angezeigten Genauigkeit...

 

[Scientist]

In meiner Testdatei wurde das Problem mit "Genauigkeit wie angezeigt festlegen" nicht geloest und fuehrt auch genau du deinem Problem in EDIT2 ...
Da hilft nur die Funktion Runden ...

 

[Tarkoon]

Aber verrückt, dass so ein Problem schon bei einer einzelnen, absolut banalen Berechnung auftritt, und auch noch trotz der Einstellung, die gegen derartiges eigentlich helfen sollte...
Heißt das im Folgeschluss jetzt, dass man eigentlich bei ALLEN noch so banalen Berechnungen in Excel jeden Einzelwert runden muss???

 

[Nolag]

Excel rechnet halt mit binären Fließkommazahlen. Solche Fließkommazahlen dürfen eigentlich nie ohne Angabe einer erlaubten Abweichung auf Gleichheit verglichen werden. Das liegt daran, dass allein schon der Wert 0,1 nicht verlustfrei als Fließkommazahl repräsentiert werden kann. Rechnet man damit wird die Ungenauigkeit immer größer.

 

[Tarkoon]

Ja, dass das vielleicht in den Tiefen eines Prozessors zu einem Problem führt, ok, aber dass das bei einer hochentwickelten Software in so banalen Fällen nicht sauber abgefangen wird, ist sehr sehr schwach...

 

[Nolag]

Das Problem lässt sich leider nicht allgemeingültig in Software lösen. Bei binären Fließkommazahlen muss der Anwender wissen was er tut und entsprechend runden. Solange in Windows PCs kein Dezimal-Fließkommaprozessor verbaut ist, wird sich das leider auch so bald nicht ändern.

 

[Tarkoon]

Warum soll das Software nicht für spezielle Fälle umgehen können? In dem Moment, wo ich mich z. B. per Einstellung auf Festkomma bzw. Integer einschränke, was bei Finanzthemen in Kombination mit bestimmten Rechenoperatoren ja passen würde, sollte so ein Problem zu umgehen sein.
Eine allgemeingültige Lösung für Fließkommaberechnungen erwartet ja keiner.

 

[Olunixus]

Zieh doch beide Werte voneinander ab und prüfe ob der Betrag vom Ergebnis < 0,000001 ist

 

[Tarkoon]

Danke, Workarounds gibt es ja diverse, z.B. runden, in Text umwandeln etc... Bin nur erstaunt, dass man bei so einfachen Dingen zu sowas greifen muss...
Warum zum Beispiel vergleicht Excel nicht von selbst gerundete Werte, wenn man die Option mit den angezeigten Werten schon aktiviert.

 

[Nolag]

Eine allgemeingültige Lösung für Fließkommaberechnungen erwartet ja keiner.

Dann verstehe ich nicht wo das Problem ist. Wenn man weiß mit welchen Zahlensystemen und Operatoren man das Problem vermeidet, dann sollte man das auch tun.

 

[Tarkoon]

Kann ich irgendwo in Excel denn einstellen, dass ich mit Festkomma arbeiten möchte?
Ausschließlich addieren und subtrahieren tue ich ja schon...