Hoe om Excel-draaitabel-berekende velde by te voeg en te gebruik

Tipies, sodra jy 'n spiltabel geskep het, moet jy jou analise uitbrei en meer data/berekeninge daarin insluit.

As jy 'n nuwe datapunt benodig wat verkry kan word deur 'n bestaande datapunt in die spilpunttabel te gebruik, hoef jy nie terug te gaan en dit by die brondata te voeg nie.In plaas daarvan kan jy gebruikSpiltabel Berekende Veldeom dit te doen.

Wat is 'n draaitabel berekende veld?

Kom ons begin met 'n basiese voorbeeld van 'n spilpunttabel.

Gestel jy het 'n datastel van kleinhandelaars en jy skep 'n spiltabel soos volg:

Die spiltabel hierbo som die kleinhandelaar se verkope en winswaardes op.

Nou, wat as jy ook wil weet wat hierdie kleinhandelaars se winsmarges is (die winsmarge is "wins" gedeel deur "verkope")?

Daar is verskeie maniere om dit te doen:

1. Gaan terug na die oorspronklike datastel en voeg hierdie nuwe datapunt by.Jy kan dus 'n nuwe kolom in die brondata invoeg en die winsmarge daarin bereken.Sodra dit gedoen is, moet jy die spilpunttabel se brondata opdateer om hierdie nuwe kolom as deel daarvan te kry.
   • Terwyl hierdie benadering werk, moet jy met die hand teruggaan na die datastel en die berekeninge doen.Byvoorbeeld, jy wil dalk nog 'n kolom byvoeg om die gemiddelde verkope per eenheid (verkope/hoeveelheid) te bereken.Weereens, jy moet hierdie kolom by die brondata voeg en dan die spiltabel opdateer.
   • Hierdie metode laat ook jou spilpunt opblaas soos jy nuwe data daarby voeg.
2. Voeg berekeninge buite spiltabelle by.Kies hierdie opsie as jou spiltabelstruktuur onwaarskynlik is om te verander.As jy egter die spilpunttabel verander, sal die berekeninge dalk nie dienooreenkomstig bywerk nie en kan dit vir jou verkeerde resultate of foute gee.Soos hieronder getoon, het ek die winsmarge bereken wanneer daar kleinhandelaars in die ry is.Maar wanneer ek dit van klant tot area verander, loop die formule verkeerd.
3. Bereken velde met behulp van spilpunttabelle.dit isGebruik bestaande draaitabeldata en bereken diemees doeltreffende manier.Dink aan berekende velde as virtuele kolomme wat jy byvoeg deur bestaande kolomme in jou spiltabel te gebruik.Die gebruik van spiltabel-berekende velde hou baie voordele in (soos ons later sal sien):
   • Dit vereis nie dat jy met formules werk of brondata opdateer nie.
   • Dit is uitbreibaar omdat dit outomaties enige nuwe data in ag neem wat jy by die spiltabel kan voeg.Nadat u 'n berekende veld bygevoeg het, kan u dit soos enige ander veld in 'n spiltabel gebruik.
   • Dit is maklik om op te dateer en te bestuur.Byvoorbeeld, as 'n maatstaf verander of jy 'n berekening moet verander, kan jy dit maklik vanaf die spiltabel self doen.

Voeg berekende velde by spiltabel

Kom ons kyk hoe om 'n spiltabel-berekende veld by 'n bestaande spiltabel te voeg.

Gestel jy het 'n spiltabel wat so lyk, en jy wil die winsmarge vir elke kleinhandelaar bereken:

Hier is die stappe om 'n PivotTable-berekende veld by te voeg:

Nadat die berekende veld bygevoeg is, sal dit as een van die velde in die PivotTable Fields-lys verskyn.

Jy kan nou hierdie berekende veld as enige ander spiltabelveld gebruik (let daarop dat jy nie 'n spiltabelberekende veld as 'n verslagfilter of snyer kan gebruik nie).

Soos ek voorheen genoem het, is die voordeel van die gebruik van 'n spilpunt-berekende veld dat jy die struktuur van die spiltabel kan verander en dit sal outomaties aanpas.

As ek byvoorbeeld 'n streek in die ry-area sleep en laat val, sal jy resultate kry soos die volgende, waar die winsmargewaardes vir die kleinhandelaar en streek gerapporteer word.

 

In die voorbeeld hierbo het ek 'n eenvoudige formule (=Wins/Verkope) gebruik om die berekende veld in te voeg.U kan egter ook 'n paar gevorderde formules gebruik.

Voordat ek jou 'n voorbeeld wys van die gebruik van 'n gevorderde formule om 'n PivotTable-berekende veld te skep, is hier 'n paar dinge wat jy moet weet:

• Jy kan nie verwysings of benoemde reekse gebruik wanneer jy PivotTable-berekende velde skep nie.Dit sal baie formules soos VLOOKUP, INDEX, OFFSET, ens uitsluit.Jy kan egter formules gebruik wat sonder verwysings werk (bv. SOM, IF, COUNT, ens...).
• Jy kan konstantes in formules gebruik.Byvoorbeeld, as jy wil weet wat die voorspelde verkoopsgroei wat na verwagting met 10% sal toeneem, sal jy die formule =Verkope*1.1 gebruik (waar 1.1 'n konstante is).
• Volg voorrangvolgorde in formules wat berekende velde genereer.As 'n beste praktyk, gebruik hakies om te verseker dat jy nie voorrang hoef te onthou nie.

Kom ons kyk nou na 'n voorbeeld van die skep van 'n berekende veld deur 'n gevorderde formule te gebruik.

Gestel jy het die datastel wat hieronder gewys word en jy moet die voorspelde verkope in 'n spiltabel vertoon.

Vir voorspelde waardes sal jy 'n 300% verkoopsverhoging vir groot kleinhandelaars gebruik (meer as 5 miljoen verkope) en 'n 300% verkoopsverhoging vir klein en medium kleinhandelaars (minder as 10 miljoen verkope).

Let wel: Die verkoopsyfers hier is vals en is gebruik om die voorbeelde in hierdie tutoriaal te illustreer.

Hier is hoe om dit te doen:

Dit sal 'n nuwe kolom by die draaitabel voeg met verkoopsvoorspellingwaardes.

Spiltabel berekende veldprobleem

Berekende velde is 'n wonderlike kenmerk wat werklik die waarde van spilpunttabelle met veldberekeninge verhoog, terwyl alles steeds skaalbaar en hanteerbaar is.

Daar is egter 'n probleem met PivotTable-berekende velde wat u moet verstaan ​​voordat u dit gebruik.

Gestel ek het 'n spiltabel soos hieronder en ek gebruik 'n berekende veld om voorspelde verkoopsgetalle te kry.

Let daarop dat die subtotale en totale nie korrek is nie.

Alhoewel dit afsonderlike verkoopsvoorspellingswaardes vir elke kleinhandelaar moet byvoeg, volg dit in werklikheid dieselfde berekende veldformule wat ons geskep het.

Dus vir South Total, terwyl die waarde 22,824,000 moet wees, rapporteer South Total dit verkeerdelik as 22,287,000.Dit gebeur omdat dit die formule 21,225,800*1.05 gebruik om die waarde te kry.

Ongelukkig kan jy nie hierdie probleem regstel nie.

Die beste manier om dit te hanteer, is om die subtotale en groottotale van die spilpunttabel te verwyder.

Jy kan ook slaag Aangebied deur DebraSommige innoverende oplossings om hierdie probleem te hanteer.

Hoe om 'n spiltabel-berekende veld te verander of te skrap?

Nadat jy 'n PivotTable-berekende veld geskep het, kan jy die formule verander of dit uitvee deur die volgende stappe te gebruik:

Hoe om 'n lys van alle berekende veldformules te kry?

As jy baie PivotTable-berekende velde skep, hoef jy nie bekommerd te wees oor die byhou van die formules wat in elke veld gebruik word nie.

Met Excel kan u vinnig 'n lys skep van alle formules wat gebruik word om berekende velde te skep.

Hier is die stappe om vinnig 'n lys van alle berekende veldformules te kry:

• Kies enige sel in die spilpunttabel.
• Gaan na PivotTable Tools -> Analiseer -> Velde, items en stelle -> Lys formules.

Sodra jy op die lysformule geklik het, sal Excel outomaties 'n nuwe werkblad invoeg met besonderhede van al die berekende velde/items wat jy in die spiltabel gebruik het.

Dit kan 'n baie nuttige hulpmiddel wees as jy werk aan 'n kliënt moet stuur of dit met jou span moet deel.