Când creați o diagramă în Excel și datele sursă se modifică, trebuie să actualizați sursa de date a graficului pentru a vă asigura că reflectă noile date.
În cazul în care lucrați cu diagrame care sunt actualizate frecvent, este mai bine să creați o gamă dinamică de diagrame.
Ce este o gamă de diagrame dinamice?
Un interval de diagrame dinamice este un interval de date care se actualizează automat atunci când schimbați sursa de date.
Acest interval dinamic este apoi utilizat ca date sursă într-un grafic. Pe măsură ce datele se modifică, gama dinamică se actualizează instantaneu, ceea ce duce la o actualizare în grafic.
Mai jos este un exemplu de diagramă care utilizează un interval de grafice dinamic.
Rețineți că graficul se actualizează cu noile puncte de date pentru mai și iunie de îndată ce datele introduse.
Cum se creează o gamă dinamică de diagrame în Excel?
Există două moduri de a crea un interval de diagrame dinamice în Excel:
- Folosind tabelul Excel
- Folosirea formulelor
În majoritatea cazurilor, utilizarea tabelului Excel este cea mai bună modalitate de a crea intervale dinamice în Excel.
Să vedem cum funcționează fiecare dintre aceste metode.
Faceți clic aici pentru a descărca fișierul de exemplu.
Folosind tabelul Excel
Utilizarea Tabelului Excel este cel mai bun mod de a crea intervale dinamice, deoarece se actualizează automat atunci când i se adaugă un nou punct de date.
Funcția Tabel Excel a fost introdusă în versiunea Excel 2007 a Windows și, dacă sunteți versiuni anterioare, nu o veți putea folosi (consultați următoarea secțiune despre crearea intervalului de diagrame dinamice folosind formule).
Sfat Pro: Pentru a converti o gamă de celule într-un tabel Excel, selectați celulele și utilizați comanda rapidă de la tastatură - Control + T (țineți apăsată tasta Control și apăsați tasta T).În exemplul de mai jos, puteți vedea că imediat ce adaug date noi, tabelul Excel se extinde pentru a include aceste date ca parte a tabelului (rețineți că marginea și formatarea se extind pentru a le include în tabel).
Acum, trebuie să folosim acest tabel Excel în timp ce creăm diagramele.
Iată pașii exacți pentru a crea o diagramă liniară dinamică folosind tabelul Excel:
- Selectați întregul tabel Excel.
- Accesați fila Inserare.
- În grupul de diagrame, selectați diagrama „Linie cu markere”.
Asta e!
Pașii de mai sus ar insera o diagramă liniară care se va actualiza automat atunci când adăugați mai multe date în tabelul Excel.
Rețineți că, în timp ce adăugați date noi, actualizați automat graficul, ștergerea datelor nu va elimina complet punctele de date. De exemplu, dacă eliminați 2 puncte de date, graficul va afișa un spațiu gol în dreapta. Pentru a corecta acest lucru, trageți marca albastră în partea dreaptă jos a tabelului Excel pentru a elimina punctele de date șterse din tabel (așa cum se arată mai jos).
Deși am luat exemplul unei diagrame liniare, puteți crea și alte tipuri de diagrame, cum ar fi diagrame coloane / bare, utilizând această tehnică.
Utilizarea formulelor Excel
După cum am menționat, utilizarea tabelului Excel este cea mai bună modalitate de a crea intervale dinamice ale graficelor.
Cu toate acestea, dacă nu puteți utiliza tabelul Excel dintr-un anumit motiv (posibil dacă utilizați Excel 2003), există un alt mod (ușor complicat) de a crea intervale de diagrame dinamice utilizând formule Excel și intervale denumite.
Să presupunem că aveți setul de date așa cum se arată mai jos:
Pentru a crea un grafic dinamic din aceste date, trebuie să:
- Creați două intervale denumite dinamice utilizând formula OFFSET (câte una pentru coloanele „Valori” și „Luni”). Adăugarea / ștergerea unui punct de date ar actualiza automat aceste intervale denumite.
- Introduceți o diagramă care utilizează intervalele denumite ca sursă de date.
Permiteți-mi să explic fiecare pas în detaliu acum.
Pasul 1 - Crearea gamelor dinamice numite
Mai jos sunt pașii pentru a crea intervale denumite dinamice:
- Accesați fila „Formule”.
- Faceți clic pe „Manager de nume”.
- În caseta de dialog Manager nume, specificați numele ca ChartValues și introduceți următoarea formulă în Se referă la parte: = OFFSET (Formula! $ B $ 2 ,,, COUNTIF (Formula! $ B $ 2: $ B $ 100, ””))
- Faceți clic pe OK.
- În caseta de dialog Manager nume, faceți clic pe Nou.
- În caseta de dialog Manager nume, specificați numele ca ChartMonths și introduceți următoarea formulă în Se referă la parte: = OFFSET (Formula! $ A $ 2 ,,, COUNTIF (Formula! $ A $ 2: $ A $ 100, ””))
- Faceți clic pe Ok.
- Faceți clic pe Închidere.
Pașii de mai sus au creat două intervale denumite în Registrul de lucru - ChartValue și ChartMonth (acestea se referă la valorile și intervalul lunilor din setul de date, respectiv).
Dacă mergeți și actualizați coloana valoare adăugând încă un punct de date, intervalul numit ChartValue se va actualiza automat pentru a afișa punctul de date suplimentar în acesta.
Magia se face aici prin funcția OFFSET.
În formula de interval numită „ChartValue”, am specificat B2 ca punct de referință. Formula OFFSET începe acolo și se extinde pentru a acoperi toate celulele umplute din coloană.
Aceeași logică funcționează și în formula de interval numită ChartMonth.
Pasul 2 - Creați o diagramă folosind aceste game numite
Acum tot ce trebuie să faceți este să inserați o diagramă care va utiliza intervalele denumite ca sursă de date.
Iată pașii pentru inserarea unei diagrame și utilizarea intervalelor dinamice ale diagramei:
- Accesați fila Inserare.
- Faceți clic pe „Inserare diagramă linie sau zonă” și introduceți graficul „Linie cu markere”. Aceasta va insera graficul în foaia de lucru.
- Cu graficul selectat, accesați fila Proiectare.
- Faceți clic pe Selectare date.
- În caseta de dialog „Selectați sursa de date”, faceți clic pe butonul Adăugați în „Legenda intrărilor (serie)”.
- În câmpul Valoare serie, introduceți = Formula! ChartValues (rețineți că trebuie să specificați numele foii de lucru înainte de intervalul numit pentru ca acesta să funcționeze).
- Faceți clic pe OK.
- Faceți clic pe butonul Editați din „Etichete axale orizontale (categorii)”.
- În caseta de dialog „Etichete axiale”, introduceți = Formula! ChartMonths
- Faceți clic pe Ok.
Asta e! Acum, graficul dvs. utilizează un interval dinamic și se va actualiza când adăugați / ștergeți puncte de date în grafic.
Câteva lucruri importante de știut atunci când se utilizează intervale numite cu diagrame:
- Nu ar trebui să existe celule goale în datele graficului. Dacă există un spațiu necompletat, intervalul numit nu se va referi la setul de date corect (deoarece numărul total ar duce la referirea la un număr mai mic de celule).
- Trebuie să urmați convenția de denumire atunci când utilizați numele foii în sursa graficului. De exemplu, dacă numele foii este un singur cuvânt, cum ar fi Formula, atunci puteți utiliza = Formula! ChartValue. Dar dacă există mai multe cuvinte, cum ar fi Formula Chart, atunci trebuie să utilizați = „Formula Chart”! ChartValue.
