Izinzuzo ze-Pivot ngemodeli yedatha

Lapho wakha ithebula le-pivot ku-Excel, ebhokisini lengxoxo yokuqala ngqá, lapho sicelwa khona ukuthi sisethe ububanzi bokuqala bese sikhetha indawo yokufaka ithebula le-pivot, kunebhokisi lokuhlola elingacacile kodwa elibaluleke kakhulu ngezansi - Engeza le datha Kumodeli Yedatha (Engeza le datha kuya kumodeli yedatha) futhi, phezulu kancane, ukushintsha Sebenzisa imodeli yedatha yaleli bhuku (Sebenzisa Imodeli Yedatha yale ncwadi yokusebenzela):

Ngeshwa, abasebenzisi abaningi abajwayelene namathebula e-pivot isikhathi eside futhi bawasebenzisa ngempumelelo emsebenzini wabo ngezinye izikhathi abaqondi kahle incazelo yalezi zinketho futhi abalokothi bawasebenzise. Futhi ngeze. Phela, ukudala ithebula le-pivot lemodeli yedatha kusinikeza izinzuzo ezimbalwa ezibaluleke kakhulu uma kuqhathaniswa nethebula le-pivot le-Excel lakudala.

Nokho, ngaphambi kokucabangela lawa “mabhanisi” eduze, ake siqale siqonde ukuthi iyini, empeleni, iyini le Data Model?

Iyini Imodeli Yedatha

Imodeli Yedatha (efushanisiwe njenge-MD noma i-DM = Imodeli Yedatha) iyindawo ekhethekile ngaphakathi kwefayela le-Excel lapho ungagcina khona idatha yethebula - ithebula elilodwa noma amaningi axhumene, uma uthanda, kwelinye. Eqinisweni, lena isizindalwazi esincane (ikhiyubhu ye-OLAP) eshumekwe ngaphakathi kwencwadi yokusebenzela ye-Excel. Uma kuqhathaniswa nokugcinwa kwedatha yakudala ngendlela yamathebula avamile (noma ahlakaniphile) eshidini le-Excel ngokwayo, Imodeli Yedatha inezinzuzo ezimbalwa ezibalulekile:

• Amathebula angaba phezulu 2 billion imigqa, futhi ishidi le-Excel lingalingana ngaphezudlwana kwesigidi esingu-1.
• Naphezu kobukhulu obukhulu, ukucutshungulwa kwamatafula anjalo (ukuhlunga, ukuhlunga, izibalo kuwo, isifinyezo sesakhiwo, njll.) kwenziwa. ngokushesha Ishesha kakhulu kune-Excel ngokwayo.
• Ngedatha ekuModel, ungenza izibalo ezengeziwe (uma uthanda, eziyinkimbinkimbi kakhulu) usebenzisa Ulimi lwe-DAX olwakhelwe ngaphakathi.
  
• Lonke ulwazi olulayishwe kumodeli yedatha lukhulu kakhulu kucindezelwe kakhulu usebenzisa i-archiver ekhethekile eyakhelwe ngaphakathi futhi kunalokho ukwandisa ngokulingene usayizi wefayela langempela le-Excel.
  

Imodeli ilawulwa futhi ibalwa ngezengezo ezikhethekile ezakhelwe ku-Microsoft Excel - I-Powerpivotesengibhalile ngakho. Ukuze uyinike amandla, kuthebhu Unjiniyela ukuchofoza Izengezo ze-COM (Unjiniyela - Izengezo ze-COM) bese uthikha ibhokisi elifanele:

Uma amathebhu Unjiniyela (Unjiniyela)awukwazi ukuyibona kuribhoni, ungayivula Ifayela – Izinketho – Ukusethwa kweRibhoni (Ifayela — Izinketho — Yenza Iribhoni Ngokwezifiso). Uma efasiteleni eliboniswe ngenhla kuhlu lwezengezo ze-COM ungenayo i-Power Pivot, lapho-ke ayifakiwe kunguqulo yakho ye-Microsoft Office 🙁

Kuthebhu ye-Pivot Yamandla evelayo, kuzoba nenkinobho enkulu eluhlaza ngokukhanyayo Ukuphathwa (Phatha), ngokuchofoza okuzovula iwindi le-Pivot Yamandla phezulu kwe-Excel, lapho sizobona khona okuqukethwe kwemodeli Yedatha yencwadi yamanje:

Inothi elibalulekile endleleni: Incwadi yokusebenzela ye-Excel ingaba nemodeli yedatha eyodwa kuphela.

Layisha amathebula Kumodeli Yedatha

Ukulayisha idatha Kumodeli, siqale siguqule ithebula libe isinqamuleli sekhibhodi "ehlakaniphile" esiguqukayo Ctrl+T futhi uyinikeze igama elinobungane kuthebhu umakhi (Umklamo). Lesi isinyathelo esidingekayo.

Ngemuva kwalokho ungasebenzisa noma yiziphi izindlela ezintathu ongakhetha kuzo:

• Cindezela inkinobho Engeza kumodeli (Engeza Kumodeli Yedatha) ithebhu I-Powerpivot ithebhu Ikhaya (Ikhaya).
  
• Ukukhetha amaqembu Faka – PivotTable (Faka — Ithebula Le-Pivot) bese uvula ibhokisi lokuhlola Engeza le datha Kumodeli Yedatha (Engeza le datha Kumodeli Yedatha). Kulokhu, ngokuya ngedatha elayishwe kuModel, ithebula le-pivot nalo lakhiwa ngokushesha.
  
• Kuthebhu ethuthukisiwe Idatha (Usuku) chofoza inkinobho Kusukela kuThebula/Ububanzi (Kusuka kuThebula/Ububanzi)ukulayisha ithebula lethu kusihleli Sombuzo Wamandla. Le ndlela yinde kunazo zonke, kodwa, uma uthanda, lapha ungenza ukuhlanza idatha eyengeziwe, ukuhlela kanye nazo zonke izinhlobo zezinguquko, lapho Umbuzo Wamandla unamandla kakhulu.

  Khona-ke idatha ehlanganisiwe ilayishwa kuModeli ngomyalo Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe... (Ekhaya — Vala&Layisha — Vala&Layisha ku…). Ewindini elivulayo, khetha inketho Vele udale uxhumano (Dala uxhumano kuphela) futhi, okubaluleke kakhulu, faka umaka Engeza le datha Kumodeli Yedatha (Engeza le datha Kumodeli Yedatha).
  

Sakha isifinyezo Semodeli Yedatha

Ukuze wakhe imodeli yedatha efingqiwe, ungasebenzisa noma yiziphi izindlela ezintathu:

• Cindezela inkinobho isifinyezo ithebula (Ithebula Le-Pivot) efasiteleni le-Pivot Yamandla.
• Khetha imiyalo ku-Excel Faka – PivotTable bese ushintshela kumodi Sebenzisa imodeli yedatha yaleli bhuku (Faka — Ithebula Le-Pivot — Sebenzisa Imodeli Yedatha yale ncwadi yokusebenzela).
• Ukukhetha amaqembu Faka – PivotTable (Faka — Ithebula Le-Pivot) bese uvula ibhokisi lokuhlola Engeza le datha Kumodeli Yedatha (Engeza le datha Kumodeli Yedatha). Ithebula lamanje “elihlakaniphile” lizolayishwa kuModel futhi ithebula lesifinyezo lizokwakhelwa Imodeli yonke.

Manje njengoba sesithole ukuthi singayilayisha kanjani idatha kumodeli Yedatha futhi sakhe isifinyezo kuyo, ake sihlole izinzuzo nezinzuzo lokhu okusinika zona.

Inzuzo 1: Ubudlelwano phakathi kwamathebula ngaphandle kokusebenzisa amafomula

Isifinyezo esivamile singakhiwa kuphela kusetshenziswa idatha evela kuthebula lomthombo owodwa. Uma unokuningi kwakho, ngokwesibonelo, ukuthengisa, uhlu lwamanani, uhla lwemibhalo lwamakhasimende, irejista yezinkontileka, njll., kuzodingeka ukuthi uqale uqoqe idatha kuwo wonke amathebula uye kwelinye usebenzisa imisebenzi efana ne-VLOOKUP. (VLOOKUP), INDEX (INDEX), OKUNINGI KUDANWE (IMESHI), SUMMESLIMN (SUMIFS) nokunye okunjalo. Lokhu kude, kuyakhathaza futhi kushayela i-Excel yakho “emcabangweni” onenani elikhulu ledatha.

Endabeni yesifinyezo se-Data Model, yonke into ilula kakhulu. Kwanele ukusetha ubudlelwano phakathi kwamatafula kanye efasiteleni le-Power Pivot - futhi sekuqedile. Ukuze wenze lokhu, kuthebhu I-Powerpivot cindezela inkinobho Ukuphathwa (Phatha) bese kuthi efasiteleni elivelayo – inkinobho Ukubuka Kweshadi (Ukubuka Umdwebo). Kusele ukuhudula amagama amakholomu (ukhiye) ajwayelekile (izinkambu) phakathi kwamathebula ukuze udale izixhumanisi:

Ngemva kwalokho, esifinyezweni Semodeli Yedatha, ungaphonsa endaweni yesifinyezo (imigqa, amakholomu, izihlungi, amanani) noma yiziphi izinkambu kusuka kunoma imaphi amathebula ahlobene - yonke into izoxhunywa futhi ibalwe ngokuzenzakalelayo:

Inzuzo yesi-2: Bala amanani ahlukile

Ithebula le-pivot elivamile lisinikeza ithuba lokukhetha okukodwa kwemisebenzi yokubala eyakhelwe ngaphakathi eminingana: isamba, isilinganiso, ukubala, ubuncane, ubukhulu, njll. Esifingqweni Semodeli Yedatha, umsebenzi owusizo kakhulu wengezwa kulolu hlu oluvamile ukubala inombolo ehlukile (amanani angaphindi). Ngosizo lwayo, isibonelo, ungakwazi ukubala kalula inani lezinto eziyingqayizivele zezimpahla (ububanzi) esizithengisa edolobheni ngalinye.

Chofoza kwesokudla enkundleni - umyalo Izinketho zenkambu yenani nakuthebhu Operation Khetha Inani lama-elementi ahlukene (Isibalo esihlukile):

Inzuzo 3: Amafomula e-DAX ngokwezifiso

Kwesinye isikhathi kufanele wenze izibalo ezengeziwe ezihlukile kumathebula e-pivot. Ngokufingqa okuvamile, lokhu kwenziwa kusetshenziswa izinkambu nezinto ezibaliwe, kuyilapho isifinyezo semodeli yedatha sisebenzisa izilinganiso ngolimi olukhethekile lwe-DAX (DAX = Izinkulumo Zokuhlaziywa Kwedatha).

Ukuze udale isilinganiso, khetha kuthebhu I-Powerpivot Umyalo Izinyathelo - Dala Isilinganiso (Izinyathelo - Isilinganiso esisha) noma vele uchofoze kwesokudla etafuleni kuhlu lwe-Pivot Fields bese ukhetha Engeza isilinganiso (Engeza isilinganiso) kumenyu yokuqukethwe:

Ewindini elivulayo, setha:

• Igama lethebulalapho isilinganiso esidaliwe sizogcinwa khona.
• Linganisa igama – noma yiliphi igama oliqondayo ngenkambu entsha.
• Incazelo – ozikhethela.
• Formula - into ebaluleke kakhulu, ngoba lapha singena ngesandla, noma chofoza inkinobho fx bese ukhetha umsebenzi we-DAX ohlwini, okufanele ubale umphumela lapho siphonsa isilinganiso sethu endaweni Yamagugu.
• Engxenyeni engezansi yewindi, ungakwazi ukusetha ngokushesha ifomethi yenombolo yesilinganiso esisohlwini Isigaba.
  

Ulimi lwe-DAX akulula ukuluqonda ngaso sonke isikhathi ngoba alusebenzi ngamavelu angawodwana, kodwa ngamakholomu aphelele namathebula, okungukuthi ludinga ukuhlelwa kabusha kokucabanga ngemva kwamafomula akudala e-Excel. Kodwa-ke, kuyafaneleka, ngoba amandla amakhono ayo ekucubunguleni inani elikhulu ledatha kunzima ukucabangela ngokweqile.

Inzuzo yesi-4: Ukuhlelwa kwenkambu yangokwezifiso

Ngokuvamile, lapho udala imibiko evamile, kufanele uphonse izinhlanganisela ezifanayo zezinkambu kumathebula e-pivot ngokulandelana okunikeziwe, isibonelo. Unyaka-Ikota-Inyanga-Usuku, noma Isigaba-Umkhiqizo, noma I-Country-City-Client njll. Esifinyezweni Semodeli Yedatha, le nkinga ixazululwa kalula ngokuzakhela eyakho izikhundla eziphezulu - amasethi ezinkambu ngokwezifiso.

Ewindini le-Pivot Yamandla, shintshela kumodi yeshadi ngenkinobho Ukubuka Kweshadi ithebhu Ikhaya (Ikhaya — Ukubuka Umdwebo), khetha nge Ctrl izinkambu ozifunayo bese uchofoza kwesokudla kuzo. Imenyu yokuqukethwe izoqukatha umyalo Dala I-Hierarchy (Dala i-hierarchy):

Isigaba esidaliwe singaqanjwa kabusha futhi sihudulelwe kuso ngegundane izinkambu ezidingekayo, ukuze kamuva ekuhambeni okukodwa ziphonswe esifinyezweni:

Inzuzo yesi-5: Ama-stencil angokwezifiso

Ukuqhubeka nombono wesigaba sangaphambilini, esifinyezweni se-Data Model, ungaphinda udale amasethi akho ezinto zenkambu ngayinye. Isibonelo, kusukela kulo lonke uhlu lwamadolobha, ungenza kalula isethi yalawo asendaweni yakho yesibopho. Noma qoqa amakhasimende akho kuphela, izimpahla zakho, njll. wenze isethi ekhethekile.

Ukuze wenze lokhu, kuthebhu Ukuhlaziywa kwethebula le-Pivot ohlwini lokudonsela phansi Izinkambu, Izinto, namasethi kukhona imiyalo ehambisanayo (Hlaziya - Izinkambu, Iizinto namasethi - Dala isethi ngokusekelwe ezintweni zomugqa/zekholomu):

Ewindini elivulayo, ungakhipha ngokukhethayo, wengeze noma uguqule indawo yanoma yiziphi izici bese ulondoloza umphumela owusethi ngaphansi kwegama elisha:

Wonke amasethi adaliwe azovezwa kuphaneli ye-PivotTable Fields kufolda ehlukile, ukusuka lapho angahudulwa khona ngokukhululekile aye kumigqa namakholomu ezindaweni zanoma iyiphi i-PivotTable entsha:

Inzuzo yesi-6: Ngokukhetha Fihla Amathebula Namakholomu

Nakuba lokhu kuyinzuzo encane, kodwa ejabulisa kakhulu kwezinye izimo. Ngokuchofoza kwesokudla egameni lenkundla noma kuthebhu yetafula efasiteleni le-Pivot Yamandla, ungakhetha umyalo Fihla ku-Client Toolkit (Fihla kokuthi Amathuluzi Eklayenti):

Ikholomu noma ithebula elifihliwe lizonyamalala efasiteleni Lohlu Lwensimu Ye-PivotTable. Kulula kakhulu uma udinga ukufihlela umsebenzisi amakholomu athile asizayo (isibonelo, abaliwe noma amakholomu anamanani ayisihluthulelo okudala ubudlelwano) noma wonke amathebula.

Inzuzo 7. I-drill-down ethuthukisiwe

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

Esifinyezweni Semodeli Yedatha, leli thuluzi eliwusizo lisebenza ngobuqili. Ngokuma kunoma iyiphi iseli ngomphumela osithakaselayo, ungachofoza isithonjana ngengilazi yokukhulisa ephuma eduze kwayo (ibizwa ngokuthi. Express Amathrendi) bese ukhetha noma iyiphi inkambu onentshisekelo kuyo kunoma yiliphi ithebula elihlobene:

Ngemva kwalokho, inani lamanje (Model = Explorer) lizongena endaweni yokuhlunga, futhi isifinyezo sizokwakhiwa ngamahhovisi:

Kunjalo, inqubo enjalo ingaphindaphindwa izikhathi eziningi, ngokuqhubekayo ingena kudatha yakho ngendlela ofisa ngayo.

Inzuzo yesi-8: Guqula i-Pivot ibe Imisebenzi ye-Cube

Uma ukhetha noma iyiphi iseli kusifinyezo semodeli Yedatha bese ukhetha kuthebhu Ukuhlaziywa kwethebula le-Pivot Umyalo Amathuluzi e-OLAP - Guqula abe Amafomula (Hlaziya — Amathuluzi e-OLAP — Guqula abe amafomula), bese sonke isifinyezo sizoguqulwa ngokuzenzakalela sibe amafomula. Manje amanani enkambu endaweni yekholomu kanye nemiphumela endaweni yenani izotholwa kumodeli yedatha kusetshenziswa imisebenzi ekhethekile yekhiyubhu: CUBEVALUE kanye ne-CUBEMEMBER:

Ngobuchwepheshe, lokhu kusho ukuthi manje asisabhekani nesifinyezo, kodwa ngamaseli amaningana anamafomula, okungukuthi singenza kalula noma yiziphi izinguquko ngombiko wethu ezingatholakali esifinyezweni, isibonelo, faka imigqa emisha noma amakholomu phakathi nendawo. yombiko, yenza noma yiziphi izibalo ezengeziwe ngaphakathi kwesifinyezo, uzihlele nganoma iyiphi indlela oyifunayo, njll.

Ngesikhathi esifanayo, ukuxhumana nedatha yomthombo, yiqiniso, kuhlala futhi esikhathini esizayo lawa mafomula azobuyekezwa lapho imithombo ishintsha. Ubuhle!

• Ukuhlaziywa kweqiniso lokuhlela kuthebula le-pivot elinePivot Yamandla kanye Nombuzo Wamandla
• Ithebula le-Pivot elinesihloko esinemigqa eminingi
• Dala isizindalwazi ku-Excel usebenzisa i-Power Pivot