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.
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 |
|---|---|
|
Ovdje se pokazuje kako se koristi naredba Ispuna. |
|
|
Stvaranje i oblikovanje tablica
|
Ovdje se pokazuje kako se stvara tablica u programu Excel te kako se dodaju i brišu stupci ili izračunati stupci. |
|
Ovdje se prikazuje nekoliko načina automatiziranja ponavljajućih zadataka pomoću makronaredbi. |
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 |
|---|---|
|
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. |
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. |
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)
|
Ovdje se pokazuje kako se koristi naredba Pronađi i nekoliko funkcija pronalaženja teksta. |
|
Ovdje se pokazuje kako se koristi naredba Zamijeni i nekoliko funkcija uklanjanja teksta. |
|
|
Ovdje se pokazuje kako se koriste dijaloški okviri Traženje i Zamjena. |
|
|
FIND, FINDB
|
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. |
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 |
|---|---|
|
Ovdje se pokazuje kako se koriste tri funkcije veličine slova. |
|
|
Pretvara sva velika slova tekstnog niza, u mala. |
|
|
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. |
|
|
Slova teksta pretvara u velika slova. |
Tekstne vrijednosti katkad sadrže početne, završne ili višestruke ugrađene znakove razmaka (Unicode vrijednosti skupa znakova 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 |
|---|---|
|
Vraća brojčani kod za prvi znak u tekstnom nizu. |
|
|
Iz teksta uklanja prva 32 neispisiva znaka u 7-bitnom ASCII kodu (vrijednosti od 0 do 31). |
|
|
Iz teksta uklanja 7-bitne ASCII znakove razmaka (vrijednost 32). |
|
|
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. |
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 |
|---|---|
|
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. |
|
|
Pretvara broj u oblik teksta i dodaje znak valute. |
|
|
Pretvara vrijednost u tekst u određenom brojčanom obliku. |
|
|
Zaokružuje broj na zadani broj decimalnih mjesta, oblikuje broj u decimalnom obliku pomoću točki i zareza, te prikazuje rezultat kao tekst. |
|
|
Pretvara tekstni niz, koji predstavlja broj, u broj. |
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. |
|
Ovdje se pokazuje kako se različite vremenske jedinice pretvaraju jedna u drugu. |
|
|
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. |
|
|
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. |
|
|
Pretvara datum iz tekstnog oblika u serijski broj. |
|
|
Prikazuje decimalni broj zadanog vremena. Ako je oblikovanje ćelije prije unosa funkcije bilo Općenito, rezultat će biti oblikovan kao datum. |
|
|
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. |
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
|
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. |
|
Ovdje se pokazuje kako se koriste funkcije LEFT, MID, RIGHT, SEARCH i LEN za razdvajanje stupca imena u dva ili više stupaca. |
|
|
Ovdje se pokazuje kako se koristi funkcija CONCATENATE, operator & (ampersand) i čarobnjak za pretvaranje teksta u stupce. |
|
|
Ovdje se pokazuje kako se koriste naredbe Spoji ćelije, Spoji kroz i Spoji i centriraj. |
|
|
Spaja dva tekstna niza ili više njih u jedan tekstni niz. |
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 |
|---|---|
|
Okomiti raspon ćelija vraća kao vodoravni ili obratno. |
|
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 |
|---|---|
|
Ovdje se pokazuju uobičajeni načini traženja podataka pomoću funkcije 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. |
|
|
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. |
|
|
Traži vrijednost u prvom stupcu polja tablice i vraća vrijednost u istom retku iz drugog stupca u polju tablice. |
|
|
Prikazuje vrijednost ili referencu prema vrijednosti iz tablice ili raspona. Postoje dva oblika funkcije INDEX: oblik polja i oblik reference. |
|
|
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. |
|
|
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. |
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.
|
Davatelj usluge |
Proizvod |
|---|---|
|
Add-in Express Ltd. |
|
|
Add-Ins.com |
|
|
AddinTools |
|
|
WinPure |
