Cum să localizați și să modificați datele sursă pentru un tabel pivot Excel. Pentru datele sursă lipsă, pași pentru a încerca să le recreați. Schimbați opțiunile, pentru a salva datele sursă cu fișierul tabelului pivot.

NOTĂ: Pentru macrocomenzile care ajută la gestionarea datelor sursă, accesați pagina macrocomenzilor cu datele sursă ale tabelului pivot. Pentru ca o macrocomandă să nepivoteze datele sursă, accesați pagina Unpivot Excel Macro date

Localizați și remediați datele sursă

În acest videoclip, veți vedea cum să localizați tabelul pivot sursa de date, apoi verificați sursa de date, pentru a vă asigura că include toate rândurile și coloanele de care aveți nevoie. Dacă este necesar, reglați sursa de date, pentru a include rânduri sau coloane noi.

NOTĂ: soluție pe termen lung, utilizați un tabel Excel numit, ca sursă de date. Se va regla automat, dacă se adaugă rânduri noi.

Instrucțiunile scrise se află sub videoclip și puteți descărca fișierul eșantion de urmat cu videoclipul.

Localizați datele sursă

După ce a fost creat un tabel pivot, poate doriți să adăugați înregistrări noi în datele sursă sau să modificați înregistrările existente. registru de lucru mare, poate fi dificil să localizați sursa exactă pentru tabelul pivot, dacă există mai multe tabele sau liste.

NOTĂ: Dacă mi-ați cumpărat suplimentul Pivot Power Premium, faceți clic pe Tabelul pivot Informații, apoi faceți clic pe Accesați datele sursă.

Urmați acești pași, pentru a găsi datele sursă pentru un tabel pivot:

1. Selectați orice celulă din tabelul pivot.
2. Pe panglică, sub fila Instrumente pentru tabelul pivot, faceți clic pe fila Analize (în Excel 2010, faceți clic pe fila Opțiuni).
3. În grupul de date, faceți clic pe secțiunea superioară a comenzii Schimbare sursă de date.

Se deschide caseta de dialog Modificare sursă date tabel pivot și puteți vedea tabelul sau intervalul sursă în caseta Table / Range. Aceasta poate fi o referință la o foaie și o gamă de celule, cum ar fi

• Comenzi! $ A $ 1: $ H $ 9

sau un nume de tabel, cum ar fi

• Sales_East

În spatele casetei de dialog, puteți vedea intervalul sursă pe foaia de lucru, înconjurat de o margine în mișcare.

În captura de ecran de mai jos, intervalul de date sursă se termină la rândul 9 și a fost adăugată o nouă înregistrare în rândul 10. Această înregistrare nu va apărea în tabelul pivot, decât dacă Sursa de date este ajustată.

Reglați intervalul de date sursă

Dacă datele sursă ale unui tabel pivot sunt o referință statică la o anumită foaie și interval, nu se ajustează automat când se adaugă date noi. În captura de ecran de mai sus, intervalul Sursă de date se referă la Comenzi! $ A $ 1: $ H $ 9 și poate fi ajustat manual, pentru a include rândul 10.

Pentru a regla manual gama sursei statice:

Puteți ajusta gama sursei atunci când este necesar, astfel încât să includă date noi, dar o soluție mai bună este crearea unei surse dinamice , și bazați tabelul pivot pe acesta. Instrucțiunile sunt mai jos.

Modificați datele sursă

Ocazional, poate fi necesar să modificați un tabel pivot, astfel încât să utilizeze o sursă de date diferită. În acest exemplu, a fost creat un nou tabel, cu doar date pentru r egion. Tabelul pivot va fi schimbat pentru a utiliza sursa respectivă, iar apoi poate fi trimis unui manager din regiunea Est, fără a dezvălui datele regiunii Vest.

NOTĂ: După trecerea la o altă tabelă sursă , poate fi necesar să redenumiți câmpuri sau să adăugați câmpuri diferite în tabelul pivot.

Pentru a schimba datele sursă pentru un tabel pivot Excel, urmați acești pași:

1. Selectați orice celulă din tabelul pivot.
2. Pe panglică, sub fila Instrumente pentru tabelul pivot, faceți clic pe fila Analize (în Excel 2010, faceți clic pe fila Opțiuni).
3. În Date grup, faceți clic pe secțiunea de sus a comenzii Schimbare sursă date.
4. Se deschide caseta de dialog Modificare sursă date tabel pivot și puteți vedea tabelul sau intervalul sursă în caseta Tabel / Interval. În această captură de ecran mai jos, sursa de date curentă este tabelul Sales_Data, care conține date din ambele regiuni ..

   

5. Ștergeți sursa de date existentă , și tastați un nume nou de tabel sau selectați o sursă nouă de date e gama. Tabelul / Intervalul este schimbat în Sales_East, unde sunt listate doar datele de Est.

   

6. Faceți clic pe OK.

Sursă dinamică pentru tabelul pivot

În loc să utilizați un interval static ca sursă de date a tabelului pivot, o soluție mai bună este să creați un interval dinamic, care să se ajusteze automat în dimensiune.

Creați o sursă dinamică – tabel Excel

Sursă dinamică – Interval denumit

Sursă dinamică – Matrice dinamică

Utilizați o sursă dinamică

Sursă dinamică – Tabel Excel

În loc să utilizați un interval static ca sursă de date a tabelului pivot, o soluție mai bună este crearea unui interval dinamic, bazat pe un tabel Excel numit. Există instrucțiuni scrise aici sau vizionați videoclipul de mai jos.

Notați numele tabelului, astfel încât să îl puteți utiliza ca sursă a tabelului pivot

Sursă dinamică – Interval denumit

Dacă nu puteți utiliza un tabel Excel ca date sursă, creați un interval denumit dinamic, pe baza unei formule INDEX sau OFFSET. Există instrucțiuni scrise aici sau vizionați videoclipul de mai jos.

Videoclipul arată cum să creați un interval dinamic cu funcția OFFSET, în Excel 2007, iar pașii sunt similari în versiunile ulterioare.

NOTĂ: Rețineți numele intervalului, astfel încât să îl puteți utiliza ca sursă a tabelului pivot

Sursă dinamică – matrice dinamică

Dacă aveți o versiune Excel care acceptă noile funcții, cum ar fi SORT sau UNIQUE, puteți utiliza acele funcții pentru a crea matrice dinamice. Această tehnică folosește doar formule – fără macrocomenzi .

În secțiunea Descărcare, obțineți fișierul eșantion de date sursă filtrate. Acesta arată cum să configurați un interval numit cu numai rândurile vizibile dintr-un tabel Excel numit.

Iată filtere d date, pe o foaie diferită, cu doar cele 2 repetări și 3 categorii din rândurile vizibile.

Apoi, puteți crea un tabel pivot numai pe baza datelor filtrate.

Utilizați sursa dinamică pentru tabelul pivot

După ce creați o sursă dinamică care conține tabelul pivot date, urmați acești pași, pentru a utiliza acele date sursă:

1. Selectați orice celulă din tabelul pivot.
2. Pe panglică, sub fila Instrumente tabel pivot, faceți clic pe Analizați fila (în Excel 2010, faceți clic pe fila Opțiuni).
3. În grupul de date, faceți clic pe secțiunea de sus a comenzii Modificare sursă de date.
4. În caseta de dialog Schimbare sursă date tabel pivot, debifați caseta Tabel / Interval
5. În caseta Tabel / Interval, tastați numele tabelului sau intervalul numit pe care l-ați creat. În acest exemplu, sursa dinamică este un tabel numit – tblOrders.
6. Faceți clic pe OK

Salvați datele sursă cu tabelul pivot

Când creați un tabel pivot în registrul de lucru, înregistrările de date sursă sunt salvate într-o zonă de memorie specială – o memorie cache pivot. Apoi, când închideți fișierul, Excel poate salva datele sursă în acest cache pivot sau poate șterge memoria respectivă.

Există avantaje și dezavantaje ale ambelor opțiuni:

Dacă salvați datele sursă:

• Fișierul va fi mai mare
• Fișierul se poate deschide mai rapid

Dacă nu salvați datele sursă:

• Completarea va fi mai mică
• Fișierul se poate deschide încet, în timp ce cache-ul pivot este reconstruit
• Tabelul pivot trebuie reîmprospătat după deschiderea fișierului

Modificarea setării

Pentru a activa Salvarea setarea Date sursă activată sau dezactivată:

• Faceți clic dreapta pe o celulă din tabelul pivot și faceți clic pe Opțiuni pentru tabelul pivot.
• În fila Date, în secțiunea Date din tabelul pivot. , adăugați sau eliminați bifa din Salvare date sursă cu fișier
• Faceți clic pe OK.

Actualizați datele la deschidere

Dacă alegeți să activați dezactivată opțiunea Salvare date sursă cu fișier, ar trebui să activați opțiunea Reîmprospătare date la deschiderea opțiunii Fișier. Această setare este chiar sub caseta de selectare Salvare date sursă.

În caz contrar, veți vedea un mesaj când încercați să filtrați datele sau să efectuați orice alte modificări ale aspectului.

• „Raportul PivotTable a fost salvat fără datele de bază. Utilizați comanda Reîmprospătare date pentru a actualiza raportul. „

Dacă vedeți acel mesaj, faceți clic pe OK, apoi reîmprospătați manual tabelul pivot.

Și pentru a evita enervantul mesaj, activați opțiunea Reîmprospătare date la deschiderea fișierului.

Recreați datele sursă ale tabelului pivot

Dacă ștergeți accidental foaia de lucru care conține datele sursă pentru tabelul pivot sau dacă ați primit un fișier fără datele tabelului pivot, este posibil să puteți utiliza funcția Afișare detalii a tabelului pivot pentru a-l recrea.

NOTĂ: Acest lucru nu va funcționa pentru toate tabelele pivot, dar este merită încercat, pentru a recupera datele sursă.

Pentru a încerca să recreați datele sursă pentru un tabel pivot, urmați acești pași pentru a utiliza caracteristica Afișare detalii:

1. Asigurați-vă că că niciunul dintre elementele din câmpurile tabelului pivot nu este ascuns – ștergeți toate filtrele și secționatoarele care au fost aplicate.
2. Tabelul pivot nu trebuie să conțină toate câmpurile – asigurați-vă că există este cel puțin un câmp din Val zona ues.
3. Afișați totalurile mari pentru rânduri și coloane. Dacă totalurile nu sunt vizibile, selectați o celulă din tabelul pivot, iar pe Panglică, sub Instrumente tabel pivot, faceți clic pe fila Analize. În grupul Layout, faceți clic pe Totaluri mari, apoi faceți clic pe Activat pentru rânduri și coloane.
4. Faceți dublu clic pe celula totală din partea dreaptă jos a tabelului pivot. Aceasta ar trebui să creeze o foaie nouă cu înregistrările aferente din datele sursă originale.

Remediați extrasul Date

Dacă recuperați datele sursă, este posibil să trebuiască să faceți modificări suplimentare pentru a le restabili la starea inițială.

• Redenumiți tabelul, utilizând numele sursei originale, dacă este cunoscut.
• Dacă datele sursei originale conțin formule, va trebui să le recreați, deoarece funcția Afișează detalii exportă numai datele.
• Coloanele din datele extrase vor fi în aceeași ordine ca și în datele sursă originale.
• Datele extrase vor fi formatat cu formatul de tabel implicit al registrului de lucru. Puteți aplica un alt format de tabel sau puteți aplica propria dvs. formatare.
• Notă: dacă ați făcut modificări la datele sursă și nu ați actualizat tabelul pivot, aceste modificări nu vor fi în datele extrase.

Conectați-vă la datele extrase

Dacă redenumiți tabelul care a fost creat în timpul procesului Afișare detalii și utilizați același nume ca tabelul care a deținut inițial sursa date, tabelul pivot s-ar putea conecta automat la noile date sursă.

Dacă nu, vă puteți conecta la datele surse recreate – urmați pașii din secțiunea de mai sus: Utilizați sursa dinamică pentru tabelul pivot – utilizați numele tabelului pe care l-ați dat noului tabel.

Descărcați fișierul eșantion

Exemplu sursă pivot: Pentru a urma împreună cu tutorialele, puteți descărca fișierul DateAmt.zip. Fișierul comprimat este în format xlsx și nu conține macrocomenzi.

Date sursă filtrate Exemplu: Acest exemplu este pentru versiunile Excel care au noile funcții, cum ar fi SORT și UNIQUE. Dow Descărcați acest fișier eșantion pentru a vedea un tabel pivot creat numai din rândurile vizibile, într-un tabel Excel filtrat. Fișierul zip este în format xlsx și nu conține macrocomenzi.

Instrumente pentru tabelul pivot

Pentru a economisi timp la construirea, formatarea și modificarea tabelelor pivot, utilizați instrumentele din pivotul meu Supliment Power Power. Cu doar câteva clicuri, puteți:

• copiați formatarea dintr-un tabel pivot și să îl aplicați pe un alt tabel pivot.
• modificați toate valorile din Număr în Sumă
• eliminați „Suma din” din toate titlurile

și multe altele!

Nu ratați sfaturile noastre Excel