Polttoainehinnat-Excel-esimerkki

Excelin Web Query ja PivotTable-toiminnot

Microsoft Office Excel on kokeneen käyttäjän käsissä tehokas työkalu, jossa laskentataulukot ja graafit saa päivittymään automaattisesti myös millä tahansa WWW-sivulla olevalla datalla. Tämä "Web Query" -toiminto on ollut ohjelmassa versiosta Excel 97 lähtien. Kun data on onnistuneesti Exceliin saatu, sen yhteenvetämisen onnistuu parhaiten Excelin jossain määrin vaikeasti hahmotettavalla PivotTable-toiminnolla.

Excelin laskentataulukkofunktiot ovat suurten datamäärien käsittelyssä rajoittuneita, joten usein näkee työkirjan toiminnallisuutta laajennetun ikävillä makroilla. Todellinen Excel-osaaja hoitaa kuitenkin homman kun homman pelkillä laskentataulukkofunktioilla. Näin olen tehnyt monessa työprojektissa ja nyt myös oheisessa esimerkkityökirjassa, joka esittelee kuinka voit asettaa Excel-työkirjan päivittämään itsensä automaattisesti uusimpiin netistä löytyviin datoihin.

Ulkoisen tietolähteen käytössä suurin ongelma on se, että kun ulkoisen tiedon määrä vaihtelee, Excel ei osaa monistaa ko. "external data" -alueeseen liittyviä jatkolaskentafunktioita data-alueen koon muuttuessa. Tämän vuoksi esimerkiksi oheisessa työkirjassa on jouduttu valmiiksi monistamaan jatkolaskenta 2500 riville; olettaen täten, että ulkoista dataa ei ole koskaan sen enempää. Tästä suuresta funktiojoukosta "Laskenta"-taulukossa johtuu työkirjan suuri koko ja laskennan hitaus.

Mutta mikä tärkeintä, pelottavien ja ärsyttävien makrojen käyttöön ei ole tarvinnut tässäkään työkirjassa sortua. Esimerkkityökirja on testattu Excel 97:llä, Excel 2003:lla ja Excel 2010:llä. Johtuen Web Query -toiminnon pienistä eroavaisuuksista eri Excel-versioissa on mahdollista, että työkirja ei toimi oikein muissa Excel-versioissa.

Lataa "bensa.xlt" mallityökirja (ZIP; 212 kB)

Mallityökirja lataa asetustaulukossa valitsemasi polttoaineen hinnat Polttoaine.net-sivuilta ja järjestää tiedot hintajärjestykseen huomioiden ketjukohtaiset alennuksesi. Hintatiedoista lasketaan lisäksi varsin käyttökelpoinen normaalijakaumaan pohjautuva graafinen analyysi.

lataa mallityökirjan teossa käytetty Web Query

Excel Web Query -tiedosto (*.iqy) sisältää tiedot tietolähteenä käytettävästä WWW-sivusta. Tällaista tiedostoa tarvitaan, kun halutaan ohjelmoida Excel-taulukko noutamaan dataa netistä. Tiedostoa ei tarvita enää sen jälkeen. (Uusissa Excel-versioissa pystyy luomaan ja muokkaamaan *.iqy-tiedostoja, Excel 97:n käyttäjien on kirjoitettava tiedosto itse etukäteen esim. Notepadilla.)

Google Spreadsheet -onlineversio

Ulkoisen datan ja PivotTable'ien käyttö on hieman erilaista Googlen Spreadsheet -sovelluksessa kuin Microsoft Office Excelissä. Ulkoinen data poimitaan laskentataulukkoon erityisellä laskentataulukkofunktiolla, joka automaattisesti monistaa itsensä viereisiin soluihin. Jos haluat muuttaa asetuksia ja/tai muokata tätä Google-versiota, kirjaudu sisään Google-tunnuksella ja valitse Tiedosto - Luo kopio oman kopion luomiseksi.

Voit muuttaa mallityökirjan asetuksia (esim. ketjukohtaisia alennuksia "Asetukset"-sivulla) ja tallentaa siitä mukautetun version. Tällöin kannattaa kuitenkin katkaista verkkoyhteys (tai vastata kieltävästi "Query Refresh" -kysymykseen, ei mahdollista Excel 97:ssä) ennen mallityökirjan avaamista, jotta Excel ei lataa tietoja netistä työkirjaan. Näin et turhaan tallenna tekemiesi asetusmuutosten lisäksi sen hetkisiä hintatietoja työkirjaasi. Kun tallennat työkirjan Template-muodossa (*.xlt), et myöhemminkään vahingossa tallenna netistä lataamisia tietoja pysyvästi työkirjaan.

Mallityökirja on vapaasti käytettävissä haluamaasi tarkoitukseen, voit muokata sitä ja jakaa sitä vapaasti edelleen sellaisenaan ja muutettuna. Optiomoi vaikka laskentakaavoja tai kehitä esim. tilastollista analyysiä edelleen. Jos saat toteutettua hyviä parannuksia, julkaise ne ihmeessä muiden iloksi!