Allgemeine Datenbereinigungsformeln in Excel

Excel-Formeln

Seit Jahren nutze ich die Publikation als Ressource, um nicht nur zu beschreiben, wie man Dinge macht, sondern auch, um Aufzeichnungen zu führen, die ich später nachschlagen kann! Heute hatten wir einen Kunden, der uns eine Kundendatendatei überreichte, die eine Katastrophe war. Praktisch jedes Feld war falsch formatiert und; Infolgedessen konnten wir die Daten nicht importieren. Obwohl es einige großartige Add-Ons für Excel gibt, mit denen die Bereinigung mit Visual Basic durchgeführt werden kann, führen wir Office für Mac aus, das keine Makros unterstützt. Stattdessen suchen wir nach geraden Formeln, um Sie zu unterstützen. Ich dachte, ich würde einige davon hier teilen, nur damit andere sie verwenden können.

Entfernen Sie nicht numerische Zeichen

Systeme erfordern häufig das Einfügen von Telefonnummern in eine bestimmte 11-stellige Formel mit der Landesvorwahl und ohne Interpunktion. Leute geben diese Daten jedoch häufig stattdessen mit Bindestrichen und Punkten ein. Hier ist eine großartige Formel für Entfernen aller nicht numerischen Zeichen in Excel. Die Formel überprüft die Daten in Zelle A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Jetzt können Sie die resultierende Spalte kopieren und verwenden Bearbeiten> Werte einfügen um die Daten mit dem richtig formatierten Ergebnis zu überschreiben.

Bewerten Sie mehrere Felder mit einem ODER

Wir löschen häufig unvollständige Datensätze aus einem Import. Benutzer erkennen nicht, dass Sie nicht immer komplexe hierarchische Formeln schreiben müssen und stattdessen eine OR-Anweisung schreiben können. In diesem Beispiel unten möchte ich A2, B2, C2, D2 oder E2 auf fehlende Daten überprüfen. Wenn Daten fehlen, gebe ich eine 0 zurück, andernfalls eine 1. Dadurch kann ich die Daten sortieren und die unvollständigen Datensätze löschen.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Felder trimmen und verketten

Wenn Ihre Daten ein Vor- und Nachname-Feld haben, Ihr Import jedoch ein vollständiges Namensfeld enthält, können Sie die Felder mithilfe der integrierten Excel-Funktion Verketten ordentlich miteinander verknüpfen. Verwenden Sie jedoch TRIM, um alle Leerzeichen vor oder nach dem zu entfernen Text. Wir umschließen das gesamte Feld mit TRIM, falls eines der Felder keine Daten enthält:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Überprüfen Sie die gültige E-Mail-Adresse

Eine ziemlich einfache Formel, die sowohl nach dem @ als auch nach dem sucht. in einer E-Mail-Adresse:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Vor- und Nachnamen extrahieren

Manchmal ist das Problem das Gegenteil. Ihre Daten haben ein vollständiges Namensfeld, aber Sie müssen den Vor- und Nachnamen analysieren. Diese Formeln suchen nach dem Leerzeichen zwischen Vor- und Nachname und greifen bei Bedarf auf Text zu. Die IT-Abteilung kümmert sich auch darum, ob in A2 kein Nachname oder ein leerer Eintrag vorhanden ist.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

Und der Nachname:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Begrenzen Sie die Anzahl der Zeichen und fügen Sie…

Wollten Sie jemals Ihre Meta-Beschreibungen bereinigen? Wenn Sie Inhalte in Excel ziehen und dann für die Verwendung in einem Meta-Beschreibungsfeld (150 bis 160 Zeichen) zuschneiden möchten, können Sie dies mit dieser Formel aus tun Mein Platz. Es bricht die Beschreibung an einer Stelle sauber und fügt dann das…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Natürlich sollen diese nicht umfassend sein ... nur ein paar schnelle Formeln, die Ihnen den Start erleichtern! Welche anderen Formeln verwenden Sie? Fügen Sie sie in den Kommentaren hinzu und ich gebe Ihnen Anerkennung, wenn ich diesen Artikel aktualisiere.

Was denken Sie?

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahren Sie, wie Ihre Kommentardaten verarbeitet werden.