Kako dodati in uporabiti izračunano polje Excel vrtilne tabele

Ko ustvarite vrtilno tabelo, morate pogosto razširiti svojo analizo in v njen del vključiti več podatkov/izračunov.

Če potrebujete novo podatkovno točko, ki jo lahko pridobite z uporabo obstoječih podatkovnih točk v vrtilni tabeli, se vam ni treba vrniti nazaj in jo dodati v izvorne podatke. Namesto tega lahko uporabite datoteko a Izračunano polje vrtilne tabele storiti to.

Prenesite nabor podatkov in sledite.

Kaj je izračunano polje vrtilne tabele?

Začnimo z osnovnim primerom vrtilne tabele.

Recimo, da imate nabor podatkov o prodajalcih na drobno in ustvarite vrtilno tabelo, kot je prikazano spodaj:

Zgornja vrtilna tabela povzema vrednosti prodaje in dobička za trgovce na drobno.

Kaj pa, če želite izvedeti tudi, kakšna je bila stopnja dobička teh trgovcev na drobno (kjer je stopnja dobička »Dobiček«, deljen s »Prodaja«).

Obstaja nekaj načinov za to:

1. Vrnite se na prvotni 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 to storite, morate posodobiti izvorne podatke vrtilne tabele, da bo ta novi stolpec del nje.
   • Čeprav je ta metoda možna, bi se morali ročno vrniti na nabor podatkov in narediti izračune. Morda boste na primer morali dodati še en stolpec za izračun povprečne prodaje na enoto (prodaja/količina). Spet boste morali ta stolpec dodati v izvorne podatke in nato posodobiti vrtilno tabelo.
   • Ta metoda tudi poveča vašo vrtilno tabelo, ko ji dodajate nove podatke.
2. Dodajte izračune zunaj vrtilne tabele. To je lahko možnost, če se struktura vrtilne tabele verjetno ne bo spremenila. Če pa spremenite vrtilno tabelo, se izračun morda ne bo ustrezno posodobil in lahko povzroči napačne rezultate ali napake. Kot je prikazano spodaj, sem izračunal stopnjo dobička, ko so bili v vrsti prodajalci na drobno. Ko pa sem ga spremenil iz strank v regije, je formula dala napako.
3. Uporaba izračunanega polja vrtilne tabele. To je najučinkovitejši način uporabiti obstoječe podatke vrtilne tabele in izračunati želeno meritev. Izračunano polje upoštevajte kot navidezni stolpec, ki ste ga dodali z uporabo obstoječih stolpcev iz vrtilne tabele. Uporaba izračunanega polja vrtilne tabele (kot bomo videli čez minuto) ima veliko prednosti:
   • Ne zahteva, da ravnate s formulami ali posodabljate izvorne podatke.
   • Je razširljiv, saj bo samodejno upošteval vse nove podatke, ki jih lahko dodate v vrtilno tabelo. Ko dodate polje za izračun, ga lahko uporabite kot katero koli drugo polje v vrtilni tabeli.
   • Enostavno ga je posodobiti in upravljati. Na primer, če se meritve spremenijo ali morate spremeniti izračun, lahko to preprosto storite iz same vrtilne tabele.

Dodajanje izračunanega polja v vrtilno tabelo

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

Recimo, da imate vrtilno tabelo, kot je prikazano spodaj, in želite izračunati stopnjo dobička za vsakega trgovca na drobno:

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

• Izberite katero koli celico v vrtilni tabeli.
• Pojdite na Orodja za vrtilno tabelo -> Analiza -> Izračuni -> Polja, postavke in sklopi.
• V spustnem meniju izberite Izračunano polje.
• V pogovornem oknu Vstavi izračunano datoteko:
  • Dajte mu ime tako, da ga vnesete v polje Ime.
  • V polju Formula ustvarite želeno formulo za izračunano polje. Upoštevajte, da lahko izbirate med imeni polj, navedenimi pod njim. V tem primeru je formula "= dobiček/ prodaja". Imena polj lahko vnesete ročno ali dvokliknete ime polja, ki je navedeno v polju Polja.
• Kliknite Dodaj in zaprite pogovorno okno.

Takoj, ko dodate izračunano polje, bo prikazano kot eno od polj na seznamu polj vrtilne tabele.

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

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čju vrstic, boste dobili rezultat, kot je prikazano spodaj, kjer vrednost profitne marže poročajo tako za trgovce na drobno kot za regijo.

V zgornjem primeru sem za vstavitev izračunanega polja uporabil preprosto formulo (= Dobiček/Prodaja). Lahko pa uporabite tudi nekatere napredne formule.

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

• Med ustvarjanjem izračunanega polja vrtilne tabele NE morete uporabljati sklicev ali imenovanih obsegov. To bi izključilo veliko formul, kot so VLOOKUP, INDEX, OFFSET itd. Lahko pa uporabite formule, ki lahko delujejo brez referenc (na primer SUM, IF, COUNT itd.).
• V formuli lahko uporabite konstanto. Na primer, če želite vedeti napovedano prodajo, kjer naj bi se povečala za 10%, lahko uporabite formulo = Prodaja*1,1 (kjer je 1,1 konstantna).
• Vrstni red sledi formuli, ki naredi izračunano polje. Kot najboljšo prakso uporabite oklepaje, da se prepričate, da vam ni treba zapomniti vrstnega reda.

Zdaj pa poglejmo primer uporabe napredne formule za ustvarjanje izračunanega polja.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in morate prikazati napovedano vrednost prodaje v vrtilni tabeli.

Za napovedano vrednost morate uporabiti 5 -odstotno povečanje prodaje za velike trgovce na drobno (prodaja nad 3 milijone) in 10 -odstotno povečanje prodaje za male in srednje trgovce na drobno (prodaja pod 3 milijone).

Opomba: prodajne številke so ponarejene in so bile uporabljene za ponazoritev primerov v tej vadnici.

Evo, kako to storiti:

• Izberite katero koli celico v vrtilni tabeli.
• Pojdite na Orodja za vrtilno tabelo -> Analiza -> Izračuni -> Polja, postavke in sklopi.
• V spustnem meniju izberite Izračunano polje.
• V pogovornem oknu Vstavi izračunano datoteko:
  • Dajte mu ime tako, da ga vnesete v polje Ime.
  • V polju Formula uporabite naslednjo formulo: = IF (Regija = "Jug", Prodaja *1,05, Prodaja *1,1)
• Kliknite Dodaj in zaprite pogovorno okno.

To v vrtilno tabelo doda nov stolpec z vrednostjo napovedi prodaje.

Kliknite tukaj za prenos nabora podatkov.

Težava z izračunanimi polji vrtilne tabele

Izračunano polje je neverjetna funkcija, ki z izračuni polja resnično poveča vrednost vaše vrtilne tabele, hkrati pa ohranja vse prilagodljivo in obvladljivo.

Obstaja pa težava z izračunanimi polji vrtilne tabele, ki jo morate poznati, preden jo uporabite.

Recimo, da imam vrtilno tabelo, kot je prikazano spodaj, kjer sem uporabil izračunano polje za pridobitev predvidenih prodajnih številk.

Upoštevajte, da vmesni in skupni seštevek nista pravilna.

Medtem ko bi te morale dodati vrednost posamezne napovedi prodaje za vsakega trgovca na drobno, v resnici sledi isti formuli izračunanega polja, ki smo jo ustvarili.

Torej za South Total, medtem ko bi morala biti vrednost 22.824.000, South Total napačno poroča o 22.287.000. To se zgodi, ko za izračun vrednosti uporabi formulo 21,225,800*1,05.

Na žalost tega nikakor ne morete popraviti.

Najboljši način za to bi bil odstranitev vmesnih seštevkov in skupnih seštevkov iz vrtilne tabele.

Ogledate si lahko tudi nekaj inovativnih rešitev, ki jih je Debra pokazala za reševanje tega vprašanja.

Kako spremeniti ali izbrisati izračunano polje vrtilne tabele?

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

• Izberite katero koli celico v vrtilni tabeli.
• Pojdite na Orodja za vrtilno tabelo -> Analiza -> Izračuni -> Polja, postavke in sklopi.
• V spustnem meniju izberite Izračunano polje.
• V polju Ime kliknite spustno puščico (majhna puščica navzdol na koncu polja).
• Na seznamu izberite izračunano polje, ki ga želite izbrisati ali spremeniti.
• Spremenite formulo, če jo želite spremeniti, ali kliknite Izbriši, če jo želite izbrisati.

Kako pridobiti seznam vseh izračunanih formul polj?

Če ustvarite veliko polja Izračunana vrtilna tabela, ne skrbite, da boste sledili formuli, uporabljeni v vsakem od njih.

Excel vam omogoča, da hitro ustvarite seznam vseh formul, uporabljenih pri ustvarjanju izračunanih polj.

Tu so koraki za hiter dostop do seznama formul vseh izračunanih polj:

• Izberite katero koli celico v vrtilni tabeli.
• Pojdite na Orodja za vrtilno tabelo -> Analiza -> Polja, postavke in sklopi -> Formule seznama.

Takoj, ko kliknete Seznam formule, bo Excel samodejno vstavil nov delovni list, ki bo vseboval podrobnosti vseh izračunanih polj/elementov, ki ste jih uporabili v vrtilni tabeli.

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

Koristni so vam lahko tudi naslednji vodiči za vrtilno tabelo:

• Priprava izvornih podatkov za vrtilno tabelo.
• Uporaba rezancev v vrtilni tabeli Excel: Vodnik za začetnike.
• Kako združiti datume v vrtilnih tabelah v Excelu.
• Kako združiti številke v vrtilni tabeli v Excelu.
• Kako filtrirati podatke v vrtilni tabeli v Excelu.
• Kako zamenjati prazne celice z ničlami ​​v vrtilnih tabelah Excel.
• Kako uporabiti pogojno oblikovanje v vrtilni tabeli v Excelu.
• Vrtilni predpomnilnik v Excelu - kaj je to in kako ga najbolje uporabiti?