Nach Jahren der Arbeit mit komplexen und unübersichtlichen Tabellen habe ich vier Excel-Funktionen entdeckt, die mir jede Woche viele Stunden Arbeit ersparen, indem sie Routineaufgaben automatisieren, die die meisten Menschen manuell ausführen. Diese Funktionen sind unverzichtbar für jeden, der regelmäßig mit Daten arbeitet – egal, ob Sie professioneller Datenanalyst sind oder nur gelegentlich Ihre Arbeit vereinfachen möchten.
Schnelle Links
4. XLOOKUP: Erweiterte Suche in Tabellenkalkulationen
XLOOKUP Es handelt sich um eine erweiterte Suchfunktion in Tabellenkalkulationsprogrammen wie Microsoft Excel und Google Sheets, die über die Möglichkeiten herkömmlicher Suchfunktionen hinausgeht, wie z. B. SVERWEIS و WVERWEIS. Jetzt XLOOKUP Größere Flexibilität, effizientere Datenverarbeitung und weniger häufige Fehler, die mit Legacy-Funktionen verbunden sind. XLOOKUP Ein unverzichtbares Tool für Finanzanalysten, Datenwissenschaftler und alle, die mit großen Datenmengen arbeiten und schnell und präzise spezifische Informationen extrahieren müssen. Durch die Verwendung XLOOKUPSie können nach einem Wert in einem bestimmten Bereich suchen und einen entsprechenden Wert aus einem anderen Bereich zurückgeben, unabhängig von der Position der Spalten oder Zeilen. Es unterstützt auch XLOOKUP Die Suche erfolgt von rechts nach links und von unten nach oben und ist daher vielseitiger als andere Funktionen.
Tschüss SVERWEIS: XLOOKUP ist die perfekte Lösung
Ich habe SVERWEIS vor Jahren nicht mehr verwendet, als ich XLOOKUP entdeckte. Während SVERWEIS nur nach rechts sucht und beim Verschieben von Spalten abstürzt, funktioniert XLOOKUP in jede Richtung und bleibt flexibel. XLOOKUP ist eines der Excel-Funktionen, die Ihnen Zeit sparen können Suchen Sie in Ihren Tabellen nach bestimmten Daten.
In meinen Preisdaten für Computerkomponenten muss ich spezifische GPU-Preise basierend auf Produktmodellen finden. Mit SVERWEIS müsste ich die gesamte Tabelle neu strukturieren. Mit XVERWEIS muss ich jedoch nur Folgendes eingeben:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP durchsucht die gesamte Produktspalte, findet meine GPU und gibt den entsprechenden Preis zurück. Es spielt keine Rolle, wo sich die Preisspalte befindet, und es stürzt nicht ab, wenn ich später weitere Spalten hinzufüge. Ich verwende dies ständig, um Produktinformationen über verschiedene Tabellenblätter hinweg zu referenzieren, ohne etwas neu formatieren zu müssen.
Die Grundformel für XLOOKUP lautet:
=XLOOKUP(Suchwert, Sucharray, Rückgabearray)
- Lookup-Wert: Der Wert, nach dem Sie suchen möchten.
- lookup_array: Der Ort, an dem Sie nach Wert suchen.
- Rückgabearray: Die Spalte oder Zeile, die den Wert enthält, den Sie zurückgeben möchten.
In meinem Fall war der Wert, den ich finden wollte, „GIGABYTE GeForce RTX 3060 12GB Gaming OC“. Ich wollte diesen Wert in der Spalte C:C suchen und den entsprechenden Wert aus D:D in derselben Zeile zurückgeben, in der die Übereinstimmung gefunden wurde.
Ein weiterer Vorteil von XLOOKUP ist, dass die Suche von unten nach oben erfolgt, wenn ich am Ende der Formel „,-1“ anfüge. So finde ich automatisch den aktuellsten Preiseintrag. So muss ich die Daten nicht jedes Mal manuell sortieren, wenn ich meine Tabellen aktualisiere.
3. Meine Funktionen verwenden SUMME و ZÄHLER In Tabellenkalkulationen
Professioneller Umgang mit mehreren Standards
Die grundlegenden Funktionen SUMME und ANZAHL reichen für einfache Aufgaben aus, reichen aber für die Analyse in der Praxis nicht aus. Wenn ich meine Preisdaten unter mehreren Bedingungen analysieren muss, verwende ich normalerweise die Funktionen SUMMEWENNS und ZÄHLENWENNS. Damit kann ich Hunderte von Zeilen problemlos segmentieren.
Angenommen, ich möchte die Anzahl der bei Amazon US verfügbaren AMD-Prozessoren zählen. Anstatt manuell zu filtern, gebe ich Folgendes ein:
=ZÄHLENWENNS(F:F, "Amazon US", K:K, "AMD")
Dadurch wird mir sofort angezeigt, dass in meinem Datensatz 14 AMD-Prozessoren auf Amazon gelistet sind. Das Schöne daran ist, dass ich so viele Benchmarks erstellen kann, wie ich brauche.
Für die Preisanalyse funktioniert die Funktion SUMIFS auf die gleiche Weise. Um den Gesamtwert aller derzeit auf Lager befindlichen Intel-Prozessoren zu berechnen, verwende ich:
=SUMMEWENNS(D:D, K:K, "Intel", G:G, "Auf Lager")
Dadurch werden alle Preise in Spalte D addiert, bei denen die Marke „Intel“ und der Lagerstatus „Auf Lager“ ist.
Die Syntax für die Funktion SUMIFS lautet:
=SUMMEWENNS(Summenbereich; Kriterienbereich1; Kriterien1; Kriterienbereich2; Kriterien2...)
- Summenbereich: Die Spalte, die Sie summieren möchten.
- Kriterienbereich1: Die erste Spalte, anhand derer die Bedingungen überprüft werden.
- Kriterium 1: Erste Bereichsbedingung.
- Kriterienbereich2, Kriterien2: Zusätzliche Geschäftsbedingungen (optional).
Die Funktion ZÄHLENWENNS funktioniert ähnlich, außer dass sie die übereinstimmenden Zeilen zählt, anstatt die Werte zu summieren:
=COUNTIFS(Kriterienbereich1; Kriterien1; Kriterienbereich2; Kriterien2...)
Ich bevorzuge die Funktionen SUMMEWENNS und ZÄHLENWENNS für schnelle Berichte, da sie neue Daten sofort aktualisieren, sich nahtlos in meine bestehenden Formeln einfügen und es mir ermöglichen, alles inline zu halten, ohne eine separate Pivot-Tabelle erstellen zu müssen. Diese Tools ermöglichen eine präzise und effiziente Datenanalyse und sparen Zeit und Aufwand bei der Erstellung komplexer Berichte. Die Verwendung von Funktionen wie SUMMEWENNS und ZÄHLENWENNS ist eine unverzichtbare Fähigkeit für jeden Datenanalysten, der schnell und einfach wertvolle Erkenntnisse aus Daten gewinnen möchte.
2. Trimmen und Reinigen: Wichtige Schritte zur Erhaltung des Aussehens
Datenchaos ade
Nichts ruiniert eine Tabelle schneller als unstrukturierte Daten voller überflüssiger Leerzeichen und versteckter Zeichen. Ich musste das auf die harte Tour lernen, als meine Suchvorgänge aufgrund überflüssiger Leerzeichen am Ende von Formularnamen immer wieder fehlschlugen.
Die Funktion TRIM entfernt überflüssige Leerzeichen am Anfang und Ende des Textes sowie alle überflüssigen Leerzeichen zwischen Wörtern. Beim Importieren von Daten aus verschiedenen Quellen enthalten Produktnamen oft inkonsistente Leerzeichen. Anstatt jede Zelle manuell zu bereinigen, erstelle ich eine Hilfsspalte und verwende:
=TRIM(C2)
Dann bewege ich den Mauszeiger an den Rand der Zelle, bis er sich in ein Pluszeichen (+) verwandelt, und ziehe ihn dann nach unten zu allen Zeilen, auf die die TRIM-Funktion angewendet werden soll.
1. TEXTBEFORE und TEXTAFTER: Eine ausführliche Erklärung und ihre Bedeutung
Exaktes Extrahieren der benötigten Daten
Die Funktionen TEXTBEFORE und TEXTAFTER gehören zu meinen bevorzugten Excel-Funktionen, um unübersichtliche Tabellen aufzuräumen. Die modernen Textfunktionen von Excel eignen sich hervorragend zum Extrahieren spezifischer Informationen aus unstrukturierten Textzeichenfolgen. Beispielsweise enthielt meine Preisspalte Einträge wie „$177.52“, „178.33 USD“, „₱9055“ und „9645.50 PHP“, die durcheinandergewürfelt waren.
Die Funktion TEXTBEFORE extrahiert alles, was einem angegebenen Trennzeichen vorangeht:
=TEXTBEFORE(D2, "USD")
Auf diese Weise extrahierte die Funktion sofort „178.33“ aus „178.33 USD“.
Die Funktion TEXTAFTER arbeitet umgekehrt und extrahiert alles nach dem Trennzeichen:
=TEXTAFTER(C2, "AMD ")
Auf diese Weise habe ich die Funktion „Ryzen 5 5700X 8-Core AM4 Processor“ aus „AMD Ryzen 5 5700X 8-Core AM4 Processor“ extrahiert.
Für komplexe Extraktionen kombiniere ich beide Funktionen. So erhalten Sie den numerischen Preis von 177.52 USD:
=TEXTBEFORE(TEXTAFTER(D8, "$"), "USD")
Die allgemeine Syntax der Funktionen TEXTBEFORE und TEXTAFTER lautet:
=TEXTBEFORE(Text; Trennzeichen) und =TEXTAFTER(Text; Trennzeichen)
Die enorme Verbesserung dieser beiden Funktionen liegt in ihrer Präzision. Anstatt komplexe Kombinationen aus MID-, FIND- und LEN-Funktionen zu verwenden, kann ich mit einfachen, leicht verständlichen Formeln saubere Extraktionen erzielen. Ich verwende diese Funktionen häufig, um Modellnummern zu trennen, Produktspezifikationen zu extrahieren und saubere Daten aus importiertem Text zu extrahieren, was früher stundenlange manuelle Bearbeitung erforderte.
Diese vier Funktionen beheben einige der größten Zeitfresser in Excel, wie z. B. das Auffinden von Daten mithilfe flexibler Suchvorgänge, das Analysieren anhand mehrerer Kriterien, das Bereinigen unübersichtlichen importierten Textes und das Extrahieren spezifischer Informationen aus komplexen Textzeichenfolgen. Die meisten Benutzer erledigen diese Aufgaben manuell und verbringen Stunden mit Aufgaben, deren Implementierung der richtigen Formeln normalerweise nur wenige Minuten dauern würde.
Sie haben diese Funktionen für alles Mögliche verwendet, von der Komponentenpreisanalyse bis hin zu Bestandsverwaltungsberichten. Sie funktionieren branchenunabhängig, denn unübersichtliche Daten und komplexe Suchanforderungen sind universelle Probleme. Sobald Sie diese Funktionen beherrschen, werden Sie sich fragen, wie Sie Tabellenkalkulationen jemals ohne sie verwalten konnten.
