Cum se creează un interval dinamic de diagramă în Excel

Când creați o diagramă în Excel și datele sursă se modifică, trebuie să actualizați sursa de date a diagramei 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 un interval dinamic de diagrame.

Ce este un interval dinamic de diagrame?

Un interval dinamic de diagramă este un interval de date care se actualizează automat atunci când schimbați sursa de date.

Acest interval dinamic este apoi utilizat ca sursă de date într-o diagramă. Pe măsură ce datele se modifică, intervalul dinamic se actualizează instantaneu, ceea ce duce la o actualizare a graficului.

Mai jos este un exemplu de grafic care utilizează un interval grafic dinamic.

Rețineți că graficul se actualizează cu noile puncte de date pentru lunile mai și iunie imediat ce datele sunt introduse.

Cum se creează un interval grafic dinamic în Excel?

Există două moduri de a crea un interval grafic dinamic în Excel:

• Utilizarea tabelului Excel
• Utilizarea formulelor

În majoritatea cazurilor, utilizarea tabelului Excel este cel mai bun mod de a crea intervale dinamice în Excel.

Să vedem cum funcționează fiecare dintre aceste metode.

Click aici pentru a descărca fișierul de exemplu.

Utilizarea tabelului Excel

Utilizarea tabelului Excel este cel mai bun mod de a crea intervale dinamice, deoarece se actualizează automat atunci când un nou punct de date este adăugat la acesta.

Funcția Excel Table a fost introdusă în versiunea Excel 2007 de Windows și dacă aveți versiuni anterioare acesteia, nu o veți putea utiliza (consultați următoarea secțiune despre crearea intervalului dinamic de diagrame utilizând formule).

În exemplul de mai jos, puteți vedea că, de îndată ce adaug date noi, tabelul Excel se extinde pentru a include aceste date ca parte a tabelului (observați că marginea și formatarea se extind pentru a le include în tabel).

Acum, trebuie să folosim acest tabel Excel în timpul creării graficelor.

Iată care sunt pașii exacți pentru a crea un grafic dinamic cu linii folosind tabelul Excel:

• Selectați întregul tabel Excel.
• Apăsați fila Insert.
• În Charts Group, selectați graficul ‘Line with Markers’.

Așa este!

Pasii de mai sus ar introduce un grafic cu linii care s-ar actualiza automat atunci când adăugați mai multe date în tabelul Excel.

Rețineți că, în timp ce adăugarea de date noi actualizează automat graficul, ștergerea datelor nu ar 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 semnul albastru din dreapta jos a tabelului Excel pentru a elimina punctele de date șterse din tabel (așa cum se arată mai jos).

În timp ce am luat exemplul unui grafic cu linii, puteți crea și alte tipuri de grafice, cum ar fi graficele cu coloane/barre, folosind această tehnică.

Utilizarea formulelor Excel

După cum am menționat, utilizarea tabelului Excel este cea mai bună modalitate de a crea intervale dinamice de grafice.

Cu toate acestea, dacă nu puteți utiliza tabelul Excel din anumite motive (eventual dacă utilizați Excel 2003), există o altă modalitate (ușor complicată) de a crea intervale dinamice de diagrame utilizând formule Excel și intervale numite.

Să presupunem că aveți setul de date așa cum se arată mai jos:

Pentru a crea un interval dinamic de diagrame din aceste date, trebuie să:

1. Creăm două intervale dinamice numite folosind formula OFFSET (câte unul pentru fiecare coloană ‘Values’ și ‘Months’). Adăugarea/eliminarea unui punct de date ar actualiza automat aceste intervale numite.
2. Inserați un grafic care utilizează intervalele numite ca sursă de date.

Dă-mi voie să explic acum fiecare pas în detaliu.

Pasul 1 – Crearea intervalelor numite dinamice

Iată care sunt pașii pentru a crea intervale numite dinamice:

• Apăsați fila ‘Formulas’.
• Click pe ‘Name Manager’.
• În caseta de dialog Name Manager, specificați numele ca ChartValues și introduceți următoarea formulă în Refers to part: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,”<>”))
• Clic pe OK.
• În caseta de dialog Name Manager, faceți clic pe New.
• În caseta de dialog Name Manager, specificați numele ca fiind ChartMonths și introduceți următoarea formulă în partea Refers to: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,”<>”))
• Click pe Ok.
• Click Close.

Pasii de mai sus au creat două intervale denumite în Workbook – ChartValue și ChartMonth (acestea se referă la intervalul de valori și, respectiv, la intervalul de luni din setul de date).

Dacă mergeți și actualizați coloana de valori adăugând încă un punct de date, intervalul numit ChartValue s-ar actualiza acum automat pentru a afișa punctul de date suplimentar în el.

Magia este făcută de funcția OFFSET aici.

În formula intervalului 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 intervalului numit ChartMonth.

Pasul 2 – Crearea unui grafic folosind aceste intervale numite

Acum tot ce trebuie să faceți este să inserați un grafic care va folosi intervalele numite ca sursă de date.

Iată care sunt pașii pentru a insera o diagramă și a utiliza intervale dinamice de diagrame:

• Accesați fila Insert.
• Click pe ‘Insert Line or Area Chart’ și inserați diagrama ‘Line with markers’. Acest lucru va insera graficul în foaia de lucru.
• Cu graficul selectat, mergeți la fila Design.
• Click pe Select Data.
• În caseta de dialog ‘Select Data Source’, faceți clic pe butonul Add din ‘Legend Entries (Series)’.
• În câmpul Series value, introduceți =Formula!ChartValues (rețineți că trebuie să specificați numele foii de calcul înainte de intervalul numit pentru ca acest lucru să funcționeze).
• Click OK.
• Click pe butonul Edit din ‘Horizontal (Category) Axis Labels’.
• În caseta de dialog ‘Axis Labels’, introduceți =Formula!ChartMonths
• Click OK.

Așa este! Acum graficul dvs. folosește un interval dinamic și se va actualiza atunci când adăugați/eliminați puncte de date în grafic.

Câteva lucruri importante de știut atunci când folosiți intervale numite cu grafice:

• Nu ar trebui să existe celule goale în datele graficului. Dacă există spații goale, intervalul numit nu s-ar referi la setul de date corect (deoarece numărul total ar duce la faptul că se referă la un număr mai mic de celule).
• Trebuie să respectați convenția de denumire atunci când folosiț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 mult de un cuvânt, cum ar fi Formula Chart, atunci trebuie să folosiți =’Formula Chart’!ChartValue.

Vă pot plăcea și următoarele tutoriale Excel:

• Cum să creați un grafic termometru în Excel.
• Cum se realizează o curbă în clopot în Excel.
• Crearea unei diagrame în trepte în Excel.
• Crearea unei diagrame Pareto în Excel.
• Cum se realizează o histogramă în Excel
• Cum se adaugă bare de eroare în Excel (orizontală/verticală/personalizată)

.