Gratis tips Excel - Draaitabellen omzetten naar formules


Tips Excel		go to : Index

Tip 194
Deze tip werd geschreven door Excel teacher

Draaitabellen omzetten naar formules
Deze tip heeft betrekking op draaitabellen gemaakt met Excel 2010 PowerPivot, Excel 2013 PowerPivot en draaitabellen gemaakt van het Gegevensmodel Excel 2013. In Excel tip 193 staat beschreven hoe we twee of meer bronnen data van verschillende samenstelling die een gemeenschappelijke sleutel hebben kunnen toevoegen aan het Gegevensmodel om draaitabellen te maken. Voordelen van het gebruik van het gegevensmodel: Een unieke aantal berekenen. Sets creëren (deze optie was al opgenomen in Excel 2010, maar alleen voor OLAP DATA). Gebruik maken van Kubusformules. Nadelen van het gebruik van het gegevensmodel: Je kan de gegevens niet meer groeperen. Bepaalde berekeningen zijn niet meer mogelijk, zoals “Product” en “Aantal nummers”. De opties “Berekende velden” en “Berekende items” zijn niet beschikbaar. Is de data die je wil verwerken niet afkomstig van Olap (OnLine Analytical Processing) databases dan heb je ook niet de mogelijkheid om Berekende metingen en Berekende Leden aan te maken. Je kan ook geen “Wat als analyse” uitvoeren. Je kan ook geen rijen of kolommen invoegen in de draaitabel.

De velden voor de draaitabel (1): Verkoopcijfers/SalesAmount naar Waarden. Kalender/MaandNr naar Rijen. Kalender/Jaar naar Kolommen. ProductSubcategory/ProductSubcategoryName naar Filters En een slicer voor Channel/ChannelName.


Dit geeft de volgende draaitabel (2)


Wanneer we nu een rijen (berekend items) of kolommen (berekend velden) willen invoegen in de draaitabel de krijgen we een melding dat dit niet toegestaan is. Om rijen of kolommen in te voegen gaan we de draaitabel eerst omzetten naar formules, we selecteren een cel in de draaitabel en op het lint gaan we naar “Hulpmiddelen voor draaitabellen/Analyseren/Olap/Converteren naar formules (3).


Heb je zoals in dit voorbeeld een rapportfilter gebruikt dan heb je de keuze om de filter al dan niet om te zetten naar formules, zet je de rapportfilter niet om dan kan je nog steeds deze gebruiken om te filteren, dus geen vinkje plaatsen bij “Rapportfilters converteren”.


Wanneer we nu een cel selecteren in de draaitabel bv de cel met de jaarwaarde “2007” en de maandwaarde “1” zien we in de formulebalk de formule: “=KUBUSWAARDE("ThisWorkbookDataModel";$C$7;$B$9;$B11;C$10;Slicer_ChannelName)” Dit is een zogenoemde kubusformule met de verwijzingen waaruit de formule is opgebouwd (4). Kubusformules worden in een van de volgende Excel tips besproken.


We kunnen nu rijen en kolommen invoegen en berekeningen uitvoeren op de omgezette draaitabel. In onderstaand voorbeeld heb ik enkele rijen en kolommen ingevoegd (groen gekleurd) en met enkele formules kwartaal, halfjaar en % van jaartotaal berekeningen gemaakt, je kan zelf één of meerdere cellen verplaatsen: Eindtotaalrij verplaatst (geel gekleurd).


GratisCursus.be denkt aan jullie.
> Index