Graficele dinamice care utilizează funcția OFFSET și numele intervalelor din Excel se redimensionează atunci când se adaugă sau se șterg date. Spre deosebire de utilizarea tabelelor pentru diagrame dinamice, această metodă este compatibilă cu versiunile anterioare ale Excel.
Utilizați funcțiile OFFSET și COUNTA din Excel pentru a crea nume de intervale dinamice care se ajustează automat pe măsură ce se adaugă sau se șterg date.
Click pentru a descărca fișierul tutorial de exemplu privind redimensionarea dinamică a diagramelor utilizând OFFSET și nume de intervale. Exemplul include atât date orizontale, cât și verticale.
Metoda OFFSET și nume de interval necesită mai multă muncă decât cele două clicuri folosite pentru un Tabel, dar vă oferă control asupra a tot ceea ce ține de datele din grafic, așa cum arată cartea mea „Balanced Scorecards and Operational Dashboards with Microsoft Excel”. Acest site, CriticalToSuccess.com, are exemple de alte modalități de utilizare a OFFSET, de exemplu, crearea unui grafic cu defilare, controlul graficelor cu meniuri derulante și multe altele. Căutați CriticalToSuccess.com folosind termenul „OFFSET” sau „dynamic range” pentru a vedea exemple.
Crearea unei diagrame statice
Începeți diagrama dvs. dinamică creând mai întâi o diagramă statică, după cum urmează,
1. Faceți clic în interiorul intervalului B7:D12, astfel încât Excel să știe unde se află datele graficului.
2. În fila Insert, în grupul Charts, selectați un grafic simplu, cum ar fi un grafic de coloane, de bare sau de linii.
Mutați acest grafic astfel încât să aveți spațiu pentru a adăuga date sub intervalul de date.
Crearea de nume de intervale dinamice cu OFFSET
Diferitele versiuni ale Excel au metode diferite de creare și editare a formulelor de nume de intervale. Următoarele instrucțiuni sunt pentru Excel 2007, 2010 și 2013.
1. În fila Formulas, în grupul Defined Name, faceți clic pe Define Name. Apare caseta Define Name.
Crearea de nume de intervale în caseta Define Name.
2. În caseta de text Name, introduceți rngVDate. Convențiile de denumire sunt,
rng Range Name
V Orientarea verticală a datelor în B7:B16
Date Tipul de date din interval
3. Scrieți un comentariu în căsuța text Comment care vă va ajuta pe dumneavoastră sau pe alții să știți la ce se referă acest nume de interval.
4. În caseta Refers to, scrieți formula OFFSET care calculează intervalul de date Date.
=OFFSET(wrksht!$B$7,0,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Faceți clic pe Ok.
Cu această formulă OFFSET puteți introduce date suplimentare oriunde în intervalul B7:B16, atâta timp cât noile date se află în partea de jos a vechii liste de date, fără celule goale între date.
Acum, repetați același proces pentru a crea nume de intervale pentru datele Sales și Units.
Numele și formulele pe care le veți adăuga sunt,
Data Range Name Refers to
Sales rngVSales =OFFSET(wrksht!$C$7,0,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Units rngVUnits =OFFSET(wrksht!$D$7,0,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Click aici pentru a afla cum calculează OFFSET dimensiunea numelui intervalului dinamic. Acest articol vă oferă, de asemenea, sfaturi privind verificarea numelor intervalelor dinamice.
Click aici pentru a afla greșeala pe care nu doriți să o faceți niciodată cu OFFSET. Faceți această greșeală?
Click pentru a descărca fișierul tutorial de exemplu privind redimensionarea dinamică a graficelor folosind OFFSET și numele intervalelor. Exemplul include atât date orizontale, cât și verticale.
Inserarea unui nume de interval în formula de date a graficului
Acum puteți face ca graficele dumneavoastră să fie dinamice prin inserarea numelor de intervale pe care le-ați creat în formulele pe care graficele le folosesc pentru a face referire la date. Nu vă faceți griji. Acest lucru este foarte simplu. De fapt, faptul că învățați cum să faceți acest lucru vă oferă abilități mistice și puterea de a îndoi orice grafic după voința dumneavoastră. (De asemenea, vă permite să reparați graficele care se încurcă.)
Deasupra, ați creat trei nume de intervale dinamice care fac referință la datele care urmează să fie utilizate de grafic,
rngVDate
rngVSales
rngVUnits
Cele de mai jos înlocuiesc referințele celulelor utilizate de grafic cu numele de intervale dinamice pe care le-ați creat,
1. Selectați graficul făcând clic pe marginea acestuia.
2. În fila Chart Tools Design, în grupul Data, faceți clic pe instrumentul Select Data pentru a afișa caseta de dialog Select Data Source.
Casa de dialog Select Data Source vă oferă un control magic asupra datelor pe care le utilizează un grafic.
3. Sub Horizontal (Category) Axis Labels, faceți clic pe Edit pentru a afișa caseta Axis Labels. Este greu de văzut formula în această casetă de dialog, așa că faceți clic pe pictograma selectorului din dreapta referinței celulei. Aceasta afișează o casetă Axis Labels (Etichete axă) mai largă, în care puteți face referire la formulă. ATENȚIE înainte de a edita!
Căsuța Axis Labels arată referința pentru axa orizontală.
4. ATENȚIE! Înainte de a încerca să editați formula, faceți clic în interiorul formulei și apăsați tasta Edit, F2, astfel încât să puteți edita formula. Apăsați tasta End pentru a merge la sfârșitul formulei. Înlocuiți referințele de celulă de după semnul ! cu numele intervalului, așa cum se arată în această imagine.
Formula dumneavoastră ar trebui să arate acum ca,
=’Dynamically Resize Charts to Data.xlsx’!rngVDate
Căsuța Axis Labels arată așa după ce ați apăsat F2 și ați editat formula pentru a include numele intervalului Date.
TIP: Dacă faceți o greșeală la editarea formulei, apăsați Ctrl+Z (Undo), pentru a vă întoarce înapoi.
5. Faceți clic pe Ok pentru a reveni la caseta de dialog Select Data Source (Selectare sursă de date), astfel încât să puteți adăuga numele Sales (Vânzări) și Units (Unități).
Inserarea unui nume de interval în formula Sales (Vânzări) și Units (Unități) a graficului
Acum, înlocuiți formulele axelor Sales (Vânzări) și Units (Unități) cu nume de intervale folosind aproape același proces.
1. În caseta de dialog Select Data Source (Selectare sursă de date), faceți clic pe Sales (Vânzări) și pe butonul Edit (Editare) pentru a afișa caseta de dialog Edit Series (Editare serie).
Înlocuiți referința celulei Salesdata cu numele intervalului acesteia.
2. Faceți clic pe pictograma selector din dreapta formulei Series Value (Valoare serie) care face referire la seria Sales (Vânzări).
3. Faceți clic în interiorul formulei, apăsați F2 pentru editare și schimbați referințele celulelor pentru datele Sales cu numele intervalului, rngVSales.
Graficul folosește acum numele dinamic al intervalului pentru a face referire la datele Sales.
.