Kusefa Multiple PivotTables Panguva imwe chete

Paunenge uchigadzira mishumo yakaoma uye, kunyanya, madhibhodhi muMicrosoft Excel, zvinonyanya kukosha kuti panguva imwe chete kusefa akati wandei matafura kamwechete. Ngationei kuti izvi zvingaitwa sei.

Method 1: General Slicer yekusefa pivots pane imwecheteyo data sosi

Kana mapivots akavakwa pahwaro hweimwe sosi data tafura, saka nzira iri nyore ndeyekuishandisa kuisefa panguva imwe chete. chidimbu iri graphic bhatani sefa yakabatana kune ese matafura epivot kamwechete.

Kuti uiwedzere, sarudza chero sero mune imwe yepfupiso uye pane tab ongororo sarudza timu Namatidza Chikamu (Ongorora - Isa slicer). Muhwindo rinovhurwa, tarisa mabhokisi emakoramu aunoda kusefa data uye tinya OK:

Iyo yakagadzirwa slicer ichaita, nekukasira, kusefa chete pivot iyo yayakagadzirirwa. Zvisinei, kushandisa bhatani Report Connections (Mhan'ara kubatanidza) Tab Slice (Zvimedu) isu tinogona kuwedzera nyore mamwe matafura epfupiso kune rondedzero yematafura akasefa:

Method 2. General slice yekusefa zvipfupiso pane zvakasiyana siyana

Kana mapivots ako akavakwa kwete maererano neimwe, asi zvichienderana neakasiyana dhata matafura, ipapo nzira iri pamusoro haishande, nekuti pahwindo. Report Connections izvo chete zvipfupiso izvo zvakavakwa kubva kune imwechete sosi ndiyo inoratidzwa.

Nekudaro, iwe unogona nyore kutenderedza ichi chipimo kana iwe ukashandisa iyo Data Model (takakurukura zvakadzama muchinyorwa chino). Kana tikaisa matafura edu muMuenzaniso uye tikaabatanidza ipapo, ipapo kusefa kuchashanda kune ese matafura panguva imwe chete.

Ngatitii isu tine matafura maviri ekutengesa nemitengo yekufambisa sedata rekuisa:

Ngatitii takatarisana nebasa rekuvaka pfupiso yedu kune yega yega uye tobva tazvisefa panguva imwe chete nemaguta ane kucheka kwakafanana.

Isu tinoita zvinotevera:

1. Kushandura Matafura Edu Ekutanga kuita Dynamic Smart Tables ane Keyboard Shortcut Ctrl+T kana mirairo Kumba – Fomati setafura (Kumba - Fomati seTafura) ugovapa mazita tablProdaji и tabTransport Tab Constructor (Dhizaini).

2. Rodha matafura ese ari maviri achichinjana kuita Model uchishandisa bhatani Wedzera kune Data Model pane Power Pivot tab.

Hazvizogoneke kubatanidza zvakananga matafura aya muModheru, nekuti nepo Power Pivot ichingotsigira hukama-kune-vakawanda, kureva kuti inoda kuti imwe yematafura ive isina kopi mukoramu yatiri kubatanidza pairi. Tine zvakafanana mumatafura maviri ari mumunda manzwiro pane dzokororo. Saka isu tinofanirwa kugadzira imwe yepakati yekutarisa tafura ine runyorwa rweakasarudzika mazita eguta kubva kumatafura ese ari maviri. Nzira iri nyore yekuita izvi ndeye Power Query yekuwedzera-mukushanda, iyo yakavakwa muExcel kubvira iyo 2016 vhezheni (uye yeExcel 2010-2013 inotorwa mahara kubva kuMicrosoft webhusaiti).

3. Tasarudza chero sero mukati me "smart" tafura, tinovaisa imwe neimwe muPower Query nebhatani Kubva patafura/renji Tab Data (Data - Kubva patafura / renji) uye ipapo muSimba Query hwindo sarudza pa huru mapoka Vhara uye takura - Vhara uye rodha mukati (Kumba - Vhara & Rodha - Vhara & Rodha ku…) uye import sarudzo Ingogadzira chinongedzo (Gadzira kubatana chete):

4. Isu tinobatanidza matafura maviri mune imwe nemirairo Dhata - Batanidza Mibvunzo - Wedzera (Data - Sanganisa mibvunzo - Wedzera). Makoramu ane mazita akafanana mumusoro anokwana pasi peumwe neumwe (senge column manzwiro), uye izvo zvisingaenderane zvichaiswa mumakoramu akasiyana (asi izvi hazvina kukosha kwatiri).

5. Dzima makoramu ese kunze kwekoramu manzwironekudzvanya-kurudyi pane zita rayo uye kusarudza rairo Dzima mamwe makoramu (Bvisa mamwe makoramu) wobva wabvisa mazita ese eguta nekudzvanya-kurudyi koramu inotungamira zvakare uye nekusarudza rairo Bvisa Maduplicates (Bvisa zvakapetwa):

6. Iyo yakagadzirwa referensi runyorwa inoiswa kuData Model kuburikidza Musha - Vhara uye takura - Vhara uye urodze mukati (Kumba - Vhara & Rodha - Vhara & Rodha ku…) uye sarudza sarudzo Ingogadzira chinongedzo (Gadzira kubatana chete) uye chinhu chinonyanya kukosha! – batidza bhokisi rekutarisa Wedzera iyi data kune data model (Wedzera iyi data kune Data Model):

7. Zvino isu tinogona, kudzokera kuPower Pivot hwindo (tab powerpivot - bhatani utariri), chinja ku Sravana Sameeralu Serial XNUMXth Chart View (Dhiyagiramu maonero) uye batanidza matafura edu ekutengesa nemitengo yekufambisa kuburikidza neyakagadzirwa yepakati dhairekitori remaguta (nekudhonza minda pakati pematafura):

8. Iye zvino unogona kugadzira ese anodiwa pivot matafura eiyo yakagadzirwa modhi uchishandisa bhatani pfupiso tafura (Pivot Table) on huru (Musha) tab muhwindo reSimba Pivot uye, nekusarudza chero sero mune chero pivot, pane iyo tab ongororo wedzera bhatani rekucheka Namatidza Chikamu (Ongorora - Isa Slicer) uye sarudza kucheka mubhokisi rerondedzero manzwiro mune yakawedzera dhairekitori:

Zvino, nekudzvanya pabhatani raunoziva Report Connections on Slice tab (Slicer - Mharidzo yekubatanidza) tichaona pfupiso yedu yese, nekuti iye zvino yakavakirwa pane inoenderana sosi matafura. Inoramba ichigonesa mabhokisi ekutarisa asipo uye tinya pa OK - uye slicer yedu ichatanga kusefa ese akasarudzwa matafura epivot panguva imwe chete.