PowerPivot ya Table Excel - Kuwonekera mu Zomwe Zagulitsa

Chimodzi mwa zinthu zomwe ndikulemba kwambiri pa PowerPivot kwa Excel ndizokwanira kuwonjezera matebulo kwa ma data anu. Nthawi zambiri, deta yomwe mukugwira nayo ilibe munda uliwonse womwe mukuufuna kuti musanthule. Mwachitsanzo, mungakhale ndi gawo lamasewera koma muyenera kugawa deta yanu pa kotala. Mukhoza kulemba fomu, koma ndi zophweka kupanga tebulo losavuta kuwona mkati mwa malo a PowerPivot.

Mungagwiritsenso ntchito tebulo loyang'ana pa gulu lina monga dzina la mwezi ndi theka / theka lachiwiri la chaka. Mu deta yosungiramo mawu, mumakhala ndikupanga tebulo lapamwamba. M'nkhaniyi, ndikupatsani chitsanzo chazing'ono kuti muthe kukweza PowerPivot kwa Project Excel.

Tsamba la New Text Dimension (Lookup)

Tiyeni tikambirane tebulo ndi deta yolongosola (data ya Contoso kuchokera ku Microsoft ikuphatikizapo ndondomeko ya deta). Ganizirani kuti tebulo liri ndi minda ya kasitomala, tsiku lolamula, chiwerengero cha dongosolo, ndi mtundu wa dongosolo. Titi tiganizire pa munda wamtunduwu. Tangoganizani kuti mtundu wamtunduwu uli ndi mfundo monga:

• Netbooks
• Desktops
• Zojambula
• Okonza
• Makina osindikiza
• Akanema
• Makamera a Digital
• Digital SLR makamera
• Makamera a Mafilimu
• Makanema
• Mafoni a Pafoni
• Mafoni apamwamba
• PDAs
• Mafoni a M'manja

Zoona zenizeni, mukanakhala ndi zizindikiro za izi koma kusunga chitsanzochi kukhala chophweka, kuganiza kuti izi ndizofunikira kwenikweni patebulo.

Pogwiritsira ntchito PowerPivot kwa Excel, mumatha kugawa malamulo anu mwa mtundu wa dongosolo. Bwanji ngati mukufuna gulu losiyana? Mwachitsanzo, taganizirani kuti mukufunikira gulu la "gulu" monga makompyuta, makamera, ndi mafoni. Gome ladongosolo silili ndi "gawo", koma mukhoza kulipanga ngati tebulo lokulumikiza ku PowerPivot kwa Excel.

Tchati chotsatira choyang'ana chotsatira chiri pansipa pa Table 1 . Nazi njira:

• Khwerero 1: Mukufunikira mndandanda wosiyana kuchokera kumtundu wamtundu wa tebulo lanu loyang'ana. Ichi ndi malo anu ochezera. Kuchokera m'dongosolo lanu la deta, pangani mndandanda wosiyana wa zoyendetsera kuchokera kumtundu wamtunduwu. Lowani mndandanda wosiyana wa "mitundu" mu bukhu la ntchito ya Excel. Lembani Mtundu wamtundu.
• Khwerero 2: M'ndandanda yomwe ili pafupi ndi chithunzi chanu (Mtundu), yonjezerani munda watsopano womwe mukufuna kuti mugwirizane nawo. Mu chitsanzo chathu, yonjezerani ndondomeko yokhala ndi chizindikiro chotchedwa Category.
• Khwerero 3: Phindu lirilonse mu mndandanda wanu wazinthu zoyenera (zitsanzo mu chitsanzo ichi), onjezerani ziyeneretso za "Category". Mu chitsanzo chathu chophweka, lowetsani makompyuta, makamera kapena makanema mu gawo la Gawo.
• Khwerero 4: Lembani tebulo la deta ndi mtundu wa m'kabokosi.
• Khwerero 5: Tsegulani buku la Excel ndi dongosolo la PowerPivot kwa Excel. Yambani Window ya PowerPivot. Dinani kuphatikiza zomwe zidzabweretse tebulo lanu loyang'ana. Perekani tebulo dzina ndipo onetsetsani kuti muyang'ane "Gwiritsani ntchito mzere woyamba ngati mitu yamutu." Dinani OK. Mwapanga tebulo loyang'ana ku PowerPivot.
• Khwerero 6: Pangani mgwirizano pakati pa gawo la mtundu mu tebulo la Order ndi gawo lachigawo pa tebulo loyang'ana. Dinani pa Design riboni ndi kusankha Kupanga Relationship. Pangani zosankhidwa mu Pangani Ubale wa Mauthenga ndipo dinani Pangani.

Mukamapanga PivotTable ku Excel pogwiritsa ntchito deta ya PowerPivot, mudzatha kugawana ndi gawo lanu latsopano. Kumbukirani kuti PowerPivot ya Excel yokha imathandizira maubwenzi amkati. Ngati muli ndi "mtundu wa dongosolo" wosapezeka pa tebulo lanu loyang'ana, zolemba zonse za mtundu umenewo sizikupezeka pa PivotTable iliyonse malinga ndi deta ya PowerPivot. Muyenera kuyang'ana izi nthawi ndi nthawi.

Tsiku Loyamba (Kufufuza) Patebulo

Pulogalamu ya Tsiku Lotip ikufunika kwambiri m'zinthu zambiri za PowerPivot za Excel. Zambiri zopezera deta zili ndi malo amtundu wina. Pali ntchito kuti muwerenge chaka ndi mwezi.

Komabe, ngati mukufuna mndandanda wa mwezi kapena kotala, muyenera kulembera machitidwe ovuta. Zimakhala zosavuta kuphatikiza tebulo lakutenga (kulumikiza) ndikulifananitsa ndi nambala ya mwezi mu deta yanu yaikulu. Muyenera kuwonjezera chikhomo ku tebulo lanu kuti muyimire nambala ya mwezi kuchokera kumtundu wamtunduwu. Dongosolo la DAX la "mwezi" mu chitsanzo chathu ndi "= MONTH ([Lamulo la Olamulira]." Izi zidzabwezera chiwerengero pakati pa 1 ndi 12 pa mbiri iliyonse. Zidzakuthandizani kuti mukhale osinthasintha muzomwe mukuwerengera. Zonsezi zotsatizana ndi deta ndizomwe zili m'munsimu mu Table 2 .

Mndandanda wa tanthauzo la tsikulo kapena loyang'anayo udzaphatikizapo zolemba 12. Chigawo cha mwezi chidzakhala ndi mfundo 1 - 12. Zina zidzakhala ndi malemba ophatikizidwa, mwezi wathunthulemba, kotala, ndi zina. Nazi izi:

• Khwerero 1: Lembani tebulo kuchokera pa Table 2 pansipa ndikuyika PowerPivot. Mungathe kupanga tebulo ili ku Excel koma ndikukupulumutsani nthawi. Muyenera kulumikiza mwachindunji deta yosankhidwa pansipa ngati mukugwiritsa ntchito Internet Explorer. PowerPivot imatengera tebulo kupanga ma test. Ngati mukugwiritsa ntchito sewero lina, mungafunikire kusonkhanitsa ku Excel poyamba ndi kulijambula kuchokera ku Excel kuti mutenge ma tebulo.
• Khwerero 2: Tsegulani buku la Excel ndi deta yanu mu PowerPivot kwa Excel. Yambani Window ya PowerPivot. Dinani kuphatikiza zomwe zingabweretse tebulo lanu loyang'ana lopangidwa kuchokera pa tebulo ili m'munsi kapena kuchokera ku Excel. Perekani tebulo dzina ndipo onetsetsani kuti muyang'ane "Gwiritsani ntchito mzere woyamba ngati mitu yamutu." Dinani OK. Mudapanga tebulo lokulumikiza tsiku ku PowerPivot.

• Khwerero 3 : Pangani mgwirizano pakati pa Mwezi wa Mwezi mu tebulo la Order ndi Mwezi wa MonthNumber mu tebulo loyang'ana. Dinani pa Design riboni ndi kusankha Kupanga Relationship. Pangani zosankhidwa mu Pangani Ubale wa Mauthenga ndipo dinani Pangani.

Kachiwiri, ndi Kuwonjezera kwa tsiku kukula, mudzatha kusonkhanitsa deta yanu mu PivotTable pogwiritsa ntchito njira iliyonse yosiyana kuchokera pa tebulo loyang'ana. Kugawanika ndi kotala kapena dzina la mweziwo kudzakhala kovuta.

Zitsanzo Zamakono (Lookup) Matebulo

Table 1

Table 2