Fórmules comunes de neteja de dades a Excel

fórmules excel

Durant anys, he utilitzat la publicació com a recurs per descriure no només com fer les coses, sinó també per guardar un registre perquè pugui mirar més endavant. Avui teníem un client que ens va lliurar un fitxer de dades de clients que va suposar un desastre. Pràcticament tots els camps estaven mal formatats; com a resultat, no hem pogut importar les dades. Tot i que hi ha alguns complements excel·lents per a Excel per fer la neteja mitjançant Visual Basic, executem Office per a Mac que no admet les macros. En el seu lloc, busquem fórmules directes per ajudar-vos. Vaig pensar que compartiria alguns d'aquests aquí perquè altres els puguin utilitzar.

Elimineu els caràcters no numèrics

Els sistemes solen requerir que s’insereixin números de telèfon en una fórmula específica d’11 dígits amb el codi del país i sense puntuació. Tanmateix, la gent sovint introdueix aquestes dades amb guions i punts. Aquí teniu una gran fórmula per eliminant tots els caràcters no numèrics a Excel. La fórmula revisa les dades de la cel·la 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))

Ara podeu copiar la columna resultant i utilitzar-la Edició> Enganxa valors per escriure sobre les dades amb el resultat formatat correctament.

Avalueu diversos camps amb un OR

Sovint eliminem els registres incomplets d’una importació. Els usuaris no s’adonen que no sempre heu d’escriure fórmules jeràrquiques complexes i que podeu escriure una sentència OR. En aquest exemple següent, vull comprovar si hi ha dades que falten en A2, B2, C2, D2 o E2. Si falten dades, tornaré un 0, en cas contrari, un 1. Això em permetrà ordenar les dades i suprimir els registres incomplets.

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

Retallar i concatenar camps

Si les vostres dades tenen camps de nom i cognom, però la vostra importació té un camp de nom complet, podeu concatenar els camps juntament amb la funció Concatenar de la funció Excel integrada, però assegureu-vos d’utilitzar TRIM per eliminar qualsevol espai buit abans o després text. Embolicem tot el camp amb TRIM en cas que un dels camps no tingui dades:

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

Comproveu si hi ha una adreça de correu electrònic vàlida

Una fórmula força senzilla que busca tant el @ com el. en una adreça de correu electrònic:

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

Extreu noms i cognoms

De vegades, el problema és el contrari. Les vostres dades tenen un camp de nom complet, però heu d’analitzar el nom i els cognoms. Aquestes fórmules busquen l’espai entre el nom i el cognom i agafen text quan sigui necessari. IT també gestiona si no hi ha cap cognom o si hi ha una entrada en blanc a A2.

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

I el cognom:

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

Limiteu el nombre de caràcters i afegiu ...

Heu volgut mai netejar les meta descripcions? Si voleu extreure contingut a Excel i retallar el contingut per utilitzar-lo en un camp Meta Description (de 150 a 160 caràcters), podeu fer-ho mitjançant aquesta fórmula de El meu punt. Trenca netament la descripció en un espai i després afegeix el ...:

=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)

Per descomptat, no es pretén que siguin exhaustius ... només algunes fórmules ràpides que us ajudaran a començar de forma ràpida. Quines altres fórmules us trobeu? Afegiu-los als comentaris i us donaré crèdit a mesura que actualitzi aquest article.

Què et sembla?

Aquest lloc utilitza Akismet per reduir el correu no desitjat. Esbrineu com es processa el vostre comentari.