Monthly Archives: March 2017

Päivämäärälaskentaa

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)

Ehdollinen muotoilu

Muotoillaan listan/taulukon koko rivi eri väriseksi jos jossakin sarakkeessa on ehdot täyttävä tieto.

Valitaan ensin koko alue
Valitaan Aloitusvalintanuhasta Ehdollinen muotoilu (Home, Conditional Formatting) ja siitä
Valitaan Uusi sääntö (New Rule)
Valitse Määritä kaavan avulla , mitkä solut muotoillaan (Use a formula to determine which cells to format)
Kirjoita kaava siihen varattuun ruutuun, esimerkissä: =$B1=”finland”
Napsauta painiketta Muotoile (Format)
Valitse haluamasi muotoilu
Paina OK-painiketta kunnes tulet takaisin taulukkoon.


Lopputulos:


Kaava: =$B1=”finland” tarkoittaa että kun sarakkeessa B esiintyy merkkijono finland niin koko rivi muotoillaan. Huomaa kaavan lukitus, vain sarake B lukitaan koska alueella joka valittiin ensin verrataan aina B saraketta. Rivi ei voi olla lukittu koska se on vaihduttava joka rivillä vastaamaan asianomaista riviä.
Merkkijono finland on laitettava lainausmerkkeihin, “” koska se on kaavassa olevaa tekstiä.