Kako dodati in uporabiti izračunana polja vrtilne tabele Excel

Ko ustvarite vrtilno tabelo, morate običajno razširiti analizo in vanjo vključiti več podatkov/izračunov.

Če potrebujete novo podatkovno točko, ki jo je mogoče pridobiti z obstoječo podatkovno točko v vrtilni tabeli, se vam ni treba vrniti in je dodati izvornim podatkom.Namesto tega lahko uporabiteIzračunana polja vrtilne tabelestoriti to.

Kaj je izračunano polje vrtilne tabele?

Začnimo z osnovnim primerom vrtilne tabele.

Recimo, da imate nabor podatkov trgovcev na drobno in ustvarite vrtilno tabelo, kot je ta:

Zgornja vrtilna tabela povzema vrednosti prodaje in dobička prodajalca.

Kaj pa, če želite tudi vedeti, kakšne so stopnje dobička teh trgovcev na drobno (marža dobička je "dobiček", deljen s "prodajo")?

To lahko storite na več načinov:

1. Vrnite se na izvirni nabor podatkov in dodajte to novo podatkovno točko.Tako lahko v izvorne podatke vstavite nov stolpec in v njem izračunate stopnjo dobička.Ko je to storjeno, morate posodobiti izvorne podatke vrtilne tabele, da bo ta nov stolpec postal del nje.
   • Medtem ko ta pristop deluje, se morate ročno vrniti na nabor podatkov in opraviti izračune.Na primer, morda boste želeli dodati še en stolpec za izračun povprečne prodaje na enoto (prodaja/količina).Ponovno morate ta stolpec dodati izvornim podatkom in nato posodobiti vrtilno tabelo.
   • Ta metoda tudi napihne vašo vrtilno tabelo, ko ji dodate nove podatke.
2. Dodajte izračune zunaj vrtilnih tabel.Izberite to možnost, če se struktura vrtilne tabele verjetno ne bo spremenila.Če pa spremenite vrtilno tabelo, se izračuni morda ne bodo ustrezno posodobili in vam lahko dajo napačne rezultate ali napake.Kot je prikazano spodaj, sem izračunal stopnjo dobička, ko so v vrstici trgovci na drobno.Ko pa ga spremenim od stranke do območja, gre formula narobe.
3. Izračunajte polja s pomočjo vrtilnih tabel.To jeUporabite obstoječe podatke vrtilne tabele in izračunajtenajbolj učinkovit način.Pomislite na izračunana polja kot navidezne stolpce, ki jih dodate z uporabo obstoječih stolpcev v vrtilni tabeli.Uporaba izračunanih polj vrtilne tabele ima številne prednosti (kot bomo videli pozneje):
   • Ne zahteva, da delate s formulami ali posodabljate izvorne podatke.
   • Razširljiv je, ker samodejno upošteva vse nove podatke, ki jih lahko dodate v vrtilno tabelo.Ko dodate izračunano polje, ga lahko uporabite kot katero koli drugo polje v vrtilni tabeli.
   • Enostavno ga je posodobiti in upravljati.Na primer, če se meritev spremeni ali morate spremeniti izračun, lahko to preprosto storite iz same vrtilne tabele.

Dodajte izračunana polja v vrtilno tabelo

Poglejmo, kako dodati izračunano polje vrtilne tabele v obstoječo vrtilno tabelo.

Recimo, da imate vrtilno tabelo, ki izgleda takole, in želite izračunati stopnjo dobička za vsakega trgovca na drobno:

Tu so koraki za dodajanje izračunanega polja vrtilne tabele:

Ko dodate izračunano polje, se prikaže kot eno od polj na seznamu Polja vrtilne tabele.

To izračunano polje lahko zdaj uporabite kot katero koli drugo polje vrtilne tabele (upoštevajte, da izračunanega polja vrtilne tabele ne morete uporabiti kot filter poročila ali rezalnika).

Kot sem že omenil, je prednost uporabe izračunanega polja vrtilne tabele v tem, da lahko spremenite strukturo vrtilne tabele in se samodejno prilagodi.

Če na primer povlečem in spustim regijo v območje vrstice, boste dobili naslednje rezultate, kjer so navedene vrednosti stopnje dobička za trgovca na drobno in regijo.

 

V zgornjem primeru sem uporabil preprosto formulo (=Dobiček/Prodaja), da sem vstavil izračunano polje.Lahko pa uporabite tudi nekatere napredne formule.

Preden vam pokažem primer uporabe napredne formule za ustvarjanje izračunanega polja vrtilne tabele, morate vedeti nekaj stvari:

• Pri ustvarjanju izračunanih polj vrtilne tabele ne morete uporabiti referenc ali poimenovanih obsegov.To bo izključilo številne formule, kot so VLOOKUP, INDEX, OFFSET itd.Lahko pa uporabite formule, ki delujejo brez referenc (npr. SUM, IF, COUNT itd...).
• V formulah lahko uporabite konstante.Če bi na primer želeli izvedeti, da se bo pričakovana rast prodaje povečala za 10%, bi uporabili formulo =Prodaja*1.1 (kjer je 1.1 konstanta).
• Sledite prednostnemu vrstnemu redu v formulah, ki generirajo izračunana polja.Kot najboljšo prakso uporabite oklepaje, da zagotovite, da se vam ni treba spomniti prednosti.

Zdaj pa si poglejmo primer ustvarjanja izračunanega polja z uporabo napredne formule.

Recimo, da imate spodnji nabor podatkov in morate napovedano prodajo prikazati v vrtilni tabeli.

Za napovedane vrednosti bi uporabili 300-odstotno povečanje prodaje za velike trgovce na drobno (več kot 5 milijone prodaj) in 300-odstotno povečanje prodaje za male in srednje trgovce (manj kot 10 milijone prodaje).

Opomba: prodajne številke tukaj so lažne in so bile uporabljene za ponazoritev primerov v tej vadnici.

To storite tako:

To bo vrtilni tabeli dodalo nov stolpec z vrednostmi napovedi prodaje.

Problem izračunanega polja vrtilne tabele

Izračunana polja so neverjetna funkcija, ki resnično poveča vrednost vrtilnih tabel z izračuni polj, hkrati pa ohranja vse prilagodljivo in obvladljivo.

Vendar pa obstaja težava z izračunanimi polji vrtilne tabele, ki jih morate razumeti, preden jih uporabite.

Recimo, da imam vrtilno tabelo, kot je spodaj, in uporabljam izračunano polje, da dobim napovedane številke prodaje.

Upoštevajte, da so vmesni seštevek in vsota napačna.

Medtem ko bi te morale dodati ločene vrednosti napovedi prodaje za vsakega trgovca na drobno, v resnici sledi isti izračunani formuli polja, ki smo jo ustvarili.

Torej za South Total, medtem ko bi morala biti vrednost 22,824,000, South Total napačno poroča kot 22,287,000.To se zgodi, ker za pridobitev vrednosti uporabi formulo 21,225,800*1.05.

Te težave žal ne morete odpraviti.

Najboljši način za reševanje tega je, da odstranite delne vsote in skupne vsote iz vrtilne tabele.

Lahko tudi mimo Predstavila DebraNekaj ​​inovativnih rešitev za reševanje te težave.

Kako spremeniti ali izbrisati izračunano polje vrtilne tabele?

Ko ustvarite izračunano polje vrtilne tabele, lahko spremenite formulo ali jo izbrišete z naslednjimi koraki:

Kako priti do seznama vseh izračunanih formul polj?

Če ustvarite veliko izračunanih polj vrtilne tabele, vam ni treba skrbeti za sledenje formulam, uporabljenim v vsakem polju.

Excel vam omogoča hitro ustvarjanje seznama vseh formul, ki se uporabljajo za ustvarjanje izračunanih polj.

Tukaj so koraki za hitro pridobivanje seznama vseh izračunanih formul polj:

• Izberite katero koli celico v vrtilni tabeli.
• Pojdite na Orodja vrtilne tabele -> Analiza -> Polja, predmeti in nizi -> Seznam formul.

Ko kliknete formulo seznama, bo Excel samodejno vstavil nov delovni list s podrobnostmi o vseh izračunanih poljih/postavkah, ki ste jih uporabili v vrtilni tabeli.

To je lahko zelo uporabno orodje, če morate delo poslati stranki ali ga deliti s svojo ekipo.