Mitme tulemi arvutamine andmetabeli abil

Microsoft Excelis on andmetabelid osa käskude komplektist, mida tuntakse mõjuanalüüsi riistade komplektina. Andmetabelite koostamisel ja analüüsimisel teete mõjuanalüüsi.

Mõjuanalüüs on lahtrites olevate väärtuste eesmärgipärane muutmise protsess, et kindlaks teha, kuidas need muudatused mõjutavad töölehel valemite tulemusi. Näiteks saate andmetabeli abil muuta laenu intressimäära ja kestust potentsiaalsete igakuiste maksesummade hindamiseks.

Mõjuanalüüsi tüübid    

Excelis on kolme tüüpi mõjuanalüüsi tööriistu: stsenaariumid, andmetabelid ja sihiotsing. Stsenaariumid ja andmetabelid kasutavad võimalike tulemite arvutamiseks sisendväärtuste kogumeid. Sihiotsing on selgelt erinev, kasutab ühte tulemit ja arvutab võimalikud sisendväärtused, mis annaksid selle tulemi.

Sarnaselt stsenaariumidega aitavad ka andmetabelid teil uurida võimalike tulemuste kogumit. Erinevalt stsenaariumidest kuvatakse andmetabelites kõik tulemused ühel töölehel ühes tabelis. Andmetabelite kasutamine hõlbustab mitmesuguste võimaluste uurimist. Kuna keskendute ainult ühele või kahele muutujale, on tulemeid lihtne tabelina lugeda ja jagada.

Andmetabel ei saa sisaldada mahutada rohkem kui kahte muutujat. Kui soovite analüüsida rohkem kui kahte muutujat, saate kasutada stsenaariume. Kuigi see on piiratud ainult ühe või kahe muutujaga (üks reasisestuslahtri ja teine veerusisestuslahtri jaoks), võib andmetabel sisaldada nii palju erinevaid muutuja väärtusi, kui soovite. Ühel stsenaariumil võib olla kuni 32 erinevat väärtust, kuid stsenaariume saate luua piiramatul hulgal.

Lisateavet leiate artiklist Mõjuanalüüsiisissejuhatus.

Looge kas ühe- või kahe muutujaga andmetabelid olenevalt testitavate muutujate ja valemite arvust.

Ühe muutujaga andmetabelid    

Kui soovite näha, kuidas ühe või mitme valemi ühe muutuja erinevad väärtused nende valemite tulemeid muudavad, kasutage ühe muutujaga andmetabelit. Näiteks saate kasutada ühe muutujaga andmetabeli abil vaadata, kuidas erinevad intressimäärad mõjutavad kuulaenumakset kasutades PMT funktsiooni. Sisestage muutuja väärtused ühte veergu või ritta ja tulemused kuvatakse külgnevas veerus või reas.

Järgmisel joonisel on lahtris D2 maksevalem =PMT(B3/12;B4;-B5), mis viitab sisendlahtrile B3.

Kahe muutujaga andmetabelid    

Kahe muutujaga andmetabeli abil saate vaadata, kuidas kahe muutuja erinevad väärtused ühes valemis selle valemi tulemeid muudavad. Näiteks saate kasutada kahe muutujaga andmetabelit, et vaadata, kuidas erinevad intressimäärade ja laenutingimuste kombinatsioonid mõjutavad igakuist eluasemelaenu makset.

Järgmisel joonisel on lahtris C2 maksevalem =PMT(B3/12;B4;-B5),mis kasutab kahte sisendlahtrit B3 ja B4.

 

Andmetabeli arvutused    

Iga kord, kui tööleht ümber arvutab, arvutatakse ümber ka kõik andmetabelid – isegi kui andmeid pole muudetud. Andmetabelit sisaldava töölehe arvutamise kiirendamiseks saate muuta arvutuse suvandeid nii, et tööleht arvutatakse automaatselt ümber, kuid mitte andmetabeleid. Lisateavet leiate jaotisest Andmetabeleid sisaldava töölehe arvutamise kiirendamine.

Ühe muutujaga andmetabel sisaldab sisendväärtusi kas ühes veerus (veerupõhiselt) või reas (reapõhine). Ühe muutujaga andmetabeli mis tahes valem peab viitama ainult ühele sisendlahter.

Tehke järgmist

1. Tippige sisendlahtrisse väärtuste loend, mida soovite asendada – kas ühe veeru võrra alla või üle ühe rea. Jätke mõned tühjad read ja veerud väärtuste kummalegi poole.

2. Tehke ühte järgmistest.

   • Kui andmetabel on veerule suunatud (muutuvad väärtused on veerus), tippige valem lahtrisse, mis asub ühe rea kohal ja üks lahter väärtuste veerust paremal. See ühe muutujaga andmetabel on veerupõhine ja valem asub lahtris D2.
     
     
     
     Kui soovite uurida erinevate väärtuste mõju teistele valemitele, sisestage täiendavad valemid lahtritesse, mis asuvad paremal esimese valemi suhtes.

   • Kui andmetabel on reakeskne (muutuja väärtused asuvad reas), tippige valem lahtrisse, mis asub esimesest väärtusest üks veerg vasakul ja väärtuste reast üks lahter allpool.
     
     Kui soovite uurida erinevate väärtuste mõju teistele valemitele, sisestage esimese valemi all asuvatesse lahtritesse täiendavad valemid.

3. Valige lahtrivahemik, mis sisaldab valemeid ja väärtusi, mida soovite asendada. Ülaltoodud joonisel on see vahemik C2:D5.

4. ValigeAndmed vahelehel Mõjuanalüüs > andmetabeli (jaotises Andmeriistad või Prognoos jaotises Excel 2016).

5. Tehke ühte järgmistest.

   • Kui andmetabel on veerupõhine, sisestage lahtriviide sisendlahtri väärtus Veeru sisendlahtri väljale. Ülaltoodud joonisel on sisendlahter B3.

   • Kui andmetabel on reapõhine, sisestage sisendlahtri lahtriviide Reapõhine sisestuslahter väljale.

     Märkus.: Pärast andmetabeli loomist soovite võib-olla muuta tulemilahtrite vormingut. Joonisel on tulemilahtrid vormindatud valuutana.

Ühe muutujaga andmetabelis kasutatavad valemid peavad viitama samale sisendlahtrile.

Tehke järgmist.

1. Tehke ühte järgmistest:

   • Kui andmetabel on veerupõhine, sisestage uus valem andmetabeli ülemisest reast paremal asuvasse tühja lahtrisse.

   • Kui andmetabel on reapõhine, sisestage uus valem andmetabeli esimesse veergu tühja lahtrisse olemasoleva valemi alla.

2. Valige andmetabelit sisaldav lahtrivahemik ja uus valem.

3. Valige Andmed vahekaardil Mõjuanalüüs > Andmetabel (jaotises Andmeriistad või Prognoos jaotises Excel 2016).

4. Tehke ühte järgmistest.

   • Kui andmetabel on veerupõhine, sisestage sisendlahtri lahtriviide väljale Veeru sisendlahter väljale.

   • Kui andmetabel on reapõhine, sisestage sisendlahtri lahtriviide väljale Reasisestuslahter.

Kahe muutujaga andmetabel kasutab valemit, mis sisaldab kahte sisendväärtuste loendit. Valem peab viitama kahele erinevale sisendlahtrile.

Tehke järgmist

1. Sisestage töölehe lahtrisse kahele sisendlahtrile viitav valem.

   Järgmises näites, kus valemi algväärtused sisestatakse lahtritesse B3, B4 ja B5, tuleb tippida valem =PMT(B3/12,B4,-B5) lahtrisse C2.

2. Tippige samasse veergu üks sisendväärtuste loend valemi alla..

   Sel juhul tippige lahtritesse C3, C4 ja C5 erinevad intressimäärad.

3. Sisestage teine loend valemiga samale reale (paremale).

   Tippige lahtritesse D2 ja E2 laenutingimused (kuudes).

4. Valige lahtrivahemik, mis sisaldab valemit (C2), nii väärtuste rida kui ka veergu (C3:C5 ja D2:E2) ja lahtreid, milles soovite arvutatud väärtusi (D3:E5).

   Sel juhul valige vahemik C2:E5.

5. Valige Andmed vahekaardil jaotis Andmeriistad või Prognoos jaotises (Excel 2016), select Mõjuanalüüs > Andmetabeli (Andmeriistadejaotises võiPrognoosi jaotisesExcel 2016).

6. Sisestage väljale Reasisestuslahter rea sisendväärtuste viide sisendlahtrile.
   Tippigelahtrisse B4Reasisestuslahtrisse.

7. Sisestage väljale Veeru sisendlahter veeru sisendväärtuste viide sisendlahtrile.
   Tippige B3 Veeru sisendlahtrisse.

8. Klõpsake nuppu OK.

Kahe muutujaga andmetabeli näide

Kahe muutujaga andmetabel näitab, kuidas intressimäärade ja laenutingimuste kombinatsioon mõjutab igakuist eluasemelaenumakset. Siinsel joonisel on lahtris C2 maksevalem =PMT(B3/12;B4;-B5),mis kasutab kahte sisendlahtrit B3 ja B4.

Selle arvutussuvandi seadmisel ei toimu andmetabeli arvutusi, kui kogu töövihikus toimub ümberarvutamine. Andmetabeli käsitsi ümberarvutamiseks valige selle valemid ja vajutage klahvi F9.

Arvutusjõudluse parandamiseks tehke järgmist:

1. Valige Fail > Suvandid > Valemid.

2. Jaotises Arvutussuvandid valige Automaatne.

   Näpunäide.: Soovi korral valige Valemid vahekaardil nupu Arvutussuvandid nool ja seejärel valige Automaatne.

Kui teil on konkreetseid eesmärke või suuremaid muutuvaid andmeid, saate mõjuanalüüsi tegemiseks kasutada ka muid Exceli tööriistu.

Sihiotsing

Kui teate, millise tulemi valem peaks andma, kuid pole päris kindel, millist sisendväärtust valem selle tulemi tagastamiseks vajab, kasutage sihiotsingu funktsiooni. Teavet leiate artiklist Sihiotsingu abil soovitud tulemi leidmiseks kohandage sisendväärtust.

Exceli Solver

Lisandmooduli Excel Solver abil saate kasutada sisendmuutujate kogumi optimaalse väärtuse leidmiseks. Solver töötab lahtrirühmaga (mida nimetatakse otsustusmuutujateks või lihtsalt muutujalahtriteks), mida kasutatakse sihi- ja piirangulahtrites olevate valemite arvutamiseks. Solver kohandab otsustusmuutujate lahtriväärtusi nii, et need täidaksid piirangulahtrite tingimused ja annaksid sihtlahtri jaoks teie soovitud tulemuse. Lisateavet leiate artiklist Probleemi määratlemine ja lahendamine Solveri abil.