Calcularea valorilor dintr-un raport PivotTable

Pentru a calcula valori într-un raport PivotTable, puteți utiliza unul sau toate tipurile de metode de calcul de mai jos:

• Funcțiile de rezumare în câmpurile de valori    Datele din zona de valori rezumă datele sursă subiacente din raportul PivotTable. De exemplu, următoarele date sursă:

• Generează următoarele rapoarte PivotTable și PivotChart. Dacă creați un raport PivotChart pe baza datelor dintr-un raport PivotTable, valorile din raportul PivotChart reflectă calculele din raportul PivotChart asociat.

• În raportul PivotTable, coloana Lună conține elementele Martie și Aprilie. Câmpul rând Regiune conține elementele Nord, Sud, Est și Vest. Valoarea de la intersecția coloanei Aprilie cu rândul Nord reprezintă venitul total din vânzări din înregistrările datelor sursă care conțin valorile Lună pentru Aprilie și valorile Regiune pentru Nord.

• Într-un raport PivotChart, câmpul Regiune poate fi un câmp categorie care afișează Nord, Sud, Est și Vest ca și categorii. Câmpul Lună poate fi un câmp de serie care afișează elementele Martie, Aprilie și Mai ca serii reprezentate în legendă. Un câmp Valori denumit Sumă vânzări poate conține marcatori de date care reprezintă venitul total în fiecare regiune pentru fiecare lună. De exemplu, un marcator de date poate reprezenta, în funcție de poziția sa pe axa verticală (valoare), totalul vânzărilor pentru Aprilie în regiunea Nord.

• Pentru a calcula câmpurile de valori, sunt disponibile următoarele funcții de rezumare pentru toate tipurile de date sursă, cu excepția datelor sursă OLAP (Online Analytical Processing).

  Funcția	Rezumă
  Sumă	Suma valorilor. Aceasta este funcția implicită pentru date numerice.
  Număr	Numărul valorilor de date. Funcția de rezumare Număr funcționează la fel ca funcția COUNTA. Număr este funcția implicită pentru alte date decât numerele.
  Medie	Media valorilor.
  Max	Cea mai mare valoare.
  Min	Cea mai mică valoare.
  Product	Produsul valorilor.
  Contor de numere	Numărul de valori de date care sunt numere. Funcția de rezumare Contor de numere funcționează la fel ca funcția COUNT.
  StDev	O estimare a abaterii standard a unei populații, unde eșantionul este un subset din întreaga populație.
  StDevp	Abaterea standard a unei populații, unde populația reprezintă toate datele care urmează să fie rezumate.
  Var	O estimare a varianței a unei populații, unde eșantionul este un subset din întreaga populație.
  Varp	Variația unei populații, unde populația reprezintă toate datele care urmează să fie rezumate.

• Calcule particularizate    Un calcul particularizat afișează valori pe baza altor elemente sau celule din zona de date. De exemplu, puteți să afișați valori în câmpul de date Sumă vânzări ca procent din vânzările din Martie sau ca total parțial al elementelor din câmpul Lună.

  Următoarele funcții sunt disponibile pentru calcule particularizate în câmpuri de valori.

  Funcția	Rezultat
  Fără calcul	Afișează valoarea care este introdusă în câmp.
  % din totalul general	Afișează valorile ca procente din totalul general al tuturor valorilor sau punctelor de date din raport.
  % din totalul coloanei	Afișează toate valorile din fiecare coloană sau serie ca procent din totalul pentru coloană sau serie.
  % din totalul rândului	Afișează valoarea din fiecare rând sau categorie ca procent din totalul pentru rând sau categorie.
  % din	Afișează valorile ca procente din valoarea Element de bază selectat în Câmp de bază.
  % din totalul rândului părinte	Calculează valorile după cum urmează: (valoarea elementului) / (valoarea elementului părinte în rânduri)
  % din totalul coloanei părinte	Calculează valorile după cum urmează: (valoarea elementului) / (valoarea elementului părinte în coloane)
  % din total părinte	Calculează valorile după cum urmează: (valoarea elementului) / (valoarea elementului părinte a Câmp de bază selectat)
  Diferență din	Afișează valori ca diferențe față de valoarea din Element de bază din Câmp de bază.
  % Diferență din	Afișează valori ca diferențe procentuale față de valoarea din Element de bază din Câmp de bază.
  Total parțial în	Afișează valoarea pentru elemente succesive în Câmp de bază ca total parțial.
  % total parțial în	Calculează valoarea pentru elemente succesive în Câmp de bază, care sunt afișate ca total parțial sub formă de procente.
  Rang de la cel mai mic la cel mai mare	Afișează rangul valorilor selectate într-un anumit câmp, listând cel mai mic element din câmp ca 1 și cea mai mare valoare cu o valoare de rang mai mare.
  Rang de la cel mai mare la cel mai mic	Afișează rangul valorilor selectate într-un anumit câmp, listând cel mai mare element din câmp ca 1 și fiecare valoare mai mică va avea o valoare de rang mai mare.
  Index	Calculează valorile după cum urmează: ((valoarea celulei) x (total general al totalurilor generale)) / ((total general rânduri) x (total general coloane))

• Formule    Dacă funcțiile de rezumare și calculele particularizate nu obțin rezultatele dorite, puteți crea propriile formule în câmpuri calculate și elemente calculate. De exemplu, puteți adăuga un element calculat cu formula pentru comisionul de vânzări, care poate fi diferit pentru fiecare regiune. În acest caz, raportul va include automat comisionul în subtotaluri și totalurile generale.

Calculele și opțiunile disponibile într-un raport depind de proveniența datelor sursă: dintr-o bază de date OLAP sau o sursă de date non-OLAP.

• Calculele bazate pe date sursă OLAP    Pentru rapoartele PivotTable create pe baza cuburilor OLAP, valorile rezumate sunt precalculate pe serverul OLAP înainte ca Excel să afișeze rezultatele. Nu puteți modifica modul în care aceste valori precalculate sunt calculate în raportul PivotTable. De exemplu, nu puteți să modificați funcția de rezumare care este utilizată pentru a calcula câmpuri de date sau subtotaluri, nici să adăugați câmpuri calculate sau elemente calculate.

  De asemenea, dacă serverul OLAP furnizează câmpuri calculate, numite și membri calculați, le veți vedea în Lista de câmpuri PivotTable. În plus, veți vedea toate câmpurile calculate și elementele calculate create folosind macrocomenzi care au fost scrise în Visual Basic for Applications (VBA) și stocate în registrul dvs. de lucru, dar nu veți putea modifica aceste câmpuri sau elemente. Dacă aveți nevoie de alte tipuri de calcule, contactați administratorul bazei de date OLAP.

  Pentru datele sursă OLAP, puteți să includeți sau să excludeți valorile pentru obiecte ascunse atunci când calculați subtotaluri și totaluri generale.

• Calculele bazate pe date sursă non-OLAP    În rapoartele PivotTable bazate pe alte tipuri de date externe sau pe date din foaia de lucru, Excel utilizează funcția de rezumare Sum pentru a calcula câmpuri de valori care conțin date numerice și funcția de rezumare Count pentru a calcula câmpuri de date care conțin text. Puteți alege o altă funcție de rezumare, cum ar fi Medie, Max sau Min pentru a analiza și particulariza datele. De asemenea, puteți crea propriile formule care utilizează elemente ale raportului sau alte date din foaia de lucru creând un câmp calculat sau un element calculat într-un câmp.

Puteți crea formule doar în rapoartele bazate pe date sursă non-OLAP. Nu puteți utiliza formule în rapoartele bazate pe o bază de date OLAP. Când utilizați formule în rapoarte PivotTable, trebuie să cunoașteți următoarele reguli pentru sintaxa de formulă și comportamentul formulei:

• Elementele formulelor PivotTable    În formulele pe care le creați pentru câmpuri calculate și elemente calculate, puteți utiliza operatori și expresii așa cum faceți în alte formule pentru foi de lucru. Puteți să utilizați constante și să faceți referire la date din raport, dar nu puteți utiliza referințe de celule sau nume definite. Nu puteți utiliza funcții pentru foi de lucru care necesită referințe de celule sau nume definite ca argumente, nici funcții matrice.

• Numele de câmpuri și elemente    Excel utilizează nume de câmpuri și de elemente pentru a identifica aceste elemente de raport în formulele dvs. În exemplul următor, datele din zona C3:C9 utilizează numele de câmp Lactate. Un element calculat din câmpul Tip, care estimează vânzările unui produs nou pe baza vânzărilor de Lactate necesită o formulă precum =Lactate * 115%.

  

  Notă: Într-un raport PivotChart, numele de câmpuri sunt afișate în lista de câmpuri PivotTable și numele de elemente pot fi vizualizate în fiecare listă verticală de câmpuri. Nu confundați aceste nume cu cele pe care le vedeți în sfaturile despre diagrame, care reflectă nume de serii și de puncte de date.

• Formulele operează cu sume totale, nu cu înregistrări individuale    Formulele pentru câmpuri calculate calculează suma datelor subiacente pentru orice câmpuri din formulă. De exemplu, formula de câmp calculat =Vânzări * 1,2 înmulțește suma vânzărilor pentru fiecare tip și regiune cu 1,2; aceasta nu înmulțește fiecare vânzare individuală cu 1,2 și adună apoi rezultatele înmulțirii.

  Formulele pentru elemente calculate operează cu înregistrările individuale. De exemplu, formula de element calculat =Lactate * 115% înmulțește fiecare vânzare individuală de Lactate cu 115%, după care rezultatele înmulțirii sunt rezumate împreună în zona Valori.

• Spațiile, numerele și simbolurile în cadrul numelor    Într-un nume care include mai multe câmpuri, acestea pot fi aranjate în orice ordine. În exemplul de mai sus, celulele C6:D6 pot fi 'Aprilie Nord' sau 'Nord Aprilie'. Utilizați ghilimele simple pentru a încadra numele care au mai multe cuvinte sau care includ numere sau simboluri.

• Totalurile    Formulele nu pot face referire la totaluri (cum ar fi Total martie, Total aprilie și Total general în exemplu).

• Numele de câmpuri din referințele la un element    Puteți include numele de câmp într-o referință la un element. Numele elementului trebuie să fie încadrat între paranteze drepte, de exemplu, Regiune[Nord]. Utilizați acest format pentru a evita erorile #NAME? care apar atunci când două elemente din două câmpuri diferite ale unui raport au același nume. De exemplu, dacă un raport conține un element numit Carne în câmpul Tip și alt element numit Carne în câmpul Categorie, puteți preveni erorile #NAME? făcând referire la elemente astfel: Tip[Carne] și Categorie[Carne].

• Referirea la elemente în funcție de poziție    Puteți face referire la un element în funcție de poziția sa în raport, așa cum este sortat și afișat în prezent. Tip[1] este Lactate și Tip[2] este Fructe de mare. Elementul la care se face referire astfel se poate modifica oricând se modifică pozițiile elementelor sau când sunt afișate sau ascunse diferite elemente. Elementele ascunse nu sunt contorizate în acest index.

  Puteți utiliza poziții relative pentru a face referire la elemente. Pozițiile sunt determinate în raport cu elementul calculat care conține formula. Dacă regiunea curentă este Sud, Regiune[-1] este Nord; dacă regiunea curentă este Nord, Regiune[+1] este Sud. De exemplu, un element calculat poate utiliza formula =Regiune[-1] * 3%. Dacă poziția pe care o introduceți este înaintea primului element sau după ultimul element din câmp, formula generează #REF! eroare.

Pentru a utiliza formule într-un raport PivotChart, creați-le în raportul PivotTable asociat, unde puteți vedea valorile individuale care reprezintă datele dvs., apoi rezultatele reprezentate grafic în raportul PivotChart.

De exemplu, următorul raport PivotChart arată vânzările pentru fiecare vânzător în funcție de regiune:

Pentru a vedea cum ar arăta vânzările dacă ar crește cu 10 procente, puteți crea un câmp calculat în raportul PivotTable asociat care să utilizeze o formulă, cum ar fi =Vânzări * 110%.

Rezultatul apare imediat în raportul PivotChart, așa cum se arată în următoarea diagramă:

Pentru a vedea un marcator de date separat pentru vânzările din regiunea Nord, fără costul transportului în valoare de 8 procente, puteți crea un element calculat în câmpul Regiune cu o formulă precum =Nord - (Nord * 8%).

Diagrama rezultată va arăta așa:

Însă un element calculat creat în câmpul Vânzător va apărea ca o serie reprezentată în legendă, iar în diagramă ar fi apărea ca un punct de date în fiecare categorie.

Înainte să începeți, decideți dacă doriți un câmp calculat sau un element calculat într-un câmp. Utilizați un câmp calculat atunci când doriți să folosiți datele dintr-un alt câmp în formulă. Utilizați un element calculat atunci când doriți ca formula să folosească date din unul sau mai multe elemente specifice dintr-un câmp.

Pentru elementele calculate puteți introduce formule diferite în fiecare celulă. De exemplu, dacă un element calculat denumit NumărPortocale are formula =Portocale * 0,25 pentru toate lunile, puteți să modificați formula în =Portocale * 0,5 pentru iunie, iulie și august.

Dacă aveți mai multe elemente calculate sau formule, puteți ajusta ordinea de calcul.

Adăugarea unui câmp calculat

1. Faceți clic pe raportul PivotTable.

   Astfel se afișează Instrumente PivotTable, adăugând filele Analiză și Proiectare.

2. Pe fila Analiză, în grupul Calcule, faceți clic pe Câmpuri, elemente și seturi, apoi pe Câmp calculat.

   

3. În caseta Nume, tastați un nume pentru câmp.

4. În caseta Formulă, introduceți formula pentru câmp.

   Pentru a utiliza datele dintr-un alt câmp în formulă, faceți clic pe câmpul din caseta Câmpuri, apoi pe Inserare câmp. De exemplu, pentru a calcula un comision de 15% pentru fiecare valoare din câmpul Vânzări, puteți introduce = Vânzări * 15%.

5. Faceți clic pe Adăugare.

Adăugarea unui element calculat la un câmp

1. Faceți clic pe raportul PivotTable.

   Astfel se afișează Instrumente PivotTable, adăugând filele Analiză și Proiectare.

2. Dacă elementele din câmp sunt grupate, pe fila Analiză, în grupul Grup, faceți clic Anulare grupare.

   

3. Faceți clic pe câmpul în care doriți să adăugați elementul calculat.

4. Pe fila Analiză, în grupul Calcule, faceți clic pe Câmpuri, elemente și seturi, apoi pe Element calculat.

   

5. În caseta Nume, tastați un nume pentru elementul calculat.

6. În caseta Formulă, introduceți formula pentru element.

   Pentru a utiliza datele dintr-un element în formulă, faceți clic pe acesta în lista Elemente, apoi pe Inserare element (elementul trebuie să provină din același câmp ca elementul calculat).

7. Faceți clic pe Adăugare.

Introducerea unor formule diferite în fiecare celulă pentru elementele calculate

1. Faceți clic pe o celulă pentru care doriți să modificați formula.

   Pentru a modifica formula pentru mai multe celule, țineți apăsată tasta CTRL și faceți clic pe celulele suplimentare.

2. În bară de formule, tastați modificările pentru formulă.

Ajustarea ordinii de calcul pentru mai multe elemente calculate sau formule

1. Faceți clic pe raportul PivotTable.

   Astfel se afișează Instrumente PivotTable, adăugând filele Analiză și Proiectare.

2. Pe fila Analiză, în grupul Calcule, faceți clic pe Câmpuri, elemente și seturi, apoi pe Ordine de rezolvare.

   

3. Faceți clic pe o formulă, apoi pe Mutare în sus sau pe Mutare în jos.

4. Continuați până când formulele sunt aranjate în ordinea în care doriți să fie calculate.

Puteți să afișați o listă a tuturor formulelor utilizate în raportul PivotTable curent.

1. Faceți clic pe raportul PivotTable.

   Astfel se afișează Instrumente PivotTable, adăugând filele Analiză și Proiectare.

2. Pe fila Analiză, în grupul Calcule, faceți clic pe Câmpuri, elemente și seturi, apoi pe Listare formule.

   

Înainte de a edita o formulă, stabiliți dacă aceasta este într-un câmp calculat sau un element calculat. Dacă formula este într-un element calculat, stabiliți și dacă aceasta este singura formulă pentru elementul calculat.

Pentru elementele calculate, puteți să editați formule individuale pentru anumite celule ale unui element calculat. De exemplu, dacă un element calculat denumit CalcPortocale are formula =Portocale * 0,25 pentru toate lunile, puteți să modificați formula în =Portocale * 0,5 pentru iunie, iulie și august.

Determinați dacă o formulă este într-un câmp calculat sau un element calculat

1. Faceți clic pe raportul PivotTable.

   Astfel se afișează Instrumente PivotTable, adăugând filele Analiză și Proiectare.

2. Pe fila Analiză, în grupul Calcule, faceți clic pe Câmpuri, elemente și seturi, apoi pe Listare formule.

   

3. În lista de formule, găsiți formula pe care doriți să o modificați listată sub Câmp calculat sau Element calculat.
   
   Atunci când există mai multe formule pentru un element calculat, formula implicită care a fost introdusă atunci când s-a creat element are numele de element calculat în coloana B. Pentru formule suplimentare pentru un element calculat, coloana B conține atât numele elementului calculat, cât și numele elementelor care se intersectează.

   De exemplu, aveți o formulă implicită pentru un element calculat numit ElementulMeu și o altă formulă pentru acest element, identificată ca ElementulMeu Vânzări ianuarie. În raportul PivotTable, veți găsi această formulă în celula Vânzări pentru rândul ElementulMeu și coloana Ianuarie.

4. Continuați utilizând una dintre următoarele metode de editare.

Editarea unei formule pentru un câmp calculat

1. Faceți clic pe raportul PivotTable.

   Astfel se afișează Instrumente PivotTable, adăugând filele Analiză și Proiectare.

2. Pe fila Analiză, în grupul Calcule, faceți clic pe Câmpuri, elemente și seturi, apoi pe Câmp calculat.

   

3. În caseta Nume, selectați câmpul calculat pentru care doriți să modificați formula.

4. În caseta Formulă, editați formula.

5. Faceți clic pe Modificare.

Editarea unei formule individuale pentru un element calculat

1. Faceți clic pe câmpul care conține elementul calculat.

2. Pe fila Analiză, în grupul Calcule, faceți clic pe Câmpuri, elemente și seturi, apoi pe Element calculat.

   

3. În caseta Nume, selectați elementul calculat.

4. În caseta Formulă, editați formula.

5. Faceți clic pe Modificare.

Editarea unei formule individuale pentru o anumită celulă a unui element calculat

1. Faceți clic pe o celulă pentru care doriți să modificați formula.

   Pentru a modifica formula pentru mai multe celule, țineți apăsată tasta CTRL și faceți clic pe celulele suplimentare.

2. În bară de formule, tastați modificările pentru formulă.

   Sfat: Dacă aveți mai multe elemente calculate sau formule, puteți ajusta ordinea de calcul. Pentru mai multe informații, consultați Ajustarea ordinii de calcul pentru mai multe elemente calculate sau formule.

1. Determinați dacă formula este într-un câmp calculat sau un element calculat.

   Câmpurile calculate apar în Lista de câmpuri PivotTable. Elementele calculate apar ca elemente în alte câmpuri.

2. Alegeți una dintre următoarele variante:

   • Pentru a șterge un câmp calculat, faceți clic oriunde în raportul PivotTable.

   • Pentru a șterge un element calculat, în raportul PivotTable, faceți clic pe câmpul care conține elementul pe care doriți să-l ștergeți.

     Astfel se afișează Instrumente PivotTable, adăugând filele Analiză și Proiectare.

3. Pe fila Analiză, în grupul Calcule, faceți clic pe Câmpuri, elemente și seturi, apoi pe Câmp calculat sau pe Element calculat.

   

4. În caseta Nume, selectați câmpul sau elementul pe care doriți să-l ștergeți.

5. Faceți clic pe Ștergere.