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

Programoje "Microsoft Excel" duomenų lentelės yra komandų paketo, vadinamo What-If analizės įrankiais, dalis. Kurdami ir analizuodami duomenų lenteles, atliekate analizę kas, jei.

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. 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.

Kas, jei, analizės tipai    

Yra trys kas, jei analizės įrankių tipai programoje "Excel": scenarijai, duomenų lentelės ir tikslo siekimas. Scenarijai ir duomenų lentelės naudoja įvesties reikšmių rinkinius galimiems rezultatams apskaičiuoti. Tikslingoji paieška labai skiriasi, ji naudoja vieną rezultatą ir apskaičiuoja galimas įvesties reikšmes, kurios pateiktų tą rezultatą.

Kaip ir scenarijai, duomenų lentelės padeda susipažinti su galimų rezultatų rinkiniu. Skirtingai nei scenarijai, duomenų lentelėse rodomi visi vienos lentelės rezultatai viename darbalapyje. Naudojant duomenų lenteles lengva vienu žvilgtelėjimu išnagrinėti į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 nei du kintamieji. Jei norite analizuoti daugiau nei du kintamuosius, geriau naudokite scenarijus. Nors jis ribojamas 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. Scenarijuje gali būti ne daugiau kaip 32 skirtingos reikšmės, bet galite sukurti tiek scenarijų, kiek norite.

Sužinokite daugiau straipsnyje "What-If analizės įvadas.

Kurkite vieno kintamojo arba dviejų kintamųjų duomenų lenteles, atsižvelgdami į kintamųjų ir formulių skaičių, kurį turite patikrinti.

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 viename stulpelyje arba eilutėje, o rezultatai rodomi gretimame 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 paspartinti darbalapio, kuriame yra duomenų lentelė, skaičiavimą, galite pakeisti skaičiavimo parinktis, kad darbalapis būtų automatiškai perskaičiuotas, bet ne duomenų lentelės. Norėdami sužinoti daugiau, žr. skyrių Skaičiavimo spartinimas darbalapyje, kuriame yra duomenų lentelių.

Vieno kintamojo duomenų lentelėje yra įvesties reikšmės viename stulpelyje (išdėstytame stulpelyje) arba skersai eilutei (išdėstyta eilute). Bet kuri vieno kintamojo duomenų lentelės formulė turi nurodyti tik vieną įvesties langelis.

Atlikite šiuos veiksmus:

1. Įvesties langelyje įveskite reikšmių, kurias norite pakeisti, sąrašą žemyn vienu 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ė išdėstyta stulpeliu (kintamųjų reikšmės yra 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, į langelius, esančius į dešinę nuo pirmosios formulės, įveskite papildomas formules.

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

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 spustelėkite Kas, jei analizė > Duomenų lentelė (grupėje Duomenų įrankiai arba Excel 2016 grupėje Prognozė ).

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

   • Jei duomenų lentelė yra stulpelinė, įveskite įvesties langelio langelio nuoroda lauke Stulpelio įvesties langelis . Aukščiau pateiktame paveikslėlyje įvesties langelis yra B3.

   • Jei duomenų lentelė išdėstyta eilute, įveskite įvesties langelio nuorodą 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ę į tuščią langelį į dešinę nuo esamos formulės viršutinėje duomenų lentelės eilutėje.

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

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

3. Skirtuke Duomenys spustelėkite Kas, jei analizė > duomenų lentelę (grupėje Duomenų įrankiai arba Excel 2016 grupėje Prognozė ).

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

   • Jei duomenų lentelė yra stulpelinė, įveskite įvesties langelio nuorodą lauke Stulpelio įvesties langelis .

   • Jei duomenų lentelė išdėstyta eilute, įveskite įvesties langelio nuorodą 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. Įveskite antrąjį sąrašą į tą pačią eilutę kaip ir formulė –į dešinę.

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

4. Pažymėkite langelių diapazoną, kuriame yra formulė (C2), reikšmių eilutė ir stulpelis (C3:C5 ir D2:E2) ir langeliai, kuriuose norite gauti apskaičiuotas reikšmes (D3:E5).

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

5. Skirtuko Duomenys grupėje Duomenų įrankiai arba grupėje Prognozė ( Excel 2016 ) spustelėkite Kas, jei analizė > duomenų lentelę (grupėje Duomenų įrankiai arba Excel 2016 grupėje Prognozė ).

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

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

8. Spustelėkite Gerai.

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

Dviejų kintamųjų duomenų lentelėje gali būti rodoma, kaip skirtingi palūkanų normų ir paskolos sąlygų deriniai paveiks mėnesinę paskolos į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. Spustelėkite Failo > parinktys > formulės.

2. Skyriaus Skaičiavimo parinktys dalyje Skaičiuoti spustelėkite Automatiškai, išskyrus duomenų lenteles.

   Patarimas: Jei norite, skirtuke Formulės spustelėkite rodyklę dalyje Skaičiavimo parinktys, tada spustelėkite Automatiškai, išskyrus duomenų lenteles (grupėje Skaičiavimas ).

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, ko tikėtis iš formulės, bet tiksliai nežinote, kokia įvesties reikšmė formulė turi gauti tą rezultatą, naudokite funkciją Goal-Seek. Žr. straipsnį Tikslingosios paieškos 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 su langelių grupe (vadinama 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ą.