Den SPILL-Fehler behandeln

Wenn eine Formel für ein dynamisches Array ihr vollständiges Ergebnis nicht anordnen kann, zeigt Excel in der Ankerzelle #SPILL! an. Die Formel ist normalerweise korrekt. Das Problem besteht darin, dass etwas den benötigten Ausgabebereich blockiert.

Stellen Sie sich vor, Sie möchten einen Bus auf einem Parkplatz abstellen, auf dem bereits ein Auto steht. Mit dem Bus ist alles in Ordnung, aber der Platz ist blockiert.

Die häufigste Ursache ist, dass eine oder mehrere Zellen im Spill-Bereich bereits Daten enthalten. Wenn =UNIQUE(A2:A12) den Bereich C1:C3 füllen muss, aber C2 einen versehentlichen Wert enthält, ist der Spill-Bereich blockiert.

Die Lösung ist einfach: Wählen Sie die Ankerzelle aus, sehen Sie sich die gestrichelte Umrandung des Spill-Bereichs an und leeren Sie alle nicht leeren Zellen innerhalb dieses Bereichs.

Klicken Sie auf die Zelle mit #SPILL!. Excel zeichnet einen gestrichelten Rahmen um den Bereich, den das Ergebnis belegen soll. Jede nicht leere Zelle innerhalb dieses Rahmens ist die Ursache.

Sie können auch auf das Warndreieck neben der Zelle klicken und Blockierende Zellen auswählen wählen. Dadurch springen Sie direkt zu den betreffenden Zellen und können deren Inhalte löschen.

Ein Spill-Bereich darf keine verbundenen Zellen überlappen. Auch eine leere verbundene Zelle gilt als Blockierung, weil der Spill-Bereich einzelne, separate Zellen zum Ausfüllen benötigt.

Wenn Ihre Ausgabe auf eine verbundene Titelzeile oder Beschriftung trifft, heben Sie die Verbindung dieser Zellen auf (Registerkarte Start, Umschalter Verbinden und zentrieren). Anschließend gibt die Formel das Ergebnis korrekt als Spill-Bereich aus.

Ein Spill-Vorgang schlägt auch fehl, wenn das Ergebnis über den Rand des Arbeitsblatts hinaus oder in eine Tabelle laufen würde. Eine Formel nahe der untersten Zeile, die Ergebnisse nach unten ausgeben muss, hat beispielsweise keinen Platz.

Verschieben Sie die Formel weiter nach oben oder in eine Spalte, unter der ausreichend Platz vorhanden ist. Stellen Sie sicher, dass in der Ausgaberichtung so viele leere Zellen vorhanden sind, wie das Ergebnis benötigt.

Excel-Tabellen (mit Strg+T erstellt) erlauben keine dynamischen Arrays innerhalb der Tabelle, da eine Tabelle erwartet, dass jede Zelle bearbeitbar ist.

Wenn sich Ihre Spill-Formel in einer Tabellenspalte befindet, wird #SPILL! angezeigt. Konvertieren Sie die Tabelle entweder zurück in einen normalen Bereich (Tabellenentwurf, In Bereich konvertieren) oder verschieben Sie die Formel aus der Tabelle heraus.

Auch der Verweis einer Spill-Formel auf eine gesamte Spalte kann #SPILL! auslösen, weil das Ergebnis sehr groß wird, mit anderen Inhalten kollidieren oder die Grenzen überschreiten kann.

Beispielsweise versucht =A:A*2, eine Million Zeilen auszugeben. Beschränken Sie den Verweis stattdessen auf die tatsächlichen Daten, etwa mit =A2:A100*2, damit der Spill-Bereich eine überschaubare, klar definierte Größe hat.

Fahren Sie mit dem Mauszeiger über das Warndreieck einer #SPILL!-Zelle, und Excel teilt Ihnen genau mit, warum der Fehler auftritt. Häufige Meldungen sind Der Überlaufbereich ist nicht leer, Der Überlaufbereich enthält verbundene Zellen und Der Überlaufbereich ist zu groß.

Lesen Sie diesen Tooltip immer zuerst. Er führt Sie direkt zur Ursache, statt dass Sie raten müssen, und jede Meldung entspricht einer der Lösungen, die Sie gerade kennengelernt haben.

Es ist wichtig, sich daran zu erinnern, dass ein #SPILL!-Fehler nur selten bedeutet, dass die Logik Ihrer Formel falsch ist. Die Berechnung war erfolgreich; nur die Platzierung ist fehlgeschlagen.

Sobald Sie die Blockierung beseitigen, wird dieselbe unveränderte Formel korrekt über mehrere Zellen verteilt. Vergleichen Sie das mit #VALUE! oder #REF!, die normalerweise tatsächlich auf ein Problem innerhalb der Formel hinweisen.

Sie geben =SORT(UNIQUE(A2:A20)) in D1 ein und erhalten #SPILL!. Sie wählen D1 aus, sehen, dass die gestrichelte Umrandung D1:D5 abdeckt, und bemerken eine alte Notiz in D3.

Sie löschen den Inhalt von D3. Sofort verteilt die Formel die sortierte Liste eindeutiger Werte über D1:D5. Die Formel selbst musste überhaupt nicht geändert werden.

Ein wenig Planung verhindert die meisten Überlauffehler. Geben Sie Ihren Formeln für dynamische Arrays eine leere Spalte oder einen leeren Bereich, in den sie sich ausbreiten können, und halten Sie Beschriftungen und Notizen von der Überlaufrichtung fern.

Wenn Sie auf einen anderen Überlauf verweisen, verwenden Sie den Operator # wie in =SUM(D1#), damit sich Ihre Formeln anpassen, wenn sich Bereiche vergrößern oder verkleinern. Erstellen Sie Ausgaben in freien, übersichtlichen Bereichen, dann wird #SPILL! nur selten auftreten.

Testen Sie Ihr Verständnis des SPILL-Fehlers.

Sie haben gelernt, dass #SPILL! ein Platzierungsproblem und kein Logikfehler ist:

• Am häufigsten ist eine Zelle im Überlaufbereich nicht leer.
• Verbundene Zellen und Tabellen verhindern den Überlauf.
• Vermeiden Sie Verweise auf ganze Spalten, durch die das Ergebnis zu groß wird.
• Verwenden Sie Blockierende Zellen auswählen und den Tooltip, um die Ursache zu finden.

Damit ist der Kurs zu dynamischen Arrays und Überläufen abgeschlossen. Sie können nun mit einer einzigen Formel viele Ergebnisse zurückgeben und Überläufe sicher beheben.