So erstellen Sie eine Website oder ein Blog im Jahr 2020 - Kostenlose und einfache Anleitung zum Erstellen einer Website

So verwenden Sie den Überlaufbereich in Excel

Der Spill-Bereich von Excel ist ein Produkt der Verwendung dynamischer Array-Funktionen. Erfahren Sie, wie die Überlaufserie diese Funktionen erfüllt.

In den letzten Monaten habe ich mehrere Artikel über die neuen dynamischen Array-Funktionen von Excel geschrieben. In vielen Fällen können sie ältere, komplexere Ausdrücke ersetzen. Die neuen Funktionen erledigen all dies, indem sie intern rechnen und eine Reihe von Ergebniswerten zurückgeben. Dieser Bereich der resultierenden Werte wird als Überlaufbereich bezeichnet. Wenn Sie diese dynamischen Array-Funktionen verwenden, müssen Sie alles über den Spill-Bereich wissen, also lesen Sie weiter.

SIEHE: 83 Excel-Tipps, die jeder Benutzer beherrschen sollte (TechRepublic)

Ich verwende Microsoft 365 auf einem Windows 10 64-Bit-System. Diese neuen dynamischen Matrixfunktionen sind nur in Microsoft 365, Excel für das Web und Excel für Android-Tablets und -Telefone verfügbar. Es gibt keine Demodatei; Du wirst keinen brauchen.

Beginnen wir mit einer guten Definition des Überlaufbereichs

Wenn eine Funktion oder ein dynamischer Array-Ausdruck mehr als einen Ergebniswert erzeugt, werden diese Werte auf benachbarte Zellen verteilt. Mit anderen Worten, der Überlaufbereich ist das Array von Werten, die von einem dynamischen Array-Ausdruck oder einer dynamischen Array-Funktion zurückgegeben werden. Es ist wichtig zu beachten, dass es sich lediglich um resultierende Werte handelt und die einzige Zelle, die einen Ausdruck oder eine Funktion enthält, die Zelle ist, in die Sie ihn eingegeben haben. Die obere linke Zelle enthält den Ausdruck oder die Funktion und die übrigen Zellen im Überlaufbereich zeigen nur die resultierenden Werte an. Das bedeutet, dass Sie beim Ändern des Ausdrucks oder der Funktion nur eine Zelle ändern müssen; Alle resultierenden Werte werden jedoch entsprechend aktualisiert. Glücklicherweise ist es nicht so kompliziert, wie es zunächst scheint.

SIEHE: Windows 11: Installationstipps, Sicherheit und mehr (kostenloses PDF) (TechRepublic)

Wenn Sie auf eine beliebige Zelle innerhalb des Überlaufbereichs klicken, zeigt Excel einen blauen Rahmen um den gesamten Überlaufbereich an. Alles innerhalb dieser blauen Grenze ist ein Ergebniswert. Abbildung A zeigt einen kleinen Spread-Bereich für die dynamische UNIQUE()-Funktion in H5. Diese dynamische Funktion gibt eine Liste eindeutiger Werte aus der Spalte Region zurück:

=SINGLE(Verkäufe[Region],GEFÄLSCHT)

Auf dem Screenshot ist schwer zu erkennen, dass die Grenze blau ist, weil sie dünn ist. Derzeit beträgt der Überlaufbereich H5:H7. Außerdem zeigt die Bearbeitungsleiste den expliziten Bereich an, anstatt die strukturierte Tabellenreferenz zu verwenden. Ich habe es getan, um beide im Einsatz zu zeigen; beides wird funktionieren.

Abbildung A

Da es sich bei den Quelldaten um ein Tabellenobjekt handelt, wird die dynamische Funktion in H5 aktualisiert, wenn Sie die Quelldaten in der Tabelle aktualisieren. Wenn Sie beispielsweise einen neuen Datensatz hinzufügen, der eine neue Region enthält, wird die Liste beginnend bei H5 entsprechend aktualisiert, wie in gezeigt Abbildung B. Wenn Sie keine Tabelle zum Speichern der Quelldaten verwenden und einen Wert außerhalb des ursprünglichen Bereichs (in diesem Fall der Region-Spalte) eingeben, wird der Überlaufbereich der SINGLE()-Funktion aktualisiert. Wenn Sie die dynamische Funktion auf H5 entfernen, verschwindet der Spill-Bereich vollständig.

Abbildung B

Bisher gab es viel Raum für Ergebniswerte. Was passiert, wenn dies nicht der Fall ist?

Was ist #SPILL! Fehler?

Obwohl der vorherige Abschnitt einfach, ohne Fehler und ohne Probleme war, wird dies nicht immer so sein. Abbildung C zeigt einen Überlaufbereichsfehler an, #SPILL!. Die Ankerzelle E5 hat sich nicht geändert; die Funktion ist genau die gleiche. Anstatt jedoch die Ergebniswerte zu sehen, die wir zuvor gesehen haben, sehen wir jetzt einen Fehler.

Abbildung C

Wenn Sie vermutet haben, dass der Wert in H7, Test, im Weg ist, haben Sie Recht! Wenn Sie diesen Fehler sehen, liegt das Problem fast immer darin, dass die Anzahl der Zellen nicht ausreicht, um die ursprüngliche Berechnung zu erfüllen. Wenn die dynamische Funktion nicht alle Ergebniswerte zurückgeben kann, gibt sie den Fehler #SPILL! zurück. Fehler.

Die Lösung ist einfach. Sie können die fehlerhaften Daten im Überlaufbereich löschen oder die ursprüngliche dynamische Funktion in eine Zelle verschieben, in der ausreichend Platz für alle berechneten Ergebniswerte vorhanden ist.

Wenn das Problem offensichtlich ist, ist es leicht zu lösen. Wenn das Problem nicht so offensichtlich ist wie im Beispiel Abbildung C, nicht verzweifeln. Weitere Informationen finden Sie stattdessen im Fehlertag. Die Fehlerbezeichnung ist das Ausrufezeichen, das angezeigt wird, wenn Sie den ursprünglichen Ausdruck oder die ursprüngliche Funktion auswählen (Abbildung C). Klicken Sie einfach auf das Smarttag, um die Liste der angezeigten Fehler anzuzeigen Abbildung D.

Abbildung D

Sehen wir uns die möglichen Fehler an:

  • Der Überlaufbereich ist nicht leer ist wohl der häufigste Fehler. Das ist unser Problem Abbildung C. In unserem Beispiel ist es offensichtlich, aber was passiert, wenn die problematische Zelle außerhalb des Bildschirms liegt? Wenn Sie den störenden Wert nicht finden können, wählen Sie „Hinderniszellen auswählen“. Dadurch gelangen Sie zu der Zelle, die die Ergebniswerte sperrt. An dieser Stelle können Sie entscheiden, den Wert zu entfernen oder die dynamische Funktion zu verschieben.
  • Hilfe zu diesem Fehler öffnet die Hilfedateien mit einer Liste der Bedeutung dieser Fehlermeldungen.
  • Wählen Sie blockierende Zellen aus wählt die Zelle innerhalb des Überlaufbereichs aus, die die resultierenden Werte sperrt. Es könnte auch auf zusammengeführte Zellen hinweisen, da der Dump-Bereich keine zusammengeführten Zellen aufnehmen kann.
  • Berechnungsschritte anzeigen Zeigt das Dialogfeld „Formel auswerten“ an, in dem Sie Berechnungen schrittweise durchgehen können, ähnlich wie Sie VBA-Code im Visual Basic-Editor schrittweise durchgehen könnten.
  • Fehler ignorieren ermöglicht es Ihnen, den Smarttag zu löschen und trotz des Fehlers weiterzuarbeiten. Ich empfehle nicht, dieses zu verwenden, es sei denn, Sie haben einen bestimmten Grund dafür.
  • Bearbeiten Sie in der Bearbeitungsleiste Legt den Fokus auf den tatsächlichen Ausdruck oder die Funktion in der Bearbeitungsleiste, sodass Sie sie bearbeiten können.
  • Optionen zur Fehlerprüfung Öffnet das Dialogfeld „Optionen“, in dem Sie diese Optionen überprüfen und möglicherweise ändern können.

In unserem Fall, der wahrscheinlich der häufigste Fehler ist, ist das erste Element, Der Überlaufbereich ist nicht leer Es ist direkt und informativ. Wenn Sie die fehlerhafte Zelle nicht finden können, wählen Sie Wählen Sie blockierende Zellen aus. Die anderen Fehler werden Ihnen nicht oft begegnen.

Darüber hinaus unterstützt das Table-Objekt keine dynamischen Array-Funktionen oder -Ausdrücke. Geben Sie diese Ausdrücke und Funktionen nicht in ein Tabellenobjekt ein. Die Quelldaten können und sollten in einer Tabelle vorliegen, die eigentliche Funktion darf jedoch nicht in einer Tabelle abgelegt werden.

Die integrierten dynamischen Matrixfunktionen von Excel sind einfach zu verwenden und zu warten, sofern Sie den Spread-Bereich kennen und wissen, wie Sie ihn an Ihre Bedürfnisse anpassen können.

Lesen Sie die folgenden Artikel, um mehr über diese neuen dynamischen Array-Funktionen zu erfahren: