Organisieren Sie Excel-Tabellen mit den Operatoren TRIMRANGE und Trim Ref
Excel ist ein leistungsstarkes Datenverwaltungstool, aber die Organisation großer Tabellen kann ohne die richtigen Tools eine Herausforderung sein. Die Operatoren TRIMRANGE und Trim Ref bieten eine effektive Lösung zum Organisieren von Daten und Reduzieren der Unordnung in Ihren Tabellen. In diesem Artikel zeigen wir Ihnen Schritt für Schritt, wie Sie diese Trigger nutzen können, um die Organisation Ihrer Excel-Tabellen zu verbessern, Ihre Arbeitseffizienz zu steigern und die Datenverwaltung zu vereinfachen. Wenn Sie nach Möglichkeiten suchen, Ihre Excel-Erfahrung zu verbessern, finden Sie hier die entsprechenden Schritte.
Suchen Sie nach einer Möglichkeit, Ihre Tabelle zu organisieren und ihre Größe zu reduzieren, ohne dynamische Bereichsformeln wie OFFSET, INDEX oder TOCOL verwenden zu müssen? TRIMRANGE erkennt die Zellen, die Ihre Daten einnehmen, und erweitert und verkleinert sie automatisch.
ملاحظة
Der Trim Ref-Operator ist eine einfachere, verkürzte Version der TRIMRANGE-Funktion. Darauf werde ich später im Artikel näher eingehen.
TRIMRANGE-STRUKTUR
Die TRIMRANGE-Funktion hat drei Argumente:
=TRIMBEREICH(a,b,c)
Wo
- a (erforderlich) ist der auszuschneidende Bereich,
- b (optional) Gibt die zu trimmenden Zeilen an (0 = keine Zeilen, 1 = führende Zeilen, 2 = nachfolgende Zeilen und 3 = führende und nachfolgende Zeilen) und
- c (Optional) Gibt die zu entfernenden Spalten an (0 = keine Spalten, 1 = führende Spalten, 2 = nachfolgende Spalten und 3 = führende und nachfolgende Spalten).
Wenn Sie die Argumente b und/oder c weglassen, schneidet Excel standardmäßig sowohl die führenden als auch die nachfolgenden Zeilen und/oder Spalten ab.
ملاحظة
Zum Zeitpunkt des Schreibens (Januar 2025) können Sie mit TRIMRANGE keine leeren Zeilen oder Spalten zwischen vorhandenen Daten entfernen. Sie können es nur verwenden, um Daten vor oder nach Ihren Daten auszuschneiden. Verwenden Sie stattdessen andere Methoden, um leere Datenzeilen zwischen mit Daten gefüllten Zeilen zu löschen.
Beispiel 1: TRIMRANGE mit SUM
In diesem Beispiel möchte ich die Werte in Spalte B von denen in Spalte C abziehen, um einen Gewinn zu erzielen.
Ich weiß, dass ich irgendwann in der Zukunft unten weitere Zeilen hinzufügen werde, daher möchte ich, dass Excel diese Zeilen automatisch als Teil der Subtraktionsformel berücksichtigt. Dazu kann ich schreiben:
=(C2:C200)-(B2:B200)
In Zelle D2 werden dadurch Verweise auf die ersten 200 Zeilen gelöscht. Allerdings entsteht dadurch eine unübersichtliche Tabelle mit vielen leeren Berechnungen. Wenn Sie vollständige Spaltenbezüge verwenden, wird die Berechnung außerdem in der Tabelle angezeigt. Dies führt zu über einer Million Berechnungen, wodurch die Größe der Tabelle erheblich zunimmt und sie langsamer wird.
Stattdessen schreibe ich:
=TRIMRANGE(C2:C200)-TRIMRANGE(B2:B200)
In Zelle D2. Dadurch würden die doppelten Zellen am Ende meiner Daten effektiv verkleinert, um zu verhindern, dass Excel zu viel arbeiten muss, und um dafür zu sorgen, dass meine Tabelle ordentlich aussieht.
ملاحظة
Ich habe die Argumente b und c in den obigen TRIMRANGE-Referenzen weggelassen, da die Standardeinstellung von Excel zum Trimmen führender und nachfolgender Spalten und Zeilen in diesem Beispiel einwandfrei funktioniert, da es keine leeren führenden Zeilen oder Spalten und keine Daten rechts von Spalte D gibt. .
Wenn ich jetzt „Mehr“ aus den Datenzeilen unten hinzufüge, werden die entsprechenden Zellen in der Spalte „Gewinn“ automatisch berechnet.
Beispiel 2: TRIMRANGE mit XLOOKUP
In diesem zweiten Beispiel habe ich eine Liste mit Fußballspielern und verwende die Funktion XLOOKUP, um herauszufinden, welche Trikotfarbe sie haben müssen, je nachdem, zu welcher Mannschaft sie gehören.
Ich weiß auch, dass ich fünf weitere Spieler rekrutieren werde. Wenn ich also die XLOOKUP-Formel hinzufüge, muss ich sie bis zur Zeile 22 erweitern. In Zelle E2 kann ich also Folgendes eingeben:
=XLOOKUP(B2:B22,$G$2:$G$7,$H$2:$H$7)
wobei B2:B22 der Bereich, $G$2:$G$7 das Sucharray und $H$2:$H$7 das Rückgabearray ist. Ich habe $-Symbole verwendet, um Excel mitzuteilen, dass es sich um absolute (feste) Zellbezüge handelt. Allerdings werden dadurch, wie im vorherigen Beispiel, die fünf Zeilen mit leeren Daten aufgrund des Fehlers #N/A ungeordnet erscheinen. Darüber hinaus muss Excel mehr arbeiten als es sollte. Dies kann die Leistung beeinträchtigen, wenn Ihre Formel viele leere Zeilen enthält.
Ich kann auch OFFSET, INDEX oder TOCOL verwenden, um dynamische Bereichsformeln zu erstellen, aber dies sind komplexere Möglichkeiten, um dasselbe Ergebnis zu erzielen wie mit der einfacheren TRIMRANGE-Methode.
Also gebe ich in Zelle E2 Folgendes ein:
=XLOOKUP(TRIMRANGE(B2:B22,2),$G$2:$G$7,$H$2:$H$7)
Es ist genau die gleiche Formel wie oben, außer dass ich innerhalb der TRIMRANGE-Funktion auf den Bereich (B2:B22) verwiesen habe. Beachten Sie, dass ich mich dieses Mal entschieden habe, das zweite Argument („2“) einzuschließen, das Excel mitteilt, dass ich die nachfolgenden leeren Zeilen entfernen möchte. Ich habe jedoch kein drittes Argument aufgenommen, da sich mein Umfang nur auf eine Spalte erstreckt.
Dieses Mal hat Excel mein XLOOKUP-Ergebnis abgeschnitten, und als ich unten mehrere Datenzeilen hinzugefügt habe, wurde die Zelle in Spalte E automatisch ausgefüllt.
Trim Ref-Operatoren verwenden
Einige der Formeln, die ich in den obigen Beispielen verwendet habe, sind sicherlich ziemlich komplex. Aus diesem Grund hat Microsoft auch die Trim Ref-Operatoren eingeführt, eine verkürzte Version der TRIMRANGE-Funktion. Mit anderen Worten: Trim Ref-Operatoren ersparen Ihnen die Einbindung von TRIMRANGE in andere Funktionen. Darüber hinaus müssen Sie bei Trim-Ref-Operatoren nicht angeben, ob Sie Zeilen oder Spalten trimmen, da sie automatisch beides trimmen.
Bei Verwendung des Trim Ref-Operators fügen Sie in Ihrer Formel auf einer oder beiden Seiten des Doppelpunkts einen Punkt (.) hinzu:
|
|
||
|---|---|---|---|
|
|
||
|
|
||
|
Zusätzliche Leerzeichen am Anfang und Ende |
ملاحظة
Da Trim-Ref-Operatoren einen einzelnen „Punkt“ in Ihrer Formel darstellen, sind sie bei der Überprüfung Ihrer Arbeit oder der Tabelle einer anderen Person möglicherweise schwer zu erkennen. Sie sollten auf der Hut sein, wenn die Dinge nicht wie erwartet laufen!
Ich verwende dieselben Fußballspielerdaten wie in Beispiel 2 und gebe in Zelle E2 Folgendes ein:
=XLOOKUP(B2:.B22,$G$2:$G$7,$H$2:$H$7)
Beachten Sie, dass ich in B2:.B22 nach dem Doppelpunkt einen Punkt hinzugefügt habe. Der Grund hierfür ist, dass ich möchte, dass Excel die nachstehenden Leerzeichen entfernt. Der Screenshot unten zeigt dasselbe Ergebnis, obwohl ich eine explizitere Formel verwendet habe, ohne die TRIMRANGE-Funktion in meine Formel einzubeziehen.
Wenn ich beispielsweise die Funktion TRIMRANGE verwende, weiß ich, dass ich unten mehrere Datenzeilen hinzufügen kann und die Funktion XLOOKUP auf diese Daten angewendet wird.
Warum TRIMRANGE und Trim Refs anstelle strukturierter Tabellen verwenden?
Sie könnten zu Recht argumentieren, dass Sie Ihre Daten stattdessen in formatierten Excel-Tabellen darstellen und strukturierte Referenzen verwenden sollten, die automatisch erweitert werden, um neue Daten und alle damit verbundenen Berechnungen einzuschließen, die Sie erstellen. In den meisten Situationen stimme ich zu.
Allerdings können Spill-Matrizen (und einige LAMBDAS) nicht in strukturierte Tabellen eingefügt werden. In diesen Situationen ist die Verwendung von TRIMRANGE eine nützliche Alternative. In manchen Szenarien möchten Sie einen Bereich möglicherweise auch außerhalb einer strukturierten Tabelle platzieren (beispielsweise, wenn Sie Ihre Daten eindeutig formatieren möchten) und die Funktion TRIMRANGE unterstützt Sie dabei, unstrukturierte Daten zu organisieren.
Neben der TRIMRANGE-Funktion und den Trim Refs-Operatoren von Excel gibt es noch andere Möglichkeiten, Ihre Daten in Excel zu bereinigen, z. B. die Verwendung von Power Query oder die volle Nutzung der KI mit Copilot.