Tag Archives: sum

Vertaillaan arvoja edellisen vuoden/kuukauden arvoihin

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

SIIRTYMÄ – VASTINE (OFFSET – MATCH)

Esimerkki Siirtymä (Offset) ja Vastine (Match) -funktioiden käytöstä dynaamiseen summaamiseen.

Halutaan laskea listasta aina annetusta päivämäärästä seitsemän seuraava lukua yhteen.

Aloituspäivä kirjoitetaan soluun ja kaavassa käytetään Vastine-funktiota hakemaan päivän sijainti.

Koska vastinefunktioon on annettu koko A-sarake ja ensimmäisellä rivillä on otsikko, palautettavasta rivistä miinustetaan 1.
Kaava on muotoa:
=VASTINE(D2;A:A;0)-1, =MATCH(D2;A:A;0)-1

Esimerkissä se palauttaa päivämäärän positioksi 6.
Positiosta 6 halutaan laskea seuraavat 7 lukua yhteen B-sarakkeesta.
Käytetään Summa-funktiota, johon yhdistetään Siirtymä-funktio.
Siirtymä-funktiolla kerrotaan, mistä lähdetään liikkeelle (A1), monenneltako riviltä laskenta (Vastineen palauttama arvo) aloitetaan ja kuinka monta riviä lasketaan (E2).
=SUMMA(SIIRTYMÄ(A1;D4;1;E2)), =SUM(OFFSET(A1;D4;1;E2))
Kaikki voidaan laittaa yhteen ja samaan kaavaan, tällöin kaavan muoto on:
=SUMMA(SIIRTYMÄ(A1;VASTINE(D2;A:A;0)-1;1;E2))
=SUM(OFFSET(A1;MATCH(D2;A:A;0)-1;1;E2))

Esimerkki Summa, Siirtymä ja Vastine funktiosta

Summan laskeminen ei onnistu

Kun summan laskeminen ei onnistu sen takia kun alueella on virhearvoja niin tällöin voidaan käyttää matriisikaavaa ja JOSVIRHE (ISERROR) funktiota.

Esimerkki:

Sarakkeissa A ja B on arvoja. A-sarakkeen arvot jaetaan B sarakkeiden arvoilla jolloin saadaan C sarakkeeseen virhearvo jos B:ssä on nolla (0) tai on tyhjä.

Tällöin kuten esimerkissä näkyy saadaan myös virhe soluun C5 jossa yritetään summata C-sarakkeen arvoja. Jotta summa voitaisiin laskea C:n virhearvoista huolimatta käytetään matriisikaavaa kaavaa ja funktiota JOSVIRHE (IFERROR) summakaavassa:
{=SUMMA(JOSVIRHE(C1:C4;””))}
{=SUM(IFERROR(C1:C4;””))}

Kaava kirjoitetaan normaalisti mutta enterin sijaista painetaan Ctrl+Vaihto+Enter jolloin saadaan aikaan matriisikaava.

Esimerkin D-sarakkeessa on käytetyt kaavat.

Esimerkki

Esimerkki