Uobičajene formule za čišćenje podataka u Excelu

excel formule

Godinama sam koristio ovu publikaciju kao resurs kako bih ne samo opisao kako se to radi, već i kako bih zadržao evidenciju da bih je potražio kasnije! Danas smo imali klijenta koji nam je predao datoteku podataka o kupcima koja je bila katastrofa. Praktično svako polje bilo je pogrešno formatirano i; kao rezultat, nismo uspjeli uvesti podatke. Iako postoje neki sjajni dodaci za Excel za čišćenje pomoću Visual Basic-a, mi pokrećemo Office za Mac koji neće podržavati makronaredbe. Umjesto toga, tražimo ravne formule za pomoć. Mislio sam da neke od njih podijelim ovdje samo da bi ih drugi mogli koristiti.

Uklonite numeričke znakove

Sistemi često zahtijevaju da se brojevi telefona ubace u određenu, 11-cifrenu formulu sa pozivnim brojem države i bez interpunkcije. Međutim, ljudi često unose ove podatke crticama i tačkama. Evo odlične formule za uklanjanje svih nenumeričkih znakova u programu Excel. Formula pregledava podatke u ćeliji 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))

Sada možete kopirati rezultirajući stupac i koristiti Uredi> Zalijepi vrijednosti za pisanje podataka s pravilno formatiranim rezultatom.

Procijenite više polja sa ILI

Često uklanjamo nepotpune zapise iz uvoza. Korisnici ne shvaćaju da ne morate uvijek pisati složene hijerarhijske formule i da umjesto toga možete napisati izraz OR. U ovom donjem primjeru želim provjeriti A2, B2, C2, D2 ili E2 da li nedostaju podaci. Ako nedostaju neki podaci, vratit ću 0, inače 1. To će mi omogućiti sortiranje podataka i brisanje nepotpunih zapisa.

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

Obrežite i spojite polja

Ako vaši podaci imaju polja Ime i Prezime, ali vaš uvoz ima polje punog imena, polja možete uredno povezati pomoću ugrađene funkcije Excel Concatenate, ali svakako upotrijebite TRIM za uklanjanje praznih prostora prije ili poslije tekst. Obmotavamo cijelo polje TRIM-om u slučaju da jedno od polja nema podatke:

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

Provjerite valjanost adrese e-pošte

Prilično jednostavna formula koja traži i @ i. na e-mail adresu:

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

Izdvojite imena i prezimena

Problem je ponekad suprotan. Vaši podaci imaju polje s punim imenom, ali morate raščlaniti imena i prezimena. Ove formule traže razmak između imena i prezimena i po potrebi dohvaćaju tekst. IT obrađuje i ako nema prezimena ili ako je u A2 prazan unos.

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

I prezime:

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

Ograničite broj znakova i dodajte…

Jeste li ikada željeli očistiti svoje meta opise? Ako ste željeli povući sadržaj u Excel, a zatim izrezati sadržaj za upotrebu u polju Meta opis (150 do 160 znakova), to možete učiniti pomoću ove formule iz Moja tačka. Čisto razbija opis u razmaku, a zatim dodaje ...:

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

Naravno, nisu zamišljeni kao sveobuhvatni ... samo neke brze formule koje će vam pomoći da započnete! Koje druge formule koristite? Dodajte ih u komentare i pripazit ću vam dok ažuriram ovaj članak.

Šta ti misliš?

Ova stranica koristi Akismet kako bi smanjila neželjenu poštu. Saznajte kako se podaci vašeg komentara obrađuju.