Tavalised andmete puhastamise valemid Excelis

exceli valemid

Aastaid olen trükist kasutanud ressursina, et mitte ainult kirjeldada, kuidas asju teha, vaid ka selleks, et enda jaoks hiljem üles otsida! Täna oli meil klient, kes ulatas meile katastroofiks olnud kliendi andmefaili. Praktiliselt iga väli oli valesti vormistatud ja; seetõttu ei saanud me andmeid importida. Ehkki Exceli jaoks on Visual Basicu abil koristamiseks mõned suurepärased lisandmoodulid, käivitame Office for Maci, mis ei toeta makrosid. Selle asemel otsime abistamiseks sirgeid valemeid. Mõtlesin, et jagan mõnda neist siin lihtsalt selleks, et teised saaksid neid kasutada.

Eemaldage mitte-numbrilised märgid

Süsteemid nõuavad sageli telefoninumbrite sisestamist konkreetsesse 11-kohalisse valemisse riigikoodiga ja ilma kirjavahemärkideta. Kuid inimesed sisestavad need andmed sageli kriipsude ja punktidega. Siin on suurepärane valem kõigi mittemärkide eemaldamine Excelis. Valem vaatab lahtris A2 olevad andmed üle:

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

Nüüd saate saadud veeru kopeerida ja kasutada Redigeerimine> Kleebi väärtused õigesti vormindatud tulemusega andmete üle kirjutamiseks.

Hinnake OR-iga mitut välja

Tühjendame mittetäielikud kirjed sageli impordist. Kasutajad ei mõista, et te ei pea alati kirjutama keerukaid hierarhilisi valemeid ja selle asemel võite kirjutada OR-lause. Selles allpool toodud näites soovin kontrollida, kas A2, B2, C2, D2 või E2 puuduvad andmed. Andmete puudumisel tagastan 0, vastasel juhul 1. See võimaldab mul andmeid järjestada ja puudulikud kirjed kustutada.

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

Väljade kärpimine ja liitmine

Kui teie andmetel on väljad Ees- ja Perekonnanimi, kuid teie impordil on täisnimi, saate väljad omavahel liita, kasutades sisseehitatud Exceli funktsioonide liitmist, kuid enne või pärast tühja tühiku eemaldamiseks kasutage kindlasti TRIM-i. teksti. Mähitame kogu välja TRIM-iga juhul, kui ühel väljal pole andmeid:

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

Kontrollige kehtivat e-posti aadressi

Päris lihtne valem, mis otsib nii @ kui ka. e-posti aadressis:

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

Väljavõte ees- ja perekonnanimed

Mõnikord on probleem vastupidine. Teie andmetel on täisnimi, kuid peate sõeluma ees- ja perekonnanimed. Need valemid otsivad ruumi ees- ja perekonnanime vahel ning haaravad vajaduse korral teksti. IT tegeleb ka siis, kui perekonnanime pole või kui A2-s on tühi kirje.

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

Ja perekonnanimi:

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

Piirake märkide arvu ja lisage…

Kas soovite kunagi oma metakirjeldusi puhastada? Kui soovite tõmmata sisu Excelisse ja seejärel sisustada sisu kasutamiseks metakirjelduse väljal (150 kuni 160 tähemärki), saate seda teha selle valemi abil Minu koht. See rikub tühiku kirjelduse puhtalt ja lisab seejärel…:

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

Muidugi ei ole need mõeldud kõikehõlmavaks ... vaid mõned kiired valemid, mis aitavad teil alustada. Milliseid muid valemeid kasutate? Lisage need kommentaaridesse ja annan teile selle artikli värskendamisel au.

Mis sa arvad?

Sellel saidil kasutatakse rämpsposti vähendamiseks Akismetit. Vaadake, kuidas teie andmeid töödeldakse.