„Excel“ formulės bendram duomenų valymui

„Excel“ duomenų valymo formulės

Daugelį metų aš naudoju leidinį kaip šaltinį, norėdamas ne tik aprašyti, kaip reikia atlikti veiksmus, bet ir pats saugoti įrašą, kad galėčiau ieškoti vėliau! Šiandien mes turėjome klientą, kuris mums perdavė kliento duomenų bylą, kuri buvo nelaimė. Praktiškai kiekvienas laukas buvo netinkamai suformatuotas ir; todėl mums nepavyko importuoti duomenų. Nors yra keletas puikių priedų, skirtų „Excel“ atlikti valymą naudojant „Visual Basic“, vykdome „Office for Mac“, kuri nepalaiko makrokomandų. Vietoj to mes ieškome tiesių formulių, kurios padėtų. Maniau, kad čia pasidalysiu kai kuriais iš tų, kad kiti galėtų jais naudotis.

Pašalinkite ne skaitmeninius simbolius

Sistemos dažnai reikalauja, kad telefono numeriai būtų įterpiami į konkrečią 11 skaitmenų formulę su šalies kodu ir be skyrybos ženklų. Tačiau žmonės dažnai įveda šiuos duomenis su brūkšneliais ir taškais. Čia yra puiki formulė pašalinant visus ne skaitmeninius simbolius „Excel“. Formulė peržiūri duomenis A2 langelyje:

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

Dabar galite nukopijuoti gautą stulpelį ir naudoti Redaguoti> Įklijuoti vertes užrašyti duomenis su tinkamai suformatuotu rezultatu.

Įvertinkite kelis laukus su OR

Nepilnus įrašus dažnai pašaliname iš importo. Vartotojai nesuvokia, kad ne visada turite rašyti sudėtingas hierarchines formules ir kad vietoj to galite parašyti OR sakinį. Šiame pavyzdyje noriu patikrinti, ar trūksta duomenų A2, B2, C2, D2 ar E2. Jei trūksta kokių nors duomenų, grąžinsiu 0, kitaip - 1. Tai leis rūšiuoti duomenų tvarkymo tvarką ir ištrinti nepilnus įrašus.

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

Apkirpti ir sujungti laukus

Jei jūsų duomenyse yra laukai Vardas ir Pavardė, bet importuojant yra visas vardo laukas, galite tvarkingai susieti laukus naudodami integruotą „Excel“ funkcijų sąsają, tačiau būtinai naudokite TRIM, kad pašalintumėte visas tuščias vietas prieš arba po tekstas. Apvyniojame visą lauką su TRIM tuo atveju, jei viename iš laukų nėra duomenų:

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

Patikrinkite, ar yra tinkamas el. Pašto adresas

Gana paprasta formulė, kuri ieško ir @, ir. pašto adresu:

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

Išskleisti vardus ir pavardes

Kartais problema yra priešinga. Jūsų duomenys turi vardą ir lauką, tačiau turite išanalizuoti vardus ir pavardes. Šios formulės ieško tarpo tarp vardo ir pavardės ir prireikus griebia tekstą. IT taip pat tvarko, jei nėra pavardės arba A2 yra tuščias įrašas.

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

Ir pavardė:

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

Apriboti simbolių skaičių ir pridėti ...

Ar kada norėjote išvalyti savo metaaprašymus? Jei norėjote ištraukti turinį į „Excel“ ir tada apkarpyti turinį, skirtą naudoti „Meta Description“ lauke (nuo 150 iki 160 simbolių), galite tai padaryti naudodami šią formulę iš Mano taškas. Jis švariai sulaužo aprašą erdvėje ir prideda…:

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

Žinoma, tai nėra suprantama ... tik keletas greitų formulių, kurios padės jums pradėti! Kokias dar formules naudojate jūs? Pridėkite juos komentaruose ir aš suteiksiu jums kreditą, kai atnaujinsiu šį straipsnį.

Ką manote?

Ši svetainė naudoja "Akismet", kad sumažintų šlamštą. Sužinokite, kaip apdorojamas jūsų komentaras.