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

  1. 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.
  2. Faceți clic pe Mai multe intervale de consolidare, apoi faceți clic pe Următorul

  • Faceți clic pe „Voi crea câmpurile de pagină”, apoi faceți clic pe Următorul
  • Selectați fiecare zonă și faceți clic pe Adăugare
    • Î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]

  • Faceți clic pe 1 ca număr de câmpuri de pagină
  • În lista intervalelor, selectați primul interval și tastați o etichetă de articol pentru intervalul respectiv în câmpurile de pagină
  • Repetați pentru intervalele rămase. În captura de ecran de mai jos, intervalul de pe foaia de Vest este selectat, iar eticheta articolului „Vest” a fost introdusă pentru acea zonă.

  • Faceți clic pe Următorul
  • Selectați o locație pentru tabelul pivot, apoi faceți clic pe Finalizare

    • 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.

    1. Faceți clic pe săgeata verticală din antetul Etichetelor coloanei
    2. Eliminați bifele pentru câmpurile pe care doriți să le eliminați.
    3. 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.

    1. Faceți clic dreapta pe una dintre valori
    2. 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.

    1. Faceți clic dreapta pe titlul pentru Totalul total pentru rânduri
    2. 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.

    1. Faceți clic pe orice etichetă din tabelul pivot și tastați o nouă etichetă, apoi apăsați Enter
    2. 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.

    1. Selectați orice celulă din tabelul pivot
    2. Pe Panglică, sub Instrumente pentru tabelul pivot, faceți clic pe fila Proiectare.
    3. În grupul Layout, faceți clic pe Report Layout, apoi faceți clic pe Outline Form
    4. Î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.
    5. 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.

  • Puteți schimba funcția (de exemplu SUM) care este utilizată de valoarea datelor, dar va utiliza aceeași funcție pe toate aceste coloane.
  • Prima coloană din datele sursă este întotdeauna adăugată în zona Rând din tabelul 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.

    tabelului

    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.