Să presupunem că avem un tabel pivot construit cu rezultatele analizei vânzărilor pe luni pentru diferite orașe (dacă este necesar, citiți acest articol pentru a înțelege cum să le creați în general sau să vă reîmprospătați memoria):
Dorim să-i schimbăm ușor aspectul, astfel încât să afișeze datele de care aveți nevoie mai clar și nu doar să aruncăm o grămadă de numere pe ecran. Ce se poate face pentru asta?
Alte funcții de calcul în locul sumei banale
Dacă faceți clic dreapta pe câmpul calculat din zona de date și selectați comanda din meniul contextual Opțiuni de câmp (Setări de câmp) sau în versiunea Excel 2007 – Opțiuni pentru câmpul de valoare (Setări câmpuri de valoare), apoi se va deschide o fereastră foarte utilă, cu ajutorul căreia puteți seta o grămadă de setări interesante:
În special, puteți schimba cu ușurință funcția de calcul a câmpului la medie, minim, maxim etc. De exemplu, dacă modificăm suma la cantitatea din tabelul nostru pivot, atunci vom vedea nu veniturile totale, ci numărul de tranzacții pentru fiecare produs:
În mod implicit, Excel selectează întotdeauna automat sumarea pentru datele numerice. (Sumă), iar pentru cele nenumerice (chiar dacă dintr-o mie de celule cu numere există cel puțin una goală sau cu text sau cu un număr în format text) – o funcție de numărare a numărului de valori (Numara).
Dacă doriți să vedeți într-un singur tabel pivot deodată media, suma și cantitatea, adică mai multe funcții de calcul pentru același câmp, atunci nu ezitați să aruncați mouse-ul în zona de date a câmpului de care aveți nevoie de mai multe ori la rând pentru a obține ceva asemănător:
...și apoi setați diferite funcții pentru fiecare dintre câmpuri făcând clic pe ele pe rând cu mouse-ul și alegând comanda Opțiuni de câmp (Setări de câmp)pentru a ajunge cu ceea ce vrei:
Dacă în aceeași fereastră Opțiuni de câmp faceți clic pe buton În plus, (Opțiuni) sau accesați fila Calcule suplimentare (în Excel 2007-2010), atunci lista derulantă va deveni disponibilă Calcule suplimentare (Afișați datele ca):
În această listă, de exemplu, puteți selecta opțiuni Procentul din valoarea liniei (% din rând), Procent din total pe coloană (% din coloană) or Ponderea din total (% din total)pentru a calcula automat procente pentru fiecare produs sau oraș. Așa va arăta, de exemplu, tabelul nostru pivot cu funcția activată Procent din total pe coloană:
Dinamica vânzărilor
Dacă se află în lista derulantă Calcule suplimentare (Afișați datele ca) alege opțiunea distincție (Diferență), iar în ferestrele de jos Câmp (câmp de bază) и Element (Articol de bază) selecta Lună и înapoi (în versiunea nativă engleză, în loc de acest cuvânt ciudat, a existat un cuvânt mai ușor de înțeles Anterior, acestea. anterior):
… apoi obținem un tabel pivot care arată diferențele de vânzări ale fiecărei luni următoare față de cea anterioară, adică – dinamica vânzărilor:
Și dacă înlocuiești distincție (Diferență) on Dată diferența (% din diferență) și adăugați formatarea condițională pentru a evidenția valorile negative în roșu, obținem același lucru, dar nu în ruble, ci ca procent:
PS
În Microsoft Excel 2010, toate setările de calcul de mai sus pot fi făcute și mai ușor - făcând clic dreapta pe orice câmp și selectând comenzi din meniul contextual Totaluri pentru (Rezumați valorile după):
… și Calcule suplimentare (Afișați datele ca):
De asemenea, în versiunea Excel 2010, la acest set au fost adăugate câteva funcții noi:
- % din total după rândul părinte (coloană) – vă permite să calculați cota relativă la subtotalul pentru un rând sau coloană:
În versiunile anterioare, puteți calcula doar proporția față de totalul general.
- % din suma cumulata – funcționează similar cu funcția sumă cumulativă, dar afișează rezultatul sub formă de fracție, adică în procente. Este convenabil să se calculeze, de exemplu, procentul din execuția planului sau bugetului:
- Sortarea de la cel mai mic la cel mai mare și invers – un nume ușor ciudat pentru funcția de clasare (RANK), care calculează numărul ordinal (poziția) unui element din lista generală de valori. De exemplu, cu ajutorul acestuia, este convenabil să clasați managerii în funcție de veniturile lor totale, determinând cine se află în ce loc în clasamentul general:
- Ce sunt tabelele pivot și cum să le construiți
- Gruparea numerelor și a datelor cu pasul dorit în tabelele pivot
- Crearea unui raport PivotTable pe mai multe intervale de date sursă