Exceli liigendtabeli arvutusvälja lisamine ja kasutamine

Sageli on pärast Pivot -tabeli loomist vaja oma analüüsi laiendada ja selle osana lisada rohkem andmeid/arvutusi.

Kui vajate uut andmepunkti, mida saab saada Pivot -tabeli olemasolevate andmepunktide abil, ei pea te tagasi minema ja seda lähteandmetesse lisama. Selle asemel võite kasutada a Pivot tabeli arvutatud väli seda tegema.

Laadige andmestik alla ja järgige.

Mis on liigendtabeli arvutusväli?

Alustame pöördetabeli põhinäitega.

Oletame, et teil on jaemüüjate andmekogum ja loote liigendtabeli, nagu allpool näidatud:

Ülaltoodud liigendtabel võtab kokku jaemüüjate müügi- ja kasumiväärtused.

Mis siis, kui soovite ka teada, milline oli nende jaemüüjate kasumimarginaal (kus kasumimarginaal on „Kasum” jagatud „Müügiga”).

Selleks on paar võimalust.

1. Minge tagasi algse andmekogumi juurde ja lisage see uus andmepunkt. Nii saate lähteandmetesse sisestada uue veeru ja arvutada selles kasumimarginaali. Kui olete seda teinud, peate värskendustabeli lähteandmeid värskendama, et see uus veerg selle osana kätte saada.
   • Kuigi see meetod on võimalik, peate andmekogumi juurde tagasi minema ja arvutused tegema. Näiteks peate võib -olla lisama teise veeru, et arvutada keskmine müük ühiku kohta (müük/kogus). Jällegi peate selle veeru oma lähteandmetesse lisama ja seejärel pöördtabelit värskendama.
   • See meetod paisutab ka teie liigendtabelit, kui lisate sellele uusi andmeid.
2. Lisage arvutused väljaspool pöördtabelit. See võib olla valik, kui teie liigendtabeli struktuur tõenäoliselt ei muutu. Kuid kui muudate Pivot -tabelit, ei pruugi arvutust vastavalt värskendada ja see võib teile anda valesid tulemusi või vigu. Nagu allpool näidatud, arvutasin kasumimarginaali, kui reas olid jaemüüjad. Aga kui muutsin selle klientidelt piirkondadeks, andis valem vea.
3. Pivot tabeli arvutatud välja kasutamine. See on kõige tõhusam viis olemasolevate Pivot Tabeli andmete kasutamiseks ja soovitud mõõdiku arvutamiseks. Kaaluge arvutatud välja virtuaalse veeruna, mille olete lisanud liigendtabeli olemasolevate veergude abil. Pivot tabeli arvutusvälja kasutamisel on palju eeliseid (nagu me näeme minuti pärast):
   • See ei nõua valemite käsitlemist ega lähteandmete värskendamist.
   • See on skaleeritav, kuna arvestab automaatselt kõiki uusi andmeid, mille saate oma pöördetabelisse lisada. Kui olete arvutusvälja lisanud, saate seda kasutada nagu mis tahes muud oma liigendtabeli välja.
   • Seda on lihtne värskendada ja hallata. Näiteks kui mõõdikud muutuvad või peate arvutust muutma, saate seda hõlpsalt teha liigendtabelist.

Arvutatud välja lisamine pöördetabelisse

Vaatame, kuidas olemasolevasse liigendtabelisse lisada liigendtabeli arvutatud väli.

Oletame, et teil on Pivot -tabel, nagu allpool näidatud, ja soovite arvutada iga jaemüüja kasumimarginaali:

Pivot -tabeli arvutusvälja lisamiseks toimige järgmiselt.

• Valige liigendtabelis suvaline lahter.
• Avage liigendtabeli tööriistad -> Analüüs -> Arvutused -> Väljad, üksused ja komplektid.
• Valige rippmenüüst Arvutatud väli.
• Dialoogiboksis Arvutatud faili lisamine:
  • Andke sellele nimi, sisestades selle väljale Nimi.
  • Looge väljal Valem arvutatud väljale soovitud valem. Pange tähele, et saate valida allpool loetletud väljade nimede hulgast. Sel juhul on valem ‘= kasum/ müük’. Saate kas väljade nimed käsitsi sisestada või topeltklõpsata väljade väljal loetletud väljade nimel.
• Klõpsake nuppu Lisa ja sulgege dialoogiboks.

Niipea kui arvutatud väli lisatakse, kuvatakse see PivotTable -liigendtabeli väljade loendis ühe väljana.

Nüüd saate seda arvutatud välja kasutada mis tahes muu liigendtabeli väljana (pange tähele, et te ei saa aruandefiltri või lõikurina kasutada liigendtabeli arvutatud välja).

Nagu ma juba mainisin, on Pivot Tabeli arvutusvälja kasutamise eeliseks see, et saate muuta Pivot Tabeli struktuuri ja see kohandub automaatselt.

Näiteks kui lohistan piirkonda ridade piirkonnas, näete allpool näidatud tulemust, kus kasumimarginaali väärtus on esitatud nii jaemüüjate kui ka piirkonna kohta.

Ülaltoodud näites kasutasin arvutatud välja sisestamiseks lihtsat valemit (= kasum/müük). Siiski võite kasutada ka mõnda täpsemat valemit.

Enne kui näitan teile näite täpsema valemi kasutamisest Pivot Tabeli arvutamisvälja loomiseks, on siin mõned asjad, mida peate teadma.

• PIVOT -tabeli arvutusvälja loomisel EI SAA kasutada viiteid ega nimevahemikke. See välistaks paljud valemid nagu VLOOKUP, INDEX, OFFSET jne. Siiski saate kasutada valemeid, mis võivad töötada ilma viiteta (näiteks SUM, IF, COUNT jne).
• Valemis saate kasutada konstanti. Näiteks kui soovite teada prognoositavat müüki, kus prognoositakse 10%kasvu, võite kasutada valemit = Müük*1,1 (kus 1,1 on konstant).
• Eelisjärjestust järgitakse valemis, mis teeb arvutatud välja. Parima tavana kasutage sulgusid, et veenduda, et te ei pea eelisjärjekorda meeles pidama.

Vaatame nüüd näidet arvutusvälja loomiseks täiustatud valemi kasutamisest.

Oletame, et teil on allpool näidatud andmekogum ja peate Pivot -tabelis näitama prognoositud müügiväärtust.

Prognoositava väärtuse saamiseks peate suurte jaemüüjate puhul kasutama müügi suurenemist 5% (müük üle 3 miljoni) ja väikeste ja keskmise jaemüüjate puhul 10% müügi kasvu (müük alla 3 miljoni).

Märkus. Siin olevad müüginumbrid on võltsitud ja neid on kasutatud selle õpetuse näidete illustreerimiseks.

Siin on, kuidas seda teha.

• Valige liigendtabelis suvaline lahter.
• Avage liigendtabeli tööriistad -> Analüüs -> Arvutused -> Väljad, üksused ja komplektid.
• Valige rippmenüüst Arvutatud väli.
• Dialoogiboksis Arvutatud faili lisamine:
  • Andke sellele nimi, sisestades selle väljale Nimi.
  • Kasutage väljal Valem järgmist valemit: = IF (Region = ”South”, Sales *1,05, Sales *1.1)
• Klõpsake nuppu Lisa ja sulgege dialoogiboks.

See lisab liigendtabelisse uue veeru müügiprognoosi väärtusega.

Andmekogumi allalaadimiseks klõpsake siin.

Pivottabeli arvutatud väljade probleem

Arvutatud väli on hämmastav funktsioon, mis tõstab teie arvutustabeli väärtust väljaarvutustega, hoides samal ajal kõike muudetavana ja hallatavana.

Pivot -tabeli arvutusväljadega on aga probleem, mida peate enne selle kasutamist teadma.

Oletame, et mul on Pivot -tabel, nagu allpool näidatud, kus kasutasin arvutatud välja prognoositud müüginumbrite saamiseks.

Pange tähele, et vahesumma ja kogusumma ei ole õiged.

Kuigi need peaksid lisama iga jaemüüja jaoks individuaalse müügiprognoosi väärtuse, järgib see tegelikult sama arvutatud välja valemit, mille oleme loonud.

Nii et South Total jaoks, kuigi väärtus peaks olema 22 824 000, teatab South Total selle ekslikult 22 287 000 -st. See juhtub, kuna väärtuse saamiseks kasutatakse valemit 21 225 800*1,05.

Kahjuks ei saa te seda kuidagi parandada.

Parim viis selle lahendamiseks oleks eemaldada Pivot tabelist vahesummad ja kogusummad.

Samuti saate läbida mõned uuenduslikud lahendused, mida Debra on näidanud selle probleemiga tegelemiseks.

Kuidas muuta või kustutada liigendtabeli arvutatud välja?

Kui olete Pivot Tabeli arvutusvälja loonud, saate valemit muuta või selle kustutada, toimides järgmiselt.

• Valige liigendtabelis suvaline lahter.
• Avage liigendtabeli tööriistad -> Analüüs -> Arvutused -> Väljad, üksused ja komplektid.
• Valige rippmenüüst Arvutatud väli.
• Klõpsake väljal Nimi rippmenüü noolt (väike allanool välja lõpus).
• Valige loendist arvutatud väli, mida soovite kustutada või muuta.
• Muutke valemit juhuks, kui soovite seda muuta, või klõpsake kustutamiseks nuppu Kustuta.

Kuidas saada kõigi arvutatud väljade valemite loendit?

Kui loote palju välja Pivot Table Calculated (Pivottabeli arvutatud), ärge muretsege, et jälgiksite igal sellel kasutatavat valemit.

Excel võimaldab teil kiiresti luua kõigi arvutatud väljade loomisel kasutatud valemite loendi.

Kõikide arvutatud väljade valemite loendi kiireks hankimiseks toimige järgmiselt.

• Valige liigendtabelis suvaline lahter.
• Avage liigendtabeli tööriistad -> Analüüs -> Väljad, üksused ja komplektid -> Loendivalemid.

Niipea kui klõpsate loendivalemitel, lisab Excel automaatselt uue töölehe, mis sisaldab üksikasju kõigi arvutatud väljade/üksuste kohta, mida olete liigendtabelis kasutanud.

See võib olla tõesti kasulik tööriist, kui peate oma töö kliendile saatma või oma meeskonnaga jagama.

Kasulikud võivad olla ka järgmised liigendtabeli õpetused:

• Lähteandmete ettevalmistamine pöördetabeli jaoks.
• Viilutajate kasutamine Exceli liigendtabelis: algajate juhend.
• Kuupäevade rühmitamine Exceli liigendtabelites.
• Kuidas rühmitada numbreid Exceli liigendtabelis.
• Kuidas Excelis liigendtabelis andmeid filtreerida.
• Kuidas tühjad lahtrid nullidega asendada Exceli liigendtabelites.
• Tingimusvormingu rakendamine Exceli pöördtabelis.
• Pivot -vahemälu Excelis - mis see on ja kuidas seda kõige paremini kasutada?