Bei der Arbeit mit Daten in Excel können sich manche Aufgaben unnötig mühsam anfühlen. Vielleicht müssen Sie eine Spalte mit vollständigen Namen in separate Spalten für Vor- und Nachnamen aufteilen oder Text aus mehreren Zellen mit bestimmten Kommas kombinieren. Dabei handelt es sich nicht um komplexe analytische Herausforderungen, sondern um grundlegende Datenverarbeitungsaufgaben, die regelmäßig auftreten.
Die gute Nachricht ist, dass Excel über integrierte Funktionen verfügt, die speziell für diese Situationen entwickelt wurden. Sie werden jedoch oft übersehen, da sie nicht Teil von Das Standard-Excel-Toolset, das die meisten Leute lernen, mich eingeschlossen. Bei den Funktionen, die ich hier beschreibe, geht es nicht um fortgeschrittene Berechnungen, aber wenn Sie häufig mit Daten arbeiten, können diese Funktionen Ihnen etwas Zeit sparen.
Schnelle Links
5. TEXTSPLIT
Trennt zusammengeklebte Texte
Wenn Sie schon einmal eine Tabelle erhalten haben, in der jemand seinen Vor- und Nachnamen und vielleicht sogar seine zweiten Initialen in eine einzige Zelle gequetscht hat, wissen Sie, wie mühsam es ist, diese Daten zu trennen. TextSplit löst genau dieses Problem: Es nimmt Text aus einer einzelnen Zelle und teilt ihn basierend auf einem von Ihnen angegebenen Trennzeichen auf mehrere Spalten auf.
Lassen Sie uns mit einer Beispiel-Verkaufstabelle arbeiten. Sie sehen die Namen der Vertriebsmitarbeiter als „Sarah Chen“, „Mike Johnson“ und „Lisa Park“ in einer Spalte. Anstatt jeden Namen manuell in separate Spalten einzugeben, kann TextSplit die Arbeit automatisch erledigen.
Die Formel lautet wie folgt:
=TEXTSPLIT(Text, Spaltentrennzeichen, [Zeilentrennzeichen], [Leere_ignorieren], [Übereinstimmungsmodus], [Auffüllen_mit])
Folgendes macht jeder Lehrer:
- Text: Die Zelle, die den Text enthält, den Sie teilen möchten.
- Spaltenbegrenzer: Das Zeichen, das Ihre Daten trennt (z. B. ein Leerzeichen, Komma oder Semikolon).
- Zeilentrennzeichen (optional): Wird beim Aufteilen in Zeilen und Spalten verwendet.
- ignore_empty (optional): TRUE ignoriert leere Werte, FALSE behält sie (Standard ist FALSE).
- match_mode (optional): Steuert die Groß-/Kleinschreibung (0 für Groß-/Kleinschreibung, 1 für Groß-/Kleinschreibung nicht).
- pad_with (optional): Womit füllen Sie leere Zellen, wenn die Ergebnisse ungleich lang sind?
Für die Namen der Vertriebsmitarbeiter würde ich beispielsweise die folgende Formel verwenden, um die Namen in separate Spalten aufzuteilen:
=TEXTSPLIT(A2, " ")
Die Funktion erstellt automatisch die erforderliche Anzahl von Spalten basierend auf Ihren Daten. Während dieser grundlegende Ansatz in den meisten Fällen funktioniert, gibt es zusätzliche Parameter, die Ihnen eine feinere Kontrolle über TEXTSPLIT-Funktion in Excel.
4. TEXTVERBINDEN
Mehrere Zellen zu einer Zelle zusammenführen
TEXTJOIN macht das Gegenteil von TEXTSPLIT. Es nimmt Text aus mehreren Zellen und kombiniert sie mit dem von Ihnen gewählten Trennzeichen zu einer einzigen Zelle. Dies ist nützlich, wenn Sie sequenzielle Werte wie vollständige Adressen, Produktbeschreibungen oder E-Mail-Listen erstellen müssen.
Die Formel sieht folgendermaßen aus:
=TEXTVERBINDUNG(Trennzeichen, "Leere_ignorieren", "Text1", "[Text2]", "...)
Jeder Parameter steuert Folgendes:
- Trennzeichen: Das Zeichen oder der Text, der die eingebetteten Werte trennt (Komma, Leerzeichen, Bindestrich usw.).
- leer ignorieren: TRUE, um leere Zellen zu ignorieren, FALSE, um sie in das Ergebnis einzubeziehen.
- text1, text2 usw.: Die Zellen oder Bereiche, die Sie zusammenführen möchten (Sie können einzelne Zellen oder ganze Bereiche angeben).
Wenn ich mir die Verkaufstabelle ansehe und separate Spalten für Vorname und Region habe, diese aber in einer Spalte brauche, die sie kombiniert, würde ich TEXTJOIN verwenden. ignorieren_leer Auf WAHR bedeutet, dass alle leeren Zellen automatisch übersprungen werden.
=TEXTJOIN(" - ", WAHR, B2, D2)Bei der Auswahl zwischen verschiedenen Textintegrationsmethoden ist es wichtig zu verstehen, Unterschiede zwischen den Funktionen CONCAT und TEXTJOIN Es kann Ihnen dabei helfen, das richtige Tool für Ihre spezifischen Datenintegrationsanforderungen auszuwählen.
3. AUSWAHL
Geben Sie bestimmte Spalten Ihrer Daten an.
Mit CHOOSECOLS können Sie bestimmte Spalten aus einem Bereich extrahieren, ohne kopieren und einfügen oder Referenzen erstellen zu müssen. Wenn Sie einen großen Datensatz haben, aber nur die Spalten 2, 5 und 8 für Ihre Analyse benötigen, extrahiert diese Funktion die benötigten Daten und verwirft den Rest.
Anhand der Verkaufsdaten möchte ich möglicherweise nur die Verkäufer und deren Namen extrahieren und Bestelldaten, Produktkategorien und andere Details ignorieren. Anstatt Spalten manuell auszuwählen und zu kopieren, erstellt die Funktion CHOOSECOLS eine dynamische Referenz, die bei Änderungen der Quelldaten automatisch aktualisiert wird.
Die Funktion folgt der folgenden Formel:
=CHOOSECOLS(Array, col_num1, [col_num2], ...)
So funktioniert jeder Parameter:
- Array: Der Bereich oder die Tabelle, die Ihre Quelldaten enthält (es kann ein Zellbereich wie A1:F100 oder ein Tabellenverweis sein).
- Spaltennummer1: Die Nummer der ersten Spalte, die Sie extrahieren möchten (1 für die erste Spalte, 2 für die zweite Spalte usw.).
- col_num2 usw.: Zusätzliche Spaltennummern, die Sie einschließen möchten (optional – Sie können so viele angeben, wie Sie möchten).
Wenn ich beispielsweise die Namen der Vertriebsmitarbeiter aus Spalte 2 und ihren Status aus Spalte 9 extrahieren möchte, würde ich Folgendes verwenden:
=CHOOSECOLS(A1:I23, 2, 9)
Die Funktion gibt beide Spalten als gestreamtes Array zurück, dessen Größe automatisch an die Daten angepasst wird. Deshalb ist CHOOSECOLS eine der Excel-Funktionen, die Ihnen viel Zeit sparen könnenDadurch entfällt die Notwendigkeit mehrerer SVERWEIS-Formeln oder des manuellen Kopierens von Spalten bei der Arbeit mit großen Datensätzen.
Excel verfügt auch über eine CHOOSEROWS-Funktion, die ähnlich funktioniert, aber bestimmte Zeilen statt Spalten auswählt und dabei dieselbe Formelstruktur mit Zeilennummern verwendet.
2. Nehmen und Ablegen
Extrahieren Sie Teile Ihrer Daten
TAKE und DROP arbeiten zusammen, um bestimmte Teile Ihres Datenbereichs zu erfassen. TAKE extrahiert eine bestimmte Anzahl von Zeilen oder Spalten vom Anfang oder Ende Ihres Datensatzes, während DROP Zeilen oder Spalten vom Anfang oder Ende entfernt und Ihnen den Rest übrig lässt.
Diese Funktionen dienen als präzise Werkzeuge für die Datenstichprobe. Egal, ob Sie nur die ersten zehn Datenzeilen für eine schnelle Analyse benötigen oder Kopfzeilen entfernen möchten, die Ihre Berechnungen behindern, diese Funktionen erledigen die Aufgabe problemlos.
TAKE verwendet diese Formel:
=TAKE(Array, Zeilen, [Spalten])
DROP folgt einem ähnlichen Muster:
=DROP(Array, Zeilen, [Spalten])
So funktionieren die Parameter für beide Funktionen:
- Array: Der Quelldatenbereich, den Sie extrahieren oder ändern möchten.
- Reihen: Anzahl der zu nehmenden/zu löschenden Zeilen (positive Zahlen gelten von oben, negative von unten).
- Spalten (optional):
Die Anzahl der Spalten, die Sie übernehmen oder löschen möchten (positiv von links, negativ von rechts).
Um die ersten fünf Zeilen der Verkaufsdaten zu erhalten, verwenden Sie die folgende Formel:
=TAKE(A1:C100, 5)
Um die ersten 20 Zeilen zu entfernen und mit sauberen Daten zu arbeiten, versuchen Sie:
=DROP(A1:C23, 20)
Sie können Zeilen- und Spaltenoperationen kombinieren. Mit der folgenden Formel erhalten Sie beispielsweise die ersten zehn Zeilen und die ersten drei Spalten:
=TAKE(A1:F23, 10, 3)
Diese Funktionen sind besonders nützlich, wenn Sie dynamische Teilmengen von Daten benötigen, die sich automatisch anpassen. Erfahren Sie So verwenden Sie die TAKE- und DROP-Funktionen in Excel Es eröffnet Ihnen die Möglichkeit, flexible Berichte zu erstellen, die sich an wechselnde Datensatzgrößen anpassen.
1. AGGREGAT
Leistungsstarke Berechnungen für die Verarbeitung unübersichtlicher Daten
AGGREGATE kombiniert die Funktionalität von 19 verschiedenen Statistikfunktionen in einer flexiblen Formel. Das Besondere daran ist die Fähigkeit, Fehler, ausgeblendete Zeilen oder gefilterte Daten zu ignorieren – etwas, das Standardfunktionen wie SUM oder AVERAGE nicht zuverlässig leisten können.
Wenn Ihre Daten Fehler vom Typ #N/A enthalten oder Sie nur bestimmte Regionen filtern, kann AGGREGATE Summen, Durchschnittswerte oder andere Statistiken berechnen, ohne dass diese Probleme Ihre Ergebnisse beeinträchtigen. Ich finde es nützlich bei der Arbeit mit dynamischen Datensätzen, bei denen sich Sichtbarkeit und Datenqualität häufig ändern.
Der Satzbau umfasst mehrere Komponenten:
=AGGREGATE(function_num, options, array, [k])
Jedes Kriterium steuert unterschiedliche Aspekte der Berechnung:
- Funktionsnummer: Eine Zahl zwischen 1 und 19, die die zu verwendende Funktion angibt (1=DURCHSCHNITT, 4=MAX, 9=SUMME, 12=MEDIAN usw.).
- Optionen: Steuert, was bei der Berechnung ignoriert werden soll (0=keine, 1=versteckte Zeilen, 2=Fehlerwerte, 3=versteckte Zeilen und Fehler, 5=nur Fehlerwerte, 6=versteckte Zeilen und Fehlerwerte).
- Array: Der zu berechnende Zellbereich.
- k (optional):
- Wird nur mit bestimmten Funktionen wie „LARGE“, „SMALL“ oder „PERCENTILE“ verwendet.
Um die angezeigten Verkaufsbeträge zusammenzufassen und dabei etwaige Fehler zu ignorieren, kann ich Folgendes verwenden:
=AGGREGAT(9; 6; D2:D23)
Die Zahl 9 gibt die SUMME an und die Zahl 6 weist die Funktion an, sowohl ausgeblendete Zeilen als auch Fehlerwerte zu ignorieren.
Diese leistungsstarke Fähigkeit zur Durchführung von Berechnungen ist genau der Grund, warum AGGREGATE enthalten ist. Liste der Excel-Funktionen, die jeder Büroangestellte kennen sollte– Es bewältigt das Chaos realer Daten, das einfachere Funktionen nicht effektiv bewältigen können.
Integrierte Tools, deren Verwendung sich lohnt
Die wichtigsten Excel-Funktionen sind oft nicht die, die man zuerst lernt. Sie lösen jedoch die subtilen Probleme, die bei der tatsächlichen Tabellenkalkulation auftreten, einschließlich des Umgangs mit unübersichtlichen Textdaten, des Extrahierens bestimmter Teile aus großen Datensätzen und der Durchführung von Berechnungen mit unvollständigen Daten. Keine der besprochenen Funktionen erfordert fortgeschrittene Excel-Kenntnisse. TEXTSPLIT, CHOOSECOLS, TAKE und DROP sind jedoch nur in Microsoft 365 und Excel für das Web verfügbar.
Wenn Sie das nächste Mal wiederholt Daten bereinigen oder Spalten manuell kopieren müssen, denken Sie an diese Funktionen. Sie sind bereits in Excel integriert und übernehmen die mühsame Arbeit, sodass Sie sich auf das konzentrieren können, was Ihnen die Daten tatsächlich sagen.
