Draaitabelformule in Excel (inhoudsopgave)
- Draaitabelformule in Excel
- Aangepast veld om winstbedrag te berekenen
- Geavanceerde formule in berekend veld
Draaitabelformule in Excel
Draaitabel is een tool waarmee we grote gegevensbereiken kunnen analyseren. We kunnen analyseren, interpreteren en vele andere dingen doen zonder veel van ons hoofd en zweet te breken. Het kan bijna alles geven wat er in de brongegevens aanwezig is.
Als een deel van de informatie in de brongegevens staat, moeten we dit mogelijk zelf berekenen. Als we bijvoorbeeld een Totaal verkoopbedrag en Totale kosten hebben, moeten we mogelijk de totale winst of het eigen verlies berekenen.
We hoeven dit niet in de bron te doen, maar we kunnen dit in de draaitabel zelf doen, deze worden berekende velden in de draaitabel genoemd. We kunnen aangepaste formules gebruiken om de gegevens meer verhaal uit de gegevens te laten vertellen. Met Berekende velden kunnen we een nieuwe berekende kolom maken die niet bestaat in de werkelijke gegevensbron.
In dit artikel zullen we de manieren demonstreren om Pivot Table Calculated Fields te gebruiken om nieuwe kolommen te bouwen op basis van onze eis.
Aangepast veld om winstbedrag te berekenen
U kunt deze draaitabelformule Excel-sjabloon hier downloaden - Draaitabelformule Excel-sjabloonDit is het meest gebruikte berekende veld in de draaitabel. Bekijk de onderstaande gegevens, ik heb de landnaam, productnaam, verkochte eenheden, eenheidsprijs, bruto-omzet, COGS (kosten van verkochte goederen), datum en jaarkolom.
Laat me de draaitabel toepassen om de totale verkoop en totale kosten voor elk land te vinden. Hieronder vindt u de draaitabel voor de bovenstaande gegevens.
Het probleem is dat ik geen winstkolom in de brongegevens heb. Ik moet de winst en het winstpercentage voor elk land achterhalen. We kunnen deze twee kolommen toevoegen aan de draaitabel zelf.
Stap 1: Selecteer een cel in de draaitabel. Ga naar het tabblad Analyse in het lint en selecteer Velden, Items en sets. Selecteer hieronder Berekend veld.
Stap 2: Geef in het onderstaande dialoogvenster een naam aan uw nieuwe berekende veld.
Stap 3: Pas in de sectie Formule de formule toe om de winst te vinden. De formule om de winst te vinden is bruto-omzet - COGS.
Ga binnen de formulebalk> Selecteer Bruto-omzet in het onderstaande veld en dubbelklik erop dat het verschijnt in de formulebalk.
Typ nu het minteken (-) en selecteer COGS> Dubbelklik.
Stap 4: Klik op TOEVOEGEN en OK om de formule te voltooien.
Stap 5: Nu hebben we onze TOTALE WINST-kolom in de draaitabel.
Dit berekende veld is flexibel, het is niet alleen beperkt tot analyse per land, maar we kunnen dit voor alle soorten analyses gebruiken. Als ik de analyse per land en per product wil bekijken, hoef ik alleen maar de productkolom naar het ROW-veld te slepen om de verdeling van de winst voor elk product onder elk land weer te geven.
Stap 6: Nu moeten we het winstpercentage berekenen. De formule om het winstpercentage te berekenen, is de totale winst / bruto-omzet.
Ga naar Analyseren en selecteer opnieuw Berekend veld onder Velden, Items en sets.
Stap 7: Nu moeten we het nieuw ingevoegde berekende veld Totale winst zien in de lijst Velden. Voeg dit veld in de formule in.
Stap 8: Typ het scheidingsteken (/) en voeg het veld Bruto verkoop in.
Stap 9: Noem dit berekende veld als winstpercentage.
Stap 10: Klik op TOEVOEGEN en OK om de formule te voltooien. We hebben winstpercentage als de nieuwe kolom.
Geavanceerde formule in berekend veld
Wat ik nu heb laten zien, is de basis van Calculated Field. In dit voorbeeld zal ik u de geavanceerde formules in berekende velden van de draaitabel tonen. Nu wil ik het stimuleringsbedrag berekenen op basis van het winstpercentage.
Als de winst%> 15% is, moet de incentive 6% van de totale winst zijn.
Als de winst%> 10% is, moet de incentive 5% van de totale winst zijn.
Als de winst% <10% is, moet de stimulans 3% van de totale winst zijn.
Stap 1: Ga naar Berekend veld en open het onderstaande dialoogvenster. Geef de naam als stimuleringsbedrag.
Stap 2: Nu zal ik de IF-voorwaarde gebruiken om het stimuleringsbedrag te berekenen. Pas de onderstaande formules toe zoals weergegeven in de afbeelding.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Stap 3: Klik op TOEVOEGEN & OK om te voltooien. Nu hebben we een kolom Stimuleringsbedrag.
Beperking van berekend veld
We hebben het wonder van Berekende Velden gezien, maar het heeft ook enkele beperkingen. Kijk nu naar de onderstaande afbeelding, als ik het opsplitsing van productgewijs stimuleringsbedrag wil zien, hebben we een verkeerd SUB TOTAAL & GROOT TOTAAL van INCENTIEF BEDRAG.
Wees dus voorzichtig bij het tonen van het subtotaal van berekende velden. Het zal je de verkeerde bedragen tonen.
Krijg een lijst met alle berekende veldformules
Als u niet weet hoeveel formules er in het berekende draaitabelveld staan, kunt u de samenvatting van deze allemaal in een afzonderlijk werkblad krijgen.
Ga naar Analyseren> Velden, items en sets -> Lijstformules.
Het geeft u een samenvatting van alle formules in een nieuw werkblad.
Dingen om te onthouden over de draaitabelformule in Excel
- We kunnen alle berekende velden verwijderen, wijzigen.
- We kunnen geen formules gebruiken zoals VLOOKUP, SUMIF en veel andere bereikformules in berekende velden, dat wil zeggen dat alle formules waarvoor bereik nodig is, niet kunnen worden gebruikt.
Aanbevolen artikelen
Dit is een gids geweest voor de draaitabelformule in Excel. Hier hebben we de stappen voor het gebruik van de formule van de draaitabel in Excel besproken, samen met voorbeelden en een downloadbare Excel-sjabloon. U kunt deze handige functies ook bekijken in Excel -
- Zelfstudies over draaitabel in Excel
- Draaitabel maken in Excel
- VLOOKUP Zelfstudie in Excel
- Excel Maak database