Lupauduin eilen kirjoittamaan pivot-taulukoiden hyödyistä. Tämä onkin kieltämättä yksi Excelin ikuisuusaiheista – olen useasti löytänyt itseni tilanteesta, jossa saan selittää pivot-taulukoinnista ja siitä miksi se on niin näppärää. Muutamalla lauseella tätä onkin vaikeaa selittää, joten kokonainen postaus esimerkkeineen toivottavasti kattaa suurimman osan – vaikka ei kuitenkaan mitenkään kaikkea!
Ennen kuin aloitetaan listan läpikäyminen, on syytä sanoa vielä yksi asia:
Pivot-taulukointi löytyy käytännössä kaikista taulukkolaskentaohjelmista, mutta MS Excelin editori on ehdottomasti paras.
Erot korostuvat nimenomaan editoreissa. Pivot-taulukointia pystyy tehdä esimerkiksi myös LibreOfficella ja Google Spreadsheetsillä. Oman kokemukseni perusteella raporttien luominen ja muokkaaminen on kuitenkin ehdottomasti helpointa Excelillä. Erityisesti Google Spreadsheetsissä toiminto on lähes käyttökelvoton; LibreOfficessa se on vielä ihan käytettävissä mutta ei kuitenkaan yhtä hyvä kuin Excelissä. Jos kysyntää löytyy, niin voin kirjoittaa myöhemmin toki näihinkin ohjeita!
1. Pienet ja suuret taulukot
Pivot-taulukoinnin yksi selkeimmistä eduista on suurten tietomäärien nopea käsittely. Yritinkin tätä kirjoitusta varten etsiä hyvää taulukkoa, jossa olisi vähintään 10 000 riviä tietoja 20 sarakkeessa – ajatuksena vaikuttaa lukija pivotoinnin uskomattomasta nopeudesta. Näin suurta taulukkoa en kuitenkaan löytänyt, joka itseasiassa on jopa ihan hyvä. Pivot-taulukoinnista on nimittäin usein hyötyä myös pienempien taulukoiden hallinnassa.
Tätä esimerkkiä varten käytän vuoden 2014 Euroviisujen äänestystilastoja, jotka soveltuu tähän tarkoitukseen oikein hyvin.
Taulukko luodaan esim. Excel 2010:ssa valitsemalla taulukko, ja klikkaamalla Insert -> PivotTable. Toiminto on samantyyppinen myös muissa ohjelmistoissa, esim Google Spreadsheetsissä Pivot-taulukko löytyy Data – > Pivot Table report…
2. Dynaamisuus
Dynaaminen on nykyään aika arkinen sana, mutta oletko koskaan pysähtynyt miettimään mitä se oikeastaan tarkoittaa? Erään määritelmän mukaan dynaamisella tarkoitetaan muun muassa seuraavia asioita:
Pivot-taulukko on totisesti näistä kaikkea. Excel 2010:ssa editori näyttää seuraavanlaiselta:
Taulukossa on neljä eri kenttää: Report Filter, Column Labels, Row Labels ja Values. Saat tiedot näihin näppärästi yksinkertaisesti vetämällä niihin halutut kentät. Pystyt myös käsittelemään niiden arvoja, suodattamaan niitä, järjestelemään, ynnä muuta todella pienellä vaivalla. Kokeile ja ylläty!
Column Labels tarkoittaa sarakkeen tietoja, kun taas Row Labels riveihin tulevia tietoja. Report Filter ja Values kohtaa käsittelen tarkemmin kohdissa 3 ja 4. Kentät ovat edelleen samat kaikissa editoreissa, mutta saattavat näyttää täysin erilaiselta.
Palataan Euroviisuesimerkkiin. Ehkä luonnollisin taulukko on sellainen, jossa on pystysuoraan pisteitä antavat maat ja vaakasuoraan vastaanottavat maat. Silloin nelikenttään vedettäisiin seuraavat arvot:
Values kohtaan laitetaan Sum Of Points. Ei hätää, se käydään läpi kohdassa 4.
3. Filtteröinti
Tarvitsetko raportin vain pohjoismaisista maista? Tässä kohtaa voit painaa rivin tai sarakkeen reunassa olevaa nuolta ja valita vain tietyt maat, eli:
Jolloin taulukosta näkyy vain pohjoismaille annetut pisteet.
Voit myös laittaa filtteröinnin erikseen nelikentän kohtaan Report Filter. Tälloin taulukossa näkyy vain pisteet, joita on annettu pohjoismaille. Näppärää, eikö?
4.Values, eli mitä lasketaan
Values-kohtaa pääset muokkaamaanpainamalla nelikentässä olevan laatikon oikeassa reunassa olevaa mustaa nuolta, josta avautuvasta valikosta paina ”Value Field Settings”. Tai no, katso alla olevasta kuvasta mitä tarkoitin.
Tästä avautuvasta ikkunasta löytyy paljon vaihtoehtoja, jotka soveltuvat eri tarkoituksiin. Euroviisuesimerkissä käytämme kohtaa Sum, jolloin lasketaan solujen (eli maan antamien pisteiden) summa.
Count-lasku tarkoittaa sitä, kuinka monta solua yhteensä sisältää tietoja. Koska Count-laskee myös nollat tiedoiksi, tämä kenttä ei oikein sovellu sellaisenaan euroviisuesimerkkiin. Käytännössä count on äärimmäisen hyödyllinen, jos tiedot ovat esimerkiksi nimiä ja haluat tietää henkilömäärän osastoittain.
Jos haluamme laskea maiden pohjoismaille antamien pisteiden lukumäärät (eli sijoitukset Top 10), voimme käyttää Count Numbers- toimintoa. Tämä toiminto nimensä mukaan laskee vain numerot, mutta ei laske nollaa mukaan tuloksiin.
Average tarkoittaa keskiarvoa, eli luonnollisesti se kertoisi keskimääräisen pisteemäärän. Muutkin kohdat toimivat nimensä mukaisesti (eli StDev laskee keskihajonnnan jne..)
Entä jos haluat käyttää samaa kohtaa uudelleen? Klikkaa kohtaa oikealla painikkeella ja lisää se haluamaasi kohtaan allaolevan kuvan mukaisesti.
Yksi syy miksi samaa kenttää voidaan tarvita useasti, on jos tahdot toiseen kenttään vaikka summan ja toiseen sen prosentuaalisen osuuden. Tämän pystyt valitsemaan esimerkiksi samasta ikkunasta kuin äsken, mutta Show Values As kohdasta.
Nyt taulukko näyttää pisteden summan ja niiden prosentuaalisen osuuden kokonaismäärästä. Okei, ei ehkä kaikista hyödyllisin esimerkki, mutta uskot varmasti että vastaavanlaiselle taulukolle on paljon hyötyä käytännössä!
5. Groupit eli ryhmät
Euroviisuissa on kautta aikain ollut epäilyksiä siitä, että maat tietyiltä alueilta äänestävät omiaan. Tälläistä kartoitusta varten olisikin todella hyödyllistä jos maat voitaisiin jakaa valmiiksi ryhmiin. Onneksi Pivot-taulukossa tämä onnistuu todella helposti!
Valitse maat painamalla Ctrl-näppäintä (Macilla Cmd), jonka jälkeen klikkaa oikealla. Sen jälkeen paina Group.
Nyt maat on kätevästi järjestetty ryhmään.
Kutistamalla ryhmän näet aikaisemmat arvot laskettuina koko ryhmälle. Kätevää, eikö?
Joskus taulukot ovat järjestetty esimerkiksi päivämäärän tai muun numeerisen arvon perusteella. Silloin Group-toiminto on todella käytännöllinen. Koska euroviisuesimerkissä arvot ovat maittain, on pakko poiketa hetkeksi esimerkistä.
Samasta ladattavasta taulukosta löytyykin sopivasti jokaisen tuomariston jäsenen syntymäpäivä. Pienellä kikkailulla tein niistä samanlaisen pivot-taulukon, jonka lajittelin siten, että että kunkin vuoden kohdalla näkyy kuinka monta tuomaria on syntynyt silloin. Eli:
Tämäkin on ihan hyvä taulukko, mutta esimerkiksi kuvaajan tekeminen sen perusteella on mahdotonta, koska välistä puuttuu vuosia jolloin ei ole syntynyt yhtään tuomaria (esimerkiksi 1945 ja 1947).
Tälläisessä tilanteessa automaattinen ryhmittäminen usein pelastaa tilanteen. Painat vain koko sarakkeen nimikenttää ja klikkaat oikealla -> Group. Jos tiedot sisältää vain numeerisia arvoja, eikä yhtään tyhjää kenttää, saat seuraavanlaisen ikkunan auki. Tästä voit asettaa, että jaat tiedot vuosikymmenittäin ajalta 1940-2000.
Tämän jälkeen lista on ryhmitelty vuosittain, ja voit huomata, että suurin yksittäinen ikäryhmä oli 1980-luvulla syntyneet. (Huomaa Count-laskun käyttö!)
7. Monitasoisuus
Vaikka tarkoitus oli käsitellä vain ohimennen tuomareiden ikää, niin sattumalta se avasi kuitenkin niin herkullisen mahdollisuuden käsitellä erästä toista Pivot-taulukoiden ominaisuutta ettei sitä voinut jättää käyttämättä. Nimittäin tietojen monitasoisuutta.
Samaan tyyliin kuin voit asettaa useita arvoja Values-kohtaan, voit tehdä saman myös rivi- ja sarakekohdille (Row Labels / Column Labels)
Vetämällä samaan kenttään kuukausi-kohdan, voimme tarkistella tiettynä kuukautena syntyneiden tuomarieiden syntymävuosia, jolloin lista näyttää seuraavalta:
Huomaa kuinka vuosiluvut pysyivät ryhmiteltyinä!
8. Soluviittaus
Jokainen, joka on käyttänyt Pivot-taukoita, tulee jossain vaiheessa törmäämään siihen, kuinka hankalaa Pivot-taulukkoon viittaaminen on. Jos nimittäin klikkaat pivot-taulukon arvoa, saat kaavaasi tyypillisen soluviittauksen sijaan esimerkiksi:
=GETPIVOTDATA(”Points”;$A$4;”From country”;”Armenia”;”To country”;”Finland”)
Joka tarkoittaa suomeksi suunnilleen: Etsi pisteet Pivot-taulukosta, joita Armenia on antanut Suomelle.
Tämän usein harmillisen kaavan voit tietysti kiertää kirjoittamalla solun sijainnin suoraan kaavaan. Mutta oletko koskaan pysähtynyt katsomaan mitä tuo kaava oikeastaan pitää sisällään? Tästä aiheesta täytyisi melkein kirjoittaa oma kokonainen postaus, mutta periaatteessa pystyt käyttämään tuota funktiota samaan tapaan kuin esimerkiksi VLOOKUPia tai INDEX MATCH MATCHiä (tulossa). Suosittelen tutustumaan!
9. Luo graafi ja se päivittyy
Tämä kohta liittyy oikeastan enemmän graafien ominaisuuksiin, mutta koska löydän itseni usein käyttämässä nimenoamaan graafi + pivot-taulukko- yhdistelmää, ei tätä voinut jättää poiskaan. Jos käytät pivot-taulukkoa nimenomaan graafien luomiseen, kannattaa ehdottomasti luoda valmiiksi halutun näköinen graafi ja sen jälkeen muokata kentät oikeanlaisiksi.
Graafia kutsutaan tässä yhteydessä nimellä PIvot Chart, joka tarkoittaa aivan samaa asiaa kuin normaali Chart, jonka lisäksi pystyt muokata tietoja suoraan graafista.
Joskus Values-kohdan arvot eivät riitä, ja voit tarvita lisäksi tarkempia tai uniikimpia kaavoja. Näitä voit lisätä Options-valikon alta Calculations -> Calculated Field.
Euroviisuesimerkissä voidaan esimerkiksi laskea erotus tuomariston antaman järkestyksen ja yleisön äänestämän järjestyksen välillä, luomalla kenttä =’Jury Rank’ – ’Televote rank’
11: Lisää uudet tiedot taulukkoon päivittämällä
Tätä ominaisuutta ei voi liikaa kiitellä. Jos lähdeaineistoosi tulee lisää tietoja (tai ne muuttuvat), voit päivittää Pivot-taulukon yksinkertaisesti painamalla Refresh!
+ EXTRA: tiedot monista lähteistä
Tämän tyyppiset listat on usein tapana päättää One more thing- tyylisesti. Usein tämä vaihe on vähän ärsyttävä ja kiusallinen, mutta tällä kertaa sille on oikeasti ihan hyvät perustelut. Kuulemieni huhujen mukaan Excel 2013 on nimittäin tuonut uudistuksen, joka todella helpottaa suurten tietomäärien käsittelyä. Voit nimittäin luoda Pivot-taulukon yhdistelemällä useita eri tietolähteitä. Jätän tämän kuitenkin extraksi, koska en ole itse vielä päässyt kokeilemaan tätä uudistusta. Kysyntää sille kuitenkin on!
Jäikö jokin ominaisuus puuttumaan listasta? Jätä kommentti ja kerro mikä on oma suosikkisi!
Paluuviite: Kuinka usein Cheek Flexaa? - Transponoinointi, Text to Columns, Pivot-taulukko - Excel-guru.fi