Okuqukethwe
- Isinyathelo 1. Ukuxhuma isengezo se-Power Pivot
- Isinyathelo sesi-2: Layisha Idatha Kumodeli Yedatha Ye-Pivot Yamandla
- Isinyathelo sesi-3. Dala isilinganiso ukuze uthole inkinobho ecindezelwe ocezwini
- Isinyathelo sesi-4. Dala isilinganiso sokubala enkinobho ecindezelweyo
- Isinyathelo sesi-5. Ukwakha isifinyezo nokwengeza ucezu
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:
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).
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:
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 esilandelayo siwukwenza isilinganiso sezinketho ezahlukene zokubala kuye ngenani lesilinganiso sangaphambilini Cindezelwe inkinobho. Nansi ifomula iyinkimbinkimbi kakhulu:
Masiyihlephule isiqeshana ngesiqephu:
- Umsebenzi SHINTSHA - i-analogue ye-IF efakwe isidleke - ihlola ukugcwaliseka kwezimo ezishiwo futhi ibuyisele amanani ahlukene ngokuya ngokugcwaliseka kwamanye awo.
- Umsebenzi IQINISO() - inikeza "iqiniso" elinengqondo ukuze izimo ezihlolwe kamuva umsebenzi we-SWITCH zisebenze kuphela uma kuhlangatshezwana nazo, okungukuthi iqiniso.
- 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.
- 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:
- Siphonsa insimu Product etafuleni Sales endaweni Imigqa (Imigqa).
- Ukujikijela insimu lapho Result etafuleni Imisebenzi.
- Chofoza kwesokudla enkundleni Resultbese ukhetha iqembu Engeza njengocezu (Engeza Njengosisiki).
- 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