Kusefa Ma PivotTable Angapo Nthawi Imodzi

Mukapanga malipoti ovuta komanso, makamaka ma dashboards mu Microsoft Excel, nthawi zambiri ndikofunikira kusefa magome angapo nthawi imodzi. Tiyeni tione mmene zimenezi zingakwaniritsidwire.

Njira 1: General Slicer yosefa ma pivots pa gwero la data lomwelo

Ngati ma pivots amamangidwa pamaziko a tebulo limodzi la deta, ndiye njira yosavuta ndiyo kuwagwiritsa ntchito kuwasefa nthawi imodzi. gawo ndi zosefera za batani lojambula zolumikizidwa ndi ma pivot table onse nthawi imodzi.

Kuti muwonjezere, sankhani selo iliyonse muchidule chimodzi ndi pa tabu Analysis sankhani timu Matani Kagawo (Yendetsani - Ikani chodulira). Pazenera lomwe limatsegulidwa, fufuzani mabokosi amipingo yomwe mukufuna kusefa deta ndikudina OK:

Chodulira chomwe chidapangidwacho, mwachikhazikitso, chidzasefa pivot yokha yomwe idapangidwira. Komabe, kugwiritsa ntchito batani Nenani Malumikizidwe (Nenani za kulumikizana) tsamba Kagawo (Magawo) titha kuwonjezera mosavuta matebulo ena achidule pamndandanda wamatebulo osefedwa:

Njira 2. Kagawo kakang'ono ka kusefa mwachidule pa magwero osiyanasiyana

Ngati ma pivots anu sanamangidwe molingana ndi imodzi, koma malinga ndi matebulo osiyanasiyana a deta, ndiye kuti njira yomwe ili pamwambayi siigwira ntchito, chifukwa pawindo. Nenani Malumikizidwe Ndemanga zokhazo zomwe zidamangidwa kuchokera kugwero lomwelo ndizomwe zikuwonetsedwa.

Komabe, mutha kuzungulira izi mosavuta ngati mutagwiritsa ntchito Data Model (tidakambirana mwatsatanetsatane m'nkhaniyi). Tikayika matebulo athu mu Model ndikuwalumikiza pamenepo, ndiye kusefa kudzagwira ntchito pamagome onse awiri nthawi imodzi.

Tinene kuti tili ndi matebulo awiri amitengo yogulitsa ndi mayendedwe ngati zolowetsa:

Tiyerekeze kuti tikuyang'anizana ndi ntchito yomanga chidule chathu cha aliyense wa iwo ndikusefa nthawi imodzi ndi mizinda yokhala ndi kudula wamba.

Timachita izi:

1. Kutembenuza Matebulo Athu Oyambirira Kukhala Matebulo Anzeru Amphamvu Ndi Njira Yachidule ya Kiyibodi Ctrl+T kapena malamulo Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table) ndi kuwapatsa maina tablProdaji и tabTransport tsamba Constructor (Kapangidwe).

2. Kwezani magome onse awiriwo mu Model pogwiritsa ntchito batani Onjezani ku Data Model pa Power Pivot tabu.

Sizingatheke kulumikiza mwachindunji matebulowa mu Model, chifukwa pomwe Power Pivot imathandizira maubwenzi amodzi ndi ambiri, mwachitsanzo, imafuna kuti imodzi mwamatebulo ikhale yopanda zobwereza muzambiri zomwe tikulumikiza. Tili ndi zofanana m'magome onse awiri m'munda maganizo pali kubwereza. Chifukwa chake tifunika kupanga tebulo lina lapakati loyang'ana ndi mndandanda wa mayina apadera amizinda kuchokera pamagome onse awiri. Njira yosavuta yochitira izi ndi ntchito yowonjezera ya Power Query, yomwe idamangidwa mu Excel kuyambira mtundu wa 2016 (ndi Excel 2010-2013 imatsitsidwa kwaulere patsamba la Microsoft).

3. Titasankha selo iliyonse mkati mwa tebulo la "smart", timawayika mmodzimmodzi mu Power Query ndi batani Kuchokera pa tebulo/ranji tsamba Deta (Deta - Kuchokera patebulo/mitundu) ndiyeno pawindo la Power Query sankhani waukulu magulu Tsekani ndi katundu - Tsekani ndikulowetsani (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku…) ndi njira yolowera Ingopangani kulumikizana (Pangani kulumikizana kokha):

4. Timagwirizanitsa matebulo onse awiri kukhala amodzi ndi lamulo Deta - Phatikizani Mafunso - Onjezani (Deta - Phatikizani mafunso - Ikani). Mizati yokhala ndi mayina omwewo pamutu idzakwanira pansi pa mzake (monga ndime maganizo), ndipo zomwe sizikufanana zidzayikidwa m'magawo osiyanasiyana (koma izi sizofunikira kwa ife).

5. Chotsani zigawo zonse kupatula gawo maganizopodina kumanja pamutu wake ndikusankha lamulo Chotsani zigawo zina (Chotsani zigawo zina) ndiyeno chotsani mayina amizinda obwerezabwereza ndikudina kumanja pamutu womwe uli patsamba ndikusankhanso lamulo Chotsani Zobwerezedwa (Chotsani zobwereza):

6. Mndandanda wamareferensi opangidwa umakwezedwa ku Data Model kudzera Kunyumba - Tsekani ndikutsegula - Tsekani ndikulowetsani (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku…) ndipo sankhani kusankha Ingopangani kulumikizana (Pangani kulumikizana kokha) ndi chinthu chofunikira kwambiri! - Yatsani bokosi loyang'anira Onjezani deta iyi kumtundu wa data (Onjezani deta iyi ku Data Model):

7. Tsopano titha, kubwerera ku zenera la Power Pivot (tabu mphamvu pivot - batani Management), sinthani ku Mawonedwe a Ma chart (Mawonedwe a zojambulajambula) ndikulumikizani mitengo yathu yazogulitsa ndi zoyendera kudzera mu bukhu lapakati lamizinda (pokokera minda pakati pa matebulo):

8. Tsopano mutha kupanga matebulo onse ofunikira amtundu womwe wapangidwa pogwiritsa ntchito batani mwachidule tebulo (Pivot Table) on waukulu (Kunyumba) tabu pawindo la Power Pivot ndipo, posankha selo iliyonse mu pivot iliyonse, pa tabu Analysis yonjezerani batani Matani Kagawo (Yendetsani - Ikani Slicer) ndi kusankha kagawo mu ndandanda bokosi maganizo m'ndandanda yowonjezera:

Tsopano, mwa kuwonekera pa batani lodziwika bwino Nenani Malumikizidwe on Kagawo tabu (Slicer - Report maulalo) tiwona chidule chathu chonse, chifukwa tsopano chamangidwa pamatebulo ogwirizana nawo. Zimatsalira kuti zithandizire ma checkbox omwe akusowa ndikudina OK - ndipo slicer yathu iyamba kusefa magome onse osankhidwa nthawi imodzi.