Pogrešno napisane reči, tvrdoglavi razmaci na kraju, neželjeni prefiksi, nepravilna slova i znakovi koji se ne štampaju ostavljaju loš prvi utisak. A to nije čak ni kompletna lista načina na koje vaši podaci mogu da se zaprljaju. Zasučite rukave. Vreme je za veliko veliko proleće čišćenje radnih listova pomoću programa Microsoft Excel.
Osnove čišćenja podataka
Nemate uvek kontrolu nad formatom i tipom podataka koje uvozite iz spoljnog izvora podataka, kao što je baza podataka, tekstualna datoteka ili veb stranica. Pre nego što možete da analizirate podatke, često morate da ih očistite. Srećom, Excel ima mnogo funkcija koje vam pomažu da podatke dobijete u željenom formatu. Ponekad je zadatak jednostavan i postoji specifična funkcija koja ga obavlja umesto vas. Na primer, lako možete da koristite proveru pravopisa za uklanjanje pogrešno napisanih reči u kolonama koje sadrže komentare ili opise. Odnosno, ako želite da uklonite duplirane redove, to možete brzo uraditi pomoću dijaloga " Uklanjanje duplikata ".
U drugim situacijama ćete možda morati da manipulišete nekim kolonama pomoću formule kako biste uvezene vrednosti pretvorili u nove. Na primer, ako želite da uklonite razmake na kraju, možete da napravite novu kolonu da biste očistili podatke tako što ćete koristiti formulu, popuniti novu kolonu, konvertovati formule te nove kolone u vrednosti, a zatim ukloniti originalnu kolonu.
Osnovni koraci za čišćenje podataka su sledeći:
Uvezite podatke iz spoljnog izvora podataka.
Napravite rezervnu kopiju originalnih podataka u zasebnoj radnoj svesci.
Uverite se da su podaci u tabelarnom formatu redova i kolona sa: sličnim podacima u svakoj koloni, vidljivim svim kolonama i redovima i bez praznih redova unutar opsega. Ako želite najbolje rezultate, koristite Excel tabelu.
Prvo uradite zadatke koji ne zahtevaju manipulisanje kolonama, kao što su provera pravopisa ili korišćenje dijaloga "Pronalaženje i zamena ".
Zatim uradite zadatke koji zahtevaju manipulisanje kolonama. Opšti koraci za manipulisanje kolonom su:
- Umetnite novu kolonu (B) pored originalne kolone (A) koju treba očistiti.
- Dodajte formulu koja će transformisati podatke na vrh nove kolone (B).
- Popunite formulu u novoj koloni (B). U Excel tabeli se automatski kreira izračunata kolona sa popunjenim vrednostima.
- Izaberite novu kolonu (B), kopirajte je i nalepite kao vrednosti u novu kolonu (B).
- Uklonite originalnu kolonu (A), čime se nova kolona konvertuje iz B u A.
Da biste periodično očistili isti izvor podataka, uzmite u obzir snimanje makroa ili pisanje koda da biste automatizovali ceo proces. Postoji i nekoliko spoljnih programskih dodataka koje su napisali nezavisni dobavljači, a koji su navedeni u odeljku " Nezavisni dobavljači ", a koje možete da razmotrite ako nemate vremena ili resursa da sami automatizujete proces.
| Dodatne informacije | Opis |
|---|---|
| Automatsko popunjavanje podataka u ćelijama radnog lista | Pokazuje kako da koristite komandu " Popuna ". |
|
Pravljenje i oblikovanje tabela Promena veličine tabele dodavanjem ili uklanjanjem redova i kolona Korišćenje izračunatih kolona u Excel tabeli |
Pokažite kako da kreirate Excel tabelu i dodate ili izbrišete kolone ili izračunate kolone. |
| Kreiranje makroa | Prikazuje nekoliko načina za automatizaciju zadataka koji se ponavljaju pomoću makroa. |
Provera pravopisa
Kontrolor pravopisa možete da koristite ne samo za pronalaženje pogrešno napisanih reči, već i za pronalaženje vrednosti koje se ne koriste dosledno, kao što su imena proizvoda ili preduzeća, tako što ćete dodati te vrednosti u prilagođeni rečnik.
| Dodatne informacije | Opis |
|---|---|
| Provera pravopisa i gramatike | Pokazuje kako da ispravite pogrešno napisane reči na radnom listu. |
| Korišćenje prilagođenih rečnika za dodavanje reči u kontrolor pravopisa | Objašnjava kako da koristite prilagođene rečnike. |
Uklanjanje dupliranih redova
Duplirani redovi su čest problem prilikom uvoza podataka. Dobra ideja je da prvo filtrirate u cilju pronalaženja jedinstvenih vrednosti da biste potvrdili da su rezultati ono što želite, a da pre nego što uklonite duplirane vrednosti.
| Dodatne informacije | Opis |
|---|---|
| Filtriranje u cilju pronalaženja jedinstvenih vrednosti i uklanjanje dupliranih vrednosti | Prikazuje dve veoma povezane procedure: kako da filtrirate jedinstvene redove i kako da uklonite duplirane redove. |
Pronalaženje i zamena teksta
Možda ćete želeti da uklonite uobičajenu nisku na početku, kao što je oznaka iza koje slede dvotačka i razmak ili sufiks, kao što je zagradska fraza na kraju niske koja je zastarela ili nepotrebna. To možete da uradite tako što ćete pronaći instance tog teksta, a zatim ga zameniti bez teksta ili drugog teksta.
| Dodatne informacije | Opis |
|---|---|
|
Provera da li ćelija sadrži tekst (ne razlikuje mala i velika slova) Provera da li ćelija sadrži tekst (razlikuje mala i velika slova) |
Pokazuje kako da koristite komandu "Pronađi " i nekoliko funkcija za pronalaženje teksta. |
| Uklanjanje znakova iz teksta | Pokazuje kako da koristite komandu Zameni i nekoliko funkcija za uklanjanje teksta. |
| Pronalaženje ili zamena teksta i brojeva na radnom listu | Prikaz načina korišćenja dijaloga "Pronalaženje i zamena ". |
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
To su funkcije koje možete da koristite da biste izvršili razne zadatke rukovanja niskom, kao što je pronalaženje i zamena podniske u okviru niske, izdvajanje delova niske ili utvrđivanje dužine niske. |
Promena veličine slova teksta
Ponekad tekst dolazi u mešanoj vreći, naročito kada je reč o tekstu. Pomoću neke od tri funkcije Case možete da konvertujete tekst u mala slova, kao što su e-adrese, velika slova, kao što su kodovi proizvoda, ili normalna slova, kao što su imena ili naslovi knjiga.
| Dodatne informacije | Opis |
|---|---|
| Promena malih i velikih slova | Pokazuje kako da koristite tri funkcije Case. |
| LOWER | Pretvara sva velika slova iz tekstualne niske u mala. |
| PROPER | Ispisuje velikim slovom prvo slovo u tekstualnoj niski kao i sva ostala slova u tekstu koja su napisana posle znakova koji nisu slova. Pretvara sva ostala slova u mala. |
| UPPER | Pretvara tekst u velika slova. |
Uklanjanje razmaka i znakova koji se neće štampati iz teksta
Ponekad tekstualne vrednosti sadrže znakove za početak, kraj ili više ugrađenih razmaka (vrednosti Unikod skupova znakova 32 i 160) ili znakove koji se neće štampati (vrednosti Unikod skupa znakova od 0 do 31, 127, 129, 141, 143, 144 i 157). Ti znakovi ponekad mogu da dovedu do neočekivanih rezultata kada sortirate, filtrirate ili pretražujete. Na primer, u spoljnom izvoru podataka korisnici mogu da naprave tipografske greške tako što će nenamerno dodati dodatne znakove za razmak ili uvezeni tekstualni podaci iz spoljnih izvora mogu da sadrže znakove koji se ne štampaju i koji su ugrađeni u tekst. Budući da ove znakove nije lako primetiti, možda će biti teško razumeti neočekivane rezultate. Da biste uklonili ove neželjene znakove, možete koristiti kombinaciju funkcija TRIM, CLEAN i SUBSTITUTE.
| Dodatne informacije | Opis |
|---|---|
| KÔD | Daje numerički kôd za prvi znak u tekstualnoj niski. |
| OČISTI | Uklanja prva 32 znaka koji se neće štampati u 7-bitnom ASCII kodu (vrednosti od 0 do 31) iz teksta. |
| TRIM | Uklanja 7-bitni ASCII razmak (vrednost 32) iz teksta. |
| SUBSTITUTE | Funkciju SUBSTITUTE možete koristiti da biste zamenili Unikod znakove većih vrednosti (vrednosti 127, 129, 141, 143, 144, 157 i 160) sa 7-bitnim ASCII znakovima za koje su dizajnirane funkcije TRIM i CLEAN. |
Fiksiranje brojeva i znakova za brojeve
Postoje dva glavna problema sa brojevima koji mogu zahtevati da očistite podatke: broj je slučajno uvezen kao tekst, a negativni znak treba da se promeni u standard za organizaciju.
| Dodatne informacije | Opis |
|---|---|
| Konvertovanje brojeva uskladištenih kao tekst u brojeve | Pokazuje kako da konvertujete brojeve koji su oblikovani i uskladišteni u ćelijama kao tekst, što može dovesti do problema sa izračunavanjem ili dati zbunjujuće redoslede sortiranja u oblikovanje brojeva. |
| DOLLAR | Pretvara broj u tekst i primenjuje simbol odgovarajuće valute. |
| Tekstualna poruka | Konvertuje vrednost u tekst u određenom formatu broja. |
| POPRAVLJENO | Zaokružuje broj na određeni broj decimala, oblikuje broj u decimalni format korišćenjem tačke i i zareza i daje rezultat u obliku teksta. |
| VREDNOST | Pretvara tekstualnu nisku koja predstavlja broj u brojnu vrednost. |
Fiksiranje datuma i vremena
Budući da postoji veliki broj različitih formata datuma i pošto ovi formati mogu da se mešaju sa kodovima numerisanih delova ili drugim niskama koje sadrže kose crte ili crtice, datume i vremena često treba da se konvertuju i ponovo oblikuju.
| Dodatne informacije | Opis |
|---|---|
| Promena datumskog sistema, formata ili dvocifrenog tumačenja godine | Opisuje kako datumski sistem funkcioniše u programu Office Excel. |
| Konvertovanje vremena | Pokazuje kako da se konvertuju između različitih vremenskih jedinica. |
| Konvertovanje datuma uskladištenih kao tekst u datume | Pokazuje kako da konvertujete datume koji su oblikovani i uskladišteni u ćelijama kao tekst, što može dovesti do problema sa izračunavanjem ili dati zbunjujuće redoslede sortiranja u format datuma. |
| DATUM | Daje sekvencijalni redni broj koji predstavlja određeni datum. Ako je pre unošenja funkcije oblikovanje ćelije bilo podešeno na opciju Opšti format, rezultat će biti u formatu datuma. |
| DATEVALUE | Konvertuje datum predstavljen tekstom u redni broj. |
| TIME | Daje decimalni broj koji odgovara određenom vremenu. Ako je pre unošenja funkcije oblikovanje ćelije bilo podešeno na opciju Opšti format, rezultat će biti u formatu datuma. |
| TIMEVALUE | Pretvara vreme predstavljeno u obliku tekstualne niske u decimalni broj. Decimalni broj je neka vrednost od 0 (nule) do 0,99999999, koja predstavlja vreme od 0:00:00 do 23:59:59. |
Spajanje i razdvajanje kolona
Uobičajen zadatak nakon uvoza podataka iz spoljnog izvora podataka jeste objedinjavanje dve ili više kolona u jednu ili razdvajanje jedne kolone na dve ili više kolona. Na primer, možda ćete želeti da razdelite kolonu koja sadrži puno ime na ime i prezime. Možda ćete želeti da razdelite kolonu koja sadrži polje adrese u odvojene kolone ulica, grad, region i poštanski broj. Može biti i obrnuto. Možda ćete želeti da objedinite kolone "Ime" i "Prezime" u koloni "Ime i prezime" ili da kombinujete zasebne kolone sa adresama u jednu kolonu. Dodatne uobičajene vrednosti koje zahtevaju objedinjavanje u jednu kolonu ili podelu na više kolona obuhvataju šifre proizvoda, putanje datoteka i adrese internet protokola (IP).
| Dodatne informacije | Opis |
|---|---|
|
Kombinacija imena i prezimena Kombinovanje teksta i brojeva Kombinovanje teksta sa datumom ili vremenom Kombinovanje dve ili više kolona korišćenjem funkcije |
Prikažite tipične primere kombinovanja vrednosti iz dve ili više kolona. |
| Razdeljivanje teksta u kolone pomoću čarobnjaka za pretvaranje teksta u kolone | Pokazuje kako da koristite ovaj čarobnjak da biste razdelili kolone na osnovu različitih uobičajenih znakova za razgraničavanje. |
| Razdeljivanje teksta u kolone pomoću funkcija | Pokazuje kako se koriste funkcije LEFT, MID, RIGHT, SEARCH i LEN za razdeljivanje kolone sa imenom na dve ili više kolona. |
| Kombinovanje ili razdeljivanje sadržaja ćelija | Pokazuje kako se koristi funkcija CONCATENATE, operator & (ampersand) i čarobnjak za konvertovanje teksta u kolone. |
| Objedinjavanje ćelija ili razdvajanje objedinjenih ćelija | Pokazuje kako da koristite komande "Objedini ćelije", "Objedini popreč" i "Objedini i centriraj ". |
| CONCATENATE | Spaja dve ili više niski teksta u jednu tekstualnu nisku. |
Transforming and rearrange columns and rows
Većina funkcija za analizu i oblikovanje u programu Office Excel pretpostavlja da podaci postoje u jednoj, ravnoj dvodimenzionalnoj tabeli. Ponekad ćete možda želeti da redovi postanu kolone, a kolone redovi. U drugim situacijama, podaci čak nisu ni strukturirani u tabelarnom formatu, a vama je potreban način da transformišete podatke iz netabelarnog u tabelarni format.
| Dodatne informacije | Opis |
|---|---|
| TRANSPOSE | Daje vertikalni opseg ćelija kao horizontalni opseg ili obrnuto. |
Reconciling table data by joining or match
Povremeno administratori baza podataka koriste Office Excel da bi pronašli i ispravili greške koje se podudaraju kada su spojene dve ili više tabela. To može da podrazumeva usklađivanje dve tabele iz različitih radnih listova, na primer, da biste videli sve zapise u obe tabele ili da biste uporedili tabele i pronašli redove koji se ne podudaraju.
| Dodatne informacije | Opis |
|---|---|
| Pronalaženje vrednosti na listi podataka | Prikazuje uobičajene načine za pronalaženje podataka pomoću funkcija pronalaženja. |
| PRONALAŽENJE | Daje vrednost iz opsega od jednog reda ili jedne kolone ili iz niza. Funkcija LOOKUP ima dva sintaksička oblika: vektorski oblik i oblik niza. |
| HLOOKUP | Traži vrednost u gornjem redu tabele ili nizu vrednosti, a zatim je vraća u istoj koloni, iz reda koji navedete u tabeli ili nizu. |
| VLOOKUP | Traži vrednost u prvoj koloni niza tabele i vraća vrednost u istom redu iz druge kolone u nizu tabele. |
| INDEKS | Daje vrednost ili referencu na vrednost iz tabele ili opsega. Postoje dva oblika funkcije INDEX: oblik niza i oblik reference. |
| MATCH | Daje relativni položaj stavke u nizu koji se podudara sa određenom vrednošću u navedenom redosledu. Koristite funkciju MATCH umesto neke od funkcija LOOKUP kada vam je potreban položaj stavke, a ne sama stavka. |
| OFFSET | Daje referencu na opseg koji predstavlja precizirani broj redova i kolona iz ćelije ili opsega ćelija. Referenca koja se dobija može biti pojedinačna ćelija ili opseg ćelija. Moguće je precizirati broj redova i kolona koji će biti dobijeni. |
Nezavisni dobavljači
Sledi delimična lista nezavisnih dobavljača koji imaju proizvode koji se koriste za čišćenje podataka na razne načine.
Napomena
Microsoft ne pruža podršku za proizvode nezavisnih proizvođača.