Pagpapalit ng mga kalkulasyon sa isang PivotTable na may mga slicer

Maaaring gamitin ang mga slicer sa mga pivot table hindi lamang sa klasikong paraan – upang i-filter ang source data, ngunit upang lumipat din sa pagitan ng iba't ibang uri ng mga kalkulasyon sa value area:

Napakadali ng pagpapatupad nito – ang kailangan mo lang ay isang pares ng mga formula at isang auxiliary table. Well, gagawin namin ang lahat ng ito hindi sa karaniwang buod, ngunit sa buod na binuo ayon sa Power Pivot Data Model.

Hakbang 1. Pagkonekta sa Power Pivot add-in

Kung ang mga tab ng Power Pivot add-in ay hindi nakikita sa iyong Excel, kakailanganin mo muna itong paganahin. Mayroong dalawang mga pagpipilian para dito:

• Tab developer - pindutan COM add-in (Developer — COM Add-in)
• File – Opsyon – Add-in – COM Add-in – Go (File — Mga Pagpipilian — Mga Add-in — COM-Add-in — Pumunta sa)

Kung hindi ito makakatulong, subukang i-restart ang Microsoft Excel.

Hakbang 2: I-load ang Data sa Power Pivot Data Model

Magkakaroon kami ng dalawang talahanayan bilang paunang data:

Ang una ay isang talahanayan na may mga benta, ayon sa kung saan kami ay bubuo ng isang buod. Ang pangalawa ay isang auxiliary table, kung saan ang mga pangalan para sa mga pindutan ng hinaharap na slice ay ipinasok.

Ang parehong mga talahanayan na ito ay kailangang i-convert sa "smart" (dynamic) na may keyboard shortcut Ctrl+T o pangkat Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan) at ito ay kanais-nais na bigyan sila ng matinong pangalan sa tab Tagagawa (Disenyo). Hayaan ito, halimbawa, Bintahan и Serbisyo.

Pagkatapos nito, kailangang i-load ang bawat talahanayan sa Modelo ng Data - para dito ginagamit namin sa tab powerpivot butones Idagdag sa Modelo ng Data (Idagdag sa Modelo ng Data).

Hakbang 3. Gumawa ng sukatan upang matukoy ang pindutan na pinindot sa hiwa

Tinatawag ang mga kalkuladong field sa isang PivotTable ayon sa Modelo ng Data mga panukala. Gumawa tayo ng sukatan na magpapakita ng pangalan ng pinindot na button sa hinaharap na hiwa. Upang gawin ito, sa alinman sa aming mga talahanayan, pumili ng anumang walang laman na cell sa ibabang panel ng pagkalkula at ilagay ang sumusunod na konstruksyon sa formula bar:

Dito, nauuna ang pangalan ng panukat (Pinindot ang pindutan), at pagkatapos pagkatapos ng isang colon at isang pantay na tanda, isang formula upang kalkulahin ito gamit ang function Mga halaga DAX na binuo sa Power Pivot.

Kung uulitin mo ito hindi sa Power Pivot, ngunit sa Power BI, hindi na kailangan ang colon at sa halip Mga halaga maaari mong gamitin ang mas modernong katapat nito - ang function SELECTEDVALUE.

Hindi namin binibigyang-pansin ang mga error sa ibabang bahagi ng window na lumilitaw pagkatapos na ipasok ang formula - bumangon sila, dahil wala pa kaming buod at isang slice kung saan may na-click.

Hakbang 4. Gumawa ng sukat para sa pagkalkula sa pinindot na pindutan

Ang susunod na hakbang ay gumawa ng sukatan para sa iba't ibang opsyon sa pagkalkula depende sa halaga ng nakaraang sukat Pinindot ang pindutan. Narito ang formula ay medyo mas kumplikado:

Hatiin natin ito nang paisa-isa:

1. tungkulin SWITCH – isang analogue ng nested IF – sinusuri ang katuparan ng mga tinukoy na kondisyon at nagbabalik ng iba't ibang mga halaga depende sa katuparan ng ilan sa mga ito.
2. tungkulin TRUE() – nagbibigay ng lohikal na “totoo” upang ang mga kundisyon na sinuri sa ibang pagkakataon ng SWITCH function ay gagana lamang kung ang mga ito ay natutugunan, ibig sabihin, katotohanan.
3. Pagkatapos ay sinusuri namin ang halaga ng pagpindot sa pindutan ng sukat at kalkulahin ang panghuling resulta para sa tatlong magkakaibang mga opsyon - bilang ang kabuuan ng gastos, ang average na pagsusuri at ang bilang ng mga natatanging user. Upang mabilang ang mga natatanging halaga, gamitin ang function DISTINCTCOUNT, at para sa rounding – ROUND.
4. Kung wala sa tatlong kundisyon sa itaas ang natutugunan, ang huling argumento ng SWITCH function ay ipapakita – itinakda namin ito bilang dummy gamit ang function. BLANK().

Hakbang 5. Pagbuo ng buod at pagdaragdag ng isang slice

Nananatili itong bumalik mula sa Power Pivot patungo sa Excel at bumuo ng pivot table doon para sa lahat ng aming data at mga panukala. Upang gawin ito, sa Power Pivot window sa Ang pangunahing tab piliin ang utos talahanayan ng buod (Home — Pivot Table).

Pagkatapos:

1. Itapon namin ang field produkto mula sa talahanayan Bintahan sa lugar Mga hilera (Mga hilera).
2. Naghahagis ng field doon Resulta mula sa talahanayan Serbisyo.
3. Mag-right click sa field Resultaat pumili ng isang pangkat Idagdag bilang slice (Idagdag bilang Slicer).
4. Paghahagis ng pangalawang sukat Konklusyon mula sa talahanayan Serbisyo sa lugar Halaga ng (Mga halaga).

Narito, sa katunayan, ang lahat ng mga trick. Ngayon ay maaari kang mag-click sa mga pindutan ng slicer - at ang mga kabuuan sa pivot table ay lilipat sa function na kailangan mo.

Kagandahan 🙂

• Mga Benepisyo ng Pivot ayon sa Modelo ng Data
• Plan-fact analysis sa isang pivot table sa Power Pivot
• Gumawa ng database sa Excel gamit ang Power Pivot add-in