Pivot-taulukoissa voit käyttää arvokentissä yhteenvetofunktioita pohjana olevien lähdetietojen arvojen yhdistämiseen. Jos yhteenvetofunktiot ja mukautetut laskutoimitukset eivät tuota haluttuja tuloksia, voit luoda omia kaavoja lasketuissa kentissä ja lasketuissa osissa. Voit esimerkiksi lisätä lasketun osan ja kaavan myyntiprovisiolle, joka voi vaihdella eri alueilla. Pivot-taulukko sisällyttää myyntiprovision automaattisesti välisummiin ja loppusummiin.
Toinen tapa laskea on käyttää mittayksiköitä Power Pivotissa, joka luodaan DAX (Data Analysis Expressions) -kaavan avulla. Lisätietoja on artikkelissa Mittayksikön luominen Power Pivotissa.
Pivot-taulukot tarjoavat useita tapoja tietojen laskemiseen. Lue lisätietoja käytettävissä olevista laskutavoista, lähdetietojen tyypin vaikutuksesta laskutoimituksiin ja Pivot-taulukoissa ja Pivot-kaavioissa olevien kaavojen käytöstä.
Käytettävissä olevat laskutavat
Pivot-taulukon arvojen laskemiseen voi käyttää jotakin seuraavista laskutavoista tai kaikkia niitä:
Yhteenvetofunktiot arvokentissä Arvoalueen tiedot ovat taustalla olevien lähdetietojen yhteenveto Pivot-taulukossa. Esimerkiksi seuraavista lähdetiedoista:
saadaan seuraavat Pivot-taulukot ja Pivot-kaaviot. Jos luot Pivot-kaavion Pivot-taulukossa olevista tiedoista, kyseisessä Pivot-kaaviossa olevat arvot kuvastavat laskutoimituksia liittyvässä Pivot-taulukkoraportissa.
Pivot-taulukossa Kuukausi-sarakekenttä sisältää kohdat Maaliskuu ja Huhtikuu. Alue-rivikenttä sisältää kohdat Pohjoinen, Etelä, Itä ja Länsi. Arvo Huhtikuu-sarakkeen ja Pohjoinen-rivin yhtymäkohdassa on myynnin kokonaistuotto lähdetietojen tietueista, joiden Kuukausi-arvona on Huhtikuu ja Alue-arvona on Pohjoinen.
Pivot-taulukossa Alue-kenttä voi olla luokkakenttä, jossa Pohjoinen, Etelä, Itä ja Länsi näkyvät luokkina. Kuukausi-kenttä voi olla sarjakenttä, joka esittää kohdat Maaliskuu, Huhtikuu ja Toukokuu sarjana selitteen mukaisesti. Arvot-kenttä nimeltä Myynnin summa voi sisältää merkintöjä, jotka kuvastavat kunkin alueen kokonaistuottoa kussakin kuussa. Esimerkiksi yksi merkintä kuvastaa pystyakselin (arvo) sijainnin perusteella kokonaismyyntiä huhtikuussaPohjoinen-alueella.
Arvokenttien laskemiseen ovat käytettävissä seuraavat yhteenvetofunktiot kaikille lähdetietojen tyypeille lukuun ottamatta Online Analytical Processing (OLAP) -lähdetietoja.
Toiminto Yhteenveto Summa Arvojen summa. Tämä on numeeristen tietojen oletusarvoinen funktio. Määrä Tietoarvojen määrä. Määrä-yhteenvetofunktio toimii samoin kuin LASKE.A-funktio. Määrä on muiden tietojen kuin lukujen oletusarvoinen funktio. Keskiarvo Arvojen keskiarvo. Maks Suurin arvo. Min Pienin arvo. Tulo Arvojen tulo. Laske numerot Lukuja olevien tietoarvojen määrä. Laske numerot -yhteenvetofunktio toimii samoin kuin LASKE-funktio. Keskihajonta Arvio väestön keskihajonnasta, jossa malli on koko väestön osajoukko. Keskihajontap Väestön keskihajonta, jossa väestö on kaikki yhteenvedettävä tieto. Var Arvio väestön varianssista, jossa malli on koko väestön osajoukko. Varp Väestön varianssi, jossa väestö on kaikki yhteenvedettävä tieto.
Mukautetut laskutoimitukset Mukautettu laskutoimitus näyttää arvot tietoalueen muiden kohteiden tai solujen perusteella. Voit esimerkiksi näyttää Myynnin summa -tietokentän arvot prosentteina maaliskuun myynnistä tai juoksevana kohteiden summana Kuukausi-kentässä.
Seuraavat funktiot ovat käytettävissä mukautetuissa laskutoimituksissa arvokentissä.Toiminto Tulos Ei laskentaa Näyttää arvon, joka on syötetty kenttään. Prosenttia kokonaissummasta Näyttää arvot prosentteina kaikkien raportin arvojen tai arvopisteiden kokonaissummasta. Prosenttia sarakkeen summasta Näyttää kunkin sarakkeen tai sarjan kaikki arvot prosentteina sarakkeen tai sarjan summasta. Prosenttia rivin summasta Näyttää kunkin rivin tai luokan arvon prosentteina rivin tai luokan summasta. Prosenttia Näyttää arvot prosentteina Peruskenttä-kohdan Perusosa-arvosta. Prosenttia päärivin summasta Laskee arvot seuraavasti:
(kohteen arvo) / (rivien pääkohteen arvo)Prosenttia pääsarakkeen summasta Laskee arvot seuraavasti:
(kohteen arvo) / (sarakkeiden pääkohteen arvo)Prosenttia yläsummasta Laskee arvot seuraavasti:
(kohteen arvo) / (valitun peruskentän pääkohteen arvo)Ero Näyttää arvot erona Peruskenttä-kohdan Perusosa-arvosta. Prosenttiero Näyttää arvot prosenttierona Peruskenttä-kohdan Perusosa-arvosta. Juokseva summa Näyttää peruskentän perättäisten kohteiden arvon juoksevana summana. Prosenttia juoksevasta summasta Laskee peruskentän perättäisten kohteiden arvon prosentteina juoksevana summana. Järjestä pienimmästä suurimpaan Näyttää tietyn kentän valittujen arvojen järjestyksen siten, että kentän pienimmän kohteen arvo on 1 ja sitä suuremmat kohteet saavat kukin tätä suuremman arvon. Järjestä suurimmasta pienimpään Näyttää tietyn kentän valittujen arvojen järjestyksen siten, että kentän suurimman kohteen arvo on 1 ja sitä pienemmät kohteet saavat kukin tätä suuremman arvon. Indeksi Laskee arvot seuraavasti:
((arvo solussa) x (loppusummien loppusumma)) / ((rivin loppusumma) x (sarakkeen loppusumma))
- Kaavat Jos yhteenvetofunktiot ja mukautetut laskutoimitukset eivät tuota haluttuja tuloksia, voit luoda omia kaavoja lasketuissa kentissä ja lasketuissa osissa. Voit esimerkiksi lisätä lasketun osan ja kaavan myyntiprovisiolle, joka voi vaihdella eri alueilla. Raportti sisällyttää myyntiprovision automaattisesti välisummiin ja loppusummiin.
Lähdetietojen tyypin vaikutus laskutoimituksiin
Raportissa käytettävissä olevat laskutoimitukset ja asetukset riippuvat siitä, ovatko lähdetiedot peräisin OLAP-tietokannasta tai muusta kuin OLAP-tietolähteestä.
-
OLAP-lähdetietoihin perustuvat laskutoimitukset OLAP-kuutioista luotujen Pivot-taulukoiden yhteenvetoarvot lasketaan OLAP-palvelimella ennen kuin Excel näyttää tulokset. Et voi muuttaa tapaa, jolla nämä valmiiksi lasketut arvot lasketaan Pivot-taulukossa. Et esimerkiksi voi muuta yhteenvetofunktiota, jota käytetään tietokenttien tai välisummien laskemiseen, tai lisätä laskettuja kenttiä tai laskettuja osia.
Lisäksi jos OLAP-palvelin tuottaa lasketut kentät eli lasketut jäsenet, näet nämä kentät Pivot-taulukon kenttäluettelossa. Näet myös kaikki lasketut kentät ja lasketut osat, jotka on luotu makroilla, jotka on kirjoitettu Visual Basic for Applications (VBA) -sovelluksessa ja tallennettu työkirjaan. Näitä kenttiä tai kohteita ei voi muuttaa. Jos tarvitset muunlaisia laskutoimituksia, ota yhteyttä OLAP tietokannan järjestelmänvalvojaan.
OLAP-lähdetiedoissa piilotettujen kohteiden arvot voidaan sisällyttää tai jättää pois, kun välisummia ja loppusummia lasketaan. - Laskutoimitukset, jotka eivät perustu OLAP-lähdetietoihin Muuntyyppisiin ulkoisiin tietoihin tai laskentataulukon tietoihin perustuvissa Pivot-taulukoissa Excel laskee numeerisia tietoja sisältävät arvokentät Summa-yhteenvetofunktion avulla ja tekstiä sisältävät tietokentät Määrä-yhteenvetofunktion avulla. Voit valita toisen yhteenvetofunktion, esimerkiksi Keskiarvo, Maks. tai Min., ja analysoida sekä mukauttaa tietoja tarkemmin. Voit myös luoda omia kaavoja, joissa käytetään raportin osia tai laskentataulukon muita tietoja, luomalla lasketun kentän tai kentässä olevan lasketun osan.
Kaavojen käyttäminen Pivot-taulukoissa
Voit luoda kaavoja vain raporteissa, jotka perustuvat muihin kuin OLAP-lähdetietoihin. Et voi käyttää kaavoja raporteissa, jotka perustuvat OLAP-tietokantaan. Kun käytät kaavoja Pivot-taulukoissa, seuraavat kaavojen syntaksisäännöt ja kaavan toiminnat kannattaa sisäistää:
Pivot-taulukon kaavan elementit Voit käyttää laskettuja kenttiä ja laskettuja osia varten luotavissa kaavoissa operaattoreita ja lausekkeita muiden laskentataulukon kaavojen tavoin. Voit käyttää vakioita ja viitata raportin tietoihin, mutta et voi käyttää soluviittauksia tai määritettyjä nimiä. Et voi käyttää laskentataulukkofunktioita, joissa tarvitaan soluviittauksia tai määritettyjä nimiä argumentteina. Et voi myöskään käyttää matriisifunktioita.
Kenttien ja osien nimet Excel käyttää kenttien ja osien nimiä raportin kyseisten osien tunnistamiseen kaavoissa. Seuraavassa esimerkissä tietoalueella C3:C9 käytetään kenttänimeä Maitotuotteet. Tyyppi-kentän lasketussa osassa, jolla arvioidaan uuden tuotteen myynti maitotuotteiden myynnin perusteella, voidaan käyttää esimerkiksi kaavaa =Maitotuotteet * 115%.
Huomautus
Pivot-kaaviossa kenttien nimet näkyvät Pivot-taulukon kenttäluettelossa. Kohteiden nimet näkyvät kunkin kentän avattavassa luettelossa. Älä sekoita näitä nimiä kaaviovihjeissä näkyviin nimiin, sillä vihjeiden nimet viittaavat sarjan ja arvopisteiden nimiin.
Kaavoissa käytetään loppusummia, eivät yksittäisiä tietueita Laskettujen kenttien kaavoissa käytetään kaavan taustalla olevien kenttien loppusummia. Esimerkiksi lasketun kentän kaavassa =Myynti * 1.2 kerrotaan kunkin tyypin ja alueen myynnin summa 1,2:lla. Kaavassa ei kerrota kutakin yksittäistä myyntiä 1,2:lla ja lasketa sitten kerrottuja summia yhteen.
Laskettujen osien kaavoissa käytetään yksittäisiä tietueita. Esimerkiksi lasketun osan kaavassa =Maitotuotteet *115% jokaisen maitotuotteen yksittäinen myynti kerrotaan 115 prosentilla, minkä jälkeen kerrotut summat lasketaan yhteen Arvot-alueella.Välilyönnit, numerot ja symbolit nimissä Nimessä, joka sisältää useita kenttiä, kentät voivat olla missä tahansa järjestyksessä. Yllä olevassa esimerkissä solut C6:D6 voivat olla Huhtikuu Pohjoinen tai Pohjoinen Huhtikuu. Käytä useita sanoja tai numeroita tai symboleja sisältävien nimien ympärillä puolilainausmerkkejä.
Summat Kaavoissa ei voida viitata summiin (kuten Maaliskuun summa, Huhtikuun summa ja Kokonaissumma esimerkissä).
Kenttien nimet kohdeviittauksissa Voit sisällyttää kentän nimen kohteen viittaukseen. Kohteen nimi on oltava hakasulkeissa, esimerkiksi Alue[Pohjoinen]. Käytä tätä muotoilua #NAME? -virheet, kun raportin kahdella eri kentällä on sama nimi. Voit estää #NAME, jos raportissa on esimerkiksi nimike nimeltä Lihatuotteet Tyyppi-kentässä ja toinen nimike nimeltä Lihatuotteet Luokka-kentässä. -virheet viittaamalla nimikkeisiin seuraavasti: Tyyppi[Liha] ja Luokka[Liha].
Kohteisiin viittaaminen sijainnin perusteella Voit viitata kohteeseen sen sijainnin perusteella raportissa nykyisen lajittelun ja näyttötavan mukaan. Tyyppi[1] on Maitotuotteet ja Tyyppi[2] on Kalaruoka. Tällä tavalla viitattu kohde voi muuttua aina kun kohteiden sijainti vaihtuu tai eri nimikkeitä näytetään tai piilotetaan. Piilotettuja kohteita ei lasketa tässä indeksissä.
Voit viitata kohteisiin suhteellisilla sijainneilla. Sijainnit määritetään suhteessa kaavan sisältävään laskettuun osaan. Jos nykyinen alue on Etelä, Alue[-1] on Pohjoinen; jos nykyinen alue on Pohjoinen, Alue[+1] on Etelä. Lasketussa osassa voidaan käyttää esimerkiksi kaavaa =Alue[-1] * 3%. Jos antamasi sijainti on ennen kentän ensimmäistä nimikettä tai viimeisen nimikkeen jälkeen, kaava antaa tuloksena #REF! -virheen.
Kaavojen käyttäminen Pivot-kaavioissa
Jos haluat käyttää kaavoja Pivot-kaaviossa, luo kaavat liitetyssä Pivot-taulukossa, jossa näet tietojen perustana olevat yksittäiset arvot. Sen jälkeen voit tarkastella tuloksia graafisesti Pivot-kaaviossa.
Esimerkiksi seuraavassa Pivot-kaaviossa näytetään myyjien myynnit alueittain:
Jos haluat nähdä, miltä myynti näyttäisi, jos myynti kasvaisi 10 prosenttia, voit luoda liitetyssä Pivot-taulukossa lasketun kentän, jossa käytetään esimerkiksi kaavaa =Myynti * 110%.
Tulos näkyy heti Pivot-kaaviossa seuraavan kaavion mukaisesti:
Jos haluat nähdä erillisen merkinnän Pohjoinen-alueen myynnille vähennettynä 8 prosentin kuljetuskustannuksilla, voit luoda Alue-kenttään lasketun osan, jossa on esimerkiksi kaava =Pohjoinen – (Pohjoinen * 8%).
Tuloksena syntyvä kaavio näyttää tältä:
Myyjä-kentässä luotu laskettu osa näkyy kuitenkin sarjana selitteen mukaisesti ja kaaviossa arvopisteenä kussakin luokassa.
Tarvitsetko lisäohjeita?
Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta tai saada tukea yhteisöistä.