Formulat e zakonshme të pastrimit të të dhënave në Excel

formula të shkëlqyera

Për vite, e kam përdorur botimin si një burim jo vetëm për të përshkruar se si t'i bëj gjërat, por edhe për të mbajtur një regjistër për veten time për të kërkuar më vonë! Sot, ne kishim një klient që na dha një skedar të dhënash të klientit që ishte një katastrofë. Pothuajse çdo fushë ishte keqformatuar dhe; si rezultat, ne nuk ishim në gjendje të importonim të dhënat. Ndërsa ka disa shtesa të shkëlqyera për Excel për të bërë pastrimin duke përdorur Visual Basic, ne ekzekutojmë Office for Mac i cili nuk do të mbështesë makrot. Në vend të kësaj, ne kërkojmë formula të drejtpërdrejta për të ndihmuar. Mendova se do të ndaja disa nga ata këtu vetëm që të tjerët t'i përdorin ato.

Hiqni Karakteret Jo-Numerike

Sistemet shpesh kërkojnë që numrat e telefonit të futen në një formulë specifike, 11-shifrore me kodin e vendit dhe pa pikësim. Sidoqoftë, njerëzit shpesh i fusin këto të dhëna me ndërprerje dhe periudha. Këtu është një formulë e shkëlqyeshme për heqja e të gjithë karaktereve jo-numerike në Excel. Formula rishikon të dhënat në qelizën 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))

Tani mund të kopjoni kolonën që rezulton dhe ta përdorni Redakto> Ngjit vlerat për të shkruar mbi të dhënat me rezultatin e formatuar siç duhet.

Vlerësoni shumë fusha me një OR

Shpesh pastrojmë shënime jo të plota nga një import. Përdoruesit nuk e kuptojnë që ju nuk keni gjithmonë nevojë të shkruani formula komplekse hierarkike dhe që në vend të kësaj mund të shkruani një deklaratë OSE. Në këtë shembull më poshtë, unë dua të kontrolloj A2, B2, C2, D2 ose E2 për të dhëna që mungojnë. Nëse ndonjë e dhënë mungon, unë do të kthej një 0, përndryshe një 1. Kjo do të më lejojë të rendit renditjen e të dhënave dhe të fshij regjistrimet që nuk janë të plota.

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

Fushat e shkurtimit dhe bashkimit

Nëse të dhënat tuaja kanë fusha Emri dhe Mbiemri, por importi juaj ka një fushë me emër të plotë, ju mund të bashkoni fushat së bashku mjeshtërisht duke përdorur funksionin e bashkuar në Excel, por sigurohuni që të përdorni TRIM për të hequr çdo hapësirë ​​bosh para ose pas teksti. Ne mbështjellim të gjithë fushën me TRIM në rast se njëra nga fushat nuk ka të dhëna:

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

Kontrolloni për adresë të vlefshme emaili

Një formulë mjaft e thjeshtë që kërkon si për @ edhe për. në një adresë emaili:

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

Nxjerrni Emrat dhe Mbiemrat

Ndonjëherë, problemi është i kundërt. Të dhënat tuaja kanë një fushë me emër të plotë, por duhet të analizoni emrat dhe mbiemrat. Këto formula kërkojnë hapësirën midis emrit dhe mbiemrit dhe kapin tekstin kur është e nevojshme. IT gjithashtu merret nëse nuk ka mbiemër ose ka një hyrje të zbrazët në A2.

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

Dhe mbiemri:

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

Kufizoni numrin e personazheve dhe shtoni

A keni dashur ndonjëherë të pastroni përshkrimet tuaja meta? Nëse dëshironi të tërhiqni përmbajtje në Excel dhe më pas të shkurtoni përmbajtjen për përdorim në një fushë Meta Përshkrimi (150 deri 160 karaktere), mund ta bëni duke përdorur këtë formulë nga Spoti im. Prishet pastër përshkrimi në një hapësirë ​​dhe më pas shton…:

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

Sigurisht, këto nuk kanë për qëllim të jenë gjithëpërfshirëse… vetëm disa formula të shpejta për t'ju ndihmuar të merrni një fillim të shpejtë! Cilat formula të tjera i gjeni duke përdorur? Shtojini ato në komente dhe unë do t'ju jap merita ndërsa azhurnoj këtë artikull.

Çfarë mendoni ju?

Kjo faqe përdor Akismet për të reduktuar spamin. Mësoni se si përpunohet komenti juaj.