Excel vzorce pro běžné čištění dat
Po celá léta jsem tuto publikaci používal jako zdroj k popisu toho, jak věci dělat, a vedl jsem si záznamy, abych je mohl později vyhledat! Klient nám předal soubor zákaznických dat, což byla katastrofa. Prakticky každé pole bylo špatně naformátováno a v důsledku toho jsme nemohli importovat data. I když existuje několik skvělých doplňků pro Excel pro čištění pomocí jazyka Visual Basic, provozujeme Office pro Mac, který nepodporuje makra. Místo toho hledáme přímé vzorce, které nám pomohou. Myslel jsem, že některé z nich zde sdílím, abyste je mohli použít.
Odeberte nečíselné znaky
Systémy často vyžadují telefonní čísla vložená do specifického 11místného vzorce s kódem země a bez interpunkce. Lidé však často místo toho zadávají tato data s pomlčkami a tečkami. Zde je skvělý vzorec pro odstranění všech nečíselných znaků v aplikaci Excel. Vzorec kontroluje data v buňce 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))
Výsledný sloupec můžete zkopírovat a použít Úpravy> Vložit hodnoty přepsat data se správně naformátovaným výsledkem.
Vyhodnoťte více polí pomocí OR
Často odstraňujeme neúplné záznamy z importu. Uživatelé si neuvědomují, že nemusíte vždy psát složité hierarchické vzorce a že místo toho můžete napsat příkaz OR. Chci zkontrolovat A2, B2, C2, D2 nebo E2, zda v níže uvedeném příkladu chybí data. Pokud některá data chybí, vrátím 0; jinak 1. To mi umožní třídit data a mazat neúplné záznamy.
=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)
Ořezávání a zřetězení polí
Pokud vaše data obsahují pole Jméno a Příjmení, ale váš import obsahuje pole s celým jménem, můžete pole úhledně zřetězit dohromady pomocí vestavěné funkce Excel Concatenate, ale nezapomeňte použít TRIM k odstranění všech prázdných mezer před nebo za text. Pokud jedno z polí nemá data, obalíme celé pole pomocí TRIM:
=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))
Zkontrolujte platnou e-mailovou adresu
Docela jednoduchý vzorec, který hledá @ i . v e-mailové adrese (ne v standard RFC
):=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))
Extrahujte jméno a příjmení
Někdy je problém opačný. Vaše data obsahují pole pro celé jméno, ale musíte analyzovat jméno a příjmení. Tyto vzorce hledají mezeru mezi jménem a příjmením a v případě potřeby uchopí text. Poradí si také, zda v A2 není žádné příjmení nebo prázdný záznam.
=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))
A příjmení:
=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")
Omezte počet znaků a přidejte…
Chtěli jste někdy vyčistit své meta popisy? Pokud chcete načíst obsah do Excelu a poté jej oříznout pro použití v poli Meta Description (150 až 160 znaků), můžete to udělat pomocí tohoto vzorce. Čistě přeruší popis na mezeru a poté přidá …:
=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)
Samozřejmě, tyto nejsou zamýšleny jako vyčerpávající... jen několik rychlých vzorců, které vám pomohou začít! Jaké další vzorce používáte? Přidejte je do komentářů a já vám při aktualizaci tohoto článku dám pochvalu.