Kelių rezultatų apskaičiavimas naudojant duomenų lentelę

Programoje „Microsoft Excel“ duomenų lentelės yra komandų paketo, vadinamo sąlyginės analizės įrankiais, dalis. Kurdami ir analizuodami duomenų lenteles, atliekate sąlyginę analizę.

Sąlyginė analizė yra reikšmių keitimo langeliuose procesas, skirtas pamatyti, kaip tie pokyčiai veikia darbalapio formulių rezultatus. Pavyzdžiui, galite naudoti duomenų lentelę norėdami keisti paskolos palūkanų normą ir termino trukmę, kad įvertintumėte galimas mėnesinių mokėjimų sumas.

Sąlyginės analizės tipai    

Yra trijų tipų sąlyginės analizės įrankiai programoje „Excel“: scenarijai, duomenų lentelėsir tikslo siekimas. Scenarijai ir duomenų lentelės naudoja įvesties reikšmių rinkinius galimiems rezultatams apskaičiuoti. Tikslo siekimas labai skiriasi, ji naudoja vieną rezultatą ir apskaičiuoja galimas įvesties reikšmes, kurios gautų tą rezultatą.

Kaip ir scenarijai, duomenų lentelės padeda naršyti galimų rezultatų rinkinį. Skirtingai nei scenarijai, duomenų lentelėse rodomi visi vienos lentelės rezultatai viename darbalapyje. Naudojant duomenų lenteles lengva trumpai apžvelgti įvairias galimybes. Kadangi dėmesys skiriamas tik vienam ar dviem kintamiesiems, rezultatus paprasta suprasti ir bendrinti lentelės forma.

Duomenų lentelėje negali būti daugiau negu du kintamieji. Jei norite analizuoti daugiau nei du kintamuosius, geriau naudokite scenarijus. Nors ribojama iki vieno ar dviejų kintamųjų (vieno eilutės įvesties langelio ir vieno stulpelio įvesties langelio), duomenų lentelėje gali būti tiek skirtingų kintamųjų reikšmių, kiek norite. Scenarijus gali apimti ne daugiau nei 32 skirtingas reikšmes, tačiau galima sukurti tiek scenarijų, kiek norite.

Sužinokite daugiau straipsnyje Įvadas į sąlyginę analizę.

Galite kurti vieno kintamojo arba dviejų kintamųjų duomenų lenteles, atsižvelgdami į kintamųjų ir formulių, kuriuos norite išbandyti, skaičių.

Vieno kintamojo duomenų lentelės    

Naudokite vieno kintamojo duomenų lentelę, jei norite pamatyti, kaip skirtingos vieno kintamojo reikšmės vienoje arba daugiau formulių pakeis tų formulių rezultatus. Pavyzdžiui, galite naudoti vieno kintamojo duomenų lentelę, norėdami pamatyti, kokią įtaką skirtingos palūkanų normos daro mėnesio įmokai naudodami funkciją PMT. Kintamųjų reikšmes įvedate į vieną stulpelį arba eilutę, o rezultatai rodomi šalia esančiame stulpelyje arba eilutėje.

Tolesnėje iliustracijoje langelyje D2 yra mokėjimo formulė =PMT(B3/12,B4,-B5), kuri nurodo įvesties langelį B3.

Dviejų kintamųjų duomenų lentelės    

Dviejų kintamųjų duomenų lentelę naudokite norėdami pamatyti, kaip skirtingos dviejų kintamųjų reikšmės vienoje formulėje pakeis tos formulės rezultatus. Pavyzdžiui, dviejų kintamųjų duomenų lentelę galite naudoti norėdami pamatyti, kaip skirtingi palūkanų normų ir paskolos terminų deriniai paveiks mėnesinę įmoką.

Tolesnėje iliustracijoje langelyje C2 yra mokėjimo formulė =PMT(B3/12,B4,-B5), kuri naudoja du įvesties langelius B3 ir B4.

 

Duomenų lentelės skaičiavimai    

Kiekvieną kartą, kai darbalapis perskaičiuoja, visos duomenų lentelės taip pat bus perskaičiuotos, net jei duomenys nebuvo pakeisti. Norėdami pagreitinti darbalapio, kuriame yra duomenų lentelė, skaičiavimą, galite pakeisti Skaičiavimo parinktis taip, kad darbalapis būtų perskaičiuojamas automatiškai, bet duomenų lentelės – ne. Norėdami sužinoti daugiau, žr. skyrių Skaičiavimo spartinimas darbalapyje, kuriame yra duomenų lentelių.

Vieno kintamojo duomenų lentelėje yra įvesties reikšmių, kurios išvardytos žemyn stulpeliu (išdėstyta stulpeliu) arba skersai eilute (išdėstyta eilute). Formulės, kurios naudojamos vieno kintamojo duomenų lentelėje, turi nurodyti tik vieną įvesties langelis.

Atlikite šiuos veiksmus:

1. Sudarykite įvesties langelio reikšmių, kurias norite pakeisti, sąrašą, išdėstytą žemyn stulpeliu arba skersai eilute. Kiekvienoje reikšmių pusėje palikite keletą tuščių eilučių ir stulpelių.

2. Atlikite vieną iš šių veiksmų:

   • Jei duomenų lentelė yra išdėstyta stulpeliais (kintamųjų reikšmės išdėstytos stulpelyje), įveskite formulę langelyje, esančiame viena eilute aukščiau ir per vieną langelį į dešinę nuo reikšmių stulpelio. Ši vieno kintamojo duomenų lentelė išdėstyta stulpeliu, o formulė yra langelyje D2.
     
     
     
     Jei norite patikrinti įvairių reikšmių poveikį kitose formulėse, įveskite papildomas formules į langelius į dešinę nuo pirmosios formulės.

   • Jei duomenų lentelė yra išdėstyta eilute (kintamųjų reikšmės išdėstytos eilute), įveskite formulę langelyje, esančiame per vieną stulpelį į kairę nuo pirmosios reikšmės ir per vieną langelį žemyn nuo reikšmių eilutės.
     
     Jei norite patikrinti įvairių reikšmių poveikį kitose formulėse, įveskite papildomas formules į langelius žemiau pirmosios formulės.

3. Pasirinkite langelių diapazoną, kuriame yra formulės ir reikšmės, kurias norite pakeisti. Aukščiau pateiktame paveikslėlyje šis diapazonas yra C2:D5.

4. Skirtuke Duomenys pasirinkite Sąlyginė analizė > Duomenų lentelė (grupėje Duomenų įrankiai arba Prognozė grupėje Excel 2016).

5. Atlikite vieną iš šių veiksmų:

   • Jei duomenų lentelė išdėstyta stulpeliu, įveskite langelio nuoroda įvesties langelyje Stulpelio įvesties langelis lauką. Aukščiau pateiktame paveikslėlyje įvesties langelis yra B3.

   • Jei duomenų lentelė išdėstyta eilute, įvesties langelio nuorodą įveskite lauke Eilutės įvesties langelis.

     Pastaba: Sukūrę duomenų lentelę, galite pakeisti rezultatų langelių formatą. Paveikslėlyje rezultatų langeliai suformatuoti kaip valiuta.

Formulės, kurios naudojamos vieno kintamojo duomenų lentelėje, turi nurodyti tą patį įvesties langelį.

Atlikite šiuos veiksmus:

1. Atlikite vieną iš šių veiksmų:

   • Jei duomenų lentelė išdėstyta stulpeliu, įveskite naują formulę viršutinėje duomenų lentelės eilutėje, į dešinę nuo dabartinės formulės esančiame tuščiame langelyje.

   • Jei duomenų lentelė išdėstyta eilute, įveskite naują formulę į tuščią langelį po esama formule pirmame duomenų lentelės stulpelyje.

2. Pažymėkite langelių diapazoną, kuriame yra duomenų lentelė ir nauja formulė.

3. Skirtuke Duomenys pasirinkite Sąlyginė analizė > Duomenų lentelė (grupėje Duomenų įrankiai arba Prognozė grupėje Excel 2016).

4. Atlikite vieną iš šių veiksmų:

   • Jei duomenų lentelė išdėstyta stulpeliu, įvesties langelio nuorodą įveskite lauke Stulpelio įvesties langelis.

   • Jei duomenų lentelė išdėstyta eilute, įvesties langelio nuorodą įveskite lauke Eilutės įvesties langelis.

Dviejų kintamųjų duomenų lentelėje naudojama formulė, kurioje yra du įvesties reikšmių sąrašai. Formulė turi nurodyti du skirtingus įvesties langelius.

Atlikite šiuos veiksmus:

1. Darbalapio langelyje įveskite formulę, kuri nurodo du įvesties langelius.

   Toliau pateiktame pavyzdyje– kai formulės pradinės reikšmės įvedamos langeliuose B3, B4 ir B5, langelyje C2 įvedate formulę =PMT(B3/12,B4,-B5).

2. Vieną įvesties reikšmių sąrašą įveskite tame pačiame stulpelyje po formule.

   Tokiu atveju langeliuose C3, C4 ir C5 įveskite skirtingas palūkanų normas.

3. Antrą įvesties sąrašą įveskite toje pačioje eilutėje, į dešinę nuo formulės.

   Įveskite paskolos sąlygas (mėnesiais) langeliuose D2 ir E2.

4. Pasirinkite langelių diapazoną, kuriame yra formulė (C2), reikšmių eilutė ir stulpelis (C3:C5 ir D2:E2), ir langelius, kuriuose norite apskaičiuoti reikšmes (D3:E5).

   Tokiu atveju pažymėkite diapazoną C2:E5.

5. Skirtuke Duomenys, grupėje Duomenų įrankiaiarba grupėje Prognozė (Excel 2016) pasirinkite Sąlyginė analizė > Duomenų lentelė (grupėje Duomenų įrankiai arba grupėje Prognozė Excel 2016).

6. Lauke Eilutės įvesties langelis įveskite nuorodą į įvesties langelį, skirtą eilutės įvesties reikšmėms.
   Įveskite langelį B4 į lauką Eilutės įvesties langelis.

7. Lauke Stulpelio įvesties langelis įveskite nuorodą į įvesties langelį, skirtą stulpelio įvesties reikšmėms.
   Įveskite B3 į lauką Stulpelio įvesties langelis.

8. Pasirinkite Gerai.

Dviejų kintamųjų duomenų lentelės pavyzdys

Pavyzdžiui, dviejų kintamųjų duomenų lentelę galite naudoti norėdami pamatyti, kaip skirtingi palūkanų normų ir paskolos terminų deriniai paveiks mėnesinę įmoką. Čia pateiktame paveikslėlyje langelyje C2 yra mokėjimo formulė =PMT(B3/12,B4,-B5), kuri naudoja du įvesties langelius B3 ir B4.

Kai nustatote šią skaičiavimo parinktį, perskaičiavus visoje darbaknygėje, duomenų lentelės skaičiavimai neįvyksta. Jei norite duomenų lenteles perskaičiuoti neautomatiniu būdu, pasirinkite jų formules ir paspauskite klavišą F9.

Norėdami pagerinti skaičiavimo našumą, atlikite šiuos veiksmus:

1. Pasirinkite Failas > Parinktys > Formulės.

2. Dalyje Skaičiavimo parinktys pasirinkite Automatinis.

   Patarimas: Jei norite, skirtuke Formulės pasirinkite rodyklę dalyje Skaičiavimo parinktys, tada pasirinkite Automatinis.

Jei turite konkrečius tikslus arba didelius kintamųjų rinkinius, norėdami atlikti sąlyginę analizę, galite naudoti dar kelis kitus „Excel“ įrankius.

Tikslingoji paieška

Jei žinote rezultatą, kurio tikitės iš formulės, bet tiksliai nežinote, kokia įvesties reikšme formulė turi gauti šį rezultatą, naudokite tikslo siekimo funkciją. Žr. straipsnį Tikslo siekimo naudojimas norint rasti norimą rezultatą koreguojant įvesties reikšmę.

„Excel“ sprendimo paieška

Galite naudoti „Excel“ sprendimo paieškos papildinį, kad rastumėte optimalią įvesties kintamųjų rinkinio reikšmę. Sprendimo paieška veikia grupėje langelių (vadinamų sprendimo kintamaisiais arba tiesiog kintamųjų langeliais), kurie naudojami skaičiuojant formules tikslo ir apribojimo langeliuose. Sprendimo paieška koreguoja sprendimo kintamojo langelių reikšmes, kad būtų atitikti apribojimo langelių apribojimai ir pateikia pageidaujamą rezultatą tikslo langelyje. Sužinokite daugiau šiame straipsnyje: Problemos apibrėžimas ir sprendimas naudojant sprendimo paiešką.