In Excel ist ein hässlicher Berechnungsfehler aufgetreten. Es scheint, dass das Problem tief in die Excel-Berechnungs-Engine eindringt und nicht einfach zu beheben ist.
Der Kern des Problems ist eine einfache Tatsache: Excel speichert 15 Stellen Genauigkeit in einer Zelle. Sie können Zahlen mit 20 Ziffern haben, aber alle Ziffern zwischen der letzten signifikanten Ziffer und der Dezimalstelle müssen Null sein.
Ich habe kürzlich zwei Fälle gesehen, in denen die Berechnungs-Engine von Excel die falschen Ergebnisse zurückgab. Als ich mich mit dem Problem befasste und mir das zugrunde liegende XML ansah, war ich überrascht zu sehen, dass Excel 17 Stellen im XML heimlich speicherte.
Das Problem ist, dass Excel nur 15 Ziffern anzeigt. Sie denken also, dass Sie eine Nummer als 0.123456789012345 gespeichert haben, diese aber tatsächlich als 0.12345678901234567 gespeichert ist.
Sie können die letzten beiden Ziffern nicht sehen. Bei den meisten Funktionen von Excel werden die letzten beiden Ziffern ignoriert. Wenn * alle * Funktionen die letzten beiden Ziffern ignorieren würden, hätten wir kein Problem. Bisher werden für Sortierung, RANG und FREQUENZ alle 17 Ziffern verwendet.
Im Folgenden finden Sie einen bekannten Trick zum Einordnen von Zellen. Wenn jeder Rang genau einmal angezeigt werden soll, können Sie RANK und COUNTIF kombinieren. Im Bild unten sind Claire, Flo, Ivana und Lucy zu 115% gebunden. Mit der RANK + COUNTIF-Formel sollten sie den Rängen 5, 6, 7 und 8 zugeordnet werden.
Aber die Formel schlägt fehl. Zwei Zeilen werden als 7 eingestuft. Das passiert nie. Vier Formeln in Spalte D stellen sicher, dass die 115% in B6, B9, B12 und B15 gleich sind. Die =B6=B15Formel gibt an, dass beide Zellen dieselben Daten enthalten.
Schauen Sie sich beim Versuch, das Problem zu isolieren, nur die RANK-Funktion an. Es sollte ein 4-Wege-Gleichstand am 4. für die Menschen mit 115% melden. Aber irgendwie liegt Lucy in Reihe 15 vor den anderen drei.
Um das herauszufinden, habe ich eine Hilfeanfrage an die anderen Excel-MVPs gesendet. Jan Karel Pieterse öffnete die Excel-Datei und sah im XML nach. In der XML können Sie sehen, dass sie 17 Stellen Genauigkeit speichern. Die vier Zellen, die in Excel wie ein Gleichstand aussehen, sind im XML nicht gebunden. Einer der 115% wird als 1.1500000000000001 gespeichert und die anderen sind 1.1499999999999999.
Bisher werden für Sortierung, Rangfolge und die Funktion FREQUENCY die zusätzlichen Ziffern verwendet. Warum ist das ein Problem? Weil wir auf RANK und COUNTIF zählen, um beide die gleiche Anzahl von Ziffern zu verwenden. Bei einer Funktion mit 15 Stellen und der anderen mit 17 Stellen haben Sie ein Problem.
Im Moment scheint die Lösung darin zu bestehen, alle Ihre Antworten mit zu konvertieren =ROUND(A4,15).
Jeden Freitag untersuche ich einen Fehler oder ein anderes fischartiges Verhalten in Excel. Dieser Berechnungsfehler ist schwer zu erkennen und gilt als großer Fisch.
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Jedes Mal, wenn Sie Zellen zusammenführen, ermorden Sie ein Kätzchen"
Szilvia Juhasz
