Creați un tabel pivot utilizând date din foi diferite dintr-un registru de lucru sau din registre de lucru diferite, dacă acele tabele au structuri de coloane identice.
Creați un tabel pivot din mai multe foi
Pentru a vedea cum puteți crea un tabel pivot din date pe diferite foi, urmăriți acest scurt videoclip. Instrucțiunile scrise sunt mai jos.
Notă: pentru a vedea transcrierea videoclipului, accesați pagina Video cu foi multiple.
Introducere în game multiple de consolidare
Pentru a crea un tabel pivot, puteți utiliza date din foi diferite dintr-un registru de lucru sau din registre de lucru diferite, dacă acele tabele au structuri de coloane identice.
Cu toate acestea, nu veți obține același aspect al tabelului pivot pe care l-ați obține dintr-un singur interval, așa cum puteți vedea în captura de ecran de mai jos.
Dacă este posibil, mutați datele într-o singură foaie de lucru sau stocați-le într-o bază de date, cum ar fi Microsoft Access, și veți avea mai multă flexibilitate în crearea tabelului pivot.
Dacă combinarea datelor dvs. nu este o opțiune, acest tutorial pentru tabelul pivot explică pașii pentru a crea un tabel pivot din mai multe intervale de consolidare, descrie limitările și sugerează soluții de soluție.
Tabel pivot din mai multe game de consolidare
- Pentru a deschide PivotTable și PivotChart Wizard, selectați orice celulă dintr-o foaie de lucru, apoi apăsați Alt + D, apoi apăsați P. Această comandă rapidă este utilizată deoarece, în versiunile mai vechi de Excel, expertul a fost listat în Data meniu, ca PComanda ivotTable și PivotChart Report.
- Faceți clic pe Mai multe intervale de consolidare, apoi faceți clic pe Următorul
- În loc să selectați un interval pe foaia de lucru, puteți utiliza un interval numit, cum ar fi EastData.
- Dacă datele sursă se află într-un tabel numit Excel, puteți consulta tabelul respectiv, folosind numele și [#Toate]. De exemplu: Tabelul 2 [#Toate]
Un tabel pivot apare pe foaia de lucru, cu primul câmp din zona Rând și toate celelalte câmpuri din datele sursă din zona Valori, afișând un număr.
Curățați tabelul pivot de consolidare multiplă
Toate câmpurile din datele sursă sunt incluse în tabelul pivot de consolidare multiplă, astfel încât să puteți elimina unele dintre ele și să faceți alte câteva modificări.
Eliminați câmpurile care nu conțin date semnificative
În acest exemplu, câmpurile Culoare, Data, Preț și Rep conțin text sau numere care nu au sens în acest raport, deci vor fi eliminate.
- Faceți clic pe săgeata verticală din antetul Etichetelor coloanei
- Eliminați bifele pentru câmpurile pe care doriți să le eliminați.
- Faceți clic pe OK
Modificați calculul câmpului valorii
În mod implicit, valorile vor fi afișate ca număr și puteți schimba acest lucru în sumă sau în alt calcul.
NOTĂ: Acest lucru va afecta toate valorile - acestea nu pot fi modificate separat.
- Faceți clic dreapta pe una dintre valori
- Indicați pentru a rezuma valorile după și faceți clic pe Sum.
Eliminați Totalul total pentru rânduri
Marele total pentru rânduri nu are sens în acest raport, deoarece afișează totalul pentru articolele fără legătură, deci ar trebui eliminat.
- Faceți clic dreapta pe titlul pentru Totalul total pentru rânduri
- Faceți clic pe Remove Total Total.
Schimbați etichetele
În tabelele pivot, sunt create câmpuri generice - Rând, Coloană, Valoare și Pagină1. Puteți redenumi aceste câmpuri, pentru a ușura înțelegerea tabelului pivot.
- Faceți clic pe orice etichetă din tabelul pivot și tastați o nouă etichetă, apoi apăsați Enter
- De exemplu, faceți clic pe eticheta Page1, tastați Regiune și apăsați Enter
Etichetele au fost modificate în captura de ecran prezentată mai jos. Antetul Etichetelor coloanei a fost înlocuit cu un caracter spațial.
Schimbați aspectul
În mod implicit, tabelul pivot are aspectul raportului compact și îl puteți schimba în contur, astfel încât fiecare câmp rând va fi într-o coloană separată. Apoi, mutați câmpul Pagină în zona Rând, deasupra câmpului Rând existent.
- Selectați orice celulă din tabelul pivot
- Pe Panglică, sub Instrumente pentru tabelul pivot, faceți clic pe fila Proiectare.
- În grupul Layout, faceți clic pe Report Layout, apoi faceți clic pe Outline Form
- În lista de câmpuri a tabelului pivot, trageți câmpul Pagina1 din zona Filtre, în zona Rând, deasupra câmpului Rând existent.
- Schimbați câmpul rând la Element, acum că se află într-o coloană separată.
Limitele consolidării multiple
În acest exemplu, Elementul este prima coloană din sursa de date, iar titlul rândului din tabelul pivot arată numele articolelor. Câmpurile rămase sunt afișate în zona coloanei.
-
Tabelul pivot conține unele date fără sens, cum ar fi suma datei și coloanele pline de zerouri în care coloanele bazei de date conțin text. Eliminați aceste câmpuri, așa cum este descris în secțiunea de mai sus pentru Curățarea tabelului pivot.
- Pentru a obține cele mai bune rezultate, rearanjați coloanele bazei de date, astfel încât cea mai importantă coloană se află în extrema stângă. Această coloană de date va deveni valorile Row din tabelul pivot.
- Dacă există coloane pe care nu le doriți în tabelul pivot, mutați-le în extrema dreaptă în datele sursă. Apoi, nu includeți aceste coloane atunci când selectați intervalele de date pentru tabelul pivot.
Alternative la consolidarea multiplă
Pentru a evita limitările mai multor intervale de consolidare, puteți combina datele sursă într-un singur tabel, utilizând una dintre următoarele metode.
Combinați tabelele cu Power Interogare
Dacă aveți o versiune de Excel care acceptă programul de completare Microsoft Power Query, îl puteți folosi pentru a combina datele în două sau mai multe tabele. Tabelele pot fi în același registru de lucru sau în fișiere diferite.
Tabelele pot avea structuri diferite și ar trebui să aibă câteva coloane cu titluri identice, în care datele pot fi combinate. În acest exemplu, datele regiunii Est și Vest vor fi combinate, iar o coloană este unică în fiecare tabel.
Accesați pagina Combinați tabelele cu Power Query pentru instrucțiuni scrise și fișierul eșantion.
Pentru a urma acest tutorial video, accesați pagina Combinați tabelele cu Power Query și descărcați fișierul eșantion cu datele de vânzări Est și Vest.
Creați o interogare Union
Dacă nu vă puteți combina datele într-o singură foaie de lucru, o altă soluție este să creați intervale denumite într-un fișier Excel și să utilizați Microsoft Query (MS Query) pentru a combina datele.
Creați manual o interogare Union
În Excel, puteți deschide instrumentul de interogare Microsoft și puteți scrie o instrucțiune SQL pentru a crea o interogare Union (unire exterioară completă) pentru a combina mai multe tabele. Apoi, utilizați rezultatul ca date sursă ale tabelului pivot.
Pentru a vedea un exemplu, descărcați fișierele eșantion Union Query. Are o interogare care a fost construită manual și are un buton pentru reîmprospătarea datelor.
Cu această soluție, veți ajunge la un tabel pivot normal, fără nici una dintre limitări. Cu toate acestea, este un pic plictisitor să vă configurați, mai ales dacă aveți mai mult de câteva mese.
Puteți citi mai multe despre interogările MS aici:
Macro de interogare Union - Foi într-un singur fișier
În loc să configurați manual o interogare sindicală, puteți utiliza codul într-un fișier eșantion de la Excel MVP-uri, Kirill Lapin (KL), cu amendamente de Hector Miguel Orozco Diaz.
1. Schimbați numele foilor
Înainte de a utiliza codul de probă, înlocuiți numele de foi de probă cu numele de foi din registrul de lucru. De exemplu, dacă numele foilor dvs. sunt „Est” și „Vest”, modificați această linie de cod:
- arrSheets = Array („Ontario”, „Alberta”)
- arrSheets = Array („Est”, „Vest”)
2. Schimbați locația tabelului pivot
În cod, puteți schimba și locația în care va fi adăugat tabelul pivot. În fișierul eșantion, TableDestination este setat pentru foaia activă, în intervalul A1.
3. Rulați macro-ul
Apoi, după ce faceți aceste mici modificări, faceți clic pe butonul din foaia de lucru și se creează automat un tabel pivot rezumat.
Macro de interogare Union - date în mai multe fișiere Excel
Dacă trebuie să combinați date în mai multe fișiere, iată câteva opțiuni, folosind macro-urile furnizate de expertul Excel, Kirill Lapin.
Masă rotativă - Primul exemplu funcționează pe mai multe fișiere, care trebuie să aibă datele în structuri identice și puteți citi instrucțiunile de pe blogul meu. Pentru a vedea codul tabelului pivot al lui Kirill, puteți descărca exemplul Pivot Workbooks. Dosarul comprimat care conține fișierul Report.xls și cele cinci exemplare de fișiere de date. Dezarhivați folderul și păstrați toate fișierele în același folder. Când deschideți fișierul Report.xls, activați macrocomenzile pentru a rula codul.
Tabel pivot sau Tabel Excel - Selectați două sau mai multe fișiere care au liste într-o structură identică, iar codul din acest registru de lucru va crea automat un tabel pivot sau un tabel Excel din toate datele. Citiți detaliile din postarea pe blog, Creați un tabel pivot din mai multe fișiere. Faceți clic aici pentru a descărca exemplele de fișiere.
Descărcați fișierul de probă
Instrumente pentru tabelul pivot
Pentru a economisi timp la construirea, formatarea și modificarea tabelelor pivot, utilizați instrumentele din programul meu de completare Pivot Power Premium. Cu doar câteva clicuri, puteți:
- copiați formatarea dintr-un tabel pivot și aplicați-l pe un alt tabel pivot.
- schimbați toate valorile de la Număr la sumă
- eliminați „Suma de” din toate titlurile
Nu ratați sfaturile noastre Excel
Nu ratați ultimele mele sfaturi și videoclipuri Excel! Faceți clic pe OK, pentru a obține buletinul meu săptămânal cu sfaturi Excel și linkuri către alte știri și resurse Excel.