Pogrešno napisane riječi, stalno dodavanje bespotrebnih razmaka, neželjeni prefiksi, pogrešni padeži i znakovi koji se ne mogu ispisati ostavljaju loš prvi dojam. A to čak i nije čitav popis načina na koje možete onečistiti svoje podatke. Zasučite rukave. Vrijeme je za veliko proljetno čišćenje radnih listova u programu Microsoft Excel.
Osnove čišćenja podataka
Kada uvozite podatke iz vanjskih izvora, kao što su baze podataka, tekstne datoteke ili web-stranice, nemate uvijek kontrolu nad oblikom i vrstom tih podataka. Da biste mogli analizirati podatke, često ih je prvo potrebno pročistiti. Excel, srećom, sadrži brojne značajke koje vam mogu olakšati oblikovanje podataka baš onako kako želite. Zadatak je u nekim slučajevima jednostavan i postoji konkretna značajka koja će ga obaviti umjesto vas. Primjerice, pomoću provjere pravopisa možete jednostavno pročistiti pogreške u pisanju u stupcima koji sadrže komentare ili opise. Isto tako, ako želite ukloniti dvostruke retke, to možete brzinsku učiniti putem dijaloškog okvira Uklanjanje duplikata.
U nekim drugim slučajevima potrebno je manipulirati jednim ili većim brojem stupaca pomoću formula da bi se uvezene vrijednosti pretvorile u nove. Tako, primjerice, ako želite ukloniti završne razmake, možete stvoriti novi stupac da biste očistili podatke pomoću formule, ispunjavajući novi stupac i pretvarajući formule tog novog stupca u vrijednosti te zatim uklanjajući izvorni stupac.
U nastavku su osnovni koraci čišćenja podataka:
Uvezite podatke iz vanjskog podatkovnog izvora.
Stvorite sigurnosnu kopiju izvornih podataka u zasebnoj radnoj knjizi.
Provjerite jesu li podaci u tabličnom obliku redaka i stupaca sa sličnim podacima u svakom stupcu, sa svim vidljivim stupcima i recima te bez praznih redaka u rasponu. Za najbolje rezultate poslužite se tablicom programa Excel.
Prvo obavite zadatke za koje nije potrebna manipulacija stupcima, kao što je provjera pravopisa ili zamjena pomoću dijaloškog okvira Traži i zamijeni.
Potom obavite one postupke za koje je potrebna manipulacija stupcima. Općeniti koraci za manipulaciju stupcima:
- Umetnite novi stupac (B) uz izvorni stupac (A) kojemu je potrebno čišćenje.
- Formulu s pomoću koje će se podaci promijeniti dodajte na vrh novoga stupca (b).
- Unesite formulu u novi stupac (B) prema dolje. U tablici programa Excel izračunati stupac automatski se stvara s vrijednostima koje su unesene prema dolje.
- Odaberite novi stupac (B), kopirajte ga, a zatim zalijepiti kao vrijednosti u novi stupac (B).
- Uklonite izvorni stupac (A), čime se novi stupac B pretvara u stupac A.
Razmislite o snimanju makronaredbe ili pisanju koda kojim bi se automatiziralo povremeno čišćenje istog podatkovnog izvora. Postoji i nekoliko vanjskih dodataka koje su napisali proizvođači treće strane, a koji su navedeni u rubrici Davatelji treće strane i kojima se možete poslužiti ako nemate vremena ni resursa da sami automatizirate proces.
| Dodatne informacije | Opis |
|---|---|
| Automatsko ispunjavanje ćelija radnog lista podacima | Ovdje se pokazuje kako se koristi naredba Ispuna. |
|
Stvaranje i oblikovanje tablica Promjena veličine tablice dodavanjem ili uklanjanjem redaka i stupaca Korištenje izračunatih stupaca u tablici programa Excel |
Ovdje se pokazuje kako se stvara tablica u programu Excel te kako se dodaju i brišu stupci ili izračunati stupci. |
| Stvaranje makronaredbe | Ovdje se prikazuje nekoliko načina automatiziranja ponavljajućih zadataka pomoću makronaredbi. |
Provjera pravopisa
Provjerom pravopisa ne pronalaze se samo pogrešno napisane riječi, nego i vrijednosti koje se ne koriste dosljedno, kao što su, primjerice, nazivi proizvoda ili tvrtki. To se čini dodavanjem tih vrijednosti u prilagođeni rječnik.
| Dodatne informacije | Opis |
|---|---|
| Provjera pravopisa i gramatike | Ovdje se pokazuje kako se ispravljaju pogrešno napisane riječi na radnom listu. |
| Korištenje prilagođenih rječnika za dodavanje riječi u alat za provjeru pravopisa | Ovdje se objašnjava kako se koriste prilagođeni rječnici. |
Uklanjanje dvostrukih redaka
Dvostruki reci uobičajeni su problem prilikom uvoza podataka. Bilo bi dobro prvo filtrirati jedinstvene vrijednosti te tako prije uklanjanja dvostrukih vrijednosti potvrditi da su rezultati onakvi kakve želite.
| Dodatne informacije | Opis |
|---|---|
| Filtriranje jedinstvenih vrijednosti i uklanjanje duplikata vrijednosti | Ovdje se pokazuju dva bliska postupka: filtriranje jedinstvenih redaka i uklanjanje dvostrukih. |
Traženje i zamjena teksta
Bilo bi dobro ukloniti zajednički vodeći niz, kao što je oznaka nakon koje slijedi dvotočka i razmak ili sufiks, kao što je izraz u zgradi na kraju niza, a koji je zastario ili nije potreban. To se može učiniti tako da pronađete instance toga teksta te ga zamijenite prazninom ili nekim drugim tekstom.
| Dodatne informacije | Opis |
|---|---|
|
Provjera sadrži li ćelija neki tekst (razlikuju se velika i mala slova) Provjera sadrži li ćelija tekst (razlikuju se velika i mala slova) |
Ovdje se pokazuje kako se koristi naredba Pronađi i nekoliko funkcija pronalaženja teksta. |
| Uklanjanje znakova iz teksta | Ovdje se pokazuje kako se koristi naredba Zamijeni i nekoliko funkcija uklanjanja teksta. |
| Traženje i zamjena teksta i brojeva u radnom listu | Ovdje se pokazuje kako se koriste dijaloški okviri Traženje i Zamjena. |
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
To su funkcije koje možete upotrijebiti za različite postupke manipulacijama nizovima, kao što je pronalaženje i zamjena podniza u nizu, izdvajanje dijelova niza ili utvrđivanje duljine niza. |
Promjena veličine slova teksta
Tekst je ponekad napisan miješano malim i velikim slovima. Korištenje jedne ili više od tri funkcije velikih i malih slova, možete tekst promijeniti u sva mala slova, kao u slučaju adresa e-pošte, u sva velika slova, kao u slučaju šifri proizvoda ili u velika početna slova, kao što je u slučaju naziva i imena.
| Dodatne informacije | Opis |
|---|---|
| Promjena veličine slova teksta | Ovdje se pokazuje kako se koriste tri funkcije veličine slova. |
| LOWER | Pretvara sva velika slova tekstnog niza, u mala. |
| PROPER | Pretvara u veliko slovo prvo slovo u tekstnom nizu i sva ostala slova u tekstu koja slijede iza znaka koji nije slovo. Sva ostala slova pretvara u kurent. |
| UPPER | Slova teksta pretvara u velika slova. |
Uklanjanje razmaka i neispisivih znakova iz teksta
Tekstne vrijednosti katkad sadrže početne, završne ili višestruke ugrađene znakove razmaka (vrijednosti skupa znakova Unicode 32 i 160) ili pak neispisive znakove (vrijednosti skupa znakova Unicode od 0 do 31, 127, 129, 141, 143, 144 i 157). Zbog tih znakova sortiranje, filtriranje i traženje daju neočekivane rezultate. Tako, primjerice, korisnici u vanjskom podatkovnom izvoru mogu napraviti pogreške u tipkanju nenamjerno dodajući dodatne znakove razmaka ili uvezeni tekstni podaci iz vanjskih izvora mogu sadržavati neispisive znakove koji su ugrađeni u tekst. Budući da je takve znakove teško primijetiti, neočekivane je rezultate teško razumjeti. Da biste uklonili te neželjene znakove, možete se poslužiti kombinacijom funkcija TRIM, CLEAN i SUBSTITUTE.
| Dodatne informacije | Opis |
|---|---|
| CODE | Vraća brojčani kod za prvi znak u tekstnom nizu. |
| CLEAN | Iz teksta uklanja prva 32 neispisiva znaka u 7-bitnom ASCII kodu (vrijednosti od 0 do 31). |
| TRIM | Iz teksta uklanja 7-bitne ASCII znakove razmaka (vrijednost 32). |
| SUBSTITUTE | Funkcijom SUBSTITUTE možete se poslužiti za zamjenu veće vrijednosti Unicode znakova (vrijednosti 127, 129, 141, 143, 144, 157 i 160) 7-bitnim ASCII znakovima za koje su namijenjene funkcije TRIM i CLEAN. |
Popravljanje brojeva i brojčanih znakova
Postoje dva glavna problema s brojevima zbog kojih će možda biti potrebno čišćenje podataka: broj je nenamjerno uvezen kao tekst i negativni predznak radi organizacije se mora promijeniti u standardni.
| Dodatne informacije | Opis |
|---|---|
| Pretvorba brojeva spremljenih kao tekst u brojeve | Ovdje se pokazuje kako pretvoriti brojeve koji su oblikovani i pohranjeni u ćelije kao tekst, što može izazvati probleme s izračunom ili dati zbunjujuće redoslijede pri sortiranju, u brojčani oblik. |
| DOLLAR | Pretvara broj u oblik teksta i dodaje znak valute. |
| TEXT | Pretvara vrijednost u tekst u određenom brojčanom obliku. |
| FIXED | Zaokružuje broj na zadani broj decimalnih mjesta, oblikuje broj u decimalnom obliku pomoću točki i zareza, te prikazuje rezultat kao tekst. |
| VALUE | Pretvara tekstni niz, koji predstavlja broj, u broj. |
Popravljanje datuma i vremena
Budući da postoji mnogo različitih oblika datuma i s obzirom na to da se ti oblici mogu zamijeniti za numerirane šifre dijelova ili drugih nizova koji sadrže znakove kose crte ili crtice, datume i vremena često je potrebno pretvoriti i preoblikovati.
| Dodatne informacije | Opis |
|---|---|
| Mijenjanje sustava i oblika datuma ili tumačenja godine u dvije znamenke | Ovdje se opisuje kako u programu Office Excel funkcionira sustav datuma. |
| Pretvaranje vremena | Ovdje se pokazuje kako se različite vremenske jedinice pretvaraju jedna u drugu. |
| Pretvaranje datuma pohranjenih kao tekst u datume | Ovdje se pokazuje kako pretvoriti datume koji su oblikovani i pohranjeni u ćelije kao tekst, što može izazvati probleme s izračunom ili dati zbunjujuće redoslijede pri sortiranju, u oblik datuma. |
| DATE | Vraća serijski broj iz niza koji predstavlja određeni datum. Ako je prije unosa funkcije oblik ćelije bio Općenito, rezultat se oblikuje kao datum. |
| DATEVALUE | Pretvara datum iz tekstnog oblika u serijski broj. |
| TIME | Prikazuje decimalni broj zadanog vremena. Ako je oblikovanje ćelije prije unosa funkcije bilo Općenito, rezultat će biti oblikovan kao datum. |
| TIMEVALUE | Prikazuje decimalni broj vremena koje je predstavljeno tekstnim nizom. Decimalni je broj vrijednost u rasponu od 0 do 0,99999999, što predstavlja vrijeme od 0:00:00 do 23:59:59. |
Spajanje i razdvajanje ćelija
Jedan od uobičajenih zadataka nakon uvoza podataka iz vanjskog podatkovnog izvora jest spajanje dva ili više stupaca u jedan ili razdvajanje jednog stupca u dva ili više. Tako, primjerice, možda poželite podijeliti stupac koji sadrži ime i u prezime u jedan za ime i drugi za prezime. Isto tako, možda želite stupac koji sadrži polje adrese u zasebne stupce za ulicu, grad, županiju i poštanski broj. Može vrijediti i obrnuto. Možda stupac za ime i stupac za prezime želite spojiti u jedan stupac za ime i prezime, odnosno, možda želite zasebne stupce s dijelovima adrese spojiti u jedan. Još neke uobičajene vrijednosti za koje je ponekad potrebno spajanje u jedan stupac ili razdvajanje u zasebne su, primjerice, šifre proizvoda, putovi datoteka i adrese internetskog protokola (IP).
| Dodatne informacije | Opis |
|---|---|
|
Spajanje imena i prezimena Kombiniranje teksta i brojeva Kombiniranje teksta s datumom ili vremenom Kombiniranje dvaju ili više stupaca pomoću funkcije |
Ovdje se pokazuju najčešći primjeri kombiniranja vrijednosti iz dva ili više stupca. |
| Razdvajanje teksta u različite stupce pomoću čarobnjaka za pretvaranje teksta u stupce | Ovdje se pokazuje kako se koristi ovaj čarobnjak za razdvajanje stupaca na temelju različitih uobičajenih graničnika. |
| Razdvajanje teksta u različite stupce pomoću funkcija | Ovdje se pokazuje kako se koriste funkcije LEFT, MID, RIGHT, SEARCH i LEN za razdvajanje stupca imena u dva ili više stupaca. |
| Spajanje ili razdvajanje sadržaja ćelija | Ovdje se pokazuje kako se koristi funkcija CONCATENATE, operator & (ampersand) i čarobnjak za pretvaranje teksta u stupce. |
| Spajanje ćelija i podjela spojenih ćelija | Ovdje se pokazuje kako se koriste naredbe Spoji ćelije, Spoji kroz i Spoji i centriraj. |
| CONCATENATE | Spaja dva tekstna niza ili više njih u jedan tekstni niz. |
Pretvorba i promjena rasporeda stupaca i redaka
Većina značajki za analizu i oblikovanje u programu Office Excel pretpostavlja da su podaci sadržani u jednoj plošnoj, dvodimenzionalnoj tablici. U nekim ćete slučajevima možda poželjeti pretvoriti retke u stupce, a stupce u retke. U nekim drugim slučajevima podaci čak i nisu strukturirani u tablični oblik pa će vam biti potreban način pretvaranja podataka iz netabličnog u tablični oblik.
| Dodatne informacije | Opis |
|---|---|
| TRANSPOSE | Okomiti raspon ćelija vraća kao vodoravni ili obratno. |
Usklađivanje tabličnih podataka pridruživanjem ili podudaranjem
Administratori baza podataka povremeno koriste Office Excel za pronalaženje i ispravljanje pogrešaka podudaranja prilikom spajanja dviju ili više tablica. To može značiti usklađivanje dviju tablica s različitih radnih listova, primjerice, zato da bi se pregledali svi zapisi iz obje tablice ili zato da bi se usporedile tablice i pronašli reci koji se ne podudaraju.
| Dodatne informacije | Opis |
|---|---|
| Traženje vrijednosti na popisu podataka | Ovdje se pokazuju uobičajeni načini traženja podataka pomoću funkcije lookup. |
| LOOKUP | Vraća vrijednost iz raspona od jednog retka ili jednog stupca ili iz polja. Funkcija LOOKUP ima dva oblika sintakse: vektorski oblik i oblik polja. |
| HLOOKUP | Traži vrijednost u gornjem retku tablice ili polja s vrijednostima i zatim vraća vrijednost u istom stupcu iz retka koji navedete u tablici ili polju. |
| VLOOKUP | Traži vrijednost u prvom stupcu polja tablice i vraća vrijednost u istom retku iz drugog stupca u polju tablice. |
| INDEX | Prikazuje vrijednost ili referencu prema vrijednosti iz tablice ili raspona. Postoje dva oblika funkcije INDEX: oblik polja i oblik reference. |
| MATCH | Vraća redni broj neke stavke u polju koja odgovara navedenoj vrijednosti određenim redoslijedom. Funkcijom MATCH umjesto funkcija LOOKUP poslužite se kada vam je potreban položaj stavke u rasponu, a ne sama stavka. |
| OFFSET | Vraća referencu na raspon koji je udaljen navedeni broj redaka i stupaca od ćelije ili raspona ćelija. Vraćena referenca može biti jedna ćelija ili raspon ćelija. Možete navesti broj redaka i broj stupaca koji će biti vraćeni. |
Davatelji usluge trećih strana
U nastavku je djelomični popis davatelja usluge trećih strana koji imaju proizvode za različite načine čišćenja podataka.
Napomena
Microsoft ne daje podršku za proizvode treće strane.