Ihlunga ama-PivotTables amaningi ngesikhathi esisodwa

Lapho udala imibiko eyinkimbinkimbi futhi, ikakhulukazi, amadeshibhodi ku-Microsoft Excel, ngokuvamile kuyadingeka ukuhlunga amathebula e-pivot amaningana ngesikhathi esisodwa. Ake sibone ukuthi lokhu kungenziwa kanjani.

Indlela 1: I-General Slicer yokuhlunga ama-pivots emthonjeni ofanayo wedatha

Uma ama-pivots akhelwe phezu kwesisekelo sethebula ledatha elilodwa lomthombo, indlela elula ukuwasebenzisa ukuze uwahlunge kanyekanye. ingxenye isihlungi senkinobho yesithombe esixhunywe kuwo wonke amathebula e-pivot ngesikhathi esisodwa.

Ukuze uyengeze, khetha noma iyiphi iseli kwesinye isifinyezo nakuthebhu Analysis khetha iqembu Namathisela Ucezu (Hlaziya - Faka isisiki). Ewindini elivulayo, khetha amabhokisi amakholomu ofuna ukuhlunga ngawo idatha bese uchofoza OK:

Isisiki esidaliwe, ngokuzenzakalelayo, sizohlunga kuphela i-pivot esadalelwe yona. Nokho, usebenzisa inkinobho Bika Ukuxhumana (Bika ukuxhumana) ithebhu Slice (Izingcezu) singangeza kalula amanye amathebula afingqiwe ohlwini lwamathebula ahlungiwe:

Indlela 2. Ucezu olujwayelekile lokuhlunga izifinyezo emithonjeni ehlukene

Uma ama-pivots akho akhiwe hhayi ngokusho kweyodwa, kodwa ngokusho kwamatafula edatha yomthombo ohlukene, indlela engenhla ngeke isebenze, ngoba efasiteleni Bika Ukuxhumana kuphela lezo zifinyezo ezakhiwe emthonjeni ofanayo eziboniswayo.

Kodwa-ke, ungakwazi ukuzungeza lo mkhawulo kalula uma usebenzisa Imodeli Yedatha (sixoxe ngayo ngokuningiliziwe kulesi sihloko). Uma silayisha amatafula ethu kuModel futhi siwaxhumanisa lapho, khona-ke ukuhlunga kuzosebenza kuwo womabili amatafula ngesikhathi esisodwa.

Ake sithi sinamathebula amabili ezindleko zokuthengisa nezokuthutha njengedatha yokufaka:

Ake sithi sibhekene nomsebenzi wokwakha isifinyezo sethu sazo zonke bese siwahlunga kanyekanye ngamadolobha anokusika okuvamile.

Senza lokhu okulandelayo:

1. Ukuguqula Amathebula Ethu Oqobo Kube Amathebula Ahlakaniphile Anamandla Ngesinqamuleli Sekhibhodi Ctrl+T noma imiyalo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula) futhi uzibize ngamagama tablProdaji и tabEzokuthutha ithebhu umakhi (Umklamo).

2. Layisha womabili amathebula ngokushintshana abe Imodeli usebenzisa inkinobho Engeza Kumodeli Yedatha kuthebhu ye-Pivot yamandla.

Ngeke kwenzeke ukuxhumanisa ngokuqondile lawa mathebula Kumodeli, ngoba nakuba i-Power Pivot isekela kuphela ubudlelwano phakathi kokukodwa kuya kokuningi, okungukuthi idinga ukuthi elinye lamathebula lingabi nezimpinda kukholamu esixhumanisa kuyo. Sinakho okufanayo kuwo womabili amatafula ensimini imizwa kukhona ukuphindaphinda. Ngakho-ke sidinga ukudala elinye ithebula lokubheka elimaphakathi elinohlu lwamagama edolobha ahlukile kuwo womabili amathebula. Indlela elula yokwenza lokhu iwumsebenzi wokwengeza Wombuzo Wamandla, owakhelwe ku-Excel kusukela kunguqulo yango-2016 (futhi ku-Excel 2010-2013 ilandwa mahhala kuwebhusayithi ye-Microsoft).

3. Ngemva kokukhetha noma iyiphi iseli ngaphakathi kwethebula elithi “smart”, siyilayisha ngayinye ngayinye kokuthi Umbuzo Wamandla ngenkinobho Kusukela kuthebula/ububanzi ithebhu Idatha (Idatha - Kusuka kuthebula/ububanzi) bese efasiteleni Lombuzo Wamandla khetha on The main amaqembu Vala futhi ulayishe - Vala futhi ulayishe (Ekhaya — Vala&Layisha — Vala&Layisha ku…) kanye nenketho yokungenisa Vele udale uxhumano (Dala uxhumano kuphela):

4. Sihlanganisa womabili amatafula abe linye ngomyalo Idatha - Hlanganisa Imibuzo - Engeza (Idatha - Hlanganisa imibuzo - Faka). Amakholomu anamagama afanayo kunhlokweni azongena ngaphansi kwelinye (njengekholomu imizwa), futhi lezo ezingahambisani zizofakwa kumakholomu ahlukene (kodwa lokhu akubalulekile kithi).

5. Susa wonke amakholomu ngaphandle kwekholomu imizwangokuchofoza kwesokudla esihlokweni sayo bese ukhetha umyalo Susa amanye amakholomu (Susa amanye amakholomu) bese ususa wonke amagama amadolobha ayimpinda ngokuchofoza kwesokudla isihloko sekholomu futhi nokukhetha umyalo Susa izimpinda (Susa izimpinda):

6. Uhlu oludaliwe lwereferensi lulayishwa kumodeli yedatha nge Ikhaya — Vala bese ulayisha — Vala bese ulayisha ngaphakathi (Ekhaya — Vala&Layisha — Vala&Layisha ku…) bese ukhetha okukhethwa kukho Vele udale uxhumano (Dala uxhumano kuphela) futhi into ebaluleke kakhulu! – vula ibhokisi lokuhlola Engeza le datha kumodeli yedatha (Engeza le datha Kumodeli Yedatha):

7. Manje singakwazi, sibuyela efasiteleni le-Pivot Yamandla (ithebhu I-Powerpivot - Inkinobho Ukuphathwa), shintshela ku Ukubuka Kweshadi (Ukubuka umdwebo) futhi sixhumanise amathebula ethu okuthengisa nezindleko zokuhamba ngohla lwemibhalo oludaliwe olumaphakathi lwamadolobha (ngokuhudula izinkambu phakathi kwamatafula):

8. Manje usungakwazi ukudala wonke amathebula e-pivot adingekayo emodeli edaliwe usebenzisa inkinobho isifinyezo ithebula (Ithebula Le-Pivot) on The main (Ikhaya) ithebhu efasiteleni le-Pivot Yamandla futhi, ngokukhetha noma iyiphi iseli kunoma iyiphi i-pivot, kuthebhu Analysis engeza inkinobho yocezu Namathisela Ucezu (Hlaziya — Faka Isisiki) bese ukhetha ukusika ebhokisini lohlu imizwa ohlwini lwemibhalo olungeziwe:

Manje, ngokuchofoza inkinobho ejwayelekile Bika Ukuxhumana on Sika ithebhu (I-Slicer - Bika ukuxhumana) sizobona zonke izifinyezo zethu, ngoba manje zakhelwe kumathebula omthombo ahlobene. Kusele ukunika amandla amabhokisi okuhlola angekho bese uchofoza OK - futhi isisiki sethu sizoqala ukuhlunga wonke amatafula e-pivot akhethiwe ngesikhathi esisodwa.