Miten monta Teslaa Suomessa on ja missä ne on rekisteröity? Fiat 500:sten lukumäärä Espoossa? Autojen keskimääräinen ikä kunnittain? Mikä on suosituin Audin väri Helsingissä?

Tein Trafin avoimesta henkilöajoneuvodatasta Excelin Power Pivotilla työkirjan, jonka voit ladata itsellesi liikenteessä olevien ajoneuvojen tutkimiseen. Jos käytössäsi on 64-bittinen Excel-versio, pystyt analysoimaan jopa koko 2,6 miljoonan ajoneuvon aineistoa. Blogissa on myös 32-bittistä Excel-versiota varten työkirja, johon latautuu vähemmän ajoneuvoja (2010-luvulla käyttöönotetut ajoneuvot eli hitusen yli miljoona riviä).

[EDIT 03/2015: Tässä blogissa mainitut työkirjat toimivat alkuperäisen vuonna 2014 julkistetun datan analysoinnissa (dataversiot 1.0 ja 2.0). Uusi 3.0 datan käsittelyyn tarkoitettu työkirja löytyy jutusta Trafin uusi avoin ajoneuvodata 3.0]

Hienoa, että viranomaiset avaavat dataa ja mainiota, että Excelin Power Pivotin tietomalliin voi imuroida  kookkaita data-aineistoja. Alla olevasta kuvasta näet, että Power Pivotin pakkaussuhde on huomattava (alla kuva Excel-työkirjasta, johon on ladattu Trafin data.csv):

Laatimissani ajoneuvotyökirjoissa on tietomallin lisäksi runsaasti valmiita pivot-taulukoita, mutta niitä sekä laskennallisia kenttiä voit luoda itse lisää. Tästä blogista ladattavat esimerkkityökirjat sisältävät vain pienen otoksen datasta, joten sinun pitää ladata varsinainen aineisto myös Trafin sivuilta ja suorittaa ajoneuvotyökirjojen päivitys alla olevien ohjeiden mukaisesti.

Suomen ajoneuvot -työkirja tutkittavaksi

Työkirjat on laadittu Excelin 2013 –versiolla ja tietomalli on toteutettu Power Pivotilla, joten tarvitset Excel 2013:sta jonkun seuraavista versioista: Professional Plus, Office 365 Pro Plus tai Standalone.

Datan ja kuvaustiedoston lataus Trafin sivuilta

Lataa ja tallenna Trafin sivuilta sekä zipattu data-tiedosto (.csv) että datatiedoston koodiston kuvaus (.xlsx).

Ajoneuvotyökirjan imurointi

Imuroi joko täyden datan analysointiin (vaatii 64-bittisen Excelin ja Power Pivotin) tarkoitettu työkirja:

• Kaikki liikenteessä olevat ajoneuvot: Liikenteessa olevat henkiloajoneuvot (1 967 KB)

Tai 2010-luvun ajoneuvojen analysointiin luotu työkirja (pitäisi pyöriä myös 32-bittisessä, jos työasemassa riittävästi muistia):

• 2010-luvulla käyttöönotetut liikenteessä olevat ajoneuvot: Liikenteessa olevat henkiloajoneuvot 2010-luku (1 863 KB)

Huom! Molemmissa työkirjoissa on vain suppeat otokset datasta, joten työkirjat latautuvat hetkessä.

Power Pivot -apuohjelman aktivointi Excelissä

Aktivoi Power Pivot käyttöön komennolla File > Options > Add-Ins (Tiedosto > Asetukset > Apuohjelmat). Valitse ikkunan alaosasta COM Add-Ins ja Go (Siirry). Rastita Microsoft Office PowerPivot for Excel 2013. Jos Power Pivot puuttuu ikkunasta, sinulla ei ole oikeaa Excel-versiota.

 

Ajoneuvotyökirjan valmistelu

Avaa ajoneuvotyökirja ja salli ulkoiset tietolähteet.

Siirry Power Pivotin puolelle valitsemalla PowerPivot-välilehdeltä Manage (Hallitse).

Valitse Power Pivotissa Home > Existing Connections (Aloitus > Olemassa olevat yhteydet) ja valitse vuorollaan molemmat ikkunan yläosassa olevat tietolähteet (Ajoneuvot ja Excel Koodisto) ja muokkaa tiedostopolku osoittamaan lataamiisi Trafin tiedostoihin valitsemalla Edit > Browse (Muokkaa > Selaa). Suorita myös molempien tietolähteiden päivitys Refresh (Päivitä) -painikkeella. Huomaa, että tietolähteiden muuttaminen sekä niiden päivitys (erityisesti ajoneuvodata) saattaa kestää useita minuutteja. Odota rauhassa, jos ikkunoiden painikkeet eivät heti vaikuta reagoivan.

Työkirjat on toteutettu 30.5.2014 (1.0) ja 31.8.2014 (2.0)  julkaistujen datatiedostojen rakenteen mukaisesti. Työkirjan päivitys onnistuu, mikäli Trafin datan rakenne on säilynyt ennallaan.

Kun tiedot ovat päivittyneet, sulje Power Pivot -ikkuna ja testaa Excelissä olevia mallipivotraportteja. Voit luoda työkirjaan lisää pivotteja sekä lisätä laskettuja kenttiä. Ja sitten vastauksiin:

Ja sitten vastauksiin:

Helsingissä Audin suosituin väri on musta

Tesloja on 45 ja niitä löytyy seuraavilta paikkakunnilta:

Fiat 500 viistoperiä löytyy Espoosta 110 ja minulla on niistä yksi. 🙂

Olen postannut saman jutun myös työnantajani Onsightin blogiin: Trafin ajoneuvotiedot Excelin tietomallissa

PS. Jos haluat itse oppia tekemään vastaavia Power Pivot -malleja, lue myös Power Pivotiin tutustuminen – Trafin ajoneuvodata. Jutussa neuvottaan vaihe vaiheelta tietomallin luonti.

Löydät muita HExcelligent-työkaluja täältä.