Ko imate kompleksno formulo, odvisno od več spremenljivk, in želite razumeti, kako spreminjanje teh vnosov učinkovito vpliva na rezultate, je podatkovna tabela Kaj-Če v Excelu močno orodje. Omogoča vam, da s hitrim pogledom vidite vse možne rezultate. Tukaj je vodnik po korakih o tem, kako ustvariti podatkovno tabelo v Excelu. Poleg tega bomo razpravljali o nekaterih ključnih točkah za učinkovito uporabo podatkovnih tabel in prikazali druge operacije, kot so brisanje, urejanje in ponovno izračunavanje podatkovnih tabel.

Kaj je podatkovna tabela v Excelu?

Ustvarite podatkovno tabelo z eno spremenljivko

• Podatkovna tabela, usmerjena v stolpce
• Vrstično usmerjena podatkovna tabela
• Več formul za podatkovno tabelo z eno spremenljivko

Ustvari tabelo podatkov z dvema spremenljivkama

Ključne točke z uporabo podatkovnih tabel

Druge operacije za uporabo podatkovnih tabel

• Brisanje podatkovne tabele
• Uredi rezultat podatkovne tabele
• Preračunajte podatkovno tabelo ročno

Kaj je podatkovna tabela v Excelu?

V Excelu je podatkovna tabela eno od orodij za analizo kaj če, ki vam omogoča eksperimentiranje z različnimi vhodnimi vrednostmi za formule in opazovanje sprememb v izhodu formule. To orodje je izjemno uporabno za raziskovanje različnih scenarijev in izvajanje analiz občutljivosti, zlasti kadar je formula odvisna od več spremenljivk.

V Excelu obstajata dve vrsti podatkovnih tabel:

Podatkovna tabela z eno spremenljivko: To vam omogoča, da analizirate, kako bodo različne vrednosti ene spremenljivke vplivale na formulo. Podatkovno tabelo z eno spremenljivko lahko nastavite v formatu, usmerjenem v vrstico ali stolpec, odvisno od tega, ali želite svoj vnos spreminjati po vrstici ali navzdol po stolpcu.

Tabela podatkov z dvema spremenljivkama: Ta vrsta vam omogoča, da vidite vpliv spreminjanja dveh različnih spremenljivk na izid formule. V podatkovni tabeli z dvema spremenljivkama spreminjate vrednosti v vrstici in stolpcu.

Ustvarite podatkovno tabelo z eno spremenljivko

Ustvarjanje podatkovne tabele z eno spremenljivko v Excelu je dragocena veščina za analizo, kako lahko spremembe v posameznem vnosu vplivajo na različne rezultate. Ta razdelek vas bo vodil skozi postopek ustvarjanja podatkovnih tabel z eno spremenljivko, usmerjenih v stolpce, vrstice in več formul.

Neverjetno! Uporaba učinkovitih zavihkov v Excelu, kot so Chrome, Edge, Firefox in Safari!
Prihranite 50% svojega časa in vsak dan zmanjšajte na tisoče klikov z miško!

---

Podatkovna tabela, usmerjena v stolpce

Podatkovna tabela, usmerjena v stolpce, je uporabna, če želite preizkusiti, kako različne vrednosti posamezne spremenljivke vplivajo na izhod formule, pri čemer so vrednosti spremenljivk navedene v stolpcu. Poglejmo preprost finančni primer:

Recimo, da razmišljate o posojilu v višini 50,000 USD, ki ga nameravate odplačati v obdobju 3 let (kar ustreza 36 mesecem). Da bi ocenili, kakšno mesečno plačilo bi bilo dostopno glede na vašo plačo, vas zanima, kako bi različne obrestne mere vplivale na znesek, ki ga morate plačati vsak mesec.

1. korak: Nastavite osnovno formulo

Za izračun plačila bom tukaj določil 5 % obresti. V celico B4 vnesite formulo PMT, ki izračuna mesečno plačilo na podlagi obrestne mere, števila obdobij in zneska posojila. Oglejte si posnetek zaslona:

= -PMT($B$1/12, $B$2*12, $B$3)

2. korak: navedite obrestne mere v stolpcu

V stolpcu navedite različne obrestne mere, ki jih želite preizkusiti. Na primer, navedite vrednosti od 4 % do 11 % v korakih po 1 % v stolpcu in pustite vsaj en prazen stolpec na desni strani za rezultate. Oglejte si posnetek zaslona:

3. korak: Ustvarite podatkovno tabelo

1. V celico E2 vnesite to formulo: = B4.
   
   Opombe: B4 je celica, kjer se nahaja vaša glavna formula, to je formula, katere rezultati želite videti spremenjene, ko spreminjate obrestno mero.
   
2. Izberite obseg, ki vključuje vašo formulo, seznam obrestnih mer in sosednje celice za rezultate (npr. izberite D2 do E10). Oglejte si posnetek zaslona:
   
3. Pojdite na trak, kliknite na datum , nato kliknite Kaj-če analiza > Tabela podatkov, glej posnetek zaslona:
   
4. v Tabela podatkov pogovornem oknu, kliknite na Vnosna celica stolpca polje (ker za vnosne vrednosti uporabljamo stolpec) in izberite celico spremenljivke, na katero se sklicuje vaša formula. V tem primeru izberemo B1, ki vsebuje obrestno mero. Končno kliknite OK gumb, glej posnetek zaslona:
   
5. Excel bo prazne celice ob vsaki od vaših spremenljivk (različne obrestne mere) samodejno zapolnil z ustreznimi rezultati. Oglejte si posnetek zaslona:
   
6. Uporabite želeno obliko številk za rezultate (Valuta) glede na vaše potrebe. Zdaj je podatkovna tabela, usmerjena v stolpec, uspešno ustvarjena in lahko hitro pregledate rezultate, da ocenite, kateri mesečni zneski plačil bi bili za vas dostopni, ko se obrestna mera spremeni. Oglejte si posnetek zaslona:
   

Vrstično usmerjena podatkovna tabela

Ustvarjanje podatkovne tabele, usmerjene v vrstico, v Excelu vključuje razporeditev podatkov tako, da so vrednosti spremenljivk navedene v vrstici in ne v stolpcu. Če vzamemo zgornji primer kot referenco, nadaljujmo s koraki za dokončanje ustvarjanja vrstično usmerjene podatkovne tabele v Excelu.

1. korak: naštejte obrestne mere v vrstici

Postavite vrednosti spremenljivk (obrestne mere) v vrstico, pri čemer zagotovite, da je na levi strani vsaj en prazen stolpec za formulo in ena prazna vrstica spodaj za rezultate, glejte posnetek zaslona:

2. korak: Ustvarite podatkovno tabelo

1. V celico A9 vnesite to formulo: = B4.
   
2. Izberite obseg, ki vključuje vašo formulo, seznam obrestnih mer in sosednje celice za rezultate (npr. izberite A8 do I9). Nato kliknite datum > Kaj-če analiza > Tabela podatkov.
3. v Tabela podatkov pogovornem oknu, kliknite na Vrstica za vnos celice polje (ker za vnosne vrednosti uporabljamo vrstico) in izberite celico spremenljivke, na katero se sklicuje vaša formula. V tem primeru izberemo B1, ki vsebuje obrestno mero. Končno kliknite OK gumb, glej posnetek zaslona:
   
4. Uporabite želeno obliko številk za rezultate (Valuta) glede na vaše potrebe. Zdaj je podatkovna tabela, usmerjena v vrstico, ustvarjena, glejte posnetek zaslona:
   

Več formul za podatkovno tabelo z eno spremenljivko

Ustvarjanje podatkovne tabele z eno spremenljivko z več formulami v Excelu vam omogoča, da vidite, kako sprememba enega vnosa vpliva na več različnih formul hkrati. Kaj pa v zgornjem primeru, če želite videti spremembo obrestnih mer za odplačila in skupne obresti? To nastavite tako:

1. korak: dodajte novo formulo za izračun skupnih obresti

V celico B5 vnesite naslednjo formulo za izračun skupnih obresti:

=B4*B2*12-B3

2. korak: Uredite izvorne podatke podatkovne tabele

V stolpcu navedite različne obrestne mere, ki jih želite preizkusiti, in pustite vsaj dva prazna stolpca na desni strani za rezultate. Oglejte si posnetek zaslona:

3. korak: Ustvarite podatkovno tabelo:

1. V celico E2 vnesite to formulo: = B4 ustvariti sklic na izračun poplačila v izvornih podatkih.
2. V celico F2 vnesite to formulo: = B5 za ustvarjanje sklicevanja na skupno zanimanje za izvirne podatke.
   
3. Izberite obseg, ki vključuje vaše formule, seznam obrestnih mer in sosednje celice za rezultat (npr. izberite D2 do F10). Nato kliknite datum > Kaj-če analiza > Tabela podatkov.
4. v Tabela podatkov pogovornem oknu, kliknite na Vhodna celica stolpca polje (ker za vnosne vrednosti uporabljamo stolpec) in izberite celico spremenljivke, na katero se sklicuje vaša formula. V tem primeru izberemo B1, ki vsebuje obrestno mero. Končno kliknite OK gumb, glej posnetek zaslona:
   
5. Uporabite želeno obliko številk za rezultate (Valuta) glede na vaše potrebe. Ogledate si lahko rezultate za vsako formulo na podlagi različnih vrednosti spremenljivk.
   

Ustvari tabelo podatkov z dvema spremenljivkama

Podatkovna tabela z dvema spremenljivkama v Excelu prikazuje vpliv različnih kombinacij dveh nizov vrednosti spremenljivk na rezultat formule in prikazuje, kako spremembe dveh vnosov formule hkrati vplivajo na njen rezultat.

Tukaj sem narisal preprosto skico, ki vam pomaga bolje razumeti videz in strukturo podatkovne tabele z dvema spremenljivkama.

Na podlagi primera ustvarjanja podatkovne tabele z eno spremenljivko se zdaj naučimo, kako ustvariti podatkovno tabelo z dvema spremenljivkama v Excelu.

V spodnjem nizu podatkov imamo obrestno mero, trajanje posojila in znesek posojila ter izračunamo mesečno plačilo po tej formuli: =-PMT($B$1/12, $B$2*12, $B$3) prav tako. Tu se bomo osredotočili na dve ključni spremenljivki iz naših podatkov: obrestno mero in znesek posojila, da bi opazovali, kako spremembe obeh dejavnikov hkrati vplivajo na zneske odplačila.

1. korak: Nastavite dve spreminjajoči se spremenljivki

1. V stolpcu navedite različne obrestne mere, ki jih želite preizkusiti. glej posnetek zaslona:
   
2. V vrstici vnesite različne vrednosti zneska posojila tik nad vrednostmi stolpcev (začenši od ene celice na desni strani celice formule), glejte posnetek zaslona:
   

2. korak: Ustvarite podatkovno tabelo

1. Svojo formulo postavite na presečišče vrstice in stolpca, kjer ste navedli vrednosti spremenljivk. V tem primeru bom vnesel to formulo: = B4 v celico E2. Oglejte si posnetek zaslona:
   
2. Izberite obseg, ki vključuje zneske vašega posojila, obrestne mere, celico s formulo in celice, kjer bodo prikazani rezultati.
   
3. Nato kliknite datum > Kaj-če analiza > Tabela podatkov. V pogovornem oknu Podatkovna tabela:
   • v Vrstica za vnos celice izberite sklic celice na vnosno celico za vrednosti spremenljivk v vrstici (v tem primeru B3 vsebuje znesek posojila).
   • v Vnos stolpca cell polje izberite sklic celice na vnosno celico za vrednosti spremenljivke v stolpcu (B1 vsebuje obrestno mero).
   • In nato kliknite OK Button.
     
4. Zdaj bo Excel izpolnil podatkovno tabelo z rezultati za vsako kombinacijo zneska posojila in obrestne mere. Omogoča neposreden vpogled v to, kako različne kombinacije zneskov posojila in obrestnih mer vplivajo na mesečno plačilo, zaradi česar je dragoceno orodje za finančno načrtovanje in analizo.
   
5. Nazadnje morate uporabiti želeno obliko števila za rezultate (Valuta) glede na vaše potrebe.
   

Ključne točke z uporabo podatkovnih tabel

• Novo ustvarjena podatkovna tabela mora biti na istem delovnem listu kot izvirni podatki.
• Izhod podatkovne tabele je odvisen od celice s formulo v izvornem nizu podatkov in vse spremembe te celice s formulo bodo samodejno posodobile izhod.
• Ko so vrednosti izračunane s podatkovno tabelo, jih ni mogoče razveljaviti Ctrl + Z. Lahko pa ročno izberete vse vrednosti in jih izbrišete.
• Podatkovna tabela ustvari matrične formule, zato posameznih celic v tabeli ni mogoče spremeniti ali izbrisati.

Druge operacije za uporabo podatkovnih tabel

Ko ustvarite podatkovno tabelo, boste morda potrebovali dodatne operacije za učinkovito upravljanje, vključno z brisanjem podatkovne tabele, spreminjanjem njenih rezultatov in izvajanjem ročnih ponovnih izračunov.

Brisanje podatkovne tabele

Ker so rezultati podatkovne tabele izračunani z matrično formulo, ne morete izbrisati posamezne vrednosti iz podatkovne tabele. Vendar pa lahko izbrišete samo celotno podatkovno tabelo.

Izberite vse celice podatkovne tabele ali samo celice z rezultati in pritisnite Brisanje tipka na tipkovnici.

Uredi rezultat podatkovne tabele

Pravzaprav ni mogoče neposredno urejati posameznih celic v podatkovni tabeli, saj vsebujejo matrične formule, ki jih Excel samodejno ustvari.

Če želite izvesti spremembe, bi običajno morali izbrisati obstoječo podatkovno tabelo in nato ustvariti novo z želenimi spremembami. To vključuje prilagoditev vaše osnovne formule ali vhodnih vrednosti in nato ponovno nastavitev podatkovne tabele, da odraža te spremembe.

Preračunajte podatkovno tabelo ročno

Običajno Excel znova izračuna vse formule v vseh odprtih delovnih zvezkih ob vsaki spremembi. Če velika podatkovna tabela, ki vsebuje številne vrednosti spremenljivk in zapletene formule, povzroča upočasnitev vašega Excelovega delovnega zvezka.

Če želite preprečiti, da bi Excel samodejno izvajal izračune za vse podatkovne tabele vsakič, ko je v delovnem zvezku narejena sprememba, lahko preklopite način izračuna iz Samodejno v Ročno. Naredite naslednje:

Pojdi na Formule kartico in nato kliknite Možnosti izračuna > Samodejno, razen za podatkovne tabele, glej posnetek zaslona:

Ko spremenite to nastavitev, ko ponovno izračunate celoten delovni zvezek, Excel ne bo več samodejno posodabljal izračunov v vaših podatkovnih tabelah.

Če morate podatkovno tabelo posodobiti ročno, samo izberite celice, v katerih so prikazani rezultati (celice, ki vsebujejo formule TABLE(), in nato pritisnite F9 ključ.

Če sledite korakom, opisanim v tem priročniku, in upoštevate ključne točke, lahko učinkovito uporabite podatkovne tabele za svoje potrebe analize podatkov. Če vas zanima več nasvetov in trikov za Excel, naše spletno mesto ponuja na tisoče vadnic, kliknite tukaj za dostop do njih. Hvala za branje in veselimo se, da vam bomo v prihodnje zagotovili več koristnih informacij!