Dit is het eerste artikel van de vijfdelige reeks over gegevensanalyse in Excel. In deze sectie zal ik u laten zien hoe u een variabele gegevenstabel in Excel maakt en gebruikt.

Download voorbeeldbestand

Bekijk Video – één-variabele gegevenstabel in Excel

een variabele gegevenstabel in Excel is het meest geschikt in situaties waarin u wilt zien hoe het eindresultaat verandert wanneer u een van de invoervariabelen wijzigt. Als u twee variabelen wilt wijzigen, gebruikt u twee variabele gegevenstabel of Scenario Manager.

Wanneer moet u een variabele gegevenstabel gebruiken in Excel

stel dat u een gegevensset hebt zoals hieronder getoond:

in de bovenstaande gegevensset hebben we het bedrag van de lening, de rente en het aantal maandelijkse betalingen.

De maandelijkse Betalingswaarde wordt berekend op basis van deze drie variabelen (Het is in rood omdat het een uitstroom van geld is). De volgende formule wordt gebruikt om de maandelijkse betaling te berekenen:

=PMT(B2/12,B3,B1)

nu wilt u misschien een analyse doen om te zien welk aantal maandelijkse betalingen past bij uw conditie, en hoe de maandelijkse betaling zou variëren op basis van het. Bijvoorbeeld, kunt u de looptijd van uw lening (aantal maandelijkse betalingen) te houden, zodat uw maandelijkse betaling bedrag is niet meer dan $1,000.

een snelle manier is om de gegevens in deze tabel te wijzigen en de formule wordt automatisch bijgewerkt. Als u echter wilt testen of voor veel verschillende maandelijkse betalingen, dan is een variabele gegevenstabel in Excel de beste manier om te gaan.

een variabele gegevenstabel instellen in Excel

Hier zijn de stappen om een variabele gegevenstabel in Excel in te stellen:

• in een kolom, hebben alle verschillende waarden van het aantal maandelijkse betalingen dat u wilt testen. In dit voorbeeld testen we op 72, 84, 96 … 240.
• Type =B4 in cel E1 te testen, wat één rij boven de waarden in de aangrenzende kolom aan de rechterkant is. Dit is een constructie die moet worden gevolgd wanneer u met één variabele gegevenstabel in Excel werkt. Zorg er ook voor dat de waarde in cel E1 afhankelijk is van het aantal maandelijkse betalingen. Het werkt niet als u de waarde handmatig invoert in cel E1.
  In dit geval verwijst cel E1 naar cel B4, die een waarde heeft die wordt berekend met behulp van een formule die de cellen B1, B2 en B3 gebruikt.
• nu is de gegevens helemaal ingesteld om te worden gebruikt in de één variabele gegevenstabel.
• Selecteer de gegevens (D1: E16). Ga naar Data Tab –> Data Tools –> wat als Analyse –> gegevenstabel.
• in het dialoogvenster Gegevenstabel, refereer naar cel B3 in kolom invoercelveld. Aangezien we een gegevenstabel met één variabele gebruiken, laat u de rij-invoercel leeg. We hebben ook gekozen voor kolom – invoerceloptie omdat de gegevens in een kolom zijn gestructureerd.
• klik op OK. Zodra u op OK klikt, vult het automatisch de cellen naast de variabele gegevenspunten (aantal maandelijkse betalingen).

bijvoorbeeld, als u wilt berekenen welk aantal maandelijkse betalingen U moet gebruiken om de maandelijkse betaling minder dan $1.000 te hebben. U kunt eenvoudig 144 maanden (12 jaar) selecteren.

opmerking:

• zodra u de waarden hebt berekend met behulp van de gegevenstabel, kan deze niet ongedaan worden gemaakt met behulp van Control + Z. U kunt echter handmatig alle waarden selecteren en verwijderen.
• u kunt geen enkele cel in de volledige reeks berekende waarden verwijderen/wijzigen. Aangezien dit een array is, moet u alle waarden verwijderen.
• u kunt dezelfde berekening uitvoeren door de gegevens horizontaal te rangschikken. Het enige verschil zou zijn dat u Rij-invoercel moet gebruiken in het dialoogvenster Gegevenstabel (in plaats van kolom-invoercel).

downloadbestand.. Probeer het zelf eens

U kunt ook de volgende Excel-Tutorials leuk vinden:

• standaarddeviatie berekenen in Excel.
• Histogram maken in Excel.
• berekenen van het gewogen gemiddelde in Excel.
• berekenen van het CAGR in Excel.