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

excel formule

Godinama se služim ovom publikacijom kao resursom ne samo da bih opisao kako se stvari rade, već i kako bih zadržao evidenciju da bih je potražio kasnije! Danas smo imali klijenta koji nam je predao datoteku s podacima o kupcima koja je bila katastrofa. Gotovo svako polje bilo je pogrešno formatirano i; kao rezultat, nismo uspjeli uvesti podatke. Iako postoje izvrsni dodaci za Excel za čišćenje pomoću Visual Basica, pokrećemo Office za Mac koji neće podržavati makronaredbe. Umjesto toga, tražimo ravne formule za pomoć. Mislio sam podijeliti neke od njih ovdje samo da bi ih drugi mogli koristiti.

Uklonite numeričke znakove

Sustavi često zahtijevaju da se telefonski brojevi ubace u određenu, 11-znamenkastu formulu s kodom države i bez interpunkcije. Međutim, ljudi često unose ove podatke crticama i točkama. Evo izvrsne formule za uklanjanje svih nenumeričkih znakova u Excelu. 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 zapisivanje podataka s pravilno oblikovanim rezultatom.

Procijenite više polja s 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 ILI. U ovom primjeru u nastavku želim provjeriti A2, B2, C2, D2 ili E2 da li nedostaju podaci. Ako nedostaju bilo kakvi podaci, vratit ću 0, u suprotnom 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 sadrže 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-adresu:

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

Izdvojite imena i prezimena

Ponekad je problem suprotan. Vaši podaci imaju polje s punim imenom, ali morate raščlaniti imena i prezimena. Ove formule traže prostor 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 ikad pož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 Spot. Č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 pronalazite u korištenju? Dodajte ih u komentare i dat ću vam priznanje dok ažuriram ovaj članak.

Što vi mislite?

Ova web stranica koristi Akismet za smanjenje neželjene pošte. Saznajte kako se podaci vašeg komentara obrađuju.