Cum se creează o gamă dinamică de diagrame în Excel

Acest tutorial va demonstra cum să creați o gamă de diagrame dinamice în toate versiunile de Excel: 2007, 2010, 2013, 2016 și 2022.

În mod implicit, atunci când extindeți sau contractați un set de date utilizat pentru trasarea unei diagrame în Excel, datele sursă subiacente trebuie, de asemenea, să fie ajustate manual.

Cu toate acestea, creând intervale dinamice ale graficelor, puteți evita această problemă.

Intervalele grafice dinamice vă permit să actualizați automat datele sursă de fiecare dată când adăugați sau eliminați valori din intervalul de date, economisind mult timp și efort.

În acest tutorial, veți învăța tot ce trebuie să știți pentru a dezlănțui puterea Intervalele grafice dinamice.

Gamele dinamice ale diagramelor - Introducere

Luați în considerare următorul exemplu de set de date care analizează fluctuațiile marjei de profit:

Practic, există două modalități de a configura un interval de diagrame dinamice:

1. Conversia intervalului de date într-un tabel
2. Folosind intervale denumite dinamice ca date sursă ale graficului.

Ambele metode au avantajele și dezavantajele lor, așa că vom vorbi despre fiecare dintre ele mai detaliat pentru a vă ajuta să determinați care va funcționa cel mai bine pentru dvs.

Fără alte întrebări, să începem.

Metoda tabelului

Permiteți-mi să încep prin a vă arăta cel mai rapid și mai simplu mod de a îndeplini sarcina la îndemână. Deci, iată exercițiul: transformați intervalul de date într-un tabel și sunteți mai ușor de aur decât decojirea mazărei.

În acest fel, tot ce introduceți în celule la sfârșitul acelui tabel va fi inclus automat în datele sursă ale graficului.

Iată cum puteți face acest lucru în doar doi pași simpli.

Pasul 1: convertiți intervalul de date într-un tabel.

Chiar în afara porții, transformați gama de celule care conține datele grafice într-un tabel.

1. Evidențiați întregul interval de date (A1: B6).
2. Apasă pe Introduce filă.
3. Loveste "Masa”Buton.

În Creați tabel caseta de dialog, procedați în felul următor:

1. Verificați dacă intervalul de celule evidențiat se potrivește cu întregul tabel de date.
2. Dacă tabelul dvs. nu conține rând de antet, debifați „Masa mea are anteturi" cutie.
3. Faceți clic pe „BINE.“

Ca urmare, ar trebui să ajungeți la acest tabel:

Pasul 2: Creați un grafic pe baza tabelului.

Fundația a fost pusă, ceea ce înseamnă că acum puteți configura o diagramă folosind tabelul.

1. Evidențiați întregul tabel (A1: B6).
2. Navigați la Introduce filă.
3. Creați orice diagramă 2-D. În scop ilustrativ, să configurăm o diagramă simplă a coloanelor (Introduceți o coloană sau o diagramă de bare> Coloană grupată).

Asta e! Pentru a testa tehnica, încercați să adăugați noi puncte de date în partea de jos a mesei pentru a le vedea automat graficate pe complot. Cu cât poate fi mai simplu?

NOTĂ: Cu această abordare, setul de date ar trebui nu conține celule goale în el - care vor distruge graficul.

Metoda Dynamic Named Range

Deși ușor de aplicat, demonstrațiile anterioare, Metoda tabelului are unele contra serioase. De exemplu, graficul este încurcat ori de câte ori setul de date nou ajunge să fie mai mic decât tabelul de date inițial plus, uneori pur și simplu nu doriți ca intervalul de date să fie convertit într-un tabel.

Alegerea intervalelor denumite poate necesita ceva mai mult timp și efort din partea dvs., dar tehnica neagă dezavantajele metodei de masă și, pe deasupra, face ca gama dinamică să fie mult mai confortabilă pentru a lucra pe termen lung.

Pasul 1: Creați intervalele dinamice denumite.

Pentru început, configurați intervalele denumite care vor fi folosite în cele din urmă ca date sursă pentru viitoarea diagramă.

1. Du-te la Formule filă.
2. Faceți clic pe „Manager nume.”
3. În Manager nume caseta de dialog care apare, selectați „Nou.”

În Nume nou caseta de dialog, creați un nou interval numit:

1. Tip "Sfert" Alături de "Nume" camp. Pentru confortul dvs., faceți ca numele intervalului dinamic să se potrivească cu celula rândului antet corespunzătoare din coloana A (A1).
2. În "Domeniul de aplicare”, Selectați foaia de lucru curentă. În cazul nostru, asta este Foaia 1.
3. Introduceți următoarea formulă în „Se refera la" camp: = OFFSET (Sheet1! $ A $ 2,0,0, COUNTA (Sheet1! $ A: $ A) -1,1)

În engleză simplă, de fiecare dată când modificați orice celulă din foaia de lucru, funcția OFFSET returnează numai valorile reale din coloana A, lăsând în afara celula rândului antet (A1), în timp ce funcția COUNTA recalculează numărul valorilor din coloană de fiecare dată când foaia de lucru este actualizată, efectiv făcând toate lucrurile murdare pentru dvs.

Să prezentăm mai detaliat formula pentru a vă ajuta să înțelegeți cum funcționează:

NOTĂ: Numele unui interval numit trebuie să înceapă cu o literă sau cu subliniere și nu trebuie să conțină spații.

Prin același simbol, configurați un alt interval numit pe baza coloana Marja de profit (coloana B) cu ajutorul acestei formule și etichetați-o „Marja de profit”:

1	= OFFSET (Sheet1! $ B $ 2,0,0, COUNTA (Sheet1! $ B: $ B) -1,1)

Repetați același proces dacă tabelul de date conține mai multe coloane cu valori reale. În cazul nostru, ca urmare, ar trebui să aveți două intervale denumite pregătite pentru acțiune:

Pasul 2: creați o diagramă goală.

Am reușit să trecem prin cea mai dificilă parte. Acum, este timpul să configurați o diagramă goală, astfel încât să puteți introduce manual intervalele dinamice denumite în ea.

1. Selectați orice celulă goală din foaia de lucru curentă (Foaia 1).
2. Întoarce-te la Introduce filă.
3. Configurați orice diagramă 2-D doriți. Pentru exemplul nostru, vom crea o diagramă pe coloane (Introduceți o coloană sau o diagramă de bare> Coloană grupată).

Pasul 3: Adăugați intervalul / intervalele numite care conțin valorile reale.

Mai întâi, introduceți intervalul numit (Marja de profit) legat de valorile reale (coloana B) în grafic.

Faceți clic dreapta pe graficul gol și alegeți „Selectați Date”Din meniul contextual.

În Selectați Sursa de date fereastra de dialog, faceți clic pe „Adăuga.”

În Editați seria casetă, creați o nouă serie de date:

1. Sub "Numele seriei, ”Evidențiați celula corespunzătoare rândului de antet (B1).
2. Sub "Valorile seriei, ”Specificați intervalul numit care urmează a fi trasat pe diagramă, tastând următoarele:„= Sheet1! Profit_Margin.”Referința este alcătuită din două părți: numele fișei de lucru curente (= Foaie1) și gama dinamică respectivă denumită (Marja de profit). Semnul de exclamare este utilizat pentru a lega cele două variabile împreună.
3. Selectați "BINE.”

Odată ajuns acolo, Excel va grafica automat valorile:

Pasul 4: introduceți zona numită cu etichetele axelor.

În cele din urmă, înlocuiți etichetele implicite ale axelor categoriei cu intervalul numit format din coloana A (Sfert).

În Selectați Sursa de date caseta de dialog, sub „Etichete cu axă orizontală (categorie)," selectează "Editați | ×”Buton.

Apoi, introduceți intervalul numit în diagramă introducând următoarea referință sub „Gama de etichete axe:”

1	= Sheet1! Quarter

În cele din urmă, graficul de coloane bazat pe intervalul grafic dinamic este gata:

Verificați acest lucru: diagrama se actualizează automat ori de câte ori adăugați sau eliminați date din intervalul dinamic.