Pivot table fuq diversi firxiet tad-dejta

Formulazzjoni tal-problema

Pivot tables huma waħda mill-aktar għodod tal-għaġeb f'Excel. Iżda s'issa, sfortunatament, l-ebda waħda mill-verżjonijiet ta 'Excel ma tista' tagħmel ħaġa daqshekk sempliċi u meħtieġa fuq il-fly bħall-bini ta 'sommarju għal diversi firxiet ta' data inizjali li jinsabu, pereżempju, fuq folji differenti jew f'tabelli differenti:

Qabel ma nibdew, ejja niċċaraw ftit punti. A priori, nemmen li l-kundizzjonijiet li ġejjin huma sodisfatti fid-dejta tagħna:

• It-tabelli jista' jkollhom kwalunkwe numru ta' ringieli bi kwalunkwe data, iżda għandu jkollhom l-istess header.
• M'għandux ikun hemm dejta żejda fuq il-folji bit-tabelli tas-sors. Folja waħda - tabella waħda. Biex tikkontrolla, nagħtik parir biex tuża shortcut tat-tastiera Ctrl+tmiem, li jmexxik għall-aħħar ċellula użata fil-worksheet. Idealment, din għandha tkun l-aħħar ċellula fit-tabella tad-dejta. Jekk meta tikklikkja fuq Ctrl+tmiem kwalunkwe ċellula vojta fuq il-lemin jew taħt it-tabella hija enfasizzata - ħassar dawn il-kolonni vojta fuq il-lemin jew ringieli taħt it-tabella wara t-tabella u ssalva l-fajl.

Metodu 1: Ibni tabelli għal pern billi tuża Power Query

Tibda mill-verżjoni tal-2010 għal Excel, hemm add-in b'xejn ta' Power Query li jista' jiġbor u jittrasforma kwalunkwe dejta u mbagħad jagħtiha bħala sors għall-bini ta' tabella pivot. Issolvi l-problema tagħna bl-għajnuna ta 'dan il-add-in mhu diffiċli xejn.

L-ewwel, ejja noħolqu fajl vojt ġdid f'Excel - l-assemblaġġ se jsir fih u mbagħad tinħoloq tabella tal-pern fiha.

Imbagħad fuq it-tab Data (jekk għandek Excel 2016 jew aktar tard) jew fuq it-tab Mistoqsija dwar l-Enerġija (jekk għandek Excel 2010-2013) agħżel il-kmand Oħloq Mistoqsija - Mill-Fajl - Excel (Ikseb Data — Mill-fajl — Excel) u speċifika l-fajl tas-sors bit-tabelli li jridu jinġabru:

Fit-tieqa li tidher, agħżel kwalunkwe folja (ma jimpurtax liema waħda) u agħfas il-buttuna hawn taħt Bidla (Edit):

It-tieqa tal-Editur tal-Mistoqsija tal-Power Query għandha tiftaħ fuq Excel. Fuq in-naħa tal-lemin tat-tieqa fuq il-pannell Parametri tat-Talba ħassar il-passi kollha maħluqa awtomatikament ħlief l-ewwel - sors (Sors):

Issa naraw lista ġenerali tal-folji kollha. Jekk minbarra l-folji tad-dejta hemm xi folji oħra tal-ġenb fil-fajl, allura f'dan il-pass ix-xogħol tagħna huwa li nagħżlu biss dawk il-folji li minnhom trid titgħabba l-informazzjoni, esklużi l-oħrajn kollha bl-użu tal-filtru fl-intestatura tat-tabella:

Ħassar il-kolonni kollha ħlief il-kolonna Databilli tikklikkja bil-lemin fuq intestatura tal-kolonna u tagħżel Ħassar kolonni oħra (Neħħi kolonni oħra):

Tista’ mbagħad tespandi l-kontenut tat-tabelli miġbura billi tikklikkja fuq il-vleġġa doppja fin-naħa ta’ fuq tal-kolonna (checkbox Uża l-isem tal-kolonna oriġinali bħala prefiss tista itfih):

Jekk għamilt kollox b'mod korrett, allura f'dan il-punt għandek tara l-kontenut tat-tabelli kollha miġbura waħda taħt l-oħra:

Jibqa 'tgħolli l-ewwel ringiela għall-intestatura tal-mejda bil-buttuna Uża l-ewwel linja bħala headers (Uża l-ewwel ringiela bħala headers) tab home (Dar) u neħħi l-intestaturi tat-tabella duplikati mid-dejta billi tuża filtru:

Issejvja dak kollu li sar bil-kmand Agħlaq u tagħbija – Agħlaq u tagħbija fi... (Agħlaq u Tagħbija — Agħlaq u Tagħbija sa...) tab home (Dar), u fit-tieqa li tiftaħ, agħżel l-għażla Konnessjoni biss (Konnessjoni Biss):

Kollox. Fadal biss biex jinbena sommarju. Biex tagħmel dan, mur fit-tab Daħħal – PivotTable (Daħħal — Pivot Table), agħżel l-għażla Uża sors ta 'dejta estern (Uża sors ta' dejta estern)u mbagħad billi tikklikkja l-buttuna Agħżel il-konnessjoni, it-talba tagħna. Aktar ħolqien u konfigurazzjoni tal-pern iseħħ b'mod kompletament standard billi tkaxkru l-oqsma li neħtieġu fiż-żona ta 'ringieli, kolonni u valuri:

Jekk id-dejta tas-sors tinbidel fil-futur jew jiġu miżjuda ftit aktar folji tal-maħżen, allura jkun biżżejjed li taġġorna l-mistoqsija u s-sommarju tagħna billi tuża l-kmand Aġġorna kollox tab Data (Data — Aġġorna Kollha).

Metodu 2. Ngħaqqdu t-tabelli mal-kmand UNION SQL f'makro

Soluzzjoni oħra għall-problema tagħna hija rappreżentata minn din il-makro, li toħloq sett tad-dejta (cache) għat-tabella tal-pern bl-użu tal-kmand UNITÀ Lingwa tal-mistoqsijiet SQL. Dan il-kmand jgħaqqad tabelli minn dak kollu speċifikat fil-firxa SheetNames folji tal-ktieb f'tabella tad-dejta waħda. Jiġifieri, minflok ma nikkuppjaw fiżikament u nwaħħal firxiet minn folji differenti għal waħda, nagħmlu l-istess fir-RAM tal-kompjuter. Imbagħad il-makro żżid folja ġdida bl-isem mogħti (varjabbli ResultSheetName) u joħloq sommarju sħiħ (!) fuqu bbażat fuq il-cache miġbura.

Biex tuża makro, uża l-buttuna Visual Basic fuq it-tab iżviluppatur (Żviluppatur) jew shortcut tat-tastiera Alt+F11. Imbagħad aħna daħħal modulu vojt ġdid permezz tal-menu Daħħal – Modulu u kkopja l-kodiċi li ġej hemmhekk:

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 'isem tal-folja fejn il-pern li jirriżulta se jintwera ResultSheetName = "Arranġament ta' ResultSheetName = "Pivot" ismijiet bit-tabelli tas-sors SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'aħna niffurmaw cache għal tabelli minn folji minn SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Għal i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Li jmiss 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 ) Tmiem Bil 'Oħloq mill-ġdid il-folja biex turi t-tabella tal-pern li tirriżulta Fuq Żball Resume Next Application.DisplayAlerts = Foloz Worksheets(ResultSheetName). Ħassar Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'wiri s-sommarju tal-cache ġġenerat fuq din il-folja Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Issettja objPivotCache.Recordset = objRS Issettja objRS = Xejn Bil wsPivot objPivotCache.=CreatePivotCache.=CreatePivot. objPivotCache = Xejn Range ("A3"). Agħżel End With End Sub    

Il-makro lest jista 'mbagħad jitmexxa b'shortcut tat-tastiera Alt+F8 jew il-buttuna Macros fuq it-tab iżviluppatur (Żviluppatur — Makros).

Żvantaġġi ta' dan l-approċċ:

• Id-dejta mhix aġġornata minħabba li l-cache m'għandha l-ebda konnessjoni mat-tabelli tas-sors. Jekk tibdel id-dejta tas-sors, trid terġa' tħaddem il-makro u terġa' tibni s-sommarju.
• Meta tbiddel in-numru ta 'folji, huwa meħtieġ li teditja l-kodiċi makro (array SheetNames).
  

Iżda fl-aħħar ikollna tabella tal-pern reali sħiħa, mibnija fuq diversi firxiet minn folji differenti:

Voilà!

Nota Teknika: jekk ikollok żball bħal "Fornitur mhux irreġistrat" ​​meta tħaddem il-makro, allura x'aktarx ikollok verżjoni 64-bit ta 'Excel jew verżjoni mhux kompluta ta' Office hija installata (l-ebda Aċċess). Biex tirranġa s-sitwazzjoni, ibdel il-framment fil-kodiċi makro:

	 Fornitur=Microsoft.Jet.OLEDB.4.0;  

għal:

	Fornitur=Microsoft.ACE.OLEDB.12.0;  

U niżżel u installa l-magna tal-ipproċessar tad-dejta b’xejn minn Access mill-websajt tal-Microsoft – Microsoft Access Database Engine 2010 Redistributable

Metodu 3: Ikkonsolida PivotTable Wizard minn Verżjonijiet Qodma ta 'Excel

Dan il-metodu huwa ftit skadut, iżda xorta ta 'min isemmi. Formalment, fil-verżjonijiet kollha sa u inkluż l-2003, kien hemm għażla fil-PivotTable Wizard biex "jibni pern għal diversi firxiet ta 'konsolidazzjoni". Madankollu, rapport mibni b'dan il-mod, sfortunatament, se jkun biss dehra ħasra ta' sommarju reali reali u ma jappoġġjax ħafna miċ-"ċipep" ta 'tabelli pivot konvenzjonali:

F'tali pern, m'hemm l-ebda intestaturi tal-kolonni fil-lista tal-qasam, m'hemm l-ebda iffissar ta 'struttura flessibbli, is-sett ta' funzjonijiet użat huwa limitat, u, b'mod ġenerali, dan kollu mhuwiex simili ħafna għal tabella tal-pern. Forsi hu għalhekk li, mill-2007, Microsoft neħħiet din il-funzjoni mid-djalogu standard meta ħoloq rapporti tal-pern table. Issa din il-karatteristika hija disponibbli biss permezz ta 'buttuna personalizzata PivotTable Wizard(Wizard tal-Pivot Table), li, jekk mixtieq, jistgħu jiġu miżjuda mal-Barra ta' Aċċess Quick permezz ta' Fajl – Għażliet – Ippersonalizza l-Barra tal-Aċċess Rapidu – Il-Kmandi kollha (Fajl — Għażliet — Ippersonalizza l-Barra tal-Aċċess Rapidu — Il-Kmandi kollha):

Wara li tikklikkja fuq il-buttuna miżjuda, trid tagħżel l-għażla xierqa fl-ewwel pass tal-wizard:

U mbagħad fit-tieqa li jmiss, agħżel kull firxa mbagħad u żidha mal-lista ġenerali:

Iżda, għal darb'oħra, dan mhux sommarju sħiħ, għalhekk tistennix wisq minnu. Nista 'nirrakkomanda din l-għażla biss f'każijiet sempliċi ħafna.

• Ħolqien ta 'Rapporti b'PivotTables
• Stabbilixxi kalkoli f'PivotTables
• X'inhuma macros, kif tużahom, fejn tikkopja kodiċi VBA, eċċ.
• Ġbir tad-dejta minn folji multipli għal wieħed (add-on PLEX)