Hoe maak je een gegevenstabel in Excel

De tutorial laat zien hoe je gegevenstabellen gebruikt voor wat-als analyse in Excel. Leer hoe u een één-variabele en twee-variabele tabel maakt om de effecten van een of twee invoerwaarden op uw formule te zien, en hoe u een gegevenstabel opzet om meerdere formules tegelijk te evalueren.

u hebt een complexe formule gebouwd die afhankelijk is van meerdere variabelen en wilt weten hoe het veranderen van die ingangen de resultaten verandert., In plaats van elke variabele afzonderlijk te testen, Maak een wat-als analyse data tabel en observeer alle mogelijke uitkomsten met een snelle blik!

• gegevenstabel in Excel
• Maak één variabele gegevenstabel
• maak twee variabele gegevenstabel
• gegevenstabel om meerdere formules
• Excel – gegevenstabellen te evalueren-3 dingen die u moet weten
• Verwijder een gegevenstabel in Excel
• gegevenstabel bewerken resultaten
• gegevenstabel handmatig herberekenen

Wat is een gegevenstabel in Excel?,

in Microsoft Excel is een gegevenstabel een van de analysetools waarmee u verschillende invoerwaarden voor formules kunt uitproberen en kunt zien hoe veranderingen in die waarden de uitvoer van formules beïnvloeden.

gegevenstabellen zijn vooral nuttig wanneer een formule afhankelijk is van meerdere waarden, en u wilt experimenteren met verschillende combinaties van inputs en de resultaten vergelijken.

momenteel bestaat er één tabel met variabele gegevens en twee tabel met variabele gegevens. Hoewel beperkt tot maximaal twee verschillende invoercellen, kunt u met een gegevenstabel zoveel variabele waarden testen als u wilt.,

Hoe maak je een variabele gegevenstabel in Excel

Eén variabele gegevenstabel in Excel maakt het testen van een reeks waarden voor een enkele invoercel mogelijk en laat zien hoe deze waarden het resultaat van een gerelateerde formule beïnvloeden.,

om u te helpen deze functie beter te begrijpen, gaan we een specifiek voorbeeld volgen in plaats van generieke stappen te beschrijven.

stel dat u overweegt uw spaargeld te storten bij een bank, die maandelijks 5% rente betaalt. Om verschillende opties te controleren, hebt u de volgende samengestelde rentecalculator gebouwd waarbij:

• B8 de FV-formule bevat die het eindsaldo berekent.
• B2 is de variabele die u wilt testen (initiële investering).,

en nu, laten we een eenvoudige What-If analyse doen om te zien wat uw besparingen zullen zijn in 5 jaar, afhankelijk van het bedrag van uw initiële investering, variërend van $1.000 tot $6.000.

Hier volgen de stappen om een gegevenstabel met één variabele te maken:

1. Voer de waarden van de variabele in in één kolom of in één rij. In dit voorbeeld gaan we een kolom-georiënteerde gegevenstabel maken, dus we typen onze variabele waarden in een kolom (D3:D8) en laten ten minste één lege kolom rechts voor de uitkomsten.,
2. Typ uw formule in de cel één rij hierboven en één cel rechts van de variabele waarden (E2 in ons geval). Of koppel deze cel aan de formule in uw oorspronkelijke dataset (als u besluit de formule in de toekomst te wijzigen, hoeft u slechts één cel bij te werken). We kiezen voor de laatste optie en voeren deze eenvoudige formule in E2 in: =B8
   
   Tip., Als u de impact van de variabele waarden op andere formules wilt onderzoeken die naar dezelfde invoercel verwijzen, voert u de extra formule(s) rechts van de eerste formule in, zoals in dit voorbeeld wordt getoond.
3. Selecteer het bereik van de gegevenstabel, inclusief uw formule, cellen met variabele waarden en lege cellen voor de resultaten (D2:E8).,
4. ga naar de Data tab > Data Tools Groep, klik op de wat-als Analyse knop, en klik vervolgens op Data Table…
   
5. In het dialoogvenster Data Table, klik in het kolom invoercel vak (omdat onze Investeringswaarden in een kolom), en selecteer de variabele cel waarnaar wordt verwezen in uw formule. In dit voorbeeld selecteren we B3 die de initiële investeringswaarde bevat.
   
6. klik op OK, en Excel zal onmiddellijk de lege cellen vullen met resultaten die overeenkomen met de variabele waarde in dezelfde rij.,
7. pas het gewenste getalformaat toe op de resultaten( valuta in ons geval), en u bent klaar om te gaan!

nu kunt u snel uw gegevenstabel met één variabele bekijken, de mogelijke balansen onderzoeken en de optimale stortingsgrootte kiezen:

Rijgeoriënteerde gegevenstabel

het bovenstaande voorbeeld laat zien hoe u een verticale of kolomgeoriënteerde gegevenstabel in Excel kunt instellen., Als u de voorkeur geeft aan een horizontale opmaak, moet u het volgende doen:

1. Typ de variabele waarden in een rij, waarbij u ten minste één lege kolom links laat (voor de formule) en één lege rij hieronder (voor de resultaten). Voor dit voorbeeld voeren we de variabele waarden in cellen F3:J3.
2. Voer de formule in in de cel die één kolom links van uw eerste variabele en één cel daaronder is (E4 in ons geval).,
3. Maak een gegevenstabel zoals hierboven besproken, maar voer de invoerwaarde (B3) in in de rij invoercel doos:
   
4. klik op OK, en u zult het volgende resultaat hebben:
   

Hoe maak je een twee variabele gegevenstabel in Excel

A twee-variabele gegevenstabel laat zien hoe verschillende combinaties van 2 sets variabele waarden het resultaat van de formule beïnvloeden. Met andere woorden, het laat zien hoe het veranderen van twee invoerwaarden van dezelfde formule de output verandert.,

de stappen om een gegevenstabel met twee variabelen te maken in Excel zijn in principe hetzelfde als in het bovenstaande voorbeeld, behalve dat u twee reeksen van mogelijke invoerwaarden invoert, één op een rij en een andere in een kolom.

om te zien hoe het werkt, laten we dezelfde samengestelde rentecalculator gebruiken en de effecten van de omvang van de initiële investering en het aantal jaren op het saldo onderzoeken. Om dit te doen, stel uw gegevenstabel op deze manier in:

1. Voer uw formule in een lege cel in of koppel die cel aan uw oorspronkelijke formule., Zorg ervoor dat u voldoende lege kolommen aan de rechterkant en lege rijen hieronder om uw variabele waarden tegemoet. Zoals voorheen koppelen we de cel E2 aan de originele FV-formule die het saldo berekent: =B8
2. typ een set invoerwaarden onder de formule in dezelfde kolom (investeringswaarden in E3:E8).
3. Voer de andere reeks variabele waarden rechts van de formule in, in dezelfde rij (aantal jaren in F2:H2).,

   Op dit punt zou uw tabel met twee variabele gegevens er ongeveer zo uit moeten zien:
   

4. Selecteer het volledige bereik van de gegevenstabel inclusief de formule, de rij en kolom van de variabele waarden en de cellen waarin de berekende waarden zullen verschijnen. We selecteren het bereik E2: H8.,
5. Maak een gegevenstabel aan op de reeds bekende manier: Data tab > wat-als Analyseknop > gegevenstabel…
6. voer in het invoervak de verwijzing naar de invoercel in voor de variabele waarden in de rij (in dit voorbeeld is het B6 met de jaarwaarde).
7. voer in het vak kolom invoercel de verwijzing naar de invoercel in voor de variabele waarden in de kolom (B3 met de initiële investeringswaarde).
8. klik op OK.,n ons geval), en het analyseren van de resultaten:

   

Data tabel vergelijk meerdere resultaten

Als u wilt evalueren meer dan een formule op hetzelfde moment, het bouwen van uw gegevens tabel zoals weergegeven in de vorige voorbeelden, en geef de aanvullende formule(s):

• Aan de rechterkant van de eerste formule in het geval van een verticale tabel in kolommen
• Onderaan de eerste formule in het geval van een horizontale tabel in rijen geordend

Voor de “multi-formule” gegevens tabel correct te laten werken, alle formules moeten verwijzen naar dezelfde ingang van de cel.,

laten we als voorbeeld nog een formule toevoegen aan onze gegevenstabel met één variabele om de rente te berekenen en te zien hoe deze wordt beïnvloed door de grootte van de initiële investering. Hier is wat we doen:

1. in cel B10, bereken de rente met deze formule: =B8-B3
2. Schik de brongegevens van de gegevenstabel zoals we eerder deden: variabele waarden in D3:D8 en E2 gekoppeld aan B8 (Balansformule).,
3. voeg nog een kolom toe aan het bereik van de gegevenstabel (kolom F) en koppel F2 aan B10 (renteformule):
   
4. Selecteer het bereik van de uitgebreide gegevenstabel (D2:F8).
5. Open het dialoogvenster Gegevenstabel door op het tabblad Gegevens te klikken > What-If Analysis > gegevenstabel…
6. voer de invoercel (B3) in het vak kolom in en klik op OK.,

Voilà, u kunt nu de effecten van uw variabele waarden op beide formules waarnemen:

gegevenstabel in Excel – 3 dingen die u moet weten

om gegevenstabellen effectief te gebruiken in Excel, Houd rekening met deze 3 eenvoudige feiten:

1. om een gegevenstabel succesvol te maken, moeten de invoercel(s) op de hetzelfde blad als de gegevenstabel.,
2. Microsoft Excel gebruikt de functie TABLE (row_input_cell, colum_input_cell) om de resultaten van de gegevenstabel te berekenen:
   • In één variabele gegevenstabel wordt een van de argumenten weggelaten, afhankelijk van de lay-out (kolom-georiënteerd of rij-georiënteerd). In onze horizontale gegevenstabel met één variabele is de formule bijvoorbeeld =TABLE(, B3) waarbij B3 de kolom-invoercel is.
   • In twee-variabele gegevenstabel zijn beide argumenten aanwezig. Bijvoorbeeld, waarbij B6 de rij-invoercel is en B3 de kolom-invoercel.,

   De TABELFUNCTIE wordt ingevoerd als een matrixformule. Om hier zeker van te zijn, selecteert u een cel met de berekende waarde, kijkt u naar de formulebalk en noteert u de {accolades} rond de formule. Het is echter geen normale matrixformule – u kunt het niet typen in de formulebalk noch kunt u een bestaande bewerken. Het is gewoon “voor de show”.

3. omdat de resultaten van de gegevenstabel worden berekend met een matrixformule, kunnen de resulterende cellen niet afzonderlijk worden bewerkt. U kunt alleen de volledige reeks cellen bewerken of verwijderen zoals hieronder wordt uitgelegd.,

hoe een gegevenstabel in Excel te verwijderen

zoals hierboven vermeld, staat Excel het verwijderen van waarden in afzonderlijke cellen met de resultaten niet toe. Wanneer u dit probeert te doen, verschijnt er een foutmelding “Kan een deel van een gegevenstabel niet wijzigen”.

U kunt echter eenvoudig de gehele array van de resulterende waarden wissen. Hier is hoe:

1. afhankelijk van uw behoeften, selecteert u alle cellen in de gegevenstabel of alleen de cellen met de resultaten.
2. druk op de Delete-toets.

klaar!, 🙂

resultaten van de gegevenstabel bewerken

omdat het niet mogelijk is om een deel van een array in Excel te wijzigen, kunt u geen afzonderlijke cellen met berekende waarden bewerken. U kunt alleen al deze waarden vervangen door uw eigen door de volgende stappen uit te voeren:

1. Selecteer alle resulterende cellen.
2. verwijder de TABELFORMULE in de formulebalk.
3. Typ de gewenste waarde en druk op Ctrl + Enter.,

Dit zal dezelfde waarde in alle geselecteerde cellen invoegen:

zodra de TABELFORMULE is verdwenen, wordt de vroegere gegevenstabel een gebruikelijk bereik en bent u vrij om elke afzonderlijke cel normaal te bewerken.

hoe de gegevenstabel handmatig te herberekenen

als een grote gegevenstabel met meerdere variabele waarden en formules uw Excel vertraagt, kunt u automatische herberekeningen in die en alle andere gegevenstabellen uitschakelen.,

Ga hiervoor naar het tabblad Formules > Rekengroep, klik op de knop Berekeningsopties en klik vervolgens op Automatisch behalve gegevenstabellen.

Dit schakelt automatische gegevenstabelberekeningen uit en versnelt herberekeningen van de gehele werkmap.

om uw gegevenstabel handmatig te herberekenen, selecteert u de resulterende cellen, d.w.z. de cellen met TABLE () – formules, en drukt u op F9.

Dit is hoe u een gegevenstabel maakt en gebruikt in Excel., Om de voorbeelden die in deze zelfstudie zijn besproken nader te bekijken, bent u van harte welkom om onze voorbeeldwerkmap Excel-gegevenstabellen te downloaden. Ik dank u voor het lezen en zou blij zijn om je volgende week weer te zien!

• Wat-als analyse met Doel zoeken in Excel
• Hoe te gebruiken Solver in Excel: stap-voor-stap met voorbeelden
• Hoe rijen te groeperen in Excel