I-PowerPivot ye-Excel - I-Table yokuLondoloza kwi-Data Store

Enye yezinto endizichazayo kakhulu ngeWindowsPivot ye-Excel yikhono lokuncedisa iithebula ze-lookup kwiiseti zakho zeedatha. Ininzi yexesha, idatha osebenzayo ayinayo yonke insimu oyifunayo ukuhlalutya kwakho. Umzekelo, unokufumana inkundla yosuku kodwa kufuneka uqoke idatha yakho ngekota. Ungabhala ifomula, kodwa kulula ukudala itafile elula yokujonga ngaphakathi kwendawo ye-PowerPivot.

Ungasebenzisa kwakhona le tafile yelokup kwenye iqoqo njengegama lenyanga kunye nesahlulo sokuqala sonyaka. Kwimiqathango yokugcinwa kwedatha, ngokwenene udala idayibhile yetafile. Kule nqaku, ndiza kukunika umzekelo omncinci amatafula ekuphuculeni iPowerPivot yeprojekthi ye-Excel.

I-New Dimension (Lookup) Itheyibhuli

Makhe siqwalasele itafile nge-data yedatha (idatha yeContoso evela kwi-Microsoft ifaka umfanekiso wesetyenzisiweyo kule fayile). Cinga ukuba itafile inemihlaba yabathengi, umhla wolawulo, umyalelo wokubala, kunye nohlobo lomyalelo. Siza kugxininisa kwintsimi yohlobo lomyalelo. Kucinga ukuba insimu yohlobo lohlobo iquka ixabiso ezifana:

• Netbooks
• Desktops
• Abahloli
• Abaprojekthi
• Iiprinta
• Ba Scan
• Iikhamera Zedijithali
• Iikhamera ze-Digital SLR
• Iikhamera zeFilimu
• Iikhamera
• Iifowuni zeeofisi
• IiFowuni
• PDAs
• Iifowuni zeeSelfowuni

Enyanisweni, uya kuba neenkcukacha ezi zinto kodwa ukugcina lo mzekelo ulula, uthathele ezi zixabiso langempela kwitheyibhile.

Ukusebenzisa i-PowerPivot ye-Excel, uya kuba lula ukuqoqa imiyalelo yakho ngohlobo lomyalelo. Kuthekani ukuba ufuna iqela elihlukileyo? Umzekelo, cinga ukuba ufuna "uluhlu" lweqela njengeekhompyutha, iikhamera kunye neefowuni. Itafile ye-oda ayinayo "intambo" kwintsimi, kodwa unokwenza ngokulula ukuyisebenzisa njengetable yokubheka kwi PowerPivot ye-Excel.

Itheyibhile yokufumana isampula epheleleyo ngezantsi kwiTable 1 . Nazi a ma nyathelo:

• Inyathelo 1: Ufuna uluhlu olucacileyo ukusuka kwintsimi yohlobo lwetafile yakho yokujonga. Oku kuya kuba yintsimi yakho yokujonga. Ukususela kwisethi yedatha yakho, yenza uludwe olucacileyo lwexabiso ukusuka kwintsimi yohlobo lwe-oda. Faka uluhlu olucacileyo lwe "iintlobo" kwiincwadi ze-Excel. Bhala igama lohlobo lwekholomu.
• Inyathelo 2: Kwikholam ecaleni kwikholamu yakho yokujonga (Uhlobo), faka insimu entsha ofuna ukuyiqela kuyo. Ngokomzekelo wethu, yongeza ikholomu enelebula ebizwa ngokuba yiCandelo.
• Inyathelo lesi-3: Ngexabiso ngalinye kwiluhlu lwakho oluhlukileyo lwexabiso (iintlobo kulo mzekelo), faka ixabiso "elifanayo" lemiqathango. Ngomzekelo wethu olula, faka iiKompyutha, iiKhamera okanye iiFowuni kwikholam yeCandelo.
• Isinyathelo 4: Kopisha itheyibhile yedatha kunye neCandelo kwi-clipboard yakho.
• Inyathelo 5: Vula iincwadi ze-Excel kunye nedatha yolawulo kwi-PowerPivot ye-Excel. Qalisa iWindowsPivot Window. Cofa unamathisele okuza kuzisa kwitafile yakho entsha yokujonga. Nika itafile igama uze uqinisekise ukuba uhlolisise "Sebenzisa umqolo wokuqala njengomqolo wesihloko." Chofoza OK. Udale ithebula lokungena kwi PowerPivot.
• Inyathelo 6: Yakha ubudlelwane phakathi kwendawo yentlobo kwitafile ye-oda kunye nensima yeCandelo kwitheyibhile yokungena. Cofa kwi-Ribbon ibhande kwaye ukhethe Yakha ubudlelwane. Yenza ukhetho kwidibano yencoko yobudlelwane kwaye nqakraza Yakha.

Xa udala i-PivotTable kwi-Excel ngokusekelwe kwidatha ye-PowerPivot, uya kukwazi ukuqela iqela lakho elitsha kwiCandelo. Gcina ukhumbule ukuba i-PowerPivot ye-Excel isekela kuphela i-Inner Joins. Ukuba unalo "uhlobo lomyalelo" olungekhoyo kwitheyibhile yakho yokujonga, zonke iirekhodi ezihambelanayo zolu hlobo ziya kulahleka kuyo nayiphi na i-PivotTable ngokusekelwe kwiData yePowerPivot. Uya kufuneka uhlolisise eli xesha ngezikhathi.

Umhla woMlinganiselo (uLookup) Itheyibhuli

Umhla woLungiso loLuku oluya ku funeka lufuneke kakhulu kwiiprojekthi zakho zePowerPivot ze-Excel. Uninzi lweeseti zedatha lunjani uhlobo lwenkcazo yamasimu. Kukho imisebenzi yokubala unyaka nenyanga.

Nangona kunjalo, ukuba ufuna i-text text yangempela okanye kwikota, kufuneka ubhale ifomula eyinkimbinkimbi. Kulula kakhulu ukuquka i-dimension date (lookup) itafile kwaye uyilinganise nenombolo yenyanga kwisetyenzisi sakho sedatha. Uzakufuna ukongeza ikholomu kwitheyibhile yakho yokwenza umyalelo ukumela inyanga yenyanga ukusuka kwintsimi yosuku lomyalelo. Ifom ye-DAX ye "ngenyanga" kumzekelo wethu "= MONTH ([Umhla Wemiyalelo]." Le nto iya kubuyisa inombolo phakathi kwe-1 no-12 kwirekhodi nganye. Uya kunika uguquko kwi-analysis yakho.

Itafile yomhla okanye i-lookup itafile iya kubandakanya iirekhodi ezili-12. Inyanga yekholamu iya kuba nexabiso 1 - 12. Ezinye iikholomu ziza kubandakanya isicatshulwa seenyanga esicacisiwe, itekisi epheleleyo yenyanga, ikota, njl. Namanyathelo oku:

• Inyathelo 1: Khuphela itafile kwiTable 2 ngezantsi uze unamathisele kwi-PowerPivot. Unokwenza le tafile kwi-Excel kodwa ndikulondoloza ixesha. Kufuneka ukwazi ukunamathela ngqo kwi-data ekhethiweyo apha ngezantsi ukuba usebenzisa i-Internet Explorer. I-PowerPivot ikhetha ukufometha kwetafile kwindlela yokuvavanya. Ukuba usebenzisa esinye isiphequluli, kufuneka udibanise kwi-Excel kuqala uze uyikopishe kwi-Excel ukufumana ukufometha kwetafile.
• Inyathelo 2: Vula incwadi ye-Excel kunye nedatha yolawulo kwi-PowerPivot ye-Excel. Qalisa iWindowsPivot Window. Chofoza unamathela oza kuzisa kwitafile yakho yokubhaliweyo ikopi ezantsi okanye kwi-Excel. Nika itafile igama uze uqinisekise ukuba uhlolisise "Sebenzisa umqolo wokuqala njengomqolo wesihloko." Chofoza OK. Udale idethi yokujonga i-PowerPivot.

• Inyathelo lesi-3 : Yenza ubudlelwane phakathi kwenkalo yeNyanga kwitafile ye-oda kunye neNyanga yeNumber kwitheyibhile yokujonga. Cofa kwi-Ribbon ibhande kwaye ukhethe Yakha ubudlelwane. Yenza ukhetho kwidibano yencoko yobudlelwane kwaye nqakraza Yakha.

Kwakhona, ngokudibanisa kwimihla yamanani, uya kukwazi ukuqoqa idatha kwi-PivotTable yakho usebenzisa nayiphi na imilinganiselo eyahlukileyo ukusuka kumhla we-lookup itafile. Ukubamba ngekota okanye igama le nyanga liya kuba yi-snap.

ISampula Dimension (Lookup) Iidables

Itheyibhuli 1

Itheyibhuli 2