Excelin GETPIVOTDATA-funktio hyötykäytössä

11.10.2016, 21:40 in Kuukauden vinkki, Taulukkolaskenta, Vinkit, Yleinen by Tuula Virtanen

Excelin NOUDA.PIVOT.TIEDOT (GETPIVOTDATA) -funktio ei ole aivan yleisimmin käytettyjen funktioiden joukossa. Mitä sillä oikeastaan voisikaan saada aikaan? Esimerkiksi yhteenvetoja, joiden ulkoasu ei rajoitu Pivot-taulukon tarjoamiin mahdollisuuksiin.

Esimerkkinä yhteenvedon tekeminen myyntidatasta

Esimerkin lähtötilanteena on tuotteiden myyntidataa, joka on muotoiltu Excelissä taulukoksi. Toiveena on saada selkeä yhteenveto eri kampanjojen euromääristä niin, että sama yhteenveto on myös helppo päivittää myöhemmin uusilla tiedoilla. Pivot-taulukkohan tämän tarpeen toki täyttää, mutta nyt halutaankin visuaalisesti hieman eri näköinen lopputulos.

Mitä esimerkkivideolla tapahtuu?

Esimerkissä Excel-tiedostoon suoritetaan seuraavat toimenpiteet:

  1. Luodaan tiedoista Pivot-taulukko, joka näyttää myyntisummat kampanjoittain ja vuosittain.
  2. Lisätään erillinen Yhteenveto-välilehti, jonne laitetaan raportoinnissa tarvittavat otsikot.
  3. Lisätään yhteenveto-välilehdelle nouda.pivot.tiedot -funktio, mikä kätevimmin tapahtuu aloittamalla kaava yhtäsuuruusmerkillä = ja napsauttamalla sitten hiirellä lukua pivot-taulukosta.
  4. Kaava halutaan kopioida muihin soluihin yhteenveto-välilehdellä, joten kiinteiden tekstiarvojen sijaan funktioon vaihdetaan soluviittaukset otsikoihin. Helpoin tapa on valita ensin funktiossa oleva kiinteä teksti ja napsauttaa sitten solua, jossa vastaava otsikko sijaitsee..
  5. Soluviittauksiin lisätään tarvittavat $-merkit, jotta kaavan kopiointi onnistuisi niin, että siinä viitataan aina otsikoihin. Tässä kannattaa napsauttaa kaavarivillä soluviittausta ja painella F4-näppäintä, joka rullaa esiin vuorotellen eri vaihtoehtoja $-merkin käytössä (lukitaanko rivi, sarake vai molemmat)
  6. Kaava kopioidaan otsikoiden korkeudelle ja leveydelle hyödyntämällä automaattista täyttöä, eli solu valitaan, tartutaan valitun solun oikeaan alakulmaan ja vedetään alemmille riveille. Tämän jälkeen irroitetaan hiiri, tartutaan oikeaan alakulmaan uudelleen ja täytetään kaava myös oikealle päin tarvittaviin sarakkeisiin.
  7. Solut muotoillaan solutyyleillä.
  8. Reunaviivat otetaan pois näkyvistä asetusten kautta.
  9. Lopuksi lisätään vielä Sparkline-kaaviot lukujen oikealle puolelle trendiä osoittamaan.

Lopputuloksena syntyy juuri halutun näköinen yhteenvetoraportti, jota ei tarvitse aina joka kerran lähteä tekemään alusta uudestaan. Riittää, että uusien myyntilukujen tullessa päivitetään Pivot-taulukko Tiedot > Päivitä (Data > Refresh) -komennolla.

Milloin kannattaa käyttää erillistä yhteenvetosivua?

Hyödynnä NOUDA.PIVOT.TIEDOT-funktiota ja erillistä yhteenvetosivua silloin, kun tarvitset yhteenvedollesi näyttävämmän ulkoasun ja tiedät, että tiedot tulevat päivittymään myöhemmin. Varsinkin, kun tiedät että otsikot eivät juuri tule muuttumaan, tämä toteutustapa on pitkäikäinen ja säästää työaikaasi.

Milloin taas tätä toteutustapaa ei kannata hyödyntää?

  • Jos on kysymys vain kertaluonteisesta yhteenvedon laatimisesta –> Kopioi mieluummin kaikki yhteenvedon tiedot suoraan eri välilehdelle ja muotoile haluamasi näköiseksi.
  • Jos yhteenvedossa tarvittavat otsikot muuttuvat usein –> Muotoile tällöin mieluummin Pivot-taulukko lähelle tarvitsemaasi ulkoasua ja anna vähän periksi ulkoasuvaatimuksistasi. Pivot-taulukossakin voit esimerkiksi vaihdella otsikoiden paikkoja sekä muotoilla tietoja niin, että muotoilu säilyy päivitystilanteissakin.

Lisävinkki

Erillisestä yhteenvetosivusta ja kiinteistä otsikoista on erityistä hyötyä silloin, kun myös puuttuvat tiedot halutaan nostaa esiin. Jos haluaisimme vaikkapa tehdä yhteenvedon myynneistä viikoittain, niin olisi tärkeää nähdä, jos jollain viikolla ei ole myyty yhtään mitään. Pivot-taulukolla tällaista et saa esiin, koska sen tiedot perustuvat täysin tietoriveihin, kyseinen viikko vain jää puuttumaan yhteenvedosta. Sen sijaan erillisen yhteenvedon ja kiinteiden otsikoiden avulla nappaat kiinni ja korostat myös puuttuvat tiedot!

Tällöin kaavaan pitäisi lisätä virheen käsittely JOSVIRHE (IFERROR) -funktiolla, sillä kun NOUDA.PIVOT.TIEDOT-funktio ei löydä etsittävää tietoa Pivot-taulukosta, se palauttaa virheilmoituksen. Seuraava muutos kaavaan (punaisella) antaa soluun arvon 0, jos tietoja ei löydy ja virhetilanne ilmenee.

=JOSVIRHE(NOUDA.PIVOT.TIEDOT(”TotalCost”;Pivot!$A$3;”Promotion”;$A6;”Vuodet”;B$5);0)

Avaimet rautaiseen Excel-osaamiseen löydät Excel-koulutustarjonnastamme