Vzorce programu Excel na čistenie bežných údajov

Vzorce na čistenie údajov programu Excel

Roky som používal túto publikáciu ako zdroj nielen na to, aby som opísal, ako robiť veci, ale aj na to, aby som si neskôr urobil záznam, ktorý by som si neskôr vyhľadal! Dnes sme mali klienta, ktorý nám odovzdal súbor s údajmi o zákazníkoch, ktorý bol katastrofou. Prakticky každé pole bolo nesprávne naformátované a; vo výsledku sa nám nepodarilo importovať údaje. Aj keď pre Excel existuje niekoľko skvelých doplnkov na čistenie pomocou jazyka Visual Basic, spúšťame Office pre Mac, ktorý nepodporuje makrá. Namiesto toho hľadáme priame vzorce, ktoré by nám pomohli. Myslel som, že tu zdieľam niektoré z nich, len aby ich mohli použiť ostatní.

Odstráňte nečíselné znaky

Systémy často vyžadujú, aby boli telefónne čísla vložené do konkrétneho 11-miestneho vzorca s kódom krajiny a bez interpunkcie. Ľudia však často zadávajú tieto údaje čiarkami a bodkami. Tu je skvelý vzorec pre odstránenie všetkých nečíselných znakov v programe Excel. Vzorec kontroluje údaje v bunke 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))

Teraz môžete skopírovať výsledný stĺpec a použiť Úpravy> Prilepiť hodnoty prepísať údaje so správne naformátovaným výsledkom.

Vyhodnoťte viac polí pomocou ALEBO

Z importu často čistíme neúplné záznamy. Používatelia si neuvedomujú, že nemusíte vždy písať zložité hierarchické vzorce a že namiesto nich môžete napísať príkaz OR. V tomto príklade nižšie chcem skontrolovať, či v údajoch A2, B2, C2, D2 alebo E2 chýbajú údaje. Ak nejaké údaje chýbajú, vrátim hodnotu 0, inak hodnotu 1. To mi umožní zoradiť údaje a odstrániť neúplné záznamy.

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

Orezanie a zreťazenie polí

Ak majú vaše údaje polia Meno a Priezvisko, ale váš import má pole pre celé meno, môžete tieto polia spojiť úhľadne pomocou zabudovanej funkcie Excel Function Concatenate, nezabudnite však na odstránenie všetkých prázdnych medzier pred alebo za text. Celé pole zabalíme do TRIM pre prípad, že jedno z polí nemá údaje:

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

Skontrolujte platnú e-mailovú adresu

Veľmi jednoduchý vzorec, ktorý vyhľadáva znaky @ aj. na e-mailovú adresu:

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

Extrahujte meno a priezvisko

Niekedy je problém opačný. Vaše údaje obsahujú pole s celým menom, musíte však analyzovať meno a priezvisko. Tieto vzorce hľadajú medzeru medzi menom a priezviskom a v prípade potreby uchopia text. IT tiež spracováva, ak nie je priezvisko alebo je v položke A2 prázdny záznam.

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

A priezvisko:

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

Obmedzte počet znakov a pridajte…

Chceli ste niekedy vyčistiť svoje metapopisy? Ak ste chceli stiahnuť obsah do programu Excel a potom ho orezať na použitie v poli Meta Description (150 až 160 znakov), môžete to urobiť pomocou tohto vzorca z Moje miesto. Čisto rozbije popis v medzere a potom pridá ...:

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

Samozrejme, tieto nie sú myslené komplexne ... iba niektoré rýchle vzorce, ktoré vám pomôžu naštartovať sa! Aké ďalšie vzorce podľa seba používaš? Pridajte ich do komentárov a pri aktualizácii tohto článku vám poskytnem uznanie.

Čo si myslíte?

Táto stránka používa Akismet na zníženie spamu. Zistite, ako sa spracúvajú údaje vašich komentárov.