tutorialul arată cum se utilizează tabelele de date pentru analiza ce-dacă în Excel. Aflați cum să creați un tabel cu o variabilă și două variabile pentru a vedea efectele uneia sau a două valori de intrare asupra formulei dvs. și cum să configurați un tabel de date pentru a evalua mai multe formule simultan.

ați construit o formulă complexă dependentă de mai multe variabile și doriți să știți cum schimbarea acestor intrări modifică rezultatele., În loc să testați fiecare variabilă individual, faceți un tabel de date de analiză ce-ar fi dacă și observați toate rezultatele posibile cu o privire rapidă!

  • tabel de Date în Excel
  • de a Crea o variabilă tabelul de date
  • Face două variabile tabelul de date
  • tabel de Date pentru a evalua mai multe formule
  • Excel tabele de date – 3 lucruri pe care ar trebui să știi
  • a Șterge un tabel de date în Excel
  • Editare tabel de date rezultate
  • Recalcularea tabelul de date manual

Ce este un tabel de date în Excel?,

în Microsoft Excel, un tabel de date este unul dintre instrumentele de analiză What-If care vă permite să încercați diferite valori de intrare pentru formule și să vedeți cum modificările acestor valori afectează ieșirea formulelor.tabelele de date sunt utile în special atunci când o formulă depinde de mai multe valori și doriți să experimentați diferite combinații de intrări și să comparați rezultatele.în prezent, există un tabel de date variabile și două tabele de date variabile. Deși limitat la maximum două celule de intrare diferite, un tabel de date vă permite să testați câte valori variabile doriți.,

notă. Un tabel de date nu este același lucru ca un tabel Excel, care este destinat gestionării unui grup de date conexe. Dacă doriți să aflați despre multe modalități posibile de a crea, șterge și formata un tabel Excel obișnuit, nu un tabel de date, vă rugăm să consultați acest tutorial: cum să creați și să utilizați un tabel în Excel.

cum se creează un tabel de date variabile în Excel

un tabel de date variabile în Excel permite testarea unei serii de valori pentru o singură celulă de intrare și arată modul în care aceste valori influențează rezultatul unei formule înrudite.,pentru a vă ajuta să înțelegeți mai bine această caracteristică, vom urma un exemplu specific, mai degrabă decât să descriem pașii generici.să presupunem că aveți în vedere depunerea economiilor într-o bancă, care plătește o dobândă de 5% care se compune lunar. Pentru a verifica diferite opțiuni, ați construit următorul calculator de interes compus unde:

  • B8 conține formula FV care calculează soldul de închidere.
  • B2 este variabila pe care doriți să o testați (investiție inițială).,

și acum, să facem o simplă analiză „ce-ar fi dacă” pentru a vedea care vor fi economiile dvs. în 5 ani, în funcție de valoarea investiției inițiale, variind de la 1.000 USD la 6.000 USD.

iată pașii pentru a crea un tabel de date cu o singură variabilă:

  1. introduceți valorile variabile fie într-o coloană, fie pe un rând. În acest exemplu, vom crea un tabel de date orientat pe coloană, așa că tastăm valorile variabile într-o coloană (D3:D8) și lăsăm cel puțin o coloană goală la dreapta pentru rezultate.,
  2. introduceți formula în celulă un rând de mai sus și o celulă în dreapta valorilor variabile (E2 în cazul nostru). Sau conectați această celulă la formula din setul de date original (dacă decideți să modificați formula în viitor, va trebui să actualizați o singură celulă). Am ales a doua opțiune, și introduceți această formulă simplă într-E2: =B8
    Sfat., Dacă doriți să examinați impactul valorilor variabile asupra altor formule care se referă la aceeași celulă de intrare, introduceți formula(formulele) suplimentare din dreapta primei formule, așa cum se arată în acest exemplu.
  3. selectați intervalul tabelului de date, inclusiv formula, celulele cu valori variabile și celulele goale pentru rezultate (D2:E8).,
  4. Du-te la fila Date > grupul Instrumente de Date, faceți clic pe ceea Ce-ar fi Dacă Analiza butonul, și apoi faceți clic pe Tabel de Date…
  5. În Tabelul de Date fereastră de dialog, faceți clic în Coloană celula de Intrare cutie (pentru că Investițiile noastre valori sunt într-o coloană), și selectați variabila de celule referite în formulă. În acest exemplu, selectăm B3 care conține valoarea investiției inițiale.
  6. Faceți clic pe OK, iar Excel va popula imediat celulele goale cu rezultate corespunzătoare valorii variabile din același rând.,
  7. Aplicați formatul de număr dorit rezultatelor (moneda în cazul nostru) și sunteți bine să mergeți!

Acum, puteți lua o privire rapidă la ta-date variabile masă, să examineze posibilele soldurile și de a alege varianta de depozit dimensiune:

Rând de date orientat pe masă

exemplul De mai sus arată cum să înființeze o linie verticală, sau o coloană-orientate, tabel de date în Excel., Dacă preferați un aspect orizontal, iată ce trebuie să faceți:

  1. tastați valorile variabile într-un rând, lăsând cel puțin o coloană goală la stânga (pentru formulă) și un rând gol mai jos (pentru rezultate). Pentru acest exemplu, introducem valorile variabile în celulele F3: J3.
  2. introduceți formula în celula care este o coloană din stânga primei valori variabile și o celulă de mai jos (E4 în cazul nostru).,
  3. Face un tabel de date așa cum sa discutat mai sus, dar intre valoarea de intrare (B3) în Row input cell cutie:
  4. faceți Clic pe OK, și va avea următorul rezultat:

Cum sa faci o două variabile tabelul de date în Excel

două variabile tabelul de date arată cât de diferite combinații de 2 seturi de valori variabile afectează formula rezultat. Cu alte cuvinte, arată cum schimbarea a două valori de intrare ale aceleiași formule schimbă ieșirea.,pașii pentru crearea unui tabel de date cu două variabile în Excel sunt practic aceiași ca în exemplul de mai sus, cu excepția faptului că introduceți două intervale de valori de intrare posibile, unul la rând și altul într-o coloană.pentru a vedea cum funcționează, să folosim același calculator de interes compus și să examinăm efectele dimensiunii investiției inițiale și a numărului de ani asupra soldului. Pentru a face acest lucru, configurați tabelul de date în acest fel:

  1. introduceți formula într-o celulă necompletată sau conectați acea celulă la formula originală., Asigurați-vă că aveți suficiente coloane goale în rândurile din dreapta și goale de mai jos pentru a vă adapta valorile variabile. Ca și înainte, legăm celula E2 la formula originală FV care calculează soldul: =B8
  2. tastați un set de valori de intrare sub formula, în aceeași coloană (valori de investiții în E3:E8).
  3. introduceți celălalt set de valori variabile din dreapta formulei, în același rând (numărul de ani în F2:H2).,

    În acest punct, cele doua variabile din tabelul de date ar trebui să arate similar cu asta:

  4. Selectați întregul tabel de date gamă inclusiv formula, rândul și coloana de valori variabile, iar celulele în care valorile calculate vor apărea. Selectăm intervalul E2: H8.,
  5. de a Crea un tabel de date în cadrul deja familiar: fila Date > Ce-ar fi Dacă Analiza butonul > Tabel de Date…
  6. În Row input cell caseta, introduceți referința la celula de intrare pentru valori variabile în rând (în acest exemplu, e B6 conținând Ani valoare).
  7. în caseta celulă de intrare coloană, introduceți referința la celula de intrare pentru valorile variabile din coloană (B3 care conține valoarea investiției inițiale).
  8. Faceți clic pe OK.,n cazul nostru), și analiza rezultatelor:

Date tabel pentru a compara mai multe rezultate

Dacă doriți pentru a evalua mai mult de o formulă în același timp, a construi tabelul de date așa cum se arată în exemplele anterioare, și introduceți suplimentare formula(s):

  • La dreapta primei formule, în cazul unei verticale tabelul de date organizate în coloane
  • Sub prima formulă în caz de orizontală tabelul de date organizate în rânduri

Pentru „multi-formula” tabel de date pentru a funcționa corect, toate formulele trebuie să se refere la același celula de intrare.,de exemplu, să adăugăm încă o formulă în tabelul nostru de date variabile pentru a calcula dobânda și a vedea cum este afectată de mărimea investiției inițiale. Uite ce vom face:

  1. În celula B10, calcula dobânda cu formula: =B8-B3
  2. Aranja tabelul de date sursa de date cum am făcut-o mai devreme: valori variabile în D3:D8 și E2 legate de B8 (Echilibru formula).,
  3. mai Adaugă o coloană la tabel de date range (coloana F), și link-ul de pe F2 pentru B10 (dobânzi formula):
  4. Selectați extins tabel de date range (D2:F8).
  5. de a Deschide Tabelul de Date caseta de dialog prin clic pe fila Date > Ce-ar fi Dacă Analiza > Tabel de Date…
  6. În Coloana celula de Intrare cutie, alimentare cu celula de intrare (B3), și faceți clic pe OK.,

Iată, acum puteți observa efectele de valori variabile pe ambele formule:

tabel de Date în Excel – 3 lucruri pe care ar trebui să știi

Pentru a utiliza în mod eficient tabele de date în Excel, vă rugăm să păstrați în minte aceste 3 lucruri simple:

  1. Pentru un tabel de date să fie creat cu succes, celula de intrare(s) trebuie să fie pe aceeași foaie de date tabel.,
  2. Microsoft Excel utilizează TABELUL(row_input_cell, colum_input_cell) funcție pentru a calcula tabelul de date rezultate:
    • Într-o variabilă tabelul de date, unul dintre argumente este omis, în funcție de layout (column-oriented sau rând-oriented). De exemplu, în tabelul nostru de date cu o variabilă orizontală, formula este =TABLE(, B3) unde B3 este celula de intrare a coloanei.
    • în tabelul de date cu două variabile, ambele argumente sunt în vigoare. De exemplu, =TABLE(B6, B3) unde B6 este celula de intrare a rândului și B3 este celula de intrare a coloanei.,

    funcția tabelului este introdusă ca o formulă de matrice. Pentru a vă asigura de acest lucru, selectați orice celulă cu valoarea calculată, uitați-vă la bara de formule și notați {paranteze curly} în jurul formulei. Cu toate acestea, nu este o formulă de matrice normală – nu o puteți introduce în bara de formule și nici nu puteți edita una existentă. Este doar „pentru spectacol”.

  3. deoarece rezultatele tabelului de date sunt calculate cu o formulă de matrice, celulele rezultate nu pot fi editate individual. Puteți edita sau șterge întreaga gamă de celule, așa cum este explicat mai jos.,

cum se șterge un tabel de date în Excel

după cum sa menționat mai sus, Excel nu permite ștergerea valorilor din celulele individuale care conțin rezultatele. Ori de câte ori încercați să faceți acest lucru, va apărea un mesaj de eroare „Nu se poate schimba o parte dintr-un tabel de date”.cu toate acestea, puteți șterge cu ușurință întreaga matrice a valorilor rezultate. Iată cum:

  1. În funcție de nevoile dvs., selectați toate celulele tabelului de date sau numai celulele cu rezultatele.
  2. apăsați tasta ștergere.

gata!, 🙂

cum se editează rezultatele tabelelor de date

deoarece nu este posibilă modificarea unei părți dintr-o matrice în Excel, nu puteți edita celule individuale cu valori calculate. Puteți înlocui toate aceste valori numai cu una proprie, efectuând acești pași:

  1. Selectați toate celulele rezultate.
  2. ștergeți formula tabelului din bara de formule.
  3. tastați valoarea dorită și apăsați Ctrl + Enter.,aceasta va insera aceeași valoare în toate celulele selectate:

    odată ce formula tabelului a dispărut, fostul tabel de date devine un interval obișnuit și sunteți liber să editați orice celulă individuală în mod normal.

    cum se recalculează manual tabelul de date

    dacă un tabel mare de date cu valori și formule variabile multiple încetinește Excel-ul dvs., puteți dezactiva recalculările automate în acesta și în toate celelalte tabele de date.,pentru aceasta, accesați fila Formule > grup de calcul, faceți clic pe butonul Opțiuni de calcul, apoi faceți clic pe automat, cu excepția tabelelor de date.

    aceasta va dezactiva calculele automate ale tabelelor de date și va accelera recalculările întregului registru de lucru.

    pentru a recalcula manual tabelul de date, selectați celulele rezultate, adică celulele cu formule TABLE () și apăsați F9.

    acesta este modul în care creați și utilizați un tabel de date în Excel., Pentru a avea o privire mai atentă la exemplele discutate acest acest tutorial, sunteți bineveniți să descărcați exemplul nostru Excel tabele de date workbook. Vă mulțumesc pentru lectură și ar fi fericit să te văd din nou săptămâna viitoare!

    • ce-dacă analiza cu scopul de a căuta în Excel
    • cum se utilizează Solver în Excel: pas cu pas cu exemple
    • cum se grupează rândurile în Excel