Berekende velden in Excel-draaitabel toevoegen en gebruiken

Als u eenmaal een draaitabel heeft gemaakt, moet u doorgaans uw analyse uitbreiden en er meer gegevens/berekeningen in opnemen.

Als u een nieuw gegevenspunt nodig hebt dat kan worden verkregen met behulp van een bestaand gegevenspunt in de draaitabel, hoeft u niet terug te gaan en het aan de brongegevens toe te voegen.In plaats daarvan kunt uBerekende velden in draaitabelom dit te doen.

Wat is een berekend draaitabelveld?

Laten we beginnen met een eenvoudig voorbeeld van een draaitabel.

Stel je hebt een dataset van retailers en je maakt een draaitabel als volgt:

De bovenstaande draaitabel geeft een overzicht van de verkoop- en winstwaarden van de detailhandelaar.

Wat als u nu ook zou willen weten wat de winstmarges van die retailers waren (de winstmarge is "winst" gedeeld door "verkoop")?

Er zijn verschillende manieren om dit te doen:

1. Ga terug naar de oorspronkelijke dataset en voeg dit nieuwe datapunt toe.U kunt dus een nieuwe kolom in de brongegevens invoegen en daarin de winstmarge berekenen.Zodra dit is gebeurd, moet u de brongegevens van de draaitabel bijwerken om deze nieuwe kolom als onderdeel ervan te krijgen.
   • Hoewel deze aanpak werkt, moet u handmatig teruggaan naar de gegevensset en de berekeningen uitvoeren.U wilt bijvoorbeeld nog een kolom toevoegen om de gemiddelde verkoop per eenheid (verkoop/hoeveelheid) te berekenen.Nogmaals, u moet deze kolom toevoegen aan de brongegevens en vervolgens de draaitabel bijwerken.
   • Deze methode laat ook uw draaitabel opzwellen terwijl u er nieuwe gegevens aan toevoegt.
2. Voeg berekeningen toe buiten draaitabellen.Kies deze optie als het onwaarschijnlijk is dat uw draaitabelstructuur zal veranderen.Als u echter de draaitabel wijzigt, worden de berekeningen mogelijk niet dienovereenkomstig bijgewerkt en kunnen ze u verkeerde resultaten of fouten opleveren.Zoals hieronder te zien is, heb ik de winstmarge berekend als er retailers in de rij zijn.Maar als ik het van klant naar gebied verander, gaat de formule mis.
3. Bereken velden met behulp van draaitabellen.dit isGebruik bestaande draaitabelgegevens en bereken demeest efficiënte manier.Beschouw berekende velden als virtuele kolommen die u toevoegt met bestaande kolommen in uw draaitabel.Het gebruik van berekende velden in de draaitabel heeft veel voordelen (zoals we later zullen zien):
   • U hoeft niet met formules te werken of brongegevens bij te werken.
   • Het is uitbreidbaar omdat het automatisch rekening houdt met nieuwe gegevens die u aan de draaitabel toevoegt.Nadat u een berekend veld hebt toegevoegd, kunt u het net als elk ander veld in een draaitabel gebruiken.
   • Het is gemakkelijk te updaten en te beheren.Als een metriek bijvoorbeeld verandert of als u een berekening moet wijzigen, kunt u dit eenvoudig vanuit de draaitabel zelf doen.

Berekende velden toevoegen aan draaitabel

Laten we eens kijken hoe u een berekend veld in een draaitabel kunt toevoegen aan een bestaande draaitabel.

Stel dat u een draaitabel heeft die er als volgt uitziet, en u wilt de winstmarge voor elke detailhandelaar berekenen:

Dit zijn de stappen om een ​​berekend veld in een draaitabel toe te voegen:

Nadat het berekende veld is toegevoegd, wordt het weergegeven als een van de velden in de lijst Draaitabelvelden.

U kunt dit berekende veld nu als elk ander draaitabelveld gebruiken (houd er rekening mee dat u een berekend draaitabelveld niet kunt gebruiken als rapportfilter of slicer).

Zoals ik eerder al zei, is het voordeel van het gebruik van een berekend veld in een draaitabel dat u de structuur van de draaitabel kunt wijzigen en deze automatisch wordt aangepast.

Als ik bijvoorbeeld de regio naar het rijgebied sleep en neerzet, krijgt u de volgende resultaten, waarbij de winstmargewaarden voor de detailhandelaar en de regio worden gerapporteerd.

 

In het bovenstaande voorbeeld heb ik een eenvoudige formule (=Winst/Verkoop) gebruikt om het berekende veld in te voegen.U kunt echter ook enkele geavanceerde formules gebruiken.

Voordat ik u een voorbeeld laat zien van het gebruik van een geavanceerde formule om een ​​berekend veld in een draaitabel te maken, zijn hier een paar dingen die u moet weten:

• U kunt geen verwijzingen of benoemde bereiken gebruiken bij het maken van berekende draaitabelvelden.Dit sluit veel formules uit, zoals VERT.ZOEKEN, INDEX, OFFSET, enz.U kunt echter formules gebruiken die werken zonder verwijzingen (bijv. SUM, IF, COUNT, etc...).
• U kunt constanten gebruiken in formules.Als u bijvoorbeeld de verwachte omzetgroei van 10% wilt weten, gebruikt u de formule =Sales*1.1 (waarbij 1.1 een constante is).
• Volg de prioriteitsvolgorde in formules die berekende velden genereren.Gebruik als best practice haakjes om ervoor te zorgen dat u de prioriteit niet hoeft te onthouden.

Laten we nu eens kijken naar een voorbeeld van het maken van een berekend veld met behulp van een geavanceerde formule.

Stel dat u de onderstaande dataset heeft en dat u de voorspelde verkopen in een draaitabel moet weergeven.

Voor prognosewaarden zou u een verkoopstijging van 300% gebruiken voor grote retailers (meer dan 5 miljoen verkopen) en een verkoopstijging van 300% voor kleine en middelgrote retailers (minder dan 10 miljoen verkopen).

Opmerking: de verkoopcijfers hier zijn nep en zijn gebruikt om de voorbeelden in deze tutorial te illustreren.

Hier is hoe het te doen:

Hiermee wordt een nieuwe kolom aan de draaitabel toegevoegd met verkoopprognosewaarden.

Probleem met berekend veld in draaitabel

Berekende velden zijn een geweldige functie die de waarde van draaitabellen echt verhoogt met veldberekeningen, terwijl alles nog steeds schaalbaar en beheersbaar blijft.

Er is echter een probleem met berekende draaitabelvelden dat u moet begrijpen voordat u ze gebruikt.

Stel dat ik een draaitabel heb zoals hieronder en ik gebruik een berekend veld om verkoopprognoses te krijgen.

Merk op dat de subtotalen en totalen onjuist zijn.

Hoewel deze voor elke detailhandelaar afzonderlijke verkoopprognosewaarden zouden moeten toevoegen, volgt deze in werkelijkheid dezelfde berekende veldformule die we hebben gemaakt.

Dus voor Zuid-Totaal, terwijl de waarde 22,824,000 zou moeten zijn, rapporteert Zuid-Totaal het ten onrechte als 22,287,000.Dit gebeurt omdat het de formule 21,225,800*1.05 gebruikt om de waarde te krijgen.

Helaas kunt u dit probleem niet oplossen.

De beste manier om hiermee om te gaan, is door de subtotalen en eindtotalen uit de draaitabel te verwijderen.

U kunt ook passeren Gepresenteerd door DebraEnkele innovatieve oplossingen om dit probleem aan te pakken.

Hoe een berekend veld in een draaitabel wijzigen of verwijderen?

Nadat u een berekend veld in een draaitabel hebt gemaakt, kunt u de formule als volgt wijzigen of verwijderen:

Hoe krijg ik een lijst van alle berekende veldformules?

Als u veel berekende draaitabelvelden maakt, hoeft u zich geen zorgen te maken over het bijhouden van de formules die in elk veld worden gebruikt.

Met Excel kunt u snel een lijst maken van alle formules die worden gebruikt om berekende velden te maken.

Hier zijn de stappen om snel een lijst te krijgen van alle berekende veldformules:

• Selecteer een cel in de draaitabel.
• Ga naar Hulpmiddelen voor draaitabellen -> Analyseren -> Velden, items en sets -> Lijstformules.

Zodra u op de lijstformule klikt, voegt Excel automatisch een nieuw werkblad in met details van alle berekende velden/items die u in de draaitabel hebt gebruikt.

Dit kan een erg handig hulpmiddel zijn als je werk naar een klant moet sturen of het moet delen met je team.