Excel 2010: Bug in der Excel-Funktion REST

Eingestellt: 22.07.2011
Letzte Änderung: 22.07.2011


 

Kurzbeschreibung

Bis einschließlich Excel 2007 ist die Excel-Funktion REST(Zahl; Divisor) von einem Bug betroffen, welcher sich darin zeigt, dass als Ergebnis der Fehlerwert #ZAHL! ausgegeben wird, wenn das Produkt von Divisor und 227 kleiner gleich Zahl ist.[1]

Ab Excel 2010 wird nun dieser Bug durch einen anderen ersetzt. Die Excel-Funktion REST liefert als Ergebnis anstelle des Wertes null den Wert von Divisor (bzw. einen nahe an Divisor liegenden Wert), wenngleich Zahl ein Vielfaches von Divisor ist und somit Zahl ohne Rest teilbar sein müsste.[2]

 

Beispiel

Die Excel-Funktion REST gibt in den Versionen bis einschließlich Excel 2007 den Fehlerwert #ZAHL! zurück, wenn das Produkt von Divisor und 227 kleiner gleich Zahl ist. Der einfachste Fall, der diese Bedingung erfüllt, ist, wenn Zahl den Wert 227 = 134.217.728 und Divisor den Wert 1 annimmt. Abbildung 1 zeigt diesen Fall in Zeile 2 des Tabellenausschnitts. Demgegenüber ist in Zeile 3 die Bedingung nicht mehr erfüllt und liefert ein Ergebnis mit dem größten zulässigen Faktor in Zelle A3.


Formeln in der Tabelle:
C2: =REST(A2;B2)
Formel nach unten kopieren

Abbildung 1: Bug in der Excel-Funktion REST bis Excel 2007.

Anhand der Excel-Funktion REST lässt sich oftmals die Problematik der Gleitkommarechnung erkennen.[3] Ergebnisse, die bspw. den Wert null annehmen müssten, werden nicht als null, sondern als sehr nahe an null liegende, meist im Format Wissenschaftlich dargestellte Zahlen ausgegeben.

Dieses Thema besteht in Excel 2010 fort und könnte im Zusammenhang mit der Verwendung eines neuen Algorithmus, der die Genauigkeit und Geschwindigkeit der Excel-Funktion REST in Excel 2010 erhöhen soll, sogar die Ursache für den neuen Bug sein.[4] Der Fehler zeichnet sich dadurch aus, dass als Ergebnis dieser Excel-Funktion der Wert von Divisor ausgegeben wird, wenngleich das Ergebnis null sein müsste. Abbildung 3 zeigt dieses Verhalten anhand von einigen Beispielen.[5]


Formeln in der Tabelle:
C2: =REST(A2;B2)
Formel nach unten kopieren

Abbildung 2: Excel 2010 - Bug in der Excel-Funktion REST.

Für eine kurze Untersuchung dieses Verhaltens vereinbaren wir im Folgenden, dass eine positive Zahl durch ein Pluszeichen (+), eine negative Zahl durch ein Minuszeichen (-) symbolisiert wird. Übertragen auf die beiden Parameter der Excel-Funktion REST, lassen sich somit vier Fälle unterscheiden: REST(+; +), REST(+; -), REST(-; +), REST(-; -).[6] Abbildung 2 zeigt, dass der Bug in jedem der vier Fälle auftreten kann. Wird die Anzahl der Nachkommastellen hinreichend erhöht, dann kann es sein, dass bei diesen das Ergebnis von REST nicht dem Wert von Divisor selbst, sondern einem nahe an Divisor liegendem Wert entspricht.

Betrachten wir nun für ein jedes Beispiel alle vier Fälle, so kann bei einem Blick auf die Ergebnisse eine Regelmäßigkeit vermutet werden. Tritt der Bug auf, dann sind stets zwei der vier Fälle eines Beispiels von diesem betroffen, während in den anderen beiden Fällen ein nahe an null liegender Wert als Ergebnis geliefert wird. Unter der Annahme dieser Regelmäßigkeit lassen sich nunmehr sechs (bzw. vier über zwei) Kombinationen des paarweisen Auftretens des Bugs unterscheiden. Der Bug findet sich jedoch lediglich in zwei bestimmten Kombinationen, so dass folgende Vermutungen abgeleitet werden können:

  • Ist REST(+; +) von dem Bug betroffen, dann trifft dies auch für REST(-; -) zu vice versa (während REST(+; -) und REST(-; +) nicht betroffen sind).
  • Ist REST(+; -) von dem Bug betroffen, dann trifft dies auch für REST(-; +) zu vice versa (während R(+; +) und REST(-; -) nicht betroffen sind).
  • In den anderen vier Kombinationen tritt der Bug nicht auf.
  • Abbildung 3 illustriert den möglichen Zusammenhang zwischen den Vorzeichen von Zahl und Divisor und dem Auftreten des Bugs der Excel-Funktion REST in Excel 2010.


    Formeln in der Tabelle:
    C2: =REST(A2;B2)
    D2: =REST(A2;-B2)
    E2: =REST(-A2;B2)
    F2: =REST(-A2;-B2)
    Formeln nach unten kopieren

    Abbildung 3: Möglicher Einfluss der Vorzeichen von Zahl und Divisor.

    Weiter liegt der Untersuchung kein Ergebnis vor, bei dem der Bug auftritt und Divisor eine Ganzzahl ist. Werden Zahl und/oder Divisor jedoch selbst vorab noch berechnet, dann kann auch für diesen Fall ein Beispiel für Excel 2010 angebracht werden wie den Zellen C5 und C8 der Abbildung 4 zu entnehmen ist.[7] Demgegenüber sind in früheren Excel-Versionen (Excel 2007, Excel 2003 und Excel 2002) die Ergebnisse für die Beispiele in Abbildung 4 korrekt.


    Formeln in der Tabelle:
    A2: =15*0,4
    A3: =15*(1,4-1)
    A4: =15*0,4
    A5: =15*(1,4-1)
    A6: =15*(1,4-1)
    A7: =-15*(1,4-1)
    A8: =-15*(1,4-1)

    B2: =15*0,4
    B3: =15*(1,4-1)
    B4: =15*(1,4-1)
    B5: =15*0,4
    B6: =-15*0,4
    B7: =15*0,4
    B8: =-15*0,4

    C2: =REST(A2;B2)
    Formel nach unten kopieren

    Abbildung 4: Excel 2010 - Fehlerhafte Ergebnisse in der Excel-Funktion REST.

     

    Workaround

    Als Workarround bietet sich die nachstehende Excel-Formel an

    =A2-B2*GANZZAHL(A2/B2)

    wobei in Zelle A2 der Wert für Zahl und in Zelle B2 der Wert für Divisor steht.

    Wenn Sie eine Excel-Formel entsprechend der VBA-Funktion MOD verwenden möchten, dann wird zumeist auf die Excel-Formel

    =A2-B2*QUOTIENT(A2;B2)

    verwiesen.[8] Bei einigen Werten zeigt jedoch auch diese Excel-Formel Schwächen und zwar sowohl in Excel 2003, Excel 2007 als auch in Excel 2010.


    Formeln in der Tabelle:
    C2: =A2-B2*QUOTIENT(A2;B2)
    Formeln nach unten kopieren

    Abbildung 5: Fehler in der Excel-Funktion QUOTIENT

    Die Ursache liegt vermutlich wieder in der Gleitkommarechnung. Offensichtlich ist jedoch, dass die Ergebnisse der Beispiele in Abbildung 5 auf Grund der Berechnung der Excel-Funktion QUOTIENT, die den ganzzahligen Anteil einer Division zurückgibt, falsch sind. Anstelle des Wertes 12 (bzw. 6, 3, 3, 7) liefert die Excel-Funktion den Wert 11 (bzw. 5, 2, 2, 6), so dass die Ergebnisse der gesamten Excel-Formel ebenfalls nicht korrekt sind.

     

    Resümee

    Der Excel-Bug ist von besonderer Tragweite, da die Excel-Funktion REST einerseits beliebt ist und vielfältige Einsatzmöglichkeiten bietet. Beispielsweise wird sie gerne zur Navigation und zur Bildung von Reihen und Zufallszahlen oder bei der Währungsrechnung und Datumsbestimmung eingesetzt. Andererseits lassen sich schnell beachtlich viele Beispiele finden, die von diesem Bug betroffen sind.

    Als Workaround ist die Excel-Formel mit der Excel-Funktion GANZZAHL zu empfehlen, bei der an dieser Stelle kein Fehler gefunden wurde. Es kann und sollte daraus jedoch nicht geschlossen werden, dass diese Excel-Formel vor der Gleitkommarechnung gefeit ist und ausschließlich korrekte Werte zurückgibt.

    Hinsichtlich der Excel-Funktion REST könnte eine längere und systematischere Untersuchung als die unternommene helfen den Bug zutreffender zu charakterisieren und das Ausmaß besser abzuschätzen. Interessant dürfte auch ein intensiver Blick auf diejenigen Excel-Funktionen sein, deren Genauigkeit in Excel 2010 ebenfalls verbessert worden sind.

     

    [1] Vgl. Microsoft Hilfe und Support (2007): XL: MOD() Function Returns #NUM! Error Value.

    [2] Vgl. Microsoft TechNet (2011): Excel IT Pro Discussions Excel 2010 MOD bug.

    [3] Vgl. Microsoft Hilfe und Support (2010): Artikel KB 78113 Floating-point arithmetic may give inaccurate results in Excel.

    [4] Vgl. Microsoft TechNet (2010): Changes in Excel 2010 und Microsoft Office (2010): Neuerungen: Änderungen an Funktionen von Excel.

    [5] Beachten Sie bitte bei der Excel-Funktion REST, dass das Ergebnis stets dasselbe Vorzeichen besitzt wie Divisor. Vgl. Microsoft Office (2010): REST (Funktion).

    [6] Wenn Divisor (bzw. Zahl) den Wert 0 hat, gibt REST den Fehlerwert #DIV/0! (bzw. den Wert null) zurück. Diese beiden Fälle werden von der Betrachtung ausgeschlossen.

    [7] Vgl. Microsoft Answers (2010): Problem with the MOD function in excel?.

    [8] Vgl. Microsoft Hilfe und Support (2006): XL2000: MOD Function and Mod Operator Return Different Values.

     

    Haben Sie Fragen oder Anregungen zu diesem Artikel?

    Dann senden Sie einfach eine Nachricht an:

    E-Mail:

     

    Lesen Sie auch:
    ___________________________________________________________________

    Aktualisierungsfehler bei den Funktionen SPALTE und ZEILE – Bug in Excel?