Die Datenextraktion und -bereinigung ist die erste und wichtigste Aufgabe jedes Datenanalyseprojekts. In meiner fünfjährigen Erfahrung habe ich Daten erhalten, die wie Müll aussahen, aber mit den Excel-Funktionen RECHTS und LINKS konnte ich sie in aussagekräftige Berichte umwandeln.
Die Datenbereinigung in Excel ist einfach. Hier werde ich erklären, wie Sie in Excel 2016 bestimmten Text aus einer Zelle extrahieren und Mülldaten hinterlassen können.
Im obigen Bild werden mir Daten in Excel zur Verfügung gestellt. Es hat Name, Alter, und wenn die Person hat eine Versicherung oder keine (J/N). Um diese Daten zu verwenden, muss ich Daten in verschiedenen Spalten extrahieren. Jede Spalte sollte nur eine Art von Wert enthalten. Sobald dies erledigt ist, können wir Pivot-Berichte in Excel erstellen, die uns besser über die Daten informieren. Also lasst uns anfangen.
Excel RIGHT-Funktion zum Extrahieren von Daten rechts vom Text
Beginnen wir mit dem einfachsten. In diesem Beispiel haben wir "Hat eine Versicherung?" Daten am Ende des Strings. Und das ist nur ein Zeichen (Y oder N). Um diese Daten zu extrahieren, verwenden wir die RECHTE Formel, die Excel zur Verfügung stellt. Die Funktion RECHTS ist in Excel 2016, Excel 2013, Excel 2010 und älteren Excel-Versionen verfügbar.
Die RECHTE Funktion in Excel benötigt nur zwei Argumente. Erstens den TEXT und zweitens die ANZAHL DER ZEICHEN, die Sie in Excel von rechts extrahieren möchten.
Generische Formel:
=RECHTS(Text, Anzahl_der_Zeichen)
Hier haben wir unsere Text in der Zelle B3 und die Anzahl der Zeichen, die wir extrahieren möchten, ist 1. Schreiben Sie diese Formel einfach in Excel in die Zelle D3 oder wo immer Sie diese Daten haben möchten.
=RECHTS(B3,1)
Sie erhalten Ihr Ergebnis in Zelle D3. Kopieren oder ziehen Sie diese Formel einfach in die Zellen unten.
Excel LEFT-Funktion zum Extrahieren von Daten aus der linken Seite des Textes
Nehmen wir jetzt unser Alter. Bevor ich es erkläre, zeige ich Ihnen die Formel. Kopieren Sie diese Formel in die gewünschten Zellen.
=LINKS(RECHTS(B3,4,2)
In der obigen Formel haben wir LEFT- und RIGHT-Funktionen kombiniert, um unseren spezifischen Text aus der Zeichenfolge zu erhalten. So sieht es aus.
Lassen Sie uns nun diese mickrige Formel verstehen.
Jede Funktion arbeitet von innen nach außen. Beginnen wir also mit:
RECHTS(B3,4): Es extrahiert einfach 4 Zeichen aus einem bestimmten Text. Das gibt uns 30_Y. Warum 4? Da wir wissen, dass das Alter nur eines von zwei Zeichen ist, haben wir 1 „_“ und 1 J oder N.
LINKS(RECHTS(B3,4),2): Jetzt haben wir unseren Text, der mit einem Alter von 2 Zeichen beginnt. Hier kommt die LEFT-Funktion zum Einsatz. Es braucht RECHTS(B3,4) als Textargument und extrahiert daraus 2 Zeichen. Das gibt uns 30 für die erste Zelle. Kopieren Sie die Formel in die Zellen unten und Sie erhalten den Rest der Daten, wie in der Abbildung oben gezeigt.
Allgemeine Formel für LINKS:
=LINKS(Text, Anzahl_der Zeichen)
Genau wie die RECHTE-Funktion in Excel die LINKE-Funktion von Excel auch zwei gleiche Argumente verwendet. Es extrahiert nur Daten aus der linken Seite der bereitgestellten Zeichenfolge.
Excel LEFT- und Right-Funktion zum Extrahieren unbekannter Textlänge aus einer Zeichenfolge
Fügen Sie diese Formel einfach in die gewünschten Zellen ein, um den Namen aus dem Text in Excel zu extrahieren.
=LINKS(B3,LEN(B3)-5)
In den obigen Beispielen kannten wir die genaue Anzahl der Texte, die aus dem gegebenen Text extrahiert werden mussten. Aber jetzt wissen wir nicht, wie lang ein Name ist. Was kann getan werden?
Wir wissen, dass dem Namen genau 5 Zeichen folgen. 1 für das Leerzeichen, 2 für das Alter, 1 für den Unterstrich „_“ und einen für das Y.
Wenn wir die gesamte Textanzahl erhalten und dann 5 davon abziehen können, haben wir die genaue Textanzahl für den Namen.
Um die Länge eines Textes in Excel zu ermitteln, können wir die LEN-Funktion verwenden. Die LEN-Funktion von Excel gibt die Anzahl der beteiligten Zeichen zurück.
Syntax:
=LEN(Text)
So LEN(B3) gibt 20 zurück.
LEN(B3)-5 gibt 15 zurück. Und das ist die Länge des Namens in Zelle B3
Zu guter Letzt LINKS(B3, LEN(B3)-5) gibt uns Georgina Schalk als Ergebnis. Wenn Sie die Formel in die darunter liegenden Zellen kopieren, erhalten Sie alle Namen.
Jetzt können Sie solche Berichte basierend auf extrahierten Daten mithilfe von Pivot-Tabellen erstellen. Es sagt uns, dass die Hälfte unserer Probanden eine Versicherung hat und die andere Hälfte nicht.
Die LINKE und RECHTE FUNKTION von Excel ist sehr leistungsstark und nützlich für die Datenbereinigung. In Kombination mit der LEN-Funktion, der FIND-Funktion und der SUBSTITUTE-Funktion von Excel können sie jede Teilzeichenfolge innerhalb einer Zeichenfolge in Excel extrahieren.
In Zukunft werden wir eine komplexere Datenextraktion in Excel sehen. Für jetzt tschüss. Und ja, hinterlassen Sie Ihre Gedanken im Kommentarbereich unten. Ich helfe Ihnen gerne weiter.