Pivot table sa daghang mga han-ay sa datos

Pagporma sa problema

Ang mga lamesa sa pivot usa sa labing katingad-an nga mga himan sa Excel. Apan hangtod karon, sa kasubo, wala’y bisan usa sa mga bersyon sa Excel nga makahimo sa ingon usa ka yano ug kinahanglan nga butang sa langaw sama sa paghimo og usa ka katingbanan alang sa daghang mga inisyal nga han-ay sa datos nga nahimutang, pananglitan, sa lainlaing mga sheet o sa lainlaing mga lamesa:

Sa dili pa kita magsugod, atong klarohon ang pipila ka mga punto. A priori, nagtuo ko nga ang mosunod nga mga kondisyon natuman sa among datos:

• Ang mga lamesa mahimong adunay bisan unsang gidaghanon sa mga laray nga adunay bisan unsang datos, apan kinahanglan adunay parehas nga ulohan.
• Kinahanglang walay dugang nga datos sa mga palid nga adunay tinubdan nga mga lamesa. Usa ka panid - usa ka lamesa. Aron makontrol, gitambagan ko ikaw nga mogamit usa ka shortcut sa keyboard Ctrl+katapusan, nga nagpalihok kanimo sa katapusang gigamit nga cell sa worksheet. Sa tinuud, kinahanglan nga kini ang katapusan nga cell sa lamesa sa datos. Kung sa imong pag-klik sa Ctrl+katapusan bisan unsang walay sulod nga cell sa tuo o sa ubos sa lamesa gipasiugda - kuhaa kining mga walay sulod nga mga kolum sa tuo o mga laray sa ubos sa lamesa pagkahuman sa lamesa ug i-save ang file.

Pamaagi 1: Paghimo og mga lamesa para sa pivot gamit ang Power Query

Sugod sa 2010 nga bersyon para sa Excel, adunay libre nga Power Query add-in nga makakolekta ug makapausab sa bisan unsang datos ug dayon ihatag kini isip tinubdan sa paghimo ug pivot table. Ang pagsulbad sa among problema sa tabang niining add-in dili gyud lisud.

Una, maghimo kita og bag-ong walay sulod nga file sa Excel - ang asembliya mahitabo niini ug dayon usa ka pivot table ang himoon niini.

Unya sa tab Data (kung aduna kay Excel 2016 o sa ulahi) o sa tab Kusog nga Pagpangutana (kung aduna kay Excel 2010-2013) pilia ang sugo Paghimo Pangutana - Gikan sa File - Excel (Pagkuha og Data - Gikan sa file - Excel) ug ipiho ang tinubdan nga file uban sa mga lamesa nga kolektahon:

Sa bintana nga makita, pilia ang bisan unsang sheet (dili igsapayan kung asa) ug ipadayon ang buton sa ubos Kausaban (I-edit):

Ang bintana sa Power Query Query Editor kinahanglan magbukas sa ibabaw sa Excel. Sa tuo nga bahin sa bintana sa panel Pangayo Parameter kuhaa ang tanan nga awtomatikong gibuhat nga mga lakang gawas sa una - tinubdan (Gigikanan):

Karon nakita namon ang usa ka kinatibuk-ang lista sa tanan nga mga sheet. Kung dugang sa mga sheet sa datos adunay uban pang mga side sheet sa file, nan sa kini nga lakang ang among tahas mao ang pagpili lamang sa mga sheet diin kinahanglan i-load ang kasayuran, wala’y labot ang tanan nga gigamit ang filter sa header sa lamesa:

I-delete ang tanang column gawas sa column Datapinaagi sa pag-right-click sa ulohan sa kolum ug pagpili Pagtangtang sa ubang mga kolum (Kuhaa ubang mga kolum):

Mahimo nimong palapdan ang mga sulud sa mga nakolekta nga mga lamesa pinaagi sa pag-klik sa doble nga arrow sa ibabaw sa kolum (checkbox Gamita ang orihinal nga ngalan sa kolum isip prefix mahimo nimo kini i-off):

Kung gibuhat nimo ang tanan nga husto, nan sa kini nga punto kinahanglan nimo nga makita ang sulud sa tanan nga mga lamesa nga nakolekta usa sa ubos sa lain:

Nagpabilin nga ipataas ang unang laray sa header sa lamesa gamit ang buton Gamita ang unang linya isip mga ulohan (Gamita ang unang laray isip mga ulohan) tab Home (Balay) ug kuhaa ang duplicate nga mga header sa lamesa gikan sa datos gamit ang filter:

I-save ang tanan nga nahimo gamit ang mando Pagsira ug pagkarga - Pagsira ug pagkarga sa… (Close & Load — Close & Load to…) tab Home (Balay), ug sa bintana nga moabli, pilia ang kapilian Koneksyon lang (Koneksyon Lamang):

Ang tanan. Nagpabilin lamang kini sa paghimo og usa ka summary. Aron mahimo kini, adto sa tab Isulod – PivotTable (Sal-ot — Pivot Table), pilia ang kapilian Gamita ang eksternal nga tinubdan sa datos (Gamita ang eksternal nga tinubdan sa datos)ug dayon pinaagi sa pag-klik sa buton Pagpili koneksyon, among hangyo. Ang dugang nga paghimo ug pag-configure sa pivot mahitabo sa usa ka hingpit nga sumbanan nga paagi pinaagi sa pag-drag sa mga natad nga kinahanglan namon sa mga laray, kolum ug kantidad nga lugar:

Kung ang gigikanan nga datos mausab sa umaabot o pipila pa nga mga sheet sa tindahan idugang, nan kini igo na aron ma-update ang pangutana ug ang among katingbanan gamit ang mando Refresh tanan tab Data (Data — I-refresh Tanan).

Pamaagi 2. Gihiusa namo ang mga lamesa sa command sa UNION SQL sa usa ka macro

Ang laing solusyon sa among problema girepresentahan niini nga macro, nga nagmugna og data set (cache) alang sa pivot table gamit ang command UNITY SQL pangutana nga pinulongan. Kini nga sugo naghiusa sa mga lamesa gikan sa tanan nga gipiho sa array SheetNames mga panid sa libro ngadto sa usa ka talaan sa datos. Sa ato pa, imbes nga pisikal nga pagkopya ug pag-paste sa mga han-ay gikan sa lain-laing mga sheet ngadto sa usa, atong buhaton ang sama sa RAM sa computer. Unya ang macro nagdugang usa ka bag-ong sheet nga adunay gihatag nga ngalan (variable ResultaSheetName) ug nagmugna ug bug-os nga (!) summary niini base sa nakolekta nga cache.

Aron makagamit ug macro, gamita ang Visual Basic nga buton sa tab developer (Developer) o keyboard shortcut alt+F11. Dayon magsal-ot kami og bag-ong walay sulod nga module pinaagi sa menu Isulod – Module ug kopyaha ang mosunod nga code didto:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'sheet name kung asa ang resulta nga pivot kay ipakita" = "Pivot of Sheet" = "Sheet. mga ngalan nga adunay gigikanan nga mga lamesa SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'naghimo kami og cache alang sa mga lamesa gikan sa mga sheet gikan sa SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Para sa i = LBound (SheetsNames) Ngadto sa UBound(SheetsNames) arSQL(i + 1) = "PILI * GIKAN SA [" & SheetsNames(i) & "$]" Sunod i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join $(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Tapuson Uban sa 'paghimo pag-usab sa sheet aron ipakita ang resulta nga pivot table Sa Error Ipadayon ang Sunod nga Aplikasyon.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Ngalan = ResultSheetName 'ipakita ang namugna nga summary sa cache niini nga panid Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Wala sa wsPivot objPivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCache. objPivotCache = Walay Sakup("A3"). Pilia ang Katapusan Uban sa Katapusan nga Sub    

Ang nahuman nga macro mahimo dayon nga pagdagan gamit ang usa ka shortcut sa keyboard alt+F8 o ang Macros button sa tab developer (Developer - Macros).

Mga disbentaha niini nga pamaagi:

• Ang datos wala ma-update tungod kay ang cache walay koneksyon sa tinubdan nga mga lamesa. Kung imong usbon ang tinubdan nga datos, kinahanglan nimong ipadagan pag-usab ang macro ug tukuron pag-usab ang summary.
• Kung gibag-o ang gidaghanon sa mga sheet, kinahanglan nga i-edit ang macro code (array SheetNames).
  

Apan sa katapusan nakakuha kami usa ka tinuud nga tibuuk nga pivot nga lamesa, nga gitukod sa daghang mga han-ay gikan sa lainlaing mga sheet:

Voilà!

Teknikal nga nota: Kung nakakuha ka usa ka sayup sama sa "Wala narehistro ang Provider" kung nagpadagan sa macro, nan lagmit adunay ka 64-bit nga bersyon sa Excel o usa ka dili kompleto nga bersyon sa Opisina ang na-install (walay Access). Aron ayuhon ang sitwasyon, ilisan ang tipik sa macro code:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

sa:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Ug i-download ug i-install ang libre nga makina sa pagproseso sa datos gikan sa Access gikan sa website sa Microsoft - Microsoft Access Database Engine 2010 Redistributable

Pamaagi 3: Paghiusa sa PivotTable Wizard gikan sa Daang Bersyon sa Excel

Kini nga pamaagi mao ang usa ka gamay nga outdated, apan sa gihapon angay sa paghisgot. Sa pormal nga pagkasulti, sa tanan nga mga bersyon hangtod ug lakip ang 2003, adunay kapilian sa PivotTable Wizard nga "maghimo usa ka pivot alang sa daghang mga sakup sa pagkonsolida". Bisan pa, ang usa ka taho nga gihimo sa ingon niini nga paagi, sa walay palad, mahimo lamang usa ka makalolooy nga dagway sa usa ka tinuud nga tibuuk nga katingbanan ug wala nagsuporta sa daghang mga "chips" sa naandan nga mga pivot table:

Sa ingon nga pivot, wala’y mga ulohan sa kolum sa lista sa uma, wala’y flexible nga setting sa istruktura, limitado ang set sa mga gimbuhaton nga gigamit, ug, sa kinatibuk-an, kining tanan dili kaayo parehas sa usa ka pivot table. Tingali mao kana kung ngano, sugod sa 2007, gitangtang sa Microsoft kini nga function gikan sa standard nga dialog sa paghimo og mga taho sa pivot table. Karon kini nga bahin magamit ra pinaagi sa usa ka naandan nga buton PivotTable Wizard(Pivot Table Wizard), nga, kon gusto, mahimong idugang sa Quick Access Toolbar pinaagi sa File - Mga Opsyon - Ipasibo ang Quick Access Toolbar - Tanan nga mga Sugo (File — Opsyon — Ipasibo ang Quick Access Toolbar — Tanan nga Sugo):

Pagkahuman sa pag-klik sa gidugang nga buton, kinahanglan nimo nga pilion ang angay nga kapilian sa una nga lakang sa wizard:

Ug unya sa sunod nga bintana, pilia ang matag range ug idugang kini sa kinatibuk-ang lista:

Apan, pag-usab, kini dili usa ka bug-os nga summary, busa ayaw pagdahom ug daghan gikan niini. Mahimo nako nga irekomendar kini nga kapilian sa yano kaayo nga mga kaso.

• Paghimo og mga Report gamit ang PivotTable
• I-set up ang mga kalkulasyon sa PivotTables
• Unsa ang mga macro, unsaon paggamit niini, asa kopyahon ang VBA code, ug uban pa.
• Pagkolekta sa datos gikan sa daghang mga sheet ngadto sa usa (PLEX add-on)