Pivot table sa maraming hanay ng data

Pagbubuo ng problema

Ang mga pivot table ay isa sa mga pinakakahanga-hangang tool sa Excel. Ngunit sa ngayon, sa kasamaang-palad, wala sa mga bersyon ng Excel ang makakagawa ng isang simple at kinakailangang bagay sa mabilisang paggawa ng isang buod para sa ilang mga paunang hanay ng data na matatagpuan, halimbawa, sa iba't ibang mga sheet o sa iba't ibang mga talahanayan:

Bago tayo magsimula, linawin natin ang ilang puntos. A priori, naniniwala ako na ang mga sumusunod na kundisyon ay natutugunan sa aming data:

• Ang mga talahanayan ay maaaring magkaroon ng anumang bilang ng mga hilera na may anumang data, ngunit dapat silang magkaroon ng parehong header.
• Dapat ay walang dagdag na data sa mga sheet na may mga source table. Isang sheet - isang table. Para makontrol, ipinapayo ko sa iyo na gumamit ng keyboard shortcut Ctrl+katapusan, na naglilipat sa iyo sa huling ginamit na cell sa worksheet. Sa isip, ito dapat ang huling cell sa talahanayan ng data. Kung kapag nag-click ka sa Ctrl+katapusan anumang walang laman na cell sa kanan o sa ibaba ng talahanayan ay naka-highlight - tanggalin ang mga walang laman na column sa kanan o mga hilera sa ibaba ng talahanayan pagkatapos ng talahanayan at i-save ang file.

Paraan 1: Bumuo ng mga talahanayan para sa isang pivot gamit ang Power Query

Simula sa 2010 na bersyon para sa Excel, mayroong isang libreng Power Query add-in na maaaring kolektahin at baguhin ang anumang data at pagkatapos ay ibigay ito bilang isang mapagkukunan para sa pagbuo ng isang pivot table. Ang paglutas ng aming problema sa tulong ng add-in na ito ay hindi mahirap sa lahat.

Una, gumawa tayo ng bagong walang laman na file sa Excel – magaganap dito ang pagpupulong at pagkatapos ay gagawa ng pivot table dito.

Pagkatapos ay sa tab data (kung mayroon kang Excel 2016 o mas bago) o sa tab kapangyarihan query (kung mayroon kang Excel 2010-2013) piliin ang command Lumikha ng Query – Mula sa File – Excel (Kumuha ng Data — Mula sa file — Excel) at tukuyin ang source file kasama ang mga talahanayan na kokolektahin:

Sa lalabas na window, pumili ng anumang sheet (hindi mahalaga kung alin) at pindutin ang button sa ibaba Baguhin (I-edit):

Dapat bumukas ang window ng Power Query Query Editor sa ibabaw ng Excel. Sa kanang bahagi ng window sa panel Humiling ng Mga Parameter tanggalin ang lahat ng awtomatikong ginawang hakbang maliban sa una - pinagmulan (Pinagmulan):

Ngayon ay nakikita namin ang isang pangkalahatang listahan ng lahat ng mga sheet. Kung bilang karagdagan sa mga sheet ng data ay may ilang iba pang mga side sheet sa file, kung gayon sa hakbang na ito ang aming gawain ay piliin lamang ang mga sheet kung saan kailangang i-load ang impormasyon, hindi kasama ang lahat ng iba pa gamit ang filter sa header ng talahanayan:

Tanggalin ang lahat ng column maliban sa column datasa pamamagitan ng pag-right click sa heading ng column at pagpili Tanggalin ang iba pang mga column (Alisin iba pang mga column):

Maaari mong palawakin ang mga nilalaman ng mga nakolektang talahanayan sa pamamagitan ng pag-click sa dobleng arrow sa tuktok ng hanay (checkbox Gamitin ang orihinal na pangalan ng column bilang prefix maaari mong i-off ito):

Kung ginawa mo nang tama ang lahat, sa puntong ito dapat mong makita ang mga nilalaman ng lahat ng mga talahanayan na nakolekta sa ibaba ng isa:

Nananatili itong itaas ang unang hilera sa header ng talahanayan gamit ang pindutan Gamitin ang unang linya bilang mga header (Gamitin ang unang hilera bilang mga header) tab Tahanan (Tahanan) at alisin ang mga duplicate na header ng talahanayan mula sa data gamit ang isang filter:

I-save ang lahat ng ginawa gamit ang utos Isara at i-load – Isara at i-load sa… (Isara at I-load — Isara at I-load sa…) tab Tahanan (Tahanan), at sa window na bubukas, piliin ang opsyon Koneksyon lang (Koneksyon Lang):

Lahat. Ito ay nananatiling lamang upang bumuo ng isang buod. Upang gawin ito, pumunta sa tab Ipasok – PivotTable (Ipasok — Pivot Table), piliin ang opsyon Gumamit ng external na data source (Gumamit ng external na data source)at pagkatapos ay sa pamamagitan ng pag-click sa pindutan Pumili ng koneksyon, ang aming kahilingan. Ang karagdagang paglikha at pagsasaayos ng pivot ay nangyayari sa isang ganap na karaniwang paraan sa pamamagitan ng pag-drag sa mga patlang na kailangan namin sa mga row, column at values ​​area:

Kung magbabago ang pinagmumulan ng data sa hinaharap o magdadagdag ng ilan pang mga sheet ng tindahan, magiging sapat na upang i-update ang query at ang aming buod gamit ang command I-refresh lahat tab data (Data — I-refresh Lahat).

Paraan 2. Pinagsasama namin ang mga talahanayan sa utos ng UNION SQL sa isang macro

Ang isa pang solusyon sa aming problema ay kinakatawan ng macro na ito, na lumilikha ng set ng data (cache) para sa pivot table gamit ang command UNITY Wika ng query sa SQL. Pinagsasama ng command na ito ang mga talahanayan mula sa lahat ng tinukoy sa array SheetNames mga sheet ng aklat sa isang talahanayan ng data. Ibig sabihin, sa halip na pisikal na kopyahin at i-paste ang mga hanay mula sa iba't ibang mga sheet hanggang sa isa, ginagawa namin ang parehong sa RAM ng computer. Pagkatapos ang macro ay nagdaragdag ng isang bagong sheet na may ibinigay na pangalan (variable ResultSheetName) at lumilikha ng ganap na (!) na buod dito batay sa nakolektang cache.

Upang gumamit ng macro, gamitin ang Visual Basic na button sa tab developer (Developer) o keyboard shortcut Alt+F11. Pagkatapos ay nagpasok kami ng isang bagong walang laman na module sa pamamagitan ng menu Ipasok – Module at kopyahin ang sumusunod na code doon:

Sub New_Multi_Table_Pivot() Dim i Bilang Long Dim arSQL() Bilang String Dim objPivotCache Bilang PivotCache Dim objRS Bilang Object Dim ResultSheetName Bilang String Dim SheetsNames Bilang Variant 'sheet name kung saan ang resultang arrayNa pivot ay ipapakita ' = "Pivot ng sheet mga pangalan na may source table na SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'bumubuo kami ng cache para sa mga table mula sa mga sheet mula sa SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Para sa i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Buksan ang Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With 're-create the sheet to display the resulting pivot table On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Pangalan = ResultSheetName 'ipakita ang nabuong buod ng cache sa sheet na ito Itakda ang objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Itakda ang objPivotCache.Recordset = objRS Itakda objRS = Wala Sa wsPivot objPivotCachet.TableRayPivotCachet.CreateDePivotCachet.CreateDePivotCache. objPivotCache = Nothing Range("A3"). Piliin ang End With End Sub    

Ang natapos na macro ay maaaring patakbuhin gamit ang isang keyboard shortcut Alt+F8 o ang Macros button sa tab developer (Developer — Macros).

Kahinaan ng diskarteng ito:

• Hindi na-update ang data dahil walang koneksyon ang cache sa mga source table. Kung babaguhin mo ang source data, dapat mong patakbuhin muli ang macro at buuin muli ang buod.
• Kapag binabago ang bilang ng mga sheet, kinakailangang i-edit ang macro code (array SheetNames).
  

Ngunit sa huli ay nakakakuha kami ng isang tunay na ganap na pivot table, na binuo sa ilang mga hanay mula sa iba't ibang mga sheet:

Voilà!

Teknikal na tala: kung nakakakuha ka ng error tulad ng "Hindi nakarehistro ang provider" kapag pinapatakbo ang macro, malamang na mayroon kang 64-bit na bersyon ng Excel o isang hindi kumpletong bersyon ng Office ang naka-install (walang Access). Upang ayusin ang sitwasyon, palitan ang fragment sa macro code:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

sa:

	Provider=Microsoft.ACE.OLEDB.12.0;  

At i-download at i-install ang libreng data processing engine mula sa Access mula sa website ng Microsoft – Microsoft Access Database Engine 2010 Redistributable

Paraan 3: Pagsamahin ang PivotTable Wizard mula sa Mga Lumang Bersyon ng Excel

Ang pamamaraang ito ay medyo hindi napapanahon, ngunit nagkakahalaga pa rin ng pagbanggit. Sa pormal na pagsasalita, sa lahat ng mga bersyon hanggang sa at kabilang ang 2003, mayroong isang opsyon sa PivotTable Wizard na "bumuo ng pivot para sa ilang mga hanay ng pagsasama-sama." Gayunpaman, ang isang ulat na ginawa sa ganitong paraan, sa kasamaang-palad, ay magiging isang kaawa-awang pagkakahawig lamang ng isang tunay na ganap na buod at hindi sumusuporta sa marami sa mga "chips" ng mga karaniwang pivot table:

Sa gayong pivot, walang mga heading ng column sa listahan ng field, walang flexible na setting ng istraktura, limitado ang set ng mga function na ginamit, at, sa pangkalahatan, ang lahat ng ito ay hindi masyadong katulad ng pivot table. Marahil iyon ang dahilan kung bakit, simula noong 2007, inalis ng Microsoft ang function na ito mula sa karaniwang dialog kapag lumilikha ng mga ulat ng pivot table. Ngayon ang tampok na ito ay magagamit lamang sa pamamagitan ng isang pasadyang pindutan PivotTable Wizard(Pivot Table Wizard), na, kung ninanais, ay maaaring idagdag sa Quick Access Toolbar sa pamamagitan ng File – Mga Opsyon – I-customize ang Quick Access Toolbar – Lahat ng Command (File — Mga Opsyon — I-customize ang Quick Access Toolbar — Lahat ng Command):

Pagkatapos mag-click sa idinagdag na pindutan, kailangan mong piliin ang naaangkop na opsyon sa unang hakbang ng wizard:

At pagkatapos ay sa susunod na window, piliin ang bawat saklaw at idagdag ito sa pangkalahatang listahan:

Ngunit, muli, hindi ito isang ganap na buod, kaya huwag umasa nang labis mula rito. Maaari kong irekomenda ang pagpipiliang ito sa napakasimpleng mga kaso.

• Paglikha ng Mga Ulat gamit ang PivotTables
• I-set up ang mga kalkulasyon sa PivotTables
• Ano ang mga macro, paano gamitin ang mga ito, kung saan kokopyahin ang VBA code, atbp.
• Pangongolekta ng data mula sa maraming sheet hanggang sa isa (PLEX add-on)