Wanneer u een complexe formule heeft die afhankelijk is van meerdere variabelen en u wilt begrijpen hoe het wijzigen van die invoer de resultaten efficiënt beïnvloedt, is een What-If-analysegegevenstabel in Excel een krachtig hulpmiddel. Hiermee kunt u in één oogopslag alle mogelijke resultaten zien. Hier vindt u een stapsgewijze handleiding voor het maken van een gegevenstabel in Excel. Daarnaast bespreken we enkele belangrijke punten voor het effectief gebruik van datatabellen en demonstreren we andere bewerkingen, zoals het verwijderen, bewerken en opnieuw berekenen van datatabellen.

Wat is een gegevenstabel in Excel?

Maak een gegevenstabel met één variabele

• Kolomgeoriënteerde gegevenstabel
• Rijgeoriënteerde gegevenstabel
• Meerdere formules voor een gegevenstabel met één variabele

Maak een gegevenstabel met twee variabelen

Kernpunten met behulp van de gegevenstabellen

Andere bewerkingen voor het gebruik van gegevenstabellen

• Een gegevenstabel verwijderen
• Bewerk het resultaat van de gegevenstabel
• Gegevenstabel handmatig opnieuw berekenen

Wat is een gegevenstabel in Excel?

In Excel is een gegevenstabel een van de What-If-analysehulpmiddelen waarmee u kunt experimenteren met verschillende invoerwaarden voor formules en de veranderingen in de uitvoer van de formule kunt observeren. Deze tool is ongelooflijk handig 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:

Gegevenstabel met één variabele: Hiermee kunt u analyseren hoe verschillende waarden van één variabele een formule beïnvloeden. U kunt een gegevenstabel met één variabele instellen in een rij- of kolomgeoriënteerde indeling, afhankelijk van of u uw invoer over een rij of kolom omlaag wilt variëren.

Gegevenstabel met twee variabelen: Met dit type kunt u de impact zien van het wijzigen van twee verschillende variabelen op de uitkomst van een formule. In een gegevenstabel met twee variabelen varieert u de waarden langs zowel een rij als een kolom.

Maak een gegevenstabel met één variabele

Het maken van een gegevenstabel met één variabele in Excel is een waardevolle vaardigheid om te analyseren hoe veranderingen in een enkele invoer verschillende uitkomsten kunnen beïnvloeden. In dit gedeelte wordt u door het proces geleid voor het maken van kolomgeoriënteerde, rijgerichte gegevenstabellen en gegevenstabellen met meerdere formules en één variabele.

Verbazingwekkend! Efficiënte tabbladen gebruiken in Excel zoals Chrome, Edge, Firefox en Safari!
Bespaar 50% van uw tijd en verminder elke dag duizenden muisklikken!

---

Kolomgeoriënteerde gegevenstabel

Een kolomgeoriënteerde gegevenstabel is handig als u wilt testen hoe verschillende waarden van een enkele variabele de uitvoer van een formule beïnvloeden, waarbij de variabelewaarden in een kolom worden vermeld. Laten we een eenvoudig financieel voorbeeld bekijken:

Stel dat u een lening van €50,000 overweegt, die u over een periode van drie jaar (wat overeenkomt met 3 maanden) wilt terugbetalen. Om te beoordelen welke maandelijkse betaling betaalbaar zou zijn op basis van uw salaris, wilt u onderzoeken hoe verschillende rentetarieven van invloed zijn op het bedrag dat u elke maand moet betalen.

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 rentetarief, het aantal perioden en het geleende bedrag. Zie schermafbeelding:

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

Stap 2: Zet de rentetarieven in een kolom

Vermeld in een kolom de verschillende rentetarieven die u wilt testen. Plaats bijvoorbeeld waarden van 4% tot 11% in stappen van 1% in een kolom en laat ten minste één lege kolom aan de rechterkant voor de uitkomsten. Zie schermafbeelding:

Stap 3: Maak de gegevenstabel

1. Voer in cel E2 deze formule in: = B4.
   
   Note: B4 is de cel waarin uw hoofdformule zich bevindt. Dit is de formule waarvan u de resultaten wilt zien veranderen als u de rente varieert.
   
2. Selecteer het bereik dat uw formule, lijst met rentetarieven en aangrenzende cellen voor de resultaten omvat (selecteer bijvoorbeeld D2 tot E10). Zie schermafbeelding:
   
3. Ga naar het lint, klik op de Data tabblad en klik vervolgens op Wat als analyse > data Table, zie screenshot:
   
4. In het data Table dialoogvenster, klik in het Kolom Invoercel (aangezien we een kolom gebruiken voor invoerwaarden) en selecteer de variabele cel waarnaar in uw formule wordt verwezen. In dit voorbeeld selecteren we B1 dat het rentepercentage bevat. Klik ten slotte OK knop, zie screenshot:
   
5. Excel vult automatisch de lege cellen naast elk van uw variabelewaarden (verschillende rentetarieven) met de bijbehorende uitkomsten. Zie schermafbeelding:
   
6. Pas het gewenste getalformaat toe op de resultaten (valuta) naar behoefte. Nu is de kolomgeoriënteerde gegevenstabel met succes gemaakt en kunt u snel de resultaten bekijken om te beoordelen welke maandelijkse betalingsbedragen voor u betaalbaar zouden zijn als de rente verandert. Zie schermafbeelding:
   

Rijgeoriënteerde gegevenstabel

Als u een rijgeoriënteerde gegevenstabel in Excel maakt, moet u uw gegevens zo rangschikken dat de variabelewaarden in een rij worden weergegeven in plaats van in een kolom. Laten we het bovenstaande voorbeeld als referentie nemen en doorgaan met de stappen om het maken van een rijgeoriënteerde gegevenstabel in Excel te voltooien.

Stap 1: Zet de rentetarieven op een rij

Plaats de variabele waarden (rentetarieven) op een rij, zorg ervoor dat er minimaal één lege kolom aan de linkerkant is voor de formule en één lege rij eronder voor de resultaten, zie screenshot:

Stap 2: Maak de gegevenstabel

1. Voer in cel A9 deze formule in: = B4.
   
2. Selecteer het bereik dat uw formule, lijst met rentetarieven en aangrenzende cellen voor de resultaten omvat (selecteer bijvoorbeeld A8 tot I9). Dan klikken Data > Wat als analyse > data Table.
3. In het data Table dialoogvenster, klik in het Rij-invoercel (aangezien we een rij gebruiken voor invoerwaarden) en selecteer de variabele cel waarnaar in uw formule wordt verwezen. In dit voorbeeld selecteren we B1 dat het rentepercentage bevat. Klik ten slotte OK knop, zie screenshot:
   
4. Pas het gewenste getalformaat toe op de resultaten (valuta) naar behoefte. Nu wordt de rijgeoriënteerde gegevenstabel gemaakt, zie screenshot:
   

Meerdere formules voor een gegevenstabel met één variabele

Door een gegevenstabel met één variabele met meerdere formules in Excel te maken, kunt u zien hoe het wijzigen van een enkele invoer verschillende formules tegelijk beïnvloedt. Wat als u in het bovenstaande voorbeeld de verandering in de rentetarieven op zowel de aflossingen als de totale rente wilt zien? Zo stelt u 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

Vermeld in een kolom de verschillende rentetarieven die u wilt testen en laat ten minste twee lege kolommen aan de rechterkant vrij voor de uitkomsten. Zie schermafbeelding:

Stap 3: Maak de gegevenstabel:

1. Voer in cel E2 deze formule in: = B4 om een ​​verwijzing naar de terugbetalingsberekening in de oorspronkelijke gegevens aan te maken.
2. Voer in cel F2 deze formule in: = B5 om een ​​verwijzing te creëren naar de totale interesse in de originele gegevens.
   
3. Selecteer het bereik dat uw formules, een lijst met rentetarieven en aangrenzende cellen voor het resultaat bevat (selecteer bijvoorbeeld D2 tot en met F10). Dan klikken Data > Wat als analyse > data Table.
4. In het data Table dialoogvenster, klik in het Kolominvoercel (aangezien we een kolom gebruiken voor invoerwaarden) en selecteer de variabele cel waarnaar in uw formule wordt verwezen. In dit voorbeeld selecteren we B1 dat het rentepercentage bevat. Klik ten slotte OK knop, zie screenshot:
   
5. Pas het gewenste getalformaat toe op de resultaten (valuta) naar behoefte. En u kunt de resultaten voor elke formule bekijken op basis van de variërende variabelewaarden.
   

Maak een gegevenstabel met twee variabelen

Een gegevenstabel met twee variabelen in Excel geeft de impact weer van verschillende combinaties van twee sets variabelewaarden op de uitkomst van een formule, en laat zien hoe wijzigingen in twee invoer van een formule tegelijkertijd het resultaat beïnvloeden.

Hier heb ik een eenvoudige schets getekend om u te helpen het uiterlijk en de structuur van een gegevenstabel met twee variabelen beter te begrijpen.

Laten we, voortbouwend op het voorbeeld van het maken van een gegevenstabel met één variabele, nu leren hoe u een gegevenstabel met twee variabelen kunt maken in Excel.

In de onderstaande gegevensset hebben we de rente, de looptijd van de lening en het geleende bedrag, en hebben we de maandelijkse betaling berekend met behulp van deze formule: =-PMT($B$1/12, $B$2*12, $B$3) ook. Hier zullen we ons concentreren op twee belangrijke variabelen uit onze gegevens: de rente en het geleende bedrag, om te observeren hoe veranderingen in beide factoren tegelijkertijd de aflossingsbedragen beïnvloeden.

Stap 1: Stel de twee veranderende variabelen in

1. Vermeld in een kolom de verschillende rentetarieven die u wilt testen. zie schermafdruk:
   
2. Voer in een rij de verschillende waarden van het geleende bedrag in, net boven de kolomwaarden (beginnend vanaf één cel rechts van de formulecel), zie screenshot:
   

Stap 2: Maak de gegevenstabel

1. Plaats uw formule op het snijpunt van de rij en de kolom waarin u de variabelewaarden hebt vermeld. In dit geval voer ik deze formule in: = B4 in cel E2. Zie schermafbeelding:
   
2. Selecteer het bereik dat uw leningsbedragen, rentetarieven, de formulecel en de cellen bevat waarin de resultaten worden weergegeven.
   
3. Dan klikken Data > Wat als analyse > data Table. In het dialoogvenster Gegevenstabel:
   • In het Rij-invoercel Selecteer in het vak de celverwijzing naar de invoercel voor de variabelewaarden in de rij (in dit voorbeeld bevat B3 het geleende bedrag).
   • In het Kolominvoer celSelecteer in het vak de celverwijzing naar de invoercel voor de variabelewaarden in de kolom (B1 bevat het rentepercentage).
   • En klik dan OK Knop.
     
4. Nu vult Excel de gegevenstabel in met de resultaten voor elke combinatie van leenbedrag en rentepercentage. Het biedt een direct beeld van hoe verschillende combinaties van leenbedragen en rentetarieven de maandelijkse betaling beïnvloeden, waardoor het een waardevol hulpmiddel is voor financiële planning en analyse.
   
5. Ten slotte moet u het gewenste getalformaat op de resultaten toepassen (Valuta), afhankelijk van uw behoefte.
   

Kernpunten met behulp van de gegevenstabellen

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

Andere bewerkingen voor het gebruik van gegevenstabellen

Nadat u een gegevenstabel heeft gemaakt, heeft u mogelijk extra handelingen nodig om deze effectief te beheren, zoals het verwijderen van de gegevenstabel, het wijzigen van de resultaten ervan en het uitvoeren van handmatige herberekeningen.

Een gegevenstabel verwijderen

Omdat de gegevenstabelresultaten worden berekend met een matrixformule, kunt u geen afzonderlijke waarde uit de gegevenstabel verwijderen. U kunt echter alleen de hele gegevenstabel verwijderen.

Selecteer alle gegevenstabelcellen of alleen de cellen met de resultaten en druk vervolgens op Verwijder toets op toetsenbord.

Bewerk het resultaat van de gegevenstabel

Het is inderdaad niet mogelijk om individuele cellen binnen een gegevenstabel rechtstreeks te bewerken, omdat deze matrixformules bevatten die Excel automatisch genereert.

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

Gegevenstabel handmatig opnieuw berekenen

Normaal gesproken berekent Excel alle formules in alle geopende werkmappen opnieuw telkens wanneer er een wijziging wordt aangebracht. Als een grote gegevenstabel met talloze variabele waarden en complexe formules ervoor zorgt dat uw Excel-werkmap langzamer gaat werken.

Om te voorkomen dat Excel automatisch berekeningen uitvoert voor alle gegevenstabellen telkens wanneer er een wijziging in de werkmap wordt aangebracht, kunt u de berekeningsmodus overschakelen van Automatisch naar Handmatig. Ga als volgt te werk:

Ga naar uw Formules Tabblad en klik vervolgens op Berekeningsopties > Automatisch Behalve voor gegevenstabellen, zie screenshot:

Nadat u deze instelling heeft gewijzigd en u uw hele werkmap opnieuw berekent, werkt Excel de berekeningen in uw gegevenstabellen niet langer automatisch bij.

Als u uw gegevenstabel handmatig moet bijwerken, selecteert u gewoon de cellen waarin de resultaten (cellen die de TABLE()-formules bevatten) worden weergegeven en drukt u vervolgens op F9 sleutel.

Door de stappen in deze handleiding te volgen en de belangrijkste punten in gedachten te houden, kunt u gegevenstabellen efficiënt gebruiken voor uw gegevensanalysebehoeften. Als u geïnteresseerd bent in meer tips en trucs voor Excel, biedt onze website duizenden tutorials, Klik hier om ze te openen. Bedankt voor het lezen en we kijken ernaar uit u in de toekomst van meer nuttige informatie te voorzien!