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

Eingestellt: 01.08.2010
Letzte Änderung: 01.08.2010


 

Kurzbeschreibung

Im vorliegenden Artikel möchten wir Sie gerne darauf hinweisen, dass vermutlich ein Aktualisierungsfehler bei den Excel-Funktionen SPALTE und ZEILE in Microsoft Excel vorliegt. Dieser kann durch die Kombination aus dem Anwenden von CopyPaste oder Drag’n’Drop der Excel-Funktion SPALTE (bzw. ZEILE) und dem Einfügen einer Spalte (bzw. Zeile) zustande kommen.

Der vermeintliche Excel-Bug ist an zwei Bedingungen geknüpft und konnte von Andreas, Mourad und mir in intensiven Tests in den Versionen Excel 2010, Excel 2007, Excel 2003 und Excel 2002 nachvollzogen werden. Unsere Tests beinhalteten sowohl Einzelinstallationen der genannten Excel-Versionen als auch Parallelinstallationen unter Windows 7, Windows Vista, Server 2008 und XP. Zudem haben wir – leider erfolglose - Internet-Recherchen zu diesem Thema durchgeführt.[1]

 

Beispiel für die Excel-Funktion SPALTE

Zur Illustration des mutmaßlichen Aktualisierungsfehlers genügt die einfache Excel-Funktion und soll exemplarisch an einem Beispiel mit der Excel-Funktion SPALTE betrachtet werden. Hierbei werden die Ergebnisse der Excel-Funktion SPALTE per Copy-Paste- und per Drag’n’Drop-Methode mit denen der manuellen Eingabe gegenübergestellt. Auf ein Beispiel für die Excel-Funktion ZEILE wird bewusst verzichtet.

Im ersten Schritt wird die Excel-Funktion SPALTE mit drei unterschiedlichen Methoden in jeweils fünf Zellen eingefügt:

  • Methode I: Geben Sie für die CopyPaste-Methode in Zelle A1 die Excel-Funktion =SPALTE(J1) ein, kopieren Sie die Zelle A1, markieren Sie den Zielbereich B1:E1 und gehen Sie auf Einfügen.
  • Methode II: Geben Sie für die Drag’n’Drop-Methode in Zelle A5 die Excel-Funktion =SPALTE(J1) ein. Gehen Sie mit dem Mauszeiger auf die untere rechte Ecke der Zelle A5 bis sich der Mauszeiger in ein Pluszeichen (+) verwandelt. Drücken Sie nun die linke Maustaste und ziehen Sie den Bereich bei gedrückter linker Maustaste bis einschließlich der Zelle E5 und lassen hier die Maustaste wieder los.[2]
  • Methode III: Geben Sie über die Tastatur in Zelle A9 die Excel-Funktion =SPALTE(J1) ein und schließen Sie die Eingabe mit der Taste Enter ab. Wiederholen Sie den Vorgang für B9: =SPALTE(K1), C9: =SPALTE(L1), D9: =SPALTE(M1) und E9: =SPALTE(N1).
  • Abbildung 1 stellt die drei Methoden schematisch dar.

    Abbildung 1: Einfügen der Excel-Funktion SPALTE mittels 3 Methoden

    Wie nicht anders zu erwarten sind die Ergebnisse der drei Methoden identisch (vgl. Abbildung 2). Die Zellen A1, A5 und A9 liefern als Ergebnis der Excel-Funktion =SPALTE(J1) einen Wert von 10, da J die zehnte Spalte im Tabellenblatt ist, die Zellen B1, B5 und B9 den Wert 11 usw.[3]

    Abbildung 2: Ergebnisse der Excel-Funktion SPALTE vor dem Einfügen einer Spalte

    In einem zweiten Schritt fügen Sie bitte in einer der Spalten F:I des Tabellenblatts eine Spalte ein, indem Sie bspw. Zelle F1 wählen und markieren, Rechtsklick, Zellen einfügen auswählen, im Dialog Zellen einfügen Ganze Spalte aktivieren und OK drücken (vgl. Abbildung 3). Eine Alternative ist Spalte F markieren, Rechtsklick, Zellen einfügen.

    Abbildung 3: Vorgehensweise Spalte Einfügen

    Es liegen nun offensichtlich unterschiedliche Ergebnisse vor. Anscheinend liefern alle Zellen, die manuell eingetragen wurden (A1, A5 und A9:E9), korrekte Ergebnisse, während die Ergebnisse der übrigen Zellen mit der Excel-Funktion SPALTE (B1:E1 und B5:E5) unverändert geblieben sind und somit falsch sind – und dies obwohl sich der Bezug in den Excel-Funktionen korrekt geändert hat (vgl. Abbildung 4).

    Abbildung 4: Ergebnisse der Excel-Funktion SPALTE nach dem Einfügen einer Spalte

    Ein solches Verhalten der Excel-Funktion SPALTE (bzw. ZEILE) zeigt sich ebenfalls, wenn anstelle eines relativen Bezugs ein absoluter Bezug gesetzt wird (bspw. SPALTE($J$1) bzw. ZEILE($A$20) und/oder lediglich die Spalten (bzw. Zeilen) als Bezug eingesetzt werden (bspw. SPALTE(J:J) bzw. ZEILE(20:20). Auch der Einsatz in Excel-Formeln (bspw. =SUMME(SPALTE(J1);SPALTE(K1))) ist davon betroffen. Darüber hinaus ist dieses Verhalten davon unabhängig, ob die betroffenen Zellen ein- oder ausgeblendet sind.

     

    Korrektur

    Eine Neuberechnung (F9), ein Zwischenspeichern und/oder ein erneutes Öffnen der Arbeitsmappe korrigieren anscheinend diesen Fehler nicht. Damit das richtige Ergebnis angezeigt wird, haben Sie folgende Möglichkeiten:

    1. Gehen Sie in die jeweilige Zelle hinein (bspw. mit der Taste F2 oder Doppelklick mit der linken Maustaste) und bestätigen die Eingabe erneut mit der Taste Enter.[4]
    2. Fügen Sie bei Verwendung der Excel-Funktion SPALTE eine Zeile ein, bei Verwendung der Excel-Funktion ZEILE eine Spalte (Sie können anschließend diese Aktion unter Beibehalt der korrekten Ergebnisse rückgängig machen).
    3. Fügen Sie bei Verwendung der Excel-Funktion SPALTE eine weitere Spalte ein, bei Verwendung der Excel-Funktion ZEILE eine weitere Zeile, und machen Sie diese Aktion rückgängig (Strg+Z) oder löschen Sie die Spalte bzw. Zeile wieder.
    4. Fügen Sie eine Spalte (bzw. Zeile), welche sich links (bzw. oberhalb) von der Zelle mit der Excel-Funktion SPALTE (bzw. ZEILE) befindet, ein.
    5. Drücken Sie die Tastenkombination Strg+Alt+F9.[5]

     

    Bedingungen

    Der beschriebene Aktualisierungsfehler bei diesen Excel-Funktionen tritt wohlgemerkt lediglich unter zwei Bedingungen auf:

    1. Der CopyPaste- bzw. Drag’n’Drop-Bereich muss zwei Zellen oder mehr enthalten und
    2. die eingefügte Spalte (bzw. Zeile) muss sich rechts (bzw. unterhalb) von den per CopyPaste oder Drag'n'Drop eingefügten Zellen mit der Excel-Funktion und links (bzw. oberhalb) von den Zellen, die dem jeweiligen Bezug der Excel-Funktion entspricht, befinden.

    Dies bedeutet, üben Sie die CopyPaste- oder Drag'n'Drop-Methode separat für jede einzelne Zelle durch oder fügen Sie eine Spalte (bzw. Zeile) an einer anderen Position im Tabellenblatt ein, so tritt dieser Fehler nicht (oder nicht erkennbar) auf.

     

    Resümee

    Die Excel-Funktionen SPALTE und ZEILE sind beliebt und werden vielfältig eingesetzt, oftmals zur einfachen Indizierung, aber auch als Referenz in großen Tabellen sowie in komplexen Excel-Formeln. Die Frage ist, welchen Einfluss der Aktualisierungsfehler, sofern er einer ist, auf die Praxis hat, zumal er speicherresistent ist und auch bei ausgeblendeten Zellen existent ist.

    Wir haben mit bestem Wissen und Gewissen den Sachverhalt geprüft. Ob hierbei tatsächlich von einem Excel-Bug zu sprechen ist, der sich durch eine Vielzahl von Excel-Versionen zieht, können wir nur mutmaßen. Es wäre daher nett, wenn Sie dies testen und uns unter Angabe der verwendeten Excel-Version ein kurzes Feedback per E-Mail senden oder einen Kommentar zum Artikel schreiben könnten. Vielen Dank für Ihre Unterstützung.

     

    [1] Ein vergleichbares Problem wurde jedoch in VisualStudio von Chris Menegay auf connect.microsoft.com im Artikel Excel formulas not maintaining when new rows added to the top during an Refresh genannt.

    [2] Damit diese Methode funktioniert, muss die Option Ausfüllkästchen und Drag & Drop von Zellen aktivieren eingeschaltet sein.

    [3] Sie können dies auch leicht sehen, wenn Sie in den Excel-Optionen die Option Z1S1-Bezugsart aktivieren. Die Spaltenüberschriften werden sodann von Buchstaben nach Zahlen umgestellt.

    [4] Es genügt übrigens lediglich die Zelle des CopyPaste- bzw. Drag’n’Drop-Bereichs, welche zur kopierten oder gezogenen Zelle am nächsten liegt (in diesem Beispiel also B1 bzw. B5) auf oben beschriebene Weise zu aktualisieren und anschließend die Arbeitsmappe speichern, schließen und erneut öffnen, um für den gesamten CopyPaste- bzw. Drag’n’Drop-Bereich die korrekten Ergebnisse berechnen zu lassen.

    [5] "CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation." Quelle: Microsoft Office: Excel shortcut and function keys.

     

    Haben Sie Fragen oder Anregungen zu diesem Artikel?

    Dann senden Sie einfach eine Nachricht an:

    E-Mail:

     

    Lesen Sie auch:
    ___________________________________________________________________

    Eigenartiges Blinken der Fenstersymbole - Bug in Excel 2010?