Excel je najpularnejše orodje za delo s preglednicami in mnogi ga uporabljamo tudi za pripravo in analizo podatkov. Za bralce bloga ADP sem pripravila izbor devetih ključnih funkcionalnosti Excela, ki jih sama najpogosteje uporabljam pri delu s podatki ter sem jih predstavila na delavnici za novinarje (maj 2017).
1. Pretvorba besedila v stolpce
Ko boste na spletu iskali podatke, jih boste le redko našli urejene v XLS formatu. Eden izmed pogostejših formatov, v katerih so shranjeni javno dostopni podatki, je CSV (coma separated values), kar v prevodu pomeni z vejico ločene vrednosti. To je tudi eden izmed formatov, ki ga lahko izberete pri prenosu podatkovnih datotek ADP.
Ko CSV datoteko ADP raziskave odprete v Excelu, vidite navidez neurejen niz podatkov, kot na primer na Sliki 1. Vsaka vrstica predstavlja niz vrednosti, ki so ločene z vejico. Te vrednosti v stolpce pretvorimo z ukazom text to columns (besedilo v stolpce), kjer izberemo opis delimited (razmejeni) in ločilo comma (vejica).
Ukaz lahko uporabite tudi za pretvorbo drugih tekstovnih formatov (TXT datoteke), kjer pa so namesto vejic lahko uporabljena druga ločila (npr. presledki, podpičja, tabulatorji).
2. Fiksiranje vrstic/stolpcev
Dimenzije tabel in podatkovnih datotek, ki jih v Excelu pregledujemo, pogosto presegajo velikost zaslona. To vsekakor velja za podatkovne baze ADP, kjer imamo navadno precej več enot (vrstic) in spremenljivk (stolpcev), kot jih lahko vidimo na zaslonu. Pri premikanju po delovnem listu želimo, da bi tudi v spodnejših vrsticah in desnejših stolpcih lista lahko videli prvo vrstico (pri ADP je to navadno šifra enote) oziroma prvi stolpec (navadno imena spremenljivk). Prvo vrstico (ali več vrstic) in/ali prvi stolpec (ali več stolpcev) lahko zamrznemo z ukazom freeze panes oziroma freeze top row (prva vrstica) ali freeze first column (prvi stolpec). Odmrznemo jih z ukazom unfreeze panes (odmrzni) (Slika 2).
3. Razvrščanje in filtriranje podatkov v tabeli
Še dve funkcionalnosti, ki nam precej olajšata pregledovanje podatkovnih datotek, sta razvrščanje in filtiranje podatkov. Podatke lahko razvrščamo po velikosti od najmanjšega do največjega ali največjega do najmanjšega (Slika 3) po eni ali več spremenljivkah. Pred razvrščanjem poskrbimo, da izberemo spremenljivko, s katero lahko podatke uredimo v prvotno stanje (npr. id številka).
S filtri pa omogočimo, da se prikažejo samo določene izmed vrednosti spremenljivke. Napredno filtriranje omogoča tudi identifikacijo podvojenih vrednosti, če se teh želimo znebiti. Zame je filtriranje ena izmed nepogrešljivih funkcij, predvsem jo uporabljam v fazi čiščenja in priprave podatkov.
4. Relativno sklicevanje
Osnova uporabe formul v Excelu je sklicevanje na druge celice (npr. =A1), pri čemer črke označujejo stolpce, številke pa vrstice. Privzeto je sklicevanje vedno relativno, kar pomeni, da se pri kopiranju formul iz ene celice v drugo, formula prilagodi tako, da se sklic nanaša na celico, ki je določeno število stolpcev desno in/ali vrstic navzdol. Npr. formula =A1+B1 pri kopiranju v celico desno postane =B1+C1, pri kopiranju v celico navzdol pa =A2+B2.
Včasih pa si pri kopiranju formul želimo, da bi bil sklic absoluten, kar pomeni, da bi se pri kopiranju formule v drugo celico ohranil sklic na isto celico, tj. =A1. Sklic naredimo absoluten tako, da pred črko in pred številko dodamo dolarski znak ($), s čimer vrednost fiksiramo, da se pri kopiranju ohrani izvorni klic. V zgornjem primeru bi torej formula =$A$1+$B$1 pri kopiranju v katero koli drugo celico ostala nespremenjena.
Sama v praksi pogosto uporabljam tudi kombinacijo relativnega in absolutnega sklicevanja, kjer dolarski znak dodam le pred črko (fiksiranje sklica na stolpec) ali pred številko (fiksiranje sklica na vrstico). V zgornjem primeru bi se sklic =$A1+$B1 pri kopiranju v desno ohranil, pri kopiranju v celico navzdol pa bi postal =$A2+$B2. Obratno pa bi se sklic =A$1+B$1 pri kopiranju v desno spremenil v =$B1+$C1, pri kopiranju navzdol pa bi se ohranil.
5. Posebno lepljenje
Kopirati in prelepiti podatke zna vsakdo, bodisi z desnim klikom bodisi z bližnjicama ctrl+c in ctrl+v, vendar v Excelu s tovrstnim lepljenjem včasih ne dosežemo želenega rezultata. S klasičnim lepljenjem celice (ali niza celic) namreč ne prenesemo le njene vrednosti, ampak tudi oblikovne lastnosti, skilcevanja (formule) in druge lastnosti. Če želimo kopirati le enega izmed atributov celice (najpogosteje je to vrednost), izberemo ukaz “paste special” (posebno lepljenje), kjer izberemo “values” (vrednosti) ali pa kaj drugega, odvisno od namena lepljenja podatkov.
6. Pogojno oblikovanje
Ena izmed funkcij, ki je včasih koristna pri pregledovanju podatkov, je “conditional formating” (pogojno oblikovanje), kjer lahko celice v tabeli obarvamo glede na njihove vrednosti. Na Sliki 6 sem jo na primer uporabila, da sem vizualno izpostavila vrednosti, ki so nad določeno mejo. Uporabimo pa jo lahko še na številne druge načine.
7. Funkcije za besedilo
V podatkovni datoteki imamo pogosto lahko tudi tekstovna polja. Pri čiščenju tovrstnih vnosov so izjemno koristne tekstovne funkcije, med katerimi bi izpostavila naslednje:
- LOWER velike tiskane črke spremeni v male tiskane;
- UPPER male tiskane črke spremeni v velike tiskane;
- PROPER deluje podobno kot LOWER, le da pri prvi črki v besedi ohrani veliko začetnico (glej primer na sliki);
- TRIM, s katero izbrišemo odvečne presledke (ohranijo se le enojni presledki);
- LEN izpiše dolžino niza znakov;
- CONCAT (CONCATENATE v starejših verzijah Excela; namesto nje lahko uporabimo tudi simbol &) združi vrednosti dveh ali več polj v en niz (npr. združevanje imena in priimka v skupen stolpec);
- LEFT prepiše niz znakov določene dolžine s skrajno leve;
- RIGHT prepiše niz znakov določene dolžine s skrajno desne;
- MID prepiše niz znakov določene dolžine z začetkom na mestu, ki ga sami določimo;
- FIND poišče določen besedilni niz v določeni celici in vrne njegovo lokacijo v tej celici.
Sama besedilne funkcije pogosto uporabljam tudi za tvorjenje sintakse za analizo podatkov v SPSS in v programskih jezikih.
8. Funkcija VLOOKUP
S funkcijo VLOOKUP lahko določen element poiščemo v tabeli in mu pripišemo vrednost na podlagi neke druge tabele (legende). Med drugim je uporabna pri analizi podatkov ADP, ko želimo določeni številski vrednosti v Excelu pripisati oznako (labelo) spremenljivke glede na vrednosti v vprašalniku. Pred tem na drug (ali isti) list prekopiramo legendo vrednosti in oznak (label) spremenljivke.
Npr. za spremenljivko regija lahko vrednostim od 1 do 12 v podatkovni datoteki pripišemo nov stolpec, v katerem izpišemo pripadajoče ime regije (Pomurska, Podravska, Koroška, itd.).
9. Vrtilne tabele
Podatkovno tabelo lahko pretvorimo v vrtilno tabelo, kar nam omogoči izdelavo večdimenzionalnih tabel. Na desni sam prikaže okno, v katerem lahko izberemo spremenljivke, ki jih bomo uporabili za filtre, stolpce, vrstice in za vrednosti. Ko analiziramo podatke ADP, za vrednost lahko uporabimo kar šifro enote (navadno prvi stolpec), ki enolično določa enoto analize ter izberemo funkcijo COUNT (štetje). Pri uteženih podatkih uporabimo utež ter izberemo funkcijo SUM (vsota). S filtrom lahko izberemo, da tabela prikazuje le enote, ki ustrezajo določenim pogojem. Nato s poljubno kombinacijo spremenljivk oblikujemo vrstice in stolpce tabele. Pri vrednostih so privzete absolutne vrednosti, lahko pa izberemo tudi poljubne odstotke (po vrsticah ali stolpcih) ali druge izračune.
Sama vrtilne tabele včasih uporabljam kot nadomestilo za kontingenčne tabele, ki jih z ukazom CROSSTABS izpisujem v programu SPSS.
To je seveda le nekaj izmed funkcionalnosti – Excel omogoča še veliko drugega. Za nadalnje branje o Excelu priporočam kar uradno stran MS Excel, kjer so številne teme tudi prevedene v slovenščino, na voljo pa so tudi razne izobraževalne vsebine.
Za konec bralce ADP bloga prosim še za povratno informacijo, kako uporabne so se jim zdele vsebine v tej objavi (spodaj v komentarjih) ter katere funkcije so sami že kdaj uporabljali:
