Meerdere resultaten berekenen met een gegevenstabel

In Microsoft Excel maken gegevenstabellen deel uit van een reeks opdrachten die bekend staan What-If analysehulpmiddelen. Wanneer u gegevenstabellen maakt en analyseert, doet u 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 looptijd van een lening te variëren, om mogelijke maandelijkse betalingsbedragen te evalueren.

Typen wat-als-analyse    

Er zijn drie typen wat-als-analysehulpmiddelen in Excel: scenario's, gegevenstabellenen doelzoekprogramma's. Scenario's en gegevenstabellen gebruiken sets met invoerwaarden om mogelijke resultaten te berekenen. Doelzoeker is duidelijk anders, gebruikt éé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 weergeven. 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 het beperkt is tot slechts één of twee variabelen (een 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 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. U kunt bijvoorbeeld een gegevenstabel met één variabele gebruiken om te zien hoe verschillende rentepercentages van invloed zijn op een maandelijkse hypotheekbetaling met 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),waarin twee invoercellen, B3 en B4, worden gebruikt.

 

Gegevenstabelberekeningen    

Wanneer een werkblad opnieuw wordt berekend, worden gegevenstabellen ook herberekend, zelfs als er geen wijziging in de gegevens is geweest. 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 gegevenstabellenvoor meer informatie.

Een gegevenstabel met één variabele bevat de invoerwaarden in één kolom (kolomgeoriënteerd) of in een rij (rijgeoriënteerd). Formules in een gegevenstabel met één variabele mogen slechts één invoercel.

Voer de volgende stappen uit:

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

2. Voer een van de volgende handelingen uit:

   • Als de gegevenstabel kolomgeoriënteerd is (de variabele waarden in een kolom staan), typt u de formule in de cel één rij erboven en één cel rechts van de kolom met waarden. Deze gegevenstabel met één variabele is kolomgeoriënteerd en de formule is opgenomen 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 variabele waarden staan 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 Voorspellen van Excel 2016 ).  

5. Voer een van de volgende handelingen uit:

   • Als de gegevenstabel kolomgeoriënteerd is, typt u de celverwijzing voor de invoercel in het veld Kolominvoercel. 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 volgende 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 een lege cel onder een bestaande formule in de eerste kolom van de gegevenstabel in.

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 Voorspellen van Excel 2016 ).

4. Ga als volgt 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 Rijinvoercel 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 als de formule in, rechts.

   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 de groep Prognose (in Excel 2016 ) op Wat-als-analyse >gegevenstabel (in de groep Hulpmiddelen voor gegevens of De groep Voorspellen 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 Rijinvoercel.

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

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),waarin twee invoercellen, B3 en B4, worden gebruikt.

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

Volg de volgende stappen om de berekeningsprestaties te verbeteren:

1. Klik op > opties >Formules.

2. Klik in de sectie Berekeningsopties onder Berekenenop Automatisch behalve gegevenstabellen.

   Tip: Klik desgewenst op het tabblad Formules op de pijl bij Berekeningsoptiesenklik vervolgens op Automatisch behalve gegevenstabellen (in de groep Berekening).

U kunt een paar andere Excel gebruiken om wat-als-analyse uit te voeren als u specifieke doelen of grotere sets variabele gegevens hebt.

Doelzoeken

Als u weet wat het resultaat is van een formule, maar niet precies weet welke invoerwaarde de formule nodig heeft om dat resultaat te krijgen, gebruikt u Goal-Seek functie. Zie het artikel Doel zoeken gebruiken om het juiste resultaat te vinden door een invoerwaarde aan te passen.

Excel Oplokker

U kunt de invoeg Excel Oplokker gebruiken om de optimale waarde te vinden voor een set invoervariabelen. Oplokker werkt met een groep cellen (beslissingsvariabelen of gewoon variabele cellen) 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 Oplokker.