Gegevenstabel in Excel

Wat is een gegevenstabel in Excel?

Gegevenstabellen in Excel worden gebruikt om variabelen en hun impact op het resultaat en de algemene gegevens te vergelijken, de gegevenstabel is een soort wat-als-analyse-tool in Excel en is aanwezig op het gegevens-tabblad in de wat-als-analyse, deze tool vraagt ​​om een rij-invoer en een kolom-invoertabel om een ​​gegevenstabel te maken en de impact wordt berekend door één variabele of twee variabele gegevenstabel.

Typen gegevenstabellen in Excel

  1. Gegevenstabel met één variabele
  2. Gegevenstabel met twee variabelen

1) Gegevenstabel met één variabele in Excel

De basisvereiste voor de gegevenstabel is om een ​​basis- of testmodel te maken. U moet uw gegevenstabel instrueren welke formules uit uw gegevensmodel u wilt testen. De gegevenstabel met één variabele is het meest geschikt als u wilt zien hoe het uiteindelijke resultaat verandert wanneer u de invoervariabelen wijzigt.

Voorbeeld

U kunt deze DATA-tabel Excel-sjabloon hier downloaden - DATA-tabel Excel-sjabloon

De heer Murali runt een fabriek genaamd ABC Pvt Ltd. Daarnaast maakt hij een schatting van de omzet voor het jaar 2019. Onderstaande tabel toont de omzet voor het jaar 2018 en de geschatte omzet op verschillende niveaus.

De bovenstaande tabel laat zien dat de omzet voor het jaar 2018 15 lakh USD is en een minimale groei van 12% verwacht voor het volgende jaar. Nu wil Murali een gegevenstabel, die de omzetgroeitabel laat zien met verschillende stijgingspercentages. Hij wil de scenariotabel in het onderstaande formaat.

Pas de gegevenstabeltechniek toe om de gewenste resultaten te krijgen.

Stap 1: Maak dit gegevenstabelformaat in een Excel-bestand. De geschatte omzet voor 2019 wordt weergegeven in cel B5.

Stap 2: Selecteer cel D8 en geef een link naar cel B5 (cel voor geschatte inkomsten). Nu toont de cel D8 de geschatte omzet voor 2019.

Stap 3: Selecteer het bereik van C8 tot D19.

Stap 4: Klik op het gegevenstabblad > Wat-als-analyse > gegevenstabel

Stap 5: Het dialoogvenster Gegevenstabel verschijnt. In de Kolominvoercel , selecteert u de cel B3 (die de minimale groei percentage bevat).

Aangezien het een gegevenstabel met één variabele is, laat u de rijinvoercel weg. De reden waarom we de cel B3 hebben geselecteerd, omdat we op basis van de verschillende groeisnelheden de scenario's gaan maken. Nu begrijpt de gegevenstabel dat 12% de geschatte omzet 15 lakh USD is. Evenzo zal het scenario's creëren voor 12,5%, 13,5%, 14,5% enzovoort.

Stap 6: Klik op ok om verschillende scenario's te creëren.

Nu toont het bereik D9: D19 enkele nieuwe waarden. Uit de tabel blijkt heel duidelijk dat de geschatte omzet van @ 12,5% groeipercentage 16.875 lakh USD zal zijn en dat de geschatte omzet van @ 14,5% 17.175 lakh USD zou zijn, enzovoort.

Dit is hoe een voorbeeld van een tabel met variabele gegevens werkt. U kunt dit ook in een grafiek laten zien.

2) Gegevenstabel met twee variabelen in Excel

We kunnen een gegevenstabel met twee variabelen gebruiken om scenario's te analyseren als twee variabelen tegelijk veranderen. Hiervoor hebben we twee bereiken met mogelijke invoerwaarden nodig voor dezelfde formule. Dat betekent dat het hier zowel de rij als de kolom zal beïnvloeden.

Voorbeeld

Bekijk nu dit voorbeeld van een gegevenstabel met twee variabelen.

De heer Murali dacht aan een andere omzetgroei. Evenzo is hij van plan zijn klanten kortingen te geven om de verkoopkansen te vergroten.

Onderstaande tabel toont Murlali's plan om de groei het komende jaar te vergroten. Hij wil de omzet schatten op verschillende groeianiveaus met verschillende discontovoeten.

Stap 1: Maak een bovenstaande gegevenstabel in Excel.

Stap 2: In de cel zet B6 de onderstaande formule om de uiteindelijke opbrengst na de korting te berekenen.

= B2 + (B2 * B3) - (B2 * B4)

Ten eerste wordt de groeivoet van het voorgaande jaar opgeteld en de discontovoet afgetrokken.

Cel D9 bevat de verwijzing naar de cel B6.

Nu toont de bovenstaande tabel het mogelijke groeipercentage van D10 naar D18 (kolomwaarde) en een mogelijke discontovoet van E9 naar J9 (rijwaarde).

Stap 3: Selecteer het bereik D9: J18.

Stap 4: Klik op het gegevenstabblad > Wat-als-analyse > gegevenstabel

Stap 5: Het dialoogvenster Gegevenstabel verschijnt. In de Kolominvoercel , selecteert u de cel B3 (die de minimale groei percentage bevat) en in de rij Input Cell, selecteert u de cel B4.

Samen met de formule in cel D9 (verwijst naar cel B6 ), weet Excel dat het cel B4 moet vervangen door 2,5% (cel E9) en cel B3 door 12,5% (cel D10) enzovoort.

Stap 6: Klik op ok.

Als Murali nu streeft naar een groei van 13,5% met een discontovoet van 2,5%, dan is de omzet 16,65 lakh USD. Als Murali streeft naar het behalen van 17 lakh USD, is de maximale korting die hij volgend jaar kan geven 3% en het kan hem een ​​opbrengst van 17.025 lakh USD opleveren.

Dit is hoe nuttig de gegevenstabel is om verschillende scenariomodellen te creëren om het besluitvormingsproces te ondersteunen.

Belangrijke aantekeningen:

  • Een gegevenstabel laat u zien hoe u door het wijzigen van bepaalde waarden in uw functie het resultaat van de formule kunt wijzigen.
  • Het slaat de resultaten van veel variabele scenario's op in één tabel, zodat u het beste scenario voor uw bedrijf of project kunt afronden. De resultaten zijn in tabelformaat geschreven.
  • Het is een matrixformule waarmee meerdere berekeningen op één locatie mogelijk zijn.
  • Nadat u de waarden via de gegevenstabel heeft berekend, kunt u de actie niet ongedaan maken (Ctrl + Z). U kunt echter alle waarden handmatig uit de tabel verwijderen.
  • Het is niet toegestaan ​​om een ​​enkele cel in het datamodel te wijzigen. Omdat het een array is, moet u alles verwijderen.
  • Het zou zo verwarrend zijn om de rij-invoercel en de kolominvoercel te selecteren. U moet de cellen correct selecteren om nauwkeurige resultaten te krijgen.
  • De gegevenstabel in Excel hoeft niet altijd te worden vernieuwd, in tegenstelling tot de draaitabel.

Original text