Amagama athi “Power Query”, “Power Pivot”, “Power BI” kanye namanye “amandla” aya ngokuya evela kuma-athikili nezinto zokwakha mayelana ne-Microsoft Excel. Ngokuhlangenwe nakho kwami, akuwona wonke umuntu oqonda ngokucacile ukuthi yini elele ngemuva kwale miqondo, ukuthi ixhumeke kanjani nokuthi ingasiza kanjani umsebenzisi we-Excel olula.
Ake sicacise isimo.
Umbuzo Wamandla
Emuva ngo-2013, iqembu elakhiwe ngokukhethekile lonjiniyela ngaphakathi kwe-Microsoft likhiphe isengezo samahhala se-Excel. Umbuzo Wamandla (amanye amagama i-Data Explorer, Get & Transform), engenza izinto eziningi eziwusizo emsebenzini wansuku zonke:
- Layisha idatha ku-Excel evela emithonjeni ecishe ibe ngu-40 ehlukene, okuhlanganisa isizindalwazi (SQL, Oracle, Access, Teradata…), izinhlelo ze-ERP zezinkampani (SAP, Microsoft Dynamics, 1C…), amasevisi e-inthanethi (Facebook, Google Analytics, cishe noma imaphi amawebhusayithi).
- Qoqa idatha kusuka Amafayela zonke izinhlobo zedatha ezinkulu (i-XLSX, i-TXT, i-CSV, i-JSON, i-HTML, i-XML…), kokubili kanye nangenqwaba - kuwo wonke amafayela akufolda eshiwo. Ezincwadini zokusebenza ze-Excel, ungadawuniloda idatha ngokuzenzakalelayo kuwo wonke amashidi ngesikhathi esisodwa.
- Hlanza ithole idatha “kudoti”: amakholomu engeziwe noma imigqa, izimpinda, ulwazi lwesevisi “kusihloko”, izikhala ezengeziwe noma izinhlamvu ezingaphrinteki, njll.
- Letha idatha Oda: uhlobo olulungile, izinombolo-njengombhalo, gcwalisa izikhala, engeza "ikhephu" elungile yethebula, hlaziya umbhalo "okunamathelayo" ube amakholomu bese uwunamathisela futhi, hlukanisa usuku lube izingxenye, njll.
- ngazo zonke izindlela guqula amathebula, ukuwaletha ngendlela oyifunayo (isihlungi, hlela, shintsha ukuhleleka kwamakholomu, guqula, engeza amathothali, nweba amathebula ayisiphambano abe isicaba bese uhlehla emuva).
- Faka esikhundleni idatha kusuka kwelinye ithebula uye kwelinye ngokufanisa ipharamitha eyodwa noma ngaphezulu, okungukuthi umsebenzi omuhle wokushintsha VPR (VLOOKUP) kanye nama-analogues ayo.
Umbuzo Wamandla utholakala ezinguqulweni ezimbili: njengesengezo esihlukile se-Excel 2010-2013, esingalandwa kuwebhusayithi esemthethweni ye-Microsoft, futhi njengengxenye ye-Excel 2016. Esimweni sokuqala, ngemva kokufaka, ithebhu ehlukile ivela I-Excel:
Ku-Excel 2016, konke ukusebenza Kombuzo Wamandla sekuvele kwakhiwe ngokuzenzakalelayo futhi kukuthebhu Idatha (Usuku) njengeqembu Thola futhi uguqule (Thola futhi Uguqule):
Amathuba alezi zinketho afana ngokuphelele.
Isici esibalulekile Sombuzo Wamandla ukuthi zonke izenzo zokungenisa nokuguqula idatha zigcinwa ngendlela yombuzo - ukulandelana kwezinyathelo olimini lwezinhlelo lwangaphakathi Lombuzo Wamandla, olubizwa kafushane ngokuthi “M”. Izinyathelo zingahle zihlelwe futhi ziphinde zidlalwe noma yiziphi izikhathi (qala kabusha umbuzo).
Iwindi Lombuzo Wamandla amakhulu ngokuvamile libukeka kanjena:
Ngokubona kwami, lesi isengezo esisebenziseka kakhulu esisohlwini lwalesi sihloko kubasebenzisi abahlukahlukene. Imisebenzi eminingi obekumele uyihlanekezele kabi ngamafomula noma ubhale amamakhro manje yenziwe kalula futhi kahle ku-Power Query. Yebo, futhi ngokubuyekezwa okuzenzakalelayo kwemiphumela okwalandela. Futhi uma kucatshangelwa ukuthi kumahhala, ngokwesilinganiso sekhwalithi yentengo, Umbuzo Wamandla uphumile emqhudelwaneni futhi kufanele ube nakho ngokuphelele kunoma yimuphi umsebenzisi we-Excel osezingeni eliphakathi kulezi zinsuku.
I-Powerpivot
I-Power Pivot nayo iyisengezo se-Microsoft Excel, kodwa yakhelwe imisebenzi ehluke kancane. Uma Umbuzo Wamandla ugxile ekungeniseni nasekucubungulweni, lapho-ke i-Power Pivot iyadingeka ngokuyinhloko ekuhlaziyweni okuyinkimbinkimbi kwenani elikhulu ledatha. Njengokulinganiselwa kokuqala, ungacabanga nge-Power Pivot njengethebula le-pivot elihle.
Izimiso ezijwayelekile zokusebenza ku-Power Pivot zimi kanje:
- Thina singabokuqala ilayisha idatha ku-Power Pivot - kusekelwa imithombo ehlukene engu-15: isizindalwazi esivamile (SQL, Oracle, Access ...), amafayela e-Excel, amafayela ombhalo, izifunzo zedatha. Ngaphezu kwalokho, ungasebenzisa i-Power Query njengomthombo wedatha, okwenza ukuhlaziya kucishe kube yi-omnivorous.
- Bese kuba phakathi kwamatafula alayishiwe ukuxhumana kumisiwe noma, njengoba besho, idaliwe Imodeli Yedatha. Lokhu kuzovumela esikhathini esizayo ukwakha imibiko kunoma yiziphi izinkambu ezivela kumathebula akhona njengokungathi ithebula elilodwa. Futhi ayikho i-VPR futhi.
- Uma kunesidingo, izibalo ezengeziwe zengezwa kumodeli Yedatha kusetshenziswa amakholomu abaliwe (efana nekholomu enamafomula "kuthebula elihlakaniphile") kanye izinyathelo (i-analogue yenkambu ebaliwe esifinyezweni). Konke lokhu kubhalwe ngolimi lwangaphakathi lwe-Power Pivot olubizwa nge-DAX (Data Analysis eXpressions).
- Eshidini le-Excel, ngokusho kwe-Data Model, imibiko enentshisekelo kithi yakhiwe efomini amatafula we-pivot kanye nemidwebo.
Iwindi elikhulu le-Power Pivot libukeka kanjena:
Futhi nansi indlela Imodeli Yedatha ebukeka ngayo, okungukuthi wonke amatafula alayishiwe anobudlelwano obudaliwe:
I-Power Pivot inezici eziningi eziyenza ibe ithuluzi elihlukile leminye imisebenzi:
- Ku-Pivot Yamandla awukho umkhawulo womugqa (njengaku-Excel). Ungalayisha amatafula anoma yimuphi usayizi futhi usebenze kalula nawo.
- I-Power Pivot inhle kakhulu cindezela idatha lapho uwalayisha kuModel. Ifayela loqobo elingu-50MB lingashintsha kalula libe ngu-3-5MB ngemva kokulanda.
- Njengoba "ngaphansi kwe-hood" i-Power Pivot, empeleni, inenjini yedatha egcwele, ibhekana nenani elikhulu lolwazi. ngokushesha. Udinga ukuhlaziya amarekhodi ayizigidi eziyi-10-15 futhi wakhe isifinyezo? Futhi konke lokhu kukhompuyutha ephathekayo endala? Ayikho inkinga!
Ngeshwa, i-Power Pivot ayikakafakwa kuzo zonke izinguqulo ze-Excel. Uma une-Excel 2010, ungayilanda mahhala kuwebhusayithi ye-Microsoft. Kodwa uma une-Excel 2013-2016, ngakho konke kuncike ilayisense yakho, ngoba. kwezinye izinguqulo ifakiwe (i-Office Pro Plus, isibonelo), kanti kwezinye ayifakiwe (Ikhaya le-Office 365, i-Office 365 Personal, njll.) Ungafunda kabanzi ngalokhu lapha.
Amandla Amamephu
Lesi sengezo saqala ukuvela ngo-2013 futhi ekuqaleni sasibizwa nge-GeoFlow. Ihloselwe ukubonakala kwedatha yezwe, okungukuthi ulwazi lwezinombolo kumamephu ezindawo. Idatha yokuqala ezoboniswa ithathwe kumodeli yedatha ye-Pivot Yamandla efanayo (bona isigaba sangaphambilini).
Inguqulo yedemo ye-Power Map (cishe ayihlukile kunaleyo egcwele, noma kunjalo) ingalandwa mahhala futhi kuwebhusayithi ye-Microsoft. Inguqulo ephelele ifakiwe kwamanye amaphakheji e-Microsoft Office 2013-2016 kanye ne-Power Pivot - ngesimo senkinobho Imephu ye-3D ithebhu Faka (Faka — 3D-map):
Izici ezibalulekile ze-Power Map:
- Amamephu kungaba kokubili flat kanye voluminous (globe).
- Ungasebenzisa eziningana ezahlukene izinhlobo zokubuka (ama-histograms, amashadi ebhamuza, amamephu okushisa, ukugcwaliswa kwendawo).
- Ungangeza ukulinganisa isikhathi, okungukuthi philisa inqubo futhi uyibuke ithuthuka.
- Amamephu alayishwa kusukela kusevisi Amamephu we-Bing, okungukuthi Udinga uxhumano lwe-inthanethi olushesha kakhulu ukuze ubuke. Ngezinye izikhathi kuba nobunzima ngokuqashelwa kahle kwamakheli, ngoba. amagama akudatha awafani ngaso sonke isikhathi nalawo e-Bing Maps.
- Kunguqulo ephelele (engeyona eyedemo) ye-Power Map, ungasebenzisa eyakho amamephu alandekayo, isibonelo, ukubona ngeso lengqondo izivakashi eziya esikhungweni sezitolo noma izintengo zamafulethi esakhiweni sokuhlala khona kanye kuhlelo lwesakhiwo.
- Ngokusekelwe emibonweni edaliwe ye-geo-visualization, ungakha amavidiyo ngokuqondile ku-Power Map (isibonelo) ukuze wabelane ngawo kamuva nalabo abangenaso isengezo esifakiwe noma usifake kuphrezentheshini ye-Power Point.
ukubuka kwamandla
Okokuqala kwethulwa ku-Excel 2013, lesi sengezo siklanyelwe ukwenza idatha yakho iphile ngamagrafu asebenzayo, amashadi, amamephu, namathebula. Ngezinye izikhathi amagama asetshenziselwa lokhu. dashboard (ideshibhodi) or dashboard (ikhadi lamaphuzu). Okubalulekile ukuthi ungakwazi ukufaka ishidi elikhethekile elingenawo amaseli efayeleni lakho le-Excel - i-Power View slide, lapho ungakwazi ukwengeza umbhalo, izithombe kanye nezinhlobo eziningi ezahlukene zokuboniswa ngokusekelwe kudatha yakho evela kumodeli yedatha ye-Pivot Power.
Kuzobukeka okuthile okufana nalokhu:
Ama-nuances lapha:
- Idatha yokuqala ithathwa endaweni efanayo - kumodeli yedatha ye-Pivot Power.
- Ukuze usebenze ne-Power View, udinga ukufaka i-Silverlight kukhompyutha yakho - i-analogue ye-Microsoft ye-Flash (yamahhala).
Kuwebhusayithi ye-Microsoft, ngendlela, kukhona inkambo yokuqeqesha ehloniphekile ku-Power View ku-.
I-Power BI
Ngokungafani nalezi zangaphambilini, i-Power BI ayisona isengezo se-Excel, kodwa umkhiqizo ohlukile, okuyisethi yonke yamathuluzi okuhlaziya nokubonwa kwebhizinisi. Iqukethe izici ezintathu ezibalulekile:
1. Ideskithophu ye-Power BI - uhlelo lokuhlaziya nokubona idatha ngeso lengqondo, okuhlanganisa, phakathi kwezinye izinto, konke ukusebenza Kombuzo Wamandla kanye nesengezo se-Power Pivot + izindlela ezithuthukisiwe zokubuka ezivela ku-Power View kanye ne-Power Map. Ungayilanda futhi uyifake mahhala kuwebhusayithi yeMicrosoft.
Ku-Power BI Desktop ungakwazi:
- Layisha idatha kusuka ngaphezulu 70 imithombo ehlukene (njengakumbuzo Wamandla + izixhumi ezengeziwe).
- bopha amathebula azomodela (njengaku-Power Pivot)
- Engeza izibalo ezengeziwe kudatha nge izinyathelo и amakholomu abaliwe ku-DAX (njengaku-Power Pivot)
- Dala idatha enhle ngokusekelwe imibiko esebenzisanayo ngezinhlobo ezahlukene zokubuka (ezifana kakhulu ne-Power View, kodwa engcono nakakhulu futhi enamandla kakhulu).
- Shicilela udale imibiko kusizindalwazi se-Power BI Service (bona iphuzu elilandelayo) futhi wabelane ngayo nozakwabo. Ngaphezu kwalokho, kungenzeka ukunikeza amalungelo ahlukene (ukufunda, ukuhlela) kubantu abahlukene.
2. Isevisi ye-intanethi ye-Power BI - ukukubeka kalula, leli yisayithi lapho wena kanye nomsebenzisi ngamunye enkampanini yakho nizoba "nebhokisi lesihlabathi" (indawo yokusebenza) lapho ungalayisha khona imibiko edalwe ku-Power BI Desktop. Ngaphezu kokubuka, kukuvumela nokuthi uzihlele, zikhiqize kabusha cishe yonke imisebenzi ye-Power BI Desktop ku-inthanethi. Ungakwazi futhi ukuboleka okubonwayo ngakunye emibikweni yabanye abantu lapha, uqoqe amadeshibhodi ombhali wakho kubo.
Kubukeka kanjena:
3. Amandla BI Mobile wuhlelo lokusebenza lwe-iOS/Android/Windows lokuxhuma ku-Power BI Service futhi ubuke kalula (hhayi ukuhlela) imibiko edaliwe namadeshibhodi khona kanye kusikrini sefoni noma ithebhulethi yakho. Ungayilanda (mahhala ngokuphelele) lapha.
Ku-iPhone, isibonelo, umbiko owenziwe ngenhla ubukeka kanje:
Futhi konke lokhu ngenkathi kugcinwa ukusebenzisana kanye nokugqwayiza + okuboshelwe ukuthintwa nokudwetshwa esikrinini ngepeni. Ngokunethezeka kakhulu. Ngakho, ubuhlakani bebhizinisi butholakala kubo bonke abantu ababalulekile benkampani nganoma yisiphi isikhathi nanoma iyiphi indawo - ukufinyelela ku-inthanethi kuphela okudingekayo.
Izinhlelo ze-Power BI zamanani. I-Power BI Desktop kanye ne-Mobile kumahhala ngaphandle kwebhokisi, futhi izici eziningi ze-Power BI Service zimahhala nazo. Ngakho-ke ngokusetshenziswa komuntu siqu noma ukusetshenziswa enkampanini encane, awudingi ukukhokha isenti lakho konke okungenhla futhi ungahlala ngokuphephile ohlelweni. Mahhala. Uma ufuna ukwabelana ngemibiko nozakwenu futhi ulawule amalungelo abo okufinyelela, kuzodingeka uye ku BESS ($10 ngenyanga ngomsebenzisi ngamunye). Ingabe kukhona okunye Premium - ezinkampanini ezinkulu (> abasebenzisi abangu-500) ezidinga isitoreji esihlukile namandla weseva wedatha.
- Ishadi le-Project Gantt ku-Excel elinombuzo wamandla
- Uyenza kanjani i-database ku-Excel usebenzisa i-Power Pivot
- Ukubona ngeso lengqondo ukunyakaza emzileni emephini ku-Power Map