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 u gegevenstabellen maakt en analyseert, doet u een wat-als-analyse.

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. U kunt bijvoorbeeld een gegevenstabel gebruiken om het rentepercentage en de duur van de looptijd van een lening te variëren om potentiële maandelijkse betalingsbedragen te evalueren.

Typen wat-als-analyse    

Er zijn drie typen hulpprogramma's voor wat-als-analyse in Excel: scenario's, gegevenstabellen en doelzoeken. Scenario's en gegevenstabellen gebruiken sets invoerwaarden om mogelijke resultaten te berekenen. 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. In tegenstelling tot scenario's worden in 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 deze is beperkt tot slechts één of twee variabelen (één voor de rijinvoercel en één voor de kolominvoercel), kan een gegevenstabel zoveel verschillende variabelewaarden bevatten als u 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 u wilt 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. U kunt bijvoorbeeld een gegevenstabel met één variabele gebruiken om te zien hoe verschillende rentevoeten van invloed zijn op een maandelijkse hypotheekbetaling met behulp van de functie PMT. 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 =PMT(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 =PMT(B3/12;B4;-B5), waarbij 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 u de berekening van een werkblad met een gegevenstabel wilt versnellen, kunt u de berekeningsopties wijzigen om het werkblad automatisch opnieuw te berekenen, maar niet de gegevenstabellen. Zie de sectie Berekening versnellen in een werkblad met gegevenstabellen voor meer informatie.

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

Voer de volgende stappen uit:

1. Typ de lijst met waarden die u wilt vervangen in de invoercel, één kolom omlaag of één rij. Laat een paar lege rijen en kolommen aan weerszijden van de waarden staan.

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

   • Als de gegevenstabel kolomgeoriënteerd is (de variabelewaarden bevinden zich in een kolom), typt u de formule in de cel één rij boven en één cel rechts van de kolom met waarden. Deze gegevenstabel met één variabele is kolomgeoriënteerd en de formule bevindt zich in cel D2.
     
     
     
     Als u de effecten van verschillende waarden op andere formules wilt onderzoeken, voert u de extra formules in cellen rechts van de eerste formule in.

   • Als de gegevenstabel rijgeoriënteerd is (de variabelewaarden bevinden zich in een rij), typt u de formule in de cel één kolom links van de eerste waarde en één cel onder de rij met waarden.
     
     Als u de effecten van verschillende waarden op andere formules wilt onderzoeken, voert u de extra formules in cellen onder 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. Klik op het tabblad Gegevens op Wat-als-analyse > Gegevenstabel (in de groep Hulpmiddelen voor gegevens of de groep Prognose van Excel 2016 ).

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

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

   • Als de gegevenstabel rijgeoriënteerd is, voert u 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, voert u 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. Klik op het tabblad Gegevens op Wat-als-analyse > Gegevenstabel (in de groep Hulpmiddelen voor gegevens of de groep Prognose van Excel 2016 ).

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

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

   • Als de gegevenstabel rijgeoriënteerd is, voert u 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. Voer de tweede lijst in dezelfde rij in als de formule: rechts ervan.

   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. Klik op het tabblad Gegevens in de groep Hulpmiddelen voor gegevens of prognose (in Excel 2016 ) op Wat-als-analyse > Gegevenstabel (in de groep Hulpmiddelen voor gegevens of prognose van Excel 2016 ).

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

7. Voer in het veld Kolominvoercel de verwijzing naar de invoercel in voor de invoerwaarden in de kolom.
   Typ B3 in het celvak Kolominvoer .

8. Klik op 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 de afbeelding hier bevat cel C2 de betalingsformule =PMT(B3/12;B4;-B5), waarbij twee invoercellen, B3 en B4, worden gebruikt.

Wanneer u 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. Klik op Bestand > Opties > formules.

2. Klik in de sectie Berekeningsopties onder Berekenen op Automatisch, met uitzondering van gegevenstabellen.

   Tip: U kunt desgewenst op het tabblad Formules op de pijl bij Berekeningsopties klikken en vervolgens op Automatisch behalve gegevenstabellen (in de groep Berekening).

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

Doelzoeken

Als u het resultaat weet dat u van een formule kunt verwachten, maar niet precies weet welke invoerwaarde de formule nodig heeft om dat resultaat te krijgen, gebruikt u de functie Goal-Seek. Zie het artikel Doelzoeken gebruiken om het gewenste resultaat te vinden door een invoerwaarde aan te passen.

Excel Oplosser

U 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.