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

Programmā Microsoft Excel datu tabulas ir daļa no komandu komplekta, ko sauc par iespēju analīzes rīkiem. Kad veidojat un analizēsit datu tabulas, jūs veicat iespēju analīzi.

Iespēju analīze ir šūnu vērtību mainīšanas process, lai redzētu, kā šīs izmaiņas ietekmēs darblapā esošo formulu rezultātu. Piemēram, varat izmantot datu tabulu, lai mainītu aizdevuma procentu likmi un termiņa ilgumu, lai novērtētu iespējamos ikmēneša maksājumus.

Iespēju analīzes veidi    

Programmā Excel ir trīs veidu iespēju analīzes rīki: scenāriji, datu tabulas un vērtības sasniegšanas. Scenāriji un datu tabulas izmanto ievades vērtību kopas, lai aprēķinātu iespējamos rezultātus. Mērķa meklēšana ir būtiski atšķirīga — tā izmanto vienu rezultātu un aprēķina iespējamos ievades datus, kas radītu šo rezultātu.

Līdzīgi scenārijiem 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 ērti 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ā nevar ietilpt vairāk par diviem mainīgajiem. Ja vēlaties analizēt vairāk nekā divus mainīgos, izmantojiet scenārijus. Lai gan tas ir ierobežots līdz tikai vienam vai diviem mainīgajiem (viens rindas ievades šūnai un viens kolonnas ievades šūnai), datu tabulā var būt tik daudz dažādu mainīgo vērtību, cik vien vēlaties. Scenārijā var būt ne vairāk kā 32 dažādas vērtības, bet varat izveidot tik daudz scenāriju, cik vien vēlaties.

Papildinformāciju skatiet rakstā ievads par iespēju analīzi.

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

Viena mainīgā datu tabulas    

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

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

Divu mainīgo datu tabulas    

Izmantojiet divu mainīgo datu tabulu, lai uzzinātu, kā divu mainīgo atšķirīgās vērtības vienā formulā mainīs šīs formulas rezultātus. Piemēram, varat izmantot divu mainīgo datu tabulu, lai uzzinātu, kā dažādas procentu likmes un aizdevuma nosacījumu kombinācijas ietekmēs ikmēneša hipotēkas maksājumu.

Tālāk esošajā attēlā šūnā C2 ir maksājuma formula =PMT(B3/12,B4,-B5), kurā izmantotas 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 darblapai, kurā ir datu tabula, varat mainīt Aprēķina opcijas, lai automātiski pārrēķinātu darblapu, bet ne datu tabulas. Lai uzzinātu vairāk, skatiet sadaļu Aprēķina ātrums darblapā, kurā ir datu tabulas.

Viena mainīgā datu tabulā ir ievades vērtības vienā kolonnā (uz kolonnu orientēta) vai pāri rindai (uz rindu orientēta). Jebkurai formulai viena mainīgā datu tabulā ir jāatsaucas tikai uz vienu ievades šūna.

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

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

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

   • Ja datu tabula ir uz kolonnu orientēta (mainīgās vērtības ir kolonnā), ierakstiet formulu šūnā, kas atrodas vienu rindu virs un vienu šūnu pa labi no vērtību kolonnas. Šī viena mainīgā datu tabula ir orientēta uz kolonnu, un formula ir iekļauta šū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 uz rindu orientēta (mainīgās vērtības ir rindā), ierakstiet formulu šūnā, kas atrodas 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, kuras vēlaties aizstāt. Iepriekš norādītajā attēlā šis diapazons ir C2:D5.

4. Cilnē Dati atlasiet iespēju analīze >  Datu tabula (grupā Datu rīki vai Prognoze grupā Excel 2016).

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

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

   • Ja datu tabula ir orientēta uz rindu, ievadiet šūnas atsauci ievades šūnai rindas ievades laukā.

     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.

Viena mainīgā datu tabulā izmantotajām formulām ir jāatsaucas uz vienu un to pašu ievades šūnu.

Veiciet šīs darbības

1. Veiciet kādu no šīm darbībām:

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

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

2. Atlasiet šūnu diapazonu, kurā ir datu tabula un jaunā formula.

3. Cilnē Dati atlasiet iespēju analīze > Datu tabula (grupā Datu rīki vai Prognoze grupā Excel 2016).

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

   • Ja datu tabula ir orientēta uz kolonnu, ievadiet šūnas atsauci ievades šūnai kolonnas ievades šūnas lodziņā.

   • Ja datu tabula ir orientēta uz rindu, ievadiet šūnas atsauci ievades šūnai rindas ievades šūnas lodziņā.

Divu mainīgo datu tabulā tiek izmantota formula, kurā ir divi ievades vērtību saraksti. Formulai 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 abām ievades šūnām.

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

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

   Šajā gadījumā šūnās C3, C4 un C5 ierakstiet dažādas 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 vēlaties aprēķinātās vērtības (D3:E5).

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

5. Cilnē Dati, grupā Datu rīki vai Prognoze (programmā Excel 2016) atlasiet Iespējas analīze > Datu tabula (grupā Datu rīki vai Prognoze grupā Excel 2016).

6. Laukā rindas ievades ievadiet atsauci uz ievades šūnu ievades vērtībām rindā.
   Ierakstiet šūnu B4Ievades šūnas lodziņā.

7. Laukā Kolonnas ievades šūna ievadiet atsauci uz ievades šūnu, kas satur kolonnas ievades vērtības.
   Ierakstiet šūnu B3Ievades kolonnas lodziņā.

8. Atlasiet Labi.

Divu mainīgo datu tabulas piemērs

Divu mainīgo datu tabulā var parādīt, kā dažādas procentu likmes un aizdevuma nosacījumi 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ēķināšana tiek veikta 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ēķina veiktspēju, veiciet tālāk norādītās darbības:

1. Noklikšķiniet uz Fails > Opcijas > Formulas.

2. Sadaļā Aprēķina opcijas atlasiet Automātiski.

   Padoms.: Ja vēlaties, cilnē Formulas atlasiet bultiņu cilnē Aprēķina opcijas, pēc tam atlasietAutomātiski.

Varat izmantot dažus citus Excel rīkus, lai veiktu iespēju analīzi, ja jums ir konkrēti mērķi vai lielāka mainīgo datu kopa.

Vērtības sasniegšana

Ja zināt rezultātu, kas sagaidāms no formulas, bet precīzi nezināt, kāda ievades vērtība formulai ir nepieciešama, lai iegūtu šo rezultātu, izmantojiet līdzekli Mērķa meklēšana. 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ālu ievades mainīgo kopas vērtību. Risinātājs darbojas ar šūnu grupu (ko sauc par lēmumu mainīgajiem vai vienkārši mainīgajām šūnām), kas tiek izmantotas, lai aprēķinātu formulas mērķa un ierobežojumu šū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ēšana un risināšana, izmantojot pievienojumprogrammu Risinātājs.