Izračun več rezultatov s podatkovno tabelo

V Microsoft Excelu so podatkovne tabele del zbirke ukazov, znanih kot orodja za analizo »kaj-če«. Ko ustvarjate in analizirate podatkovne tabele, izvajate analizo »kaj-če«.

Analiza »kaj-če« je postopek spreminjanja vrednosti v celicah, da vidite, kako te spremembe vplivajo na rezultat formul na delovnem listu. S podatkovno tabelo lahko na primer spremenite obrestno mero in trajanje posojila, da ovrednotite 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 je povsem drugačno – uporablja en sam rezultat in izračuna možne vhodne vrednosti, ki bi privedle do tega rezultata.

Podobno kot s scenariji lahko tudi s podatkovnimi tabelami lažje raziščete nabor možnih rezultatov. V nasprotju s scenariji so v podatkovnih tabelah prikazani vsi rezultati v eni tabeli na enem delovnem listu. S podatkovnimi tabelami lahko že na prvi pogled preprosto pregledate nabor možnosti. Ker se osredotočate le na eno ali dve spremenljivki, lahko rezultate preprosto preberete in jih date v skupno rabo v tabelarični obliki.

V podatkovno tabelo ne morete vključiti več kot dveh spremenljivk. Če želite analizirati več kot dve spremenljivki, uporabite scenarije. Čeprav je podatkovna tabela omejena na eno ali dve spremenljivki (eno za vhodno celico vrstice in eno za vhodno celico stolpca), lahko vsebuje poljubno število različnih vrednosti spremenljivk. V scenariju lahko uporabite največ 32 različnih vrednosti, vendar lahko ustvarite različno število scenarijev.

Več informacij najdete v članku Uvod v analizo »kaj-če«.

Ustvarite lahko podatkovne tabele z eno spremenljivko ali dvema spremenljivkama, odvisno od števila spremenljivk in formul, ki jih želite preskusiti.

Podatkovne tabele z eno spremenljivko    

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. S podatkovno tabelo z eno spremenljivko lahko na primer z uporabo funkcije PMT preverite, kako različne obrestne mere vplivajo na mesečno plačilo hipoteke. Vrednosti spremenljivk vnesete v en stolpec ali eno vrstico, rezultati pa so prikazani v sosednjem stolpcu ali sosednji vrstici.

Na spodnji sliki celica D2 vsebuje formulo za plačilo, =PMT(B3/12,B4,-B5), ki se nanaša na vhodno celico B3.

Podatkovne tabele z dvema spremenljivkama    

S podatkovno tabelo z dvema spremenljivkama si lahko ogledate, kako različne vrednosti dveh spremenljivk v eni formuli spremenijo rezultate te formule. S podatkovno tabelo z dvema spremenljivkama si lahko na primer ogledate, kako različne kombinacije obrestnih mer in pogojev posojila vplivajo na mesečno plačilo hipoteke.

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

 

Izračuni podatkovne tabele    

Ko je izveden ponovni izračun delovnega lista, so ponovni izračuni izvedeni tudi za podatkovne tabele, tudi če podatki niso bili spremenjeni. Če želite pospešiti izračun delovnega lista s podatkovno tabelo, lahko spremenite možnosti izračuna tako, da je samodejni ponovni izračune izveden za delovni list, ne pa tudi za podatkovne tabele. Več informacij najdete v razdelku Pospešite izračun na delovnem listu, ki vsebuje podatkovne tabele.

Podatkovna tabela z eno spremenljivko vsebuje vhodne vrednosti v enem stolpcu (stolpčno usmerjena) ali v vrstici (vrstično usmerjena). Morebitna 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 nadomestiti v vhodni celici – bodisi navzdol v enem stolpcu ali v eni vrstici. Pustite nekaj praznih vrstic in stolpcev na obeh straneh vrednosti.

2. Naredite nekaj od tega:

   • Če je podatkovna tabela stolpčno usmerjena (vrednosti spremenljivk so v stolpcu), vnesite formulo v celico eno vrstico nad stolpcem z vrednostmi in eno celico desno od stolpca z vrednostmi. Ta podatkovna tabela z eno spremenljivko je stolpčno usmerjena, formula pa je v celici D2.
     
     
     
     Če želite pregledati, kako različne vrednosti vplivajo na druge formule, vnesite dodatne formule v celice desno od prve formule.

   • Če je podatkovna tabela vrstično usmerjena (vrednosti spremenljivk so v vrstici), vnesite formulo v celico en stolpec levo od prve vrednosti in eno celico pod vrstico z vrednostmi.
     
     Če želite pregledati, kako različne vrednosti vplivajo na druge formule, vnesite dodatne formule v celice pod prvo formulo.

3. Izberite obseg celic, ki vsebuje formule in vrednosti, ki jih želite nadomestiti. Na zgornji sliki je ta obseg C2:D5.

4. Na zavihku Podatki izberite Analiza »kaj-če« > Podatkovna tabela (v skupini Podatkovna orodja ali skupini Napoved v razdelku Excel 2016).

5. Naredite nekaj od tega:

   • Če je podatkovna tabela stolpčno usmerjena, vnesite sklic na celico za vhodno celico v polje Vhodna celica stolpca. Na zgornji sliki je vhodna celica B3.

   • Če je podatkovna tabela vrstično usmerjena, vnesite sklic na celico za vhodno celico v polje Vhodna celica vrstice.

     Opomba: Ko ustvarite podatkovno tabelo, boste morda želeli spremeniti obliko zapisa celic z rezultati. Na sliki imajo celice z rezultati obliko zapisa valute.

Formule, uporabljene 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 vrstično usmerjena, vnesite novo formulo v prazno celico pod obstoječo formulo v prvem stolpcu podatkovne tabele.

2. Izberite obseg celic, ki vsebuje podatkovno tabelo in novo formulo.

3. Na zavihku Podatki izberite Analiza »kaj-če« > Podatkovna tabela (v skupini Podatkovna orodja ali skupini Napoved v razdelku Excel 2016).

4. Naredite nekaj od tega:

   • Če je podatkovna tabela stolpčno usmerjena, vnesite sklic na celico za vhodno celico v polje Vhodna celica stolpca.

   • Če je podatkovna tabela vrstično usmerjena, vnesite sklic na celico za vhodno celico v polje Vhodna celica vrstice.

Podatkovna tabela z dvema spremenljivkama 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 – kjer so začetne vrednosti formule vnesene v celice B3, B4 in B5 – vnesete formulo =PMT(B3/12,B4,-B5) v celico C2.

2. V isti stolpec, pod formulo, vnesite en seznam vhodnih vrednosti.

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

3. Drugi seznam vnesite v isto vrstico kot formulo – na desni strani.

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

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

   V tem primeru izberite obseg C2:E5.

5. Na zavihku Podatki v skupini Podatkovna orodja ali skupini Napoved (v razdelku Excel 2016) izberite Analiza »kaj-če« > Podatkovna tabela (v skupini Podatkovna orodja ali skupini Forecast v razdelku Excel 2016).

6. V polje Vhodne celice vrstice vnesite sklic na vhodno celico za vhodne vrednosti v vrstici.
   Vnesite celica B4 v polje Vhodna celica vrstice.

7. V polje Vhodne celice stolpca vnesite sklic na vhodno celico za vhodne vrednosti v stolpcu.
   Vnesite B3 v polje Vhodna celica stolpca.

8. Izberite V redu.

Primer podatkovne tabele z dvema spremenljivkama

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

Ko nastavite to možnost izračuna, pri postopku ponovnega izračuna celotnega delovnega zvezka niso izvedeni izračuni podatkovnih tabel. Če želite izvesti ročni ponovni izračun podatkovne tabele, izberite njene formule in nato pritisnite F9.

Za izboljšanje učinkovitosti izračuna upoštevajte te korake:

1. Izberite Datoteka > Možnosti > Formule.

2. V razdelku Možnosti izračuna izberite Samodejno.

   Namig: Če želite, lahko na zavihku Formule izberete puščico v razdelku Možnosti izračuna, nato pa izberete Samodejno.

Z nekaterimi 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 želite pridobiti s formulo, vendar ne veste natančno, katero vhodno vrednost morate vnesti v formulo za pridobitev želenega rezultata, uporabite funkcijo iskanja cilja. Oglejte si članek Iskanje želenega rezultata z iskanjem cilja (s spreminjanjem vhodne vrednosti).

Excelov reševalnik

Z dodatkom Excelov reševalnik lahko poiščete optimalno vrednost za nabor vhodnih spremenljivk. Reševalnik uporablja skupino celic, imenovano spremenljivke odločitev ali preprosto spremenljive celice, ki se uporabljajo pri izračunavanju 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.