Cum se adaugă și se utilizează un câmp calculat în tabelul pivot Excel

Adesea, odată ce creați un tabel pivot, este necesar să vă extindeți analiza și să includeți mai multe date / calcule ca parte a acesteia.

Dacă aveți nevoie de un nou punct de date care poate fi obținut utilizând punctele de date existente în tabelul pivot, nu este necesar să vă întoarceți și să îl adăugați în datele sursă. În schimb, puteți utiliza un Câmp calculat din tabelul pivot pentru a face acest lucru.

Descărcați setul de date și urmați.

Ce este un câmp calculat în tabelul pivot?

Să începem cu un exemplu de bază al unui tabel pivot.

Să presupunem că aveți un set de date de comercianți cu amănuntul și că creați un tabel pivot așa cum se arată mai jos:

Tabelul pivot de mai sus rezumă valorile vânzărilor și profitului pentru comercianții cu amănuntul.

Acum, ce se întâmplă dacă doriți, de asemenea, să știți care a fost marja de profit a acestor comercianți cu amănuntul (unde marja de profit este „Profit” împărțită la „Vânzări”).

Există câteva modalități de a face acest lucru:

1. Reveniți la setul de date original și adăugați acest nou punct de date. Deci, puteți introduce o nouă coloană în datele sursă și puteți calcula marja de profit în ea. După ce faceți acest lucru, trebuie să actualizați datele sursă ale tabelului pivot pentru a obține această nouă coloană ca parte a acesteia.
   • Deși această metodă este o posibilitate, ar trebui să reveniți manual la setul de date și să faceți calculele. De exemplu, poate fi necesar să adăugați o altă coloană pentru a calcula vânzarea medie pe unitate (Vânzări / Cantitate). Din nou, va trebui să adăugați această coloană la datele sursă și apoi să actualizați tabelul pivot.
   • Această metodă umflă și tabelul pivot pe măsură ce îi adăugați date noi.
2. Adăugați calcule în afara tabelului pivot. Aceasta poate fi o opțiune dacă structura tabelului pivot este puțin probabil să se schimbe. Dar dacă modificați tabelul pivot, este posibil ca calculul să nu se actualizeze corespunzător și să vă dea rezultate sau erori greșite. Așa cum se arată mai jos, am calculat marja de profit atunci când erau retaileri pe rând. Dar când l-am schimbat din clienți în regiuni, formula a dat o eroare.
3. Utilizarea unui câmp calculat în tabelul pivot. Acesta este cel mai eficient mod pentru a utiliza datele existente din tabelul pivot și pentru a calcula valoarea dorită. Luați în considerare Câmpul calculat ca o coloană virtuală pe care ați adăugat-o folosind coloanele existente din tabelul pivot. Există o mulțime de avantaje ale utilizării unui câmp calculat în tabelul pivot (așa cum vom vedea într-un minut):
   • Nu vă cere să gestionați formule sau să actualizați datele sursă.
   • Este scalabil, deoarece va ține cont automat de datele noi pe care le puteți adăuga în tabelul pivot. După ce adăugați un câmp de calcul, îl puteți folosi ca orice alt câmp din tabelul pivot.
   • Este ușor de actualizat și gestionat. De exemplu, dacă valorile se modifică sau trebuie să modificați calculul, puteți face acest lucru cu ușurință din Tabelul pivot în sine.

Adăugarea unui câmp calculat la tabelul pivot

Să vedem cum să adăugăm un câmp calculat în tabelul pivot într-un tabel pivot existent.

Să presupunem că aveți un tabel pivot așa cum se arată mai jos și doriți să calculați marja de profit pentru fiecare comerciant cu amănuntul:

Iată pașii pentru a adăuga un câmp calculat în tabelul pivot:

• Selectați orice celulă din tabelul pivot.
• Accesați Instrumente din tabelul pivot -> Analizați -> Calcule -> Câmpuri, articole și seturi.
• Din meniul derulant, selectați Câmp calculat.
• În caseta de dialog Inserare fișier calculat:
  • Dă-i un nume introducându-l în câmpul Nume.
  • În câmpul Formula, creați formula dorită pentru câmpul calculat. Rețineți că puteți alege dintre numele câmpurilor enumerate sub acesta. În acest caz, formula este „= Profit / Vânzări”. Puteți introduce manual numele câmpurilor sau puteți face dublu clic pe numele câmpului listat în caseta Câmpuri.
• Faceți clic pe Adăugare și închideți caseta de dialog.

De îndată ce adăugați câmpul calculat, acesta va apărea ca unul dintre câmpurile din lista Câmpuri ale tabelului pivot.

Acum puteți utiliza acest câmp calculat ca orice alt câmp al tabelului pivot (rețineți că nu puteți utiliza câmpul calculat al tabelului pivot ca filtru de raport sau feliat).

După cum am menționat anterior, avantajul utilizării unui câmp calculat pentru tabelul pivot este că puteți schimba structura tabelului pivot și acesta se va regla automat.

De exemplu, dacă trag și plasez regiunea în zona rândurilor, veți obține rezultatul așa cum se arată mai jos, unde valoarea Marjei de profit este raportată atât pentru comercianții cu amănuntul, cât și pentru regiune.

În exemplul de mai sus, am folosit o formulă simplă (= Profit / Vânzări) pentru a insera un câmp calculat. Cu toate acestea, puteți utiliza și câteva formule avansate.

Înainte de a vă arăta un exemplu de utilizare a unei formule avansate pentru a crea un câmp de calcul al tabelului pivot, iată câteva lucruri pe care trebuie să le cunoașteți:

• NU PUTEȚI folosi referințe sau intervale denumite în timp ce creați un câmp calculat pentru tabelul pivot. Asta ar exclude o mulțime de formule precum VLOOKUP, INDEX, OFFSET și așa mai departe. Cu toate acestea, puteți utiliza formule care pot funcționa fără referințe (cum ar fi SUM, IF, COUNT și așa mai departe …).
• Puteți utiliza o constantă în formulă. De exemplu, dacă doriți să cunoașteți vânzările prognozate unde se estimează că va crește cu 10%, puteți utiliza formula = Vânzări * 1.1 (unde 1.1 este constantă).
• Ordinea de prioritate este urmată în formula care face câmpul calculat. Ca cea mai bună practică, utilizați paranteze pentru a vă asigura că nu trebuie să vă amintiți ordinea de prioritate.

Acum, să vedem un exemplu de utilizare a unei formule avansate pentru a crea un câmp calculat.

Să presupunem că aveți setul de date așa cum se arată mai jos și că trebuie să afișați valoarea estimată a vânzărilor în Tabelul pivot.

Pentru valoarea prognozată, trebuie să utilizați o creștere a vânzărilor cu 5% pentru comercianții cu amănuntul mari (vânzări peste 3 milioane) și o creștere cu 10% pentru vânzătorii cu amănuntul mici și mijlocii (vânzări sub 3 milioane).

Notă: numerele de vânzări de aici sunt false și au fost utilizate pentru a ilustra exemplele din acest tutorial.

Iată cum puteți face acest lucru:

• Selectați orice celulă din tabelul pivot.
• Accesați Instrumente din tabelul pivot -> Analizați -> Calcule -> Câmpuri, articole și seturi.
• Din meniul derulant, selectați Câmp calculat.
• În caseta de dialog Inserare fișier calculat:
  • Dă-i un nume introducându-l în câmpul Nume.
  • În câmpul Formula, utilizați următoarea formulă: = IF (Regiune = ”Sud”, Vânzări * 1.05, Vânzări * 1.1)
• Faceți clic pe Adăugare și închideți caseta de dialog.

Aceasta adaugă o nouă coloană în tabelul pivot cu valoarea estimată a vânzărilor.

Faceți clic aici pentru a descărca setul de date.

O problemă cu câmpurile calculate din tabelul pivot

Câmpul calculat este o caracteristică uimitoare care îmbunătățește cu adevărat valoarea tabelului pivot cu calcule de câmp, păstrând totuși totul scalabil și gestionabil.

Cu toate acestea, există o problemă cu câmpurile calculate din tabelul pivot pe care trebuie să o cunoașteți înainte de ao utiliza.

Să presupunem că am un tabel pivot așa cum se arată mai jos, unde am folosit câmpul calculat pentru a obține numerele de vânzări prognozate.

Rețineți că totalul subtotal și total nu sunt corecte.

Deși acestea ar trebui să adauge valoarea estimată individuală a vânzărilor pentru fiecare comerciant cu amănuntul, în realitate, aceasta urmează aceeași formulă de câmp calculată pe care am creat-o.

Deci, pentru South Total, în timp ce valoarea ar trebui să fie de 22.824.000, South Total o raportează greșit ca fiind 22.287.000. Acest lucru se întâmplă deoarece folosește formula 21,225,800 * 1,05 pentru a obține valoarea.

Din păcate, nu există nicio modalitate de a corecta acest lucru.

Cel mai bun mod de a rezolva acest lucru ar fi să eliminați subtotalurile și totalele mari din tabelul pivot.

De asemenea, puteți parcurge câteva soluții inovatoare pe care Debra le-a demonstrat pentru a rezolva această problemă.

Cum se modifică sau se șterge un câmp calculat în tabelul pivot?

După ce ați creat un câmp calculat pentru tabelul pivot, puteți modifica formula sau o puteți șterge urmând pașii următori:

• Selectați orice celulă din tabelul pivot.
• Accesați Instrumente din tabelul pivot -> Analizați -> Calcule -> Câmpuri, articole și seturi.
• Din meniul derulant, selectați Câmp calculat.
• În câmpul Nume, faceți clic pe săgeata drop-down (săgeată mică în jos la capătul câmpului).
• Din listă, selectați câmpul calculat pe care doriți să îl ștergeți sau să îl modificați.
• Schimbați formula în cazul în care doriți să o modificați sau faceți clic pe Ștergeți în cazul în care doriți să o ștergeți.

Cum se obține o listă a tuturor formulelor de câmp calculate?

Dacă creați o mulțime de câmpuri din tabelul pivot calculat, nu vă faceți griji cu privire la urmărirea formulei utilizate în fiecare dintre acestea.

Excel vă permite să creați rapid o listă cu toate formulele utilizate în crearea câmpurilor calculate.

Iată pașii pentru a obține rapid lista tuturor formulelor câmpurilor calculate:

• Selectați orice celulă din tabelul pivot.
• Accesați Instrumente pentru tabelul pivot -> Analizați -> Câmpuri, articole și seturi -> Formule de listă.

De îndată ce faceți clic pe Listă formule, Excel va introduce automat o nouă foaie de lucru care va conține detaliile tuturor câmpurilor / articolelor calculate pe care le-ați utilizat în tabelul pivot.

Acesta poate fi un instrument cu adevărat util dacă trebuie să trimiteți munca dvs. clientului sau să o împărtășiți echipei dvs.

Puteți găsi, de asemenea, utile următoarele tutoriale din tabelul pivot:

• Pregătirea datelor sursă pentru tabelul pivot.
• Utilizarea secționatoarelor în tabelul pivot Excel: un ghid pentru începători.
• Cum să grupați datele în tabelele pivot în Excel.
• Cum să grupați numerele în tabelul pivot în Excel.
• Cum se filtrează datele într-un tabel pivot în Excel.
• Cum se înlocuiește celulele goale cu zero în tabelele pivot Excel.
• Cum se aplică formatarea condiționată într-un tabel pivot în Excel.
• Pivot Cache în Excel - Ce este și cum se folosește cel mai bine?