#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!