Fórmulas comúns de limpeza de datos en Excel

fórmulas excel

Hai anos que usei a publicación como un recurso para non só describir como facer as cousas, senón tamén para gardar un rexistro para que busque máis tarde. Hoxe tivemos un cliente que nos entregou un ficheiro de datos do cliente que foi un desastre. Practicamente todos os campos estaban mal formatados e; como resultado, non puidemos importar os datos. Aínda que hai algúns complementos excelentes para que Excel poida facer a limpeza usando Visual Basic, executamos Office para Mac que non admite macros. Pola contra, buscamos fórmulas directas para axudar. Pensei en compartir algúns destes aquí para que outros os poidan usar.

Elimina caracteres non numéricos

Os sistemas adoitan requirir a inserción de números de teléfono nunha fórmula específica de 11 díxitos co código do país e sen puntuación. Non obstante, a xente a miúdo introduce estes datos con trazos e puntos. Aquí tes unha gran fórmula para eliminando todos os caracteres non numéricos en Excel. A fórmula revisa os datos da cela 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))

Agora podes copiar a columna resultante e usala Editar> Pegar valores para escribir sobre os datos co resultado correctamente formatado.

Avalía varios campos cun OR

Moitas veces purgamos rexistros incompletos dunha importación. Os usuarios non se dan conta de que non sempre tes que escribir fórmulas xerárquicas complexas e que no seu lugar podes escribir unha instrución OR. Neste exemplo a continuación, quero comprobar a falta de datos A2, B2, C2, D2 ou E2. Se falta algún dato, devolverei un 0, se non, un 1. Isto permitirame ordenar os datos e eliminar os rexistros incompletos.

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

Recortar e concatenar campos

Se os teus datos teñen campos de nome e apelido, pero a túa importación ten un nome completo, podes concatenar os campos perfectamente usando a función concatenada integrada en Excel, pero asegúrate de usar TRIM para eliminar os espazos baleiros antes ou despois do texto. Envolvemos todo o campo con TRIM no caso de que un dos campos non teña datos:

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

Comprobe se hai un enderezo de correo electrónico válido

Unha fórmula bastante sinxela que busca tanto @ como. nun enderezo de correo electrónico:

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

Extraer nome e apelidos

Ás veces, o problema é o contrario. Os teus datos teñen un campo de nome completo, pero debes analizar o nome e apelidos. Estas fórmulas buscan o espazo entre o nome e o apelido e collen texto onde sexa necesario. TI tamén se encarga de se non hai apelido ou hai unha entrada en branco en A2.

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

E o apelido:

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

Limita o número de caracteres e engade ...

Algunha vez quixeches limpar as túas meta descricións? Se queres extraer contido en Excel e despois recortalo para empregalo nun campo Meta Description (150 a 160 caracteres), podes facelo usando esta fórmula de O meu Spot. Rompe a descrición nun espazo e despois engade o ...:

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

Por suposto, non se pretende que sexan completos ... só algunhas fórmulas rápidas para axudarche a comezar. Que outras fórmulas atopas usando? Engádeos nos comentarios e darémosche crédito ao actualizar este artigo.

¿Que pensas?

Este sitio usa Akismet para reducir o spam. Aprende a procesar os teus datos de comentarios.