Izračun več rezultatov s podatkovno tabelo

V Microsoft Excelu so podatkovne tabele del zbirke ukazov, imenovanih orodja za What-If analizo. Ko sestavljate in analizirate podatkovne tabele, izvajate analizo »kaj-če«.

Analiza »kaj-če« je proces spreminjanja vrednosti v celicah, da vidite, kako bodo te spremembe vplivale na rezultat formul na delovnem listu. S podatkovno tabelo lahko na primer spremenite obrestno mero in trajanje posojila, da ocenite morebitne zneske mesečnih plačil.

Vrste analize »kaj-če«    

V Excelu so na voljo tri vrste orodij za analizo »kaj-če«: scenariji, podatkovne tabele in iskanje cilja. Scenariji in podatkovne tabele uporabljajo nabore vhodnih vrednosti za izračun možnih rezultatov. Iskanje cilja se razlikuje, uporablja en rezultat in izračuna možne vhodne vrednosti, ki bi vrnile ta rezultat.

Podobno kot pri scenarijih vam tudi podatkovne tabele pomagajo raziskati nabor možnih rezultatov. V nasprotju s scenariji so v podatkovnih tabelah vsi rezultati v eni tabeli na enem delovnem listu. S podatkovno tabelo lahko preprosto pregledate nabor možnosti že na prvi pogled. Ker se osredotočite le na eno ali dve spremenljivki, lahko berete rezultate in jih daste v skupno rabo v obliki tabele.

Podatkovna tabela ne more prostora za več kot dve spremenljivki. Če želite analizirati več kot dve spremenljivki, raje uporabite scenarije. Čeprav je omejena na le eno ali dve spremenljivki (eno za vhodno celico vrstice in eno za vhodno celico stolpca), lahko podatkovna tabela vsebuje poljubno število različnih vrednosti spremenljivk. V scenariju je lahko največ 32 različnih vrednosti, vendar lahko ustvarite poljubno število scenarijev.

Več informacij najdete v članku Uvod v What-If analize.

Ustvarite podatkovne tabele z eno spremenljivko ali dve spremenljivki, odvisno od števila spremenljivk in formul, ki jih morate preskusiti.

One-variable data tables    

Uporabite podatkovno tabelo z eno spremenljivko, če si želite ogledati, kako različne vrednosti ene spremenljivke v eni ali več formulah spremenijo rezultate teh formul. Uporabite lahko na primer podatkovno tabelo z eno spremenljivko, da si ogledate, kako različne obrestne mere vplivajo na mesečno plačilo hipoteke s funkcijo PMT. Spremenljive vrednosti vnesete v en stolpec ali vrstico, rezultati pa so prikazani v sosednjem stolpcu ali vrstici.

Na tej sliki celica D2 vsebuje formulo plačila =PMT(B3/12,B4,-B5), ki se sklicuje na vhodno celico B3.

Podatkovne tabele z dvema spremenljivkami    

Uporabite podatkovno tabelo z dvema spremenljivkami, če si želite ogledati, kako različne vrednosti dveh spremenljivk v eni formuli spremenijo rezultate te formule. Uporabite lahko na primer podatkovno tabelo z dvema spremenljivkima, da si ogledate, kako različne kombinacije obrestnih mer in pogojev posojila vplivajo na mesečno odplačilo hipoteke.

Na tej sliki celica C2 vsebuje formulo plačila =PMT(B3/12,B4,-B5), ki uporablja dve vhodni celici, B3 in B4.

 

Izračuni podatkovne tabele    

Ko delovni list znova izračuna, se preračunajo tudi morebitne podatkovne tabele , tudi če podatki niso bili posodobljeni. Če želite pospešiti izračun delovnega lista s podatkovno tabelo, lahko spremenite možnosti izračuna tako, da samodejno preračunajo delovni list, ne pa tudi podatkovnih tabel. Če želite izvedeti več, glejte razdelek Pospešite izračun na delovnem listu, ki vsebuje podatkovne tabele.

Podatkovna tabela z eno spremenljivko vsebuje vhodne vrednosti v enem stolpcu (stolpec usmerjen) ali v vrstici (v vrstici). Vsaka formula v podatkovni tabeli z eno spremenljivko se mora sklicevati le na eno vhodna celica.

Upoštevajte te korake:

1. Vnesite seznam vrednosti, ki jih želite zamenjati z vnosno celico – en stolpec navzdol ali prek ene vrstice. Pustite nekaj praznih vrstic in stolpcev na obeh strani vrednosti.

2. Naredite nekaj od tega:

   • Če je podatkovna tabela usmerjena v stolpec (vrednosti spremenljivk so v stolpcu), vnesite formulo v celico ena vrstica nad in eno celico desno od stolpca vrednosti. Ta podatkovna tabela z eno spremenljivko je usmerjena v stolpec in formula je v celici D2.
     
     
     
     Če želite pregledati učinke različnih vrednosti v drugih formulah, vnesite dodatne formule v celice desno od prve formule.

   • Če je podatkovna tabela usmerjena v vrstico (vrednosti spremenljivk so v vrstici), vnesite formulo v celico en stolpec levo od prve vrednosti in eno celico pod vrstico vrednosti.
     
     Če želite pregledati učinke različnih vrednosti v drugih formulah, vnesite dodatne formule v celice pod prvo formulo.

3. Izberite obseg celic s formulami in vrednostmi, ki jih želite zamenjati. Na zgornji sliki je ta obseg C2:D5.

4. Na zavihku Podatki kliknite Analiza »kaj-če« >podatkovno tabelo (v skupini Podatkovna orodja ali skupina Napoved Excel 2016 ). 

5. Naredite nekaj od tega:

   • Če je podatkovna tabela usmerjena v stolpec, vnesite sklic na celico vnosne celice v polje vnosne celice Stolpec . Na zgornji sliki je vhodna celica B3.

   • Če je podatkovna tabela usmerjena v vrstico, vnesite sklic na celico za vhodno celico v polje Vnosna celica vrstice .

     Opomba: Ko ustvarite podatkovno tabelo, boste morda želeli spremeniti obliko celic z rezultati. Na sliki so celice z rezultati oblikovane kot valuta.

Formule, ki se uporabljajo v podatkovni tabeli z eno spremenljivko, se morajo sklicevati na isto vhodno celico.

Upoštevajte te korake

1. Naredite nekaj od tega:

   • Če je podatkovna tabela stolpčno usmerjena, vnesite novo formulo v prazno celico desno od obstoječe formule v zgornji vrstici podatkovne tabele.

   • Če je podatkovna tabela usmerjena v vrstico, vnesite novo formulo v prazno celico pod obstoječo formulo v prvi stolpec podatkovne tabele.

2. Izberite obseg celic s podatkovno tabelo in novo formulo.

3. Na zavihku Podatki kliknite Analiza »kaj-če« > podatkovno tabelo (v skupini Podatkovna orodja ali skupina Napoved Excel 2016 ).

4. Naredite nekaj od tega:

   • Če je podatkovna tabela usmerjena v stolpec, vnesite sklic na celico za vhodno celico v polje Vnosna celica stolpca .

   • Če je podatkovna tabela usmerjena v vrstico, vnesite sklic na celico za vhodno celico v polje Vnosna celica vrstice.

Podatkovna tabela z dvema spremenljivkami uporablja formulo, ki vsebuje dva seznama vhodnih vrednosti. Formula se mora sklicevati na dve različni vhodni celici.

Upoštevajte te korake:

1. V celico na delovnem listu vnesite formulo, ki se sklicuje na dve vhodni celici.

   V tem primeru – v katerega v celice B3, B4 in B5 vnesete formulo =PMT(B3/12,B4,-B5) formule =PMT(B3/12,B4,-B5) v celico C2.

2. Pod formulo vnesite en seznam vhodnih vrednosti v istem stolpcu.

   V tem primeru vnesite različne obrestne mere v celice C3, C4 in C5.

3. Vnesite drugi seznam v isto vrstico kot formula – na desni strani.

   Vnesite pogoje posojila (v mesecih) v celici D2 in E2.

4. Izberite obseg celic s formulo (C2), vrstico in stolpec vrednosti (C3:C5 in D2:E2) ter celice, v katerih želite izračunane vrednosti (D3:E5).

   V tem primeru izberite obseg C2:E5.

5. Na zavihku Podatki v skupini Podatkovna orodja ali Napoved (v sistemu Excel 2016 ) kliknite Analiza »kaj-če« >v podatkovni tabeli (v skupini Podatkovna orodja ali skupina Napoved Excel 2016). 

6. V polje vnosne celice Vrstice vnesite sklic na vhodno celico za vhodne vrednosti v vrstici.
   V polje Vnosna celicavrstice vnesite celico B4.

7. V polje vnosne celice v stolpcu vnesite sklic na vhodno celico za vhodne vrednosti v stolpcu.
   V polje Vnosna celica stolpca vnesite B3.

8. Kliknite V redu.

Example of a two-variable data table

Podatkovna tabela z dvema spremenljivkami lahko pokaže, kako različne kombinacije obrestnih mer in pogojev posojila vplivajo na mesečno plačilo hipoteke. Na tej sliki celica C2 vsebuje formulo plačila =PMT(B3/12,B4,-B5), ki uporablja dve vhodni celici, B3 in B4.

Ko nastavite to možnost izračuna, ne pride do izračunov podatkovne tabele, ko izvedete preračunavanje v celotnem delovnem zvezku. Če želite ročno preračunati podatkovno tabelo, izberite njene formule in nato pritisnite F9.

Če želite izboljšati učinkovitost računanja, sledite tem korakom:

1. Kliknite Možnosti > možnosti > formulah.

2. V razdelku Možnosti izračuna v razdelku Izračun kliknite Samodejno , razen za podatkovne tabele.

   Namig: Če želite, lahko na zavihku Formule kliknete puščico v možnostih izračuna, nato pa kliknete Samodejno razen podatkovnih tabel (v skupini Izračun ).

Z nekaj drugimi Excelovimi orodji lahko izvedete analizo »kaj-če«, če imate določene cilje ali večje nabore spremenljivih podatkov.

Iskanje cilja

Če poznate rezultat, ki ga pričakujete od formule, vendar ne veste točno, katero vhodno vrednost potrebuje formula za ta rezultat, uporabite funkcijo Goal-Seek vnos. V članku Iskanje cilja poiščite želene rezultate tako, da prilagodite vhodno vrednost.

Excelov reševalec

Z dodatkom Excel Solver lahko poiščete optimalno vrednost za nabor vnosnih spremenljivk. Reševalec deluje s skupino celic (imenovanimi spremenljivke odločitev ali le spremenljive celice), ki se uporabljajo pri računanju formul v ciljnih in omejenih celicah. Reševalec prilagodi vrednosti v spremenljivih celicah odločitve, da doseže omejitve v omejenih celicah in proizvede želeni rezultat za ciljno celico. Več informacij najdete v tem članku: Določanje in reševanje težave z reševalnikom.