hur man gör och använder en datatabell i Excel

handledningen visar hur man använder datatabeller för What-If-analys i Excel. Lär dig hur du skapar en tabell med en variabel och två variabler för att se effekterna av ett eller två inmatningsvärden på din formel och hur du ställer in en datatabell för att utvärdera flera formler samtidigt.

Du har byggt en komplex formel beroende på flera variabler och vill veta hur ändra dessa ingångar ändrar resultaten., I stället för att testa varje variabel individuellt, gör en What-if analys Datatabell och observera alla möjliga resultat med en snabb blick!

• datatabell i Excel
• skapa en variabel datatabell
• gör två variabla datatabell
• datatabell för att utvärdera flera formler
• Excel – datatabeller-3 Saker du borde veta
• ta bort en datatabell i Excel
• redigera datatabellresultat
• räkna om datatabell manuellt

Vad är en datatabell i Excel?,

i Microsoft Excel är en datatabell ett av analysverktygen för What-If som låter dig prova olika inmatningsvärden för formler och se hur förändringar i dessa värden påverkar formlerutgången.

datatabeller är särskilt användbara när en formel beror på flera värden, och du vill experimentera med olika kombinationer av ingångar och jämföra resultaten.

för närvarande finns det en variabel Datatabell och två variabeldatatabell. Även begränsad till högst två olika inmatningsceller, en datatabell kan du testa så många variabelvärden som du vill.,

hur man skapar en variabel datatabell i Excel

en variabel datatabell i Excel gör det möjligt att testa en rad värden för en enda inmatningscell och visar hur dessa värden påverkar resultatet av en relaterad formel.,

för att hjälpa dig att bättre förstå den här funktionen kommer vi att följa ett specifikt exempel snarare än att beskriva generiska steg.

anta att du funderar på att deponera dina besparingar i en bank, som betalar 5% ränta som föreningar varje månad. För att kontrollera olika alternativ, har du byggt följande sammansatt ränta kalkylator där:

• B8 innehåller FV formel som beräknar slutbalansen.
• B2 är den variabel du vill testa (initial investment).,

och nu, låt oss göra en enkel What-If analys för att se vad dina besparingar kommer att vara i 5 år beroende på mängden av din ursprungliga investering, allt från $1,000 till $6,000.

här är stegen för att skapa en datatabell med en variabel:

1. ange variabelvärdena antingen i en kolumn eller över en rad. I det här exemplet kommer vi att skapa en kolumnorienterad datatabell, så vi skriver våra variabelvärden i en kolumn (D3:D8) och lämnar minst en tom kolumn till höger för resultaten.,
2. Skriv din formel i cellen en rad ovanför och en cell till höger om variabelvärdena (E2 i vårt fall). Eller länka den här cellen till formeln i din ursprungliga datauppsättning (om du bestämmer dig för att ändra formeln i framtiden behöver du bara uppdatera en cell). Vi väljer det senare alternativet och anger den här enkla formeln i E2:=B8
   
   tips., Om du vill undersöka effekten av variabelvärdena på andra formler som refererar till samma inmatningscell anger du ytterligare formel(er) till höger om den första formeln, som visas i det här exemplet.
3. Välj datatabellsintervallet, inklusive din formel, celler med variabelvärden och tomma celler för resultaten (D2:E8).,
4. gå till fliken Data > Data verktyg grupp, klicka på What-If analys knappen, och klicka sedan på Datatabell…
   
5. i Datatabell dialogrutan, klicka i kolumnen inmatningscell rutan (eftersom våra investeringsvärden är i en kolumn), och välj den variabel cell som refereras i din formel. I det här exemplet väljer vi B3 som innehåller det ursprungliga investeringsvärdet.
   
6. Klicka på OK, och Excel fyller omedelbart de tomma cellerna med resultat som motsvarar variabelvärdet i samma rad.,
7. använd önskat nummerformat till resultaten (valuta i vårt fall), och du är bra att gå!

Nu kan du ta en snabb titt på din datatabell med en variabel, undersöka möjliga saldon och välja den optimala insättningsstorleken:

Radorienterad datatabell

ovanstående exempel visar hur du ställer in en vertikal eller kolumnorienterad datatabell i Excel., Om du föredrar en horisontell layout är det här vad du behöver göra:

1. skriv variabelvärdena i rad och lämna minst en tom kolumn till vänster (för formeln) och en tom rad nedan (för resultaten). I det här exemplet anger vi variabelvärdena i cellerna F3: J3.
2. ange formeln i cellen som är en kolumn till vänster om ditt första variabelvärde och en cell nedan (E4 i vårt fall).,
3. gör en datatabell som diskuterats ovan, men skriv in inmatningsvärdet (B3) i rutan rad indatacell:
   
4. Klicka på OK, och du kommer att ha följande resultat:
   

hur man gör en två variabeldatatabell i Excel

en tvåvariabel datatabell visar hur man gör olika kombinationer av 2 uppsättningar variabla värden påverkar formelresultatet. Med andra ord, det visar hur ändra två ingångsvärden av samma formel ändrar utdata.,

stegen för att skapa en datatabell med två variabler i Excel är i princip desamma som i ovanstående exempel, förutom att du anger två intervall av möjliga inmatningsvärden, en i rad och en annan i en kolumn.

för att se hur det fungerar, låt oss använda samma sammansatt ränta kalkylator och undersöka effekterna av storleken på den ursprungliga investeringen och antalet år på balansen. För att få det gjort, Ställ in din datatabell på så sätt:

1. ange din formel i en tom cell eller länka den cellen till din ursprungliga formel., Se till att du har tillräckligt med tomma kolumner till höger och tomma rader nedan för att rymma dina variabelvärden. Som tidigare länkar vi cellen E2 till den ursprungliga FV-formeln som beräknar balansen: =B8
2. Skriv en uppsättning ingångsvärden under formeln, i samma kolumn (investeringsvärden i E3:E8).
3. ange den andra uppsättningen variabelvärden till höger om formeln, i samma rad (antal år i F2:H2).,

   vid denna tidpunkt bör din två variabla datatabell likna detta:
   

4. Välj hela datatabellen, inklusive formeln, raden och kolumnen för variabelvärdena och cellerna där de beräknade värdena kommer att visas. Vi väljer intervallet E2: H8.,
5. skapa en datatabell på det redan välbekanta sättet: datafliken > What-If Analysis button > Datatabell…
6. i rutan rad input cell anger du referensen till inmatningscellen för variabelvärdena i raden (i det här exemplet är det B6 som innehåller Årvärdet).
7. i rutan Kolumninmatningscell anger du referensen till inmatningscellen för variabelvärdena i kolumnen (B3 som innehåller det ursprungliga investeringsvärdet).
8. Klicka på OK.,n vårt fall), och analysera resultaten:

   

datatabell för att jämföra flera resultat

om du vill utvärdera mer än en formel samtidigt, Bygg din datatabell som visas i de tidigare exemplen och ange ytterligare formel(er):

• till höger om den första formeln vid en vertikal datatabell organiserad i kolumnerna
• under den första formeln vid en horisontell datatabell organiserad i rader

för att datatabellen ”Multi-Formula” ska fungera korrekt bör alla formler hänvisa till samma inmatningscell.,

som ett exempel, låt oss lägga till ytterligare en formel till vår envariabel datatabell för att beräkna räntan och se hur den påverkas av storleken på den ursprungliga investeringen. Här är vad vi gör:

1. i cell B10, beräkna räntan med denna formel: =B8-B3
2. ordna datatabellens källdata som vi gjorde tidigare: variabelvärden i D3:D8 och E2 kopplade till B8 (Balansformel).,
3. Lägg till ytterligare en kolumn i datatabellsintervallet (kolumn F) och länk F2 till B10 (ränteformel):
   
4. Välj det utökade datatabellsintervallet (D2:F8).
5. öppna dialogrutan Datatabell genom att klicka på fliken Data > What-If Analysis > Datatabell…
6. i rutan Kolumninmatningscell, mata in inmatningscellen (B3) och klicka på OK.,

Voilà, du kan nu observera effekterna av dina variabelvärden på båda formlerna:

datatabell i Excel-3 Saker du borde veta

för att effektivt använda datatabeller i Excel, kom ihåg dessa 3 enkla fakta:

1. för att en datatabell ska skapas framgångsrikt måste inmatningscellen(erna) vara på samma ark som datatabellen (er). bord.,
2. Microsoft Excel använder tabellen(row_input_cell, colum_input_cell) för att beräkna datatabellresultat:
   • i en variabel datatabell utelämnas ett av argumenten beroende på layouten (kolumnorienterad eller radorienterad). I vår horisontella datatabell med en variabel är formeln =TABLE(, B3) där B3 är kolumninmatningscellen.
   • i två variabeldatatabellen är båda argumenten på plats. Till exempel =TABLE(B6, B3) där B6 är raden inmatningscell och B3 är kolumnen inmatningscell.,

   TABELLFUNKTIONEN anges som en matrisformel. För att försäkra dig om detta, välj vilken cell som helst med det beräknade värdet, titta på formelfältet och notera {curly brackets} runt formeln. Det är dock inte en vanlig matrisformel – du kan inte skriva den i formelfältet och du kan inte redigera en befintlig. Det är bara ”för show”.

3. eftersom datatabellresultaten beräknas med en matrisformel kan de resulterande cellerna inte redigeras individuellt. Du kan bara redigera eller ta bort hela matrisen av celler som förklaras nedan.,

ta bort en datatabell i Excel

Som nämnts ovan tillåter Excel inte att radera värden i enskilda celler som innehåller resultaten. När du försöker göra detta visas ett felmeddelande ”kan inte ändra en del av en datatabell”.

Du kan dock enkelt rensa hela matrisen av de resulterande värdena. Så här:

1. beroende på dina behov väljer du alla datatabellceller eller bara cellerna med resultaten.
2. tryck på Delete-tangenten.

klar!, 🙂

så här redigerar du datatabellresultat

eftersom det inte går att ändra en del av en array i Excel kan du inte redigera enskilda celler med beräknade värden. Du kan bara ersätta alla dessa värden med din egen genom att utföra dessa steg:

1. Välj alla resulterande celler.
2. ta bort TABELLFORMELN i formelfältet.
3. skriv önskat värde och tryck på Ctrl + Enter.,

detta kommer att infoga samma värde i alla markerade celler:

När TABELLFORMELN är borta blir den tidigare datatabellen ett vanligt intervall, och du är fri att redigera en enskild cell normalt.

så här omberäknar du datatabellen manuellt

om en stor datatabell med flera variabelvärden och formler saktar ner Excel kan du inaktivera automatiska omberäkningar i det och alla andra datatabeller.,

för detta, gå till fliken Formler > Beräkningsgrupp, klicka på knappen beräkningsalternativ och klicka sedan på automatisk utom datatabeller.

detta kommer att stänga av automatiska datatabeller beräkningar och påskynda omräkning av hela arbetsboken.

om du vill räkna om datatabellen manuellt väljer du de resulterande cellerna, dvs. cellerna med tabellformler och trycker på F9.

Så här skapar och använder du en datatabell i Excel., För att få en närmare titt på exemplen diskuterade denna handledning, är du välkommen att ladda ner vår exempel Excel datatabeller arbetsbok. Jag tackar dig för att läsa och skulle vara glad att se dig igen nästa vecka!

• vad-om analys med Målsök i Excel
• hur man använder lösare i Excel: steg för steg med exempel
• hur grupperar man rader i Excel