Sisu
Pivot-tabelite viilureid saab kasutada mitte ainult klassikalisel viisil – lähteandmete filtreerimiseks, vaid ka väärtusalal erinevat tüüpi arvutuste vahel vahetamiseks:
Selle rakendamine on väga lihtne – vaja läheb vaid paari valemit ja abitabelit. Noh, me teeme seda kõike mitte tavalises kokkuvõttes, vaid Power Pivoti andmemudeli järgi koostatud kokkuvõttes.
Samm 1. Power Pivoti lisandmooduli ühendamine
Kui Power Pivoti lisandmooduli vahelehed pole teie Excelis nähtavad, peate selle esmalt lubama. Selleks on kaks võimalust:
- Tab arendaja - nupp COM-i lisandmoodulid (Arendaja – COM-i lisandmoodulid)
- Fail – Valikud – Lisandmoodulid – COM-i lisandmoodulid – Mine (Fail — Valikud — Lisandmoodulid — COM-lisandmoodulid — Mine)
Kui see ei aita, proovige taaskäivitada Microsoft Excel.
2. samm: laadige andmed Power Pivoti andmemudelisse
Algandmetena on meil kaks tabelit:
Esimene on tabel müügiga, mille järgi koostame hiljem kokkuvõtte. Teine on abitabel, kuhu sisestatakse tulevase lõigu nuppude nimed.
Mõlemad tabelid tuleb klaviatuuri otsetee abil teisendada nutikaks (dünaamiliseks). Ctrl+T või meeskond Avaleht – vorminda tabelina (Avaleht — vorminda tabelina) ja vahekaardil on soovitav neile mõistlikud nimed anda Ehitaja (Kujundus). Olgu see näiteks Müük и Teenused.
Pärast seda tuleb iga tabel omakorda andmemudelisse laadida – selleks kasutame vahekaardil PowerPivot nupp Lisa andmemudelisse (Lisa andmemudelisse).
Andmemudeliga PivotTable-liigendtabeli arvutatud väljad kutsutakse välja meetmed. Loome mõõdiku, mis kuvab tulevasel lõigul vajutatud nupu nime. Selleks valige ükskõik millises meie tabelis alumisel arvutuspaneelil suvaline tühi lahter ja sisestage valemiribale järgmine konstruktsioon:
Siin on meetme nimi esimene (Vajutatud nuppu) ning seejärel kooloni ja võrdusmärgi järel valem selle arvutamiseks funktsiooni abil VÄÄRTUSED Power Pivoti sisseehitatud DAX.
Kui kordate seda mitte Power Pivotis, vaid Power BI-s, pole koolonit vaja ja selle asemel VÄÄRTUSED saate kasutada selle kaasaegsemat vastet – funktsiooni SELECTEDVALUE.
Me ei pööra tähelepanu vigadele akna alumises osas, mis ilmuvad pärast valemi sisestamist – need tekivad, sest meil pole veel kokkuvõtet ja lõiku, milles midagi klõpsatakse.
Järgmise sammuna tuleb koostada mõõt erinevate arvutusvõimaluste jaoks, olenevalt eelmise mõõdiku väärtusest Vajutatud nuppu. Siin on valem veidi keerulisem:
Jagame selle osade kaupa lahti:
- funktsioon SWITCH – pesastatud IF-i analoog – kontrollib määratud tingimuste täitmist ja tagastab olenevalt mõne tingimuste täitmisest erinevad väärtused.
- funktsioon TÕSI() – annab loogilise “tõene”, et hiljem funktsiooni SWITCH poolt kontrollitud tingimused toimiksid ainult nende täitumisel ehk tõesusel.
- Seejärel kontrollime nupuvajutuse mõõte väärtust ja arvutame lõpptulemuse kolme erineva variandi puhul – kulu, keskmise kontrolli ja unikaalsete kasutajate arvu summana. Unikaalsete väärtuste loendamiseks kasutage funktsiooni DISTINCTCOUNTja ümardamiseks – ROUND.
- Kui ükski ülaltoodud kolmest tingimusest ei ole täidetud, kuvatakse funktsiooni SWITCH viimane argument – määrame selle funktsiooni abil näitlikuks TÜHI().
5. samm. Kokkuvõtte koostamine ja lõigu lisamine
Jääb üle naasta Power Pivotist Excelisse ja koostada seal kõigi meie andmete ja meetmete jaoks pivot-tabel. Selleks lülitage Power Pivoti aknas sisse Põhiline vahekaardi valimise käsk kokkuvõtlik tabel (Avaleht – Pivot Table).
siis:
- Viskame välja Toode tabelist Müük piirkonda Read (Read).
- Sinna põllu viskamine Tulemus tabelist Teenused.
- Paremklõpsake väljal Tulemusja vali meeskond Lisa viiludeks (Lisa viilutajana).
- Teise mõõdu viskamine Järeldus tabelist Teenused piirkonda Väärtused (Väärtused).
Siin on tegelikult kõik nipid. Nüüd saate klõpsata viilutaja nuppe – ja liigendtabelis olevad kogusummad lülituvad teile vajaliku funktsiooni juurde.
Ilu 🙂
- Pivoti andmemudeli eelised
- Plaani-fakti analüüs Power Pivoti liigendtabelis
- Looge Excelis andmebaas Power Pivoti lisandmooduli abil