Pridobivanje in čiščenje podatkov je prva in najpomembnejša naloga vsakega projekta za analizo podatkov. V mojih petletnih izkušnjah sem prejel podatke, ki so bili videti kot smeti, vendar sem jih z uporabo funkcij excel DESNO in LEVO lahko spremenil v smiselna poročila.
Čiščenje podatkov v Excelu je preprosto. Tukaj bom razložil, kako lahko v Excelu 2016 izvlečete določeno besedilo iz celice in pustite podatke o smeti.
Na zgornji sliki so mi na voljo podatki v Excelu. Ima Ime, Starost, in če je oseba ima zavarovanje ali ne (DA/N). Za uporabo teh podatkov moram izvleči podatke v različne stolpce. Vsak stolpec mora vsebovati samo eno vrsto vrednosti. Ko je to končano, lahko naredimo vrtilna poročila v Excelu, ki nam bodo bolje povedala podatke. Pa začnimo.
Excel DESNA funkcija za ekstrahiranje podatkov z desne strani besedila
Začnimo z najlažjim. V tem primeru imamo "Ali ima zavarovanje?" podatki na koncu niza. In to je samo en znak (Y ali N). Za pridobivanje teh podatkov bomo uporabili PRAVO formulo, ki jo ponuja Excel. Funkcija DESNO je na voljo v programih Excel 2016, Excel 2013, Excel 2010 in starejših različicah Excela.
Funkcija RIGHT v Excelu ima le dva argumenta. Najprej BESEDILO, nato pa ŠTEVILO ZNAKOV, ki jih želite izvleči iz desne v Excelu.
Splošna formula:
= DESNO (besedilo, število_znakov)
Tukaj imamo svoje besedilo v celici B3 in število znakov, ki jih želimo izvleči, je 1. To formulo samo zapišite v Excel v celico D3 ali kjer koli želite imeti te podatke.
= DESNO (B3,1)
Rezultat boste imeli v celici D3. samo kopirajte ali povlecite to formulo v spodnje celice.
Excel LEFT funkcija za ekstrahiranje podatkov z leve strani besedila
Zdaj pa se postavimo za starost. Preden razložim, naj vam pokažem formulo. Kopirajte to formulo v želene celice.
= LEVO (DESNO (B3,4), 2)
V zgornji formuli smo združili funkcije LEFT in DESNO, da dobimo naše specifično besedilo iz niza. Tako izgleda.
Zdaj pa razumejmo to šibko formulo.
Vsaka funkcija deluje od znotraj navzven. Začnimo torej z:
DESNO (B3,4): Preprosto izvleče 4 znake iz danega besedila. To nam daje 30_Y. Zakaj 4? Ker vemo, da je starost le eden od dveh znakov in imamo 1 "_" in 1 Y ali N.
LEVO (DESNO (B3,4), 2): Zdaj imamo besedilo, ki se začne pri starosti 2 znakov. Tu začne delovati funkcija LEFT. Vzame DESNO (B3,4) kot njegov besedilni argument in iz tega izvleče 2 znaka. To nam daje 30 za prvo celico. Kopirajte formulo v spodnje celice in dobili boste preostale podatke, kot je prikazano na zgornji sliki.
Splošna formula za LEVO:
= LEFT (besedilo, število_znakov)
Tako kot funkcija RIGHT v Excelu funkcija LEFT v Excelu vzame tudi dva enaka argumenta. Samo izvleče podatke iz leve strani podanega niza.
Excel LEFT in Right Funkcija za izvlečenje neznane dolžine besedila iz niza
Samo prilepite to formulo v želene celice, da izvlečete ime iz besedila v Excelu.
= LEVO (B3, LEN (B3) -5)
V zgornjih primerih smo vedeli natančno število besedil, ki jih je treba izvleči iz danega besedila. Zdaj pa ne vemo, kako dolgo je ime dolgo. Kaj je mogoče storiti?
Vemo, da imenu sledi natanko 5 znakov. 1 za vesolje, 2 za starost, 1 za »_« podčrtaj in eno za Y.
Če lahko dobimo vse število besedila in od tega odštejemo 5, bomo imeli natančno število besedila za ime.
Za iskanje dolžine besedila v Excelu lahko uporabimo funkcijo LEN. Funkcija LEN programa excel vrne število vključenih znakov.
Sintaksa:
= LEN (besedilo)
Torej LEN (B3) vrne 20.
LEN (B3) -5 vrne 15. In to je dolžina imena v celici B3
Končno LEVO (B3, LEN (B3) -5) nam daje Georgina Schalk kot rezultat. Ko kopirate formulo v spodnje celice, dobite vsa imena.
Zdaj lahko na podlagi izvlečenih podatkov s pomočjo vrtilnih tabel pripravite takšna poročila. Pove nam, da ima polovica naših subjektov zavarovanje, druga polovica pa ne.
LEVA in DESNA FUNKCIJA Excela je zelo zmogljiva in uporabna za čiščenje podatkov. V kombinaciji s funkcijo LEN, funkcijo FIND in funkcijo SUBSTITUTE v Excelu lahko izvlečejo kateri koli podniz v nizu v Excelu.
V prihodnosti bomo v Excelu videli bolj zapleteno pridobivanje podatkov. Za zdaj adijo. In ja, pustite svoje misli v spodnjem oddelku za komentarje. Z veseljem vam bom pomagal.