Koosteet ovat tapa tiivistää, tiivistää tai ryhmitllä tietoja. Kun aloitat taulukoiden tai muiden tietolähteiden raakatiedoista, tiedot ovat usein tasaisia, mikä tarkoittaa, että yksityiskohtia on paljon, mutta niitä ei ole järjestetty tai ryhmitelty millään tavalla. Tämä yhteenvetojen tai rakenteen puute voi vaikeuttaa kaavoiden löytämistä tiedoista. Tärkeä osa tietojen mallintamista on määrittää koosteet, jotka yksinkertaistavat, abstraktit tai tiivistävät kaavoja vastauksena tiettyyn liiketoimintakysymykseen.
Yleisimmät koosteet, kuten KESKIARVO-, LASKE-, DISTINCTCOUNT-, MAKS-, MIN- tai SUMMA-funktioita käyttävät koosteet, voidaan luoda mittayksikkönä automaattisesti summan avulla. Muuntyyppiset koosteet, kuten AVERAGEX, COUNTX, COUNTROWS tai SUMX, palauttavat taulukon ja edellyttävät DAX (Data Analysis Expressions) -lausekkeella luotua kaavaa.
Tietoja koosteiden Power Pivot
Kooste Ryhmät valitseminen
Kun koostat tietoja, ryhmittelet tiedot määritteiden, kuten tuotteen, hinnan, alueen tai päivämäärän mukaan, ja määrität sitten kaavan, joka toimii kaikissa ryhmän tiedoissa. Kun esimerkiksi luot kokonaissumman vuodeksi, luot koosteet. Jos luot tämän vuoden suhteen edelliseen vuoteen verrattuna ja esität ne prosentteina, koostetyyppi on erilainen.
Päätös tietojen ryhmittelystä perustuu liiketoimintakysymykseen. Koosteet voivat esimerkiksi vastata seuraaviin kysymyksiin:
Laskee Kuinka monta tapahtumaa kuukaudessa oli?
Keskiarvot Mikä oli myyjän tämän kuun keskimääräinen myynti?
Vähimmäis- ja enimmäisarvot Mitkä myyntipiirit olivat myydyistä yksiköistä viisi parasta?
Jos haluat luoda laskutoimituksen, joka vastaa näihin kysymyksiin, sinulla on oltava yksityiskohtaiset tiedot, jotka sisältävät laskettavat tai laskettavat luvut, ja numeeristen tietojen on oltava jollain tavalla yhteydessä ryhmiin, joita käytät tulosten järjestämiseen.
Jos tiedot eivät vielä sisällä arvoja, joita voit käyttää ryhmittelyssä, kuten tuoteluokan tai sen maantieteellisen alueen nimen, jossa kauppa sijaitsee, haluat ehkä lisätä tietoihin ryhmiä lisäämällä luokkia. Kun luot ryhmiä Excelissä, sinun on kirjoitettava tai valittava manuaalisesti ryhmät, joita haluat käyttää laskentataulukon sarakkeista. Relaatiojärjestelmässä hierarkioita, kuten tuotteiden luokkia, tallennetaan kuitenkin usein toiseen taulukkoon kuin fakta- tai arvotaulukkoon. Yleensä luokkataulukko on linkitetty faktatietoihin jonkinlaisella avaimella. Oletetaan esimerkiksi, että tietosi sisältävät tuotetunnuksia, mutta eivät tuotteiden nimiä tai niiden luokkia. Jos haluat lisätä luokan tasaiseen Excel-laskentataulukkoon, sinun on kopioitava sarakkeeseen, joka sisältää luokkien nimet. kun käytössäsi on Power Pivot, voit tuoda tuoteluokkataulukon tietomalliin, luoda yhteyden taulukon ja numerotietojen ja tuoteluokkaluettelon välille ja ryhmitellä sitten tiedot luokkien avulla. Lisätietoja on artikkelissa taulukoiden välisen yhteyden Create.
Koostefunktion valitseminen
Kun olet tunnistanut ja lisännyt käytettävät ryhmittelyt, sinun on päätettävä, mitä matemaattisia funktioita käytetään koostamiseen. Usein sanaa kooste käytetään synonyyminä matemaattisille tai tilastollisille toiminnoille, joita käytetään koosteissa, kuten summien, keskiarvojen, vähimmäisarvojen tai lukujen kanssa. Power Pivot avulla voit kuitenkin luoda koostetta varten mukautettuja kaavoja sekä Power Pivot että Excelissä olevien vakiokoosteiden lisäksi.
Jos esimerkiksi edellisten esimerkkien arvot ja ryhmittelyt ovat samat, voit luoda mukautettuja koosteita, jotka vastaavat seuraaviin kysymyksiin:
Suodatetut määrät Kuinka monta tapahtumaa kuukaudessa oli, lukuun ottamatta kuukauden lopun ylläpito-ikkunaa?
Ajan keskiarvoja käyttävät suhteet Mikä oli myynnin prosentuaalinen kasvu tai lasku viime vuoden samaan ajanjaksoon verrattuna?
Ryhmitelty vähimmäis- ja enimmäisarvo Mitkä myyntialueet olivat kunkin tuoteluokan tai kunkin myyntikampanjan kärjessä?
Koosteiden lisääminen kaavoihin ja Pivot-taulukoisiin
Kun sinulla on yleinen käsitys siitä, miten tiedot ryhmiteltäisiin merkityksellisiksi ja mitä arvoja haluat käsitellä, voit päättää, haluatko luoda Pivot-taulukon vai luoda laskutoimituksia taulukkoon. Power Pivot laajentaa ja parantaa Excelin alkuperäistä kykyä luoda koosteita, kuten summia, lukuja tai keskiarvoja. Voit luoda mukautettuja koosteita Power Pivot joko Power Pivot-ikkunassa tai Excelin Pivot-taulukkoalueella.
-
Lasketussa sarakkeessa voit luoda koosteita, joissa otetaan huomioon nykyinen rivikonteksti, jotta voit noutaa toisiinsa liittyvät rivit toisesta taulukosta ja laskea sitten yhteen, laskea tai laskea kyseiset arvot toisiinsa liittyvillä riveillä.
-
Mittayksikkönä voit luoda dynaamisia koosteita, joissa käytetään sekä kaavassa määritettyjä suodattimia että Pivot-taulukon rakenteen sekä osittajien, sarakeotsikoiden ja riviotsikoiden valinnan määrittämiä suodattimia. Mittayksiköitä, joissa käytetään vakiokoosteita, voi luoda Power Pivot käyttämällä Summa-toimintoa tai luomalla kaavan. Voit myös luoda implisiittisiä mittayksiköitä Käyttämällä Vakiokoosteita Pivot-taulukossa Excelissä.
Ryhmittelyjen lisääminen Pivot-taulukkoon
Kun suunnittelet Pivot-taulukkoa, ryhmittelyjä, luokkia tai hierarkioita edustavat kentät vedetään Pivot-taulukon sarakkeet ja rivit -osaan tietojen ryhmittelyä varten. Vedä sitten numeerisia arvoja sisältävät kentät arvoalueelle, jotta ne voidaan laskea, laskea keskiarvoon tai laskea yhteen.
Jos lisäät luokkia Pivot-taulukkoon, mutta luokkatiedot eivät liity faktatietoihin, saatat saada virheen tai erikoisia tuloksia. Yleensä Power Pivot yrittää korjata ongelman tunnistamalla ja ehdottamalla yhteyksiä automaattisesti. Lisätietoja on artikkelissa Yhteyksien käyttäminen Pivot-taulukoissa.
Voit myös vetää kenttiä osittajia varten ja valita tietyt tietoryhmät tarkastelua varten. Osittajien avulla voit ryhmitellä, lajitella ja suodattaa pivot-taulukon tuloksia vuorovaikutteisesti.
Ryhmittelyn käsitteleminen kaavassa
Ryhmittelyjen ja luokkien avulla voit myös koostaa taulukoihin tallennettuja tietoja luomalla yhteyksiä taulukoiden välille ja luomalla sitten kaavoja, jotka hyödyntävät näitä yhteyksiä toisiinsa liittyvien arvojen etsimiseen.
Toisin sanoen, jos haluat luoda kaavan, joka ryhmittelee arvot luokan mukaan, yhdistä ensin tiedot sisältävä taulukko ja luokat sisältävät taulukot yhteydellä ja muodosta sitten kaava.
Lisätietoja hakuja käyttävien kaavojen luomisesta on artikkelissa Power Pivot -kaavojen haut.
Suodattimien käyttäminen koosteissa
Power Pivot uusi ominaisuus on mahdollisuus käyttää suodattimia sarakkeisiin ja tietotaulukoihin, ei vain käyttöliittymässä, Pivot-taulukossa tai -kaaviossa, vaan myös juuri niissä kaavoissa, joita käytät koosteiden laskemiseen. Suodattimia voidaan käyttää kaavoissa sekä lasketuissa sarakkeissa että kaavoissa.
Esimerkiksi uusissa DAX-koostefunktioissa voit määrittää argumentiksi koko taulukon sen sijaan, että määrittäisit arvot, joiden summan tai määrän haluat laskea. Jos et ole määrittänyt suodattimia kyseiseen taulukkoon, koostefunktio toimii taulukon määritetyn sarakkeen kaikkia arvoja vastaan. DAX-kielessä voit kuitenkin luoda taulukkoon joko dynaamisen tai staattisen suodattimen, jotta kooste toimii eri tietojen alijoukkoa vastaan suodatinehdon ja nykyisen kontekstin mukaan.
Yhdistämällä ehdot ja suodattimet kaavoihin voit luoda koosteita, jotka muuttuvat kaavoissa annettujen arvojen mukaan tai jotka muuttuvat pivot-taulukon riviotsikoiden ja sarakeotsikoiden valinnan mukaan.
Lisätietoja on artikkelissa Kaavojen tietojen suodattaminen.
Excelin koostefunktioiden ja DAX-koostefunktioiden vertailu
Seuraavassa taulukossa on lueteltu joitakin Excelin tarjoamia vakiokoostettamisfunktioita ja linkkejä näiden funktioiden käyttöönottoon Power Pivot. Näiden funktioiden DAX-versio toimii paljolti samalla tavalla kuin Excel-versio, ja tiettyjen tietotyyppien syntaksissa ja käsittelyssä on joitakin pieniä eroja.
Tavalliset koostefunktiot
|
Funktio |
Yleismerkki |
|
Palauttaa sarakkeen kaikkien lukujen keskiarvon (aritmeettisen keskiarvon). |
|
|
Palauttaa sarakkeen kaikkien arvojen keskiarvon (aritmeettisen keskiarvon). Käsittelee tekstiä ja muita kuin numeerisia arvoja. |
|
|
Laskee sarakkeen numeeristen arvojen määrän. |
|
|
Laskee sarakkeen arvojen määrän, jotka eivät ole tyhjiä. |
|
|
Palauttaa sarakkeen suurimman numeerisen arvon. |
|
|
Palauttaa taulukon yli arvioidun lausekejoukon suurimman arvon. |
|
|
Palauttaa sarakkeen pienimmän numeerisen arvon. |
|
|
Palauttaa taulukon päälle arvioidun lausekejoukon pienimmän arvon. |
|
|
Laskee yhteen kaikki sarakkeen luvut. |
DAX-koostefunktiot
DAX sisältää koostefunktioita, joiden avulla voit määrittää taulukon, jossa kooste suoritetaan. Sen sijaan, että vain lisäisit tai keskiarvonisit sarakkeen arvoja, näiden funktioiden avulla voit luoda lausekkeen, joka määrittää koostettavat tiedot dynaamisesti.
Seuraavassa taulukossa on lueteltu DAX-kielessä käytettävissä olevat koostefunktiot.
|
Funktio |
Yleismerkki |
|
Laskee taulukon perusteella arvioidun lausekejoukon. |
|
|
Laskee taulukon päälle arvioidun lausekkeiden joukon. |
|
|
Laskee sarakkeen tyhjien arvojen määrän. |
|
|
Laskee taulukon rivien kokonaismäärän. |
|
|
Laskee sisäkkäisen taulukkofunktion, kuten suodatinfunktion, palauttamien rivien määrän. |
|
|
Palauttaa taulukon päälle arvioidun lausekejoukon summan. |
DAX- ja Excel Aggregation -funktioiden väliset erot
Vaikka näillä funktioilla on samat nimet kuin Excel-vastaavilla funktioilla, ne käyttävät Power Pivot muistinsisäistä analytiikkamoduulia, ja ne on kirjoitettu uudelleen taulukoiden ja sarakkeiden käyttöä varten. Et voi käyttää DAX-kaavaa Excel-työkirjassa ja päinvastoin. Niitä voi käyttää vain Power Pivot-ikkunassa ja pivot-taulukoissa, jotka perustuvat Power Pivot tietoihin. Lisäksi, vaikka funktioilla on samat nimet, toiminta voi olla hieman erilainen. Lisätietoja on yksittäisten funktioiden ohjeaiheissa.
Sarakkeiden laskentatapa koosteessa eroaa myös tavasta, jolla Excel käsittelee koosteita. Esimerkki voi auttaa havainnollistamaan.
Oletetaan, että haluat saada summan myyntitaulukon Summa-sarakkeen arvoista, joten luo seuraava kaava:
=SUM('Sales'[Amount])
Yksinkertaisin tapaus on, että funktio saa arvot yhdestä suodattamattomasta sarakkeesta, ja tulos on sama kuin Excelissä, joka laskee aina yhteen sarakkeen Arvot Määrä. Power Pivot kaava tulkitaan kuitenkin seuraavasti: "Nouda Summa-arvo myyntitaulukon kullekin riville ja laske sitten kyseiset yksittäiset arvot yhteen. Power Pivot laskee jokaisen rivin, jonka yli kooste suoritetaan, ja laskee yksittäisen skalaariarvon kullekin riville ja suorittaa sitten koosteita kyseisille arvoille. Kaavan tulos voi siis olla erilainen, jos taulukossa on käytetty suodattimia tai jos arvot lasketaan muiden suodatettavien koosteiden perusteella. Lisätietoja on kohdassa DAX-kaavojen konteksti.
DAX-aikatietofunktiot
Edellisessä osassa kuvattujen taulukon koostefunktioiden lisäksi DAX-funktioissa on koostefunktioita, jotka toimivat määrittämiesi päivämäärien ja kellonaikojen kanssa ja jotka tarjoavat valmiita aikatietoja. Nämä funktiot käyttävät päivämääräalueita toisiinsa liittyvien arvojen hakemiseen ja arvojen koostamiseen. Voit myös vertailla arvoja eri päivämääräalueissa.
Seuraavassa taulukossa on lueteltu aikatietofunktiot, joita voidaan käyttää koostamiseen.
|
Funktio |
Yleismerkki |
|
Laskee arvon annetun kauden kalenterin lopussa. |
|
|
Laskee arvon annettua kautta edeltävän kauden kalenterin lopussa. |
|
|
Laskee arvon väliltä, joka alkaa kauden ensimmäisenä päivänä ja päättyy määritetyn päivämääräsarakkeen uusimpaan päivämäärään. |
Muut Aikatietofunktiot-osan (Aikatietofunktiot) funktiot ovat funktioita, joiden avulla voidaan noutaa päivämääriä tai mukautettuja päivämääriä koosteissa käytettäviksi. Voit esimerkiksi käyttää DATESINPERIOD-funktiota päivämäärien alueen palauttamiseen ja käyttää kyseistä päivämääräjoukkoa argumenttina toiseen funktioon, kun haluat laskea mukautetun koostuksen vain näille päivämäärille.
