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

Piezīme.: Mēs vēlamies sniegt jums visjaunāko palīdzības saturu jūsu valodā, cik vien ātri to varam. Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai lapas lejasdaļā varat mums pavēstīt, vai informācija jums noderēja? Ērtai atsaucei šeit ir šis raksts angļu valodā.

Datu tabula ir šūnu diapazons, kurā dažās šūnās varat mainīt vērtības un nākt klajā ar dažādām atbildēm uz problēmu. Labs datu tabulas piemērs nodarbina funkciju PMT ar dažādu aizdevuma summu un procentu likmēm, lai aprēķinātu pieņemamu summu mājokļa hipotekārajā aizņēmumā. Eksperimentēšana ar dažādām vērtībām, lai ievērotu attiecīgās izmaiņas rezultātos, ir kopējs uzdevums datu analīzē.

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

Iespēju analīze ir process, kurā šūnās tiek mainītas vērtības, lai uzzinātu, kā šīs izmaiņas ietekmēs darblapā iekļauto formulu rezultātu. Piemēram, varat izmantot datu tabulu, lai mainītu aizdevuma procentu likmi un termiņa ilgumu, lai novērtētu potenciālās mēneša maksājumu summas.

Piezīme.: Varat veikt ātrāku aprēķinu ar datu tabulām un Visual Basic for Applications (VBA). Lai iegūtu papildinformāciju, skatiet rakstu Excel iespēju datu tabulas: ātrāks aprēķins ar VBA.

Iespēju analīzes veidi    

Programmā Excel ir trīs veidu iespēju analīzes rīki: scenāriji, datu tabulasun mērķa meklēšana. Scenāriji un datu tabulas izmanto ievades vērtību kopas, lai aprēķinātu iespējamos rezultātus. Mērķis-meklēt ir izteikti atšķirīgi, tas izmanto vienu rezultātu un aprēķina iespējamās ievades vērtības, kas izraisa š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 tabulas parāda visus rezultātus vienā tabulā vienā darblapā. Izmantojot datu tabulas, varat viegli izpētīt 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 tabula nevar ietvert vairāk nekā divus mainīgos. Ja vēlaties analizēt vairāk nekā divus mainīgos, izmantojiet scenārijus. Lai gan tas ir ierobežots tikai ar vienu vai diviem mainīgajiem (viens rindas ievades šūnai un viens kolonnas ievades šūnai), datu tabulā var iekļaut tik daudz dažādu mainīgo vērtību, cik vien vēlaties. Scenārijā var būt ne vairāk kā 32 dažādu vērtību, taču varat izveidot tik daudz scenāriju, cik vien vēlaties.

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

Izveidojiet datu tabulas ar vienu mainīgo vai diviem mainīgajiem atkarībā no tā, cik mainīgo un formulu ir jāpārbauda.

Datu tabulas ar vienu mainīgo    

Izmantojiet datu tabulu ar vienu mainīgo, ja vēlaties uzzināt, kā viena mainīgā vērtības vienā vai vairākās formulās mainīs šo formulu rezultātus. Piemēram, var izmantot datu tabulu ar vienu mainīgo, lai uzzinātu, kā dažādi procentu kursi ietekmē ikmēneša ķīlas maksājumus, izmantojot funkciju PMT. Vienā kolonnā vai rindā ievada mainīgo vērtības, un rezultāti tiek parādīti blakus esošā kolonnā vai rindā.

Tālāk redzamajā attēlā šūnā D2 ir maksājuma formula = PMT (B3/12; B4;-B5), kas norāda uz ievades šūnu B3.

Datu tabula ar vienu mainīgo

Datu tabulas ar diviem mainīgajiem    

Izmantojiet datu tabulu ar diviem mainīgajiem, lai uzzinātu, kā divu mainīgu vērtību starpā vienā formulā mainīsies šīs formulas rezultāti. Piemēram, varat izmantot datu tabulu ar diviem mainīgajiem, lai uzzinātu, kā dažādas procentu likmju un aizņēmuma termiņu kombinācijas ietekmē ikmēneša ķīlas maksājumus.

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

Datu tabula ar diviem mainīgajiem
 

Datu tabulas aprēķini    

Ikreiz, kad darblapa tiek pārrēķināta, visas datu tabulas arī pārrēķinās — pat tad, ja dati nav mainīti. Lai paātrinātu darblapas aprēķinu, kurā ir ietverta datu tabula, varat mainīt aprēķināšanas opcijas, lai automātiski pārrēķinātu darblapu, bet nelietotu datu tabulas. Lai uzzinātu vairāk, skatiet sadaļu kā paātrināt aprēķinus darblapā, kurā ir datu tabulas.

Datu tabula ar vienu mainīgo iekļauj tās ievades vērtības vienā kolonnā (kolonna orientēta) vai rindā (uz rindu orientēta). Visām formulām datu tabulās ar vienu mainīgo ir jāattiecas 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 ar ievades šūnu, vai nu vienu kolonnu uz leju vai pa 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 kā kolonna (mainīgo vērtību vērtība ir kolonnā), ierakstiet formulu šūnā vienu rindu virs un vienu šūnu pa labi no vērtību kolonnas. Šī datu tabula ar vienu mainīgo ir orientēta kā kolonna, un formula ir iekļauta šūnā D2.


      Datu tabula ar vienu mainīgo
      Ja vēlaties pārbaudī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 kā rinda (mainīgo vērtību vērtība ir 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 pārbaudī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 aizstājamās formulas un vērtības. Iepriekš redzamajā attēlā šis diapazons ir C2: D5.

  4. Cilnē dati noklikšķiniet uz iespēju analīzes >datu tabula (grupā datu rīki vai Excel 2016 ).  

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

    • Ja datu tabula ir orientēta kā kolonna, lodziņā kolonnas ievades šūna ievadiet ievades šūnas šūnas atsauce. Iepriekš redzamajā attēlā ievades šūna ir B3.

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

      Piezīme.: Pēc datu tabulas izveidošanas, iespējams, vēlēsities mainīt rezultātu šūnu formātu. Ilustrācijā rezultāta šūnas ir formatētas kā valūta.

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

Veiciet šīs darbības

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

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

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

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

  3. Cilnē dati noklikšķiniet uz iespēju analīzes > datu tabula (grupā datu rīki vai Excel 2016 ).

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

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

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

Datu tabula ar diviem mainīgajiem izmanto formulu, 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 ir ievadītas šūnās B3, B4 un B5, ierakstāt formulu = PMT (B3/12; B4;-B5) šūnā C2.

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

    Šajā gadījumā ierakstiet dažādos procentu kursus šūnās C3, C4 un C5.

  3. Ievadiet otro sarakstu tajā pašā rindā kā formula — pa labi.

    Ievadiet aizņēmuma termiņus (mēnešos) šūnās D2 un E2.

  4. Atlasiet šūnu diapazonu, kurā ir formula (C2), vērtību rindu un kolonnu (C3: C5 un D2: E2), kā arī šūnas, kurās jāatrodas aprēķinātajām vērtībām (D3: E5).

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

  5. Cilnes dati grupā datu rīki vai prognozes grupa (programmā Excel 2016 ) noklikšķiniet uz iespēju analīzes > datu tabula (grupā datu rīki vai prognozes grupa Excel 2016 ).

  6. Laukā rindas ievades šūna ievadiet atsauci uz ievades šūnu rindas ievades vērtībām.
    Lodziņā rindas ievades šūna ierakstiet šūnu B4 .

  7. Laukā kolonnas ievades šūna ievadiet atsauci uz ievades šūnu kolonnas ievades vērtībām.
    Lodziņā kolonnas ievades šūna ierakstiet B3 .

  8. Noklikšķiniet uz Labi.

Datu tabulas ar diviem mainīgajiem piemērs

Datu tabula ar diviem mainīgajiem var parādīt, kā dažādas procentu likmju un aizņēmuma termiņu kombinācijas ietekmē ikmēneša ķīlas maksājumus. Šajā attēlā šūnā C2 ir iekļauta maksājuma formula = PMT (B3/12; B4;-B5), kas izmanto divas ievades šūnas B3 un B4.

Datu tabula ar diviem mainīgajiem

Iestatot šo aprēķinu opciju, netiek veikti datu tabulas aprēķini, ja 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. Veiciet kādu no tālāk minētajām darbībām.

    • Programmā Excel 2007 noklikšķiniet uz Microsoft Office pogas Office pogas attēls , noklikšķiniet uz Excel opcijasun pēc tam uz kategorijas formulas .

    • Visās citās versijās noklikšķiniet uz Fails > Opcijas > formulas.

  2. Sadaļā aprēķina opcijas zem Aprēķinātnoklikšķiniet uz automātiski, izņemot datu tabulas.

    Padoms.: Ja vēlaties, cilnē formulas noklikšķiniet uz bultiņas pie aprēķina opcijasun 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 iespēju analīzi, ja jums ir noteikti mērķi vai lielākas mainīgo datu kopas.

Vērtības sasniegšana

Ja esat pārliecināts, ka rezultāts ir sagaidāms no formulas, bet nezināt precīzu ievades vērtību, kas nepieciešama formulai, lai iegūtu šo rezultātu, izmantojiet funkciju Goal-Search. Skatiet rakstu vērtības sasniegšanas līdzekļa izmantošana, lai atrastu vēlamo rezultātu, pielāgojot ievades vērtību.

Excel Risinātājs

Varat izmantot Excel pievienojumprogrammu Risinātājs, lai atrastu optimālāko vērtību ievades mainīgo kopai. Risinātājs darbojas ar šūnu grupu (dēvētu par lēmumu mainīgajiem vai vienkārši mainīgajām šūnām), kas tiek izmantotas, lai formulās lietotu 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.

Pievienojot dažādus skaitļus šūnā, varat ātri saņemt dažādas atbildes uz problēmu. Liels piemērs ir funkcijas PMT izmantošana ar dažādiem procentu likmēm un aizņēmuma periodiem (mēnešos), lai noskaidrotu, cik daudz aizdevuma varat atļauties mājas vai auto. Jūs ievadiet skaitļus šūnu diapazonā, ko dēvē par datu tabulu.

Šeit datu tabula ir šūnu diapazons B2: D8. Varat mainīt vērtību šūnā B4, aizdevuma summu un ikmēneša maksājumus kolonnā D automātiski atjaunināt. Izmantojot 3,75% procentu likmi, D2 atgriež mēneša maksājumu $1 042,01, izmantojot šo formulu: = PMT (C2/12, $B $3, $B $4).

Šis šūnu diapazons, B2:D8, ir datu tabula

Atkarībā no tā, cik mainīgo un formulu vēlaties pārbaudīt, varat izmantot vienu vai divus mainīgos.

Izmantojiet vienu mainīgo testēšanu, lai uzzinātu, kā vienā mainīgā lielumā tiek mainītas dažādas vērtības formulā. Piemēram, varat mainīt procentu likmi par ikmēneša ķīlas maksājumu, izmantojot funkciju PMT. Jūs ievadiet mainīgās vērtības (procentu likmes) vienā kolonnā vai rindā un rezultāti tiek parādīti tuvējā kolonnā vai rindā.

Šajā dzīvajā darbgrāmatā šūnā D2 ir ietverta maksājuma formula =PMT (C2/12, $B $3, $B $4). Šūnā B3 ir mainīga šūna, kur varat pievienot dažādu terminu ilgumu (mēneša maksājumu periodu skaits). Šūnā D2 funkcija PMT pievieno procentu likmi 3.75%/12, 360 mēnešus un $225 000 aizņēmumu un aprēķina $1 042,01 mēneša maksājumu.

Izmantojiet divus mainīgos testus, lai uzzinātu, kā formulā mainīs divu mainīgo lielumu atšķirīgas vērtības. Piemēram, varat pārbaudīt dažādas procentu likmju kombinācijas un mēneša maksājumu periodu skaitu, lai aprēķinātu ķīlas maksājumus.

Šajā dzīvajā darbgrāmatā šūnā C3 ir maksājuma formula =PMT ($B $3/12, $B $2, B4), kas izmanto divas mainīgās šūnas B2 un B3. Šūnā C2 funkcija PMT tiek pievienota procentu likmju 3.875%/12, 360 mēnešu un $225 000 aizdevumam un aprēķina $1 058,03 mēneša maksājumu.

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×