Izračun več rezultatov s podatkovno tabelo

Podatkovna tabela je obseg celic, v katerem lahko spremenite vrednosti v nekaterih celicah in izkoristite različne odgovore na težavo. Dober primer podatkovne tabele uporablja funkcijo PMT z različnimi zneski posojila in obrestnimi merami za izračun cenovno ugoden znesek na posojilo za domačo hipoteko. Eksperimentiranje z različnimi vrednostmi za opazovanje ustrezne variacije rezultatov je navadno opravilo v analizi podatkov.

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

Analiza» kaj-če «je postopek spreminjanja vrednosti v celicah, da si ogledate, kako bodo te spremembe vplivale na rezultate formul na delovnem listu. S podatkovno tabelo lahko na primer spreminjate obrestno mero in dolžino izraza za posojilo, da ocenite morebitne mesečne zneske plačil.

Opomba: Izvedete lahko hitrejše izračune s podatkovnimi tabelami in Visual Basic for Applications (VBA). Če želite več informacij, glejte Excelove podatkovne tabele» kaj-če «: hitrejši izračun z VBA.

Vrste analize» kaj-če «    

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

Na primer scenariji, podatkovne tabele omogočajo raziskovanje nabora možnih rezultatov. V nasprotju s temi scenariji so v podatkovnih tabelah prikazani vsi rezultati v eni tabeli na enem delovnem listu. S podatkovnimi tabelami lahko hitro pregledate obseg možnosti. Ker se osredotočate le na eno ali dve spremenljivki, so rezultati lahko berljivi in so v skupni rabi v obliki tabele.

Podatkovna tabela ne more sprejeti več kot dveh spremenljivk. Če želite analizirati več kot dve spremenljivki, raje uporabite scenarije. Čeprav je omejena le na eno ali dve spremenljivki (eno za vhodno celico vrstice in eno za vhodno celico stolpca), lahko podatkovna tabela vključuje toliko različnih vrednosti spremenljivke, kot jih želite. V scenariju je lahko največ 32 različnih vrednosti, lahko pa ustvarite želene več scenarijev.

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

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

Podatkovne tabele z eno spremenljivko    

Uporabite podatkovno tabelo z eno spremenljivko, če želite videti, kako bodo različne vrednosti ene spremenljivke v eni ali več formulah spremenile rezultate teh formul. Podatkovno tabelo z eno spremenljivko lahko na primer uporabite, če želite videti, kako različne obrestne mere vplivajo na mesečno plačilo hipoteke z uporabo funkcije PMT. Vrednosti spremenljivke vnesete v en stolpec ali vrstico, rezultati pa so prikazani v priležnem stolpcu ali vrstici.

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

Podatkovna tabela z eno spremenljivko

Podatkovne tabele z dvema spremenljivkama    

Uporabite podatkovno tabelo z dvema spremenljivkama, da si ogledate, kako bodo različne vrednosti dveh spremenljivk v eni formuli spremenile rezultate te formule. Podatkovno tabelo z dvema spremenljivkama lahko na primer uporabite, če želite videti, kako različne kombinacije obrestnih mer in posojilnih pogojev 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.

Data table with two variables
 

Izračuni podatkovne tabele    

Vsakič, ko se delovni list znova izračuna, bodo vse podatkovne tabele tudi znova izračunane – tudi če ni prišlo do sprememb podatkov. Če želite pospešiti izračun delovnega lista, v katerem je podatkovna tabela, lahko spremenite možnosti izračuna za samodejno vnovično izračunavanje delovnega lista, ne pa tudi podatkovnih tabel. Če želite izvedeti več, si oglejte razdelek pospešitev izračuna na delovnem listu, v katerem so podatkovne tabele.

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

Upoštevajte te korake:

  1. V vhodno celico vnesite seznam vrednosti, ki jih želite nadomestiti, in sicer v enem stolpcu ali v eni vrstici. Pustite nekaj praznih vrstic in stolpcev na kateri koli strani 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 in eno celico desno od stolpca vrednosti. Ta podatkovna tabela z eno spremenljivko je stolpčno usmerjena, formula pa je vsebovana v celici D2.

      Podatkovna tabela z eno spremenljivko

      Če želite preučiti učinke različnih vrednosti v drugih formulah, 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 vrednosti.

      Če želite preučiti učinke različnih vrednosti v drugih formulah, vnesite dodatne formule v celicah 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 kliknite Analiza» kaj-če «>podatkovno tabelo (v skupini podatkovna orodja ali skupina» napoved « 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 celic rezultatov. Na sliki so celice rezultatov 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 eno od teh:

    • Č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 kliknite Analiza» kaj-če «> podatkovno tabelo (v skupini podatkovna orodja ali skupina» napoved « 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 naslednjem primeru – v katerem so vnosne vrednosti formule vnesene v celice B3, B4 in B5, vnesete formulo = PMT (B3/12, B4,-B5) v celici C2.

  2. Vnesite en seznam vhodnih vrednosti v isti stolpec pod formulo.

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

  3. Vnesite drugi seznam v isto vrstico kot formulo – na desno.

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

  4. Izberite obseg celic, ki vsebuje formulo (C2), obe vrstici in stolpcu vrednosti (C3: C5 in D2: E2) in celice, v katerih želite izračunati vrednosti (D3: E5).

    V tem primeru izberite obseg C2: E5.

  5. Na zavihku podatki v skupini podatkovna orodja ali v skupini napoved (v Excel 2016 ) kliknite Analiza» kaj-če «>podatkovno tabelo (v skupini podatkovna orodja ali v skupini napoved Excel 2016 ). 

  6. V polje Vnosna celica vrstice vnesite sklic na vhodno celico za vhodne vrednosti v vrstico.
    Vnesite celico B4 v polje Vhodna celica vrstice .

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

  8. Kliknite V redu.

Primer podatkovne tabele z dvema spremenljivkama

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

Data table with two variables

Ko nastavite to možnost izračuna, se ne izračuni podatkovne tabele pojavijo, ko se v celotnem delovnem zvezku izvede Vnovični izračun. Če želite ročno preračunati podatkovno tabelo, izberite njegove formule in nato pritisnite F9.

Če želite izboljšati učinkovitost izračuna, upoštevajte ta navodila:

  1. Naredite nekaj od tega:

    • V Excel 2007 kliknite gumb Microsoft Office Slika gumba »Office« , kliknite Excelove možnostiin nato kliknite kategorijo formule .

    • V vseh drugih različicah kliknite možnosti > datoteke > formul.

  2. V razdelku Možnosti izračuna v razdelku Izračunajkliknite samodejno, razen za podatkovne tabele.

    Namig: Če želite, na zavihku formule kliknite puščico na Možnosti izračunain nato kliknite samodejno, razen podatkovnih tabel (v skupini izračun ).

Če imate določene cilje ali večje nabore podatkov spremenljivk, lahko uporabite nekaj drugih Excelovih orodij za izvajanje analize» kaj-če «.

Iskanje cilja

Če poznate rezultat formule, vendar ne veste natančno, katero vhodno vrednost mora formula pridobiti, uporabite funkcijo za iskanje cilja. Če želite poiskati želeni rezultat, si oglejte članek uporaba cilja iskanje s prilagoditvijo vhodne vrednosti.

Reševalec iz programa Excel

Z dodatkom za Excel reševalec lahko poiščete optimalno vrednost za nabor vhodnih spremenljivk. Reševalec deluje s skupino celic (imenovano» spremenljivke «ali» preprosto spremenljive celice «), ki se uporabljajo za izračunavanje formul v ciljnih celicah in omejitvah. 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 v tem članku: določite in razrešite težavo z uporabo reševalca.

S priklopom različnih številk v celico lahko hitro dokončate različne odgovore na težavo. Odličen primer je uporaba funkcije PMT z različnimi obrestnimi merami in obdobji posojila (v mesecih), da ugotovite, koliko posojila si lahko privoščite za dom ali avto. Številke vnesete v obseg celic, imenovanih podatkovna tabela.

Tukaj je podatkovna tabela obseg celic B2: D8. Spremenite lahko vrednost v B4, znesek posojila in mesečna plačila v stolpcu D se samodejno posodobijo. Če uporabljate 3,75% obrestne mere, vrne D2 mesečno plačilo v višini $1.042,01 s to formulo: = PMT (C2/12, $B $3, $B $4).

Ta obseg celic, B2:D8, je podatkovna tabela

Uporabite lahko eno ali dve spremenljivki, odvisno od števila spremenljivk in formul, ki jih želite preskusiti.

Uporabite preskus z eno spremenljivko, da si ogledate, kako bodo različne vrednosti ene spremenljivke v formuli spremenile rezultate. Obrestno mero za mesečno plačilo hipoteke lahko na primer spremenite tako, da uporabite funkcijo PMT. Vrednosti spremenljivk (obrestne mere) vnesete v en stolpec ali vrstico, rezultati pa so prikazani v bližnjem stolpcu ali vrstici.

V tem delovnem zvezku v živo vsebuje celica D2 formulo plačila =PMT (C2/12, $B $3, $B $4). Celica B3 je celica s spremenljivkami , kjer lahko priključite drugo dolžino obdobja (število mesečnih plačilnih obdobij). V celici D2 so vtičniki funkcije PMT v obrestni meri 3.75%/, 360 mesecev in $225.000 posojila ter izračuna $1.042,01 mesečno plačilo.

Če si želite ogledati, kako bodo različne vrednosti dveh spremenljivk v formuli spremenile rezultate, uporabite preskus z dvema spremenljivkama. Preskusite lahko na primer različne kombinacije obrestnih mer in število mesečnih plačilnih obdobij za izračun plačila hipoteke.

V tem delovnem zvezku v živo celica C3 vsebuje formulo plačila, =PMT ($B $3/12, $B $2, B4), ki uporablja dve spremenljivki celic, B2 in B3. V celici C2 je funkcija PMT Plugs v obrestni meri 3.875%/, 360 mesecev in $225.000 posojila ter izračuna $1.058,03 mesečno plačilo.

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel, pridobite podporo skupnosti Answers ali predlagate novo funkcijo oziroma izboljšavo na spletnem mestu Excel User Voice.

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×