Taula dinamikoa hainbat datu-barrutitan

Arazoaren formulazioa

Taula dinamikoak Excel-en tresna harrigarrienetako bat dira. Baina orain arte, zoritxarrez, Excel-en bertsio batek ezin du hain gauza sinple eta beharrezkoa egin berehalakoan, adibidez, orri ezberdinetan edo taula ezberdinetan kokatutako hasierako datu-barruti batzuen laburpen bat eraikitzea:

Hasi baino lehen, argi ditzagun puntu pare bat. A priori, uste dut baldintza hauek betetzen direla gure datuetan:

• Taulek edozein errenkada izan ditzakete edozein daturekin, baina goiburu bera izan behar dute.
• Iturburu-taulen fitxetan ez da datu gehigarririk egon behar. Orrialde bat - mahai bat. Kontrolatzeko, teklatuko lasterbide bat erabiltzea gomendatzen dizut Ctrl+amaiera, lan-orriko azken erabilitako gelaxkara eramaten zaituena. Egokiena, datu-taularen azken gelaxka izan beharko litzateke. Sakatzen duzunean bada Ctrl+amaiera taularen eskuinaldean edo azpian dagoen edozein gelaxka huts nabarmentzen da: ezabatu zutabe huts hauek eskuineko edo taularen azpiko errenkadak taularen ondoren eta gorde fitxategia.

1. metodoa: Power Query erabiliz pibote baterako taulak eraiki

Excel-erako 2010eko bertsiotik hasita, doako Power Query gehigarri bat dago, edozein datu bildu eta eraldatu eta gero taula dinamiko bat eraikitzeko iturri gisa eman dezakeena. Gehigarri honen laguntzaz gure arazoa konpontzea ez da batere zaila.

Lehenik eta behin, sor dezagun fitxategi huts berri bat Excel-en - muntaia egingo da bertan eta gero taula dinamiko bat sortuko da bertan.

Ondoren, fitxan Data (Excel 2016 edo berriagoa baduzu) edo fitxan Power kontsulta (Excel 2010-2013 baduzu) hautatu komandoa Sortu kontsulta - Fitxategitik - Excel (Lortu datuak — Fitxategitik — Excel) eta zehaztu iturburu-fitxategia bildu beharreko taulekin:

Agertzen den leihoan, hautatu edozein orri (berdin du zein den) eta sakatu beheko botoia Aldatu (Editatu):

Power Query Query Editor leihoa Excel-en gainean ireki behar da. Paneleko leihoaren eskuineko aldean Eskatu parametroak ezabatu automatikoki sortutako urrats guztiak lehenengoa izan ezik - Iturria (Iturria):

Orain orri guztien zerrenda orokor bat ikusiko dugu. Fitxategian datu-orriez gain beste albo-orri batzuk badaude, pauso honetan informazioa kargatu behar duten orriak bakarrik hautatzea da, taulako goiburuko iragazkia erabiliz beste guztiak kenduta:

Ezabatu zutabe guztiak zutabea izan ezik Dataeskuineko botoiarekin zutabe-izenburu batean klik eginez eta hautatuz Ezabatu beste zutabe batzuk (Kendu beste zutabe batzuk):

Ondoren, bildutako taulen edukia zabal dezakezu zutabearen goiko aldean dagoen gezi bikoitzean klik eginez (kontrol-laukia Erabili jatorrizko zutabearen izena aurrizki gisa desaktibatu dezakezu):

Dena ondo egin baduzu, une honetan bata bestearen azpian bildutako taula guztien edukia ikusi beharko zenuke:

Lehenengo errenkada mahaiaren goiburura igotzea geratzen da botoiarekin Erabili lehen lerroa goiburu gisa (Erabili lehen errenkada goiburu gisa) fitxa Hasiera (Hasiera) eta kendu bikoiztutako taula goiburuak datuetatik iragazki bat erabiliz:

Gorde komandoarekin egindako guztia Itxi eta kargatu – Itxi eta kargatu… (Itxi eta kargatu — Itxi eta kargatu...) fitxa Hasiera (Hasiera), eta irekitzen den leihoan, hautatu aukera Konexioa soilik (Konexioa soilik):

Dena. Laburpen bat eraikitzea baino ez da geratzen. Horretarako, joan fitxara Txertatu - Taula dinamikoa (Txertatu — taula dinamikoa), aukeratu aukera Erabili kanpoko datu-iturria (Erabili kanpoko datu-iturburua)eta gero botoian klik eginez Hautatu konexioa, gure eskaera. Pibotaren sorkuntza eta konfigurazio gehiago modu guztiz estandarrean gertatzen dira behar ditugun eremuak errenkada, zutabe eta balioen eremura arrastatuz:

Etorkizunean iturri-datuak aldatzen badira edo denda-orri batzuk gehitzen badira, nahikoa izango da komandoa erabiliz kontsulta eta gure laburpena eguneratzea. Freskatu guztiak fitxa Data (Datuak — Freskatu guztiak).

2. metodoa. UNION SQL komandoarekin taulak makro batean batzen ditugu

Gure arazoaren beste irtenbide bat makro honek adierazten du, komandoa erabiliz taula dinamikorako datu multzo bat (cachea) sortzen duena. UNITY SQL kontsulta-lengoaia. Komando honek matrizean zehaztutako guztien taulak konbinatzen ditu SheetNames liburuko fitxak datu-taula bakar batean. Hau da, orri ezberdinetako barrutiak fisikoki kopiatu eta itsatsi beharrean, gauza bera egiten dugu ordenagailuaren RAMean. Ondoren makroak orri berri bat gehitzen du emandako izenarekin (aldagaia ResultSheetName) eta bildutako cachean oinarritutako laburpen osoa (!) sortzen du.

Makro bat erabiltzeko, erabili fitxako Visual Basic botoia developer (Garatzailea) edo teklatuko lasterbidea Alt+F11. Ondoren, modulu huts berri bat txertatuko dugu menuaren bidez Txertatu – Modulua eta kopiatu bertan kodea:

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 'orriaren izena non emaitza pibota bistaratuko den ResultSheetName = "Pivot-aren matrizea" iturburu-taulekin izenak SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") ' SheetsNames-eko orrietatik tauletarako cache bat osatzen dugu ActiveWorkbook With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Hurrengoa i Set objRS = CreateObject("ADODB.Recordset") objRS .Ireki Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Amaitu 'orria birsortu, ondoriozko taula dinamikoa bistaratzeko Errorea On Berekin hurrengo aplikazioa.DisplayAlerts = Lan-orri faltsuak (ResultSheetName). Ezabatu multzoa wsPivot = Lan-orriak. Gehitu wsPivo t. Name = ResultSheetName 'erakutsi sortutako cache-ren laburpena orri honetan Ezarri objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Ezarri objPivotCache.Recordset = objRS Ezarri objRS = Ez dago ezer wsPivot-ekin objPivotCache.TablewPivotCache.TablewPivotCache.=CreatePivot. objPivotCache = Nothing Range("A3"). Hautatu End With End Sub    

Amaitutako makroa teklatuko lasterbide batekin exekutatu daiteke Alt+F8 edo fitxako Makroak botoia developer (Garatzailea — Makroak).

Ikuspegi honen alde txarrak:

• Datuak ez dira eguneratzen, cacheak ez duelako konexiorik iturburu-taulekin. Iturburuko datuak aldatzen badituzu, makroa berriro exekutatu eta laburpena berriro eraiki behar duzu.
• Orri kopurua aldatzean, makro kodea editatu behar da (array SheetNames).
  

Baina, azkenean, benetako mahai pibotagarria lortuko dugu, orri ezberdinetako hainbat gamatan eraikia:

Voila!

Ohar teknikoa: Makroa exekutatzen ari zarenean "Hornitzailea erregistratuta ez" bezalako errore bat jasotzen baduzu, ziurrenik Excel-en 64 biteko bertsioa izango duzu edo Office-ren bertsio osatugabea instalatuta dago (ez dago sarbidea). Egoera konpontzeko, ordezkatu zatia makro-kodean:

	 Hornitzailea=Microsoft.Jet.OLEDB.4.0;  

to:

	Hornitzailea=Microsoft.ACE.OLEDB.12.0;  

Eta deskargatu eta instalatu doako datuak prozesatzeko motorra Access-etik Microsoft webgunetik - Microsoft Access Database Engine 2010 Redistributable

3. metodoa: Excel-en bertsio zaharretatik PivotTable morroia finkatu

Metodo hau apur bat zaharkituta dago, baina aipatzea merezi du. Formalki hitz eginez, 2003ra arteko bertsio guztietan, Taula Pibota Morroian aukera bat zegoen "hainbat kontsolidazio barrutietarako pibota eraikitzeko". Hala ere, era honetan eraikitako txosten bat, zoritxarrez, benetako laburpen oso baten itxura penagarria baino ez da izango eta ez ditu ohiko taula dinamikoen "txip" asko onartzen:

Pibote horretan, eremu-zerrendan ez dago zutabe-izenbururik, ez dago egitura-ezarpen malgurik, erabilitako funtzio-multzoa mugatua da eta, oro har, hori guztia ez da taula dinamiko baten oso antzekoa. Agian horregatik, 2007an hasita, Microsoft-ek funtzio hau kendu zuen elkarrizketa-koadro estandarretik taula dinamikoen txostenak sortzean. Orain funtzio hau botoi pertsonalizatu baten bidez bakarrik dago erabilgarri Taula dinamikoen morroia(Taula dinamikoen morroia), nahi izanez gero, Sarbide Azkarreko Tresna-barrara gehi daitekeen bidez Fitxategia – Aukerak – Pertsonalizatu Sarbide bizkorra tresna-barra – Komando guztiak (Fitxategia — Aukerak — Pertsonalizatu Sarbide bizkorra tresna-barra — Komando guztiak):

Gehitutako botoian klik egin ondoren, aukera egokia hautatu behar duzu morroiaren lehen urratsean:

Ondoren, hurrengo leihoan, hautatu barruti bakoitza txandaka eta gehitu zerrenda orokorrera:

Baina, berriro ere, hau ez da erabateko laburpena, beraz, ez espero handirik. Aukera hau oso kasu sinpleetan bakarrik gomenda dezaket.

• Txostenak sortzea taula dinamikoekin
• Konfiguratu kalkuluak taula dinamikoetan
• Zer dira makroak, nola erabili, non kopiatu VBA kodea, etab.
• Datu-bilketa hainbat orri batetik bestera (PLEX gehigarria)