Kako dodati i koristiti izračunato polje Excel zaokretne tablice

Često, nakon što stvorite zaokretnu tablicu, postoji potreba da proširite svoju analizu i uključite više podataka/izračuna u njen dio.

Ako vam je potrebna nova podatkovna točka koja se može dobiti korištenjem postojećih podatkovnih točaka u zaokretnoj tablici, ne morate se vratiti i dodati je u izvorne podatke. Umjesto toga, možete koristiti a Izračunato polje zaokretne tablice uraditi ovo.

Preuzmite skup podataka i slijedite ga.

Što je izračunato polje zaokretne tablice?

Počnimo s osnovnim primjerom zaokretne tablice.

Pretpostavimo da imate skup podataka o trgovcima na malo i stvorite zaokretnu tablicu kao što je prikazano u nastavku:

Gornja zaokretna tablica sažima vrijednosti prodaje i dobiti za trgovce na malo.

Što ako želite saznati i kolika je marža profita ovih trgovaca (gdje je marža profita "dobit" podijeljena s "prodaja").

Postoji nekoliko načina za to:

1. Vratite se na izvorni skup podataka i dodajte ovu novu podatkovnu točku. Tako možete umetnuti novi stupac u izvorne podatke i u njemu izračunati maržu dobiti. Nakon što to učinite, morate ažurirati izvorne podatke zaokretne tablice kako bi ovaj novi stupac postao njezin dio.
   • Iako je ova metoda moguća, morat ćete se ručno vratiti na skup podataka i izvršiti izračune. Na primjer, možda ćete morati dodati drugi stupac za izračun prosječne prodaje po jedinici (prodaja/količina). Opet ćete morati dodati ovaj stupac izvornim podacima, a zatim ažurirati zaokretnu tablicu.
   • Ova metoda također povećava vašu zaokretnu tablicu dok joj dodajete nove podatke.
2. Dodajte izračune izvan zaokretne tablice. To može biti opcija ako se vjerojatno neće promijeniti vaša struktura zaokretne tablice. No ako promijenite zaokretnu tablicu, izračun se možda neće ažurirati u skladu s tim i može vam dati pogrešne rezultate ili pogreške. Kao što je dolje prikazano, izračunao sam profitnu maržu kada su u nizu bili trgovci. Ali kad sam ga promijenio iz kupaca u regije, formula je dala pogrešku.
3. Korištenje izračunatog polja zaokretne tablice. Ovo je najučinkovitiji način koristiti postojeće podatke zaokretne tablice i izračunati željenu metriku. Izračunajte polje kao virtualni stupac koji ste dodali pomoću postojećih stupaca iz zaokretne tablice. Korištenje izračunatog polja zaokretne tablice (kao što ćemo vidjeti za minutu) ima mnogo prednosti:
   • Ne zahtijeva rukovanje formulama ili ažuriranje izvornih podataka.
   • On je skalabilan jer će automatski uzeti u obzir sve nove podatke koje možete dodati u zaokretnu tablicu. Nakon što dodate polje za izračunavanje, možete ga koristiti kao i svako drugo polje u zaokretnoj tablici.
   • Lako se ažurira i upravlja. Na primjer, ako se mjerni podaci promijene ili trebate promijeniti izračun, to možete lako učiniti iz same zaokretne tablice.

Dodavanje izračunatog polja u zaokretnu tablicu

Pogledajmo kako dodati izračunato polje zaokretne tablice u postojeću zaokretnu tablicu.

Pretpostavimo da imate zaokretnu tablicu kao što je prikazano u nastavku i želite izračunati maržu dobiti za svakog trgovca:

Evo koraka za dodavanje izračunatog polja zaokretne tablice:

• Odaberite bilo koju ćeliju u zaokretnoj tablici.
• Idite na Alati zaokretne tablice -> Analiza -> Izračuni -> Polja, stavke i skupovi.
• Na padajućem izborniku odaberite Izračunato polje.
• U dijaloškom okviru Umetni izračunate datoteke:
  • Dajte mu ime unošenjem u polje Ime.
  • U polju Formula stvorite formulu koju želite za izračunato polje. Imajte na umu da možete birati između naziva polja navedenih ispod njega. U ovom slučaju, formula je '= Dobit/ prodaja'. Možete ručno unijeti nazive polja ili dvaput kliknuti na naziv polja naveden u polju Polja.
• Pritisnite Dodaj i zatvorite dijaloški okvir.

Čim dodate Izračunato polje, ono će se pojaviti kao jedno od polja na popisu polja zaokretne tablice.

Sada možete koristiti ovo izračunato polje kao bilo koje drugo polje zaokretne tablice (imajte na umu da ne možete koristiti izračunato polje zaokretne tablice kao filter izvješća ili rezač).

Kao što sam već spomenuo, prednost korištenja izračunatog polja zaokretne tablice je u tome što možete promijeniti strukturu zaokretne tablice i ona će se automatski prilagoditi.

Na primjer, ako povučem i ispustim područje u području redova, dobit ćete rezultat kao što je prikazano u nastavku, gdje se vrijednost profitne marže prijavljuje za trgovce na malo kao i za regiju.

U gornjem primjeru koristio sam jednostavnu formulu (= dobit/prodaja) za umetanje izračunatog polja. Međutim, možete koristiti i neke napredne formule.

Prije nego što vam pokažem primjer korištenja napredne formule za stvaranje polja za izračunavanje zaokretne tablice, evo nekoliko stvari koje morate znati:

• NE MOŽETE koristiti reference ili imenovane raspone prilikom stvaranja izračunatog polja zaokretne tablice. To bi isključilo mnogo formula poput VLOOKUP -a, INDEX -a, OFFSET -a itd. Međutim, možete koristiti formule koje mogu raditi bez referenci (kao što su SUM, IF, COUNT i tako dalje …).
• U formuli možete koristiti konstantu. Na primjer, ako želite znati predviđenu prodaju za koju se predviđa rast od 10%, možete upotrijebiti formulu = Prodaja*1,1 (gdje je 1,1 konstantna).
• Redoslijed prvenstva slijedi se u formuli koja čini izračunato polje. Kao najbolju praksu upotrijebite zagrade kako biste bili sigurni da ne morate pamtiti redoslijed prvenstva.

Pogledajmo sada primjer korištenja napredne formule za stvaranje izračunatog polja.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i morate prikazati predviđenu vrijednost prodaje u zaokretnoj tablici.

Za predviđenu vrijednost morate koristiti povećanje prodaje od 5% za velike maloprodaje (prodaja iznad 3 milijuna) i povećanje prodaje od 10% za male i srednje trgovce (prodaja ispod 3 milijuna).

Napomena: Ovdje su prodajni brojevi lažni i korišteni su za ilustraciju primjera u ovom vodiču.

Evo kako to učiniti:

• Odaberite bilo koju ćeliju u zaokretnoj tablici.
• Idite na Alati zaokretne tablice -> Analiza -> Izračuni -> Polja, stavke i skupovi.
• Na padajućem izborniku odaberite Izračunato polje.
• U dijaloškom okviru Umetni izračunate datoteke:
  • Dajte mu ime unošenjem u polje Ime.
  • U polju Formula upotrijebite sljedeću formulu: = IF (Regija = "Jug", Prodaja *1,05, Prodaja *1,1)
• Pritisnite Dodaj i zatvorite dijaloški okvir.

Time se u zaokretnu tablicu dodaje novi stupac s vrijednošću predviđanja prodaje.

Kliknite ovdje za preuzimanje skupa podataka.

Problem s poljima izračunatim zaokretnom tablicom

Izračunato polje nevjerojatna je značajka koja doista povećava vrijednost vaše zaokretne tablice izračunima polja, a istovremeno održava sve skalabilnim i upravljivim.

Međutim, postoji problem s izračunskim poljima zaokretne tablice koje morate znati prije nego što ga upotrebite.

Pretpostavimo da imam zaokretnu tablicu kao što je prikazano u nastavku gdje sam koristio izračunato polje za dobivanje prognoziranih prodajnih brojeva.

Imajte na umu da međuzbir i ukupni zbrojevi nisu točni.

Iako bi trebale dodati vrijednost prognoze pojedinačne prodaje za svakog trgovca, u stvarnosti se slijedi ista izračunata formula polja koju smo stvorili.

Dakle, za South Total, dok bi vrijednost trebala biti 22.824.000, South Total pogrešno izvještava o 22.287.000. To se događa jer koristi formulu 21,225,800*1,05 za dobivanje vrijednosti.

Nažalost, ne možete to ispraviti.

Najbolji način da se to riješi bilo bi uklanjanje međuzbrojeva i ukupnih zbrojeva iz zaokretne tablice.

Također možete proći kroz neka inovativna rješenja koja je Debra pokazala da rješava ovaj problem.

Kako izmijeniti ili izbrisati izračunato polje zaokretne tablice?

Nakon što ste stvorili izračunato polje zaokretne tablice, formulu možete izmijeniti ili izbrisati pomoću sljedećih koraka:

• Odaberite bilo koju ćeliju u zaokretnoj tablici.
• Idite na Alati zaokretne tablice -> Analiza -> Izračuni -> Polja, stavke i skupovi.
• S padajućeg izbornika odaberite Izračunato polje.
• U polju Ime kliknite strelicu padajućeg izbornika (mala strelica prema dolje na kraju polja).
• S popisa odaberite izračunato polje koje želite izbrisati ili izmijeniti.
• Promijenite formulu u slučaju da je želite izmijeniti ili kliknite Izbriši u slučaju da je želite izbrisati.

Kako dobiti popis svih izračunatih formula polja?

Ako stvorite puno polja Izračunata zaokretna tablica, ne brinite o praćenju formule koja se koristi u svakom od njih.

Excel vam omogućuje brzo stvaranje popisa svih formula korištenih u stvaranju izračunatih polja.

Evo koraka za brzo dobivanje popisa formula svih izračunatih polja:

• Odaberite bilo koju ćeliju u zaokretnoj tablici.
• Idite na Alati zaokretne tablice -> Analiza -> Polja, stavke i skupovi -> Formule popisa.

Čim kliknete na Formule popisa, Excel bi automatski umetnuo novi radni list koji će sadržavati detalje svih izračunatih polja/stavki koje ste koristili u zaokretnoj tablici.

Ovo može biti jako koristan alat ako svoj rad morate poslati klijentu ili ga podijeliti sa svojim timom.

Sljedeći vodiči za zaokretnu tablicu mogli bi vam također biti korisni:

• Priprema izvornih podataka za zaokretnu tablicu.
• Korištenje rezača u zaokretnoj tablici programa Excel: Vodič za početnike.
• Kako grupirati datume u zaokretnim tablicama u Excelu.
• Kako grupirati brojeve u zaokretnoj tablici u Excelu.
• Kako filtrirati podatke u zaokretnoj tablici u Excelu.
• Kako zamijeniti prazne ćelije nulama u zaokretnim tablicama programa Excel.
• Kako primijeniti uvjetno oblikovanje u zaokretnoj tablici u Excelu.
• Zaokretna predmemorija u Excelu - što je to i kako je najbolje koristiti?