Dieser Artikel wird Ihnen helfen, alle Ursachen von #SPILL-Fehlern sowie die Lösungen zu ihrer Behebung in Excel 365 zu verstehen.
#VERSCHÜTTEN! ist eine neue Art von Excel-Fehler, der hauptsächlich auftritt, wenn eine Formel, die mehrere Berechnungsergebnisse erzeugt, versucht, ihre Ausgaben in einem Überlaufbereich anzuzeigen, dieser Bereich jedoch bereits einige andere Daten enthält.
Die blockierenden Daten können alles sein, einschließlich Textwert, verbundene Zellen, ein Leerzeichen oder sogar, wenn nicht genügend Platz für die Rückgabe der Ergebnisse vorhanden ist. Die Lösung ist einfach: Löschen Sie entweder den Bereich von blockierenden Daten oder wählen Sie ein leeres Array von Zellen aus, die keine Datentypen enthalten.
Beim Berechnen dynamischer Arrayformeln tritt normalerweise ein Überlauffehler auf, da die dynamische Arrayformel die Ergebnisse in mehrere Zellen oder ein Array ausgibt. Sehen wir uns genauer an, was diesen Fehler in Excel auslöst und wie er behoben werden kann.
Was verursacht einen Überlauffehler?
Seit der Einführung dynamischer Arrays im Jahr 2018 können Excel-Formeln mehrere Werte gleichzeitig verarbeiten und Ergebnisse in mehr als einer Zelle zurückgeben. Dynamische Arrays sind in der Größe veränderbare Arrays, mit denen Formeln basierend auf einer in eine einzelne Zelle eingegebenen Formel mehrere Ergebnisse an einen Zellbereich auf dem Arbeitsblatt zurückgeben können.
Wenn eine dynamische Matrixformel mehrere Ergebnisse zurückgibt, werden diese Ergebnisse automatisch in die benachbarten Zellen übertragen. Dieses Verhalten wird in Excel als „Verschütten“ bezeichnet. Und der Bereich der Zellen, in den die Ergebnisse einfließen, wird als "Spill-Bereich" bezeichnet. Der Überlaufbereich wird basierend auf den Quellwerten automatisch erweitert oder verkleinert.
Wenn eine Formel versucht, einen Spill-Bereich mit mehreren Ergebnissen zu füllen, aber durch etwas in diesem Bereich blockiert wird, tritt ein #SPILL-Fehler auf.
Excel verfügt jetzt über 9 Funktionen, die die Dynamic Array-Funktionalität verwenden, um Probleme zu lösen. Dazu gehören:
- REIHENFOLGE
- FILTER
- TRANSPOSIEREN
- SORTIEREN
- SORTIERE NACH
- RANDARRAY
- EINZIGARTIG
- XVERWEIS
- XMATCH
Dynamische Arrayformeln sind nur in „Excel 365“ verfügbar und werden derzeit von keiner der Offline-Excel-Software (z. B. Microsoft Excel 2016, 2019) unterstützt.
Spill-Fehler werden nicht nur durch das Blockieren von Daten verursacht, es gibt mehrere Gründe, warum Sie #Spill-Fehler erhalten können. Lassen Sie uns die verschiedenen Situationen untersuchen, in denen Sie dem #SPILL begegnen könnten! Fehler und wie man sie behebt.
Spill Range ist nicht leer
Eine der Hauptursachen für Überlauffehler ist, dass der Überlaufbereich nicht leer ist. Wenn Sie beispielsweise versuchen, 10 Ergebnisse anzuzeigen, aber Daten in einer der Zellen im Überlaufbereich vorhanden sind, gibt die Formel ein #SPILL! Error.
Beispiel 1:
Im folgenden Beispiel haben wir die Funktion TRANSPOSE in Zelle C2 eingegeben, um den vertikalen Bereich der Zellen (B2:B5) in einen horizontalen Bereich (C2:F2) umzuwandeln. Anstatt die Spalte in eine Zeile umzuwandeln, zeigt Excel uns das #SPILL! Error.
Und wenn Sie auf die Formelzelle klicken, sehen Sie einen gestrichelten blauen Rahmen, der den Überlaufbereich/-bereich (C2:F2) anzeigt, der benötigt wird, um die Ergebnisse wie unten gezeigt anzuzeigen. Außerdem sehen Sie ein gelbes Warnschild mit einem Ausrufezeichen darauf.
Um den Grund für den Fehler zu verstehen, klicken Sie auf das Warnsymbol neben dem Fehler und sehen Sie sich die grau markierte Meldung in der ersten Zeile an. Wie Sie sehen, steht hier „Spill range is not blank“.
Das Problem hierbei ist, dass die Zellen im Überlaufbereich D2 und E2 Textzeichen haben (nicht leer), daher der Fehler.
Lösung:
Die Lösung ist einfach: Löschen Sie entweder die Daten (entweder verschieben oder löschen), die sich im Überlaufbereich befinden, oder verschieben Sie die Formel an einen anderen Ort, an dem es keine Hindernisse gibt.
Sobald Sie die Blockade löschen oder verschieben, füllt Excel die Zellen automatisch mit den Ergebnissen der Formel. Wenn wir hier den Text in D2 und E2 löschen, transponiert die Formel die Spalte wie beabsichtigt in die Zeile.
Beispiel 2:
Im folgenden Beispiel zeigt die Formel, obwohl der Überlaufbereich leer erscheint, immer noch den Überlauf! Error. Dies liegt daran, dass der Überlauf nicht wirklich leer ist, sondern ein unsichtbares Leerzeichen in einer der Zellen hat.
Es ist schwer, Leerzeichen oder andere unsichtbare Zeichen zu finden, die sich in scheinbar leeren Zellen verstecken. Um solche Zellen mit unerwünschten Daten zu finden, klicken Sie auf den Fehler-Floatie (Warnzeichen) und wählen Sie im Menü "Blockierende Zellen auswählen". Sie gelangen dann zu der Zelle, die die blockierenden Daten enthält.
Wie Sie im folgenden Screenshot sehen können, enthält Zelle E2 zwei Leerzeichen. Wenn Sie diese Daten löschen, erhalten Sie die richtige Ausgabe.
Manchmal kann das unsichtbare Zeichen ein Text sein, der mit derselben Schriftfarbe wie die Füllfarbe der Zelle formatiert ist, oder ein benutzerdefinierter Zellenwert, der mit dem Zahlencode ;;; formatiert ist. Wenn Sie einen Zellenwert mit ;;; benutzerdefiniert formatieren, wird alles in dieser Zelle ausgeblendet, unabhängig von der Schriftfarbe oder Zellenfarbe.
Spill Range enthält verbundene Zellen
Manchmal ist das #VERSCHÜTZEN! Fehler tritt auf, wenn der Überlaufbereich die verbundenen Zellen enthält. Die Formel für dynamische Arrays funktioniert nicht mit verbundenen Zellen. Um dies zu beheben, müssen Sie lediglich die Zellen im Überlaufbereich aufheben oder die Formel in einen anderen Bereich verschieben, der keine verbundenen Zellen enthält.
Im folgenden Beispiel gibt die Formel den Überlauffehler zurück, obwohl der Überlaufbereich leer ist (C2:CC8). Dies liegt daran, dass die Zellen C4 und C5 zusammengeführt werden.
Um sicherzustellen, dass verbundene Zellen der Grund für die Fehlermeldung sind, klicken Sie auf dasWarnzeichen und überprüfen Sie die Ursache – „Spill range has merged cell“.
Lösung:
Um die Zusammenführung der Zellen aufzuheben, wählen Sie die zusammengeführten Zellen aus, klicken Sie dann auf der Registerkarte „Startseite“ auf die Schaltfläche „Zusammenführen und zentrieren“ und wählen Sie „Zellen aufheben“.
Wenn es Ihnen schwerfällt, die verbundenen Zellen in Ihrer großen Tabelle zu finden, klicken Sie im Warnzeichenmenü auf die Option "Blockierende Zellen auswählen", um zu den verbundenen Zellen zu springen.
Auslaufbereich in Tabelle
Verschüttete Arrayformeln werden in Excel-Tabellen nicht unterstützt. Dynamische Arrayformeln sollten nur in eine einzelne einzelne Zelle eingegeben werden. Wenn Sie eine verschüttete Array-Formel in eine Tabelle eingeben oder der Überlaufbereich in eine Tabelle fällt, erhalten Sie den Verschüttungsfehler. Versuchen Sie in diesem Fall, die Tabelle in einen normalen Bereich umzuwandeln, oder verschieben Sie die Formel außerhalb der Tabelle.
Wenn wir beispielsweise die folgende Überlaufbereichsformel in eine Excel-Tabelle eingeben, erhalten wir in jeder Zelle der Tabelle einen Überlauffehler, nicht nur in der Formelzelle. Dies liegt daran, dass Excel automatisch jede in einer Tabelle eingegebene Formel in jede Zelle in der Spalte der Tabelle kopiert.
Außerdem erhalten Sie einen Verschüttungsfehler, wenn eine Formel versucht, Ergebnisse in einer Tabelle zu verschütten. Im folgenden Screenshot fällt der Spill-Bereich in die vorhandene Tabelle, sodass wir einen Spill-Fehler erhalten.
Um die Ursache für diesen Fehler zu bestätigen, klicken Sie auf das Warnzeichen und sehen Sie sich die Fehlerursache an – „Überlaufbereich in der Tabelle“
Lösung:
Um den Fehler zu beheben, müssen Sie die Excel-Tabelle wieder auf den Bereich zurücksetzen. Klicken Sie dazu mit der rechten Maustaste auf eine beliebige Stelle in der Tabelle, klicken Sie auf "Tabelle" und wählen Sie dann die Option "In Bereich konvertieren". Alternativ können Sie mit der linken Maustaste auf eine beliebige Stelle innerhalb der Tabelle klicken, dann zur Registerkarte "Tabellendesign" gehen und die Option "In Bereich konvertieren" auswählen.
Spill Range ist unbekannt
Wenn Excel die Größe des verschütteten Arrays nicht ermitteln konnte, wird der Überlauffehler ausgelöst. Manchmal ermöglicht die Formel die Größenänderung eines dynamischen Arrays zwischen den einzelnen Berechnungsdurchläufen. Wenn sich die Größe des dynamischen Arrays während der Berechnungsdurchläufe ständig ändert und nicht ausgeglichen wird, führt dies zu #SPILL! Fehler.
Diese Art von Spill-Fehler wird normalerweise ausgelöst, wenn flüchtige Funktionen wie RAND, RANDARRAY, RANDBETWEEN, OFFSET und INDIRECT verwendet werden.
Wenn wir beispielsweise die folgende Formel in Zelle B3 verwenden, erhalten wir den Spill-Fehler:
=SEQUENZ(RANDZWISCHEN(1, 500))Im Beispiel gibt die Funktion RANDBETWEEN eine zufällige ganze Zahl zwischen den Zahlen 1 und 500 zurück, und ihre Ausgabe ändert sich ständig. Und die SEQUENCE-Funktion weiß nicht, wie viele Werte in einem Spill-Array erzeugt werden sollen. Daher der #SPILL-Fehler.
Sie können die Fehlerursache auch bestätigen, indem Sie auf das Warnzeichen – „Überlaufbereich ist unbekannt“ klicken.
Lösung:
Um den Fehler für diese Formel zu beheben, können Sie nur eine andere Formel für Ihre Berechnung verwenden.
Spill Range ist zu groß
Manchmal können Sie eine Formel ausführen, die einen verschütteten Bereich ausgibt, der für das Arbeitsblatt zu groß ist und sich über die Ränder des Arbeitsblatts hinaus erstrecken kann. Wenn das passiert, können Sie #SPILL bekommen! Error. Um dieses Problem zu beheben, können Sie versuchen, auf einen bestimmten Bereich oder eine Zelle statt auf ganze Spalten zu verweisen oder das Zeichen „@“ zu verwenden, um implizite Schnittmengen zu aktivieren
Im folgenden Beispiel versuchen wir, 20 % der Verkaufszahlen in Spalte A zu berechnen und die Ergebnisse in Spalte B zurückzugeben, aber stattdessen erhalten wir einen Spill-Fehler.
Die Formel in B3 berechnet 20 % des Wertes in A3, dann 20 % des Wertes in A4 und so weiter. Es erzeugt über eine Million Ergebnisse (1.048.576) und verschüttet sie alle in Spalte B, beginnend in Zelle B3, aber es erreicht das Ende des Arbeitsblatts. Es ist nicht genügend Platz vorhanden, um alle Ausgaben anzuzeigen. Als Ergebnis erhalten wir einen #SPILL-Fehler.
Wie Sie sehen können, liegt die Ursache für diesen Fehler darin, dass – „Spill range is too big“ ist.
Lösungen:
Um dieses Problem zu lösen, versuchen Sie, die gesamte Spalte mit einem relevanten Bereich oder einer Einzelzellenreferenz zu ändern, oder fügen Sie den @-Operator hinzu, um eine implizite Schnittmenge durchzuführen.
Fix 1: Sie können versuchen, auf Bereiche anstatt auf ganze Spalten zu verweisen. Hier ändern wir den gesamten Bereich A: A mit A3: A11 in der Formel, und die Formel füllt den Bereich automatisch mit Ergebnissen.
Lösung 2: Ersetzen Sie die gesamte Spalte nur durch den Zellbezug in derselben Zeile (A3), und kopieren Sie dann die Formel mithilfe des Ausfüllkästchens im Bereich nach unten.
Lösung 3: Sie können auch versuchen, den @-Operator vor dem Verweis hinzuzufügen, um eine implizite Schnittmenge durchzuführen. Dadurch wird die Ausgabe nur in der Formelzelle angezeigt.
Kopieren Sie dann die Formel aus Zelle B3 in den Rest des Bereichs.
Notiz: Wenn Sie eine verschüttete Formel bearbeiten, können Sie nur die erste Zelle im Verschüttungsbereich/-bereich bearbeiten. Sie können die Formel in anderen Zellen des Überlaufbereichs sehen, aber sie sind ausgegraut und können nicht aktualisiert werden.
Kein Speicher mehr
Wenn Sie eine verschüttete Array-Formel ausführen, die dazu führt, dass Excel nicht mehr genügend Arbeitsspeicher hat, kann dies den #SPILL-Fehler auslösen. Versuchen Sie unter diesen Umständen, auf ein kleineres Array oder einen kleineren Bereich zu verweisen.
Nicht erkannt / Fallback
Sie können auch dann einen Spill-Fehler erhalten, wenn Excel die Fehlerursache nicht erkennt oder nicht abgleichen kann. Überprüfen Sie in solchen Fällen Ihre Formel und stellen Sie sicher, dass alle Parameter der Funktionen korrekt sind.
Jetzt kennen Sie alle Ursachen und Lösungen für #SPILL! Fehler in Excel 365.