Conversia celulelor PivotTable în formule de foaie de lucru

Un raport PivotTable are mai multe aspecte care furnizează o structură predefinită raportului, dar nu puteți particulariza aceste aspecte. Dacă aveți nevoie de mai multă flexibilitate în proiectarea aspectului unui raport PivotTable, puteți să efectuați conversia celulelor la formule de foaie de lucru, apoi să modificați aspectul acestor celule, profitând din plin de toate caracteristicile disponibile într-o foaie de lucru. Puteți face conversia celulelor în formule care utilizează funcțiile Cube sau utilizați funcția GETPIVOTDATA. Conversia celulelor în formule simplifică mult procesul de creare, actualizare și întreținere a acestor rapoarte PivotTable particularizate.

Când efectuați conversia celulelor în formule, aceste formule accesează aceleași date ca raportul PivotTable și pot fi reîmprospătate pentru a vedea rezultate actualizate. Însă, cu excepția posibilă a filtrelor de raport, nu mai aveți acces la caracteristicile interactive ale unui raport PivotTable, cum ar fi nivelurile de filtrare, sortare sau extindere și restrângere.

Aflați despre scenariile obișnuite pentru conversia rapoartelor PivotTable în formule de foaie de lucru

Următoarele sunt exemple tipice cu ceea ce puteți face după ce efectuați conversia celulelor PivotTable în formule de foaie de lucru pentru a particulariza aspectul celulelor convertite.

Rearanjarea și ștergerea celulelor 

Să presupunem că aveți un raport periodic pe care trebuie să-l creați în fiecare lună pentru cadrele dvs. didactice. Aveți nevoie doar de un subset de informații din raport și preferați să aranjați datele într-un mod particularizat. Puteți pur și simplu să mutați și să aranjați celulele dintr-un aspect de proiectare dorit, să ștergeți celulele care nu sunt necesare pentru raportul lunar de personal, apoi să formatați celulele și foaia de lucru după preferințele dvs.

Inserarea rândurilor și coloanelor 

Să presupunem că doriți să afișați informațiile de vânzări pentru ultimii doi ani, separate pe regiuni și grupe de produse și că doriți să inserați un comentariu extins în rânduri suplimentare. Inserați pur și simplu un rând și introduceți textul. În plus, doriți să adăugați o coloană care arată vânzările după regiune și după grupul de produse care nu se află în raportul PivotTable original. Inserați o coloană, adăugați o formulă pentru a obține rezultatele dorite, apoi completați coloana în jos pentru a obține rezultatele pentru fiecare rând.

Utilizarea mai multor surse de date 

Să presupunem că doriți să comparați rezultatele între o bază de date de producție și cea de testare pentru a vă asigura că baza de date de test generează rezultatele așteptate. Puteți să copiați cu ușurință formule de celule, apoi să modificați argumentul de conexiune pentru a indica spre baza de date de test pentru a compara aceste două rezultate.

Utilizarea referințelor la celule pentru a varia intervenția utilizatorului 

Să presupunem că doriți ca întregul raport să se modifice pe baza intervențiilor utilizatorului. Puteți să transformați argumentele formulelor Cub în referințe de celule în foaia de lucru, apoi să introduceți valori diferite în acele celule pentru a obține rezultate diferite.

Crearea unui aspect neuniform de rând sau de coloană (denumit și raportare asimetrică) 

Să presupunem că trebuie să creați un raport care conține o coloană pentru anul 2008 denumită Vânzări reale, o coloană pentru 2009 numită Vânzări proiectate, dar nu doriți alte coloane. Puteți crea un raport care conține doar acele coloane, spre deosebire de raportul PivotTable, care necesită raportare simetrică.

Crearea propriilor formule de cub și expresii MDX 

Să spunem că doriți să creați un raport care arată vânzările pentru un anumit produs de către trei anumiți vânzători pentru luna iulie. Dacă aveți cunoștințe despre expresiile MDX și interogările OLAP, puteți introduce singur formulele Cub. Deși aceste formule pot deveni destul de elaborate, puteți să simplificați crearea și să îmbunătățiți acuratețea lor utilizând Completare automată formulă. Pentru mai multe informații, consultați Utilizarea completării automate a formulei.

Conversia celulelor la formule care utilizează funcții de cub

1. Pentru a salva raportul PivotTable pentru utilizare ulterioară, vă recomandăm să faceți o copie a registrului de lucru înainte de a efectua conversia raportului PivotTable, făcând clic pe Fișier>Salvare ca. Pentru mai multe informații, consultați Salvarea unui fișier.

2. Pregătiți raportul PivotTable astfel încât să minimizați rearanjarea celulelor după conversie, efectuând următoarele:

   • Treceți la un aspect care seamănă cel mai mult cu aspectul dorit.
   • Interacționați cu raportul, de exemplu, filtrați, sortați și reproiectați raportul, pentru a obține rezultatele dorite.

3. Faceți clic pe raportul PivotTable.

4. Pe fila Opțiuni , în grupul Instrumente , faceți clic pe Instrumente OLAP, apoi faceți clic pe Conversie în formule.
   Dacă nu există filtre de raport, operațiunea de conversie se termină. Dacă există unul sau mai multe filtre de raport, se afișează caseta de dialog Conversie în formule .

5. Decideți cum doriți să efectuați conversia raportului PivotTable:
   Conversia întregului raport PivotTable 

   • Bifați caseta de selectare Conversie filtre de raport .
     Aceasta efectuează conversia tuturor celulelor în formule de foaie de lucru și șterge întregul raport PivotTable.
     Efectuați conversia numai a etichetelor de rând, etichetelor de coloană și a zonei de valori din PivotTable, dar păstrați filtrele de raport 

   • Asigurați-vă că este debifată caseta de selectare Conversie filtre de raport . (Aceasta este opțiunea implicită.)
     Aceasta efectuează conversia tuturor celulelor de etichete de rând, etichetă de coloană și celule de zonă de valori în formule de foaie de lucru și păstrează raportul PivotTable original, dar doar cu filtrele de raport, astfel încât să puteți continua filtrarea utilizând filtrele de raport.

     Notă

     Dacă formatul PivotTable este versiunea 2000-2003 sau o versiune anterioară, puteți efectua conversia doar pentru întregul raport PivotTable.

6. Faceți clic pe Conversie.
   Operațiunea de conversie reîmprospătează mai întâi raportul PivotTable pentru a se asigura că se utilizează date actualizate.
   Un mesaj se afișează în bara de stare în timp ce are loc operațiunea de conversie. Dacă operațiunea durează mult timp și preferați să efectuați conversia în alt moment, apăsați pe ESC pentru a anula operațiunea.

   Notă

   • Nu puteți efectua conversia celulelor cu filtre aplicate la niveluri ascunse.
   • Nu puteți efectua conversia celulelor în care câmpurile au un calcul particularizat care au fost create prin intermediul filei Afișare valori ca din caseta de dialog Setări câmp valori . (Pe fila Opțiuni , în grupul Câmp activ , faceți clic pe Câmp activ, apoi faceți clic pe Setări câmp valori.)
   • Pentru celulele care sunt convertite, formatarea celulelor se păstrează, dar stilurile PivotTable sunt eliminate, deoarece aceste stiluri se pot aplica doar la rapoartele PivotTable.

Conversia celulelor utilizând funcția GETPIVOTDATA

Aveți posibilitatea să utilizați funcția GETPIVOTDATA într-o formulă pentru a efectua conversia celulelor PivotTable în formule de foaie de lucru atunci când doriți să lucrați cu surse de date non-OLAP, când preferați să nu faceți upgrade imediat la noul format PivotTable versiunea 2007 sau când doriți să evitați complexitatea utilizării funcțiilor Cube.

1. Asigurați-vă că este activată comanda Generare GETPIVOTDATA din grupul PivotTable din fila Opțiuni .

   Notă

   Comanda Generare GETPIVOTDATA setează sau debifează opțiunea Utilizare funcții GETPIVOTTABLE pentru referințe PivotTable din categoria Formule a secțiunii Lucrul cu formulele din caseta de dialog Opțiuni Excel.

2. În raportul PivotTable, asigurați-vă că celula pe care doriți să o utilizați în fiecare formulă este vizibilă.

3. Într-o celulă din foaia de lucru din afara raportului, tastați formula pe care o doriți până la punctul în care doriți să includeți date din raport.

4. Faceți clic pe celula din raportul PivotTable pe care doriți să o utilizați în formulă în raportul PivotTable. O funcție GETPIVOTDATA foaie de lucru este adăugată la formula dvs. care regăsește datele din raportul PivotTable. Această funcție continuă să preia datele corecte dacă aspectul raportului se modifică sau dacă reîmprospătați datele.

5. Terminați de tastat formula și apăsați pe ENTER.

Problemă: imposibil de efectuat conversia celulelor PivotTable în formule de foaie de lucru