Mitme tulemi arvutamine andmetabeli abil

Microsoft Excelis kuuluvad andmetabelid käskude komplekti, mida nimetatakse What-If analüüsiriistade komplektiks. Andmetabelite koostamisel ja analüüsimisel teete mõjuanalüüsi.

What-if analysis is the changing the values in cells to see how those changes will affect the result of formulas on the worksheet. 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, see 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 lihtsustab võimaluste vahemiku kiiret analüüsimist. Kuna keskendute ainult ühele või kahele muutujale, on tulemeid lihtne lugeda ja jagada tabelina.

Andmetabel ei saa sisaldada rohkem kui kahte muutujat. Kui soovite analüüsida rohkem kui kahte muutujat, peaksite selle asemel kasutama stsenaariume. Kuigi see on piiratud ainult ühe või kahe muutujaga (üks reasisestuslahtri ja teine veerusisestuslahtri jaoks), võib andmetabel sisaldada nii palju erinevaid muutujaväärtusi, kui soovite. Stsenaariumis võib olla kuni 32 erinevat väärtust, kuid saate luua nii palju stsenaariume, kui soovite.

Lisateavet leiate artiklist Sissejuhatus What-If analüüsi.

Looge kas ühe- või kahemuutujaga andmetabelid sõltuvalt testitavate muutujate ja valemite arvust.

Ühe muutujaga andmetabelid    

Kasutage ühemuutujaga andmetabelit, kui soovite näha, kuidas ühe või mitme valemi ühe muutuja erinevad väärtused nende valemite tulemeid muudavad. Näiteks saate kasutada ühemuutujaga andmetabelit, et näha, kuidas erinevad intressimäärad mõjutavad kuumakset, kasutades funktsiooni PMT. Muutujaväärtused sisestatakse ü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    

Kasutage kahe muutujaga andmetabelit, et näha, kuidas kahe muutuja erinevad väärtused ühes valemis muudavad selle valemi tulemeid. Näiteks saate kasutada kahe muutujaga andmetabelit, et näha, kuidas intressimäärade ja laenutingimuste erinevad kombinatsioonid mõjutavad igakuist hüpoteegimakset.

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 andmetes pole muudatusi tehtud. Andmetabelit sisaldava töölehe arvutamise kiirendamiseks saate muuta arvutussuvandeid , et tööleht automaatselt ümber arvutada, kuid mitte andmetabeleid. Lisateavet leiate jaotisest Andmetabeleid sisaldaval töölehel arvutamise kiirendamine.

Ühemuutujaga andmetabel sisaldab sisendväärtusi kas ühes veerus (veerupõhiselt) või reas (reas). Ü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 ühele reale. Jätke mõni tühi rida ja veerg väärtuste mõlemale poolele.

2. Tehke ühte järgmistest.

   • Kui andmetabel on veerukeskne (muutuvad väärtused on veerus), tippige valem lahtrisse, mis asub ühe rea kohal ja üks lahter väärtuste veerust paremal. See ühemuutujaga andmetabel on veerupõhine ja valem asub lahtris D2.
     
     
     
     Kui soovite uurida erinevate väärtuste mõju teistele valemitele, sisestage täiendavad valemid esimesest valemist paremal asuvatesse lahtritesse.

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

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

4. Klõpsake menüü Andmed nuppu Mõjuanalüüs > Andmetabel (jaotise Andmeriistad või Excel 2016 jaotis Prognoos ).

5. Tehke ühte järgmistest.

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

   • Kui andmetabel on reakeskne, sisestage väljale Reasisestuslahtri väli sisendlahtri lahtriviide.

     : Pärast andmetabeli loomist võite soovida 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 veerukeskne, sisestage uus valem andmetabeli ülemises reas olevast olemasolevast valemist paremal asuvasse tühja lahtrisse.

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

2. Valige andmetabelit ja uut valemit sisaldav lahtrivahemik.

3. Klõpsake menüü Andmed nuppu Mõjuanalüüs > Andmetabel (jaotises Andmeriistad või Excel 2016 jaotis Prognoos ).

4. Tehke ühte järgmistest.

   • Kui andmetabel on veerukeskne, sisestage sisendlahtri lahtriviide väljale Veerusisestuslahter .

   • Kui andmetabel on reakeskne, sisestage sisendlahtri lahtriviide väljale Reasisestuslahter .

Kahe muutujaga andmetabelis kasutatakse 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 lahtrisse C2 tippida valem =PMT(B3/12;B4;-B5 ).

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

   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) ning lahtreid, milles soovite arvutatud väärtusi (D3:E5).

   Sel juhul valige vahemik C2:E5.

5. Klõpsake menüü Andmed jaotises Andmeriistad või Prognoos ( Excel 2016 ) nuppu Mõjuanalüüs > andmetabel (jaotises Andmeriistad või Excel 2016 jaotis Prognoos ).

6. Sisestage väljale Reasisestuslahter viide rea sisendväärtuste sisendlahtrile.
   Tippige väljale Reasisestuslahterlahter B4.

7. Sisestage väljale Veeru sisendlahter viide veeru sisendväärtuste sisendlahtrile.
   Tippige väljale Veerusisestuslahter väärtusB3.

8. Klõpsake nuppu OK.

Example of a two-variable data table

Kahe muutujaga andmetabel näitab, kuidas intressimäärade ja laenutingimuste erinevad kombinatsioonid mõjutavad igakuist hüpoteegimakset. 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 ümberarvutamine toimub kogu töövihikus. Andmetabeli käsitsi ümberarvutamiseks valige selle valemid ja vajutage klahvi F9.

Arvutusjõudluse parandamiseks tehke järgmist.

1. Valemite > valige Fail > suvandid.

2. Klõpsake jaotise Arvutussuvandid jaotises Arvutamine nuppu Automaatne, välja arvatud andmetabelid.

   : Soovi korral klõpsake menüü Valemid nupu Arvutussuvandid noolt ja seejärel käsku Automaatne, v.a andmetabelid (jaotises Arvutamine ).

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, et tulem eeldatakse valemilt, kuid ei tea täpselt, millist sisendväärtust valem selle tulemi saamiseks vajab, kasutage funktsiooni Goal-Seek. Soovitud tulemi leidmiseks sisendväärtust reguleerides lugege artiklit Sihiotsingu kasutamine.

Exceli Solver

Lisandmoodulit Excel Solver saate kasutada sisendmuutujate kogumi optimaalse väärtuse leidmiseks. Solver töötab selliste lahtrite rühmaga (mida nimetatakse otsustusmuutujateks või lihtsalt muutuvateks lahtriteks), mida kasutatakse siht- ja kitsenduslahtrites valemite arvutamisel. 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.