Vairāku rezultātu aprēķināšana, izmantojot datu tabulu

Programmā Microsoft Excel datu tabulas ir daļa no komandu komplekta, ko sauc What-If rīkus. Veidojot un analizējot datu tabulas, jūs veicat what-if analīzi.

Ja vēlaties, varat mainīt šūnās esošās vērtības, lai redzētu, kā šīs izmaiņas ietekmēs darblapā formulu iznākumu. Piemēram, varat izmantot datu tabulu, lai dažādiem aizdevumam izmantotu procentu likmi un termiņa ilgumu, lai novērtētu iespējamos ikmēneša maksājumu apjomus.

What-if analysis    

Programmā Excel ir trīs veidu iespējas analīzes rīki: scenāriji, datu tabulas un vērtības sasniegšana. Scenāriji un datu tabulas izmanto ievades vērtību kopas, lai aprēķinātu iespējamos rezultātus. Vērtības sasniegšanas iespēja ir atšķirīgi, tā izmanto vienu rezultātu un aprēķina iespējamās ievades vērtības, kas varētu radīt šādu rezultātu.

Tāpat kā scenārijos, arī datu tabulas palīdz izpētīt iespējamo rezultātu kopu. Atšķirībā no scenārijiem datu tabulās tiek rādīti visi rezultāti vienā tabulā vienā darblapā. Izmantojot datu tabulas, varat ātri izpētīt plašu iespēju klāstu. Tā kā iegūsit informāciju par tikai vienu vai diviem mainīgajiem, rezultātus būs viegli nolasīt un koplietot tabulas formātā.

Datu tabulā var izmantot ne vairāk kā divus mainīgos. Ja vēlaties analizēt vairāk nekā divus mainīgos, tā vietā izmantojiet scenārijus. Datu tabulā var būt iekļauts tikai viens vai divi mainīgie (viens rindas ievades šūnai un viens kolonnas ievades šūnai), tomēr datu tabulā var tikt iekļauts tik daudz dažādu mainīgo vērtību, cik vien vēlaties. Scenārijā var izmantot līdz pat 32 dažādām vērtībām, tomēr varat izveidot tik scenāriju, cik nepieciešams.

Papildinformāciju skatiet rakstā Ievads par What-If analīzi.

Izveidojiet viena mainīgā vai divu mainīgo datu tabulas atkarībā no testējamo mainīgo un formulu skaita.

One-variable data tables    

Izmantojiet datu tabulu ar vienu mainīgo, ja vēlaties redzēt, kā atšķirīgas viena mainīgā vērtības vienā vai vairākām formulām mainīs šo formulu rezultātus. Piemēram, varat izmantot vienu mainīgo datu tabulu, lai skatītu, kā dažādas procentu likmes ietekmē ikmēneša hipotēkas maksājumu, izmantojot funkciju PMT. Mainīgo vērtības ievadiet vienā kolonnā vai rindā, un rezultāti tiek parādīti blakus esošā kolonnā vai rindā.

Nākamajā attēlā šūnā D2 ir maksājuma formula =PMT(B3/12,B4,-B5), kas attiecas uz ievades šūnu B3.

Two-variable data tables    

Izmantojiet datu tabulu ar diviem mainīgajiem, lai redzētu, kā divu mainīgo dažādās vērtības vienā formulā mainīs šīs formulas rezultātus. Piemēram, varat izmantot divu mainīgo datu tabulu, lai redzētu, kā dažādas procentu likmju un aizdevuma nosacījumu kombinācijas ietekmēs ikmēneša hipotēkas samaksu.

Nākamajā attēlā šūnā C2 ir maksājuma formula =PMT(B3/12,B4,-B5), kas izmanto divas ievades šūnas — B3 un B4.

 

Datu tabulas aprēķini    

Pārrēķinot darblapu, tiek pārrēķinātas arī visas datu tabulas pat tad, ja dati nav mainīti. Lai paātrinātu aprēķinus darblapā, kurā ir datu tabula, varat mainīt aprēķina opcijas, lai automātiski pārrēķinātu darblapu, bet ne datu tabulas. Papildinformāciju skatiet sadaļā Aprēķināšanas paātršana darblapā, kurā ir datu tabulas.

Viena mainīgā datu tabulā ir ievades vērtības vai nu vienā kolonnā (ar kolonnu orientēta), vai rindā (ar rindām orientēta). Visām viena mainīgā datu tabulas formulām ir jāatsaucas tikai uz vienu ievades šūna.

Izpildiet tālāk norādītās darbības.

1. Ievadiet vērtību sarakstu, ko vēlaties aizstāt ievades šūnā — vienu kolonnu uz leju vai pāri vienai rindai. Atstājiet dažas tukšas rindas un kolonnas jebkurā vērtību pusē.

2. Veiciet vienu no šīm darbībām:

   • Ja datu tabula ir orientēta kolonnā (mainīgās vērtības atrodas kolonnā), ierakstiet formulu šūnā, kas atrodas vienu rindu augstāk, un vienu šūnu pa labi no vērtību kolonnas. Šī mainīgo datu tabula ir orientēta kolonnā, un formula ir ietverta šūnā D2.
     
     
     
     Ja vēlaties izpētīt dažādu vērtību ietekmi uz citām formulām, ievadiet papildu formulas šūnās pa labi no pirmās formulas.

   • Ja datu tabula ir orientēta rindā ( mainīgās vērtības atrodas rindā), ierakstiet formulu šūnā vienu kolonnu pa kreisi no pirmās vērtības un vienu šūnu zem vērtību rindas.
     
     Ja vēlaties izpētīt dažādu vērtību ietekmi uz citām formulām, ievadiet papildu formulas šūnās zem pirmās formulas.

3. Atlasiet šūnu diapazonu, kurā ir formulas un vērtības, ko vēlaties aizstāt. Iepriekš minētajā attēlā šis diapazons ir C2:D5.

4. Cilnē Dati noklikšķiniet uz Iespējas analīze > tabula (grupā Datu rīki vai grupas Prognoze grupā Excel 2016 ).

5. Veiciet vienu no turpmākajām darbībām.

   • Ja datu tabula ir orientēta kolonnā, ievades šūnas atsauce ievadiet ievades šūnas nosaukumu kolonnas ievades šūnas laukā. Iepriekš attēlā ievades šūna ir B3.

   • Ja datu tabula ir orientēta rindā, ievades šūnas laukā ievadiet ievades šūnas atsauci.

     Piezīme.: Pēc datu tabulas izveides, iespējams, vēlēsities mainīt rezultātu šūnu formātu. Attēlā rezultāta šūnas tiek formatētas kā valūta.

Formulām, kas tiek izmantotas viena mainīgā datu tabulā, ir jāatsaucas uz vienu un to pašu ievades šūnu.

Veiciet šīs darbības

1. Veiciet kādu no tālāk minētajām abām tālāk minētajām darbības.

   • Ja datu tabula ir orientēta kolonnā, ievadiet jauno formulu tukšā šūnā pa labi no esošas formulas datu tabulas augšējā rindā.

   • Ja datu tabula ir orientēta rindā, ievadiet jauno formulu tukšā šūnā zem esošās formulas datu tabulas pirmajā kolonnā.

2. Atlasiet šūnu diapazonu, kurā ir datu tabula, un jauno formulu.

3. Cilnē Dati noklikšķiniet uz Iespējas analīze> tabula (grupā Datu rīki vai grupasPrognoze Excel 2016 ).

4. Veiciet kādu no tālāk minētajām darbībām.

   • Ja datu tabula ir orientēta kolonnā, ievadiet šūnas atsauci ievades šūnas lodziņā Kolonnas ievades šūna.

   • Ja datu tabula ir orientēta rindā, ievades šūnas atsauces lodziņā Rindas ievades šūna ievadiet šūnas atsauci.

Datu tabulā ar diviem mainīgajiem tiek izmantota formula, kurā ir divi ievades vērtību saraksti. Formulā ir jāatsaucas uz divām dažādām ievades šūnām.

Izpildiet tālāk norādītās darbības.

1. Darblapas šūnā ievadiet formulu, kas atsaucas uz divām ievades šūnām.

   Šajā piemērā, kur formulas sākuma vērtības tiek ievadītas šūnās B3, B4 un B5, šūnā C2 ir jāieraksta formula =PMT(B3/12,B4,-B5 ).

2. Zem formulas ierakstiet vienu ievades vērtību sarakstu tajā pašā kolonnā.

   Šajā gadījumā šūnās C3, C4 un C5 ierakstiet atšķirīgas procentu likmes.

3. Ievadiet otro sarakstu tajā pašā rindā, kurā atrodas formula , tās labajā pusē.

   Šūnās D2 un E2 ierakstiet aizdevuma nosacījumus (mēnešos).

4. Atlasiet šūnu diapazonu, kurā ir formula (C2), vērtību rindu un kolonnu (C3:C5 un D2:E2), un šūnas, kurās jāaprēķina vērtības (D3:E5).

   Šajā gadījumā atlasiet diapazonu C2:E5.

5. Cilnes Dati grupā Datu rīki vai Prognoze (programmā Excel 2016 ) noklikšķiniet uz Iespējas analīzes >Datu tabula (grupā Datu rīki vai grupas Excel 2016 prognoze). 

6. Rindas ievades šūnas laukā ievadiet atsauci uz ievades šūnu ievades vērtībām rindā.
   Ievades šūnas lodziņāRinda ierakstiet šūnu B4.

7. Kolonnas ievades šūnas laukā ievadiet atsauci uz ievades šūnu ievades vērtībām kolonnā.
   Kolonnas ievades šūnas lodziņā ierakstiet B3.

8. Noklikšķiniet uz Labi.

Datu tabulas ar diviem mainīgajiem piemēri

Datu tabulā ar diviem mainīgajiem varat parādīt, kā dažādas procentu likmju un aizdevuma nosacījumu kombinācijas ietekmēs ikmēneša hipotēkas maksājumu. Attēlā šūnā C2 ir maksājuma formula =PMT(B3/12,B4,-B5), kas izmanto divas ievades šūnas — B3 un B4.

Iestatot šo aprēķina opciju, datu tabulas aprēķini nenotiek, kad pārrēķins tiek veikts visā darbgrāmatā. Lai manuāli pārrēķinātu datu tabulu, atlasiet tās formulas un pēc tam nospiediet taustiņu F9.

Lai uzlabotu aprēķinu veiktspēju, veiciet tālāk norādītās darbības.

1. Noklikšķiniet uz > opcijas > Formulas.

2. Sadaļā Aprēķina opcijas zem Aprēķins noklikšķiniet uz Automātiski, izņemot datu tabulas.

   Padoms.: Ja vēlaties, cilnē Formulas noklikšķiniet uz bultiņas pie Aprēķina opcijas, pēc tam noklikšķiniet uz Automātiski , izņemot datu tabulas ( grupā Aprēķins).

Varat izmantot dažus citus Excel rīkus, lai veiktu ja vēlaties sasniegt noteiktus mērķus vai lielākas mainīgo datu kopas.

Vērtības sasniegšana

Ja zināt, kāds rezultāts sagaidāms, izmantojot formulu, bet precīzi nezināt, kāda ievades vērtība nepieciešama rezultāta laukā, izmantojiet Goal-Seek vērtību. Skatiet rakstu Vērtības sasniegšanas izmantošana, lai atrastu rezultātu, pielāgojot ievades vērtību.

Excel risinātājs

Varat izmantot Excel pievienojumprogrammu Risinātājs, lai atrastu optimālo ievades mainīgo kopas vērtību. Risinātājs darbojas kopā ar šūnu grupu (ko sauc par lēmumu mainīgajiem vai vienkārši mainīgajām šūnām), kas tiek izmantota, lai aprēķinātu formulas mērķa un ierobežojuma šūnās. Risinātājā tiek koriģētas norādīto mainīgo šūnu vērtības, lai atbilstu šūnu ierobežojumiem un izveidotu nepieciešamo mērķšūnas rezultātu. Papildinformāciju skatiet šajā rakstā: Problēmas definēšanu un risināšanu, izmantojot risinātāju.