Pivot by Data Modelin edut

Kun rakennat pivot-taulukkoa Excelissä, aivan ensimmäisessä valintaikkunassa, jossa meitä pyydetään asettamaan aloitusalue ja valitsemaan paikka pivot-taulukon lisäämiselle, alla on huomaamaton, mutta erittäin tärkeä valintaruutu – Lisää nämä tiedot tietomalliin (Lisää nämä tiedot tietomalliin) ja hieman korkeammalle kytkin Käytä tämän kirjan tietomallia (Käytä tämän työkirjan tietomallia):

Valitettavasti monet käyttäjät, jotka ovat tunteneet pivot-taulukot pitkään ja käyttävät niitä menestyksekkäästi työssään, eivät joskus ymmärrä näiden vaihtoehtojen merkitystä eivätkä koskaan käytä niitä. Ja turhaan. Loppujen lopuksi pivot-taulukon luominen tietomallille antaa meille useita erittäin tärkeitä etuja verrattuna klassiseen Excelin pivot-taulukkoon.

Ennen kuin tarkastelemme näitä "pullia" läheltä, ymmärrämme ensin, mikä tämä tietomalli itse asiassa on?

Mikä on tietomalli

Datamalli (lyhennettynä MD tai DM = Data Model) on erityinen alue Excel-tiedoston sisällä, jonne voit tallentaa taulukkomuotoisia tietoja – yhden tai useamman taulukon, jotka on haluttaessa linkitetty toisiinsa. Itse asiassa tämä on pieni tietokanta (OLAP-kuutio), joka on upotettu Excel-työkirjaan. Verrattuna perinteiseen tietojen tallentamiseen tavallisten (tai älykkäiden) taulukoiden muodossa itse Excel-arkeille, tietomallilla on useita merkittäviä etuja:

• Pöydät voivat olla jopa 2 miljardia riviä, ja Excel-taulukkoon mahtuu hieman yli miljoona.
• Jättimäisestä koosta huolimatta tällaisten taulukoiden käsittely (suodatus, lajittelu, laskelmat, rakennusyhteenveto jne.) suoritetaan hyvin nopeasti Paljon nopeampi kuin itse Excel.
• Mallin tietojen avulla voit suorittaa lisälaskelmia (jos haluat, erittäin monimutkaisia) käyttämällä sisäänrakennettu DAX-kieli.
  
• Kaikki tietomalliin ladattu tieto on erittäin voimakkaasti puristettuna käyttämällä erityistä sisäänrakennettua arkistointia ja lisää melko maltillisesti alkuperäisen Excel-tiedoston kokoa.
  

Mallia hallinnoi ja laskee erityinen Microsoft Exceliin sisäänrakennettu apuohjelma – powerpivotjosta olen jo kirjoittanut. Ota se käyttöön välilehdellä kehittäjä napsauttaa COM-apuohjelmat (Kehittäjä – COM-apuohjelmat) ja valitse sopiva ruutu:

Jos välilehdet kehittäjä (Kehittäjä)et näe sitä nauhassa, voit kytkeä sen päälle Tiedosto – Asetukset – Nauhan asetukset (Tiedosto — Asetukset — Mukauta nauhaa). Jos sinulla ei ole Power Pivotia yllä olevassa COM-apuohjelmien luettelossa näkyvässä ikkunassa, se ei sisälly Microsoft Office -versioosi 🙁

Näkyviin tulevassa Power Pivot -välilehdessä on suuri vaaleanvihreä painike johto (Hallinnoi), jota napsauttamalla aukeaa Excelin päälle Power Pivot -ikkuna, jossa näemme nykyisen kirjan tietomallin sisällön:

Tärkeä huomautus matkan varrella: Excel-työkirja voi sisältää vain yhden tietomallin.

Lataa taulukot tietomalliin

Tietojen lataamiseksi malliin muutetaan ensin taulukko dynaamiseksi "älykkääksi" pikanäppäimeksi Ctrl+T ja anna sille ystävällinen nimi välilehdellä Rakentaja (Design). Tämä on pakollinen vaihe.

Sitten voit valita mitä tahansa kolmesta menetelmästä:

• painaa nappia Lisää malliin (Lisää tietomalliin) kieleke powerpivot kieleke Koti (Koti).
  
• Joukkueiden valinta Lisää – Pivot-taulukko (Lisää — Pivot-taulukko) ja laita valintaruutu päälle Lisää nämä tiedot tietomalliin (Lisää nämä tiedot tietomalliin). Tällöin malliin ladattujen tietojen mukaan myös pivot-taulukko rakennetaan välittömästi.
  
• Lisäasetukset -välilehdellä Päiväys (Päivämäärä) Napsauta painiketta Taulukosta/alueesta (taulukosta/alueelta)ladataksesi taulukomme Power Query -editoriin. Tämä polku on pisin, mutta täällä voit halutessasi suorittaa lisätietojen puhdistusta, muokkausta ja kaikenlaisia ​​muunnoksia, joissa Power Query on erittäin vahva.

  Sitten kampatut tiedot ladataan malliin komennolla Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje&Lataa — Sulje&Lataa...). Valitse avautuvassa ikkunassa vaihtoehto Luo vain yhteys (Luo vain yhteys) ja mikä tärkeintä, laita rasti Lisää nämä tiedot tietomalliin (Lisää nämä tiedot tietomalliin).
  

Rakennamme tietomallista yhteenvedon

Voit luoda yhteenvetotietomallin käyttämällä mitä tahansa kolmesta lähestymistavasta:

• paina nappia yhteenvetotaulukko (Pivot-taulukko) Power Pivot -ikkunassa.
• Valitse komennot Excelissä Lisää – Pivot-taulukko ja vaihda tilaan Käytä tämän kirjan tietomallia (Insert — Pivot Table — Käytä tämän työkirjan tietomallia).
• Joukkueiden valinta Lisää – Pivot-taulukko (Lisää — Pivot-taulukko) ja laita valintaruutu päälle Lisää nämä tiedot tietomalliin (Lisää nämä tiedot tietomalliin). Nykyinen "älykäs" taulukko ladataan malliin ja yhteenvetotaulukko rakennetaan koko mallille.

Nyt kun olemme selvittäneet, kuinka ladata tietoja tietomalliin ja tehdä siitä yhteenveto, tutkitaanpa tämän meille tuomia etuja ja etuja.

Hyöty 1: Taulukoiden väliset suhteet ilman kaavoja

Säännöllinen yhteenveto voidaan rakentaa vain yhden lähdetaulukon tiedoista. Jos sinulla on niitä useita, esimerkiksi myynti, hinnasto, asiakashakemisto, sopimusrekisteri jne., sinun on ensin kerättävä tiedot kaikista taulukoista yhdeksi toiminnolla, kuten VLOOKUP. (HAKU), INDEKSI (INDEKSI), LISÄTIETOJA (OTTELU), SUMMESLIMN (SUMIFS) ja vastaavat. Tämä on pitkä, työlästä ja ajaa Excelin "ajatukseen", jossa on paljon tietoa.

Tietomallin yhteenvedon tapauksessa kaikki on paljon yksinkertaisempaa. Riittää, kun määrität taulukoiden väliset suhteet kerran Power Pivot -ikkunassa – ja se on valmis. Voit tehdä tämän välilehdellä powerpivot painaa nappia johto (Hallinnoi) ja sitten näkyviin tulevassa ikkunassa - painike Kaavionäkymä (Kaavionäkymä). Jäljellä on vetämällä yleiset (avain) sarakkeiden nimet (kentät) taulukoiden välillä linkkien luomiseksi:

Tämän jälkeen tietomallin yhteenvedossa voit heittää yhteenvetoalueelle (rivit, sarakkeet, suodattimet, arvot) mitä tahansa kenttiä kaikista aiheeseen liittyvistä taulukoista – kaikki linkitetään ja lasketaan automaattisesti:

Hyöty 2: Laske yksilölliset arvot

Tavallinen pivot-taulukko antaa meille mahdollisuuden valita yksi useista sisäänrakennetuista laskentafunktioista: summa, keskiarvo, määrä, minimi, maksimi jne. Tietomallin yhteenvedossa tähän vakioluetteloon on lisätty erittäin hyödyllinen funktio laskemaan yksilöllisten (ei-toistuvien arvojen) määrä. Sen avulla voit esimerkiksi laskea helposti myymiemme yksilöllisten tavaroiden (valikoiman) määrän kussakin kaupungissa.

Napsauta hiiren kakkospainikkeella kenttää - komento Arvokentän asetukset ja välilehdellä Toiminta Valita Eri elementtien lukumäärä (Erillinen määrä):

Hyöty 3: mukautetut DAX-kaavat

Joskus joudut tekemään erilaisia ​​lisälaskelmia pivot-taulukoissa. Tavallisissa yhteenvedoissa tämä tehdään laskettujen kenttien ja objektien avulla, kun taas tietomallin yhteenvedossa käytetään mittauksia erityisellä DAX-kielellä (DAX = Data Analysis Expressions).

Luo mittaus valitsemalla välilehdeltä powerpivot Komento Toimenpiteet – Luo mitta (Toimenpiteet – Uusi toimenpide) tai napsauta hiiren kakkospainikkeella taulukkoa Pivot Fields -luettelossa ja valitse Lisää mitta (Lisää mitta) kontekstivalikossa:

Aseta avautuvassa ikkunassa:

• Taulukon nimimihin luotu mitta tallennetaan.
• Mittauksen nimi – mikä tahansa nimi, jonka ymmärrät uudelle kentälle.
• Kuvaus - valinnainen.
• Kaava – tärkein asia, koska täällä syötetään joko manuaalisesti tai napsautetaan painiketta fx ja valitse luettelosta DAX-funktio, jonka pitäisi laskea tulos, kun sitten heitämme mittamme Arvot-alueelle.
• Ikkunan alaosassa voit heti asettaa listassa olevan suuren numeromuodon Kategoria.
  

DAX-kieli ei ole aina helppo ymmärtää, koska se ei toimi yksittäisillä arvoilla, vaan kokonaisilla sarakkeilla ja taulukoilla, eli vaatii ajattelun uudelleenjärjestelyä klassisten Excel-kaavojen jälkeen. Se on kuitenkin sen arvoista, koska sen kykyjä käsitellä suuria tietomääriä on vaikea yliarvioida.

Hyöty 4: Mukautetut kenttähierarkiat

Usein vakioraportteja luotaessa joudut heittämään samat kenttien yhdistelmät pivot-taulukoihin tietyssä järjestyksessä, esim. Vuosi-neljännes-kuukausi-päivätai Luokka-Tuotetai Maa-kaupunki-asiakas jne. Tietomallin yhteenvedossa tämä ongelma on helppo ratkaista luomalla oma hierarkiat — mukautetut kenttäsarjat.

Siirry Power Pivot -ikkunassa karttatilaan -painikkeella Kaavionäkymä kieleke Koti (Etusivu — Kaavionäkymä), valitse painikkeella Ctrl haluamasi kentät ja napsauta niitä hiiren kakkospainikkeella. Kontekstivalikko sisältää komennon Luo hierarkia (Luo hierarkia):

Luotu hierarkia voidaan nimetä uudelleen ja vetää siihen hiirellä tarvittavat kentät, jotta ne voidaan myöhemmin yhdessä liikkeessä heittää yhteenvetoon:

Etu 5: Mukautetut stensiilit

Jatkaen edellisen kappaleen ajatusta, Datamallin yhteenvedossa voit myös luoda omat elementtijoukot jokaiselle kenttään. Esimerkiksi koko kaupunkiluettelosta voit helposti tehdä joukon vain niistä, jotka ovat vastuualueellasi. Tai kerää vain asiakkaasi, tavarasi jne. erityiseen sarjaan.

Voit tehdä tämän välilehdellä Pivot-taulukon analyysi avattavassa luettelossa Kentät, kohteet ja joukot siellä on vastaavat komennot (Analysoi – Fields, Items & Sets — Luo sarja rivi-/sarakekohtien perusteella):

Avautuvassa ikkunassa voit valikoivasti poistaa, lisätä tai muuttaa minkä tahansa elementin sijaintia ja tallentaa tuloksena olevan joukon uudella nimellä:

Kaikki luodut joukot näkyvät PivotTable Fields -paneelissa erillisessä kansiossa, josta ne voidaan vapaasti vetää minkä tahansa uuden pivot-taulukon rivi- ja sarakealueille:

Hyöty 6: Piilota taulukot ja sarakkeet valikoivasti

Vaikka tämä on pieni, mutta erittäin miellyttävä etu joissakin tapauksissa. Napsauta hiiren kakkospainikkeella kentän nimeä tai taulukkovälilehteä Power Pivot -ikkunassa, voit valita komennon Piilota Client Toolkitista (Piilota asiakastyökaluista):

Piilotettu sarake tai taulukko katoaa Pivot-taulukon kenttäluetteloruudusta. Se on erittäin kätevää, jos haluat piilottaa käyttäjältä joitain apusarakkeita (esimerkiksi laskettuja tai sarakkeita, joissa on avainarvot suhteiden luomiseen) tai jopa kokonaisia ​​taulukoita.

Hyöty 7. Edistynyt drill-down

Jos kaksoisnapsautat mitä tahansa solua arvoalueella tavallisessa pivot-taulukossa, Excel näyttää erillisellä arkilla kopion lähdetietofragmentista, joka osallistui tämän solun laskemiseen. Tämä on erittäin kätevä asia, jota kutsutaan virallisesti Drill-downiksi (yleensä sanotaan "epäonnistua").

Tietomallin yhteenvedossa tämä kätevä työkalu toimii hienovaraisemmin. Seisomalla minkä tahansa solun päällä, jolla on meitä kiinnostava tulos, voit napsauttaa sen viereen ponnahtavan suurennuslasin kuvaketta (se on ns. Express Trends) ja valitse sitten mikä tahansa sinua kiinnostava kenttä mistä tahansa liittyvästä taulukosta:

Sen jälkeen nykyinen arvo (malli = Explorer) menee suodatinalueelle ja yhteenveto rakennetaan toimistoittain:

Tietenkin tällainen menettely voidaan toistaa useita kertoja, syventämällä jatkuvasti tietojasi sinua kiinnostavaan suuntaan.

Hyöty 8: Muunna Pivot kuutiofunktioiksi

Jos valitset tietomallin yhteenvedosta minkä tahansa solun ja valitset sitten välilehdeltä Pivot-taulukon analyysi Komento OLAP-työkalut – Muunna kaavoiksi (Analysoi — OLAP-työkalut — Muunna kaavoiksi), koko yhteenveto muunnetaan automaattisesti kaavoiksi. Nyt rivi-sarake-alueen kenttäarvot ja arvoalueen tulokset haetaan tietomallista käyttämällä erityisiä kuutiofunktioita: CUBEVALUE ja CUBEMBER:

Teknisesti tämä tarkoittaa, että nyt ei ole kyse yhteenvedosta, vaan useista soluista kaavoineen, eli voimme helposti tehdä raportillamme sellaisia ​​muunnoksia, joita ei ole yhteenvedossa, esim. lisätä uusia rivejä tai sarakkeita keskelle raportista, tee mahdolliset lisälaskelmat yhteenvedon sisällä, järjestä ne haluamallasi tavalla jne.

Samalla yhteys lähdetietoihin tietysti säilyy ja jatkossa näitä kaavoja päivitetään lähteiden vaihtuessa. Kaunotar!

• Suunnitelma-fakta-analyysi pivot-taulukossa Power Pivotilla ja Power Queryllä
• Pivot-taulukko monirivisellä otsikolla
• Luo tietokanta Excelissä Power Pivotilla