Tabel pivot în mai multe intervale de date

Formularea problemei

Tabelele pivot sunt unul dintre cele mai uimitoare instrumente din Excel. Dar până acum, din păcate, niciuna dintre versiunile de Excel nu poate face un lucru atât de simplu și necesar din mers precum construirea unui rezumat pentru mai multe intervale de date inițiale situate, de exemplu, pe foi diferite sau în tabele diferite:

Înainte de a începe, să clarificăm câteva puncte. A priori, consider că în datele noastre sunt îndeplinite următoarele condiții:

• Tabelele pot avea orice număr de rânduri cu orice date, dar trebuie să aibă același antet.
• Nu ar trebui să existe date suplimentare pe foile cu tabele sursă. O foaie – o masă. Pentru a controla, vă sfătuiesc să utilizați o comandă rapidă de la tastatură Ctrl+Sfârşit, care vă mută la ultima celulă utilizată din foaia de lucru. În mod ideal, aceasta ar trebui să fie ultima celulă din tabelul de date. Dacă atunci când dai clic pe Ctrl+Sfârşit orice celulă goală din dreapta sau sub tabel este evidențiată – ștergeți aceste coloane goale din dreapta sau rândurile de sub tabel după tabel și salvați fișierul.

Metoda 1: Creați tabele pentru un pivot folosind Power Query

Începând cu versiunea 2010 pentru Excel, există un program de completare Power Query gratuit care poate colecta și transforma orice date și apoi le poate oferi ca sursă pentru construirea unui tabel pivot. Rezolvarea problemei noastre cu ajutorul acestui program de completare nu este deloc dificilă.

Mai întâi, să creăm un nou fișier gol în Excel – asamblarea va avea loc în el și apoi va fi creat un tabel pivot în el.

Apoi pe filă Date (dacă aveți Excel 2016 sau o versiune ulterioară) sau pe filă Interogare de alimentare (dacă aveți Excel 2010-2013) selectați comanda Creare interogare – Din fișier – Excel (Obține date — Din fișier — Excel) și specificați fișierul sursă cu tabelele care trebuie colectate:

În fereastra care apare, selectați orice foaie (nu contează care) și apăsați butonul de mai jos Schimba (Editați | ×):

Fereastra Power Query Query Editor ar trebui să se deschidă deasupra Excel. În partea dreaptă a ferestrei de pe panou Parametri de solicitare ștergeți toți pașii creați automat, cu excepția primului - Sursă (Sursă):

Acum vedem o listă generală a tuturor foilor. Dacă pe lângă fișele de date există și alte foi laterale în fișier, atunci la acest pas sarcina noastră este să selectăm numai acele foi din care trebuie încărcate informații, excluzând toate celelalte folosind filtrul din antetul tabelului:

Ștergeți toate coloanele, cu excepția coloanei Datefăcând clic dreapta pe un titlu de coloană și selectând Ștergeți alte coloane (Elimina alte coloane):

Puteți extinde apoi conținutul tabelelor colectate făcând clic pe săgeata dublă din partea de sus a coloanei (caseta de selectare Folosiți numele coloanei originale ca prefix il poti dezactiva):

Dacă ați făcut totul corect, atunci în acest moment ar trebui să vedeți conținutul tuturor tabelelor colectate unul sub celălalt:

Rămâne să ridicați primul rând la antetul tabelului cu butonul Utilizați prima linie ca antete (Folosiți primul rând ca antete) fila Acasă (Acasă) și eliminați anteturile de tabel duplicate din date folosind un filtru:

Salvați tot ce s-a făcut cu comanda Închideți și încărcați - Închideți și încărcați în... (Închidere și încărcare — Închidere și încărcare la...) fila Acasă (Acasă), iar în fereastra care se deschide, selectați opțiunea Numai conexiune (Numai conexiune):

Tot. Rămâne doar să construim un rezumat. Pentru a face acest lucru, accesați fila Insert – PivotTable (Inserați — Tabel Pivot), alegeți opțiunea Utilizați sursa externă de date (Utilizați sursa externă de date)și apoi făcând clic pe butonul Selectați conexiunea, cererea noastră. Crearea și configurarea ulterioară a pivotului are loc într-un mod complet standard, trăgând câmpurile de care avem nevoie în zona de rânduri, coloane și valori:

Dacă datele sursă se modifică în viitor sau se mai adaugă câteva foi de magazin, atunci va fi suficient să actualizați interogarea și rezumatul nostru folosind comanda Reîmprospătați totul fila Date (Date — Actualizează tot).

Metoda 2. Unim tabelele cu comanda UNION SQL într-o macrocomandă

O altă soluție la problema noastră este reprezentată de această macrocomandă, care creează un set de date (cache) pentru tabelul pivot folosind comanda UNITATE Limbajul de interogare SQL. Această comandă combină tabele din toate cele specificate în matrice SheetNames foile cărții într-un singur tabel de date. Adică, în loc să copiem și să lipim fizic intervale de la diferite foi pe una, facem același lucru în memoria RAM a computerului. Apoi macrocomanda adaugă o nouă foaie cu numele dat (variabilă ResultSheetName) și creează un rezumat complet (!) pe baza acestuia pe baza memoriei cache colectate.

Pentru a utiliza o macrocomandă, utilizați butonul Visual Basic din filă dezvoltator (Dezvoltator) sau scurtătură de la tastatură Alt+F11. Apoi introducem un nou modul gol prin meniu Inserare – Modul și copiați următorul cod acolo:

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 'Numele foii unde va fi afișat pivotul rezultat ResultSheetName = "Matrice Pivot al foii" nume cu tabele sursă SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'formăm un cache pentru tabelele din foile din SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Pentru i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Deschide Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Încheiați cu „recreați foaia pentru a afișa tabelul pivot rezultat În eroare Reluați următoarea aplicație.DisplayAlerts = Foi de lucru false(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'afișează rezumatul cache-ului generat pe această foaie Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nimic cu wsPivot objPivotCache.=CreatePivotCache.=CreatePivotRachet. objPivotCache = Nothing Range ("A3"). Selectați End With End Sub    

Macrocomandă finalizată poate fi apoi rulată cu o comandă rapidă de la tastatură Alt+F8 sau butonul Macro-uri din filă dezvoltator (Dezvoltator – Macrocomenzi).

Contra acestei abordări:

• Datele nu sunt actualizate deoarece memoria cache nu are nicio conexiune cu tabelele sursă. Dacă modificați datele sursă, trebuie să rulați din nou macrocomanda și să creați din nou rezumatul.
• La modificarea numărului de foi, este necesar să editați codul macro (matrice SheetNames).
  

Dar, în cele din urmă, obținem un adevărat tabel pivot cu drepturi depline, construit pe mai multe game din foi diferite:

Voilà!

Notă tehnică: dacă primiți o eroare precum „Furnizorul nu este înregistrat” atunci când rulați macrocomandă, atunci cel mai probabil aveți o versiune de Excel pe 64 de biți sau este instalată o versiune incompletă de Office (fără acces). Pentru a remedia situația, înlocuiți fragmentul din codul macro:

	 Furnizor=Microsoft.Jet.OLEDB.4.0;  

la:

	Furnizor=Microsoft.ACE.OLEDB.12.0;  

Și descărcați și instalați motorul gratuit de procesare a datelor din Access de pe site-ul Microsoft – Microsoft Access Database Engine 2010 Redistributable

Metoda 3: Consolidați expertul PivotTable din versiunile vechi de Excel

Această metodă este puțin depășită, dar merită menționată. Din punct de vedere formal, în toate versiunile până în 2003 inclusiv, a existat o opțiune în Expertul PivotTable pentru a „construi un pivot pentru mai multe intervale de consolidare”. Cu toate acestea, un raport construit în acest fel, din păcate, va fi doar o aparență jalnică a unui rezumat cu drepturi depline și nu acceptă multe dintre „jetoanele” tabelelor pivot convenționale:

Într-un astfel de pivot, nu există titluri de coloană în lista de câmpuri, nu există o setare flexibilă a structurii, setul de funcții utilizat este limitat și, în general, toate acestea nu sunt foarte asemănătoare cu un tabel pivot. Poate de aceea, începând cu 2007, Microsoft a eliminat această funcție din dialogul standard la crearea rapoartelor din tabel pivot. Acum această caracteristică este disponibilă doar printr-un buton personalizat Expertul PivotTable(Asistent pentru tabel pivot), care, dacă se dorește, poate fi adăugat la Bara de instrumente Acces rapid prin Fișier – Opțiuni – Personalizare bară de instrumente Acces rapid – Toate comenzile (Fișier — Opțiuni — Personalizare bară de instrumente Acces rapid — Toate comenzile):

După ce faceți clic pe butonul adăugat, trebuie să selectați opțiunea corespunzătoare la primul pas al expertului:

Și apoi, în fereastra următoare, selectați fiecare interval pe rând și adăugați-l la lista generală:

Dar, din nou, acesta nu este un rezumat cu drepturi depline, așa că nu vă așteptați prea mult de la el. Pot recomanda aceasta optiune doar in cazuri foarte simple.

• Crearea de rapoarte cu PivotTables
• Configurați calcule în PivotTables
• Ce sunt macrocomenzile, cum să le folosești, unde să copiați codul VBA etc.
• Colectarea datelor de la mai multe foi la una (supliment PLEX)