Pivoti andmemudeli eelised

Excelis pivot-tabeli koostamisel on kõige esimeses dialoogiboksis, kus meil palutakse määrata algvahemik ja valida liigendtabeli sisestamise koht, all silmapaistmatu, kuid väga oluline märkeruut – Lisage need andmed andmemudelisse (Lisage need andmed andmemudelile) ja veidi kõrgemal, lüliti Kasutage selle raamatu andmemudelit (Kasutage selle töövihiku andmemudelit):

Kahjuks ei mõista paljud kasutajad, kes on pivot-tabelitega pikka aega tuttavad ja neid oma töös edukalt kasutavad, mõnikord nende valikute tähendusest aru ega kasuta neid kunagi. Ja asjata. Lõppude lõpuks annab andmemudeli pivot-tabeli loomine meile mitmeid väga olulisi eeliseid võrreldes klassikalise Exceli pivot-tabeliga.

Enne nende "kuklite" lähedalt kaalumist mõelgem aga kõigepealt, mis see andmemudel tegelikult on?

Mis on andmemudel

Andmemudel (lühendatult MD või DM = andmemudel) on spetsiaalne ala Exceli faili sees, kuhu saab salvestada tabeliandmeid – ühe või mitu tabelit, mis on soovi korral omavahel lingitud. Tegelikult on see väike andmebaas (OLAP-kuubik), mis on manustatud Exceli töövihikusse. Võrreldes andmete klassikalise salvestamisega tavaliste (või nutikate) tabelite kujul Exceli enda lehtedel, on andmemudelil mitmeid olulisi eeliseid:

• Tabelid võivad olla kuni 2 miljardit rida, ja Exceli lehele mahub veidi rohkem kui 1 miljon.
• Vaatamata hiiglaslikule suurusele toimub selliste tabelite töötlemine (filtreerimine, sorteerimine, nende põhjal arvutamine, hoone kokkuvõte jne) väga kiiresti Palju kiirem kui Excel ise.
• Mudelis olevate andmetega saate teha täiendavaid (soovi korral väga keerulisi) arvutusi kasutades sisseehitatud DAX-i keel.
  
• Kogu andmemudelisse laaditud teave on väga tugevalt kokku surutud kasutades spetsiaalset sisseehitatud arhiveerijat ja suurendab üsna mõõdukalt algse Exceli faili suurust.
  

Mudelit haldab ja arvutab spetsiaalne Microsoft Excelisse sisseehitatud lisandmoodul – PowerPivotmillest olen juba kirjutanud. Selle lubamiseks vahekaardil arendaja klõps COM-i lisandmoodulid (Arendaja – COM-i lisandmoodulid) ja märkige vastav ruut:

Kui vahelehed arendaja (Arendaja)te ei näe seda lindil, saate selle sisse lülitada Fail – Valikud – Lindi seadistamine (Fail — Valikud — Kohanda linti). Kui teil pole ülaltoodud COM-i lisandmoodulite loendis kuvatud aknas Power Pivoti, pole see teie Microsoft Office'i versioonis kaasas 🙁

Ilmuval Power Pivoti vahekaardil on suur heleroheline nupp juhtimine (Halda), millel klõpsates avaneb Exceli peal Power Pivoti aken, kus näeme praeguse raamatu andmemudeli sisu:

Oluline märkus: Exceli töövihik võib sisaldada ainult ühte andmemudelit.

Laadige tabelid andmemudelisse

Andmete mudelisse laadimiseks muudame tabeli esmalt dünaamiliseks "nutikaks" kiirklahviks Ctrl+T ja andke sellele vahekaardil sõbralik nimi Ehitaja (Kujundus). See on vajalik samm.

Seejärel saate valida ühe kolmest meetodist.

• Vajutage nuppu Lisa mudelisse (Lisa andmemudelisse) tab PowerPivot tab Avaleht (Kodu).
  
• Meeskondade valimine Sisesta – PivotTable (Sisesta – Pivot Table) ja lülitage märkeruut sisse Lisage need andmed andmemudelisse (Lisage need andmed andmemudelisse). Sel juhul ehitatakse vastavalt Mudelisse laetud andmetele kohe ka pivot tabel.
  
• Vahekaardil Täpsemalt kuupäev (Kuupäev) klõpsa nupul Tabelist/vahemikust (Tabelist/vahemikust)et laadida meie tabel Power Query redaktorisse. See tee on pikim, kuid soovi korral saab siin teha täiendavat andmete puhastamist, redigeerimist ja kõikvõimalikke teisendusi, milles Power Query on väga tugev.

  Seejärel laaditakse kammitud andmed käsuga üles Modelli Avaleht — sulge ja laadi — sulge ja laadi... (Avaleht — Sulge&laadi — Sule&laadi...). Avanevas aknas valige suvand Looge lihtsalt ühendus (Loo ainult ühendus) ja mis kõige tähtsam, pane linnuke Lisage need andmed andmemudelisse (Lisage need andmed andmemudelisse).
  

Koostame andmemudeli kokkuvõtte

Kokkuvõtliku andmemudeli koostamiseks võite kasutada ühte kolmest lähenemisviisist.

• vajuta nuppu kokkuvõtlik tabel (liigendtabel) Power Pivoti aknas.
• Valige Excelis käsud Sisesta – PivotTable ja lülitage režiimi Kasutage selle raamatu andmemudelit (Insert — Pivot Table — selle töövihiku andmemudeli kasutamine).
• Meeskondade valimine Sisesta – PivotTable (Sisesta – Pivot Table) ja lülitage märkeruut sisse Lisage need andmed andmemudelisse (Lisage need andmed andmemudelisse). Praegune "nutikas" tabel laaditakse mudelisse ja kogu mudeli jaoks koostatakse kokkuvõtlik tabel.

Nüüd, kui oleme välja mõelnud, kuidas andmeid andmemudelisse laadida ja sellest kokkuvõtet koostada, uurime selle eeliseid ja eeliseid.

Kasu 1: tabelitevahelised seosed ilma valemeid kasutamata

Tavalise kokkuvõtte saab koostada ainult ühe lähtetabeli andmete põhjal. Kui teil on neid mitu, näiteks müük, hinnakiri, kliendikataloog, lepingute register jne, siis peate esmalt koguma andmed kõigist tabelitest ühte, kasutades selliseid funktsioone nagu VLOOKUP (OTSING), INDEX (INDEX), ROHKEM AVALDATUD (MATCH), SUMMESLIMN (SUMIFS) ja muud taolist. See on pikk, tüütu ja ajab teie Exceli suure andmemahuga "mõttesse".

Andmemudeli kokkuvõtte puhul on kõik palju lihtsam. Piisab, kui seadistada tabelitevahelised seosed üks kord Power Pivoti aknas – ja ongi tehtud. Selleks vahekaardil PowerPivot vajuta nuppu juhtimine (Halda) ja seejärel ilmuvas aknas – nupp Diagrammi vaade (Skeemi vaade). Linkide loomiseks tuleb lohistada tavalised (võtme) veergude nimed (väljad) tabelite vahel:

Pärast seda saate andmemudeli kokkuvõttes visata kokkuvõttealale (read, veerud, filtrid, väärtused) mis tahes väljad mis tahes seotud tabelitest – kõik lingitakse ja arvutatakse automaatselt:

2. eelis: loendage kordumatuid väärtusi

Tavaline pivot-tabel annab meile võimaluse valida ühe mitmest sisseehitatud arvutusfunktsioonist: summa, keskmine, loendus, miinimum, maksimum jne. Andmemudeli kokkuvõttes on sellesse standardloendisse lisatud väga kasulik funktsioon arvutuste loendamiseks. kordumatute (mittekorduvate väärtuste) arv. Selle abiga saate näiteks hõlpsasti kokku lugeda, kui palju unikaalseid kaubaartikleid (sortimenti) igas linnas müüme.

Paremklõpsake väljal – käsk Väärtuse välja valikud ja vahekaardil töö Vali Erinevate elementide arv (Eriline arv):

3. eelis: kohandatud DAX-i valemid

Mõnikord tuleb pivot-tabelites teha erinevaid lisaarvutusi. Tavalistes kokkuvõtetes kasutatakse selleks arvutatud välju ja objekte, samas kui andmemudeli kokkuvõttes kasutatakse mõõte spetsiaalses DAX-keeles (DAX = Data Analysis Expressions).

Mõõtme loomiseks valige vahekaardil PowerPivot käsk Meetmed – looge meede (Meetmed – uus meede) või lihtsalt paremklõpsake loendis Pivot Fields tabelit ja valige Lisa mõõt (Lisa mõõt) kontekstimenüüs:

Avanevas aknas määrake:

• Tabeli nimikuhu loodud mõõt salvestatakse.
• Mõõtmise nimi – mis tahes nimi, millest uue välja puhul aru saate.
• Kirjeldus – valikuline.
• Valem – kõige tähtsam, sest siin me kas sisestame käsitsi või klõpsame nuppu fx ja valige loendist DAX-i funktsioon, mis peaks arvutama tulemuse, kui viskame oma mõõdiku väärtuste piirkonda.
• Akna alumises osas saab kohe määrata loendis oleva mõõdiku numbrivormingu Kategooria.
  

DAX-keelt ei ole alati lihtne mõista, kuna see ei opereeri üksikute väärtustega, vaid tervete veergude ja tabelitega, st nõuab klassikaliste Exceli valemite järel mõningast mõtlemise ümberstruktureerimist. Kuid see on seda väärt, sest selle võimekust suurte andmemahtude töötlemisel on raske üle hinnata.

Kasu 4: kohandatud väljade hierarhiad

Tihti tuleb standardaruannete loomisel visata samu väljakombinatsioone antud järjestuses pivot-tabelitesse, näiteks Aasta-veerand-kuu-päevvõi Kategooria-toodevõi Riik-linn-klient jne Andmemudeli kokkuvõttes on see probleem hõlpsasti lahendatav, luues oma hierarhiad — kohandatud väljakomplektid.

Lülitage Power Pivoti aknas nupuga diagrammirežiimi Diagrammi vaade tab Avaleht (Avaleht – diagrammivaade), valige nupuga Ctrl soovitud väljad ja paremklõpsake neid. Kontekstimenüü sisaldab käsku Loo hierarhia (Loo hierarhia):

Loodud hierarhiat saab ümber nimetada ja sinna hiirega lohistada vajalikud väljad, et need hiljem ühe liigutusega kokkuvõttesse visata:

Kasu 5: kohandatud šabloonid

Jätkates eelmise lõigu ideed, saate andmemudeli kokkuvõttes luua iga välja jaoks ka oma elementide komplektid. Näiteks saate kogu linnade loendist hõlpsasti koostada komplekti ainult nendest, mis kuuluvad teie vastutusalasse. Või koguge spetsiaalsesse komplekti ainult oma kliendid, oma kaubad jne.

Selleks vahekaardil Pivot tabeli analüüs ripploendis Väljad, elemendid ja komplektid seal on vastavad käsud (Analüüsi – Fields, Ielemendid ja komplektid – looge komplekt rea/veeru üksuste põhjal):

Avanevas aknas saate mistahes elemente valikuliselt eemaldada, lisada või muuta nende asukohta ning salvestada saadud komplekti uue nimega:

Kõik loodud komplektid kuvatakse PivotTable-liigendtabeli väljade paneelil eraldi kaustas, kust saab neid vabalt lohistada mis tahes uue PivotTable-liigendtabeli ridade ja veergude aladele:

6. eelis: tabelite ja veergude valikuline peitmine

Kuigi see on väike, kuid mõnel juhul väga meeldiv eelis. Paremklõpsates Power Pivoti aknas välja nimel või tabeli vahekaardil, saate valida käsu Peida kliendi tööriistakomplektist (Peida klienditööriistade eest):

Peidetud veerg või tabel kaob PivotTable-liigendtabeli väljaloendi paanilt. See on väga mugav, kui peate kasutaja eest peitma mõned abiveerud (näiteks arvutatud või suhete loomise võtmeväärtustega veerud) või isegi terveid tabeleid.

Kasu 7. Täiustatud drill-down

Kui topeltklõpsate tavaliigendtabeli väärtusala mis tahes lahtril, kuvab Excel eraldi lehel koopia lähteandmete fragmendist, mis oli kaasatud selle lahtri arvutamisse. See on väga mugav asi, mida ametlikult nimetatakse Drill-downiks (tavaliselt öeldakse "ebaõnnestumine").

Andmemudeli kokkuvõttes töötab see mugav tööriist peenemalt. Seistes mis tahes meid huvitava tulemusega lahtril, saate klõpsata selle kõrvale hüppava suurendusklaasiga ikooni (seda nimetatakse Väljendage suundumusi) ja seejärel valige mis tahes seotud tabelist mis tahes väli, millest olete huvitatud:

Pärast seda läheb praegune väärtus (mudel = Explorer) filtrialasse ja kokkuvõte koostatakse kontorite kaupa:

Loomulikult saab sellist protseduuri korrata mitu korda, süvenedes järjekindlalt oma andmetesse teid huvitavas suunas.

8. eelis: teisendage Pivot kuubikufunktsioonideks

Kui valite andmemudeli kokkuvõttes suvalise lahtri ja seejärel vahekaardil Pivot tabeli analüüs käsk OLAP-i tööriistad – teisendage valemiteks (Analüüsi – OLAP-i tööriistad – teisenda valemiteks), siis teisendatakse kogu kokkuvõte automaatselt valemiteks. Nüüd tuuakse andmemudelist välja rea-veeru ala välja väärtused ja väärtuste ala tulemused, kasutades spetsiaalseid kuubifunktsioone: CUBEVALUE ja CUBEMBER:

Tehniliselt tähendab see seda, et praegu ei ole tegemist mitte kokkuvõttega, vaid mitme valemitega lahtriga, st saame oma aruandega hõlpsasti teha mis tahes teisendusi, mida kokkuvõttes pole, näiteks sisestada keskele uued read või veerud. aruandes, tehke kokkuvõtte sees täiendavaid arvutusi, korraldage need soovitud viisil jne.

Samas seos lähteandmetega loomulikult säilib ja edaspidi uuendatakse neid valemeid allikate muutumisel. Ilu!

• Plaani-fakti analüüs liigendtabelis Power Pivoti ja Power Queryga
• Pivot-tabel mitmerealise päisega
• Looge Excelis andmebaas Power Pivoti abil