Die versteckten Kosten von Ganzspaltenbezügen in Excel: Lernen Sie 3 Alternativen, um die Leistung Ihrer Arbeitsmappe zu optimieren
Spaltenweite Verweise in Excel sind stille Performance-Killer, die das Programm oft dazu zwingen, einen Bereich von über einer Million Zeilen zu verwalten. Infolgedessen können sie die Leistung Ihrer Arbeitsmappe erheblich verlangsamen. Hören Sie also auf, A:A-Verweise zu verwenden, und erstellen Sie stattdessen dynamische Bereiche.
Die versteckten Kosten der Millionen-Zeilesteuer
Ganzspaltenbezüge (A:A) können Excel dazu zwingen, 1.048.576 Zeilen zu verwalten, insbesondere bei Array-ähnlichen Berechnungen oder Funktionen, die jede Zelle auswerten. Nehmen Sie dieses 50-zeilige Datenset als Beispiel. In Zelle D1 möchten Sie UNIQUE verwenden, um alle in Spalte B aufgeführten Personen aufzulisten.
Um sicherzustellen, dass die Formel alle neuen Daten in den Zeilen 51, 52 usw. erfasst, können Sie einen Bezug auf die gesamte Spalte verwenden:
=UNIQUE(B:B)Diese Formel überprüft alle Zellen in Spalte B - einschließlich der leeren Zeilen unterhalb des aktiven Bereichs, dargestellt durch die abweichende Null.
Der Nulltest: Den Beweis der Belastung erbringen
Wenn Sie genau sehen möchten, womit Excel umgehen muss, wenn Sie eine gesamte Spaltenreferenz eingeben, geben Sie in Zelle D1 Folgendes ein:
=B:BWenn Sie nach unten scrollen, sehen Sie über eine Million nachgestellte Nullen, die die leeren Zellen in Spalte B ab Zeile 51 darstellen.
Während einige Funktionen diese Leerstellen ignorieren, behandeln andere sie als gültige Eingaben, wodurch Nullen zurückgegeben werden, die Ihre Ergebnisse verunreinigen und darauf hinweisen, dass die Engine gezwungen ist, das leere Nichts zu verarbeiten.
Modernes Excel verwendet „Bereichsausschluss“, um leere Zeilen zu überspringen, setzt jedoch darauf, dass der verwendete Bereich sauber ist. Wenn Zeile 10.000 ein zufälliges Leerzeichen enthält, behandelt Excel den gesamten Leerraum als aktive Daten. Außerdem müssen Formeln immer noch mit dem gesamten Bereich zusammenarbeiten, um die Grenzen zu überprüfen.
Hier sind drei integrierte Werkzeuge, die Sie verwenden können, um dieses Verhalten zu verhindern.
Lösung 1: Konvertieren Sie Ihre Daten in eine Excel-Tabelle
Die robusteste Methode, die Million-Zeilen-Steuer zu vermeiden, besteht darin, Excel-Tabellen zu verwenden - dynamische Objekte, die die Art und Weise, wie Excel Ihre Daten berechnet, vollständig verändern.
Tabellen ersetzen den massiven Umfang von Verweisen auf ganze Spalten durch explizite Grenzen. Wenn Ihre Daten 50 Zeilen haben, ist das Tabellenobjekt genau 50 Zeilen lang, sodass Excel bei einer Formel, die auf die Tabelle verweist, seine Berechnungen auf diesen Bereich beschränkt.
Stellen Sie zunächst sicher, dass Ihr Datensatz Spaltenüberschriften hat. Wählen Sie als Nächstes eine beliebige Zelle im Bereich aus, drücken Sie Strg+T, und aktivieren Sie im Dialogfeld "Tabelle erstellen" das Kontrollkästchen "Meine Tabelle hat Überschriften". Klicken Sie dann auf "OK."
Tabellen verwenden strukturierte Verweise, die neben einer einfacheren Lesbarkeit auch als Sperre für die Dimensionen Ihrer Daten dienen. Um also alle eindeutigen Namen in Spalte B aufzulisten, müssen Sie Ihre Formel von einem uneingeschränkten Spaltenscan auf eine gezielte Tabellensuche ändern:
=UNIQUE(Table1[Manager])Beachten Sie, dass die Null am unteren Ende des Ergebnisses verschwunden ist, da in der Formel nur die Daten innerhalb der Grenzen der Tabelle referenziert werden.
Um den Namen Ihrer Tabelle zu ändern (von Tabelle1 zu etwas wie T_Manager), wählen Sie eine beliebige Zelle im Bereich aus, öffnen Sie die Registerkarte „Tabellenentwurf“ und geben Sie den neuen Namen in das Feld Tabellenname ein. Neben vielen anderen Vorteilen wird dadurch auch die Formel leichter lesbar.
Diese Methode ist aus zwei Gründen besser als Verweise auf ganze Spalten:
- Null-Abfall-Berechnungen: Da die Tabelle ein definiertes Ende hat, muss Excel den leeren Bereich unter den Daten nicht verarbeiten.
- Selbstverwaltende Bereiche: Wenn Sie Daten in Zeile 51 hinzufügen, erweitert sich die Tabellenbegrenzung um eine Zeile, und Ihre Formeln bleiben genau auf den Bereich bezogen, ohne dass manuelle Anpassungen erforderlich sind.
Lösung 2: Verwenden Sie die TRIMRANGE-Funktion
Wenn Sie Ihre Excel-Daten nicht in eine formelle Tabelle umwandeln möchten, ist die TRIMRANGE-Funktion die nächstbeste Lösung. Sie ermöglicht es Ihnen, wie zuvor eine Referenz auf die gesamte Spalte zu verwenden, weist Excel jedoch an, den Bereich so zu beschneiden, dass nur die Zellen enthalten sind, die tatsächlich Daten enthalten.
Die TRIMRANGE-Funktion ist nur für Benutzer von Excel für Microsoft 365, Excel für das Web sowie die Excel-Apps für Mobilgeräte und Tablets verfügbar.
TRIMRANGE hat drei Argumente:
=TRIMRANGE(range,[trim_rows],[trim_cols])
wo :
- range (erforderlich) ist der Bereich, der beschnitten werden soll.
- trim_rows (optional) teilt Excel mit, ob führende leere Zeilen (1), nachfolgende leere Zeilen (2) oder beide (3) entfernt werden sollen.
- trim_cols (optional) teilt Excel mit, ob führende leere Spalten (1), nachfolgende leere Spalten (2) oder beide (3) entfernt werden sollen.
Um diese Logik auf Ihren Datensatz anzuwenden, geben Sie ein:
=UNIQUE(TRIMRANGE(B:B,2))
Hier erzwingt die "2" in der Formel das Kürzen der nachgestellten leeren Zellen. Da in der Formel nur eine Spalte referenziert wird, ist es nicht nötig, das Argument trim_cols einzuführen.
Wenn Sie auf die Zelle D1 doppelklicken, umgibt der visuelle Auswahllrahmen immer noch die gesamte Spalte. Dies ist eine Anzeigeeigenschaft der Benutzeroberfläche, die es Ihnen erleichtert, den Quellbereich zu sehen, auf dem die Funktion basiert. Die TRIMRANGE-Funktion ist jedoch eine interne Anweisung an die Berechnungsengine, sodass die visuelle Auswahl nicht den optimierten Bereich widerspiegelt, den die Formel tatsächlich verarbeitet.
Hier ist der Grund, warum dies eine gute Lösung für Verweise auf ganze Spalten ist:
- Dynamische, aber übersichtliche Bezüge: Sie erhalten die Flexibilität eines Ganzspaltenbezugs - er findet weiterhin neue Daten, die Sie später hinzufügen. Die Berechnungsmaschine wird jedoch nur für die 50 aktiven Datensätze ausgeführt.
- Gitterflexibilität: Sie erhalten eine tabellenähnliche Leistung, ohne das visuelle Format Ihres Arbeitsblatts ändern zu müssen
Lösung 3: Verwenden Sie den Trim-Ref-Operator
Der Trim-Referenzoperator von Excel bietet genau die gleichen Vorteile wie die TRIMRANGE-Funktion, jedoch mit weniger Tipparbeit.
Der Trim-Ref-Operator ist in derselben Umgebung verfügbar wie die TRIMRANGE-Funktion: Excel für Microsoft 365, Excel für das Web sowie die Excel-Apps für Mobilgeräte und Tablets.
Indem Sie einen Punkt (.) auf jeder Seite des Doppelpunkts in Ihrem Bereich hinzufügen, teilen Sie Excel mit, dass leere Zellen aus dieser Richtung entfernt werden sollen:
Wo man den Punkt setzen sollte | Welche Leerzeichen werden entfernt |
|---|---|
Nach dem Doppelpunkt | Nachgestellte Leerzeichen |
Vor dem Doppelpunkt | Führende Leerzeichen |
Beide Seiten des Doppelpunkts | Nachgestellte und vorgestellte Leerzeichen |
Also, bleiben wir bei der gleichen Liste wie zuvor und tippen:
=UNIQUE(B:.B)
Der Punkt nach dem Doppelpunkt teilt Excel mit, dass es an der letzten Zeile mit Daten stoppen soll - in diesem Fall Zeile 50.
Dies ist eine gute Methode, weil:
- Minimalistische Formeln: Sie erhalten die Hochleistungsfunktionen, ohne das visuelle Format Ihrer Tabelle ändern oder Ihre Bereiche in zusätzliche Funktionen einfügen zu müssen.
- Präzisionskontrolle: Sie können genau auswählen und ändern, welche Lücken ignoriert werden sollen, indem Sie die Punkte an den entsprechenden Stellen relativ zu den Doppelpunkten setzen.
- Saubere Datenresultate: Diese Methode verhindert, dass moderne Funktionen nachgestellte Nullen erfassen, und stellt sicher, dass Ihre Ergebnisse genau bleiben und frei von Geisterdaten sind.
Über die Optimierung Ihrer Formeln hinaus sollten Sie diese Gewohnheiten beachten, um Ihre Excel-Arbeitsmappe effizient zu halten:
- Vermeiden Sie übermäßiges Formatieren: Wenden Sie niemals Rahmen oder Farben auf eine gesamte Spalte an.
- Der Ctrl+End-Trick: Verwenden Sie diese Tastenkombination, um herauszufinden, wo Excel denkt, dass Ihre Daten enden. Springt der Cursor zu Zeile 1.000.000, haben Sie Geisterzellen.
- Raster zurücksetzen: Wenn Sie Geisterzellen haben, wählen Sie die leeren Zeilen aus, klicken Sie mit der rechten Maustaste und wählen Sie „Löschen“ (nicht einfach die Rücktaste drücken). Speichern Sie anschließend sofort Ihre Arbeitsmappe, um die letzte Zelle zurückzusetzen und die Dateigröße zu verringern.