Formula tabelului pivot în Excel (cuprins)
- Formula tabelului pivot în Excel
- Câmp personalizat pentru calcularea valorii profitului
- Formula avansată în câmpul calculat
Formula tabelului pivot în Excel
Pivot Table este un instrument care ne permite să analizăm intervale mari de date. Putem analiza, interpreta și face multe alte lucruri fără să ne rupem mare parte din cap și transpirație. Poate oferi aproape tot ceea ce există în datele sursă.
Dacă o parte din informații există în datele sursă, este posibil să avem nevoie să calculăm acest lucru pentru noi înșine. De exemplu, dacă avem o valoare totală a vânzărilor și costul total, este posibil să fie necesar să calculăm pe cont propriu profitul sau pierderea.
Nu trebuie să facem acest lucru în sursă, dar putem face acest lucru în interiorul tabelului pivot în sine, acestea sunt numite câmpuri calculate în interiorul tabelului pivot. Putem folosi formule personalizate pentru a face ca datele să spună mai multe povești din date. Campurile calculate ne permite să construim o nouă coloană calculată care nu există în sursa de date reală.
În acest articol, vom demonstra modalitățile de utilizare a câmpurilor calculate cu tabel rotativ pentru a construi coloane noi pe baza cerinței noastre.
Câmp personalizat pentru calcularea valorii profitului
Puteți descărca acest șablon Formula tabelului pivot aici - Modelul Excel Formula tabelului rotativAcesta este cel mai des utilizat câmp calculat în tabelul pivot. Uitați-vă la datele de mai jos, am denumirea țării, numele produsului, unitățile vândute, prețul unitar, vânzările brute, COGS (costul mărfurilor vândute), data și coloana anului.
Permiteți-mi să aplic tabelul pivot pentru a găsi vânzările totale și costul total pentru fiecare țară. Mai jos este tabelul pivot pentru datele de mai sus.
Problema este că nu am o coloană de profit în datele sursă. Trebuie să aflu procentul de profit și profit pentru fiecare țară. Putem adăuga aceste două coloane în tabelul pivot propriu-zis.
Pasul 1: Selectați o celulă din tabelul pivot. Accesați fila Analiza din panglică și selectați Câmpuri, elemente și seturi. În cadrul acestui câmp selectați Calculat.
Pasul 2: În caseta de dialog de mai jos, dați un nume noului câmp calculat.
Pasul 3: În secțiunea Formula aplicați formula pentru a găsi Profitul. Formula pentru a găsi profitul este Vânzările brute - COGS.
Accesați bara de formule> Selectați Vânzări brute din câmpul de mai jos și faceți dublu clic pe ea, va apărea în bara Formula.
Acum tastați simbolul minus (-) și selectați COGS> Faceți dublu clic.
Pasul 4: Faceți clic pe ADD și OK pentru a completa formula.
Pasul 5: Acum avem coloana noastră de PROFIT TOTAL în tabelul pivot.
Acest câmp calculat este flexibil, nu se rezumă doar la analiza țării, dar putem folosi acest lucru pentru toate tipurile de analiză. Dacă vreau să văd analiza țării și produsul înțelept, trebuie doar să trageți și să plasați coloana de produs în câmpul ROW, va arăta profitul pentru fiecare produs din fiecare țară.
Pasul 6: Acum trebuie să calculăm procentul de profit. Formula de calculare a procentului de profit este profitul total / vânzările brute.
Accesați Analiza și selectați din nou Câmpul calculat în Câmpuri, elemente și seturi.
Pasul 7: Acum trebuie să vedem câmpul nou inserat Profitul total Profit din lista Câmpuri. Inserați acest câmp în formulă.
Pasul 8: Introduceți simbolul divizorului (/) și introduceți câmpul de vânzări brute.
Pasul 9: Denumiți acest câmp calculat ca procent de profit.
Pasul 10: Faceți clic pe ADD și OK pentru a completa formula. Avem procentul de profit ca nouă coloană.
Formula avansată în câmpul calculat
Orice am arătat acum sunt lucrurile de bază ale câmpului calculat. În acest exemplu, vă voi arăta formulele avansate din câmpurile calculate ale tabelului pivot. Acum vreau să calculez valoarea stimulentului pe baza procentului de profit.
Dacă profitul% este> 15% stimulent ar trebui să fie 6% din profitul total.
Dacă profitul% este> 10% stimulent ar trebui să fie 5% din profitul total.
Dacă profitul% este <10% stimulent ar trebui să fie 3% din profitul total.
Pasul 1: Accesați câmpul calculat și deschideți caseta de dialog de mai jos. Dați numele ca sumă stimulativă.
Pasul 2: Acum voi folosi condiția IF pentru a calcula valoarea stimulentului. Aplicați formulele de mai jos, așa cum se arată în imagine.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Pasul 3: Faceți clic pe ADD & OK pentru a finaliza. Acum avem o coloană cu suma stimulativă.
Limitarea câmpului calculat
Am văzut minunea câmpurilor calculate, dar are și unele limitări. Acum uitați-vă la imaginea de mai jos, dacă vreau să văd care este suma de stimulare a produsului înțelept, vom avea greșit SUB TOTAL & GRAND TOTAL DE SUMĂ INCENTIVĂ.
Așa că aveți grijă în timp ce afișați totalul câmpurilor calculate. Vă va arăta sumele greșite.
Obțineți lista tuturor formulelor de câmp calculate
Dacă nu știți câte formule există în câmpul calculat al tabelului pivot, puteți obține rezumatul tuturor acestora într-o foaie de lucru separată.
Accesați Analiza> Câmpuri, elemente și seturi -> Formule de listă.
Acesta vă va oferi un rezumat al tuturor formulelor dintr-o foaie de lucru nouă.
Lucruri de reținut despre formula de tabel rotativ în Excel
- Putem șterge, modifica toate câmpurile calculate.
- Nu putem folosi formule precum VLOOKUP, SUMIF și multe alte formule implicate în câmpuri calculate, adică toate formulele care necesită un interval nu pot fi utilizate.
Articole recomandate
Acesta a fost un ghid pentru Formula tabelului pivot în Excel. Aici am discutat Pașii pentru a utiliza Formula tabelului pivot în Excel împreună cu exemple și șablonul excel descărcabil. De asemenea, puteți analiza aceste funcții utile în excel -
- Tutoriale pe diagramă rotativă în Excel
- Crearea tabelului pivot în Excel
- Tutorial VLOOKUP în Excel
- Excel Creare bază de date