✅ Een berekend veld met een Excel-draaitabel toevoegen en gebruiken

Inhoudsopgave

Wat is een berekend veld in een draaitabel?
Een berekend veld toevoegen aan de draaitabel
Een probleem met door de draaitabel berekende velden
Hoe een berekend veld in een draaitabel wijzigen of verwijderen?
Hoe krijg ik een lijst van alle berekende veldformules?

Als u eenmaal een draaitabel heeft gemaakt, moet u uw analyse vaak uitbreiden en meer gegevens/berekeningen als onderdeel ervan opnemen.

Als u een nieuw gegevenspunt nodig hebt dat kan worden verkregen door bestaande gegevenspunten in de draaitabel te gebruiken, hoeft u niet terug te gaan en het aan de brongegevens toe te voegen. In plaats daarvan kunt u een Berekend veld in draaitabel om dit te doen.

Download de dataset en volg mee.

Wat is een berekend veld in een draaitabel?

Laten we beginnen met een eenvoudig voorbeeld van een draaitabel.

Stel je hebt een dataset van retailers en je maakt een draaitabel zoals hieronder weergegeven:

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

Wat als u nu ook wilt weten wat de winstmarge van deze retailers was (waarbij de winstmarge 'Winst' is gedeeld door 'Verkoop').

Er zijn een aantal manieren om dit te doen:

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 u dit doet, moet u de brongegevens van de draaitabel bijwerken om deze nieuwe kolom als onderdeel ervan te krijgen.
- Hoewel deze methode een mogelijkheid is, moet u handmatig teruggaan naar de gegevensset en de berekeningen maken. U moet bijvoorbeeld mogelijk nog een kolom toevoegen om de gemiddelde verkoop per eenheid (verkoop/hoeveelheid) te berekenen. Nogmaals, u moet deze kolom aan uw brongegevens toevoegen en vervolgens de draaitabel bijwerken.
- Deze methode laat ook uw draaitabel opzwellen terwijl u er nieuwe gegevens aan toevoegt.
Voeg berekeningen toe buiten de draaitabel. Dit kan een optie zijn als de structuur van uw draaitabel waarschijnlijk niet zal veranderen. Maar als u de draaitabel wijzigt, wordt de berekening mogelijk niet dienovereenkomstig bijgewerkt en krijgt u mogelijk verkeerde resultaten of fouten. Zoals hieronder weergegeven, heb ik de winstmarge berekend als er retailers in de rij waren. Maar toen ik het veranderde van klanten naar regio's, gaf de formule een fout.
Een berekend veld met een draaitabel gebruiken. Dit is de meest efficiënte manier om bestaande draaitabelgegevens te gebruiken en de gewenste metriek te berekenen. Beschouw Berekend veld als een virtuele kolom die u hebt toegevoegd met behulp van de bestaande kolommen uit de draaitabel. Het gebruik van een berekend veld met draaitabel heeft veel voordelen (zoals we zo zullen zien):
- U hoeft geen formules te hanteren of brongegevens bij te werken.
- Het is schaalbaar omdat het automatisch rekening houdt met nieuwe gegevens die u aan uw draaitabel toevoegt. Nadat u een veld Berekenen heeft toegevoegd, kunt u dit net als elk ander veld in uw draaitabel gebruiken.
- Het is gemakkelijk te updaten en te beheren. Als de metrische gegevens bijvoorbeeld veranderen of als u de berekening moet wijzigen, kunt u dat eenvoudig vanuit de draaitabel zelf doen.

Een berekend veld toevoegen aan de draaitabel

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

Stel dat u een draaitabel heeft zoals hieronder weergegeven en dat u de winstmarge voor elke detailhandelaar wilt berekenen:

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

Selecteer een cel in de draaitabel.
Ga naar Hulpmiddelen voor draaitabellen -> Analyseren -> Berekeningen -> Velden, items en sets.
Selecteer in de vervolgkeuzelijst Berekend veld.
In het dialoogvenster Berekend bestand invoegen:
- Geef het een naam door het in het veld Naam in te voeren.
- Maak in het veld Formule de formule die u voor het berekende veld wilt gebruiken. Merk op dat u kunt kiezen uit de veldnamen die eronder staan vermeld. In dit geval is de formule ‘= Winst/Omzet’. U kunt de veldnamen handmatig invoeren of dubbelklikken op de veldnaam in het vak Velden.
Klik op Toevoegen en sluit het dialoogvenster.

Zodra u het berekende veld toevoegt, wordt het weergegeven als een van de velden in de lijst Draaitabelvelden.

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

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

Als ik bijvoorbeeld de regio in het rijengebied versleep en neerzet, krijgt u het resultaat zoals hieronder weergegeven, waarbij de waarde van de winstmarge wordt gerapporteerd voor zowel retailers als de regio.

In het bovenstaande voorbeeld heb ik een eenvoudige formule (=Winst/Verkoop) gebruikt om een berekend 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 draaitabelberekeningsveld te maken, zijn hier enkele dingen die u moet weten:

U KUNT GEEN verwijzingen of benoemde bereiken gebruiken tijdens het maken van een berekend veld met draaitabel. Dat zou veel formules uitsluiten, zoals VERT.ZOEKEN, INDEX, OFFSET, enzovoort. U kunt echter formules gebruiken die zonder verwijzingen kunnen werken (zoals SUM, IF, COUNT, enzovoort…).
U kunt een constante in de formule gebruiken. Als u bijvoorbeeld de voorspelde verkoop wilt weten waarvan wordt voorspeld dat deze met 10% zal groeien, kunt u de formule =Verkoop*1.1 gebruiken (waarbij 1,1 constant is).
De volgorde van prioriteit wordt gevolgd in de formule die het berekende veld maakt. Gebruik als best practice haakjes om ervoor te zorgen dat u de volgorde van prioriteit niet hoeft te onthouden.

Laten we nu een voorbeeld bekijken van het gebruik van een geavanceerde formule om een berekend veld te maken.

Stel dat u de dataset heeft zoals hieronder weergegeven en dat u de voorspelde verkoopwaarde moet weergeven in de draaitabel.

Voor de voorspelde waarde moet u een omzetstijging van 5% gebruiken voor grote retailers (omzet boven 3 miljoen) en een omzetstijging van 10% voor kleine en middelgrote retailers (omzet onder 3 miljoen).

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

Hier is hoe dit te doen:

Selecteer een cel in de draaitabel.
Ga naar Hulpmiddelen voor draaitabellen -> Analyseren -> Berekeningen -> Velden, items en sets.
Selecteer in de vervolgkeuzelijst Berekend veld.
In het dialoogvenster Berekend bestand invoegen:
- Geef het een naam door het in het veld Naam in te voeren.
- Gebruik in het veld Formule de volgende formule: =IF(Region =”Zuid”,Sales *1,05,Sales *1.1)
Klik op Toevoegen en sluit het dialoogvenster.

Dit voegt een nieuwe kolom toe aan de draaitabel met de verkoopprognosewaarde.

Klik hier om de dataset te downloaden.

Een probleem met door de draaitabel berekende velden

Berekend veld is een geweldige functie die de waarde van uw draaitabel echt verbetert met veldberekeningen, terwijl alles toch schaalbaar en beheersbaar blijft.

Er is echter een probleem met berekende velden in draaitabel die u moet kennen voordat u deze gebruikt.

Stel dat ik een draaitabel heb zoals hieronder weergegeven, waar ik het berekende veld heb gebruikt om de prognoseverkoopcijfers te krijgen.

Merk op dat het subtotaal en de eindtotalen niet correct zijn.

Hoewel deze de individuele verkoopprognosewaarde voor elke detailhandelaar 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 het 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 op geen enkele manier corrigeren.

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

U kunt ook enkele innovatieve oplossingen gebruiken die Debra heeft laten zien om dit probleem aan te pakken.

Hoe een berekend veld in een draaitabel wijzigen of verwijderen?

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

Selecteer een cel in de draaitabel.
Ga naar Hulpmiddelen voor draaitabellen -> Analyseren -> Berekeningen -> Velden, items en sets.
Selecteer in de vervolgkeuzelijst Berekend veld.
Klik in het veld Naam op de vervolgkeuzepijl (kleine pijl naar beneden aan het einde van het veld).
Selecteer in de lijst het berekende veld dat u wilt verwijderen of wijzigen.
Wijzig de formule als u deze wilt wijzigen of klik op Verwijderen als u deze wilt 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 formule die in elk ervan wordt gebruikt.

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

Dit zijn de stappen om snel de lijst met formules voor alle berekende velden te krijgen:

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

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