Pivoti tabeli valem Excelis (sisukord)
- Pivoti tabeli valem Excelis
- Kohandatud väli kasumi summa arvutamiseks
- Täpsustatud valem arvutatud väljal
Pivoti tabeli valem Excelis
Pivoti tabel on tööriist, mis võimaldab meil analüüsida suuri andmevahemikke. Saame analüüsida, tõlgendada ja teha paljusid muid asju ilma, et peaksime palju pead murdma ja higistama. See võib anda peaaegu kõik, mis algandmetes on.
Kui osa teavet on lähteandmetes, peame võib-olla selle ise välja arvutama. Näiteks kui meil on käibe kogusumma ja kogumaksumus, peame võib-olla ise arvutama kogukasumi või -kahjumi.
Me ei pea seda tegema lähteallikas, vaid saame seda teha ka pöördtabelis endas, neid nimetatakse pöördetabeli sees arvutatud väljadeks. Saame andmete abil kohandatud valemeid kasutada, et andmete põhjal lugu rohkem rääkida. Arvutatud väljad võimaldavad meil luua uue arvutatud veeru, mida tegelikus andmeallikas pole.
Selles artiklis demonstreerime Pivoti tabeli arvutatud väljade kasutamise viise uute veergude ehitamiseks vastavalt meie nõudele.
Kohandatud väli kasumi summa arvutamiseks
Selle Pivot Table Vormel Exceli malli saate alla laadida siit - Pivot Table Vormel Exceli mallSee on pöördtabelis kõige sagedamini kasutatav arvutatud väli. Vaadake allpool toodud andmeid, mul on veerg Riigi nimi, Tootenimi, Müüdud ühikud, Ühiku hind, Brutomüük, COGS (Müüdud kauba maksumus), Kuupäev ja Aasta.
Lubage mul kasutada pöördetabelit, et leida iga riigi kogumüük ja kogumaksumus. Allpool on ülaltoodud andmete pöördetabel.
Probleem on selles, et mul pole lähteandmetes kasumiveergu. Pean välja selgitama iga riigi kasumi ja kasumi protsendi. Need kaks veergu saame lisada pöördetabelisse ise.
1. samm: valige lahter pöördetabelis. Minge lindil vahekaardile Analüüs ja valige Väljad, üksused ja komplektid. Selle all valige arvutatud väli.
2. samm: andke allpool asuvas dialoogiboksis uuele arvutatud väljale nimi.
3. samm: jaotises Vormel kasutage kasumi leidmiseks valemit. Kasumi leidmise valem on brutomüük - COGS.
Minge valemi riba sisse ja valige allpool olevast väljast Grossi müük ja topeltklõpsake seda, et kuvatakse vormeliriba.
Tippige nüüd miinus sümbol (-) ja valige KOHED> Topeltklõps.
4. samm: valemi lõpuleviimiseks klõpsake nuppu ADD ja OK.
5. samm: nüüd on pivot-tabelis meie kasum KOKKU.
See arvutatud väli on paindlik, see ei piirdu ainult riikidepõhise analüüsiga, vaid saame seda kasutada igasuguste analüüside jaoks. Kui soovin näha analüüsi riigiti ja tootepõhiselt, pean lihtsalt veeru tooterubali lohistama ROW väljale. See näitab iga toote kasumi jaotust igas riigis.
6. samm: nüüd peame arvutama kasumiprotsendi. Kasumiprotsendi arvutamise valem on kogukasum / brutomüük.
Minge Analüüsi ja valige uuesti väljade, üksuste ja komplektide alt Arvutatud väli.
7. samm: nüüd peame väljade loendis nägema äsja sisestatud arvutatud välja Kogu kasum. Lisage see valem.
8. samm: tippige jagaja sümbol (/) ja sisestage müügi koguväli.
9. samm: nimetage see arvutatud väli kasumiprotsendiks.
10. samm: valemi täitmiseks klõpsake nuppu ADD ja OK. Uue veeruna on toodud kasumiprotsent.
Täpsustatud valem arvutatud väljal
Mida iganes ma nüüd näidanud olen, on arvutatud välja põhiline värk. Selles näites näitan täpsustatud valemeid pöördetabeli arvutatud väljadel. Nüüd tahan soodustussumma arvutada kasumiprotsendi alusel.
Kui kasumiprotsent on> 15%, peaks stiimul moodustama 6% kogukasumist.
Kui kasumiprotsent on> 10%, peaks stiimul moodustama 5% kogukasumist.
Kui kasumiprotsent on <10%, peaks stiimul olema 3% kogukasumist.
1. samm: minge arvutatud väljale ja avage allolev dialoogiboks. Andke nimi kui ergutav summa.
2. samm: nüüd kasutan ergutussumma arvutamiseks IF-tingimust. Rakendage alltoodud valemeid nagu pildil näidatud.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
3. samm. Lõpetamiseks klõpsake nuppu Lisa ja OK. Nüüd on meil veerg Ergutav summa.
Arvutatud välja piirang
Oleme näinud arvutatud väljade imet, kuid sellel on ka mõned piirangud. Vaadake nüüd allolevat pilti, kui soovin näha tootepõhise stiimulite summa jagunemist, siis on meil valesti SUB KOKKU & GRAND KOKKU PÕHJUSTAV SUMMA.
Nii et ole ettevaatlik, kui kuvate arvutatud väljade vahesumma. See näitab teile valesid summasid.
Hankige kõigi arvutatud välja valemite loend
Kui te ei tea, kui palju valemeid on pöördetabeli arvutatud väljal, saate kõigi nende kokkuvõtte eraldi töölehel.
Minge jaotisse Analüüsi> väljad, üksused ja komplektid -> Valemite loend.
See annab teile kokkuvõtte kõigist valemitest uuel töölehel.
Pivoti tabeli valemi kohta Excel'is meelde tuletavad asjad
- Saame kustutada, muuta kõiki arvutatud välju.
- Me ei saa arvutatud väljadel kasutada valemeid nagu VLOOKUP, SUMIF ja paljusid teisi vahemikuga seotud valemeid, st kõiki valemit, mis vajavad vahemikku, ei saa kasutada.
Soovitatavad artiklid
See on olnud Pivoti tabeli valemi juhend Excelis. Siin arutasime Pivoti tabeli valemi kasutamise etappe Excelis koos näidete ja allalaaditava Exceli malliga. Võite vaadata neid kasulikke funktsioone ka excelis -
- Juhtnöörid Pivot-diagrammil Excelis
- Pivot-tabeli loomine Excelis
- VLOOKUPi õpetus Excelis
- Exceli andmebaasi loomine