Duomenų lentelė yra langelių diapazonas, kuriame galite keisti kai kurių langelių reikšmes ir pateikti skirtingus problemos atsakymus. Geras duomenų lentelės pavyzdys naudoja PMT funkciją su skirtingomis paskolų sumomis ir palūkanų kainomis, kad apskaičiuotų įperkamą būsto paskolos sumą. Eksperimentavimas su skirtingomis reikšmėmis norint stebėti atitinkamą rezultatų variaciją yra įprasta duomenų analizės užduotis.

"Microsoft Excel" duomenų lentelės yra komandų, vadinamų "What-If įrankiais, paketo dalis. Kurdami ir analizuokite duomenų lenteles, atliekate "what-if" analizę.

If analizė yra langelių reikšmių keitimo procesas, kad pamatytumėte, kaip šie pakeitimai paveiks darbalapio formulių rezultatus. Pavyzdžiui, galite naudoti duomenų lentelę paskolos palūkanų normai ir laikotarpio trukmei keisti, kad įvertintumėte galimas mėnesinių mokėjimų sumas.

Pastaba: Galite atlikti greitesnius skaičiavimus naudodami duomenų lenteles ir Visual Basic for Applications (VBA). Daugiau informacijos žr. duomenų Excel What-If: spartesnis skaičiavimas naudojant VBA.

If analizės tipai    

Yra trijų tipų "what-if" analizės įrankiai Excel scenarijai, duomenų lentelėsir tikslo paieška. Scenarijai ir duomenų lentelės naudoja įvesties reikšmių rinkinius, kad apskaičiuotų galimus rezultatus. Tikslo siekis yra labai skirtingas, jis naudoja vieną rezultatą ir apskaičiuoja galimas įvesties reikšmes, kurios būtų to rezultato rezultatas.

Kaip ir scenarijai, duomenų lentelės padeda tyrinėti galimų rezultatų rinkinį. Skirtingai nei scenarijai, duomenų lentelėse rodomi visi rezultatai vienoje lentelėje viename darbalapyje. Naudojant duomenų lenteles lengva iš karto 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, turėtumėte naudoti scenarijus. Nors jis ribojamas tik vienu ar dviem kintamuoju (vienas eilutės įvesties langeliui ir vienas stulpelio įvesties langeliui), duomenų lentelėje gali būti tiek skirtingų kintamųjų reikšmių, kiek norite. Scenarijus gali turėti ne daugiau kaip 32 skirtingas reikšmes, tačiau galite sukurti tiek scenarijų, kiek norite.

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

Kurkite vieno kintamojo arba dviejų kintamųjų duomenų lenteles, atsižvelgdami į kintamųjų skaičių ir formules, kurias reikia 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.

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

Vieno kintamojo duomenų lentelė

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ą.

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

Data table with two variables
 

Duomenų lentelės skaičiavimai    

Kai darbalapis perskaičiuoja, visos duomenų lentelės taip pat bus perskaičiuomos, net jei duomenys nebuvo pakeisti. Norėdami paspartinti darbalapio, kuriame yra duomenų lentelė, skaičiavimų parinktis galite pakeisti, kad būtų automatiškai perskaičiuotas darbalapis, o ne duomenų lentelės. Norėdami sužinoti daugiau, žr. skyrių Skaičiavimo paspartinkite darbalapyje, kuriame yra duomenų lentelių.

Vieno kintamojo duomenų lentelėje yra įvesties reikšmės viename stulpelyje (į stulpelį orientuotą) arba per eilutę (orientuotą į eilutę). Bet kuri vieno kintamojo duomenų lentelės formulė turi nurodyti tik vieną įvesties langelis.

Atlikite šiuos veiksmus:

  1. Įveskite reikšmių, kurias norite pakeisti, sąrašą įvesties langelyje – žemyn vienu stulpeliu arba per vieną eilutę. 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 į stulpelį (kintamųjų reikšmės yra stulpelyje), įveskite formulę į vieną eilutę aukščiau, o vieną langelį į dešinę nuo reikšmių stulpelio. Ši vieno kintamojo duomenų lentelė yra orientuota į stulpelį, o formulė yra langelyje D2.


      Vieno kintamojo duomenų lentelė
      Jei norite patikrinti įvairių reikšmių poveikį kitoms formulėms, įveskite papildomas formules langeliuose į dešinę nuo pirmosios formulės.

    • Jei duomenų lentelė yra eilutė (kintamųjų reikšmės yra eilutėje), įveskite formulę į langelį, esantį viename stulpelyje į kairę nuo pirmos reikšmės, ir vieną langelį po reikšmių eilute.

      Jei norite patikrinti įvairių reikšmių poveikį kitoms formulėms, įveskite papildomas formules langeliuose po pirmąja formule.

  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 Duomenų lentelės >analizė (grupėje Duomenų įrankiai arba Grupės Prognozė Excel 2016 ).  

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

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

    • Jei duomenų lentelė yra eilutė, įveskite įvesties langelio nuorodą lauke Eilutės įvesties langelis.

      Pastaba: Sukūrę duomenų lentelę, galbūt norėsite pakeisti rezultatų langelių formatą. Paveikslėlyje rezultatų langeliai suformatuojami 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ų:

    • Jei duomenų lentelė yra orientuota į stulpelį, įveskite naują formulę tuščiame langelyje į dešinę nuo esamos formulės viršutinėje duomenų lentelės eilutėje.

    • Jei duomenų lentelė yra eilutė, į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 Duomenų lentelės> analizė (grupėjeDuomenų įrankiai arba Grupės Prognozė Excel 2016 ).

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

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

    • Jei duomenų lentelė yra eilutė, į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.

    Šiame pavyzdyje, kuriame 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.

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

  3. Įveskite antrą sąrašą toje pačioje eilutėje kaip 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 stulpelį (C3:C5 ir D2:E2) ir langelius, kuriuose norite apskaičiuoti reikšmes (D3:E5).

    Šiuo atveju pasirinkite diapazoną C2:E5.

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

  6. Lauke Eilutės įvesties langelis įveskite eilutės įvesties reikšmių nuorodą į įvesties langelį.
    Lauke Eilutės įvesties langelis įveskite langelįB4.

  7. Lauke Stulpelio įvesties langelis įveskite stulpelio įvesties reikšmių nuorodą į įvesties langelį.
    Lauke Stulpelio įvesties langelis įveskiteB3.

  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į hipotekos mokėjimą. Čia pateiktame paveikslėlyje langelyje C2 yra mokėjimo formulė =PMT(B3/12,B4,-B5), kuri naudoja du įvesties langelius: B3 ir B4.

Data table with two variables

Kai nustatote šią skaičiavimo parinktį, duomenų lentelės skaičiavimai neskaičiuojami, kai perskaičiuojama visoje darbaknygėje. 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ę, esančią skaičiavimo parinktyse,tada spustelėkite Automatinis, 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 rezultatą, kurio galima tikėtis iš formulės, bet tiksliai nežinote, kokios įvesties reikšmės formulė turi, kad gautumėte tą rezultatą, naudokite Goal-Seek funkciją. Žr. straipsnį Tikslo siekimo naudojimas norint rasti norimą rezultatą koreguojant įvesties reikšmę.

„Excel“ sprendimo paieška

Norėdami rasti optimalią įvesties kintamųjų rinkinio reikšmę, galite naudoti Excel sprendimo priemonės papildinių. Sprendimo paieška veikia su langelių grupe (vadinama sprendimo kintamųjų arba tiesiog kintamųjų langeliais), kurie naudojami apskaič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ška.

Į langelį prijungdami skirtingus skaičius, galite greitai pateikti skirtingus problemos atsakymus. Puikus pavyzdys yra pmt funkcijos naudojimas su skirtingomis palūkanų palūkanomis ir paskolos laikotarpiais (mėnesiais), kad išsiaiškintų, kiek paskolos galite sau leisti namams ar automobiliui. Įvesite savo skaičius į langelių diapazoną, vadinamą duomenų lentele.

Čia duomenų lentelė yra langelių diapazonas B2:D8. Galite pakeisti reikšmę B4, paskolos sumą ir mėnesinius mokėjimus stulpelyje D automatiškai atnaujinti. Naudojant 3,75 % palūkanų normą, D2 grąžina 1 042,01 EUR mėnesinį mokėjimą pagal šią formulę: =PMT(C2/12,$B$3,$B$4).

Langelių diapazonas B2:D8 yra duomenų lentelė

Galite naudoti vieną ar du kintamuosius, atsižvelgdami į norimų tikrinti kintamųjų ir formulių skaičių.

Naudokite vieno kintamojo testą, kad pamatytumėte, kaip skirtingos vieno formulės kintamojo reikšmės pakeis rezultatus. Pvz., galite pakeisti mėnesinio hipotekos mokėjimo palūkanų normą naudodami funkciją PMT. Kintamųjų reikšmes (palūkanų normas) įvedate viename stulpelyje arba eilutėje, o rezultatai rodomi netoliese esančiame stulpelyje arba eilutėje.

Šioje tiesioginėje darbaknygėje langelyje D2 yra mokėjimo formulė =PMT(C2/12,$B$3,$B$4). Langelis B3 yra kintamojo langelis, kuriame galite prijungti skirtingą termino ilgį (mėnesinių mokėjimo laikotarpių skaičių). Langelyje D2 funkcija PMT prijungia palūkanų normą 3,75%/12, 360 mėnesių ir 225 000 EUR paskolą ir apskaičiuoja 1 042,01 EUR mėnesinį mokėjimą.

Naudokite dviejų kintamųjų testą, kad pamatytumėte, kaip skirtingos dviejų formulės kintamųjų reikšmės pakeis rezultatus. Pavyzdžiui, galite išbandyti skirtingus palūkanų normų derinius ir mėnesinių mokėjimo laikotarpių skaičių, kad apskaičiuotų hipotekos mokėjimą.

Šioje tiesioginėje darbaknygėje langelyje C3 yra mokėjimo formulė=PMT($B$3/12,$B$2,B4), kuri naudoja du kintamuosius langelius B2 ir B3. Langelyje C2 funkcija PMT prijungia palūkanų normą 3,875%/12, 360 mėnesių ir 225 000 EUR paskolą ir apskaičiuoja 1 058,03 EUR mėnesinį mokėjimą.

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės arba gauti pagalbos iš Atsakymų bendruomenės.

Reikia daugiau pagalbos?

Tobulinkite savo įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijungti prie "Microsoft Insider"

Ar ši informacija buvo naudinga?

Ar esate patenkinti vertimo kokybe?
Kas turėjo įtakos jūsų įspūdžiams?

Dėkojame už jūsų atsiliepimus!

×