Andmetabel on lahtrivahemik, kus saate muuta mõnes lahtris olevaid väärtusi ja leida probleemile erinevaid vastuseid. Hea näide andmetabelist kasutab funktsiooni PMT erinevate laenusummade ja intressimääradega, et arvutada kodulaenu taskukohane summa. Erinevate väärtustega katsetamine tulemuste vastava variatsiooni jälgimiseks on levinud toiming andmeanalüüsis.
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ärkus.: Andmetabelite ja rakenduse Visual Basic for Applications (VBA) abil saate teha kiiremini arvutusi. Lisateavet leiate teemast Excel What-If andmetabelid: kiirem arvutamine VBA-ga.
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
-
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.
-
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 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.
-
-
Valige lahtrivahemik, mis sisaldab valemeid ja väärtusi, mida soovite asendada. Ülaltoodud joonisel on see vahemik C2:D5.
-
Klõpsake menüü Andmed nuppu Mõjuanalüüs > Andmetabel (jaotise Andmeriistad või Excel 2016 jaotis Prognoos ).
-
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.
Märkus.: 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.
-
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.
-
-
Valige andmetabelit ja uut valemit sisaldav lahtrivahemik.
-
Klõpsake menüü Andmed nuppu Mõjuanalüüs > Andmetabel (jaotises Andmeriistad või Excel 2016 jaotis Prognoos ).
-
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
-
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 ).
-
Tippige valemi alla üks sisendväärtuste loend samasse veergu.
Sel juhul tippige lahtritesse C3, C4 ja C5 erinevad intressimäärad.
-
Sisestage teine loend valemiga samale reale (paremale).
Tippige lahtritesse D2 ja E2 laenutingimused (kuudes).
-
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.
-
Klõpsake menüü Andmed jaotises Andmeriistad või Prognoos ( Excel 2016 ) nuppu Mõjuanalüüs > andmetabel (jaotises Andmeriistad või Excel 2016 jaotis Prognoos ).
-
Sisestage väljale Reasisestuslahter viide rea sisendväärtuste sisendlahtrile.
Tippige väljale Reasisestuslahterlahter B4. -
Sisestage väljale Veeru sisendlahter viide veeru sisendväärtuste sisendlahtrile.
Tippige väljale Veerusisestuslahter väärtusB3. -
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.
-
Valemite > valige Fail > suvandid.
-
Klõpsake jaotise Arvutussuvandid jaotises Arvutamine nuppu Automaatne, välja arvatud andmetabelid.
Näpunäide.: 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.
Kui ühendate lahtrisse erinevad arvud, saate kiiresti leida probleemile erinevaid vastuseid. Hea näide on funktsiooni PMT kasutamine erinevate intressimäärade ja laenuperioodidega (kuudes), et välja selgitada, kui palju laenu saate lubada kodu või auto jaoks. Arvud sisestatakse lahtrivahemikku, mida nimetatakse andmetabeliks.
Siin on andmetabel lahtrivahemik B2:D8. Veerus D saate automaatselt värskendada 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).
Sõltuvalt testitavate muutujate ja valemite arvust saate kasutada ühte või kahte muutujat.
Kasutage ühemuutujaga testi, et näha, kuidas ühe muutuja erinevad väärtused valemis tulemeid muudavad. Näiteks saate funktsiooni PMT abil muuta igakuise hüpoteegimakse intressimäära. Sisestage muutuvad 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 terminipikkuse (igakuiste makseperioodide arvu). Lahtris D2 ühendab funktsioon PMT intressimääraga 3,75%/12, 360 kuud ja 225 000 dollarilise laenu ning arvutab igakuise 1042,01 dollarilise makse.
Kasutage kahe muutujaga testi, et näha, kuidas kahe muutuja erinevad väärtused valemis tulemeid muudavad. Näiteks saate hüpoteegimakse arvutamiseks testida intressimäärade ja igakuiste makseperioodide erinevaid 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.
Kas vajate rohkem abi?
Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.
