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

Duomenų lentelė yra langelių diapazonas, kurioje galite kai kuriuose langeliuose keisti reikšmes ir gauti skirtingus uždavinio atsakymus. Geras duomenų lentelės pavyzdys naudoja funkciją Pmt su skirtingomis paskolos sumomis ir palūkanų tarifais, kad galėtų apskaičiuoti prieinamą sumą, skirtą būsto paskolą namuose. Eksperimentai su skirtingomis reikšmėmis, kad būtų stebima atitinkama rezultatų variacija, yra įprasta duomenų analizėsužduotis.

Programoje "Microsoft Excel" duomenų lentelės yra komandų, vadinamų "If" analizės įrankiais, rinkinio dalis. Kai kuriate ir analizuojate duomenų lenteles, atliekate analizę.

Jei analizė yra langelių reikšmių keitimo procesas, kad pamatytumėte, kaip šie keitimai turės įtakos darbalapio formulių rezultatams. Pavyzdžiui, galite naudoti duomenų lentelę, kad galėtumėte keisti paskolos palūkanų normą ir termino trukmę, kad įvertintumėte galimas mėnesines mokėjimo sumas.

Pastaba: Galite atlikti greitesnius skaičiavimus su duomenų lentelėmis ir "Visual Basic for Applications" (VBA). Daugiau informacijos ieškokite "Excel" kas, jei duomenų lentelės: greitesnis skaičiavimas naudojant VBA.

"If" analizės tipai    

Programoje "Excel" yra trys "If" analizės įrankių tipai: scenarijai, duomenų lentelėsir tikslas – ieškoti. Scenarijai ir duomenų lentelės naudoja įvesties reikšmių rinkinius, kad apskaičiuos galimus rezultatus. Tikslo siekimas skiriasi, jis naudoja vieną rezultatą ir apskaičiuoja galimas įvesties reikšmes, kurios duotų rezultatą.

Kaip ir scenarijai, duomenų lentelės padeda sužinoti galimų rezultatų rinkinį. Skirtingai nei scenarijai, duomenų lentelėse rodomi visi rezultatai vienoje lentelėje viename darbalapyje. Duomenų lentelių naudojimas leidžia lengvai išnagrinėti įvairias galimybes. Kadangi dėmesys skiriamas tik vienam ar dviem kintamiesiems, rezultatus paprasta suprasti ir bendrinti lentelės forma.

Duomenų lentelė negali talpinti daugiau nei du kintamuosius. Jei norite analizuoti daugiau nei du kintamuosius, turėtumėte naudoti scenarijus. Nors jis apsiriboja tik vienu ar dviem kintamaisiais (vienas eilutės įvesties langeliui ir vienas stulpelio įvesties langeliui), duomenų lentelė gali apimti tiek skirtingų kintamųjų reikšmių, kiek norite. Scenarijuje gali būti ne daugiau kaip 32 skirtingų reikšmių, tačiau galite sukurti tiek scenarijų, kiek norite.

Sužinokite daugiau straipsnyje, Supažindinimas su "If" analize.

Kurkite vieno kintamojo arba dviejų kintamųjų duomenų lenteles, atsižvelgdami į kintamųjų ir formulių, kuriuos reikia 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. Į vieną stulpelį arba eilutę įvedate kintamąsias reikšmes ir rezultatai rodomi gretimame stulpelyje arba eilutėje.

Šioje iliustracijoje langelyje D2 yra mokėjimo formulė = Pmt (B3/12, B4,-B5), nurodanti į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ą.

Šioje 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ų lentelių skaičiavimai    

Kai darbalapis bus perskaičiuotas, bet kurios duomenų lentelės taip pat bus perskaičiuotas, net jei duomenys nebuvo keičiami. Norėdami pagreitinti darbalapio, kuriame yra duomenų lentelė, skaičiavimą, galite keisti skaičiavimo parinktis, kad automatiškai perskaičiuotų darbalapį, o ne duomenų lenteles. Jei norite sužinoti daugiau, peržiūrėkite darbalapio, kuriame yra duomenų lentelės, apskaičiavimo spartą.

Vieno kintamojo duomenų lentelėje pateikiamos įvesties reikšmės, esančios viename stulpelyje (su stulpeliu) arba per eilutę (į eilutę orientuota). Bet kuri vieno kintamojo duomenų lentelės formulė turi nurodyti tik vieną įvesties langelis.

Atlikite šiuos veiksmus:

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

      Vieno kintamojo duomenų lentelė

      Jei norite patikrinti įvairių reikšmių efektus kitose formulėse, į dešinę nuo pirmosios formulės galite įtraukti papildomas formules langeliuose.

    • Jei duomenų lentelė yra eilute orientuota (kintamųjų reikšmės yra eilutėje), įveskite formulę langelyje vienas stulpelis į kairę nuo pirmosios reikšmės ir per vieną langelį po reikšmių eilute.

      Jei norite patikrinti įvairių reikšmių efektus kitose formulėse, į langelius, esančius po pirmąja formule, galite įtraukti papildomas formules.

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

  4. Skirtuke duomenys spustelėkite analizė >duomenų lentelė (grupėje duomenų įrankiai grupė arba Prognozė Excel 2016 ). 

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

    • Jei duomenų lentelė išdėstyta stulpeliu, įvesties langelio langelio nuoroda įveskite 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ę, galbūt norėsite pakeisti rezultatų langelių formatą. Paveikslėlyje rezultatų langeliai formatuojami 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, į dešinę nuo esamos formulės, esančios duomenų lentelės viršutinėje eilutėje, parašykite naują formulę tuščiame langelyje.

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

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

  3. Skirtuke duomenys spustelėkite analizė> duomenų lentelė (grupėje duomenų įrankiai grupė arba Prognozė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, į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 paleidimo reikšmės įvedamos langeliuose B3, B4 ir B5, langelyje C2 įveskite formulę = Pmt (B3/12, B4,-B5) .

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

    Šiuo atveju įveskite skirtingas palūkanas į langelius C3, C4 ir C5.

  3. Į dešinę galite įtraukti antrą sąrašą toje pačioje eilutėje kaip ir formulė.

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

  4. Pažymėkite langelių, kuriuose yra formulė (C2), intervalą, reikšmių eilutę ir stulpelį (C3: C5 ir D2: E2) ir langelius, kuriuose apskaičiuojamos apskaičiuotosios reikšmės (D3: E5).

    Šiuo atveju pažymėkite intervalą C2: E5.

  5. Skirtuko duomenys grupėje duomenų įrankiai arba prognozės grupėje ( Excel 2016 ) spustelėkite " IF" analizė >duomenų lentelė (grupėje duomenų įrankiai grupė arba Prognozė Excel 2016 ). 

  6. Lauke eilutės įvesties langelis įveskite nuorodą į įvesties langelį, skirtą eilutės įvesties reikšmėms.
    Įveskite langelio 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ė gali Rodyti, kaip skirtingi palūkanų normų ir paskolos terminų deriniai turės įtakos mėnesiniam hipotekos mokėjimui. Šiame 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

Nustačius šią skaičiavimo parinktį, kai Perskaičiavimas atliekamas visoje darbaknygėje, duomenų lentelių skaičiavimai neatliekami. Jei norite duomenų lenteles perskaičiuoti neautomatiniu būdu, pasirinkite jų formules ir paspauskite klavišą F9.

Atlikite šiuos veiksmus, kad padidintumėte skaičiavimo našumą:

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

    • Excel 2007 spustelėkite "Microsoft Office" mygtuką Mygtuko paveikslėlis , spustelėkite "Excel" parinktys, tada spustelėkite kategoriją formulės .

    • Visose kitose versijose spustelėkite failų > parinktys > formules.

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

    Patarimas: Pasirinktinai skirtuke formulės spustelėkite rodyklę, esančią ant 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 rezultatą, kurio tikitės iš formulės, bet tiksliai nežinote, kokios įvesties reikšmės formulė turi gauti šį rezultatą, naudokite funkciją tikslo siekimas. Peržiūrėkite straipsnį naudojimo tikslo siekimas, kad rastumėte norimą rezultatą koreguodami įvesties reikšmę.

„Excel“ sprendimo paieška

Galite naudoti "Excel" sprendimo paieškos papildinį, kad rastumėte optimalų įvesties kintamųjų rinkinio reikšmę. Sprendimo paieška veikia su langelių grupe (vadinamais sprendimų kintamaisiais arba tiesiog kintančiais langeliais), naudojamais formulėse formulių tikslo ir apribojimų 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: nustatykite ir Išspręskite problemą naudodami sprendimo paiešką.

Prijungdami skirtingus skaičius į langelį, galite greitai rasti įvairių atsakymų į problemą. Puikus pavyzdys naudoja funkciją Pmt su skirtingais palūkanų tarifais ir paskolų laikotarpiais (mėnesiais), kad išsiaiškintu, kiek paskolą galite suteikti namams ar automobiliui. Įvedate savo numerius į langelių, vadinamų duomenų lentele, seką.

Čia duomenų lentelė yra langelių su B2: D8 intervalas. Galite pakeisti "B4", paskolos sumos ir mėnesinius mokėjimus, esančius stulpelyje D automatiškai atnaujinti. Naudojant 3,75% palūkanų normą, D2 grąžina mėnesinę išmoką $1 042,01, naudodami šią formulę: = PMT (C2/12, $B $3, $B $4).

Langelių diapazonas B2:D8 yra duomenų lentelė

Galite naudoti vieną arba du kintamuosius, atsižvelgdami į kintamųjų ir formulių, kuriuos norite išbandyti, skaičių.

Naudokite vieno kintamojo testą, kad pamatytumėte, kaip skirtingos formulės reikšmės turi pakeisti rezultatus. Pavyzdžiui, galite pakeisti mėnesinio hipotekos mokėjimo palūkanų normą naudodami funkciją PMT. Į vieną stulpelį arba eilutę įvedate kintamąsias reikšmes (palūkanas) ir rezultatai rodomi gretimame 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 kintamo dydžio langelis, kuriame galite prijungti kitą termino ilgį (mėnesinio mokėjimo laikotarpių skaičius). Langelyje D2, funkcija PMT kištukai į palūkanų normą 3,75%/12, 360 mėnesių ir $225 000 paskolą bei apskaičiuoja $1 042,01 mėnesinį mokėjimą.

Naudokite dviejų kintamųjų testą, kad pamatytumėte, kaip skirtingos dviejų kintamųjų reikšmės formulėje pakeis rezultatus. Pavyzdžiui, galite išbandyti skirtingus pomėgių derinius ir mėnesinio mokėjimo laikotarpių skaičių, kad apskaičiuotumėte hipotekos mokėjimą.

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

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės, gauti pagalbos iš Atsakymų bendruomenės arba siūlyti naują funkciją ar patobulinimą „Excel“ vartotojo balse.

Pastaba:  Šis puslapis išverstas automatiškai, todėl gali būti gramatikos klaidų ar netikslumų. Mūsų tikslas – padaryti, kad šis turinys būtų jums naudingas. Prašome mus informuoti, ar radote reikiamos informacijos. Čia yra straipsnis anglų kalba, kuriuo galite pasinaudoti kaip nuoroda.

Tobulinkite savo „Office“ įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×