En datatabell er et celleområde der du kan endre verdier i noen av cellene og finne forskjellige svar på et problem. Et godt eksempel på en datatabell bruker AVDRAG-funksjonen med ulike lånebeløp og renter for å beregne det rimelige beløpet på et boliglån. Eksperimentering med ulike verdier for å observere den tilsvarende variasjonen i resultatene er en vanlig oppgave i dataanalyse.
I Microsoft Excel er datatabeller en del av en serie med kommandoer som kalles What-If analyseverktøy. Når du konstruerer og analyserer datatabeller, utfører du hva-skjer-hvis-analyse.
Hva-skjer-hvis-analyse er prosessen med å endre verdiene i cellene for å se hvordan disse endringene vil påvirke resultatet av formler i regnearket. Du kan for eksempel bruke en datatabell til å variere rentesatsen og terminlengden for et lån – for å evaluere potensielle månedlige betalingsbeløp.
Typer hva-skjer-hvis-analyse
Det finnes tre typer hva-skjer-hvis-analyseverktøy i Excel: scenarioer, datatabeller og målsøking. Scenarioer og datatabeller bruker sett med inndataverdier til å beregne mulige resultater. Målsøking er forskjellig, den bruker ett enkelt resultat og beregner mulige inndataverdier som vil produsere dette resultatet.
I likhet med scenarioer hjelper datatabeller deg med å utforske et sett med mulige resultater. I motsetning til scenarioer viser datatabeller deg alle resultatene i én tabell i ett regneark. Bruk av datatabeller gjør det enkelt å undersøke en rekke muligheter med et øyekast. Siden du fokuserer på bare én eller to variabler, er resultatene enkle å lese og dele i tabellform.
En datatabell har ikke plass til mer enn to variabler. Hvis du vil analysere mer enn to variabler, bør du i stedet bruke scenarioer. Selv om den er begrenset til bare én eller to variabler (én for inndatacellen for raden og én for inndatacellen for kolonnen), kan en datatabell inneholde så mange forskjellige variabelverdier du vil. Et scenario kan ha maksimalt 32 forskjellige verdier, men du kan opprette så mange scenarioer du vil.
Mer informasjon i artikkelen Innføring i What-If Analyse.
Opprett enten én-variabel- eller tovariabel datatabeller, avhengig av antall variabler og formler du må teste.
Datatabeller med én variabel
Bruk en datatabell med én variabel hvis du vil se hvordan forskjellige verdier for én variabel i én eller flere formler vil endre resultatene av disse formlene. Du kan for eksempel bruke en datatabell med én variabel for å se hvordan ulike renter påvirker en månedlig avdragsbetaling ved hjelp av AVDRAG-funksjonen. Du skriver inn variabelverdiene i én kolonne eller rad, og resultatene vises i en tilstøtende kolonne eller rad.
I illustrasjonen nedenfor inneholder celle D2 betalingsformelen = AVDRAG(B3/12,B4,-B5), som refererer til inndatacellen B3.
Datatabeller med to variabler
Bruk en datatabell med to variabler til å se hvordan forskjellige verdier for to variabler i én formel vil endre resultatene av formelen. Du kan for eksempel bruke en datatabell med to variabler for å se hvordan ulike kombinasjoner av renter og lånevilkår vil påvirke en månedlig avdragsbetaling.
I illustrasjonen nedenfor inneholder celle C2 betalingsformelen = AVDRAG(B3/12,B4,-B5), som bruker to inndataceller, B3 og B4.
Datatabellberegninger
Når et regneark beregnes på nytt, beregnes også datatabeller på nytt, selv om det ikke har vært noen endring i dataene. Hvis du vil gjøre beregningen av et regneark som inneholder en datatabell raskere, kan du endre beregningsalternativene slik at regnearket beregnes på nytt automatisk, men ikke i datatabellene. Hvis du vil ha mer informasjon, kan du se delen Raskere beregning i et regneark som inneholder datatabeller.
En datatabell med én variabel inneholder inndataverdiene i én kolonne (kolonneorientert) eller på tvers av en rad (radorientert). Alle formler i en datatabell med én variabel må bare referere til én inndatacelle.
Gjør følgende:
-
Skriv inn listen over verdier du vil erstatte i inndatacellen – enten ned én kolonne eller over én rad. La noen tomme rader og kolonner være på hver side av verdiene.
-
Gjør ett av følgende:
-
Hvis datatabellen er kolonneorientert (variabelverdiene er i en kolonne), skriver du inn formelen i cellen én rad over og én celle til høyre for kolonnen med verdier. Denne datatabellen med én variabel er kolonneorientert, og formelen finnes i celle D2.
-
Hvis datatabellen er radorientert (variabelverdiene er i en rad), skriver du inn formelen i cellen én kolonne til venstre for den første verdien og én celle under raden med verdier.Hvis du vil undersøke effekten av ulike verdier på andre formler, skriver du inn flere formler i celler under den første formelen.
-
-
Merk celleområdet som inneholder formlene og verdiene du vil erstatte. I figuren ovenfor er dette området C2:D5.
-
Velg Hva-skjer-hvis-analyse >datatabell (i Dataverktøy-gruppen eller Prognose-gruppen i Excel 2016 ).
-
Gjør ett av følgende:
-
Hvis datatabellen er kolonneorientert, skriver du inn cellereferanse for inndatacellen i inndatacellefeltet for kolonne . I figuren ovenfor er inndatacellen B3.
-
Hvis datatabellen er radorientert, skriver du inn cellereferansen for inndatacellen i feltet Radinndatacelle .
Obs!: Når du har opprettet datatabellen, vil du kanskje endre formatet på resultatcellene. I figuren formateres resultatcellene som valuta.
-
Formler som brukes i en datatabell med én variabel, må referere til samme inndatacelle.
Følg disse trinnene
-
Gjør ett av disse:
-
Hvis datatabellen er kolonneorientert, skriver du inn den nye formelen i en tom celle til høyre for en eksisterende formel i den øverste raden i datatabellen.
-
Hvis datatabellen er radorientert, skriver du inn den nye formelen i en tom celle under en eksisterende formel i den første kolonnen i datatabellen.
-
-
Merk celleområdet som inneholder datatabellen og den nye formelen.
-
Velg Hva-skjer-hvis-analyse > datatabell (i Dataverktøy-gruppen eller Prognose-gruppen i Excel 2016 ).
-
Gjør ett av følgende:
-
Hvis datatabellen er kolonneorientert, skriver du inn cellereferansen for inndatacellen i inndatacelleboksen for kolonne .
-
Hvis datatabellen er radorientert, skriver du inn cellereferansen for inndatacellen i boksen Radinndatacelle .
-
En datatabell med to variabler bruker en formel som inneholder to lister med inndataverdier. Formelen må referere til to forskjellige inndataceller.
Gjør følgende:
-
Skriv inn formelen som refererer til de to inndatacellene, i en celle i regnearket.
I eksemplet nedenfor, der startverdiene for formelen legges inn i celle B3, B4 og B5, skriver du inn formelen =ANNUT(B3/12;B4;-B5) i celle C2.
-
Skriv inn én liste med inndataverdier i samme kolonne, under formelen.
I dette tilfellet skriver du inn de ulike rentene i celle C3, C4 og C5.
-
Skriv inn den andre listen i samme rad som formelen – til høyre.
Skriv inn lånevilkårene (i måneder) i celle D2 og E2.
-
Merk celleområdet som inneholder formelen (C2), både raden og kolonnen med verdier (C3:C5 og D2:E2), og cellene der du vil ha de beregnede verdiene (D3:E5).
I dette tilfellet velger du området C2:E5.
-
Velg Hva-skjer-hvis-analyse >datatabell (i Dataverktøy-gruppen eller Prognose-gruppen i Excel 2016 ) på Fanen Data (i Dataverktøy-gruppen eller Prognose-gruppen i Excel 2016 ).
-
Skriv inn referansen til inndatacellen for inndataverdiene i raden i inndatacellefeltet for rad.Skriv inn celle B4 i inndatacelleboksen for rad .
-
Skriv inn referansen til inndatacellen for inndataverdiene i kolonnen i inndatacellefeltet for kolonne.Skriv inn B3 i inndatacelleboksen for kolonne .
-
Velg OK.
Eksempel på en datatabell med to variabler
En to-variabel datatabell kan vise hvordan ulike kombinasjoner av renter og lånevilkår vil påvirke en månedlig boliglånsbetaling. I figuren her inneholder celle C2 betalingsformelen = AVDRAG(B3/12,B4,-B5), som bruker to inndataceller, B3 og B4.
Når du angir dette beregningsalternativet, oppstår det ingen datatabellberegninger når en ny beregning utføres i hele arbeidsboken. Hvis du vil beregne datatabellen manuelt, merker du formlene og trykker F9.
Følg disse trinnene for å forbedre beregningsytelsen:
-
Velg Alternativer for fil > > formler.
-
Velg Automatisk under Alternativer for beregning.
Tips!: Du kan også velge pilen på Beregningsalternativer på Formler-fanen, og deretter velge Automatisk.
Du kan bruke noen andre Excel-verktøy til å utføre hva-skjer-hvis-analyse hvis du har bestemte mål eller større sett med variable data.
Målsøking
Hvis du vet resultatet du kan forvente fra en formel, men ikke vet nøyaktig hvilken inndataverdi formelen trenger for å få dette resultatet, kan du bruke Goal-Seek-funksjonen. Se artikkelen Bruk målsøking for å finne ønsket resultat ved å justere en inndataverdi.
Problemløser i Excel
Du kan bruke Problemløser-tillegget i Excel til å finne den optimale verdien for et sett med inndatavariabler. Problemløser fungerer med en gruppe celler (kalt beslutningsvariabler, eller bare variabelceller) som brukes til å beregne formlene i mål- og begrensningscellene. Problemløser justerer verdiene i beslutningsvariabelcellene for å oppfylle grensene for begrensningsceller og gi ønsket resultat for målcellen. Mer informasjon i denne artikkelen: Definer og løs et problem ved hjelp av Problemløser.
Ved å koble forskjellige tall til en celle, kan du raskt finne forskjellige svar på et problem. Et godt eksempel er å bruke AVDRAG-funksjonen med ulike renter og låneperioder (i måneder) for å finne ut hvor mye av et lån du har råd til for et hjem eller en bil. Du skriver inn tallene i et celleområde som kalles en datatabell.
Her er datatabellen celleområdet B2:D8. Du kan endre verdien i B4, lånebeløpet og månedlige innbetalinger i kolonne D automatisk. Ved hjelp av en rentesats på 3,75 % returnerer D2 en månedlig betaling på KR 1 042,01 ved hjelp av denne formelen: =AVDRAG(C2/12,$B$3,$B$4).
Du kan bruke én eller to variabler, avhengig av antall variabler og formler du vil teste.
Bruk en test med én variabel for å se hvordan forskjellige verdier for én variabel i en formel vil endre resultatene. Du kan for eksempel endre rentesatsen for et månedlig avdrag ved hjelp av AVDRAG-funksjonen. Du angir variabelverdiene (rentesatsene) i én kolonne eller rad, og resultatene vises i en kolonne eller rad i nærheten.
I denne direkte arbeidsboken inneholder celle D2 betalingsformelen =ANNUITET(C2/12,$B$3,$B$4). Celle B3 er variabelcellen , der du kan koble til en annen periode (antall månedlige betalingsperioder). I celle D2 kobler ANNUT-funksjonen rentesatsen 3,75 %/12, 360 måneder og et lån på USD 225 000, og beregner en månedlig betaling på KR 1 042,01.
Bruk en test med to variabler for å se hvordan forskjellige verdier for to variabler i en formel vil endre resultatene. Du kan for eksempel teste ulike kombinasjoner av rentesatser og antall månedlige betalingsperioder for å beregne et avdrag.
I denne direkte arbeidsboken inneholder celle C3 betalingsformelen = AVDRAG($B$3/12,$B$2,B4), som bruker to variable celler, B2 og B3. I celle C2 kobler AVDRAG-funksjonen inn rentesatsen 3,875 %/12, 360 måneder og et lån på USD 225 000, og beregner et månedlig avdrag på USD 1 058,03.
Trenger du mer hjelp?
Du kan alltid spørre en ekspert i det tekniske fellesskapet for Excel eller få støtte i Fellesskap.
