Hur man skapar en datatabell i ExcelLär dig hur du använder en (1) variabel och två (2) variabla datatabeller i Excel
I dataanalys kan du ofta behöva skapa olika scenarier med olika indata för att komma fram till olika resultat.
Till exempel kan du planera olika scenarier för att producera en viss produkt med olika råmaterialsammansättningar.
Ett scenario kan ha en 70% – 30% blandning, medan ett annat kan ha en 85%-15% blandning. Att skapa dessa många scenarier kan vara tidskrävande.
Lyckligtvis har Excel en funktion som snabbt kan skapa scenarier åt dig istället för att manuellt skapa dem.
Det jag syftar på är datatabellsfunktionen, som tillåter dig att skapa… datatabeller!
De är särskilt användbara om du använder en formel som beror på flera värden (och du vill testa den med olika indatakombinationer).
Istället för att manuellt modifiera formeln för att testa alla kombinationer av indata kommer datatabellen att göra det åt dig.
För närvarande tillåter Excel endast skapandet av en (1) variabel och två (2) variabel datatabeller.
Denna begränsning gäller endast antalet indataceller, tack och lov.
Det finns ingen begränsning för antalet scenarier du kan skapa med en datatabell. Förhoppningsvis kan Excel-utvecklingsteamet i framtiden förbättra denna funktion för att tillåta fler indataceller.
I denna artikel kommer jag att visa dig hur du skapar en datatabell i Excel och vad du kan göra med den.
Att veta hur man skapar en datatabell i Excel kan hjälpa dig mycket i din tänk-om-analys på vilken dataset som helst som kräver det.
Låt oss komma igång.
Skapa en envariabel datatabell i Excel
En envariabel datatabell tillåter testning av en serie värden för en enda indatacell.
Datatabellen kommer sedan att visa hur dessa värden påverkar resultatet av den använda formeln.
Till exempel kan du använda en envariabel datatabell för att visa hur olika räntor påverkar det framtida värdet av en investering.
Det kommer att vara lättare att förklara vad en envariabel datatabell gör med en illustration.
Så här är en:
Detta är en tabell som beräknar det framtida värdet av en investering:
- Cell B2 innehåller beloppet för den initiala investeringen
- I cell B3 hittar du den årliga räntan för investeringen
- Cell B4 innehåller antalet sammansatta perioder
- Cell B5 innehåller perioden (i år) för investeringen
- Slutligen innehåller cell B7 det framtida värdet av investeringen. För att beräkna detta värde används denna formel:
Vad skulle det framtida värdet av investeringen vara om den tjänar en annan årlig ränta? För att ta reda på det kommer vi att använda en envariabel datatabell.
Hur man skapar en envariabel datatabell
- Låt oss sätta upp området där datatabellen ska skapas. Vi kan välja att ha tabellen kolumnorienterad eller radorienterad. För nu, låt oss skapa en kolumnorienterad tabell. Här är hur kolumnhuvudet för datatabellen kommer att se ut:
- I kolumnen “Årlig ränta” ange de olika räntorna som du vill testa. Låt bara den första tomma cellen innehålla värdet “aktuell“. Detta är så att datatabellen som vi kommer att skapa har en grund för sina beräkningar.
- I kolumnen “Slutsaldo”, gör en referens till cellen som innehåller formeln i den första tomma cellen. I vår illustration kommer detta att vara cell B7. Lämna de andra cellerna i kolumnen tomma eftersom datatabellen kommer att fylla i dem åt dig.
- Välj de två kolumnerna (inkludera inte kolumnhuvudena).
- Vi är redo att skapa datatabellen. Öppna fliken Data. På höger sida av menyfliksområdet bör du se knappen Tänk-om-analys. Klicka på den.
- Bland alternativen som visas, välj Datatabell.
- Detta kommer att öppna dialogrutan Datatabell. Det är här vi kommer att ange indatacellen. Eftersom vi skapar en kolumnorienterad datatabell kommer vi att fylla i Kolumnindatacellen. Referera till cellen som innehåller den aktuella variabeln (i vår illustration kommer detta att vara cell B3). Tryck på OK-knappen efter att ha gjort det.
- Vi har framgångsrikt skapat en envariabel datatabell. Den bör nu visa de olika framtida värdena med de olika årliga räntorna. Formatera tabellen som du tycker passar.
(Du kan också ha datatabellen radorienterad. För att göra detta, ange indatacellen i textrutan Radindatacell istället för textrutan Kolumnindatacell. Du kan också testa flera formler med en envariabel datatabell. Lägg bara till en annan rad eller kolumn och gör en referens till cellen som innehåller formeln. Gör detta för varje ytterligare formel som du vill testa).
Kan jag fortfarande skapa en datatabell i Excel om alternativet Infoga kolumn är nedtonat?
Om alternativet Infoga kolumn är nedtonat i Excel behöver du inte oroa dig. Det finns flera lösningar för nedtonad infoga kolumn i excel som kan återaktivera funktionen. Kontrollera arket för skyddade inställningar eller se över filtreringar som kan påverka dina möjligheter att infoga kolumner.
Skapa en tvåvariabel datatabell i Excel
En tvåvariabel datatabell tillåter testning av en serie värden för en kombination av två indataceller.
Datatabellen kommer sedan att visa hur dessa kombinationer av värden påverkar resultatet av den använda formeln.
Till exempel kan du använda en tvåvariabel datatabell för att visa hur olika räntor och perioder (i år) påverkar det framtida värdet av en investering.
För att illustrera, låt oss använda samma tabell som vi använde i vårt envariabel datatabell exempel:
Låt oss se hur en förändring i den årliga räntan och perioden (i år) påverkar det framtida värdet av investeringen. För att göra det kommer vi att skapa en tvåvariabel datatabell.
Hur man skapar en tvåvariabel datatabell i Excel
- Låt oss sätta upp området där datatabellen ska skapas. Eftersom vi använder två indataceller kommer vi att använda en uppsättning rader för en indatacell och en uppsättning kolumner för den andra. I vår illustration kommer vi att använda den första tomma kolumnen för att ange de olika årliga räntorna. Sedan kommer vi att använda den första tomma raden för att ange de olika perioderna (i år):
- Ange de olika variablerna i sina respektive områden (årliga räntor i den första tomma kolumnen och perioder i den första tomma raden):
- I den första tomma cellen i tabellen vi skapade, gör en referens till cellen som innehåller formeln. I vår illustration kommer detta att vara cell B7.
- Välj datatabellområdet (inte inklusive rad- och kolumnhuvudena).
- Vi är nu redo att skapa vår datatabell. Öppna fliken Data. På höger sida av menyfliksområdet bör du se knappen Tänk-om-analys. Klicka på den.
- Bland alternativen som visas, välj Datatabell.
- Detta kommer att öppna dialogrutan Datatabell. Eftersom vi skapar en tvåvariabel datatabell kommer vi att fylla i både textrutorna Radindatacell och Kolumnindatacell. För Radindatacellen, ange cellen som relaterar till radvariablerna (cell B5 i vår illustration). För Kolumnindatacellen, ange cellen som relaterar till kolumnvariablerna (cell b3 i vår illustration). Tryck på OK-knappen efter att ha gjort det.
- Vi har framgångsrikt skapat vår tvåvariabel datatabell. Den bör nu visa de olika framtida värdena baserat på de olika kombinationerna av årliga räntor och perioder. (Formatera tabellen som du tycker passar).
Slutsats
Och så skapar du en envariabel eller tvåvariabel datatabell i Excel.
Såg du hur praktisk en datatabell är för att utföra en tänk-om-analys?
Låt mig veta dina tankar om denna Excel-funktion i kommentarerna.