Meerdere resultaten berekenen met een gegevenstabel

In Microsoft Excel maken gegevenstabellen deel uit van een reeks opdrachten die bekend staat als What-If analysehulpprogramma's. Wanneer je gegevenstabellen gebruikt, voer je een what-if-analyse uit.

Bij een wat-als-analyse worden de waarden in cellen gewijzigd om te bekijken hoe deze wijzigingen het resultaat van formules op het werkblad beïnvloeden. Je kunt bijvoorbeeld een gegevenstabel gebruiken om het rentepercentage en de duur van een lening te variëren om potentiële maandelijkse betalingsbedragen te evalueren.

Typen wat-als-analyse    

Excel bevat drie typen hulpmiddelen voor what-if-analyses: scenario's, gegevenstabellen en Doelzoeken. Bij scenario's en gegevenstabellen wordt op basis van sets met invoerwaarden bekeken wat de mogelijke resultaten zijn. Doelzoeken is duidelijk verschillend, het maakt gebruik van één resultaat en berekent mogelijke invoerwaarden die dat resultaat zouden opleveren.

Net zoals met scenario's kunt u met gegevenstabellen een aantal mogelijke resultaten verkennen. Maar in tegenstelling tot scenario's worden bij gegevenstabellen alle resultaten in één tabel op één werkblad weergegeven. Met gegevenstabellen kunt u een aantal mogelijkheden in één oogopslag bekijken. Omdat u zich op slechts één of twee variabelen richt, zijn resultaten eenvoudig te lezen en te delen in tabelvorm.

Een gegevenstabel kan maximaal twee variabelen bevatten. Als u meer dan twee variabelen wilt analyseren, gebruikt u in plaats hiervan scenario's. Hoewel een gegevenstabel beperkt is tot een of twee variabelen (een voor de invoercel in een rij en een voor de invoercel in een kolom), kan de tabel zoveel variabele waarden bevatten als je wilt. Een scenario kan maximaal 32 verschillende waarden bevatten, maar u kunt zo veel scenario's maken als u wilt.

Meer informatie in het artikel Inleiding tot What-If-analyse.

Maak gegevenstabellen met één of twee variabelen, afhankelijk van het aantal variabelen en formules dat je moet testen.

Gegevenstabellen met één variabele    

Gebruik een gegevenstabel met één variabele als u wilt zien welk effect verschillende waarden van een variabele in een of meer formules hebben op de resultaten van deze formules. Je kunt bijvoorbeeld in een gegevenstabel met één variabele de functie BET gebruiken om te bekijken wat het effect van verschillende rentepercentages is op het maandelijkse aflossingsbedrag voor een hypotheek. U voert de variabele waarden in een kolom of rij in en de resultaten worden in een kolom of rij ernaast weergegeven.

In de volgende afbeelding bevat cel D2 de betalingsformule =BET(B3/12,B4,-B5), die verwijst naar de invoercel B3.

Gegevenstabellen met twee variabelen    

Gebruik een gegevenstabel met twee variabelen als u wilt zien welk effect verschillende waarden van twee variabelen in een formule hebben op de resultaten van deze formule. U kunt bijvoorbeeld een gegevenstabel met twee variabelen gebruiken om te bekijken wat het effect van verschillende combinaties van rentepercentages en het aantal termijnen is op het maandelijkse aflossingsbedrag voor een hypotheek.

In de volgende afbeelding bevat cel C2 de betalingsformule, =BET(B3/12,B4,-B5), waarvoor de twee invoercellen B3 en B4 worden gebruikt.

 

Gegevenstabelberekeningen    

Wanneer een werkblad opnieuw wordt berekend, worden alle gegevenstabellen ook opnieuw berekend, zelfs als er geen wijziging in de gegevens is aangebracht. Als je de berekening van een werkblad met een gegevenstabel wilt versnellen, kun je de opties Berekening wijzigen om het werkblad, maar niet de gegevenstabellen automatisch opnieuw te berekenen. Zie de sectie Berekening versnellen in een werkblad met gegevenstabellen voor meer informatie.

Een gegevenstabel met één variabele bevat invoerwaarden die onder elkaar in een kolom (kolomgeoriënteerd) of naast elkaar in een rij (rijgeoriënteerd) staan. Elke formule in een gegevenstabel met één variabele mag slechts naar één invoercel verwijzen.

Voer de volgende stappen uit:

1. Typ de lijst met gewenste waarden die je in de invoercel wilt vervangen onder elkaar in een kolom of naast elkaar in een rij. Laat een paar rijen en kolommen leeg aan beide zijden van de waarden.

2. Ga op een van de volgende manieren te werk:

   • Als de gegevenstabel kolomgeoriënteerd is (de variabelewaarden bevinden zich in een kolom), typ je de formule in de cel één rij boven en één cel rechts van de kolom met waarden. De gegevenstabel met één variabele in de afbeelding in het gedeelte Overzicht is kolomgeoriënteerd en de formule staat in cel D2.
     
     
     
     Als je de effecten van verschillende waarden op andere formules wilt onderzoeken, voer je de extra formules in cellen in rechts van de eerste formule.

   • Als de gegevenstabel rijgeoriënteerd is (de variabelewaarden bevinden zich in een rij), typ je de formule in de cel één kolom links van de eerste waarde en één cel onder de rij met waarden.
     
     Als je de effecten van verschillende waarden op andere formules wilt onderzoeken, voer je de aanvullende formules in cellen hieronder de eerste formule in.

3. Selecteer het celbereik met de formules en waarden die u wilt vervangen. In de bovenstaande afbeelding is dit bereik C2:D5.

4. Selecteer  op het tabblad DataWhat-if-analyse >Gegevenstabel (in de Data Tools groep of Forecast groep van Excel 2016).

5. Ga op een van de volgende manieren te werk:

   • Als de gegevenstabel kolomgeoriënteerd is, voer je de celverwijzing voor de invoercel in het veld Kolominvoercel in. In de bovenstaande afbeelding is de invoercel B3.

   • Als de gegevenstabel rijgeoriënteerd is, voer je de celverwijzing voor de invoercel in het veld Rijinvoercel in.

     Opmerking: Wanneer u de gegevenstabel hebt gemaakt, wilt u de opmaak van de resultaatcellen mogelijk wijzigen. In de afbeelding worden de resultaatcellen opgemaakt als valuta.

Formules in een gegevenstabel met één variabele moeten naar dezelfde invoercel verwijzen.

Voer de volgende stappen uit

1. Ga als volgt te werk:

   • Als de gegevenstabel kolomgeoriënteerd is, voert u de nieuwe formule in een lege cel rechts van een bestaande formule in de bovenste rij van de gegevenstabel in.

   • Als de gegevenstabel rijgeoriënteerd is, voer je de nieuwe formule in in een lege cel onder een bestaande formule in de eerste kolom van de gegevenstabel.

2. Selecteer het celbereik dat de gegevenstabel en de nieuwe formule bevat.

3. Selecteer  op het tabblad DataWhat-If Analysis > Data Table (in de Data Tools groep of Forecast groep van Excel 2016).

4. Ga op een van de volgende manieren te werk:

   • Als de gegevenstabel kolomgeoriënteerd is, voer je de celverwijzing voor de invoercel in het vak Kolominvoercel in.

   • Als de gegevenstabel rijgeoriënteerd is, voer je de celverwijzing voor de invoercel in het vak Rij-invoercel in.

In een gegevenstabel met twee variabelen wordt een formule gebruikt die twee lijsten met invoerwaarden bevat. In de formule moet naar twee verschillende invoercellen worden verwezen.

Voer de volgende stappen uit:

1. Voer in een cel op het werkblad de formule in die verwijst naar de twee invoercellen.

   In het volgende voorbeeld—waarin de beginwaarden van de formule worden ingevoerd in de cellen B3, B4 en B5, typt u de formule =PMT(B3/12,B4,-B5) in cel C2.

2. Typ een lijst met invoerwaarden in dezelfde kolom, onder de formule.

   In dit geval typt u de verschillende rentepercentages in cel C3, C4 en C5.

3. Typ de tweede lijst in dezelfde rij als de formule, rechts hiervan.

   Typ het aantal leentermijnen (in maanden) in cel D2 en E2.

4. Selecteer het celbereik dat de formule bevat (C2), de rij en kolom met waarden (C3:C5 and D2:E2) en de cellen waarin u de berekende waarden wilt weergeven (D3:E5).

   In dit geval selecteert u het bereik C2:E5.

5. Selecteer op het tabblad Gegevens in de groep Hulpmiddelen voor gegevens of de groep Prognose (in Excel 2016 ) de optie Wat-als-analyse > Gegevenstabel (in de groep Hulpmiddelen voor gegevens of de groep Prognose van Excel 2016 ).

6. Voer in het vak Rij-invoercel de verwijzing naar de invoercel in voor de invoerwaarden in de rij.
   Typ cel B4 in het Rij invoercel vak.

7. Voer in het vak Kolominvoercel de verwijzing naar de invoercel in voor de invoerwaarden in de rij.
   Typ B3 in het Kolom invoercel vak.

8. Selecteer OK.

Voorbeeld van een gegevenstabel met twee variabelen

In een gegevenstabel met twee variabelen kan worden aangegeven hoe verschillende combinaties van rentepercentages en aantal termijnen van invloed zijn op de maandelijkse hypotheekbetaling. In dit figuur bevat cel C2 de betalingsformule, =BET(B3/12,B4,-B5), waarvoor de twee invoercellen B3 en B4 worden gebruikt.

Wanneer je deze berekeningsoptie instelt, worden er geen gegevenstabelberekeningen uitgevoerd wanneer een herberekening voor de hele werkmap wordt uitgevoerd. Als u de gegevenstabel handmatig opnieuw wilt berekenen, selecteert u de bijbehorende formules en drukt u op F9.

Volg deze stappen om de berekeningsprestaties te verbeteren:

1. Selecteer Bestand > Opties > Formules.

2. Selecteer in de sectie Berekeningsoptiesde optie Automatisch.

   Tip: Selecteer eventueel op het tabblad Formules de pijl bij Berekeningsopties en selecteer vervolgens Automatisch.

Je kunt een aantal andere Excel-hulpprogramma's gebruiken om wat-als-analyses uit te voeren als je specifieke doelen of grotere sets variabele gegevens hebt.

Doelzoeken

Als je weet welk resultaat een formule moet opleveren maar je niet zeker weet welke invoerwaarde de formule nodig heeft om dat resultaat te verkrijgen, gebruikt je de functie Doelzoeken. Zie het artikel Doel zoeken gebruiken om het gewenste resultaat te vinden door een invoerwaarde aan te passen.

Excel Oplosser

Je kunt de invoegtoepassing Excel Oplosser gebruiken om de optimale waarde voor een set invoervariabelen te vinden. Oplosser werkt met een groep cellen (beslissingsvariabelen of gewoon variabele cellen genoemd) die worden gebruikt bij het berekenen van de formules in de doel- en beperkingscellen. De waarden in de beslissingsvariabelecellen worden aangepast op basis van de limieten voor randvoorwaardencellen en het gewenste resultaat voor de doelfunctiecel wordt geproduceerd. Meer informatie in dit artikel: Een probleem definiëren en oplossen met behulp van Oplosser.