Turinys
Suvestinių lentelių pjaustytuvai gali būti naudojami ne tik klasikiniu būdu – šaltinio duomenims filtruoti, bet ir perjungti skirtingų tipų skaičiavimus verčių srityje:
Tai įgyvendinti labai paprasta – tereikia poros formulių ir pagalbinės lentelės. Na, o visa tai darysime ne įprastoje suvestinėje, o suvestinėje, sukurtoje pagal Power Pivot duomenų modelį.
1 veiksmas. Power Pivot priedo prijungimas
Jei „Power Pivot“ papildinio skirtukai nerodomi „Excel“, pirmiausia turėsite jį įgalinti. Tam yra dvi parinktys:
- Kortelė ryškalas - mygtukas COM priedai (Kūrėjas – COM priedai)
- Failas – Parinktys – Papildiniai – COM priedai – Eiti (Failas — Parinktys — Papildiniai — COM priedai — Eiti į)
Jei tai nepadeda, pabandykite iš naujo paleisti „Microsoft Excel“.
2 veiksmas: įkelkite duomenis į „Power Pivot“ duomenų modelį
Kaip pradinius duomenis turėsime dvi lenteles:
Pirmoji – lentelė su pardavimais, pagal kurią vėliau sukursime suvestinę. Antroji – pagalbinė lentelė, kurioje įvedami būsimo skilties mygtukų pavadinimai.
Abi šios lentelės turi būti konvertuojamos į „išmaniąsias“ (dinamiškas) naudojant sparčiuosius klavišus "Ctrl"+T arba komanda Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę) ir pageidautina duoti jiems protingus pavadinimus skirtuke Konstruktorius (Dizainas). Tegul tai būna pvz. Pardavimas и Paslaugos.
Po to kiekvieną lentelę savo ruožtu reikia įkelti į duomenų modelį - tam mes naudojame skirtuką „PowerPivot“ mygtukas Pridėti prie duomenų modelio (Pridėti prie duomenų modelio).
Iškviečiami apskaičiuoti laukai PivotTable pagal duomenų modelį priemonės. Sukurkime matą, kuris būsimoje skiltyje parodys paspausto mygtuko pavadinimą. Norėdami tai padaryti, bet kurioje iš mūsų lentelių apatiniame skaičiavimo skydelyje pasirinkite bet kurį tuščią langelį ir formulės juostoje įveskite šią konstrukciją:
Čia pirmiausia pateikiamas priemonės pavadinimas (Paspaustas mygtukas), o po dvitaškio ir lygybės ženklo – formulė, skirta apskaičiuoti naudojant funkciją VERTYBĖS DAX integruotas į Power Pivot.
Jei tai kartosite ne Power Pivot, o Power BI, dvitaškis nereikalingas ir vietoj jo VERTYBĖS galite naudoti modernesnį atitikmenį – funkciją SELECTEDVALUE.
Nekreipiame dėmesio į klaidas apatinėje lango dalyje, kurios atsiranda įvedus formulę – jos atsiranda, nes dar neturime suvestinės ir pjūvio, kuriame kažkas paspaudžiama.
Kitas žingsnis – sukurti matą skirtingoms skaičiavimo parinktims, atsižvelgiant į ankstesnio mato reikšmę Paspaustas mygtukas. Čia formulė yra šiek tiek sudėtingesnė:
Išskaidykime jį po gabalėlį:
- Funkcija JUNGIKLIS – įdėto IF analogas – tikrina nurodytų sąlygų įvykdymą ir pateikia skirtingas reikšmes, priklausomai nuo kai kurių iš jų įvykdymo.
- Funkcija TIESA() – suteikia loginį „tiesa“, kad vėliau funkcija SWITCH patikrintos sąlygos veiktų tik tada, kai jos yra įvykdytos, ty tiesa.
- Tada patikriname mygtuko paspaudimo matą ir apskaičiuojame galutinį rezultatą trims skirtingiems variantams – kaip išlaidų sumą, vidutinį patikrinimą ir unikalių vartotojų skaičių. Norėdami suskaičiuoti unikalias reikšmes, naudokite funkciją DISTINCTCOUNT, o apvalinimui – Apskrito.
- Jei netenkinama nė viena iš aukščiau išvardytų trijų sąlygų, tada rodomas paskutinis funkcijos SWITCH argumentas – naudodamiesi funkcija nustatome jį kaip manekeną TUŠČIAS().
5 veiksmas. Suvestinės kūrimas ir pjūvio pridėjimas
Belieka grįžti iš „Power Pivot“ į „Excel“ ir ten sukurti visų mūsų duomenų ir matavimų suvestinę lentelę. Norėdami tai padaryti, „Power Pivot“ lange įjunkite Pagrindinis skirtuko pasirinkimo komanda suvestinė lentelė (Pagrindinis – Suvestinė lentelė).
tada:
- Mes metame lauką Produktas iš lentelės Pardavimas į sritį Eilutės (Eilutės).
- Mesti ten lauką Pasekmė iš lentelės Paslaugos.
- Dešiniuoju pelės mygtuku spustelėkite lauką Pasekmėir pasirinkti komandą Pridėti kaip gabalėlį (Pridėti kaip pjaustyklę).
- Antros priemonės metimas Išvada iš lentelės Paslaugos į sritį Vertybės (Vertės).
Tiesą sakant, čia yra visos gudrybės. Dabar galite spustelėti pjaustytuvo mygtukus – ir suvestinės lentelės sumos persijungs į jums reikalingą funkciją.
Grožis 🙂
- „Pivot by Data Model“ pranašumai
- Plano ir fakto analizė suvestinėje lentelėje Power Pivot
- Sukurkite duomenų bazę programoje „Excel“ naudodami „Power Pivot“ priedą