Dit is het tweede artikel van de vijfdelige serie over gegevensanalyse in Excel. In deze sectie zal ik u laten zien hoe u Two Variable Data Table in Excel gebruikt.
Andere artikelen in deze serie:
- Eén variabele gegevenstabel in Excel.
- Scenariobeheer in Excel.
- Doel zoeken in Excel.
- Excel-oplosser.
Bekijk video - Gegevenstabel met twee variabelen in Excel
Gegevenstabel met twee variabelen is het meest geschikt in situaties waarin u wilt zien hoe het eindresultaat verandert wanneer twee van de invoervariabelen tegelijkertijd veranderen (in tegenstelling tot één variabele gegevenstabel waarbij slechts één van de invoervariabelen verandert).
Als u gegevens wilt analyseren wanneer meer dan 2 variabelen veranderen, is scenariomanager de juiste keuze.
Wanneer twee variabele gegevenstabel in Excel gebruiken?
Stel dat u een dataset heeft zoals hieronder weergegeven:
In de bovenstaande gegevensset hebben we het leenbedrag, de rentevoet en het aantal maandelijkse betalingen. Op basis van deze 3 invoervariabelen wordt de maandelijkse betaling berekend (het is in het 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 maken om te zien wat de ideale combinatie van leenbedrag en aantal maandelijkse betalingen zou moeten zijn om aan uw vereisten te voldoen. U wilt bijvoorbeeld de maandelijkse betaling op $ 500 of minder houden en analyseren welke combinatie van leningbedrag en looptijd u dit kan opleveren.
In een dergelijke situatie moet een tabel met twee variabelen worden gebruikt.
Twee variabele gegevenstabel instellen in Excel
Hier zijn de stappen om een tabel met twee variabele gegevens in Excel in te stellen:
- Zet in een kolom alle verschillende waarden die u wilt testen voor Aantal maandelijkse betalingen. In dit voorbeeld testen we voor 72, 84, 96 … 240. Houd tegelijkertijd de verschillende leenbedragwaarden in de rij net boven de kolomwaarden (één cel naar rechts beginnend) zoals weergegeven in de onderstaande afbeelding.
- Typ =B4 in cel D1, die één rij boven de waarden in de kolom staat. Dit is een constructie die moet worden gevolgd wanneer u met tabellen met twee variabele gegevens werkt. Zorg er ook voor dat de waarde in cel D1 afhankelijk is van beide variabelen (aantal maandelijkse betalingen en leenbedrag). Het werkt niet als u de waarde handmatig invoert in cel D1.
In dit geval verwijst cel D1 naar cel B4, die een waarde heeft die is berekend met een formule die de cellen B1, B2 en B3 gebruikt.
- Nu zijn de gegevens helemaal klaar om te worden gebruikt voor een berekening met twee variabele gegevenstabellen.
- Selecteer de gegevens (D1:J16). Ga naar het tabblad Gegevens -> Hulpmiddelen voor gegevens -> Wat als analyse -> Gegevenstabel
- Gebruik in het dialoogvenster Gegevenstabeldialoog de volgende verwijzingen:
- Rij-invoercel: $B$1
- Kolominvoercel: $B$3
- Klik OK. Zodra u op OK klikt, worden onmiddellijk alle lege cellen in het geselecteerde gegevensbereik gevuld. Het geeft u snel inzicht in de maandelijkse betalingen voor verschillende combinaties van leenbedrag en aantal maandelijkse betalingen.
Als u bijvoorbeeld de combinaties van leningbedrag en aantal maandelijkse betalingen wilt identificeren die zouden resulteren in een maandelijkse betaling van minder dan $ 500 per maand, kunt u eenvoudig deze methode met 2 variabele gegevenstabel gebruiken.
Opmerking:
- Nadat u de waarden hebt berekend met behulp van de gegevenstabel, kan dit niet ongedaan worden gemaakt met Control + Z. U kunt echter alle waarden handmatig selecteren en verwijderen.
- U kunt geen enkele cel in de hele set berekende waarden verwijderen/wijzigen. Aangezien dit een array is, moet u alle waarden verwijderen.
