Waarden berekenen in een draaitabel

Als u waarden wilt berekenen in een draaitabel, kunt u een of alle van de volgende berekeningsmethoden gebruiken:

• Samenvattingsfuncties in waardevelden    De gegevens in het waardengebied zijn een samenvatting van de onderliggende brongegevens in de draaitabel. De volgende brongegevens:

• Leveren de volgende draaitabellen en draaigrafieken op. Als u een draaigrafiek maakt met de gegevens in een draaitabel, geven de waarden in de draaigrafiek de berekeningen in het gekoppeld draaitabelrapport weer.

• 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 geeft waarden weer die zijn gebaseerd op 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 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 het rapport is de commissie vervolgens automatisch opgenomen in de subtotalen en eindtotalen.

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 samenvattingswaarden vooraf berekend op de OLAP-server voordat Excel de resultaten weergeeft. 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 werkbladgegevens, gebruikt Excel de samenvattingsfunctie Som voor het berekenen van de waardevelden die numerieke gegevens bevatten, en de samenvattingsfunctie Aantal voor het berekenen van gegevensvelden 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.

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:

• Elementen van draaitabelformule    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 rapporten te identificeren in formules. 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 worden toegepast op totalen, niet op afzonderlijke records    Formules voor berekende velden worden toegepast op de som van de onderliggende gegevens van de 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 elke volgorde hebben. 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 veldnamen opnemen in itemverwijzingen. De naam van het item moet tussen rechte haken staan, bijvoorbeeld Regio[Noord]. U kunt deze notatie gebruiken om te voorkomen dat #NAAM?- fouten optreden wanneer twee items in twee verschillende velden in een rapport dezelfde naam hebben. Als een rapport bijvoorbeeld een item met de naam Vlees in het veld Type bevat en een ander item met dezelfde naam in het veld Categorie, kunt u #NAAM?- fouten voorkomen door de verwijzingen te noteren als Type[Vlees] en Categorie[Vlees].

• Verwijzen naar items op positie    U kunt naar een item verwijzen op de positie in het rapport, zoals dat op dat moment is gesorteerd en opgeslagen. 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.

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.