Gleitkommaarithmetik kann zu ungenauen Ergebnissen in Excel führen
Zusammenfassung
In diesem Artikel wird erläutert, wie Microsoft Excel Gleitkommazahlen speichert und berechnet. Dies kann sich aufgrund von Rundung oder Datenkürzung auf die Ergebnisse einiger Zahlen oder Formeln auswirken.
Übersicht
Microsoft Excel wurde auf der Grundlage der IEEE 754-Spezifikation entwickelt, um festzulegen, wie Gleitkommazahlen gespeichert und berechnet werden. IEEE ist das Institute of Electronic and Electronic Engineers, eine internationale Einrichtung, die unter anderem Standards für Computersoftware und Hardware festlegt. Die 754-Spezifikation ist eine sehr weit verbreitete Spezifikation, die beschreibt, wie Gleitkommazahlen auf einem Binärcomputer gespeichert werden sollten. Sie ist deshalb so beliebt, weil sie es ermöglicht, Fließkommazahlen auf einem vernünftigen Speicherplatz unterzubringen und Berechnungen relativ schnell durchzuführen. Der 754-Standard wird in Gleitkommaeinheiten und numerischen Datenprozessoren von fast allen heutigen PC-basierten Mikroprozessoren verwendet, die Gleitkommamathematik implementieren, einschließlich Intel-, Sun-, Sun- und MIPS-Prozessoren.
Beim Speichern von Zahlen kann eine entsprechende Binärzahl jede Zahl oder Bruchzahl darstellen. Beispielsweise kann der Bruch 1/10 in einem Dezimalzahlensystem als 0,1 dargestellt werden. Die gleiche Zahl im Binärformat ergibt jedoch die folgende fortlaufende binäre Dezimalzahl:
0001100110011100110011 (usw.)
Dies kann unendlich wiederholt werden. Diese Zahl kann nicht in einem begrenzten Speicherplatz dargestellt werden. Daher wird diese Zahl beim Speichern um ungefähr -2,8E-17 abgerundet.
Es gibt jedoch einige Einschränkungen der IEEE 754-Spezifikation, die in drei allgemeine Kategorien fallen:
- Maximum/Minimum-Einschränkungen
- Präzision
- Fortlaufende binäre Zahlen
Weitere Informationen
Maximum/Minimum-Einschränkungen
Alle Computer verfügen über eine maximale und eine minimale Zahl, die verarbeitet werden kann. Da die Anzahl der Speicherbits, in denen die Zahl gespeichert ist, begrenzt ist, folgt daraus, dass die maximale oder minimale Zahl, die gespeichert werden kann, ebenfalls begrenzt ist. Für Excel ist die maximale Zahl, die gespeichert werden kann, 1,79769313486232E+308, und die minimale positive Zahl, die gespeichert werden kann, ist 2,2250738585072E-308.
Fälle, in denen IEEE 754 eingehalten wird
- Unterlauf: Unterlauf tritt auf, wenn eine Zahl generiert wird, die zu klein ist, um dargestellt zu werden. In IEEE und Excel ist das Ergebnis 0 (mit der Ausnahme, dass IEEE das Konzept -0 aufweist und Excel nicht).
- Überlauf: Überlauf tritt auf, wenn eine Zahl zu groß ist, um dargestellt zu werden. Excel verwendet eine eigene spezielle Darstellung für diesen Fall (#NUM!).
Fälle, in denen von IEEE 754 abgewichen wird
Denormalisierte Zahlen: Eine denormalisierte Zahl wird durch den Exponenten 0 angegeben. In diesem Fall wird die gesamte Zahl in der Mantisse gespeichert, und die Mantisse hat keine implizite führende 1. Die Folge ist ein Verlust an Präzision, und je kleiner die Zahl ist, desto mehr Präzision geht verloren. Zahlen am unteren Ende dieses Bereichs haben nur eine Genauigkeit von einer Stelle.
Beispiel: Eine normalisierte Zahl hat eine implizite führende 1. Wenn die Mantisse beispielsweise 0011001 darstellt, wird die normalisierte Zahl aufgrund der implizierten führenden 1 zu 10011001. Eine denormalisierte Zahl hat keine implizite führende Eins, daher bleibt die denormalisierte Zahl in unserem Beispiel für 0011001 gleich. In diesem Fall weist die normalisierte Zahl acht signifikante Ziffern (10011001) auf, während die denormalisierte Zahl fünf signifikante Ziffern (11001) mit führenden Nullen ohne Signifikanz enthält.
Denormalisierte Zahlen sind im Wesentlichen eine Problemumgehung, um zuzulassen, dass Zahlen gespeichert werden, die kleiner als die normale Untergrenze sind. Dieser optionale Teil der Spezifikation wird von Microsoft nicht implementiert, da denormalisierte Zahlen naturgemäß eine variable Anzahl signifikanter Ziffern aufweisen. Dies kann zu erheblichen Fehlern in den Berechnungen führen.
Positive/Negative Unendlichkeiten: Unendlichkeiten treten auf, wenn durch 0 geteilt wird. Excel unterstützt keine Unendlichkeiten, sondern gibt den Fehler #DIV/0! in diesen Fällen aus.
Not-a-Number (NaN): NaN wird verwendet, um ungültige Vorgänge darzustellen (z. B. unendlich/unendlich, unendlich oder die Quadratwurzel von -1). NaNs ermöglichen es einem Programm, nach einem ungültigen Vorgang fortzufahren. Excel generiert stattdessen sofort einen Fehler, z. B. #NUM! oder #DIV/0!.
Präzision
Eine Gleitkommazahl wird binär in drei Teilen innerhalb eines 65-Bit-Bereichs gespeichert: dem Vorzeichen, dem Exponenten und der Mantisse.
| Das Vorzeichen | Der Exponent | Die Mantisse |
|---|---|---|
| 1 Vorzeichenbit | 11-Bit-Exponent | 1 impliziertes Bit + 52-Bit-Bruchzahl |
Das Vorzeichen speichert das Vorzeichen der Zahl (positiv oder negativ), der Exponent speichert die Potenz von 2, um die die Zahl erhöht oder erniedrigt wird (die maximale/minimale Potenz von 2 ist +1,023 und -1,022), und die Mantisse speichert die eigentliche Zahl. Der endliche Speicherbereich für die Mantisse bestimmt, wie nahe zwei benachbarte Gleitkommazahlen beieinander liegen können (d. h. die Genauigkeit).
Die Mantisse und der Exponent werden beide als separate Komponenten gespeichert. Daher kann die mögliche Genauigkeit abhängig von der Größe der zu bearbeitenden Zahl (der Mantisse) variieren. Im Falle von Excel können zwar Zahlen von 1,79769313486232E308 bis 2,2250738585072E-308 gespeichert werden, aber nur mit einer Genauigkeit von 15 Stellen. Diese Einschränkung ist ein direktes Ergebnis der strikten Einhaltung der IEEE 754-Spezifikation und stellt keine Einschränkung von Excel dar. Dieses Maß an Genauigkeit findet sich auch in anderen Tabellenkalkulationsprogrammen.
Gleitkommazahlen werden in der folgenden Form dargestellt, wobei Exponent der binäre Exponent ist:
X = Bruch * 2^(Exponent - Trend)
Bruch ist der normalisierte Bruchteil der Zahl. Normalisiert deshalb, weil der Exponent so angepasst wird, dass das führende Bit immer 1 ist. Auf diese Weise muss es nicht gespeichert werden und Sie erhalten ein weiteres Bit an Genauigkeit. Aus diesem Grund gibt es ein implizites Bit. Das ist vergleichbar mit der wissenschaftlichen Notation, bei der der Exponent so manipuliert wird, dass eine Stelle links vom Dezimalpunkt steht. Im Binärsystem können Sie den Exponenten jedoch immer so manipulieren, dass das erste Bit eine 1 ist, da es nur Einsen (1) und Nullen (0) gibt.
Trend ist der Trendwert, der verwendet wird, um zu vermeiden, dass negative Exponenten gespeichert werden müssen. Der Trend für Zahlen mit einfacher Genauigkeit ist 127 und 1.023 (Dezimal) für Zahlen mit doppelter Genauigkeit. Excel speichert Zahlen mit doppelter Genauigkeit.
Beispiel für sehr große Zahlen
Geben Sie Folgendes in eine neue Arbeitsmappe ein:
A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
Der resultierende Wert in Zelle C1 wäre 1,2E+200, derselbe Wert wie Zelle A1. Wenn Sie die Zellen A1 und C1 mithilfe der IF-Funktion vergleichen, z. B. IF(A1=C1), ist das Ergebnis TRUE. Dies ist auf die IEEE-Spezifikation zurückzuführen, nach der nur 15 signifikante Stellen der Genauigkeit gespeichert werden. Um die Berechnung oben zu speichern, benötigt Excel eine Genauigkeit von mindestens 100 Stellen.
Beispiel für sehr kleine Zahlen
Geben Sie Folgendes in eine neue Arbeitsmappe ein:
A1: 0.000123456789012345
B1: 1
C1: =A1+B1
Der Ergebniswert in Zelle C1 wäre 1,00012345678901 anstelle von 1,000123456789012345. Dies ist auf die IEEE-Spezifikation zurückzuführen, nach der nur 15 signifikante Stellen der Genauigkeit gespeichert werden. Um die obige Berechnung speichern zu können, wären in Excel mindestens 19 Stellen Genauigkeit erforderlich.
Korrigieren von Genauigkeitsfehlern
Excel bietet zwei grundlegende Methoden zum Ausgleich von Rundungsfehlern: die ROUND-Funktion und die Optionen Genauigkeit wie angezeigt oder Genauigkeit wie angezeigte festlegen für Arbeitsmappen.
Methode 1: Die ROUND-Funktion
Das folgende Beispiel verwendet die ROUND-Funktion, um eine Zahl auf fünf Stellen zu runden, wobei die vorherigen Daten verwendet werden. Auf diese Weise können Sie das Ergebnis erfolgreich mit einem anderen Wert vergleichen.
A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
Dies ergibt 1,2E+200.
D1: =IF(C1=1.2E+200, TRUE, FALSE)
Dies führt zu dem Wert TRUE.
Methode 2: Genauigkeit wie angezeigt
In einigen Fällen können Sie möglicherweise verhindern, dass sich Rundungsfehler auf Ihre Arbeit auswirken, indem Sie die Option Genauigkeit wie angezeigt verwenden. Diese Option erzwingt, dass der Wert jeder Zahl im Arbeitsblatt der tatsächlich angezeigte Wert ist. Gehen Sie wie folgt vor, um diese Einstellung zu aktivieren:
- Klicken Sie im Menü Datei auf Optionen und anschließend auf die Registerkarte Erweitert.
- Wählen Sie im Abschnitt Beim Berechnen dieser Arbeitsmappe die gewünschte Arbeitsmappe aus, und aktivieren Sie dann das Kontrollkästchen Genauigkeit wie angezeigt festlegen.
Wenn Sie z. B. ein Zahlenformat auswählen, das zwei Dezimalstellen anzeigt, und dann die Option Genauigkeit als angezeigt aktivieren, geht beim Speichern der Arbeitsmappe die gesamte Genauigkeit über zwei Dezimalstellen hinaus verloren. Diese Option wirkt sich auf die aktive Arbeitsmappe einschließlich aller Arbeitsblätter aus. Sie können diese Option nicht rückgängig machen und die verlorenen Daten wiederherstellen. Es wird empfohlen, die Arbeitsmappe zu speichern, bevor Sie diese Option aktivieren.
Fortlaufende Binärzahlen und Berechnungen, deren Ergebnis gegen Null geht
Ein weiteres Problem, das die Speicherung von Gleitkommazahlen im Binärformat beeinträchtigt, besteht darin, dass einige Zahlen, die in der Dezimalbasis von 10 endliche, sich nicht wiederholende Zahlen sind, in der Binärversion unendliche, sich wiederholende Zahlen sind. Das häufigste Beispiel hierfür ist der Wert 0,1 und seine Variationen. Obwohl diese Zahlen in Basis von 10 perfekt dargestellt werden können, wird dieselbe Zahl im Binärformat die folgende wiederholte binäre Zahl, wenn sie in der Mantisse gespeichert wird:
000110011001100110011 (und so weiter)
Die IEEE 754-Spezifikation sieht keine spezielle Regelung für bestimmte Zahlen vor. Es speichert, was in der Mantisse möglich ist, und der Rest wird abgeschnitten. Dies führt zu einem Fehler von etwa -2,8E-17 oder 0,000000000000000028 bei der Speicherung.
Selbst häufig verwendete Dezimalstellen, z. B. Dezimalstellen von 0,0001, können nicht exakt binär dargestellt werden. (0,0001 ist ein fortlaufender binärer Bruch mit einem Periode von 104 Bit). Dies ist vergleichbar mit der Problematik, warum der Bruch 1/3 nicht exakt in Dezimalzahlen dargestellt werden kann (eine Wiederholung von 0,33333333333333333333).
Betrachten Sie hierzu das folgende einfache Beispiel in Microsoft Visual Basic für Applikationen:
Sub Main()
MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Next I%
Debug.Print MySum
End Sub
Die Ausgabe lautet PRINT 0,999999999999996. Der kleine Fehler bei der Darstellung von 0,0001 im Binärformat überträgt sich auf die Summe.
Beispiel: Hinzufügen einer negativen Zahl
Geben Sie Folgendes in eine neue Arbeitsmappe ein:
A1: =(43.1-43.2)+1
Klicken Sie mit der rechten Maustaste auf die Zelle A1, und klicken Sie dann auf Zellen formatieren. Klicken Sie auf der Registerkarte „Zahl“ unter „Kategorie“ auf „Wissenschaftlich“. Legen Sie die Dezimalstellen auf 15 fest.
Anstatt 0,9 zeigt Excel 0,899999999999999 an. Da (43.1-43.2) zuerst berechnet wird, wird -0,1 vorübergehend gespeichert, und der Fehler beim Speichern von -0,1 wird in die Berechnung übernommen.
Beispiel, wenn ein Wert Null erreicht
Geben Sie in Excel 95 oder früher Folgendes in eine neue Arbeitsmappe ein:
A1: =1,333+1,225-1,333-1,225
Klicken Sie mit der rechten Maustaste auf die Zelle A1, und klicken Sie dann auf Zellen formatieren. Klicken Sie auf der Registerkarte „Zahl“ unter „Kategorie“ auf „Wissenschaftlich“. Legen Sie die Dezimalstellen auf 15 fest.
Anstelle von 0 zeigt Excel 95 -2,22044604925031E-16 an.
Mit Excel 97 wurde jedoch eine Optimierung eingeführt, die dieses Problem zu beheben versucht. Sollte eine Additions- oder Subtraktionsoperation einen Wert bei oder sehr nahe bei Null ergeben, kompensiert Excel 97 oder höher jeden Fehler, der durch die Konvertierung eines Operanden nach und von binär entsteht. Das obige Beispiel zeigt in Excel 97 und höher korrekt 0 oder 0.000000000000000E+00 in wissenschaftlicher Notation an.
Weitere Informationen zu Gleitkommazahlen und zur IEEE 754-Spezifikation finden Sie auf den folgenden Websites:
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für