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ă de tip „ce ar fi dacă”. Atunci când construiți și analizați tabele de date, faceți analiza de tip „ce ar fi dacă”.

Analiza de tip „ce ar fi dacă” este procesul de schimbare a valorilor din celule pentru a vedea cum aceste modificări vor afecta rezultatele formulelor în 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 ce ar putea fi de plată lunar.

Tipuri de analiză de tip „ce ar fi dacă”    

Există trei tipuri de instrumente de analiză de tip „ce ar fi dacă” î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 distinct 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 singură foaie de lucru. Utilizarea tabelelor de date vă ajută să examinați o serie de posibilități dintr-o privire. Î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, puteți utiliza Scenarii. Deși este limitat la doar 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 Analiza de tip „ce ar fi dacă”.

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 singură variabilă pentru a vedea modul în care diferite rate ale dobânzii afectează rata ipotecară lunară, 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 a două variabile dintr-o singură 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 o rată ipotecară lunară.

Î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 în tabelul de date    

Ori de câte ori o foaie de lucru se recalculează, toate tabelele de date se vor recalcula și ele, 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 pe o singură coloană (orientat pe coloane), fie pe un rând (orientat pe rânduri). 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 pe verticală într-o coloană, fie pe orizontală într-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 pe coloane (valorile variabile sunt într-o coloană), tastați formula în celula aflată cu un rând mai sus și cu o celulă mai la dreapta coloanei de valori. Acest tabel de date cu o variabilă este orientat pe coloane, iar formula este conținută în celula D2.
     
     
     
     Dacă doriți să analizați efectele diferitelor valori asupra altor 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 pe un rând), tastați formula în celula cu o coloană mai la stânga primei valori și cu o celulă dedesubtul rândului cu valori.
     
     Dacă doriți să analizați efectele diferitelor valori asupra altor formule, introduceți formulele suplimentare în celulele de dedesubtul primei formule.

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, aceasta zonă este C2:D5.

4. Pe fila Date, selectați Analiză de tip „ce ar fi dacă” Tabel de date (în grupul Instrumente de date sau în grupul Prognoză din Excel 2016).

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

   • Dacă tabelul de date este orientat pe coloane, 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 pe rânduri, 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 cu 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. Faceți una dintre următoarele:

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

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

2. Selectați zona de celule care conține tabelul de date și noua formulă.

3. Pe fila Date, selectați Analiză de tip „ce ar fi dacă”> Tabel de date (în grupul Instrumente de date sau în grupul Prognoză din Excel 2016).

4. Alegeți una dintre următoarele:

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

   • Dacă tabelul de date este orientat pe rânduri, introduceți referința de celulă pentru celula de intrare în caseta celulei 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 diferitele rate ale dobânzii în celulele C3, C4 și C5.

3. Introduceți a doua listă pe 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), precum ș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ă de tip „ce ar fi dacă” > Tabel de date (în grupul Instrumente de date sau grupul Prognoză din 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 în 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 dintre ratele dobânzii și condițiile de împrumut vor afecta rata ipotecară lunară. Î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 în tabelul 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. Faceți clic pe Fișier > Opțiuni > 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 o analiză de tip „ce ar fi dacă”, în cazul în care 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 Căutare rezultat. Consultați articolul Utilizați Căutare rezultat pentru a găsi rezultatul dorit, ajustând o valoare de intrare.

Caracteristică de soluționare Excel

Puteți utiliza programul de completare Caracteristică de soluționare Excel pentru a găsi valoarea optimă pentru un set de variabile de intrare. Caracteristica de soluționare lucrează cu un grup de celule (denumite variabile de decizie sau, mai simplu, celule variabile), care participă la calculul formulelor din celulele obiectiv și de restricție. 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: Definiți și rezolvați o problemă utilizând Caracteristica de soluționare.