Ukushintsha izibalo ku-PivotTable ngezisiki

Izisiki kumathebula e-pivot zingasetshenziswa hhayi kuphela ngendlela yakudala - ukuhlunga idatha yomthombo, kodwa futhi ukushintsha phakathi kwezinhlobo ezahlukene zokubala endaweni yenani:

Ukwenza lokhu kulula kakhulu - okudingayo amafomula ambalwa kanye netafula elisizayo. Nokho, sizokwenza konke lokhu hhayi ngesifinyezo esivamile, kodwa esifinyezweni esakhiwe ngokuvumelana ne-Power Pivot Data Model.

Isinyathelo 1. Ukuxhuma isengezo se-Power Pivot

Uma amathebhu esengezo se-Power Pivot engabonakali ku-Excel yakho, uzodinga kuqala ukuthi uyinike amandla. Kunezinketho ezimbili zalokhu:

  • Ithebhu Unjiniyela - Inkinobho Izengezo ze-COM (Unjiniyela - Izengezo ze-COM)
  • Ifayela - Izinketho - Izengezo - Izengezo ze-COM - Hamba (Ifayela — Izinketho — Izengezo — I-COM-Add-ins — Yiya ku)

Uma lokhu kungasizi, zama ukuqala kabusha i-Microsoft Excel.

Isinyathelo sesi-2: Layisha Idatha Kumodeli Yedatha Ye-Pivot Yamandla

Sizoba namathebula amabili njengedatha yokuqala:

Ukushintsha izibalo ku-PivotTable ngezisiki

Eyokuqala itafula elinokuthengiswa, ngokusho lokho esizokwakha kamuva isifinyezo. Okwesibili itafula elisizayo, lapho amagama ezinkinobho zocezu oluzayo afakwa khona.

Womabili lawa mathebula adinga ukuguqulwa abe “smart” (dynamic) ngesinqamuleli sekhibhodi Ctrl+T noma iqembu Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula) futhi kuyafiseleka ukubanika amagama ahlakaniphile kuthebhu umakhi (Umklamo). Makube, isibonelo, Sales и Imisebenzi.

Ngemuva kwalokho, ithebula ngalinye lidinga ukulayishwa kumodeli Yedatha - kulokhu sisebenzisa kuthebhu I-Powerpivot inkinobho Engeza Kumodeli Yedatha (Engeza Kumodeli Yedatha).

Isinyathelo sesi-3. Dala isilinganiso ukuze uthole inkinobho ecindezelwe ocezwini

Izinkambu ezibaliwe ku-PivotTable by Data Model zibizwa izinyathelo. Masidale isilinganiso esizobonisa igama lenkinobho ecindezelwe ocezwini oluzayo. Ukwenza lokhu, kunoma yiliphi ithebula lethu, khetha noma iyiphi iseli engenalutho kuphaneli yokubala ephansi bese ufaka ukwakhiwa okulandelayo kubha yefomula:

Ukushintsha izibalo ku-PivotTable ngezisiki

Lapha, igama lesilinganiso liza kuqala (Cindezelwe inkinobho), bese ngemva kwekholoni nophawu olulinganayo, ifomula yokubala kusetshenziswa umsebenzi AMAHLUKA I-DAX yakhelwe ku-Power Pivot.

Uma uphinda lokhu hhayi ku-Power Pivot, kodwa ku-Power BI, khona-ke ikholoni ayidingeki futhi esikhundleni salokho AMAHLUKA ungasebenzisa umlingani wayo wesimanje kakhulu - umsebenzi SELECTEDVALUE.

Asinaki amaphutha engxenyeni engezansi yefasitela evela ngemva kokufaka ifomula - avela, ngoba asikabi naso isifinyezo kanye nocezu lapho okuthile kuchofozwa khona.

Isinyathelo sesi-4. Dala isilinganiso sokubala enkinobho ecindezelweyo

Isinyathelo esilandelayo siwukwenza isilinganiso sezinketho ezahlukene zokubala kuye ngenani lesilinganiso sangaphambilini Cindezelwe inkinobho. Nansi ifomula iyinkimbinkimbi kakhulu:

Ukushintsha izibalo ku-PivotTable ngezisiki

Masiyihlephule isiqeshana ngesiqephu:

  1. Umsebenzi SHINTSHA - i-analogue ye-IF efakwe isidleke - ihlola ukugcwaliseka kwezimo ezishiwo futhi ibuyisele amanani ahlukene ngokuya ngokugcwaliseka kwamanye awo.
  2. Umsebenzi IQINISO() - inikeza "iqiniso" elinengqondo ukuze izimo ezihlolwe kamuva umsebenzi we-SWITCH zisebenze kuphela uma kuhlangatshezwana nazo, okungukuthi iqiniso.
  3. Bese sihlola inani lenkinobho ecindezelwe isilinganiso bese sibala umphumela wokugcina wezinketho ezintathu ezihlukene - njengesamba sezindleko, isheke elimaphakathi kanye nenani labasebenzisi abahlukile. Ukuze ubale amanani ahlukile, sebenzisa umsebenzi DISTINCTCOUNT, nokuhlanganisa - ROUND.
  4. Uma kungekho kwezimo ezintathu ezingenhla okuhlangatshezwana nazo, khona-ke impikiswano yokugcina yomsebenzi we-SWITCH iyaboniswa - siyibeka njenge-dummy sisebenzisa umsebenzi. AKUBHEKI().

Isinyathelo sesi-5. Ukwakha isifinyezo nokwengeza ucezu

Kusele ukubuya ku-Power Pivot kuye ku-Excel futhi sakhe ithebula le-pivot lapho layo yonke idatha yethu nezilinganiso. Ukuze wenze lokhu, efasiteleni le-Pivot yamandla livuliwe The main ithebhu khetha umyalo isifinyezo ithebula (Ekhaya — Ithebula Le-Pivot).

Khona-ke:

  1. Siphonsa insimu Product etafuleni Sales endaweni Imigqa (Imigqa).
  2. Ukujikijela insimu lapho Result etafuleni Imisebenzi.
  3. Chofoza kwesokudla enkundleni Resultbese ukhetha iqembu Engeza njengocezu (Engeza Njengosisiki).
  4. Ukuphonsa isilinganiso sesibili Isiphetho etafuleni Imisebenzi endaweni Izindinganiso (Amanani).

Lapha, empeleni, kukhona wonke amaqhinga. Manje ungachofoza izinkinobho zokusika - futhi amanani ethebula le-pivot azoshintshela kumsebenzi owudingayo.

Ubuhle 🙂

  • Izinzuzo ze-Pivot ngemodeli yedatha
  • Ukuhlaziywa kweqiniso lokuhlela kuthebula le-pivot ku-Power Pivot
  • Dala isizindalwazi ku-Excel usebenzisa isengezo se-Power Pivot

 

shiya impendulo