Excelove formule za skupno čiščenje podatkov

Formule za čiščenje podatkov Excel

Publikacijo že leta uporabljam kot vir, da ne samo opišem, kako to storiti, ampak tudi, da si evidenco shranim pozneje! Danes smo imeli stranko, ki nam je predala datoteko s podatki o strankah, ki je bila katastrofa. Skoraj vsako polje je bilo napačno formatirano in; zato podatkov nismo mogli uvoziti. Čeprav obstaja nekaj odličnih dodatkov za Excel za čiščenje z Visual Basicom, zaženemo Office za Mac, ki ne podpira makrov. Namesto tega iščemo naravne formule za pomoč. Mislil sem, da bom nekatere od teh delil tukaj, da jih bodo drugi lahko uporabili.

Odstranite neštevilske znake

Sistemi pogosto zahtevajo, da se telefonske številke vstavijo v posebno 11-mestno formulo s kodo države in brez ločil. Ljudje pa te podatke pogosto vpišejo s pomišljaji in pikami. Tu je odlična formula za odstranjevanje vseh nenumeričnih znakov v Excelu. Formula pregleduje podatke v celici 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))

Zdaj lahko kopirate nastali stolpec in uporabite Uredi> Prilepi vrednosti za zapisovanje podatkov s pravilno oblikovanim rezultatom.

Ocenite več polj z ALI

Pogosto iz uvoza odstranimo nepopolne zapise. Uporabniki se ne zavedajo, da vam ni treba vedno pisati zapletenih hierarhičnih formul in da lahko namesto tega napišete stavek ALI. V spodnjem primeru želim preveriti A2, B2, C2, D2 ali E2, ali manjkajo podatki. Če manjkajo kateri koli podatki, vrnem 0, sicer pa 1. To mi bo omogočilo razvrščanje vrstnega reda podatkov in brisanje nepopolnih zapisov.

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

Obrežite in združite polja

Če imajo vaši podatki polji Ime in Priimek, uvoz pa polno ime, lahko polja lepo združite skupaj z vgrajeno funkcijo Excel Concatenate, vendar s TRIM odstranite prazne presledke pred ali za besedilo. Celotno polje zavijemo s TRIM, če eno od polj nima podatkov:

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

Preverite veljaven e-poštni naslov

Precej preprosta formula, ki išče tako @ kot. v elektronskem naslovu:

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

Izvleček imen in priimkov

Včasih je težava ravno obratna. Vaši podatki imajo polno ime, vendar morate razčleniti imena in priimke. Te formule iščejo presledek med imenom in priimkom ter po potrebi zajemajo besedilo. IT obravnava tudi, če ni priimka ali je v A2 prazen vnos.

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

In priimek:

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

Omejite število znakov in dodajte…

Ste kdaj želeli počistiti svoje meta opise? Če ste želeli vsebino povleči v Excel in jo nato obrezati za uporabo v polju Meta Description (150 do 160 znakov), lahko to storite s to formulo iz Moja točka. Opis čisto razbije v presledku in nato doda ...:

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

Seveda te niso namenjene izčrpnosti ... le nekaj hitrih formul, ki vam bodo pomagale začeti! Katere formule še uporabljate? Dodajte jih v komentarje in pri posodabljanju tega članka vam bom dodal kredit.

Kaj menite?

Ta stran uporablja Akismet za zmanjšanje nezaželene pošte. Preberite, kako se vaš komentar obravnava.