In draaitabellen kunt u samenvattingsfuncties in waardevelden gebruiken om waarden uit de onderliggende brongegevens te combineren. Als samenvattingsfuncties en aangepaste berekeningen niet de resultaten opleveren die u zoekt, kunt u zelf formules maken in berekende velden en berekende items. U kunt bijvoorbeeld een berekend veld toevoegen met de formule voor de verkoopcommissie, die voor elke regio verschillend kan zijn. In de draaitabel wordt de commissie vervolgens automatisch opgenomen in de subtotalen en eindtotalen.
Een andere manier om te berekenen is het gebruik van Maateenheden in Power Pivot, die u maakt met behulp van een DAX-formule (Data Analysis Expressions). Zie Een meting maken in Power Pivot voor meer informatie.
Draaitabellen bieden manieren voor het berekenen van gegevens. Informatie over de beschikbare berekeningsmethoden, hoe berekeningen worden beïnvloed door de soorten brongegevens, en het gebruiken van formules in draaitabellen en draaigrafieken.
Beschikbare berekeningsmethoden
Als u waarden wilt berekenen in een draaitabel, kunt u een of alle van de volgende berekeningsmethoden gebruiken:
Overzichtsfuncties in waardevelden De gegevens in het waardengebied bevatten een overzicht van de onderliggende brongegevens in de draaitabel. De volgende brongegevens:
Leveren de volgende draaitabellen en draaigrafieken op. Als u een draaigrafiek maakt op basis van de gegevens in een draaitabel, weerspiegelen de waarden in die draaigrafiek de berekeningen in het bijbehorende draaitabelrapport.
In de draaitabel levert het kolomveld Maand de items Maart en April. Het rijveld Regio levert de items Noord, Zuid, Oost en West. De waarde op het snijpunt van de kolom April en de rij Noord is de totale verkoopopbrengst van de records in de brongegevens die een Maand-waarde April en een Regio-waarde Noord hebben.
In een draaigrafiek kan het veld Regio een categorieveld zijn met de categorieën Noord, Zuid, Oost en West. Het veld Maand kan een reeksveld zijn dat de items Maart, April en Mei weergeeft als een reeks in de legenda. Een waardenveld genaamd Totale verkoop kan de gegevensmarkeringen bevatten die de totale opbrengst per maand en per regio weergeven. Een gegevensmarkering kan bijvoorbeeld op basis van de positie op de verticale as (waarde) de totale verkoop weergeven voor de maand april in de regio Noord.
Voor het berekenen van de waardevelden zijn de volgende samenvattingsfuncties beschikbaar voor alle soorten brongegevens, met uitzondering van Online Analytical Processing-brongegevens (OLAP).
Functie Samenvatting van Som De som van de waarden. Dit is de standaardfunctie voor numerieke gegevens. Aantal Het aantal gegevenswaarden. De samenvattingsfunctie Aantal werkt op dezelfde manier als de functie AANTALARG. Aantal is de standaardfunctie voor gegevens die geen getal zijn. Gemiddelde Het gemiddelde van de waarden. Max De grootste waarde. Min De kleinste waarde. Product Het product van de waarden. Aantal nrs Het aantal gegevenswaarden die een getal zijn. De samenvattingsfunctie Aantal nrs werkt op dezelfde manier als de functie AANTAL. StDev De geschatte standaarddeviatie van een populatie, waarbij de steekproef een subset van de gehele populatie omvat. Stdevp De standaarddeviatie van een populatie, waarbij de populatie wordt gevormd door alle samen te vatten gegevens. Var De geschatte variantie van een populatie, waarbij de steekproef een subset van de gehele populatie omvat. Varp De variantie van een populatie, waarbij de populatie wordt gevormd door alle samen te vatten gegevens.
Aangepaste berekeningen Een aangepaste berekening toont waarden op basis van andere items of cellen in het gegevensgebied. U kunt bijvoorbeeld waarden weergeven in het gegevensveld Totale verkoop als een percentage van de verkoop in maart, of als een voorlopig totaal van de items in het veld Maand.
De volgende functies zijn beschikbaar voor aangepaste berekeningen in waardevelden.Functie Resultaat Geen berekening Geeft de waarde weer die is ingevoerd in het veld. % van eindtotaal Geeft de waarden weer als een percentage van het eindtotaal van alle gegevens of gegevenspunten in het rapport. % van kolomtotaal Geeft alle waarden in elke kolom of reeks weer als een percentage van het totaal voor de kolom of reeks. % van rijtotaal Geeft de waarde in elke rij of categorie weer als een percentage van het totaal voor de rij of categorie. % van Geeft waarden weer als een percentage van de waarde van het basisonderdeel in het basisveld. % van totaal van bovenliggende rij Berekent waarden als volgt:
(waarde van het item) / (waarde van het bovenliggende item in rijen)% van totaal van bovenliggende kolom Berekent waarden als volgt:
(waarde van het item) / (waarde van het bovenliggende item in kolommen)% van bovenliggend totaal Berekent waarden als volgt:
(waarde van het item) / (waarde van het bovenliggende item van het geselecteerde basisveld)Verschil met Geeft waarden weer als het verschil met de waarde van het basisonderdeel in het basisveld. % verschil met Geeft waarden weer als het percentageverschil met de waarde van het basisonderdeel in het basisveld. Voorlopig totaal in Geeft de waarde voor opeenvolgende items in het basisveld weer als een voorlopig totaal. % voorlopig totaal in Berekent de waarde voor opeenvolgende items in het gekozen basisveld die worden weergegeven als een voorlopig totaal als een percentage. Positie van klein naar groot Geeft de rang van geselecteerde waarden in een bepaald veld weer, waarbij het kleinste item in het veld als 1 wordt vermeld en elke grotere waarde een hogere rangwaarde krijgt. Positie van groot naar klein Geeft de rang van geselecteerde waarden in een bepaald veld weer, waarbij het grootste item in het veld als 1 wordt vermeld en elke kleinere waarde een hogere rangwaarde krijgt. Index Berekent waarden als volgt:
((waarde in cel) x (eindtotaal van eindtotalen)) / ((totaal van rijen) x (totaal van kolommen))
- Formules Als samenvattingsfuncties en aangepaste berekeningen niet de gewenste resultaten opleveren, kunt u uw eigen formules maken in berekende velden en berekende items. U kunt bijvoorbeeld een berekend veld toevoegen met de formule voor de verkoopcommissie, die voor elke regio verschillend kan zijn. In het rapport is de commissie vervolgens automatisch opgenomen in de subtotalen en eindtotalen.
De invloed van het type brongegevens op berekeningen
De berekeningen en opties die beschikbaar zijn in een rapport, zijn afhankelijk van of de brongegevens afkomstig zijn uit een OLAP-database of een niet-OLAP-database.
-
Berekeningen op basis van OLAP-brongegevens Voor draaitabellen die zijn gemaakt op basis van OLAP-kubussen, worden de samengevatte waarden vooraf berekend op de OLAP-server voordat de resultaten worden weergegeven. De manier waarop deze vooraf berekende waarden worden berekend in de draaitabel kunt u niet wijzigen. U kunt bijvoorbeeld niet de samenvattingsfunctie wijzigen die wordt gebruikt voor het berekenen van gegevensvelden of subtotalen, en u kunt geen berekende velden of items toevoegen.
Als de OLAP-server berekende velden levert, ook wel berekende leden genoemd, worden deze velden weergegeven in de lijst met velden van de draaitabel. Ook worden berekende velden en berekende items weergegeven die zijn gemaakt met macro’s geschreven in Visual Basic for Applications (VBA) en die zijn opgeslagen in uw werkmap. U kunt deze velden of items echter niet wijzigen. Als u meer typen berekeningen nodig hebt, neemt u contact op met de beheerder van de OLAP-database.
Voor OLAP-brongegevens kunt u de waarden van verborgen items opnemen of uitsluiten bij het berekenen van subtotalen en eindtotalen. - Berekeningen op basis van niet-OLAP-brongegevens In draaitabellen die zijn gebaseerd op andere typen externe gegevens of op werkbladgegevens, gebruikt Excel de samenvattingsfunctie Som om waardevelden te berekenen die numerieke gegevens bevatten en de samenvattingsfunctie Aantal om gegevensvelden te berekenen die tekst bevatten. Voor het verder analyseren en aanpassen van uw gegevens kunt u ook kiezen uit andere samenvattingsfuncties, bijvoorbeeld Gemiddelde, Max of Min. Daarnaast kunt u uw eigen formules maken met elementen uit het rapport of andere werkbladgegevens, door een berekend veld of berekend item te maken in een veld.
Formules gebruiken in draaitabellen
U kunt alleen formules maken in rapporten die zijn gebaseerd op niet-OLAP-brongegevens. U kunt geen formules gebruiken in rapporten die zijn gebaseerd op een OLAP-database. Wanneer u formules gebruikt in draaitabellen, moet u zich bewust zijn van de volgende regels voor formulesyntaxis en het gedrag van formules:
Draaitabelformule-elementen In formules die u maakt voor berekende velden en berekende items, kunt u operatoren en expressies gebruiken zoals in andere werkbladformules. U kunt constanten gebruiken en verwijzen naar gegevens in het rapport, maar u kunt geen celverwijzingen of gedefinieerde namen gebruiken. U kunt geen werkbladfuncties gebruiken die celverwijzingen of gedefinieerde namen vereisen als argumenten, en u kunt ook geen matrixfuncties gebruiken.
Veld- en itemnamen Excel gebruikt veld- en itemnamen om die elementen van een rapport in uw formules te identificeren. In het volgende voorbeeld maken de gegevens in bereik C3:C9 gebruik van de veldnaam Zuivel. Een berekend item in het veld Type, dat een schatting maakt van de verkoop op basis van de zuivelverkoop, kan een formule gebruiken zoals =Zuivel * 115%.
Opmerking
In een draaigrafiek worden de veldnamen weergegeven in de lijst met velden van de draaitabel, en worden de items weergegeven in elke vervolgkeuzelijst. Dit zijn niet dezelfde namen als in de tips voor grafieken, die de namen van reeksen en gegevenspunten weergeven.
Formules werken op somtotalen, niet op afzonderlijke records Formules voor berekende velden werken op de som van de onderliggende gegevens voor velden in de formule. Bijvoorbeeld: de formule voor het berekende veld =Verkoop * 1,2 vermenigvuldigt de som van de verkoop voor elk type en elke regio met 1,2. Wat de formule niet doet, is elke afzonderlijke verkoop vermenigvuldigen met 1,2 en vervolgens de vermenigvuldigde bedragen optellen.
Formules voor berekende items worden toegepast op afzonderlijke records. Bijvoorbeeld: de formule voor het berekende item =Zuivel *115% vermenigvuldigt elke afzonderlijke verkoop van Zuivel met 115%, waarna de vermenigvuldigde bedragen worden samengevat in het gebied Waarden.Spaties, getallen en symbolen in namen In een naam die meer dan één veld bevat, kunnen de velden in elke volgorde staan. In het bovenstaande voorbeeld kunnen de cellen C6:D6 zowel ‘April Noord’ zijn als ‘Noord April’. Gebruik enkele aanhalingstekens om namen die uit meer dan een woord bestaan of die getallen of symbolen bevatten.
Totalen Formules kunnen niet verwijzen naar totalen, zoals Totaal maart, Totaal april of Eindtotaal in het voorbeeld.
Veldnamen in itemverwijzingen U kunt de veldnaam opnemen in een verwijzing naar een item. De naam van het item moet tussen rechte haken staan, bijvoorbeeld Regio[Noord]. Gebruikt u deze indeling om #NAME te voorkomen? fouten wanneer twee items in twee verschillende velden in een rapport dezelfde naam hebben. Als een rapport bijvoorbeeld een item met de naam Vlees bevat in het veld Type en een ander item met de naam Vlees in het veld Categorie, kunt u voorkomen dat #NAME? door te verwijzen naar de items als Type[Vlees] en Categorie[Vlees].
Naar items verwijzen per positie U kunt naar een item verwijzen op de positie in het rapport zoals die momenteel is gesorteerd en weergegeven. Type[1] is Zuivel en Type[2] is Schaal- en schelpdieren. Het item waarnaar op deze manier wordt verwezen kan veranderen wanneer de positie van items wordt gewijzigd of wanneer andere items worden weergegeven of verborgen. Verborgen items worden niet meegeteld in deze index.
U kunt relatieve posities gebruiken om naar items te verwijzen. De posities worden bepaald ten opzichte van het berekende item dat de formule bevat. Als Zuid de huidige regio is, is Regio[-1]Noord; als Noord de huidige regio is, is Regio[+1]Zuid. Een berekend item kan bijvoorbeeld de formule =Regio[-1] * 3% gebruiken. Als de positie die u opgeeft vóór het eerste item of na het laatste item in het veld ligt, geeft de formule als resultaat een #VERW!- fout.
Formules gebruiken in draaigrafieken
Als u formules wilt gebruiken in een draaigrafiek, maakt u de formules in de gekoppelde draaitabel, omdat u daarin de afzonderlijke waarden kunt zien waaruit uw gegevens bestaan. De resultaten worden vervolgens grafisch weergegeven in de draaigrafiek.
In de volgende draaigrafiek worden bijvoorbeeld de verkopen van elke verkoper per regio weergegeven:
Als u wilt zien wat de verkoopcijfers zijn bij een stijging van 10 procent, kunt u een berekend veld maken in de gekoppelde draaitabel met een formule zoals =Verkoop * 110%.
Het resultaat wordt direct weergegeven in de draaigrafiek, zoals hieronder wordt weergegeven:
Als u aparte gegevensmarkeringen wilt zien voor verkopen in regio Noord minus de transportkosten van 8 procent, kunt u een berekend item maken in het veld Regio met een formule zoals =Noord - (Noord *8%).
Het resultaat daarvan is de volgende grafiek:
Een berekend item dat wordt gemaakt in het veld Verkoper wordt echter in de legenda weergegeven als een reeks, en in de grafiek als een gegevenspunt in elke categorie.
Meer hulp nodig?
U kunt altijd een expert in de Excel Tech Community vragen of ondersteuning krijgen in community's.