PowerPivot yeExcel - Kuona Tafura muDare Rekuchengetedza

Chimwe chezvinhu zvandinonyanya kunyora pamusoro pePowerPivot yeExcel ndiko kukwanisa kuwedzera tafura pamatafura ako. Kazhinji yenguva, data yauri kushanda nayo haina munda wose waunoda kuti uongorore. Semuenzaniso, iwe unogona kuva nemunda wemazuva asi unoda kuunganidza data yako nekota. Iwe unogona kunyora chimiro, asi zviri nyore kugadzira tafura iri nyore yekuona mukati mePowerPivot.

Iwe unogona kushandisawo tafura iyi yekuona kune rimwe boka rakadai semwedzi uye zita uye yekutanga / yechipiri hafu yegore. Mune deta kushandiswa kwemashoko, iwe uri kunyanya kusika dhesi yepamusoro tafura. Muchikamu chino, ndiri kuenda kukupa mienzaniso miviri inoshandiswa mafuraji kuti uwedzere PowerPivot yako yeExcel project.

New Text Dimension (Lookup) Tafura

Ngationgororei tafura nehurongwa hwekutsvaga (iyo Contoso data kubva kuMicrosoft inosanganisira data yakagadzirirwa mifananidzo kune iyi). Funga kuti tafura ine minda yevatengi, zuva rekurongeka, kurongeka kwese, uye marongerwo emhando. Tichaisa pfungwa pakurongeka. Funga kuti urongwa hwemhando yepakati hunosanganisira tsika dzakadai se:

• Netbooks
• Desktops
• Monitors
• Projectors
• Printers
• Scanani
• Digital Cameras
• Digital SLR Makamera
• Mafirimu Makamera
• Makhamera
• Office Phones
• Smart phones
• PDAs
• Cell Phones Accessories

Muzvokwadi, iwe unenge uine mazano kune izvi asi kuti uchengetedze muenzaniso uyu usinganzwisisiki, funga kuti aya ndiwo maonero chaiwo mumutsetse wepafura.

Uchishandisa PowerPivot yeExcel, iwe unogona kukwanisa kuisa mairairo ako nekurongeka. Ko kana iwe uchida boka rakasiyana? Somuenzaniso, fungidzira iwe unoda "boka" muchikwata semakombiyuta, makamera, uye mafoni. Itafura yakarongeka haina "category" munda, asi iwe unogona kuigadzira nyore nyore setafura yekuona mu PowerPivot yeExcel.

Muenzaniso wakakwana wekuona tafura uri pasi apa muTebvu 1 . Heano matanho:

• Nhanho 1: Unoda ruzivo rwakasiyana kubva kune rumwe rudzi rwomutambo wetafura yako. Ichi chichava nzvimbo yako yekuona. Kubva pane deta yako yakagadzirisa, ita rondedzero yakasiyana yehutano kubva pane zvakarongeka. Ita rondedzero yakasiyana ye "marudzi" muBhuku rebhuku re Excel. Tora iyo Rudzi rwepepa.
• Nhanho 2: Muchikamu chiri pedo nekuona kwekutevera yako (Tsika), wedzera munda mutsva waunoda kuisa paari. Mumuenzaniso wedu, wedzera imwe koromo ine zita rakanzi Chikamu.
• Nhanho 3: Kukosha kwega kwega mune rondedzero yako yakasiyana yehutsika (mhando iri mumuenzaniso uyu), wedzera "Maitiro" anoenderana. Muenzaniso wedu uri nyore, tumira maMakombiyuta, Makamera kana Mapikoni muChikamu.
• Danho 4: Kopa Mutauro uye Category data tafura mubhodhibhodhi yako.
• Nhanho 5: Vhura bhuku rebhuku reExcel ne data yekuita mu PowerPivot yeExcel. Shandisa WindPivot Window. Dzvanya Peta iyo inounza tafura yako yekutsvaga. Ipa tafura zita uye uone kuti unotarisa "Shandisa mutsara wokutanga sechindori zvinyorwa." Dzvanya OK. Iwe wakagadzira tafura yekuona mu PowerPivot.
• Danho 6: Ita ukama pakati Pechigadziro mumunda weDheta yeDheta uye Chikamu chemukati patafura yekuona. Dzvanya paGadzira ribhoni uye sarudza Create Relationship. Ita sarudzo muGadzira Relationship dialog uye chenga Dza.

Paunenge uumba PivotTable mu Excel zvichienderana neDynamPivot data, iwe uchakwanisa kuunganidza nehurukuro yako yeClass field. Ramba uchifunga kuti PowerPivot ye Excel inotsigira Inner Joins. Kana uine "chirongwa chekurongeka" chiripo kubva patafura yako yekuona, zvese zvinyorwa zvakarongeka zvechimiro ichocho zvichange zvisipo kubva pane chero ipi zvayo PivotTable zvichienderana neDynamPivot data. Iwe uchada kutarisisa izvi nenguva nenguva.

Zuva Dhigiri (Lookup) Dhafura

Ita Date lookup table inogona kudiwa mune zvizhinji zvePowerPivot yako yeExcel mirairo. Nyaya dzakawanda dzepa data dzine imwe yemhando yemasaini (s). Kune mabasa ekuverenga gore nemwedzi.

Zvisinei, kana uchida mhemberero yemwedzi chaiyo kana kuti yechina, unoda kunyora maitiro akaoma. Zviri nyore nyore kusanganisira Date dimension (lookup) tafura uye kuitanidza nayo nomwedzi wechirangaridzo mune deta yako huru yekuisa. Iwe uchadikanwa kuwedzera kenduru kune yako dheta tafura kuti uimirire nhamba yemwedzi kubva pamusi wezuva. Nzira ye DAX ye "mwedzi" mumuenzaniso wedu ndeye "= MUTS ([Mutemo Wemitambo]) Izvi zvichadzoka nhamba pakati pe1 ne12 ye rekodhi imwe neimwe Tafura yedu inobatsira ichapa mamwe maitiro, ayo anowirirana nomwedzi wegore. ichakupa iwe kushandura mafungiro mukuongorora kwako.

Zuva iro dhidhi kana tafura yekuona inozobatanidza zvinyorwa 12. Nyaya yemwedzi ichava nemitengo 1 - 12. Dzimwe mimwe misoro ichabatanidza mheteti yemwedzi yakanyorwa, yemwedzi yakazara rugwaro, kotanhatu, etc. Ndiwo matanho:

• Danho 1: Kopora tafura kubva paTebhesi 2 iri pasi uye unamatira kuPowerPivot. Iwe unogona kugadzira tafura iyi muExcel asi ini ndiri kukuponesa iwe nguva. Iwe unogona kukwanisa kubatanidza zvakananga kubva pane zvakasarudzwa data pasi apa kana uri kushandisa Internet Explorer. PowerPivot inotora tafura yekugadzirisa pakuedza kwangu. Kana uri kushandisa imwe shanduro, iwe unofanirwa kuisa muExcel kutanga uye kukopa iyo kubva kuExcel kuti utore mafomu ekugadzira.
• Nhanho 2: Vhura bhuku rebhuku re Excel pamwe nedharo yeDynamPivot ye Excel. Shandisa WindPivot Window. Dzvanya Peta iyo inounza patafura yako yekuona yakakopiwa kubva patafura iri pasi kana kubva kuExcel. Ipa tafura zita uye uone kuti unotarisa "Shandisa mutsara wokutanga sechindori zvinyorwa." Dzvanya OK. Iwe wakagadzira tafura yekutsvaga kwezuva mu PowerPivot.

• Danho rechitatu : Ita ukama pakati peMwedzi mwedzi mutafura yeDheta uye MonthNumber field mumutambo wekuona. Dzvanya paGadzira ribhoni uye sarudza Create Relationship. Ita sarudzo muGadzira Relationship dialog uye chenga Dza.

Zvakare, pamwe nekuwedzerwa kwezuva rakakosha, iwe uchakwanisa kuunganidza data muPivotTable yako uchishandisa chero ipi zvayo yakasiyana-siyana kubva pagore rekuona hookup. Kugovera nechechina kana zita remwedzi kuchava shanje.

Sample Dimension (Lookup) Tables

Tafura 1

Tafura 2