Tag Archives: powerpivot

Vertaillaan arvoja edellisen vuoden/kuukauden arvoihin

Leave a reply

Käytetään tässä Power Pivotia. Siirretään/linkitetään tiedot Excelistä Power Pivottiin.
Excelissä määritellään lista taulukoksi (Table) ja lisätään Tietomalliin, Power Pivot – Lisää tietomalliin (Power Pivot – Add to Data Model).

Esimerkki taulukko ja laskelmat:

Toiminto vie PowerPivot puolelle jossa määritellään vielä päivämäärä taulukko, Rakenne – Päivämäärätaulukko, Uusi (Design – Date Table, New)

Veriossa 2016 päivämäärät määräytyy tuodun taulun mukaan.
Tämän jälkeen luodaan tuodun taulun ja päivämäärätaulukon välille yhteys.
Napsauta Aloitus – Kaavionäkymä, (Home – Diagram View)
Raahaa hiirellä Päivämäärätaulukon Date kenttä arvotaulukossa olevan Day kentän päälle.
Näin saat luotua 1 suhde moneen yhteyden taulujen välille.
Palaa normaalinäkymään napsauttamalla Tietonäkymä (Data View).

Kun tämä on tehty niin luodaan mittarit.
Siirry Excelin puolella ja napsauta Power Pivot valintanauhaa.
Mittareita tarvitaan kaksi ja käytettävät funktiot ovat CALCULATE, SUM, PREVEOUSYEAR ja PREVEOUSMONTH.
Napsauta Mittayksiköt – Uusi Mittari (Measure, New Measure)
Kirjoita Taulukon nimi (Table name) ruutuun sen taulun nimi johon mittari luodaan, esimerkissä Results
Mittayksikön nimi (Measure name) ruutuun mittarin nimi, esimerkissä ValuesTotal
Kaava (Formula) ruutuun kirjoita kaava =SUM(Results[Value])
Voit tarkistaa kaavan oikeinkirjoituksen napsauttamalla Tarkista kaava (Check Formula) painiketta
Kun olet valmis napsauta OK

Luodaan uusi mittari jossa lasketaan edellisen vuoden arvot:
Napsauta Mittayksiköt – Uusi Mittari (Measure, New Measure)
Kirjoita Taulukon nimi (Table name) ruutuun sen taulun nimi johon mittari luodaan, esimerkissä Results
Mittayksikön nimi (Measure name) ruutuun mittarin nimi, esimerkissä ValuesPrevYear
Kaava (Formula) ruutuun kirjoita kaava =CALCULATE(SUM([Value]);PREVIOUSYEAR(‘Calendar'[Date]))
Voit tarkistaa kaavan oikeinkirjoituksen napsauttamalla Tarkista kaava (Check Formula) painiketta
Kun olet valmis napsauta OK

Luodaan vielä kolmas mittari jossa verrataan kuluvan kuukauden arvoja edellisen kuukauteen.
Napsauta Mittayksiköt – Uusi Mittari (Measure, New Measure)
Kirjoita Taulukon nimi (Table name) ruutuun sen taulun nimi johon mittari luodaan, esimerkissä Results
Mittayksikön nimi (Measure name) ruutuun mittarin nimi, esimerkissä ValuesPrevMonth
Kaava (Formula) ruutuun kirjoita kaava =CALCULATE(SUM([Value]);PREVIOUSMONTH(‘Calendar'[Date]))
Voit tarkistaa kaavan oikeinkirjoituksen napsauttamalla Tarkista kaava (Check Formula) painiketta
Kun olet valmis napsauta OK

Luo Pivot taulukot jossa näitä mittareita käytetään.
Päivämäärätaulukosta raahaat Vuosi (Year) kentän riviotsikoksi (Rows)
Arvo (Values) ruutuun raahaa arvotaulun (Results) ValuesTotal ja ValuesPrevYear

Kuukausi vertailuun luo uusi Pivot johon arvo (Values) ruutuun raahaat arvotaulun (Results) ValuesTotal ja ValuesPrevMonth

Päivämäärälaskentaa

Leave a reply

Ryhmittely päivämäärien kanssa

Kun Excelin Pivot-taulukossa tarvitaan päivämäärälaskentaa ja erilaisia yhteenvetoja ja vertailuja päivämäärillä niin otetaan PowerPivot avuksi.
Alla esimerkki jossa laskin kävijämäärän per viikonpäivä:

Tämän saa aikaseksi tosi kätevästi luomalla päivämäärätaulukon PowerPivottiin ja siirtämällä myös lähdetiedon PowerPivottiin.

Excel versiossa 2013 luodaan päivämäärätaulu Excelissä ja linkitetään tai viedään se PowerPivottiin.
Excel versiossa 2016 voidaan päivämäärätaulukko luoda suoraan PowerPivotissa.
Kun päivämäärätaulukko on PowerPivotissa, luodaan yhteys (suhde) päivämäärätaulun ja lähdetaulun välille. Näin saadaan pivottiin ryhmittelyt esim. viikonpäivän mukaan.
Alla on esimerkki päivämäärätaulusta jonka loin Excel 2013 versiossa.

Ja tässä kaavat:

Kaavat:

Sarake     Kaava      Suomeksi
Vuosi:     YEAR(A2)     VUOSI(A2)
Kuukausinumero     MONTH(A2)     KUUKAUSI(A2)
Kuukausinimi Suom  TEXT(MONTH(A2)     TEKSTI(KUKAUSI(A2)
Kuukausinimi Eng   TEXT(MONTH(A2);”[$-en-GB]kkkk”) TEKSTI(KUUKAUSI(A2);”[$-en-GB]kkkk”)
Viikonpäivänumero  WEEKDAY(A2)     VIIKONPÄIVÄ(A2)
Päivä Suom     TEXT(A2;”pppp”)     TEKSTI(A2;”pppp”)
Päivä Eng     TEXT(A2;” [$-en-GB]pppp”)     TEKSTI(A2;” [$-en-GB]pppp”)

Excel 2013
Siirry PowerPivot valintanauhalle ja napsauta Lisää tietomalliin (Add to Data Model)
Luo yhteys (suhde) päivämäärätaulun ja data taulun välillä PowerPivotissa:
Aloitus – Kaavionäkymä (Home – Diagram View)
Raahaa hiirellä kalenterin päivämääräkenttä linkitettävän lähdetaulun päivämääräkentän päälle.

Siirry takaisin Tietonäkymään (Data View).
Napsauta Pivot-taulukko
Pivot-taulukossa raahaa kenttäluettelosta päivän nimisarake rivit (Rows) ruutuun ja arvot (Values) ruutuun, se sarake jota haluat laskea.

Excel 2016
Luo päivämäärä taulukko PowerPivotissa sen jälkeen kun olet tuonut lähdetiedot.
Siirry Rakenne (Design) valintanauhaan
Valitse Päivämäärätaulukko – Uusi (Date Table – New)
Päivämäärätaulukko luodaan sille ajanjaksolle jolle on lähdetiedossa päivämääriä. Voit päivittää ajanjakson samasta työkalusta valitsemalla Päivitä alue (Update Range)

PowerPivot kalenteri

Leave a reply

Excel 2016:een ja PowerPivotiin on tullut uusi kiva ominaisuus, nimittäin päivämäärätaulun luonti.

Tämän avulla voit vaikka seurata päivittäistä kävijämäärää tarvitsematta itse kirjoittaa ja määritellä päivämääriä.

Kun olet tuonut PowerPivotiin taulun, joka sisältää päivämääriä, siirry Design-valintanauhaan ja valitse Date Table ja siitä edelleen New.

PowerPivot luo sinulle kalenteritaulun, jonka liität tietokannasta tai Excelistä tuotuun tauluun.

Design – Realtionships, Create Relationship.

Kun tämä on tehty, voit tehdä Pivot-taulukon Exceliin, jossa käytät riviotsikkona kalenteritaulun viikonpäivää ja Pivot-taulukon arvokenttänä kenttää, jota haluat laskea.

Alla esimerkki päivittäisestä kävijämäärästä eräänä ajanjaksona:

Mikäli päivämääriä tulee lisää, voit päivittää päivämäärätaulukon kätevästi PowerPivotissa. Sinun tarvitsee ainoastaan antaa raja-arvot, ja PowerPivot lisää automaattisesti uudet päivämäärät kalenteriin.

Taulujen välinen laskenta PowerPivotissa

Leave a reply

Halutaan poimia tietoja toisesta taulusta, johon on muodostettu yhteys (relation).

Tällöin voidaan käyttää funktiota RELATED, jonka syntaksi on RELATED(Sarakenimi).

Esimerkissä on kaava:

    =RELATED(Products[ProductName])

Se poimii tuotetaulusta tuotteen nimen.

Tämän saisi myös haettua LOOKUPVALUE-funktiolla, mutta tämän kaava on huomattavasti pitempi ja aikaavievämpi kirjoittaa:

    =LOOKUPVALUE(Products[ProductName];Products[ProductID];’Order Details'[ProductID])

 

Toisessa esimerkissä lasken, mitä tuotteen myynti olisi listahinnalla, eli kerron tilatun kappalemäärän tuotteen listahinnalla:

    =’Order Details'[Quantity]*RELATED(Products[UnitPrice])

Tässäkin käytän RELATED-funktiota, koska taulujen välillä on yhteys (Order Details ja Products).

 

Huomaa myös, että kirjoittaessasi kaavoja PowerPivotissa teet sen kaavakentässä. Muista kaikki sulut! PowerPivot ei korjaa kaavoja samalla tavalla kun varsinainen Excel.

 

Taulujen liitokset/suhteet

 

Taulujen yhteyksiä pääset tutkimaan PowerPivot ikkunan Aloitus – valintanauhan Näytä ryhmän Kaavionäkymä -työkalun avulla (Home, View, Diagram View).

 

Jos yhteyksiä on, voit kaksoisnapsauttaa yhteysviivaa ja pääset tarkastelemaan yhteyttä.

 

Takaisin normaalinäkymään pääset napsauttamalla Tietokantanäkymä-työkalua, Näytä ryhmässä (Data View).

 

Jos yhteyksiä ei ole määritelty, voit itse määritellä taulujen yhteydet PowerPivotissa.

Siirry PowerPivotin Rakenne (Design) -valintanauhaan.

Suhteet ryhmässä on kaksi työkalua, Luo suhde ja Suhteiden hallinta, joilla määrittelet ja tarkastelet taulujen välisiä liitoksia/suhteita (Create Relationships, Manage Relationships).

 

Taulujen ominaisuus (Table Properties) -työkalulla hallitset suodatusta ja pääset määrittelemään SQL-lauseketta jolla tietoja poimitaan (Vaihda kohteeseen – Kyselyeditori, Switch to – Query Editor)

PowerPivot – tietojen tuonti

Leave a reply

PowerPivot on käytännöllinen, kun tietomäärä on suuri ja tarvitaan useampi taulu samanaikaisesti analysoitavaksi. PowerPivotista teet edelleen Pivot-taulukon kädenkäänteestä.

PowerPivotiin voit teoriassa tuoda taulun, jossa on enintään 1 999 999 997 riviä!

PowerPivot tuli Excelin versioon 2010 ja se on Microsoftin ilmainen lisäohjelma.

Löydät versiot 2013 ja 2010 ladattavaksi näistä linkistä:
Suomeksi:
http://office.microsoft.com/fi-fi/excel/powerpivotin-lataaminen-HA101959985.aspx

Englanniksi:
http://office.microsoft.com/en-us/excel/download-powerpivot-HA101959985.aspx

 Taulujen tuonti

Avaa PowerPivot ikkuna (PowerPivot, PowerPivot Window).

Valitse ulkoinen tietolähde (External data):
tietokannasta (SQL-Server, Access, Analysis service), muista lähteistä (Oracle, Teradata, Sybase jne.), (From Database, From Other Sources).

Tuonti Accessista:

Etsi tietokanta napsauttamalla avautuvassa valintaikkunassa painiketta Selaa (Browse).
Seuraavassa valintaikkunassa voit joko kirjoittaa itse SQL-lauseen, jolla tiedot tuodaan,
tai valita vaihtoehdon, jolla valitset taulut suoraan tietokannasta. Tämä vaihtoehto on oletuksena. Napsauta seuraava painiketta.

 Valitse avautuvasta valintaikkunasta taulut, jotka haluat tuoda. Valintaikkunassa on myös painike Valitse liittyvät taulukot (Select Related Tables), joka valitsee valittujen taulujen yhteyksiin perustuvat muut taulut, ja painike Esikatsele ja suodata (Preview & Filter), joka näyttää esikatselunäkymän valitusta taulusta sekä mahdollistaa suodatuksen.

 Napsauta Valmis (Finish).

 

Taulujen valinta

Taulujen valinta

Tuonnista näytetään tilanneikkuna, joka kertoo, miten tuontiprosessi edistyy ja kuinka monta tietuetta tuotiin. Kun tuonti on valmis, sulje ikkuna.

Tuodut taulut näkyvät PowerPivot ikkunassa eri välilehdillä.

Tuonti SQL-Serveristä:

Valitse SQL-Server ulkoiseksi tietolähteeksi.
Kirjoita serverin nimi tai valitse se listasta.
Määrittele kirjautumistunnukset.
Valitse tietokanta.

Tietokannan määrittely

Tietokannan määrittely

Tämän jälkeen toimi samalla tavalla kun Accessista tuotaessa.

 

 

PowerPivot päivitys

Leave a reply

Automaattinen Pivot ja PowerPivot päivitys toimii Excel versiossa 2013.

Excel versioon 2013 on lisätty ominaisuus jolla päivitys voidaan hoitaa automaattisesti.

Jos se halutaan tehdä normaalissa moduulissa, lisätään seuraava koodirivi makroon:

ActiveWorkbook.Model.Refresh

Jos päivitys halutaan taas tehdä kun työkirja avataan se lisätään ThisWorkbook kohtaan Sub_ Workbook_Open:

Private Sub Workbook_Open()
     Me.Model.Refresh
End Sub

Tietojen ryhmittelyä

Leave a reply

Tässä kun Pivot- ja PowerPivot kurssia valmistelen tuli taas esiin kysymys tietojen ryhmittelystä ryhmiin, ryhmiin joita ei ole lähdetiedoissa.

Alla kaksi eri esimerkkiä miten sen voisi ratkaista.

Esimerkissäni käytän Accessin Northwind-esimerkki kannasta ottamaani dataa.

Esimerkkidata

Esimerkkidata

Pivot esimerkki:

Haluan laskea myynnin maantieteellisesti jaettuna. Lähdetiedoissa on vain maiden nimet joten joutuisin tämän tekemään manuaalisesti pivotissa joka on hankalaa.

Eräs kiertotapa on että poimin kaikki yksilölliset maat eri tauluun ja laitan siihen mihin maantieteelliseen alueeseen ao. maa kuuluu.

Tietysti voin tämän tehdä suoraan lähdetietoonkin mutta tuhansien rivien päivitys manuaalisesti on hankalaa.

Joten poimin lähteestä yksilölliset maat seuraavasti:

1. Valitsen B-sarakkeen
2. Tiedot, Lajittele ja suodata, Lisäasetukset – Vain ainutkertaiset tiedot (Data, Sort & Filter, Advanced – Unique records only)
3. Kopioin tuloksen ja liitän sen uuteen taulukkoon
(Olisin kyllä voinut lähteä liikkeelle uudesta taulukosta ja erikoissuodatuksella saada sinne kaikki yksilölliset maat mutta tällä kertaa teen sen näin)
4. Määrittelen mihin alueeseen kukin maa kuuluu (manuaalisesti) uuden taulukon B -sarake.

Ryhmät

Ryhmät

5. Tämän jälkeen siirryn lähdetietoon ja luon sinne uuden sarakkeen PHAKU (VLOOKUP) kaavalla joka poimii luomastani uudesta taulusta ryhmän.

H-sarakkeen kaava: =PHAKU(B2;Groups!A:B;2;EPÄTOSI), (=VLOOKUP(B2;Groups!A:B;2;FALSE))

Jos taulu on määritelty Taulukoksi niin kaava kopioituu automaattisesti kaikille riveille.

Valmis data

Valmis data

Nyt voin ryhmitellä pivotissani tiedot tämän ryhmän mukaan.

 PowerPivot esimerkki:

Kopioin yksilölliset maat omaksi tauluksi PowerPivottiin ja lisäsin varisainaiseen tauluun uuden sarakkeen johon laitoin kaavan:

=LOOKUPVALUE(Ryhmat[Ryhmä];Ryhmat[Country];[Country])
(Ryhmat on uusi taulu jonka loin)

PowerPivot esimerkki

PowerPivot esimerkki

Kaava etsii maan luomastani taulusta ja palauttaa ryhmän johon ao. maa kuluu.
Funktion LOOKUPVALUE syntaksi on:
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
result_columnName = sarake josta arvo palautetaan
search_columnName = sarake josta hakuarvo etsitään
search_value = arvo jota etsitään

PowerPivotista saat yksilölliset rivit esimerkiksi viemällä ne pivot-taulukoksi ja sitten kopioi tuloksen takasin uuteen taulukkoon PowerPivottiin.

 

PowerPivotin käyttöönotto Excel 2013 versiossa

Leave a reply

Tiedosto, Asetukset – Apuohjelmat
Valitse valintaikkunan alaosasta kohdasta Hallitse COM-apuohjelmat ja napsauta Siirry
Rastita vaihtoehto Microsoft Office PowerPivot for Excel 2013
Napsauta OK

 

PowerPivot tuli Excelin versioon 2010 ja se on Microsoftin ilmainen lisäohjelma.

Löydät versiot 2013 ja 2010 ladattavaksi näistä linkistä:

Suomeksi:

http://office.microsoft.com/fi-fi/excel/powerpivotin-lataaminen-HA101959985.aspx

Englanniksi:

http://office.microsoft.com/en-us/excel/download-powerpivot-HA101959985.aspx