Hoe de What-If-analysetools in Microsoft Excel te gebruiken

Wanneer u uw gegevens in Microsoft Excel analyseert, wilt u misschien enkele vergelijkingen maken, zoals “Wat als ik optie A kies in plaats van optie B?” Met behulp van de ingebouwde What-If Analysis-tools in Excel kunt u gemakkelijker getallen en bedragen vergelijken, bijvoorbeeld om baansalarissen, leningopties of inkomsten- en uitgavenscenario’s te evalueren.

De tools voor wat-als-analyse in Excel omvatten Scenariobeheer, Doelzoeken en Gegevenstabel. Laten we, om het doel van deze tools zo goed mogelijk uit te leggen, voorbeelden van elk bekijken.

Scenariomanager

Voer met behulp van Scenariobeheer waarden in die u kunt wijzigen om wisselende resultaten te zien. Maak als bonus een Scenario-overzichtsrapport om de bedragen of getallen naast elkaar te vergelijken.

Stel dat u een evenement plant en een keuze maakt tussen een paar thema’s met verschillende kosten. Stel de prijzen van elk thema in om te zien hoeveel ze kosten om ze te vergelijken.

Creëer verschillende scenario’s voor verschillende situaties om u te helpen bij het nemen van beslissingen.

Hoe de Scenariomanager te gebruiken

Als u klaar bent om verschillende situaties te vergelijken, zoals het bovenstaande voorbeeld, volgt u deze stappen om Scenariobeheer in Excel te gebruiken.

• Voer de gegevens voor uw eerste scenario in uw blad in. Aan de hand van het eerdere voorbeeld vergelijken we de themakosten voor ons evenement en voeren we de uitgaven voor ons strandthema in cel A2 tot en met A6 in en hun kosten in cel B2 tot en met B6. We tellen de prijzen bij elkaar op in cel B7 om de totale kosten te zien.

• Om deze details aan Scenariobeheer toe te voegen, gaat u naar het tabblad “Gegevens” en het gedeelte “Voorspelling” van het lint. Open het vervolgkeuzemenu “Wat-als-analyse” en kies “Scenariobeheer”.

• Klik op “Toevoegen”.

• Geef uw scenario een naam (we gebruiken “Strandthema”) en voer de cellen in die u wilt aanpassen in het veld “Changing cells”. U kunt ook uw cursor door de cellen in uw blad slepen om dat veld te vullen. Voer eventueel een andere opmerking dan de standaard in en klik op “OK”.

• De waarden in het veld “Cellen wijzigen” moeten overeenkomen met die op uw blad, maar u kunt ze hier aanpassen. Klik op “OK” om door te gaan.

• Nu je het eerste scenario hebt toegevoegd, zie je het in de Scenariomanager staan. Selecteer “Toevoegen” om uw volgende scenario in te stellen.

• Voer de details voor het tweede scenario in zoals u deed voor het eerste. Voeg de naam, veranderende cellen en optionele opmerking toe en klik vervolgens op “OK”. In ons voorbeeld voeren we “Vegas-thema” in en hetzelfde celbereik, B2 tot en met B6, om gemakkelijk een vergelijking ter plekke te zien.

• Voer de waarden voor uw tweede scenario in het venster Scenariowaarden in. Als u dezelfde cellen gebruikt als de eerste, ziet u die ingevuld. Voer degene in die u wilt gebruiken en klik op “OK”.

• Kies het scenario dat u wilt bekijken in de lijst in het venster Scenariobeheer en klik op “Toon”.

• De waarden in uw blad worden bijgewerkt om het geselecteerde scenario weer te geven.

• Ga door met het toevoegen en weergeven van aanvullende scenario’s om de bijgewerkte waarden in uw blad te bekijken. Zodra u degene heeft gevonden die u in uw blad wilt bewaren, selecteert u “Sluiten” om Scenariobeheer af te sluiten.

Bekijk het scenariooverzicht

Bekijk de Scenariosamenvatting om al uw scenario’s tegelijk te zien en ze naast elkaar te vergelijken.

• Keer terug naar “Gegevens -> Wat-als-analyse -> Scenariobeheer” en klik vervolgens op “Samenvatting”.

• Kies het rapporttype dat u wilt bekijken: ‘Scenario-overzicht’ of ‘Scenario- draaitabelrapport ‘. Optioneel, als u uw resultaat wilt weergeven, voert u de cel in die het bevat en klikt u op “OK”.

In ons voorbeeld selecteren we ‘Samenvatting scenario’, waardoor het rapport op een nieuw bladtabblad wordt geplaatst. U zult ook merken dat het rapport optioneel celgroepering kan bevatten om bepaalde delen van het rapport te verbergen.

Houd er rekening mee dat als u de details aanpast in Scenariobeheer, het rapport niet automatisch wordt bijgewerkt en u dus een nieuw rapport moet genereren.

Doel zoeken

De Goal Seek-tool werkt enigszins tegengesteld aan de Scenario Manager. Met deze tool heb je een bekende uitkomst en voer je verschillende variabelen in om te zien hoe je tot die uitkomst kunt komen.

Misschien verkoopt u bijvoorbeeld producten en heeft u een jaarlijks winstdoel. U wilt weten hoeveel eenheden u moet verkopen of tegen welke prijs om uw doel te bereiken. Goal Seek is de ideale tool om antwoorden te vinden.

Met Goal Seek kan slechts één variabele of invoerwaarde worden gebruikt. Gebruik dit voor die scenario’s waarin u de resterende waarden vooraf hebt.

Doelzoeken gebruiken

In een voorbeeld van de Goal Seek-tool hebben we 1.500 producten te verkopen en willen we een winst maken van $ 52.000. We gebruiken Goal Seek om te bepalen tegen welke prijs we ons product moeten verkopen om dat doel te bereiken.

• Begin met het invoeren van de waarden en formules in uw blad, volgens uw scenario. Aan de hand van ons voorbeeld voeren we de huidige hoeveelheid in cel B2 in, de geschatte prijs in cel B3 en een formule voor de winst in cel B4, namelijk =B2*B3.

• Ga naar het tabblad “Gegevens”, open het vervolgkeuzemenu “Wat-als-analyse” en kies “Doelzoeken”.

• Voer de volgende waarden in en klik vervolgens op “OK”:
  • Stel cel in : de celverwijzing (met de formule) voor de waarde die u wilt wijzigen om het gewenste resultaat te bereiken. In ons voorbeeld is dit cel B4.
  • To value : de waarde van uw gewenste resultaat. Voor ons is dit 52000.
  • Door cel te wijzigen : de celverwijzing die u wilt wijzigen om het resultaat te bereiken. We gebruiken cel B3, omdat we de prijs willen wijzigen.

• Klik op “OK” om de update van het vak “Status doel zoeken” te zien om een ​​oplossing weer te geven en uw bladwijziging om de aangepaste waarden te bevatten. In ons voorbeeld moeten we ons product verkopen voor $ 35 om ons doel van $ 52.000 te bereiken. Selecteer “OK” om de nieuwe waarden in uw blad te behouden.

Weet je : er zijn genoeg dingen die je kunt doen in Microsoft Excel, inclusief het invoegen van sparklines en minigrafieken .

Data tafel

Gebruik een gegevenstabel in Excel om een ​​reeks mogelijke numerieke situaties te bekijken.

Voor een ideaal voorbeeld kunt u leningopties bekijken. Door verschillende rentetarieven in te voeren, kunt u bij elk zien wat uw maandelijkse betaling zou zijn. Dit helpt u te bepalen welk tarief u moet kopen of bespreken met uw geldschieter.

Met een gegevenstabel kunt u maximaal twee variabelen gebruiken. Als je meer nodig hebt, gebruik dan de Scenario Manager.

Hoe een gegevenstabel te gebruiken

Volg de onderstaande stappen om een ​​gegevenstabel te gebruiken, de derde tool voor wat-als-analyse. Let op de gegevensinstelling.

We gebruiken bijvoorbeeld een gegevenstabel om te zien hoeveel onze leningbetalingen zouden zijn met verschillende rentetarieven via de volgende gegevens:

• Rentepercentage, aantal betalingen en geleend bedrag in cellen B3 tot en met B5.
• Een kolom Tarief met de te verkennen rentetarieven in cellen C3 tot en met C5.
• Een kolom Betaling met de formule voor de huidige betaling in cel D2.
• De resultaatcellen onder de formule in de kolom Betaling, automatisch ingevoerd met behulp van de tool Gegevenstabel. Hierin zien we de te betalen bedragen per rentepercentage.

Houd rekening met het volgende wanneer u uw gegevens en formule in uw blad invoert:

• Gebruik een rij- of kolomgeoriënteerde lay-out. Het bepaalt de plaatsing van uw formule.
• Voor een rij-georiënteerde lay-out plaatst u uw formule in de cel één kolom links van de beginwaarde en één cel onder de rij met de waarden.
• Voor een kolomgeoriënteerde lay-out plaatst u uw formule in de cel één rij erboven en één cel rechts van de kolom met de waarden.

In ons voorbeeld gebruiken we een enkele variabele (de rentevoet) in een kolomgeoriënteerde lay-out. Let op de plaatsing van onze formule in cel D2 (een rij boven en een cel rechts van onze waarden).

• Voer uw eigen gegevens in en selecteer de cellen met de formule, waarden en resultaatcellen. In ons voorbeeld selecteren we de cellen C2 tot en met D5.

• Ga naar het tabblad “Gegevens”, open het vervolgkeuzemenu “Wat-als-analyse” en kies “Gegevenstabel”.

• Voer de cel met de veranderende variabele voor uw gegevens in het vak Gegevenstabel in. Gebruik voor een rij-georiënteerde lay-out de “Rij-invoercel” en voor een kolom-georiënteerde lay-out de “Kolom-invoercel”. In ons voorbeeld gebruiken we de laatste en voeren we “B3” in, de cel met de rentevoet.

• Nadat u op “OK” in het vak Gegevenstabel hebt geklikt, zou u moeten zien dat de resultaatcellen worden gevuld met de gegevens die u verwacht. Ons voorbeeld omvat het bedrag van onze betaling voor elke verschillende rentevoet.

Merk op dat u twee variabelen in uw gegevenstabel kunt gebruiken in plaats van één, de rijgeoriënteerde lay-out kunt uitproberen of meer details en beperkingen van deze What-If Analysis-tool kunt bekijken op de ondersteuningspagina van Microsoft voor de functie .

Veel Gestelde Vragen

Hoe bewerk ik een bestaand scenario in Excel?

U kunt de naam en waarden voor een scenario wijzigen met Scenariobeheer. Open de tool door “Gegevens -> Wat-als-analyse -> Scenariobeheer” te selecteren. Kies het scenario uit de lijst en klik rechts op “Bewerken”. Breng uw wijzigingen aan en kies “OK” om ze op te slaan.

Als u in eerste instantie een scenario-overzichtsrapport hebt gemaakt, moet u het rapport opnieuw genereren om de bijgewerkte details te zien.

Kan ik voorkomen dat Excel een gegevenstabel opnieuw berekent?

Als uw werkmap een gegevenstabel bevat, berekent Excel automatisch die gegevenstabel opnieuw, zelfs als er geen wijzigingen zijn. U kunt deze optie echter uitschakelen als u dat wilt.

Ga naar het tabblad “Formule”, open het vervolgkeuzemenu “Berekeningsopties” in de groep Berekening en selecteer “Automatisch behalve voor gegevenstabellen”.

Om uw gegevenstabel handmatig opnieuw te berekenen, selecteert u de formule(s) en drukt u op F9.

Welke andere analysetools biedt Excel?

Excel biedt veel verschillende soorten hulpprogramma’s voor gegevensanalyse, afhankelijk van wat u nodig heeft. Om er een paar te noemen, kunt u voorwaardelijke opmaak gebruiken om specifieke gegevens te markeren, Snelle analyse voor opmaak, grafieken en tabellen, en Power Query voor robuuste gegevensanalyse.

U kunt ook basisfuncties van Excel gebruiken , zoals filters , om uw gegevens te verfijnen, slicers om tabellen en grafieken te filteren, en de tool Gegevens analyseren om antwoorden te krijgen op vragen over uw gegevens.

Ga voor extra hulp bij deze functies en meer naar het tabblad ‘Help’ in Excel op Windows of gebruik de menuoptie ‘Vertel me’ in Excel op Mac.

Afbeelding tegoed: Pixabay . Alle screenshots door Sandy Geschrevenhuis.