Mitme tulemi arvutamine andmetabeli abil

Ülevaade

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

Andmetabeli põhitõed

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 andmetabeli loomine

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

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. Klõpsake menüü Andmed nuppu Mõjuanalüüsi > andmetabel (jaotises Andmeriistad või Excel 2016 jaotis Prognoos).

5. Tehke ühte järgmistest.

   • Kui andmetabel on veerukeskne, sisestage sisendlahtri lahtriviide väljale Veerusisestuslahter . Ü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.

Valemi lisamine ühe muutujaga andmetabelisse

Ü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. Klõpsake menüü Andmed nuppu Mõjuanalüüsi>andmetabel (jaotises Andmeriistad või Excel 2016 jaotis Prognoos).

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 andmetabeli loomine

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. Klõpsake menüü Andmed jaotises Andmeriistad või Prognoos (Excel 2016) nuppu Mõjuanalüüsi > andmetabel (jaotises Andmeriistad või Excel 2016 jaotis Prognoos).
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.

Andmetabeleid sisaldava töölehe arvutuste kiirendamine

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 Failisuvandite>>valemid.

2. Jaotises Arvutussuvandid valige Automaatne.

   Näpunäide.

   Soovi korral valige Valemid vahekaardil nupu Arvutussuvandid nool ja seejärel valige Automaatne.

Mis saab edasi?

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.

Kui ühendate lahtrisse erinevad arvud, saate kiiresti leida probleemile erinevaid vastuseid. Hea näide on PMT funktsiooni kasutamine erinevate intressimäärade ja laenuperioodidega (kuudes), et välja selgitada, kui palju laenu saate endale lubada kodu või auto jaoks. Arvud sisestatakse lahtrivahemikku, mida nimetatakse andmetabeliks.

Siin on andmetabel lahtrivahemik B2:D8. Veerus D saate automaatselt muuta lahtri B4 väärtust, laenusummat ja igakuiseid makseid. Kasutades 3,75% intressimäära tagastab D2 igakuise makse summas 1042,01 $, kasutades järgmist valemit: =PMT(C2/12;$B$3;$B$4).

Olenevalt testitavate muutujate ja valemite arvust saate kasutada ühte või kahte muutujat.

Ühe muutujaga testi abil saate vaadata, kuidas ühe muutuja erinevad väärtused valemis tulemeid muudavad. Näiteks saate PMT funktsiooni abil muuta igakuise hüpoteegimakse intressimäära. Sisestage muutuja väärtused (intressimäärad) ühte veergu või ritta ja tulemused kuvatakse lähedalasuvas veerus või reas.

Selles reaalajas töövihikus sisaldab lahter D2 maksevalemit =PMT(C2/12,$B$3,$B$4). Lahter B3 on muutuv lahter, kus saate ühendada erineva perioodi pikkuse (igakuiste makseperioodide arvu). Lahtris D2 ühendab funktsioon PMT intressimäära 3,75%/12, 360 kuud ja 225 000 dollarilise laenu ning arvutab igakuise 1042,01 dollarilise makse.

Kahe muutujaga testi abil saate vaadata, kuidas kahe muutuja erinevad väärtused valemis tulemeid muudavad. Näiteks saate eluasemelaenu makse arvutamiseks testida erinevaid intressimäärade ja igakuiste makseperioodide kombinatsioone.

Selles reaalajatöövihiku lahtris C3 on maksevalem =PMT($B$3/12,$B$2,B4), mis kasutab kahte muutuvat lahtrit: B2 ja B3. Lahtris C2 ühendab funktsioon PMT intressimäära 3,875%/12, 360 kuud ja 225 000 dollarilise laenu ning arvutab igakuise 1058,03 dollarilise makse.