Pivot Table Formula i Excel (innholdsfortegnelse)
- Pivot Table Formula i Excel
- Tilpasset felt for å beregne gevinstbeløp
- Avansert formel i beregnet felt
Pivot Table Formula i Excel
Pivot Table er et verktøy som lar oss analysere store datoperioder. Vi kan analysere, tolke og gjøre mange andre ting uten å bryte mye av hodet og svetten. Det kan gi nesten alt som er der i kildedataene.
Hvis noe av informasjonen er der i kildedataene, kan det hende vi må beregne dette selv. For eksempel, hvis vi har et samlet salgssum og en total kostnad, kan det hende vi trenger å beregne total fortjeneste eller tap på egen hånd.
Vi trenger ikke gjøre dette i kilden, men vi kan gjøre dette inne i selve pivottabellen. Disse kalles Beregnede felt inne i pivottabellen. Vi kan bruke tilpassede formler for å lage dataene for å fortelle mer historie fra dataene. Beregnede felt lar oss bygge en ny beregnet kolonne som ikke eksisterer i den faktiske datakilden.
I denne artikkelen vil vi demonstrere måtene å bruke Pivot Table Calculated Fields for å bygge nye kolonner basert på kravet vårt.
Tilpasset felt for å beregne gevinstbeløp
Du kan laste ned denne Pivot Table Formula Excel Template her - Pivot Table Formula Excel TemplateDette er det mest brukte kalkulerte feltet i pivottabellen. Ta en titt på dataene nedenfor, jeg har landnavn, produktnavn, solgte enheter, enhetspris, brutto salg, COGS (pris for solgte varer), dato og år.
La meg bruke pivottabellen for å finne det totale salget og de totale kostnadene for hvert land. Nedenfor er pivottabellen for dataene ovenfor.
Problemet er at jeg ikke har en gevinstkolonne i kildedataene. Jeg trenger å finne ut overskuddet og resultatprosenten for hvert land. Vi kan legge til disse to kolonnene i selve pivottabellen.
Trinn 1: Velg en celle i pivottabellen. Gå til Analyser-fanen i båndet, og velg felt, elementer og sett. Under dette velger du Beregnet felt.
Trinn 2: I dialogboksen nedenfor gir du et navn til det nye kalkulerte feltet.
Trinn 3: I formelen-delen bruker du formelen for å finne overskuddet. Formelen for å finne overskuddet er brutto salg - COGS.
Gå inn i formelfeltet> Velg brutto salg fra feltet nedenfor, og dobbeltklikk på det vil vises i formelfeltet.
Skriv nå minus symbol (-) og velg COGS> Dobbeltklikk.
Trinn 4: Klikk på ADD og OK for å fullføre formelen.
Trinn 5: Nå har vi vår TOTALE PROFIT-kolonne i pivottabellen.
Dette kalkulerte feltet er fleksibelt, det er ikke bare begrenset til landsmessig analyse, men vi kan bruke dette til alle slags analyser. Hvis jeg ønsker å se analysen landsmessig og produktmessig, må jeg bare dra og slippe produktkolonnen til ROW-feltet, det vil vise overskuddet på overskuddet for hvert produkt under hvert land.
Trinn 6: Nå må vi beregne gevinstprosenten. Formelen for å beregne profittprosenten er total fortjeneste / brutto salg.
Gå til Analyser og velg igjen Beregnet felt under felt, elementer og sett.
Trinn 7: Nå må vi se det nylig innsatte kalkulerte feltet Total fortjeneste i felt-listen. Sett dette feltet i formelen.
Trinn 8: Skriv inn skillesymbol (/) og sett inn brutto salgsfelt.
Trinn 9: Gi dette beregnede feltet navn som profittprosent.
Trinn 10: Klikk på ADD og OK for å fullføre formelen. Vi har profittprosent som den nye kolonnen.
Avansert formel i beregnet felt
Uansett hva jeg har vist nå, er de grunnleggende tingene i Beregnet felt. I dette eksemplet vil jeg vise deg de avanserte formlene i pivottabellberegnede felt. Nå vil jeg beregne insentivbeløpet basert på resultatprosenten.
Hvis overskuddet% er> 15% insentiv skal være 6% av den totale fortjenesten.
Hvis fortjenesten% er> 10% insentiv bør være 5% av den totale fortjenesten.
Hvis overskuddet% er <10% insentiv, bør det være 3% av den totale fortjenesten.
Trinn 1: Gå til Beregnet felt og åpne dialogboksen nedenfor. Gi navnet som insentivbeløp.
Trinn 2: Nå vil jeg bruke IF-betingelse for å beregne insentivbeløpet. Bruk formlene nedenfor som vist på bildet.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Trinn 3: Klikk på ADD & OK for å fullføre. Nå har vi en Incentive Amount-kolonne.
Begrensning av beregnet felt
Vi har sett rart over kalkulerte felt, men det har noen av begrensningene også. Nå kan du se på bildet nedenfor, hvis jeg vil se oppdelingen av produktmessig insentivbeløp vil vi ha feil SUBTOTAL & STORT TOTALT AV INSENTIVBETALING.
Så vær forsiktig mens du viser summen av kalkulerte felt. Det vil vise deg feil beløp.
Få en liste over alle de beregnede feltformlene
Hvis du ikke vet hvor mange formler som er i pivottabellberegnet felt, kan du få et sammendrag av alle disse i et eget regneark.
Gå til Analyser> Felt, elementer og sett -> Listeformler.
Det vil gi deg et sammendrag av alle formlene i et nytt regneark.
Ting å huske på om pivottabellformelen i Excel
- Vi kan slette, endre alle de beregnede feltene.
- Vi kan ikke bruke formler som VLOOKUP, SUMIF og mange andre formler som er involvert i beregnede felt, dvs. at alle formlene som krever rekkevidde ikke kan brukes.
Anbefalte artikler
Dette har vært en guide til Pivot Table Formula i Excel. Her diskuterte vi trinnene for å bruke formel for pivottabell i Excel sammen med eksempler og nedlastbar Excel-mal. Du kan også se på disse nyttige funksjonene i excel -
- Veiledninger om Pivot Chart i Excel
- Opprette pivottabell i Excel
- VLOOKUP Tutorial i Excel
- Excel Opprett database