#VERSCHÜTTEN! Fehler in Excel - was es bedeutet und wie man behebt

#Spill -Fehler in Excel: Diagnostik und Lösungen

Haben Sie jemals auf diese Situation gestoßen: Nach dem Upgrade auf Microsoft Office 365 mit dem neuesten Excel -Update wurde die zuverlässige Formel, die Sie seit Jahren verwenden, plötzlich nicht mehr funktioniert, ersetzt durch einen #Spill -Fehler? Mach dir keine Sorgen, dieser Artikel wird dir antworten.

Verschüttungsfehler Bedeutung

Einfach ausgedrückt, wenn eine Formel mehrere Ergebnisse erzielt, aber nicht alles auf dem Arbeitsblatt angezeigt werden kann, #spill! Fehler treten auf.

Dynamische Arrays werden in Excel 365 eingeführt, und jede Formel, die mehrere Berechnungen automatisch erzeugt, überläuft ihre Ergebnisse in benachbarten Zellen, auch wenn die Formel ursprünglich nicht für die Verarbeitung von Arrays ausgelegt war. Der Bereich der Zellen, die die Ergebnisse enthalten, wird als Überlaufbereich bezeichnet. Ein #spill! Fehler treten auf, wenn etwas auf dem Arbeitsblatt verhindert, dass der Bereich besiedelt wird.

In den meisten Fällen ist dieses Verhalten verständlich und vorhersehbar. Wenn Ihre Formel beispielsweise erwartet, mehrere Werte zurückzugeben, aber in der Nähe von Zellen mit anderen Daten gefüllt werden, löschen Sie diese Daten einfach und der Fehler wird verschwinden.

Aber manchmal ist der Grund nicht so offensichtlich, also ist es verwirrend. Vielleicht enthalten ein oder mehrere Zellen im Überlaufbereich Räume oder undruckswerte Zeichen, die vom menschlichen Auge nicht gesehen werden können. Alternativ verhindert das Kopieren der gleichen Formel in die gesamte Spalte Überlaufbereiche. Möglicherweise haben Sie einige sehr wenige Funktionen gestoßen, die keine dynamischen Arrays unterstützen. Um den Fehler zu beheben, müssen Sie jeden Fall separat untersuchen und die Hauptursache des Problems bestimmen.

Löse #spill! Fehler in Excel

VERSCHÜTTEN! Fehler können durch eine Vielzahl von Gründen verursacht werden. Um die Grundursache des Problems zu verstehen, klicken Sie auf das Warnsymbol (Yellow Diamond mit Ausrufezeichen) und lesen Sie die Nachricht in der ersten Zeile, die in Grau hervorgehoben wurde:

Finden Sie nach der Ermittlung der Ursache das entsprechende Beispiel unten, das detaillierte Anweisungen zur Behebung eines bestimmten Falls enthält.

Überlaufbereich ist nicht leer

Ursache : Der zugefüllte Bereich mit dem Formelergebnis enthält nicht leere Zellen.

Lösung : Löschen Sie den erwarteten Überlaufbereich.

Klicken Sie im einfachsten Szenario einfach auf die Formelzelle und Sie sehen einen gepunkteten Rand, der die Grenze des Überlaufbereichs anzeigt - alle Daten darin sind eine Barriere. Löschen Sie daher entweder vorhandene Daten aus dem Überlaufbereich oder verschieben Sie die Formel an einen anderen nicht blockierten Ort.

In einigen Fällen mag der Überlaufbereich jedoch leer erscheinen, ist dies jedoch nicht der Fall. Das Problem kann unsichtbare Zeichen sein, die in einigen Zellen (z. B. Leerzeichen) lauern, oder die Formel gibt eine leere Zeichenfolge zurück.

Klicken Sie zum Nachweis solcher Zellen auf das Warnflag und Sie sehen diese Beschreibung - der Überlaufbereich ist nicht leer . Darunter gibt es mehrere Optionen. Klicken Sie auf Blockierungszellen aus und Excel zeigt Ihnen, welche Zellen die Formeln überfließen lassen.

In der folgenden Abbildung ist die blockierende Zelle A6, die die von der Formel zurückgegebene leere Zeichenfolge ("") enthält.

Um blockierende Zellen zu löschen, gehen Sie zur Registerkarte Start> Bearbeiten Sie die Gruppe und klicken Sie auf Löschen> Alle löschen.

Überlaufbereich enthält zusammengeführte Zellen

Ursache : Überlauf gilt nicht für Zusammenführungszellen.

Lösung : Entmerze -Zellen im Überlaufbereich oder verschieben die Formel an einen anderen Ort, an dem keine verschmolzene Zelle vorhanden ist.

Wenn erwartet wird, dass eine oder mehrere verschumte Zellen im Überlaufarray existieren, wird die folgende Fehlermeldung angezeigt: Der Überlaufbereich enthält zusammengeführte Zellen .

Wenn Sie Probleme haben, intuitiv zusammengeführte Zellen zu erkennen, springen Sie mit der Option Select Blocking Cell in die problematische Zelle:

Überlaufbereich in der Tabelle

Ursache : Excel -Tabellen unterstützen keine dynamischen Arrays.

Lösung : Konvertieren Sie eine Tabelle in einen normalen Bereich oder platzieren Sie eine Formel außerhalb der Tabelle, damit sie überlaufen können. Es ist nicht klar, warum dynamische Array -Formeln in Excel -Tabellen nicht funktionieren (wahrscheinlich aufgrund der spezifischen Syntax strukturierter Referenzen), aber diese beiden sehr nützlichen Dinge sind nicht kompatibel :(

Um die Grundursache des Problems zu bestätigen, klicken Sie mit einem Ausrufezeichen auf das Symbol, und Sie sehen diesen Text in der ersten Zeile: Überlaufbereich in der Tabelle .

Das Beste, was Sie in diesem Fall tun können, ist, die Tabelle in einen Bereich umzuwandeln. Klicken Sie dazu mit der rechten Maustaste in die Tabelle und klicken Sie auf Tabelle > auf Bereich konvertieren . Alternativ können Sie die Formel außerhalb der Tischgrenze bewegen.

Überlaufbereich unbekannt

Ursache : Excel kann die Größe des Überlaufarrays nicht bestimmen.

Lösung : Versuchen Sie, verschiedene Formeln für Ihre Aufgabe zu formulieren.

Bei Verwendung von volatilen Funktionen wie Randarray, Rand oder Randbet zwischen dynamischen Array -Funktionen kann ein #Spill -Fehler auftreten, da das von der flüchtigen Funktion zurückgegebene Array zwischen Berechnungen in der Tabelle und der Funktion "Wrapper" seine Größe nicht bestimmen kann.

In diesem Fall wird die Fehlermeldung angezeigt als: Der Überlaufbereich ist unbekannt .

Zum Beispiel wirft die folgende Formel eine #Spill aus! Fehler, da sich die Randbetween -Ausgabe ständig ändert und die Sequenz nicht weiß, wie viele Werte zu erzeugen sind:

=SEQUENCE(RANDBETWEEN(1,100))

Übermäßiger Überlaufbereich

Ursache : Excel kann kein Überlaufarray ausgeben, da es über die Kante der Tabelle hinausgeht.

Lösung : Anstatt die gesamte Spalte zu berechnen, beziehen Sie sich auf verwendete Bereiche, einzelne Zellen oder addieren Sie @ -Operatoren, um implizite Schnittpunkte durchzuführen.

Dies ist der komplexeste Fall, abhängig von Ihren Zielen und der Struktur des Arbeitsblatts müssen Sie die Formel möglicherweise auf unterschiedliche Weise anpassen. Hier werden wir einen gemeinsamen Ansatz mit einem sehr einfachen Beispiel demonstrieren.

Angenommen, Sie haben eine Zahlenspalte in Spalte B und möchten in Spalte C 10% dieser Zahlen zurückgeben.

In der traditionellen Excel 2019 und früher funktioniert die folgende Formel einwandfrei (im linken Teil der folgenden Abbildung befindet sie sich in den Zellen C2 bis C7):

=B:B*10%

In Dynamic Excel 365 löst dieselbe Formel einen #Spill -Fehler aus.

Der Grund für den Fehler ist, dass der Überlaufbereich zu groß ist .

Warum werden Formeln in New Excel kaputt? Weil implizite Schnittpunkt nicht mehr stillschweigend im Hintergrund ausgeführt wird. Der Begriff klingt mysteriös, aber die Logik dahinter ist einfach und reduziert mehrere Werte auf einen einzelnen Wert.

In Versionen vor 365 war dies das Standardverhalten von Excel - denn die Zelle kann nur einen Wert enthalten, die Formel ist gezwungen, ein einziges Ergebnis zurückzugeben. In unserem Beispiel, selbst wenn wir eine ganze Spalte (b: b) bereitstellen, gilt Excel nur mit einem einzelnen Wert, der in der gleichen Zeile wie die Formel liegt. Daher berechnet die Formel in C2 10% des Medianwerts in B2, die Formel in C3 10% des Medianwerts in B3 usw.

In Dynamic Array Excel unterscheidet sich das Standardverhalten - jede Formel, die mehrere Ergebnisse zurückgeben können, überfluten sie automatisch auf das Arbeitsblatt. In unserem Fall multiplizierte Excel jede Zelle in Spalte B mit 10%, was zu über einer Million Ergebnissen führte (genauer gesagt 1.048.576) und versuchte, alle diese Ergebnisse in Spalte C ab C2 auszugeben, erreichte jedoch das Ende des Blattgitters - daher ein #Spill -Fehler. Ratet mal, was passieren würde, wenn wir die Formel in C1 einfügen würden? Ja, es wird funktionieren, denn diesmal gibt es genügend Zellen zum Füllen - der Überlaufbereich wird genau 1.048.576 Zellen einnehmen!

Lösung 1. Referenzumfang anstelle von Spalten

Dies ist ein sehr vernünftiger Ansatz - Verweisen Sie nicht auf die gesamte Spalte, sondern nur die relevanten Daten. Zusätzlich zur Behebung von #Spill -Fehlern hilft es auch dabei, Excel -Ressourcen zu sparen und die Leistung zu verbessern.

Für unseren Beispieldatensatz lautet die Formel:

=B2:B10*10%

Diese Formel wird nur in eine Zelle (C2) eingegeben. Das Ergebnis ist ein dynamisches Array, das automatisch in mehrere Zellen überfließt:

Beachten. Da Excel -Tabellen keine dynamischen Arrays unterstützen, ist diese Lösung nur für normale Bereiche verfügbar.

Lösung 2. Berechnen Sie eine einzelne Zelle und kopieren Sie die Formel nach unten

Dies ist die traditionelle Methode - beziehen Sie sich auf eine Zelle in derselben Zeile und kopieren Sie die Formel nach unten.

In unserem Fall befindet sich die folgende Formel in C2, die Sie dann auf C10 ziehen können:

=B2*10%

Im Gegensatz zu dynamischen Array -Formeln ist dieser traditionelle Formelstil in Bereichen und Tabellen gleichermaßen wirksam:

Lösung 3. Wenden Sie implizite Kreuzung an

Dieser Ansatz ist etwas komplexer - unter Verwendung des impliziten Schnittpunkte -Operators (@ -Symbol), der in Excel 365 als Teil der dynamischen Array -Updates eingeführt wurde.

Fügen Sie in Ihrer Formel das @ -Zeichen vor den Bereich/Spalte/Zeilenreferenz, Funktion oder Ausdruck ein, in dem Sie das Array oder den Bereich auf einen einzelnen Wert reduzieren möchten. Geben Sie die Formel in die oberste Zelle ein und ziehen Sie sie nach Bedarf auf mehrere Zeilen.

Für unseren Beispieldatensatz nimmt die Formel das folgende Formular an:

=@B:B*10%

Da das Ergebnis ein einzelner Wert ist und kein dynamisches Array, kann diese Formel sowohl in Bereichen als auch in Tabellen verwendet werden. Im letzteren Fall wären strukturierte Referenzen jedoch eine elegantere und effizientere Lösung:

=[@Sales]*10%

In gewissem Maße erklärt dies, warum der "@" -Zeichen gelegentlich in Formeln in älteren Versionen erscheint - um die Kompatibilität aufrechtzuerhalten. Da Formeln vor der Dynamik in Excel nicht mehr zu mehreren Zellen überlaufen können, sorgt der implizite Schnittbetreiber für das gleiche Verhalten, wenn die Formel in das dynamische Array -Excel importiert wird.

Mein persönlicher Rat ist, diesen Ansatz mit Vorsicht zu verwenden, da er eine neue Funktion von Excel deaktiviert, die in vielen Szenarien sehr effizient und nützlich ist.

Auf diese Weise können Sie #Spill -Fehler in Excel beheben und beheben. In den folgenden Artikeln werden wir diesen Fehler in einigen beliebten Formeln wie Vlookup, Index Match, Sumif usw. untersuchen und beheben. Vielen Dank für das Lesen, bitte bleiben Sie dran!

Das obige ist der detaillierte Inhalt von#VERSCHÜTTEN! Fehler in Excel - was es bedeutet und wie man behebt. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!