Pivot tafura pane akawanda data renji

Kugadziriswa kwedambudziko

Pivot matafura chimwe chezvishandiso zvinoshamisa muExcel. Asi kusvika zvino, zvinosuruvarisa, hapana kana imwe yeshanduro dzeExcel inokwanisa kuita chinhu chakapfava uye chinodiwa panhunzi sekuvaka pfupiso yematanho akati wandei ekutanga anowanikwa, semuenzaniso, pamapepa akasiyana kana mumatafura akasiyana:

Tisati tatanga, ngatijekese pfungwa mbiri. A priori, ndinotenda kuti zvinotevera zvinosangana mune yedu data:

• Matafura anogona kuva nenhamba ipi neipi yemitsara ine chero data, asi inofanirwa kunge ine musoro wakafanana.
• Hapafanirwe kuve nekuwedzera data pamashiti ane masource table. Pepa rimwe - tafura imwe. Kuti udzore, ndinokupa zano kuti ushandise nzira yekudimbudzira keyboard Ctrl+magumo, izvo zvinokuendesa kune yekupedzisira kushandiswa sero papepa rebasa. Zvakanaka, iyi inofanira kunge iri yesero yekupedzisira mutafura yedata. Kana ukadzvanya pa Ctrl+magumo chero sero isina chinhu kurudyi kana pazasi petafura inosimbiswa - bvisa aya makoramu asina chinhu kurudyi kana mitsara pazasi petafura mushure metafura uye chengetedza faira.

Method 1: Vaka matafura epivot uchishandisa Power Query

Kutanga kubva ku2010 vhezheni yeExcel, kune yemahara Power Query yekuwedzera iyo inogona kuunganidza uye kushandura chero data uye wozoipa senzvimbo yekuvaka tafura yepivot. Kugadzirisa dambudziko redu nerubatsiro rwekuwedzera-mukati uku hakuna kuoma zvachose.

Kutanga, ngatigadzire faira nyowani isina chinhu muExcel - musangano uchaitika mariri uye ipapo tafura yepivot ichagadzirwa mairi.

Zvadaro pane tab Data (kana iwe uine Excel 2016 kana gare gare) kana pane iyo tab Simba Query (kana uine Excel 2010-2013) sarudza murairo Gadzira Mubvunzo - Kubva Faili - Excel (Tora Dhata - Kubva faira - Excel) uye tsanangura iyo faira faira ine matafura anozounganidzwa:

Muhwindo rinoonekwa, sarudza chero pepa (hazvina basa kuti ripi) uye tinya bhatani riri pazasi chinja (Hodha):

Iyo Power Query Query Editor hwindo rinofanira kuvhura pamusoro peExcel. Kurutivi rworudyi rwehwindo pane panel Kumbira Parameter bvisa matanho ese akagadzirwa otomatiki kunze kwekutanga - mabviro (Chinhu):

Iye zvino tinoona runyoro rwese mapepa ose. Kana pamusoro pemapepa edata kune mamwe mapeji epadivi mufaira, saka padanho rino basa redu nderekusarudza chete iwo mapepa kubva kune ruzivo rwunoda kutakurwa, kusasanganisa mamwe ese achishandisa sefa iri mumusoro wetafura:

Dzima makoramu ese kunze kwekoramu Datanekudzvanya-kurudyi musoro wekoromo uye kusarudza Dzima mamwe makoramu (Bvisa mamwe makoramu):

Iwe unogona ipapo kuwedzera zviri mukati mematafura akaunganidzwa nekudzvanya pane kaviri museve kumusoro kwekoromo (checkbox Shandisa zita rekutanga rechikamu sechivakashure unogona kuidzima):

Kana iwe wakaita zvese nemazvo, saka panguva ino iwe unofanirwa kuona zviri mukati mematafura ese akaunganidzwa pazasi peimwe:

Inoramba ichisimudza mutsara wekutanga kune musoro wetafura nebhatani Shandisa mutsara wekutanga semusoro (Shandisa mutsara wekutanga semusoro) Tab Home (Musha) uye bvisa duplicate tafura misoro kubva kune data uchishandisa sefa:

Chengetedza zvese zvakaitwa nemurairo Vhara uye takura - Vhara uye rodha mukati... (Vhara & Rodha - Vhara & Rodha ku…) Tab Home (Musha), uye pahwindo rinovhurwa, sarudza sarudzo Kubatanidza chete (Kubatanidza Chete):

Zvose. Inosara chete kuvaka pfupiso. Kuti uite izvi, enda kune tab Pinza – PivotTable (Isa - Pivot Tafura), sarudza sarudzo Shandisa external data source (Shandisa kunze data source)uyezve nekudzvanya bhatani Sarudza kubatana, chikumbiro chedu. Kumwe kusikwa uye kumisikidzwa kwepivot kunoitika nenzira yakajairika nekukweva minda yatinoda mumitsara, makoramu uye kukosha kwenzvimbo:

Kana iyo sosi data ikachinja mune ramangwana kana mamwe mashoma ezvitoro mapepa akawedzerwa, zvino zvichave zvakakwana kugadzirisa mubvunzo uye pfupiso yedu uchishandisa murairo. Zorodza zvese Tab Data (Data - Zorora Zvese).

Nzira 2. Isu tinobatanidza matafura neUNION SQL command mune macro

Imwe mhinduro kudambudziko redu inomiririrwa neiyi macro, iyo inogadzira data set (cache) yetafura yepivot uchishandisa rairo. UNITY SQL mubvunzo mutauro. Uyu murairo unosanganisa matafura kubva kune ese akatsanangurwa muhurongwa SheetNames mapepa ebhuku mune imwe data tafura. Ndokunge, pachinzvimbo chekukopa uye kubatika mitsara kubva pamapepa akasiyana kuenda kune rimwe, tinoita zvimwe chete mu RAM yekombuta. Ipapo iyo macro inowedzera pepa idzva rine zita rakapihwa (variable ResultSheetName) uye inogadzira yakazara-yakazara (!) pfupiso pairi zvichibva pane yakaunganidzwa cache.

Kuti ushandise macro, shandisa Visual Basic bhatani pane tab yokuvaka (Mugadziri) kana nzira yekudimbudzira keyboard alt+F11. Zvadaro tinoisa module itsva isina chinhu kuburikidza nemenyu Pinza – Module uye kopira kodhi inotevera ipapo:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() Se String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'sheet name apo pivot inozobuda icharatidzwa = Resultation' aSheetName mazita ane matafura ekwakabva SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'tinogadzira cache yematafura kubva pamasheetsNames ane ActiveWorkbook ReDim arSQL(1 Kuenda (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) Ku UBound(SheetsNames) arSQL(i + 1) = "SARUDZA * KUBVA [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Vhura Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Pedzisa ne 'gadzirazve pepa kuti uratidze tafura yepivot inenge yabuda PaKukanganisa Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Zita = ResultSheetName 'ratidza pfupiso yecache yakagadzirwa pabepa rino Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Hapana Ne wsPivot objPivotCache" =Rakat Table. objPivotCache = Nothing Range("A3").Sarudza End With End Sub    

Iyo macro yakapedzwa inogona kumhanyiswa nekapfupi kekhibhodi alt+F8 kana Macros bhatani pane tab yokuvaka (Mugadziri - Macros).

Cons of nzira iyi:

• Iyo data haina kuvandudzwa nekuti cache haina chinongedzo kumatafura ekwakabva. Kana iwe ukashandura iyo sosi data, iwe unofanirwa kumhanya iyo macro zvakare uye kuvaka iyo pfupiso zvakare.
• Kana uchichinja huwandu hwemashiti, zvinodikanwa kugadzirisa iyo macro kodhi (array SheetNames).
  

Asi pakupedzisira tinowana tafura yepivot yakazara yakazara, yakavakirwa pamatanho akati wandei kubva pamashiti akasiyana:

Voilà!

Technical note: kana iwe ukawana chikanganiso senge "Mupi asina kunyoreswa" paunenge uchimhanyisa macro, saka kazhinji iwe une 64-bit vhezheni yeExcel kana isina kukwana vhezheni yeHofisi yakaiswa (hapana Access). Kuti ugadzirise mamiriro acho, tsiva iyo chidimbu mune macro kodhi:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

kuti:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Uye dhawunirodha uye isa iyo yemahara data yekugadzirisa injini kubva Kuwana kubva kuMicrosoft webhusaiti - Microsoft Access Database Engine 2010 Redistributable

Method 3: Consolidate PivotTable Wizard kubva Old Versions yeExcel

Iyi nzira ndeyechinyakare, asi ichiri kukosha kutaurwa. Nekutaura zviri pamutemo, mushanduro dzese kusvika uye kusanganisira 2003, pakanga paine sarudzo muPivotTable Wizard ye "kuvaka pivot kune akati wandei ekubatanidza renji". Nekudaro, chirevo chakavakwa nenzira iyi, zvinosuwisa, chinongove chimiro chinosiririsa chechidimbu chakazara uye haitsigire akawanda e "chips" ematafura epivot akajairwa:

Mune pivot yakadaro, hapana misoro yemakoramu mune runyorwa rwemunda, hapana chinochinjika chimiro chekumisikidza, seti yemabasa anoshandiswa inogumira, uye, kazhinji, zvese izvi hazvina kunyanya kufanana netafura yepivot. Zvichida ndosaka, kutanga muna 2007, Microsoft yakabvisa basa iri kubva kune yakajairwa dialog pakugadzira mishumo yetafura yepivot. Iye zvino chimiro ichi chinowanikwa chete kuburikidza nebhatani retsika PivotTable Wizard(Pivot Tafura Wizard), iyo, kana ichida, inogona kuwedzerwa kuKurumidza Kupinda Toolbar kuburikidza Faira - Sarudzo - Gadzirisa Kurumidza Kupinda Toolbar - Yese Mirairo (Faira - Sarudzo - Gadzirisa Kurumidza Kuwana Toolbar - Yese Mirairo):

Mushure mekudzvanya bhatani rakawedzerwa, unofanirwa kusarudza iyo yakakodzera sarudzo padanho rekutanga rewizard:

Uye pahwindo rinotevera, sarudza yega yega nhanho uye woiwedzera kune yakajairika runyorwa:

Asi, zvakare, iyi haisi pfupiso yakazara, saka usatarisire zvakawandisa kubva kwairi. Ndinogona kukurudzira iyi sarudzo chete mumatambudziko akareruka.

• Kugadzira Mishumo nePivotTables
• Gadzira maverengero muPivotTables
• Chii macros, maitiro ekuashandisa, kupi kukopa VBA kodhi, nezvimwe.
• Kuunganidzwa kwedata kubva kune akawanda mapepa kune rimwe (PLEX yekuwedzera)