Când aveți o formulă complexă care depinde de mai multe variabile și doriți să înțelegeți modul în care modificarea acestor intrări afectează rezultatele în mod eficient, un tabel de date de analiză What-If în Excel este un instrument puternic. Vă permite să vedeți toate rezultatele posibile cu o privire rapidă. Iată un ghid pas cu pas despre cum să creați un tabel de date în Excel. În plus, vom discuta câteva puncte cheie pentru utilizarea eficientă a tabelelor de date și vom demonstra alte operațiuni, cum ar fi ștergerea, editarea și recalcularea tabelelor de date.

Ce este tabelul de date în Excel?

Creați un tabel de date cu o singură variabilă

• Tabel de date orientat pe coloane
• Tabel de date orientat pe rând
• Formule multiple pentru tabelul de date cu o singură variabilă

Creați un tabel de date cu două variabile

Puncte cheie folosind tabelele de date

Alte operațiuni pentru utilizarea tabelelor de date

• Ștergeți un tabel de date
• Editați rezultatul tabelului de date
• Recalculați manual tabelul de date

Ce este tabelul de date în Excel?

În Excel, un tabel de date este unul dintre instrumentele de analiză What-If care vă permite să experimentați cu diferite valori de intrare pentru formule și să observați modificările în rezultatul formulei. Acest instrument este incredibil de util pentru a explora diferite scenarii și pentru a efectua analize de sensibilitate, mai ales atunci când o formulă depinde de mai multe variabile.

Există două tipuri de tabele de date în Excel:

Tabel de date cu o singură variabilă: Acest lucru vă permite să analizați modul în care diferitele valori ale unei variabile vor afecta o formulă. Puteți configura un tabel de date cu o singură variabilă, fie într-un format orientat pe rând, fie pe coloană, în funcție de dacă doriți să variați intrarea pe un rând sau în jos pe o coloană.

Tabel de date cu două variabile: Acest tip vă permite să vedeți impactul modificării a două variabile diferite asupra rezultatului unei formule. Într-un tabel de date cu două variabile, variați valorile atât de-a lungul unui rând, cât și de-a lungul unei coloane.

Creați un tabel de date cu o singură variabilă

Crearea unui tabel de date cu o singură variabilă în Excel este o abilitate valoroasă pentru analizarea modului în care modificările dintr-o singură intrare pot afecta diferite rezultate. Această secțiune vă va ghida prin procesul de creare a tabelelor de date cu o variabilă orientate pe coloane, pe rând și cu formule multiple.

Uimitor! Utilizarea filelor eficiente în Excel, cum ar fi Chrome, Edge, Firefox și Safari!
Economisiți 50% din timp și reduceți mii de clicuri de mouse pentru dvs. în fiecare zi!

---

Tabel de date orientat pe coloane

Un tabel de date orientat pe coloană este util atunci când doriți să testați modul în care diferitele valori ale unei singure variabile influențează rezultatul unei formule, cu valorile variabilelor enumerate pe o coloană. Să luăm în considerare un exemplu financiar simplu:

Să presupunem că aveți în vedere un împrumut de 50,000 USD, pe care intenționați să-l rambursați pe o perioadă de 3 ani (echivalentul a 36 de luni). Pentru a evalua ce plată lunară ar fi accesibilă pe baza salariului dvs., sunteți interesat să explorați modul în care diferitele rate ale dobânzii ar afecta suma pe care trebuie să o plătiți în fiecare lună.

Pasul 1: Configurați formula de bază

Pentru a calcula plata, aici, voi seta dobânda la 5%. Într-o celulă B4, introduceți formula PMT, aceasta calculează plata lunară pe baza ratei dobânzii, a numărului de perioade și a sumei împrumutului. Vedeți captura de ecran:

= -PMT($B$1/12, $B$2*12, $B$3)

Pasul 2: enumerați ratele dobânzii într-o coloană

Într-o coloană, enumerați diferitele rate ale dobânzii pe care doriți să le testați. De exemplu, enumerați valorile de la 4% la 11% în trepte de 1% într-o coloană și lăsați cel puțin o coloană goală la dreapta pentru rezultate. Vedeți captura de ecran:

Pasul 3: Creați tabelul de date

1. În celula E2, introduceți această formulă: = B4.
   
   notițe: B4 este celula în care se află formula principală, aceasta este formula ale cărei rezultate doriți să le vedeți să se schimbe pe măsură ce variați rata dobânzii.
   
2. Selectați intervalul care include formula dvs., lista de rate ale dobânzii și celulele adiacente pentru rezultate (de exemplu, selectați D2 până la E10). Vedeți captura de ecran:
   
3. Mergeți la panglică, faceți clic pe Date fila, apoi faceți clic pe Analiza What-If > Tabel de date, vezi captura de ecran:
   
4. În Tabel de date caseta de dialog, faceți clic în Coloană Celulă de intrare caseta (deoarece folosim o coloană pentru valorile de intrare) și selectați celula variabilă la care se face referire în formula dvs. În acest exemplu, selectăm B1 care conține rata dobânzii. În sfârșit, dați clic OK buton, vezi captura de ecran:
   
5. Excel va completa automat celulele goale adiacente fiecăreia dintre valorile dvs. variabile (rate ale dobânzii diferite) cu rezultatele corespunzătoare. Vedeți captura de ecran:
   
6. Aplicați formatul numeric dorit la rezultate (Monedă) în funcție de nevoile dvs. Acum, tabelul de date orientat pe coloane este creat cu succes și puteți citi rapid rezultatele pentru a evalua ce sume de plată lunară ar fi accesibile pentru dvs. atunci când rata dobânzii se modifică. Vedeți captura de ecran:
   

Tabel de date orientat pe rând

Crearea unui tabel de date orientat pe rând în Excel implică aranjarea datelor astfel încât valorile variabilelor să fie listate pe un rând, mai degrabă decât pe o coloană. Luând ca referință exemplul de mai sus, să continuăm cu pașii pentru a finaliza crearea unui tabel de date orientat pe rând în Excel.

Pasul 1: enumerați ratele dobânzilor pe rând

Plasați valorile variabilelor (ratele dobânzii) într-un rând, asigurându-vă că există cel puțin o coloană goală în stânga pentru formulă și un rând gol mai jos pentru rezultate, vedeți captura de ecran:

Pasul 2: Creați tabelul de date

1. În celula A9, introduceți această formulă: = B4.
   
2. Selectați intervalul care include formula dvs., lista de rate ale dobânzii și celulele adiacente pentru rezultate (de exemplu, selectați A8 până la I9). Apoi apasa Date > Analiza What-If > Tabel de date.
3. În Tabel de date caseta de dialog, faceți clic în Celulă de intrare în rând casetă (deoarece folosim un rând pentru valorile de intrare) și selectați celula variabilă la care se face referire în formula dvs. În acest exemplu, selectăm B1 care conține rata dobânzii. În sfârșit, dați clic OK buton, vezi captura de ecran:
   
4. Aplicați formatul numeric dorit la rezultate (Monedă) în funcție de nevoile dvs. Acum, tabelul de date orientat pe rând este creat, vezi captura de ecran:
   

Formule multiple pentru tabelul de date cu o singură variabilă

Crearea unui tabel de date cu o singură variabilă cu mai multe formule în Excel vă permite să vedeți cum modificarea unei singure intrări afectează mai multe formule diferite simultan. În exemplul de mai sus, ce se întâmplă dacă doriți să vedeți modificarea ratelor dobânzii atât pentru rambursări, cât și pentru dobânda totală? Iată cum să-l configurați:

Pasul 1: Adăugați o nouă formulă pentru a calcula dobânda totală

În celula B5, introduceți următoarea formulă pentru a calcula dobânda totală:

=B4*B2*12-B3

Pasul 2: Aranjați datele sursă ale tabelului de date

Într-o coloană, enumerați diferitele rate ale dobânzii pe care doriți să le testați și lăsați cel puțin două coloane goale la dreapta pentru rezultate. Vedeți captura de ecran:

Pasul 3: Creați tabelul de date:

1. În celula E2, introduceți această formulă: = B4 pentru a crea o referință la calculul rambursării în datele originale.
2. În celula F2, introduceți această formulă: = B5 pentru a crea o referință la interesul total pentru datele originale.
   
3. Selectați intervalul care include formulele dvs., lista de rate ale dobânzii și celulele adiacente pentru rezultat (de exemplu, selectați D2 până la F10). Apoi apasa Date > Analiza What-If > Tabel de date.
4. În Tabel de date caseta de dialog, faceți clic în Celula de intrare a coloanei caseta (deoarece folosim o coloană pentru valorile de intrare) și selectați celula variabilă la care se face referire în formula dvs. În acest exemplu, selectăm B1 care conține rata dobânzii. În sfârșit, dați clic OK buton, vezi captura de ecran:
   
5. Aplicați formatul numeric dorit la rezultate (Monedă) în funcție de nevoile dvs. Și puteți vedea rezultatele pentru fiecare formulă pe baza diferitelor valori ale variabilelor.
   

Creați un tabel de date cu două variabile

Un tabel de date cu două variabile în Excel afișează impactul diferitelor combinații a două seturi de valori variabile asupra rezultatului unei formule, demonstrând modul în care modificările în două intrări ale unei formule afectează simultan rezultatul acesteia.

Aici, am desenat o schiță simplă pentru a vă ajuta să înțelegeți mai bine aspectul și structura unui tabel de date cu două variabile.

Pe baza exemplului de a crea un tabel de date cu o singură variabilă, să continuăm acum să învățăm cum să creați un tabel de date cu două variabile în Excel.

În setul de date de mai jos, avem rata dobânzii, termenul împrumutului și suma împrumutului și am calculat plata lunară utilizând această formulă: =-PMT($B$1/12, $B$2*12, $B$3) de asemenea. Aici, ne vom concentra pe două variabile cheie din datele noastre: rata dobânzii și suma împrumutului, pentru a observa modul în care modificările acestor factori afectează simultan sumele de rambursare.

Pasul 1: Configurați cele două variabile care se schimbă

1. Într-o coloană, enumerați diferitele rate ale dobânzii pe care doriți să le testați. vezi captura de ecran:
   
2. Într-un rând, introduceți diferitele valori ale sumei împrumutului chiar deasupra valorilor coloanei (începând de la o celulă la dreapta celulei formulei), vedeți captura de ecran:
   

Pasul 2: Creați tabelul de date

1. Plasați formula la intersecția rândului și coloanei în care ați enumerat valorile variabilelor. În acest caz, voi introduce această formulă: = B4 în celula E2. Vedeți captura de ecran:
   
2. Selectați intervalul care include sumele împrumutului, ratele dobânzilor, celula formulei și celulele în care vor fi afișate rezultatele.
   
3. Apoi apasa Date > Analiza What-If > Tabel de date. În caseta de dialog Tabel de date:
   • În Celulă de intrare în rând caseta, selectați referința celulei la celula de intrare pentru valorile variabile din rând (în acest exemplu, B3 conține suma împrumutului).
   • În Coloana de intrare cell, selectați referința celulei la celula de intrare pentru valorile variabile din coloană (B1 conține rata dobânzii).
   • Apoi, faceți clic OK Buton.
     
4. Acum, Excel va completa tabelul de date cu rezultatele pentru fiecare combinație de suma împrumutului și rata dobânzii. Oferă o vedere directă a modului în care diferite combinații de sume de împrumut și rate ale dobânzii afectează plata lunară, făcându-l un instrument valoros pentru planificarea și analiza financiară.
   
5. În cele din urmă, ar trebui să aplicați formatul numeric dorit la rezultate (Moneda) în funcție de nevoile dvs.
   

Puncte cheie folosind tabelele de date

• Tabelul de date nou creat trebuie să fie în aceeași foaie de lucru cu datele originale.
• Ieșirea tabelului de date depinde de celula formulei din setul de date sursă și orice modificare a acestei celule de formulă va actualiza automat rezultatul.
• Odată ce valorile au fost calculate folosind tabelul de date, acestea nu pot fi anulate Ctrl + Z. Cu toate acestea, puteți selecta manual toate valorile și le puteți șterge.
• Tabelul de date generează formule matrice, astfel încât celulele individuale din tabel nu pot fi modificate sau șterse.

Alte operațiuni pentru utilizarea tabelelor de date

Odată ce ați creat un tabel de date, este posibil să aveți nevoie de operațiuni suplimentare pentru a-l gestiona eficient, inclusiv ștergerea tabelului de date, modificarea rezultatelor acestuia și efectuarea de recalculări manuale.

Ștergeți un tabel de date

Deoarece rezultatele tabelului de date sunt calculate cu o formulă matrice, nu puteți șterge o valoare individuală din tabelul de date. Cu toate acestea, puteți șterge doar întregul tabel de date.

Selectați toate celulele din tabelul de date sau numai celulele cu rezultatele, apoi apăsați Șterge tasta de pe tastatură.

Editați rezultatul tabelului de date

Într-adevăr, nu este posibilă editarea directă a celulelor individuale dintr-un tabel de date, deoarece acestea conțin formule matrice pe care Excel le generează automat.

Pentru a face modificări, ar trebui de obicei să ștergeți tabelul de date existent și apoi să creați unul nou cu modificările dorite. Aceasta implică ajustarea formulei de bază sau a valorilor de intrare și apoi configurarea din nou a tabelului de date pentru a reflecta aceste modificări.

Recalculați manual tabelul de date

În mod normal, Excel recalculează toate formulele din toate registrele de lucru deschise de fiecare dată când se face o modificare. Dacă un tabel mare de date care conține numeroase valori variabile și formule complexe provoacă încetinirea registrului de lucru Excel.

Pentru a evita ca Excel să efectueze automat calcule pentru toate tabelele de date de fiecare dată când se face o modificare în registrul de lucru, puteți comuta modul de calcul de la Automat la Manual. Vă rugăm să faceți după cum urmează:

Du-te la Formule , apoi faceți clic pe Opțiuni de calcul > Automat, cu excepția tabelelor de date, vezi captura de ecran:

Odată ce modificați această setare, atunci când vă recalculați întregul registru de lucru, Excel nu va mai actualiza automat calculele din tabelele de date.

Dacă trebuie să actualizați manual tabelul de date, trebuie doar să selectați celulele în care sunt afișate rezultatele (celule care conțin formulele TABLE()) și apoi apăsați F9 cheie.

Urmând pașii prezentați în acest ghid și ținând cont de punctele cheie, puteți utiliza eficient tabelele de date pentru nevoile dvs. de analiză a datelor. Dacă sunteți interesat să explorați mai multe sfaturi și trucuri Excel, site-ul nostru oferă mii de tutoriale, vă rugăm să faceți clic aici pentru a le accesa. Vă mulțumim pentru citit și așteptăm cu nerăbdare să vă oferim mai multe informații utile în viitor!