Excel-Formel: So beheben Sie die #SPILL! Fehler -

Inhaltsverzeichnis

Zusammenfassung

Ein # SPILL-Fehler tritt auf, wenn ein Überlaufbereich durch etwas im Arbeitsblatt blockiert wird. Die Lösung besteht normalerweise darin, den Überlaufbereich von störenden Daten zu befreien. Weitere Informationen und Schritte zum Auflösen finden Sie weiter unten.

Erläuterung

Über das Verschütten und die #SPILL! Error

Mit der Einführung von Dynamic Arrays in Excel "verschütten" Formeln, die mehrere Werte zurückgeben, diese Werte direkt auf das Arbeitsblatt. Das Rechteck, das die Werte einschließt, wird als "Überlaufbereich" bezeichnet. Wenn sich Daten ändern, wird der Überlaufbereich nach Bedarf erweitert oder verkleinert. Möglicherweise werden neue Werte hinzugefügt oder vorhandene Werte verschwinden.

Video: Verschütten und der Verschüttungsbereich

Ein # SPILL-Fehler tritt auf, wenn ein Überlaufbereich durch etwas im Arbeitsblatt blockiert wird. Manchmal wird dies erwartet. Sie haben beispielsweise eine Formel eingegeben und damit gerechnet, dass sie verschüttet wird. Vorhandene Daten im Arbeitsblatt sind jedoch im Weg. Die Lösung besteht lediglich darin, den Überlaufbereich von störenden Daten zu befreien.

Manchmal kann der Fehler jedoch unerwartet und daher verwirrend sein. Lesen Sie weiter unten, wie dieser Fehler verursacht werden kann und wie Sie ihn beheben können.

Verschüttungsverhalten ist nativ

Es ist wichtig zu verstehen, dass das Verschüttungsverhalten automatisch und nativ ist. In Dynamic Excel (derzeit nur Office 365 Excel) kann jede Formel, auch eine einfache Formel ohne Funktionen, zu Ergebnissen führen. Obwohl es Möglichkeiten gibt, zu verhindern, dass eine Formel mehrere Ergebnisse zurückgibt, kann das Verschütten selbst mit einer globalen Einstellung nicht deaktiviert werden.

Ebenso gibt es in Excel keine Option zum Deaktivieren von # SPILL-Fehlern. Um einen # SPILL-Fehler zu beheben, müssen Sie die Hauptursache des Problems untersuchen und beheben.

Fix # 1 - Löschen Sie den Überlaufbereich

Dies ist der am einfachsten zu lösende Fall. Die Formel sollte mehrere Werte enthalten, gibt aber stattdessen #SPILL zurück! weil etwas im Weg ist. Um den Fehler zu beheben, wählen Sie eine beliebige Zelle im Überlaufbereich aus, damit Sie deren Grenzen sehen können. Verschieben Sie dann entweder die blockierenden Daten an einen neuen Speicherort oder löschen Sie die Daten vollständig. Beachten Sie, dass Zellen im Überlaufbereich leer sein müssen. Achten Sie daher auf Zellen, die unsichtbare Zeichen enthalten, z. B. Leerzeichen.

Im folgenden Bildschirm blockiert das "x" den Überlaufbereich:

Wenn das "x" entfernt wird, verschüttet die UNIQUE-Funktion die Ergebnisse normal:

Fix # 2 - @ Zeichen hinzufügen

Vor Dynamic Arrays hat Excel stillschweigend ein Verhalten namens "implizite Schnittmenge" angewendet, um sicherzustellen, dass bestimmte Formeln mit dem Potenzial, mehrere Ergebnisse zurückzugeben, nur ein einziges Ergebnis zurückgeben. In nicht dynamischem Array-Excel geben diese Formeln ein normal aussehendes Ergebnis ohne Fehler zurück. In bestimmten Fällen kann jedoch dieselbe in Dynamic Excel eingegebene Formel einen # SPILL-Fehler erzeugen. Im folgenden Bildschirm enthält Zelle D5 beispielsweise die folgende Formel:

=$B$5:$B$10+3

Diese Formel würde beispielsweise in Excel 2016 keinen Fehler auslösen, da implizite Schnittmengen verhindern würden, dass die Formel mehrere Ergebnisse zurückgibt. In Dynamic Excel gibt die Formel jedoch automatisch mehrere Ergebnisse an das Arbeitsblatt zurück, die ineinander stoßen, da die Formel von D5: D10 kopiert wird.

Eine Lösung besteht darin, das @ -Zeichen zu verwenden, um eine implizite Schnittmenge wie folgt zu aktivieren:

= @$B$5:$B$10+3

Mit dieser Änderung gibt jede Formel wieder ein einzelnes Ergebnis zurück und der Fehler #SPILL verschwindet.

Hinweis: Dies erklärt teilweise, warum das "@" -Zeichen plötzlich in Formeln angezeigt wird, die in älteren Excel-Versionen erstellt wurden. Dies geschieht, um die Kompatibilität aufrechtzuerhalten. Da Formeln in älteren Excel-Versionen nicht in mehrere Zellen übertragen werden können, wird das @ hinzugefügt, um dasselbe Verhalten beim Öffnen der Formel in Dynamic Excel sicherzustellen.

Fix # 3 - native dynamische Array-Formel

Eine andere (bessere) Möglichkeit, den oben gezeigten # SPILL-Fehler zu beheben, besteht darin, eine native dynamische Array-Formel in D5 wie folgt zu verwenden: