I-PowerPivot yeThebula le-Excel - Lookup ku-Data Storage Warehouse

Enye yezinto engibhala ngazo kakhulu nge- PowerPivot ye-Excel yikhono lokufaka amatafula wokungena kumasethi wakho wedatha. Esikhathini esiningi, idatha osebenzayo ayinayo yonke insimu oyidingayo ekuhlaziyweni kwakho. Isibonelo, ungase ube nenkundla yosuku kepha udinga ukuqoqa idatha yakho ngekota. Ungabhala ifomula, kodwa kulula ukudala ithebula elilula lokubuka ngaphakathi kwendawo ye-PowerPivot.

Ungasebenzisa futhi ithebula le-lookup lokunye ukuqoqa njengegama lenyanga kanye nengxenye yokuqala / yesibili yonyaka. Emininingweni yokugcinwa kwedatha, empeleni udala ithebula lesayizi lwedethi. Kulesi sihloko, ngizokunika ambalwa amancane amatafula amathebula ukuthuthukisa amandla wakho wePowerPivot for Excel.

I-New Text Dimension (Lookup) IThebula

Ake sicabangele itafula nge-oda yedatha (idatha ye-Contoso evela ku-Microsoft ifaka umfanekiso wesethi yedatha kulokhu). Cabanga ukuthi itafula linezinkampani zamakhasimende, usuku lokuhleleka, inani elihle le-order, nohlobo lokuhleleka. Sizogxila enkundleni yohlobo lwe-oda. Cabanga ukuthi insimu yohlobo lwe-oda ihlanganisa amanani afana nalokhu:

• Ama-Netbooks
• Desktops
• Abaqaphi
• Amaprosesa
• Amaphrinta
• Iskenazi
• Ama-Digital Camera
• Ama-Digital SLR Amakhamera
• Amakhamera wefilimu
• Amakhamera
• Amafoni ehhovisi
• Amafoni ahlakaniphile
• PDAs
• Cell Ucingo Izesekeli

Eqinisweni, uzoba namakhodi kulawa kodwa ukugcina lesi sibonelo silula, ucabange ukuthi yizilinganiso zangempela kuthebula le-oda.

Ukusebenzisa i-PowerPivot ye-Excel, uzokwazi kalula ukuqoqa ama-oda wakho ngohlobo lwe-oda. Kuthiwani uma ufuna iqoqo elihlukile? Isibonelo, cabanga ukuthi udinga ukuhlukanisa "isigaba" njengama-computer, amakhamera namafoni. Itafula le-oda alinayo "isigaba" insimu, kodwa ungayidala kalula njengethebula lokubuka ku-PowerPivot ye-Excel.

Ithebula eliphelele le-sample lookup lingezansi kweThebula 1 . Nazi izinyathelo:

• Isinyathelo soku-1: Udinga uhlu oluhluke kusuka ensimu yohlobo lwethebula lakho lokubuka. Lokhu kuzoba yinkambu yakho yokubuka. Kusuka kusethi yakho yedatha, dala uhlu oluhlukile lwamagugu kusuka kwenkambu yohlobo lwe-order. Faka uhlu oluhlukile "lwezinhlobo" encwadini ye-Excel. Tlola uhlobo lwekholomu.
• Isinyathelo sesibili: Kukholomu eceleni kwekholomu yakho yokubuka (Uhlobo), engeza insimu entsha ofuna ukuyiqoqa. Esikhathini sethu, engeza ikholomu enelebuli ebizwa ngokuthi Isigaba.
• Isinyathelo sesi-3: Ngenani ngalinye kuluhlu lwakho oluhlukile lwamagugu (izinhlobo kulesi sibonelo), engeza amanani ahambisana ne- "Category". Esikhathini sethu esilula, faka noma yi-Amakhompiyutha, amaKhamera noma amafoni e-Column Category.
• Isinyathelo 4: Kopisha ithebula lwedatha lohlobo nohlobo ebhodini lokunamathisela.
• Isinyathelo sesi-5: Vula ibhuku le-Excel ngemininingwane yedatha ku-PowerPivot ye-Excel. Qalisa ifenisha ye-PowerPivot. Chofoza Namathisela okuzoletha ithebula lakho lokubuka elisha. Nikeza ithebula igama bese uqinisekisa ukuthi uhlola "Sebenzisa umugqa wokuqala njengezingqimba zekholomu." Chofoza OK. Udale ithebula lokubheka ku-PowerPivot.
• Isinyathelo 6: Dala ubudlelwane phakathi kwenkambu yohlobo kutafula le-oda kanye nensimu yesigaba kuthebula lokungena. Chofoza ku-Ribbon Design bese ukhetha Yakha Ubuhlobo. Yenza okukhethwa kukho ku-Ingxoxo Yakha Ubudlelwano bese uchofoza Yakha.

Uma udala i-PivotTable ku-Excel ngokusekelwe kwedatha ye-PowerPivot, uzokwazi ukuqoqa ngensimu yakho Yesigaba esisha. Khumbula ukuthi i-PowerPivot ye-Excel isekela kuphela ama-Inner Joins. Uma une "uhlobo lohlobo" olungekho etafuleni lakho lookup, wonke amarekhodi ahambisana nalolo hlobo ngeke alahleke kunoma iyiphi i-PivotTable ngokusekelwe kwedatha ye-PowerPivot. Kuzodingeka uhlole lokhu ngezikhathi ezithile.

Idethi Dimension (Lookup) Ithebula

Ithebula le-date lookup cishe lizodingeka emisebenzini yakho eminingi ye-PowerPivot ye-Excel. Izintambo eziningi zedatha zinomkhakha othile wenkambu yamadethi. Kukhona imisebenzi yokubala unyaka nenyanga.

Kodwa-ke, uma udinga umbhalo wenyanga wangempela noma ikota, udinga ukubhala ifomula eyinkimbinkimbi. Kulula kakhulu ukufaka ithebula lombhalo we-date (lookup) bese ulifanisa nenombolo yenyanga kusethi yakho yedatha enkulu. Uzodinga ukwengeza ikholomu etafuleni lakho le-oda ukuze ubonise inombolo yenyanga kusukela enkambeni yedethi yokuhlela. Ifomu le-DAX le "ngenyanga" kusibonelo sethu "= MONTH ([Usuku Lomyalelo]) Lokhu kuzobuyisela inombolo ephakathi kuka-1 no-12 emrekhodini ngamunye. Ithebula lethu lezintambo lizohlinzeka ngamanani athile, okuxhumanisa nenombolo yenyanga. kuzokunikeza ukuguquguquka ekuhlaziyweni kwakho. Ithebula eliphelele lesampula lendawo ye-dimension lingezansi kweThebula 2 .

Ithebula lesikhathi noma i-lookup itafula lizofaka amarekhodi angu-12. Ikholomu yenyanga izoba nemagugu 1 - 12. Amanye amakholomu afaka phakathi umbhalo obhaliwe wenyanga, umbhalo wenyanga ogcwele, ikota, njll Nanka izinyathelo:

• Isinyathelo 1: Kopisha ithebula kusukela kuThebula 2 ngezansi bese unamathisela ku-PowerPivot. Ungadala leli tafula ku-Excel kodwa ngisindise isikhathi. Kumele ukwazi ukunamathisela ngokuqondile idatha ekhethiwe ngezansi uma usebenzisa i-Internet Explorer. I-PowerPivot ikhetha ukufometha kwetafula ekuhlolweni kwami. Uma usebenzisa esinye isiphequluli, kungadingeka ukuthi unamathisele ku-Excel kuqala bese uyikopisha kusuka ku-Excel ukuze uthathe ukufometha kwetafula.
• Isinyathelo sesibili: Vula ibhuku le-Excel ngemininingwane yedatha ku-PowerPivot ye-Excel. Qalisa ifenisha ye-PowerPivot. Chofoza ukunamathisela okuzoletha ithebula lakho lookup elikopishiwe kusuka etafuleni elingezansi noma kusuka ku-Excel. Nikeza ithebula igama bese uqinisekisa ukuthi uhlola "Sebenzisa umugqa wokuqala njengezingqimba zekholomu." Chofoza OK. Udale ithebula lokubheka usuku ku-PowerPivot.

• Isinyathelo sesi-3 : Dala ubudlelwane phakathi kweNyanga yenyanga etafuleni le-oda kanye nensimu yeNyangaNumber kuthebula lokungena. Chofoza ku-Ribbon Design bese ukhetha Yakha Ubuhlobo. Yenza okukhethwa kukho ku-Ingxoxo Yakha Ubudlelwano bese uchofoza Yakha.

Futhi, ngokungezelela ubukhulu bezinsuku, uzokwazi ukuqoqa idatha ku-PivotTable yakho usebenzisa noma yimaphi amanani ahlukile kusukela kuthebula lokubuka losuku. Ukuqoqa ngekota noma igama lenyanga kuzoba yi-snap.

I-Sample Dimension (Lookup) Amathebula

Ithebula 1

Ithebula 2