Calcularea rezultatelor multiple utilizând un tabel de date

În Microsoft Excel, tabelele de date fac parte dintr-o suită de comenzi cunoscută drept instrumente de analiză What-If. Atunci când construiți și analizați tabele de date, faceți analize circumstanțiale.

Analiza circumstanțială este procesul de modificare a valorilor din celule pentru a vedea cum vor afecta modificările respective rezultatul formulelor din foaia de lucru. De exemplu, puteți utiliza un tabel de date pentru a varia rata dobânzii și lungimea termenului pentru un împrumut, pentru a evalua sumele de plată lunare potențiale.

Tipuri de analiză circumstanțială    

Există trei tipuri de instrumente de analiză circumstanțială în Excel: scenarii, tabele de date și căutare de obiective. Scenariile și tabelele de date utilizează seturi de valori de intrare pentru a calcula rezultatele posibile. Căutarea obiectivului este foarte diferită, utilizează un singur rezultat și calculează valorile de intrare posibile care ar produce rezultatul respectiv.

La fel ca scenariile, tabelele de date vă ajută să explorați un set de rezultate posibile. Spre deosebire de scenarii, tabelele de date vă arată toate rezultatele într-un singur tabel dintr-o foaie de lucru. Utilizarea tabelelor de date vă ajută să examinați dintr-o privire o serie de posibilități. Întrucât vă concentrați doar pe una sau două variabile, rezultatele sunt ușor de citit și de partajat în formă tabulară.

Un tabel de date nu poate cuprinde mai mult de două variabile. Dacă doriți să analizați mai mult de două variabile, ar trebui să utilizați în schimb scenarii. Deși este limitată la una sau două variabile (una pentru celula de intrare a rândului și una pentru celula de intrare a coloanei), un tabel de date poate include câte valori variabile diferite doriți. Un scenariu poate avea un număr maxim de 32 de valori diferite, dar puteți crea câte scenarii doriți.

Aflați mai multe în articolul Introducere în What-If Analiză.

Creați tabele de date cu o variabilă sau cu două variabile, în funcție de numărul de variabile și de formule pe care trebuie să le testați.

Tabele de date cu o variabilă    

Utilizați un tabel de date cu o variabilă dacă doriți să vedeți cum diferitele valori ale unei variabile într-una sau mai multe formule vor modifica rezultatele acestor formule. De exemplu, puteți utiliza un tabel de date cu o variabilă pentru a vedea modul în care ratele diferite ale dobânzii afectează plata lunară a unui împrumut, utilizând funcția PMT. Introduceți valorile variabile într-o singură coloană sau rând, iar rezultatele sunt afișate într-o coloană sau un rând adiacent.

În ilustrația următoare, celula D2 conține formula de plată, =PMT(B3/12,B4,-B5), care se referă la celula de intrare B3.

Tabele de date cu două variabile    

Utilizați un tabel de date cu două variabile pentru a vedea modul în care valorile diferite ale două variabile într-o formulă vor modifica rezultatele formulei respective. De exemplu, puteți utiliza un tabel de date cu două variabile pentru a vedea modul în care diferitele combinații de rate ale dobânzii și termenilor împrumutului vor afecta plata lunară a unui împrumut.

În ilustrația următoare, celula C2 conține formula de plată, =PMT(B3/12,B4,-B5), care utilizează două celule de intrare, B3 și B4.

 

Calcule tabel de date    

Ori de câte ori o foaie de lucru se recalculează, toate tabelele de date se vor recalcula, chiar dacă nu au existat modificări ale datelor. Pentru a accelera calculul unei foi de lucru care conține un tabel de date, puteți modifica opțiunile de calcul pentru a recalcula automat foaia de lucru, dar nu și tabelele de date. Pentru a afla mai multe, consultați secțiunea Accelerarea calculării într-o foaie de lucru care conține tabele de date.

Un tabel de date cu o variabilă conține valorile sale de intrare fie într-o singură coloană (orientată pe coloană), fie pe un rând (orientat spre rând). Orice formulă dintr-un tabel de date cu o singură variabilă trebuie să facă referire la un singur celulă de intrare.

Urmați acești pași:

1. Tastați lista de valori pe care doriți să le înlocuiți în celula de intrare, fie în jos, fie pe un rând. Lăsați câteva rânduri și coloane goale pe fiecare parte a valorilor.

2. Alegeți una dintre următoarele variante:

   • Dacă tabelul de date este orientat spre coloană (valorile variabile sunt într-o coloană), tastați formula în celula cu un rând deasupra și cu o celulă la dreapta coloanei de valori. Acest tabel de date cu o variabilă este orientat spre coloană, iar formula este conținută în celula D2.
     
     
     
     Dacă doriți să examinați efectele diferitelor valori din alte formule, introduceți formulele suplimentare în celulele din partea dreaptă a primei formule.

   • Dacă tabelul de date este orientat pe rânduri (valorile variabile sunt într-un rând), tastați formula în celula cu o coloană la stânga primei valori și cu o celulă sub rândul de valori.
     
     Dacă doriți să examinați efectele diferitelor valori din alte formule, introduceți formulele suplimentare în celule sub prima formulă.

3. Selectați zona de celule care conține formulele și valorile pe care doriți să le înlocuiți. În figura de mai sus, acest interval este C2:D5.

4. Pe fila Date , selectați Analiză circumstanțială > Tabel de date (în grupul Instrumente de date sau în grupul Prognoză de Excel 2016 ).

5. Alegeți una dintre următoarele variante:

   • Dacă tabelul de date este orientat spre coloană, introduceți referință de celulă pentru celula de intrare în câmpul celulei de intrare Coloană . În figura de mai sus, celula de intrare este B3.

   • Dacă tabelul de date este orientat spre rând, introduceți referința de celulă pentru celula de intrare în câmpul celulei de intrare Rând .

     Notă: După ce creați tabelul de date, se recomandă să modificați formatul celulelor rezultat. În figură, celulele rezultat sunt formatate ca monedă.

Formulele care sunt utilizate într-un tabel de date cu o variabilă trebuie să facă referire la aceeași celulă de intrare.

Urmați acești pași

1. Alegeți una dintre următoarele:

   • Dacă tabelul de date este orientat pe coloane, introduceți formula nouă într-o celulă necompletată din partea dreaptă a unei formule existente în rândul de sus al tabelului de date.

   • Dacă tabelul de date este orientat pe rânduri, introduceți formula nouă într-o celulă necompletată sub o formulă existentă din prima coloană a tabelului de date.

2. Selectați zona de celule care conține tabelul de date și formula nouă.

3. Pe fila Date , selectați Analiză circumstanțială > Tabel de date (în grupul Instrumente de date sau în grupul Prognoză de Excel 2016 ).

4. Alegeți una dintre următoarele:

   • Dacă tabelul de date este orientat spre coloană, introduceți referința de celulă pentru celula de intrare în caseta celulă de intrare Coloană .

   • Dacă tabelul de date este orientat pe rânduri, introduceți referința la celulă pentru celula de intrare în caseta celulă de intrare Rând .

Un tabel de date cu două variabile utilizează o formulă care conține două liste de valori de intrare. Formula trebuie să facă referire la două celule de intrare diferite.

Urmați acești pași:

1. Într-o celulă din foaia de lucru, introduceți formula care face referire la cele două celule de intrare.

   În exemplul următor, în care valorile de pornire ale formulei sunt introduse în celulele B3, B4 și B5, tastați formula =PMT(B3/12,B4,-B5) în celula C2.

2. Tastați o listă de valori de intrare în aceeași coloană, sub formulă.

   În acest caz, tastați ratele dobânzii diferite în celulele C3, C4 și C5.

3. Introduceți a doua listă în același rând cu formula, la dreapta acesteia.

   Tastați termenii împrumutului (în luni) în celulele D2 și E2.

4. Selectați zona de celule care conține formula (C2), atât rândul, cât și coloana de valori (C3:C5 și D2:E2) și celulele în care doriți valorile calculate (D3:E5).

   În acest caz, selectați zona C2:E5.

5. Pe fila Date , în grupul Instrumente de date sau În grupul Prognoză (în Excel 2016 ), selectați Analiză circumstanțială > Tabel de date (în grupul Instrumente de date sau Grupul Prognoză de Excel 2016 ).

6. În câmpul Celulă de intrare Rând , introduceți referința la celula de intrare pentru valorile de intrare din rând.
   Tastați celula B4 în caseta celulă de intrare Rând .

7. În câmpul Celulă de intrare Coloană , introduceți referința la celula de intrare pentru valorile de intrare din coloană.
   Tastați B3 în caseta celulă de intrare Coloană .

8. Selectați OK.

Exemplu de tabel de date cu două variabile

Un tabel de date cu două variabile poate arăta modul în care diferitele combinații de rate ale dobânzii și termenilor împrumutului vor afecta plata lunară a unui împrumut ipotecar. În figura de aici, celula C2 conține formula de plată, =PMT(B3/12,B4,-B5), care utilizează două celule de intrare, B3 și B4.

Când setați această opțiune de calcul, nu au loc calcule de tabel de date atunci când se efectuează o recalculare în tot registrul de lucru. Pentru a recalcula manual tabelul de date, selectați formulele acestuia, apoi apăsați F9.

Urmați acești pași pentru a îmbunătăți performanța calculului:

1. Selectați Opțiuni > fișier > Formule.

2. În secțiunea Opțiuni de calcul , selectați Automat.

   Sfat: Opțional, pe fila Formule , selectați săgeata din Opțiuni de calcul, apoi selectați Automat.

Puteți utiliza alte câteva instrumente Excel pentru a efectua analize circumstanțiale dacă aveți obiective specifice sau seturi mai mari de date variabile.

Căutare rezultat

Dacă știți rezultatul așteptat de la o formulă, dar nu știți exact de ce valoare de intrare are nevoie formula pentru a obține rezultatul respectiv, utilizați caracteristica Goal-Seek. Consultați articolul Utilizarea Căutării rezultatului pentru a găsi rezultatul dorit ajustând o valoare de intrare.

Rezolvitor Excel

Puteți utiliza programul de completare Rezolvitor Excel pentru a găsi valoarea optimă pentru un set de variabile de intrare. Rezolvitorul lucrează cu un grup de celule (numite variabile de decizie sau pur și simplu celule variabile) care sunt utilizate la calculul formulelor din celulele obiectiv și de constrângere. Rezolvitor ajustează valorile din celulele cu variabile de decizie pentru a satisface limitele privind celulele de restricții și a produce rezultatul dorit pentru celula obiectiv. Aflați mai multe în acest articol: Definirea și rezolvarea unei probleme utilizând Rezolvitorul.