Pivot-tabelformel i Excel (indholdsfortegnelse)
- Pivot-tabelformel i Excel
- Brugerdefineret felt til beregning af overskudsbeløb
- Avanceret formel i beregnet felt
Pivot-tabelformel i Excel
Pivot Table er et værktøj, der gør det muligt for os at analysere store dataværdier. Vi kan analysere, fortolke og gøre mange andre ting uden at bryde meget af vores hoved og sved. Det kan give næsten alt, hvad der er der i kildedataene.
Hvis nogle af oplysningerne er der i kildedataene, kan vi muligvis beregne dette selv. For eksempel, hvis vi har et samlet salgsmængde og samlede omkostninger, kan vi muligvis beregne den samlede fortjeneste eller tab på vores egen.
Vi behøver ikke at gøre dette i kilden, men vi kan gøre dette inde i selve pivottabellen, disse kaldes Beregnede felter inde i pivottabellen. Vi kan bruge tilpassede formler til at fremstille dataene til at fortælle mere historie fra dataene. Beregnede felter giver os mulighed for at opbygge en ny beregnet kolonne, som ikke findes i den faktiske datakilde.
I denne artikel vil vi demonstrere måderne til at bruge Pivot Table Beregnede felter til at opbygge nye kolonner baseret på vores krav.
Brugerdefineret felt til beregning af overskudsbeløb
Du kan downloade denne Pivot Table Formula Excel Template her - Pivot Table Formula Excel TemplateDette er det mest anvendte beregnede felt i pivottabellen. Se på nedenstående data, jeg har landnavn, produktnavn, solgte enheder, enhedspris, bruttosalg, COGS (pris for solgte varer), dato og årskolonne.
Lad mig anvende pivottabellen for at finde det samlede salg og de samlede omkostninger for hvert land. Nedenfor er pivottabellen for ovenstående data.
Problemet er, at jeg ikke har en fortjenstkolonne i kildedataene. Jeg er nødt til at finde ud af fortjeneste og overskudsprocent for hvert land. Vi kan tilføje disse to kolonner i selve pivottabellen.
Trin 1: Vælg en celle i pivottabellen. Gå til fanen Analyser i båndet, og vælg felter, emner og sæt. Under dette vælges Beregnet felt.
Trin 2: Giv et nyt beregnede felt i dialogboksen nedenfor.
Trin 3: I formelafsnittet anvender formlen for at finde overskuddet. Formlen til at finde overskuddet er bruttoomsætning - COGS.
Gå ind i formelbjælken> Vælg bruttosalg fra nedenstående felt, og dobbeltklik på det vises i formelbjælken.
Skriv nu minus symbol (-) og vælg COGS> Dobbeltklik.
Trin 4: Klik på ADD og OK for at fuldføre formlen.
Trin 5: Nu har vi vores TOTALE PROFIT-kolonne i pivottabellen.
Dette beregnede felt er fleksibelt, det er ikke kun begrænset til landemæssig analyse, men vi kan bruge dette til alle slags analyser. Hvis jeg vil se analysen landemæssigt og produktmæssigt, er jeg bare nødt til at trække og slippe produktkolonnen til ROW-feltet, det viser fordelingen af overskuddet for hvert produkt under hvert land.
Trin 6: Nu skal vi beregne profitprocenten. Formlen til beregning af fortjenesteprocenten er samlet overskud / bruttosalg.
Gå til Analyser, og vælg igen Beregnet felt under felter, elementer og sæt.
Trin 7: Nu skal vi se det nyligt indsatte beregnede felt Samlet fortjeneste på feltlisten. Indsæt dette felt til formlen.
Trin 8: Indtast skillelinjesymbol (/), og indsæt bruttosalgsfelt.
Trin 9: Navngiv dette beregnede felt som fortjenesteprocent.
Trin 10: Klik på ADD og OK for at fuldføre formlen. Vi har profitprocent som den nye kolonne.
Avanceret formel i beregnet felt
Hvad jeg nu har vist er de grundlæggende ting i Beregnet felt. I dette eksempel viser jeg dig de avancerede formler i beregnede felttabeller. Nu vil jeg beregne incitamentbeløbet baseret på overskudsprocenten.
Hvis overskuddet% er> 15%, skal incitamentet udgøre 6% af den samlede fortjeneste.
Hvis overskuddet% er> 10%, skal incitamentet udgøre 5% af den samlede fortjeneste.
Hvis overskuddet% er <10%, skal incitamentet udgøre 3% af den samlede fortjeneste.
Trin 1: Gå til Beregnet felt og åbn dialogboksen nedenfor. Giv navnet som incitamentbeløb.
Trin 2: Nu vil jeg bruge IF-betingelse til at beregne incitamentsbeløbet. Anvend nedenstående formler som vist på billedet.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Trin 3: Klik på TILFØJ & OK for at afslutte. Nu har vi en kolonne med incitamentbeløb.
Begrænsning af det beregnede felt
Vi har set undret ved beregnede felter, men det har også nogle af begrænsningerne. Se nu på nedenstående billede, hvis jeg vil se opdelingen af produktmæssigt incitamentbeløb, vil vi have forkert SUBTOTAL & STORTALT af incitamentbeløbet.
Så vær forsigtig, mens du viser summen af beregnede felter. Det viser dig de forkerte beløb.
Hent en liste over alle de beregnede feltformler
Hvis du ikke ved, hvor mange formler der er i det beregnede felt for pivottabellen, kan du få et resumé af alle disse i et separat regneark.
Gå til Analyser> Felter, elementer og sæt -> Listeformler.
Det giver dig et resumé af alle formlerne i et nyt regneark.
Ting at huske på Pivot Table Formula i Excel
- Vi kan slette, ændre alle de beregnede felter.
- Vi kan ikke bruge formler som VLOOKUP, SUMIF og meget andet interval formler i beregnede felter, dvs. alle formler, der kræver interval, kan ikke bruges.
Anbefalede artikler
Dette har været en guide til Pivot Table Formula i Excel. Her drøftede vi trinnene til brug af formel af pivottabel i Excel sammen med eksempler og downloadbar excel-skabelon. Du kan også se på disse nyttige funktioner i excel -
- Tutorials om Pivot Chart i Excel
- Oprettelse af pivottabel i Excel
- VLOOKUP-tutorial i Excel
- Excel Opret database