Meerdere resultaten berekenen met een gegevenstabel

Een gegevenstabel is een cellenbereik waarin u waarden in sommige cellen kunt wijzigen en een probleem kunt oplossen met verschillende antwoorden. Met een goed voorbeeld van een gegevenstabel wordt de functie Bet gebruikt met verschillende bedragen voor de lening en de rentepercentages om het betaalbare bedrag voor een hypotheek lening te berekenen. Experimenteer met verschillende waarden om na te gaan of de overeenkomstige variant in resultaten een veelvoorkomende taak is in Gegevensanalyse.

In Microsoft Excel maken gegevenstabellen deel uit van een suite met opdrachten, ook wel What-If analysehulpmiddelen. Wanneer u gegevenstabellen maakt en analyset, voert u wat-als-analyses 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. U kunt bijvoorbeeld een gegevenstabel gebruiken om de rente en de termijn lengte van een lening te variëren, om potentiële bedragen voor de maandelijkse betaling te evalueren.

Opmerking: U kunt snellere berekeningen uitvoeren met gegevenstabellen en VBA (Visual Basic for Applications). Zie voor meer informatie Excel What-If gegevenstabellen: sneller berekenen met VBA.

Typen ' wat als'-analyse    

Er zijn drie typen hulpmiddelen voor ' wat als'-analyse in Excel: scenario's, gegevenstabellenen doelgerichte Zoekfunctie. Scenario's en gegevenstabellen gebruiken sets invoerwaarden om mogelijke resultaten te berekenen. Doelgerichte zoekfunctie is een verschillend resultaat, en de mogelijke invoerwaarden worden berekend die het resultaat zouden produceren.

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 het slechts één of twee variabelen bevat (een voor de rij-invoercel en een voor de kolominvoercel), kunt u een gegevenstabel als veel verschillende variabele waarden opnemen. Een scenario kan maximaal 32 verschillende waarden bevatten, maar u kunt zo veel scenario's maken als u wilt.

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

Maak een gegevenstabel met één variabele of een gegevenstabel met 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 de verschillende rentevoeten van invloed zijn op een maandelijkse hypotheekaflossing met de functie BET. 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 betalings formule, = aflossing (B3/12, B4,-B5), die naar de invoercel B3 verwijst.

Gegevenstabel met één variabele

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 betalings formule, = Bet (B3/12, B4,-B5), waarin twee invoercellen B3 en B4 worden gebruikt.

Data table with two variables
 

Gegevenstabel berekeningen    

Wanneer een werkblad opnieuw wordt berekend, worden alle gegevenstabellen ook herberekend, zelfs als de gegevens niet zijn gewijzigd. U kunt de berekening van een werkblad met een gegevenstabel versnellen door de berekenings opties te wijzigen, zodat het werkblad automatisch wordt herberekend, maar niet de gegevenstabellen. Zie voor meer informatie de sectie berekeningen in een werkblad met gegevenstabellen versnellen.

Een gegevenstabel met één variabele bevat de invoerwaarden in één kolom (kolom-georiënteerd) of in een rij (op basis van een rij). Alle formules in een gegevenstabel met één variabele moeten naar slechts één invoercel verwijzen.

Volg deze stappen:

  1. Typ de lijst met waarden die u wilt vervangen door de invoercel, één kolom omlaag of over één rij. Laat enkele lege rijen en kolommen aan beide zijden van de waarden achter.

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

    • Als de gegevenstabel kolom-georiënteerd is (de variabele waarden staan 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 een kolom-georiënteerd en de formule staat in cel D2.

      Gegevenstabel met één variabele

      Als u de effecten van verschillende waarden op andere formules wilt bekijken, typt u de extra formules in cellen rechts van de eerste formule.

    • Als de gegevenstabel rij-georië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 bekijken, typt u de extra formules in cellen onder de eerste formule.

  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 What-If-analyse >gegevenstabel (in de groep hulpmiddelen voor gegevens of prognose groep van Excel 2016 ). 

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

    • Als de gegevenstabel op kolom basis wordt geplaatst, typt u de celverwijzing voor de invoercel in het veld Kolominvoercel . In de bovenstaande afbeelding is de invoer cel B3.

    • Als de gegevenstabel rij-georiënteerd is, voert u de celverwijzing voor de invoercel in het veld rij-invoer cel in.

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

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

Voer de volgende stappen uit

  1. Voer een van de volgende handelingen uit:

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

    • Voer de nieuwe formule in een lege cel onder een bestaande formule in de eerste kolom van de gegevenstabel in wanneer de gegevenstabel een rij-georiënteerd is.

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

  3. Klik op het tabblad gegevens op What-If-analyse > gegevenstabel (in de groep hulpmiddelen voor gegevens of prognose groep van Excel 2016 ).

  4. Ga op één van de volgende manieren te werk:

    • Voer de celverwijzing voor de invoercel in het vak Kolominvoercel in als de gegevens tabel een kolom heeft.

    • Voer de celverwijzing voor de invoercel in het vak rij-invoercel in als de gegevenstabel een rij-georiënteerd is.

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.

Volg deze stappen:

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

    In het volgende voorbeeld, waarin de beginwaarden van de formule in de cellen B3, B4 en B5 worden ingevoerd, typt u de formule = Bet (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, aan de rechterkant.

    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 Excel 2016 in de groep prognose op Wat-als-analyse >gegevenstabel (in de groep hulpmiddelen voor gegevens of de groep prognoses 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 vak rij-invoercel .

  7. Voer in het vak 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 bevat cel C2 de betalings formule, = aflossing (B3/12, B4,-B5), waarin twee invoercellen B3 en B4 worden gebruikt.

Data table with two variables

Wanneer u deze berekeningsoptie instelt, vinden er geen berekeningen van gegevenstabellen plaats wanneer een herberekening wordt uitgevoerd voor de hele werkmap. Als u de gegevenstabel handmatig opnieuw wilt berekenen, selecteert u de bijbehorende formules en drukt u op F9.

Voer de volgende stappen uit om de berekenings prestaties te verbeteren:

  1. Klik op bestand > Opties > formules.

  2. Ga naar het gedeelte Berekeningsopties en klik onder berekenenop Automatisch behalve voor gegevenstabellen.

    Tip: U kunt ook op het tabblad formules op de pijl bij Berekeningsoptiesklikken en vervolgens op Automatisch behalve gegevenstabellen (in de groep berekening ) klikken.

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

Doelzoeken

Als u weet welk resultaat een formule verwacht, maar u weet niet precies welke invoerwaarde de formule nodig heeft om dat resultaat te verkrijgen, 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 Excel-invoegtoepassing Oplosser gebruiken om de optimale waarde voor een set invoervariabelen te vinden. Oplosser werkt met een groep cellen (variabelen voor beslissings variabelen, of gewoon variabele cellen) die worden gebruikt voor het gebruik van de formules in de cellen objectief en CONSTRAINT. De waarden in de beslissingsvariabelecellen worden aangepast op basis van de limieten voor randvoorwaardencellen en het gewenste resultaat voor de doelfunctiecel wordt geproduceerd. Zie het volgende artikel voor meer informatie: Problemen definiëren en oplossen met Oplosser.

Als u een andere waarde aan een cel koppelt, kunt u snel op een ander antwoord klikken. In het voorbeeld wordt de functie Bet gebruikt met verschillende rentevoeten en termijn waarden (in maanden) om erachter te komen hoeveel van een lening u kunt veroorloven voor een huis of een auto. U voert uw getallen in een bereik van cellen in, een gegevenstabel genoemd.

Hier is de gegevenstabel het cellenbereik B2: D8. U kunt de waarde in B4, het geleende bedrag en de maandelijkse betalingen in kolom D automatisch bijwerken. Met behulp van een 3,75% rentepercentage wordt met deze formule een maandelijkse aflossing van $1.042,01 geretourneerd: = bet (C2/12, $B $3, $B $4).

Dit celbereik, B2:D8, is een gegevenstabel

U kunt één of twee variabelen gebruiken, afhankelijk van het aantal variabelen en formules dat u wilt testen.

Gebruik een test met één variabele om te zien hoe de resultaten van verschillende waarden van een variabele in een formule worden gewijzigd. U kunt bijvoorbeeld het rentepercentage voor een maandelijkse hypotheek betaling wijzigen met de functie BET. U voert de variabelenwaarden (de rentevoeten) in één kolom of rij in, en de resultaten worden weergegeven in een kolom of rij van een nabije stand.

Cel D2 bevat de betalings formule =bet (C2/12, $B $3, $B $4) in deze live werkmap. Cel B3 is de cel van een variabele , waar u een andere term kunt aansluiten (aantal maandelijkse termijnen). In cel D2 wordt de functie BET gebruikt om de rente in de rente 3,75%/12, 360 maanden en een $225.000-lening te berekenen, en wordt een maandelijkse betaling van $1.042,01 berekend.

Gebruik een test met twee variabelen om te zien hoe de resultaten van verschillende waarden van twee variabelen in een formule worden gewijzigd. U kunt bijvoorbeeld verschillende combinaties van rentepercentages en aantal maandelijkse termijnen testen om de hypotheekaflossing te berekenen.

In deze werkmap bevat cel C3 de betalings formule, =bet ($B $3/12, $B $2, B4), waarin twee cellen met variabele waarden worden gebruikt, B2 en B3. In cel C2 wordt de functie BET gebruikt om de rente te 3.875%/12, 360 maanden en een $225.000-lening, en wordt een maandelijkse betaling van $1.058,03 berekend.

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community, ondersteuning vragen in de Answer-community of een nieuwe functie of verbetering voorstellen in Excel User Voice.

Meer hulp nodig?

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagenten.

×