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
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
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
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
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.