Hvordan å lage og bruke en data-tabellen i Excel –

veiledningen viser hvordan du kan bruke data tabeller for Hva-Hvis-analyse i Excel. Lære hvordan å lage en en-variabel-og to-variabel-tabellen for å se effekten av en eller to input-verdiene på din formel, og hvordan du skal sette opp en data-tabellen for å vurdere flere formler på en gang.

Du har bygget en komplisert formel avhengig av flere faktorer og vil vite hvordan du endrer disse innganger endrer resultatene., I stedet for testing av hver variabel individuelt, gjøre en Hva-hvis-analyse-datatabell, og overhold alle mulige utfall med et raskt blikk!

• Data-tabellen i Excel
• Opprette en variabel data-tabellen
• Lag to variable data-tabellen
• Data-tabellen for å vurdere flere formler
• Excel-tabeller – 3 ting du bør vite
• Slett data-tabellen i Excel
• Rediger data tabell resultater
• Beregne data tabellen manuelt

Hva er en data-tabellen i Excel?,

I Microsoft Excel, en data-tabellen er en av de Hva-Hvis-Analyse-verktøy som gir deg mulighet til å prøve ut ulike input-verdier for formler og se hvordan endringer i disse verdiene påvirker formler utgang.

Data tabeller er spesielt nyttig når en formel avhenger av flere verdier, og du ønsker å eksperimentere med forskjellige kombinasjoner av inndata og sammenligne resultatene.

i Dag, det finnes en variabel data bord og to variable data-tabellen. Selv om de er begrenset til maksimalt to ulike input-cellene, en data-tabellen gir deg mulighet til å teste så mange variable verdier som du vil.,

Hvordan å lage en en-variabel-data-tabellen i Excel

En-variabel-data-tabellen i Excel tillater testing av en rekke verdier for en enkelt inngang celle, og viser hvordan disse verdiene vil påvirke resultatet av en tilknyttet formel.,

for Å hjelpe deg å bedre forstå denne funksjonen, vi kommer til å følge et konkret eksempel heller enn å beskrive generelle råd.

Tenk deg at du vurderer innskudd din sparekonto i en bank, som betaler 5% rente på at forbindelser månedlig. For å se på ulike alternativer, du har lagt følgende rentes rente kalkulator hvor:

• B8 inneholder FV formel som beregner utgående balanse.
• B2 er den variabelen du ønsker å teste (investering).,

Og nå, la oss gjøre et enkelt Hva-Hvis-analyse for å se hvilke besparelser vil bli i 5 år, avhengig av mengden av investeringen, alt fra $1000 til $6000.

Her er fremgangsmåten for å lage en en-variabel-data-tabellen:

1. skriv Inn den variable verdier, enten i en kolonne eller over en rad. I dette eksempelet skal vi lage en kolonne-orienterte data-tabellen, så skriver vi vår variable verdier i en kolonne (D3:D8) og la det være minst en tom kolonne til høyre for utfallet.,
2. Skriv inn formelen i cellen i en rad, over og én celle til høyre for den variable verdier (E2 i vårt tilfelle). Eller, koble denne cellen til formelen i det opprinnelige datasettet (hvis du bestemmer deg for å endre formelen i fremtiden, ville du trenger å oppdatere bare én celle). Vi velger det siste alternativet, og skriv inn denne enkle formelen i celle E2: =B8
   
   Tips., Hvis du ønsker å undersøke virkningen av variable verdier på andre formler som refererer til samme inngang celle, skriver du inn den ekstra formel(e) til høyre i den første formelen, som vist i dette eksemplet.
3. Velg data-tabellen serien, inkludert din formel, variable verdier celler, og tomme celler for resultatene (D2:E8).,
4. Gå til kategorien Data > Data Verktøy-gruppen, klikker du Hva-Hvis-Analyse-knappen, og klikk deretter Data-Tabellen…
   
5. I Data-Tabellen dialog-vinduet, klikk i Kolonnen Inngang celle-boksen (fordi Investeringene verdier i en kolonne), og velg variabelen cellen det refereres til i formelen. I dette eksemplet velger vi B3 som inneholder den opprinnelige investeringen.
   
6. Klikk OK, og Excel vil umiddelbart fylle ut de tomme cellene med resultater som tilsvarer de variable verdi i samme rad.,
7. Bruke den ønskede antallet format til resultater (Valuta i vårt tilfelle), og du er god til å gå!

Nå, kan du ta en rask titt på en-variabel-data-tabellen, undersøke mulige balanserer og velge den optimale innskudd størrelse:

Rad-orienterte data-tabellen

I eksemplet ovenfor viser deg hvordan du setter opp en vertikal, eller kolonne-orientert, data-tabellen i Excel., Hvis du foretrekker en horisontal layout, her er hva du trenger å gjøre:

1. Skriv inn den variable verdier i en rad, og etterlot seg minst en tom kolonne til venstre (for formel) og en tom rad nedenfor (for resultater). For dette eksemplet, vi går inn i det variable verdier i celler F3:J3.
2. skriv Inn formelen i celle som er en kolonne til venstre for første variabel verdi og én celle under (E4 i vårt tilfelle).,
3. Lag en datatabell som er diskutert ovenfor, men skriv inn input-verdi (B3) i Raden inngang celle-boksen:
   
4. Klikk OK, og du vil få følgende resultat:
   

Hvordan å lage en to-variabel-data-tabellen i Excel

En to-variabel-data-tabellen viser hvordan ulike kombinasjoner av 2 sett av variable verdiene påvirker formel resultat. Med andre ord, det viser hvordan endre to input verdier av den samme formelen endrer utgang.,

trinnene for å opprette en to-variabel-data-tabellen i Excel er i utgangspunktet den samme som i eksemplet ovenfor, bortsett fra at du skriver inn to områder av mulige input-verdier, en i en rad og en annen i en kolonne.

for Å se hvordan det fungerer, la oss bruke det samme rentes rente kalkulator og undersøke effekten av størrelsen av den opprinnelige investeringen og antall år på balansen. Å ha det gjort, sette opp dine data tabell på denne måten:

1. skriv Inn formelen i en tom celle eller en kobling som cellen til den opprinnelige formelen., Sørg for at du har nok tomme kolonner til høyre og tomme rader nedenfor for å imøtekomme dine variable verdier. Som før, vi kobler celle E2 til den opprinnelige FV formel som beregner balansen: =B8
2. Skriv inn et sett av input-verdier under formel, i samme kolonne (investering verdier i E3:E8).
3. skriv Inn det andre settet av variable verdiene til høyre for formel, i samme rad (antall år i F2:H2).,

   På dette punktet, er to variable data-tabellen skal se ut som dette:
   

4. Velg hele data tabell utvalg, inkludert formelen, rad og kolonne av variable verdier, og cellene der de beregnede verdiene vises. Vi velger det området E2:H8.,
5. Lage en data-tabellen i den allerede er kjent måte: Data kategorien > Hva-Hvis-Analyse-knappen > Data-Tabellen…
6. I innsettingscelle, Rad angi henvisning til innspill celle for variable verdier i rad (i dette eksemplet, er det B6 inneholder Årene verdi).
7. I innsettingscelle, Kolonne angi henvisning til innspill celle for variable verdier i kolonnen (B3 inneholder den Opprinnelige Investeringen verdi).
8. Klikk på OK.,n-vårt tilfelle), og analysere resultatene:

   

Data-tabellen for å sammenligne flere resultater

Hvis du ønsker å vurdere mer enn en formel på samme tid, kan du bygge din data tabell som vist i eksemplene ovenfor, og skriv inn ekstra formel(s):

• Til høyre for den første formelen i tilfelle av en vertikal data tabell organisert i kolonner
• Under den første formelen i tilfelle av en horisontal data tabell organisert i rader

For «multi-formelen» data-tabellen for å fungere korrekt, alle formler bør referere til samme inngang celle.,

Som et eksempel, la oss legge en mer formelen til våre en-variabel-data-tabellen for å beregne renter og se hvordan den er påvirket av størrelsen av den opprinnelige investeringen. Her er hva vi gjør:

1. I celle B10, beregn interesse med denne formelen: =B8-B3
2. organisere data tabell kilden data som vi gjorde tidligere: variable verdier i D3:D8 og E2 knyttet til B8 (Balanse-formelen).,
3. Legg til en mer kolonnen til data tabell utvalg (kolonne F), og link F2 for å B10 (interesse-formelen):
   
4. Velg den utvidede data tabell utvalg (D2:F8).
5. Åpne Data-Tabellen i dialogboksen ved å klikke Data kategorien > Hva-Hvis-Analyse > Data-Tabellen…
6. I innsettingscelle, Kolonne leverer inngang-celle (B3), og klikk OK.,

Nå, kan du nå se effekten av variabelen verdier på begge formlene:

Data-tabellen i Excel – 3 ting du bør vite

for Å effektivt bruke data tabeller i Excel, må du huske på disse 3 enkle fakta:

1. For et data-tabellen for å bli opprettet, input cellen(e) må være på samme ark som data-tabellen.,
2. Microsoft Excel bruker TABELLEN(row_input_cell, colum_input_cell) – funksjonen til å beregne data tabell resultater:
   • I en-variabel-data-tabellen, ett av argumentene er utelatt, avhengig av oppsettet (kolonne-orientert eller rad-orientert). For eksempel, i vår horisontal en-variabel-data-tabellen, formelen er =TABLE(, B3) hvor B3 er kolonnen inngang celle.
   • I to-variabel-data-tabellen, begge argumenter er på plass. For eksempel, =TABLE(B6, B3) hvor B6 er rad inngang celle og B3 er kolonnen inngang celle.,

   TABELLEN funksjon er lagt inn som en matrise formel. Å sørge for dette, velger du en celle med den beregnede verdien, se på formellinjen, og merk {klammeparentes} rundt formel. Imidlertid, det er ikke en vanlig array formel – du ikke kan skrive det i formellinjen og du kan heller ikke redigere en eksisterende. Det er bare «vis».

3. Fordi data tabell resultatene er beregnet med en rekke formelen, er den resulterende celler kan ikke redigeres individuelt. Du kan bare redigere eller slette hele matrisen av celler som forklart nedenfor.,

Hvordan å slette data-tabellen i Excel

Som nevnt ovenfor, Excel tillater ikke slette verdiene i individuelle celler som inneholder resultatene. Når du prøver å gjøre dette, og du mottar feilmeldingen «kan Ikke endre en del av en data-tabellen» vil dukke opp.

Men du kan enkelt fjerne hele matrisen av den resulterende verdier. Her er hvordan:

1. Avhengig av dine behov, velger du alle data tabell celler eller bare celler med resultatene.
2. Trykk på Delete-tasten.

Ferdig!, 🙂

Hvordan å redigere data i tabellen resultater

Siden det er ikke mulig å endre en del av en tabell i Excel, kan du ikke redigere individuelle celler med beregnede verdier. Du kan bare bytte ut alle disse verdiene med dine egne ved å utføre følgende fremgangsmåte:

1. Velg alle resulterende celler.
2. Slett TABELLEN formelen på formellinjen.
3. Skriv inn ønsket verdi, og trykk Ctrl + Enter.,

Dette vil sette inn den samme verdi i alle de valgte cellene:

Når BORDET formelen er borte, den tidligere data-tabellen blir en vanlig rekkevidde, og du står fritt til å redigere alle enkelt celle som normalt.

Hvordan å beregne data tabellen manuelt

Hvis en stor data-tabellen med flere variable verdier og formler bremser ned din Excel, kan du deaktivere automatisk omberegningene i det og alle andre data tabeller.,

For dette, kan du gå til kategorien Formler > Beregning-gruppen, klikker du på Beregning Alternativer-knappen, og klikk deretter Automatisk med Unntak av Data Tabeller.

Dette vil slå av automatisk data tabell utregninger og hastighet opp omberegningene av hele arbeidsboken.

for Å manuelt rekalkulere din data i tabellen, velg den resulterende celler, dvs. celler med BORD() formler, og trykk F9.

Dette er hvordan du oppretter og bruker en data-tabellen i Excel., Å ha en nærmere titt på eksemplene som diskuteres dette denne opplæringen, er du velkommen til å laste ned vårt eksempel Excel Data Tabeller arbeidsbok. Jeg takker for at du leser og ville være glad for å se deg igjen neste uke!

• Hva-Hvis-analyse med Målet Søke i Excel
• Hvordan du bruker i Excel Solver: trinn-for-trinn-med eksempler
• Hvordan å gruppere rader i Excel