Gegevenstabel in Excel: Maak eenvariabele- en tweevariabele-gegevenstabellen

Wanneer je een complexe formule hebt die afhankelijk is van meerdere variabelen en je wilt begrijpen hoe het wijzigen van die invoer de resultaten efficiënt beïnvloedt, is een What-If-analyse gegevenstabel in Excel een krachtig hulpmiddel. Het stelt je in staat om met één blik alle mogelijke uitkomsten te zien. Hier is een stap-voor-stap handleiding over het maken van een gegevenstabel in Excel. Bovendien bespreken we enkele belangrijke punten voor het effectief gebruiken van gegevenstabellen en demonstreren we andere bewerkingen zoals verwijderen, bewerken en opnieuw berekenen van gegevenstabellen.

Wat is een gegevenstabel in Excel?

Maak een eenvariabele-gegevenstabel

• Kolomgerichte gegevenstabel
• Rijgerichte gegevenstabel
• Meerdere formules voor een eenvariabele-gegevenstabel

Maak een tweevariabele-gegevenstabel

Belangrijke punten bij het gebruik van gegevenstabellen

Andere bewerkingen voor het gebruik van gegevenstabellen

• Verwijder een gegevenstabel
• Bewerk gegevenstabelresultaat
• Herbereken gegevenstabel handmatig

Wat is een gegevenstabel in Excel?

In Excel is een gegevenstabel een van de What-If Analyse tools die je in staat stelt om verschillende invoerwaarden voor formules te experimenteren en de veranderingen in de uitkomst van de formule te observeren. Dit hulpmiddel is ongelooflijk nuttig voor het verkennen van verschillende scenario's en het uitvoeren van gevoeligheidsanalyses, vooral wanneer een formule afhankelijk is van meerdere variabelen.

Er zijn twee soorten gegevenstabellen in Excel:

Eenvariabele-gegevenstabel: Dit stelt je in staat om te analyseren hoe verschillende waarden van één variabele de uitkomst van een formule beïnvloeden. Je kunt een eenvariabele-gegevenstabel instellen in een kolom- of rijgericht formaat, afhankelijk van of je de invoer over een rij of langs een kolom wilt variëren.

Tweevariabele-gegevenstabel: Dit type laat je zien wat de impact is van het veranderen van twee verschillende variabelen op de uitkomst van een formule. In een tweevariabele-gegevenstabel varieer je de waarden langs zowel een rij als een kolom.

Maak een eenvariabele-gegevenstabel

Het maken van een eenvariabele-gegevenstabel in Excel is een waardevolle vaardigheid voor het analyseren hoe veranderingen in één invoer diverse uitkomsten kunnen beïnvloeden. Deze sectie leidt je door het proces van het maken van kolomgerichte, rijgerichte en meervoudige-formule eenvariabele-gegevenstabellen.

Geweldig! Efficient Tabs gebruiken in Excel, net als in Chrome, Edge, Firefox en Safari!
Bespaar 50% van je tijd en verminder elke dag duizenden muisklikken!

---

Kolomgerichte gegevenstabel

Een kolomgerichte gegevenstabel is nuttig wanneer je wilt testen hoe verschillende waarden van één variabele de uitkomst van een formule beïnvloeden, met de variabele waarden weergegeven in een kolom. Laten we een eenvoudig financieel voorbeeld bekijken:

Stel dat je overweegt een lening van $50.000 aan te gaan, die je plannen bent terug te betalen over een periode van 3 jaar (gelijk aan 36 maanden). Om te evalueren welk maandelijkse bedrag betaalbaar zou zijn op basis van je salaris, ben je geïnteresseerd in het verkennen hoe verschillende rentepercentages de hoeveelheid die je elke maand moet betalen zouden beïnvloeden.

Stap 1: Stel de basisformule in

Om de betaling te berekenen, stel ik hier de rente in op 5%. Voer in cel B4 de PMT-formule in, deze berekent de maandelijkse betaling op basis van het rentepercentage, het aantal perioden en het leningsbedrag. Zie screenshot:

= -PMT($B$1/12, $B$2*12, $B$3)

Stap 2: Lijst de rentepercentages in een kolom

Lijst in een kolom de verschillende rentepercentages die je wilt testen. Bijvoorbeeld, lijst waarden van 4% tot 11% in stappen van 1% in een kolom, en laat ten minste één lege kolom rechts open voor de uitkomsten. Zie screenshot:

Stap 3: Maak de gegevenstabel

1. Voer in cel E2 deze formule in: =B4.
   
   Opmerking: B4 is de cel waar je hoofdformule zich bevindt, dit is de formule waarvan je de resultaten wilt zien veranderen wanneer je het rentepercentage varieert.
   
2. Selecteer het bereik dat jouw formule, lijst van rentepercentages en aangrenzende cellen voor de resultaten omvat (bijv., selecteer D2 tot E10). Zie screenshot:
   
3. Ga naar de menubalk, klik op het tabblad Gegevens , klik dan op What-If Analyse > Gegevenstabel, zie screenshot:
   Gegevenstabel"/>
4. In het Gegevenstabel dialoogvenster, klik in het veld Kolominvoercel (aangezien we een kolom gebruiken voor invoerwaarden), en selecteer de variabele cel waarnaar wordt verwezen in je formule. In dit voorbeeld selecteren we B1, die het rentepercentage bevat. Klik tot slot op de knop OK , zie screenshot:
   
5. Excel zal automatisch de lege cellen naast elk van je variabele waarden (verschillende rentepercentages) vullen met de bijbehorende uitkomsten. Zie screenshot:
   
6. Pas het gewenste getalformaat toe op de resultaten (Valuta) naar behoefte. Nu is de kolomgerichte gegevenstabel succesvol gemaakt, en kun je snel de resultaten bekijken om te evalueren welke maandelijkse betalingsbedragen betaalbaar voor je zijn wanneer het rentepercentage verandert. Zie screenshot:
   

Rijgerichte gegevenstabel

Het maken van een rijgerichte gegevenstabel in Excel houdt in dat je je gegevens zo rangschikt dat de variabele waarden worden weergegeven in een rij in plaats van in een kolom. Met het bovenstaande voorbeeld als referentie, laten we de stappen volgen om de creatie van een rijgerichte gegevenstabel in Excel te voltooien.

Stap 1: Lijst de rentepercentages in een rij

Plaats de variabele waarden (rentepercentages) in een rij, waarbij er ten minste één lege kolom links van de formule en één lege rij onder voor de resultaten blijft, zie screenshot:

Stap 2: Maak de gegevenstabel

1. Voer in cel A9 deze formule in: =B4.
   
2. Selecteer het bereik dat jouw formule, lijst van rentepercentages en aangrenzende cellen voor de resultaten omvat (bijv., selecteer A8 tot I9). Klik vervolgens op Gegevens > What-If Analyse > Gegevenstabel.
3. In het Gegevenstabel dialoogvenster, klik in het veld Rijinvoercel (aangezien we een rij gebruiken voor invoerwaarden), en selecteer de variabele cel waarnaar wordt verwezen in je formule. In dit voorbeeld selecteren we B1, die het rentepercentage bevat. Klik tot slot op de knop OK , zie screenshot:
   
4. Pas het gewenste getalformaat toe op de resultaten (Valuta) naar behoefte. Nu is de rijgerichte gegevenstabel gemaakt, zie screenshot:
   

Meerdere formules voor een eenvariabele-gegevenstabel

Het maken van een eenvariabele-gegevenstabel met meerdere formules in Excel laat je zien hoe het veranderen van één invoer tegelijkertijd verschillende formules beïnvloedt. In het bovenstaande voorbeeld, wat als je de verandering in rentepercentages zowel op de aflossingen als op de totale rente wilt zien? Zo stel je het in:

Stap 1: Voeg een nieuwe formule toe om de totale rente te berekenen

Voer in cel B5 de volgende formule in om de totale rente te berekenen:

=B4*B2*12-B3

Stap 2: Rangschik de brongegevens van de gegevenstabel

Lijst in een kolom de verschillende rentepercentages die je wilt testen, en laat ten minste twee lege kolommen rechts open voor de uitkomsten. Zie screenshot:

Stap 3: Maak de gegevenstabel:

1. Voer in cel E2 deze formule in: =B4 om een verwijzing te maken naar de aflossingsberekening in de originele gegevens.
2. Voer in cel F2 deze formule in: =B5 om een verwijzing te maken naar de totale rente in de originele gegevens.
   
3. Selecteer het bereik dat jouw formules, lijst van rentepercentages en aangrenzende cellen voor de resultaten omvat (bijv., selecteer D2 tot F10). Klik vervolgens op Gegevens > What-If Analyse > Gegevenstabel.
4. In het Gegevenstabel dialoogvenster, klik in het veld Kolominvoercel (aangezien we een kolom gebruiken voor invoerwaarden), en selecteer de variabele cel waarnaar wordt verwezen in je formule. In dit voorbeeld selecteren we B1, die het rentepercentage bevat. Klik tot slot op de knop OK , zie screenshot:
   
5. Pas het gewenste getalformaat toe op de resultaten (Valuta) naar behoefte. En je kunt de resultaten voor elke formule zien op basis van de variërende variabele waarden.
   

Ontdek de Magie van Excel met Kutools AI

• Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
• Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
• VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
• Formule-uitleg: Begrijp complexe formules gemakkelijk.
• Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.

Verhoog uw Excel-mogelijkheden met AI-aangedreven tools. Download Nu en ervaar een ongekende efficiëntie!

Maak een tweevariabele-gegevenstabel

Een tweevariabele-gegevenstabel in Excel toont de impact van verschillende combinaties van twee sets variabele waarden op de uitkomst van een formule, en demonstreert hoe veranderingen in twee invoerwaarden van een formule tegelijkertijd de uitkomst beïnvloeden.

Hier heb ik een eenvoudige schets gemaakt om je beter te helpen begrijpen hoe een tweevariabele-gegevenstabel eruitziet en hoe deze is gestructureerd.

Op basis van het voorbeeld om een eenvariabele-gegevenstabel te maken, laten we nu leren hoe we een tweevariabele-gegevenstabel in Excel maken.

In de onderstaande dataset hebben we het rentepercentage, de looptijd van de lening en het leningsbedrag, en hebben we de maandelijkse betaling berekend met behulp van deze formule: =-PMT($B$1/12, $B$2*12, $B$3) Ook hier richten we ons op twee sleutelvariabelen uit onze gegevens: rentepercentage en leningsbedrag, om te observeren hoe veranderingen in beide factoren tegelijkertijd de aflossingsbedragen beïnvloeden.

Stap 1: Stel de twee veranderlijke variabelen in

1. Lijst in een kolom de verschillende rentepercentages die je wilt testen. Zie screenshot:
   
2. Voer in een rij de verschillende leningsbedragen in, net boven de kolomwaarden (startend vanaf één cel rechts van de formulecel), zie screenshot:
   

Stap 2: Maak de gegevenstabel

1. Plaats je formule op het snijpunt van de rij en de kolom waar je de variabele waarden hebt ingevoerd. In dit geval voer ik deze formule in: =B4 in cel E2. Zie screenshot:
   
2. Selecteer het bereik dat jouw leningsbedragen, rentepercentages, de formulecel en de cellen waar de resultaten worden weergegeven omvat.
   
3. Klik vervolgens op Gegevens > What-If Analyse > Gegevenstabel. In het dialoogvenster Gegevenstabel:
   • In het veld Rijinvoercel, selecteer de celverwijzing naar de invoercel voor de variabele waarden in de rij (in dit voorbeeld bevat B3 het leningsbedrag).
   • In het veld Kolominvoercel, selecteer de celverwijzing naar de invoercel voor de variabele waarden in de kolom (B1 bevat het rentepercentage).
   • En klik vervolgens op OK Knop.
     
4. Nu zal Excel de gegevenstabel vullen met de resultaten voor elke combinatie van leningsbedrag en rentepercentage. Het biedt een direct overzicht van hoe verschillende combinaties van leningsbedragen en rentepercentages de maandelijkse betaling beïnvloeden, waardoor het een waardevol hulpmiddel is voor financieel plannen en analyse.
   
5. Tot slot moet je het gewenste getalformaat toepassen op de resultaten (Valuta) naar behoefte.
   

Belangrijke punten bij het gebruik van gegevenstabellen

• De nieuw gemaakte gegevenstabel moet zich in hetzelfde werkblad bevinden als de originele gegevens.
• De uitkomst van de gegevenstabel is afhankelijk van de formulecel in de brondatareeks, en eventuele wijzigingen aan deze formulecel zullen de uitkomst automatisch bijwerken.
• Zodra waarden zijn berekend met behulp van de gegevenstabel, kunnen ze niet ongedaan worden gemaakt met Ctrl + Z. Je kunt echter handmatig alle waarden selecteren en ze verwijderen.
• De gegevenstabel genereert matrixformules, dus individuele cellen binnen de tabel kunnen niet worden gewijzigd of verwijderd.

Andere bewerkingen voor het gebruik van gegevenstabellen

Zodra je een gegevenstabel hebt gemaakt, heb je mogelijk extra bewerkingen nodig om deze effectief te beheren, inclusief het verwijderen van de gegevenstabel, het wijzigen van de resultaten en het uitvoeren van handmatige herberekeningen.

Verwijder een gegevenstabel

Omdat de resultaten van de gegevenstabel worden berekend met een matrixformule, kun je geen individuele waarde uit de gegevenstabel verwijderen. Je kunt echter alleen de hele gegevenstabel verwijderen.

Selecteer alle cellen van de gegevenstabel of alleen de cellen met de resultaten, en druk vervolgens op de Delete-toets op het toetsenbord.

Bewerk gegevenstabelresultaat

Inderdaad, het is niet mogelijk om individuele cellen binnen een gegevenstabel rechtstreeks te bewerken, omdat ze matrixformules bevatten die Excel automatisch genereert.

Om wijzigingen aan te brengen, moet je doorgaans de bestaande gegevenstabel verwijderen en vervolgens een nieuwe maken met de gewenste wijzigingen. Dit houdt in dat je je basisformule of de invoerwaarden aanpast en vervolgens de gegevenstabel opnieuw instelt om deze wijzigingen weer te geven.

Herbereken gegevenstabel handmatig

Normaal gesproken herberekent Excel alle formules in alle open werkboeken telkens wanneer een wijziging wordt aangebracht. Als een grote gegevenstabel met talloze variabele waarden en complexe formules ervoor zorgt dat je Excel-werkmap vertraagt.

Om te voorkomen dat Excel automatisch berekeningen voor alle gegevenstabellen uitvoert telkens wanneer een wijziging wordt aangebracht in het werkblad, kun je de berekeningsmodus wijzigen van Automatisch naar Handmatig. Doe het volgende:

Ga naar het tabblad Formules en klik vervolgens op Berekeningsinstellingen > Automatisch Behalve voor Gegevenstabellen, zie screenshot:

Nadat je deze instelling hebt gewijzigd, wanneer je je hele werkmap herberekent, zal Excel de berekeningen in je gegevenstabellen niet langer automatisch bijwerken.

Als je je gegevenstabel handmatig moet bijwerken, selecteer dan de cellen waar de resultaten (cellen met de TABLE()-formules) worden weergegeven, en druk vervolgens op de F9-toets.

Door de stappen in deze handleiding te volgen en de belangrijke punten in gedachten te houden, kun je gegevenstabellen efficiënt gebruiken voor je data-analysebehoeften. Als je geïnteresseerd bent in het verkennen van meer Excel-tips en -trucs, biedt onze website duizenden tutorials aan, klik hier om ze te bekijken. Bedankt voor het lezen, en we kijken ernaar uit om je in de toekomst meer nuttige informatie te bieden!