Cum se creează diagrame interactive cu elemente dinamice în Excel

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

O diagramă interactivă este un grafic care permite utilizatorului să controleze ce serie de date ar trebui să fie ilustrate în diagramă, făcând posibilă afișarea sau ascunderea datelor folosind controale de formular. Acestea vin în mai multe forme și forme, variind de la butoane de opțiune până la bare de derulare și liste derulante.

La rândul său, dinamizarea unor elemente ale diagramei - cum ar fi titlul diagramei - face posibilă automatizarea procesului de actualizare a graficelor ori de câte ori adăugați sau eliminați elemente din datele sursă subiacente.

Deci, crearea de diagrame interactive cu elemente dinamice vă permite să ucideți două păsări cu o singură piatră.

Pentru a începe, graficele interactive vă ajută să afișați cu precizie cantități masive de date cu un singur grafic:

În plus, transformarea unor elemente ale diagramei din statică în dinamică economisește nenumărate ore pe termen lung și vă împiedică să uitați vreodată să actualizați diagrama ori de câte ori modificați tabelul de date.

Acestea fiind spuse, acest tutorial este un curs rapid asupra tehnicilor avansate de construire a graficelor în Excel: veți învăța cum să creați diagrame interactive atât cu o listă derulantă, cât și cu o bară de derulare, precum și să stăpâniți modalitățile de a face titlul graficului. și diagrama intervalului de date dinamic.

Exemple de date

Pentru a vă arăta ce este, avem nevoie de câteva date brute cu care să lucrăm. Din acest motiv, luați în considerare următorul tabel care ilustrează cifrele vânzărilor de cărți ale unei librării online fictive:

Cum se creează un titlu de diagramă dinamică

Să abordăm mai întâi partea ușoară: dinamizarea titlului graficului.

Puteți face titlul dinamic conectându-l la o anumită celulă dintr-o foaie de lucru, astfel încât orice modificare a valorii din acea celulă să fie reflectată automat în titlul graficului.

Iar frumusețea acestei metode este că este nevoie de doar câțiva pași simpli pentru a transforma titlul graficului din static în dinamic:

1. Faceți dublu clic pe titlul graficului.
2. Apasă pe Formula Bar și tastați „=”În el.
3. Selectați orice celulă pentru a o atribui ca noul titlu al diagramei.

La prima vedere, ideea pare destul de primitivă. Dar abia am zgâriat chiar și suprafața lucrurilor făcute posibile prin aplicarea acestei tehnici simple. Pentru a afla mai multe, consultați tutorialul detaliat care acoperă titlurile grafice dinamice în Excel mai exhaustiv.

Cum se creează o gamă dinamică de diagrame

În continuare: setarea unui interval de grafic dinamic. Din nou, în mod convențional, de fiecare dată când adăugați sau eliminați elemente din intervalul de date utilizat la construirea unei diagrame Excel, datele sursă ar trebui să fie ajustate manual, de asemenea.

Din fericire, vă puteți salva dificultatea de a face față tuturor acestor lucruri, creând un interval dinamic de diagrame care actualizează automat diagrama de fiecare dată când datele sursă sunt modificate.

În esență, există două modalități de a face acest lucru: metoda tabelului și tehnica intervalelor numite. Vom acoperi pe scurt metoda tabelului mai ușor aici, dar pentru a obține o înțelegere completă a modului de valorificare a puterii intervalelor grafice dinamice în Excel, consultați tutorialul care acoperă toate intrările și ieșirile în detaliu.

Oricum, reveniți la metoda tabelului. În primul rând, transformați gama de celule pe care doriți să o utilizați pentru a construi o diagramă într-un tabel.

1. Selectați întregul interval de date (A2: G6).
2. Mergeți la Introduce filă.
3. Apasă pe "Masa”Buton.

Dacă datele dvs. nu au anteturi, debifați „Masa mea are anteturi" cutie.

Odată ajuns acolo, intervalul de date va fi convertit într-un tabel.

Acum, tot ce trebuie să faceți este să faceți graficul în timp ce utilizați tabelul ca date sursă ale graficului:

1. Evidențiați masa (A2: G6).
2. Navigați la Introduce filă.
3. Creați orice diagramă 2-D, de exemplu, o diagramă cu bare în grup (Introduceți Coloana sau Diagrama de bare> Bară grupată).

Voilà! Ești gata. Acum, testați-l inserând elemente noi în tabel pentru a le vedea reîncărcate automat în grafic.

Cum să creați o diagramă interactivă cu o listă derulantă

Acum că ați făcut primii pași în domeniul instrumentelor avansate de vizualizare a datelor în Excel, este timpul să treceți la partea dificilă: diagrame interactive Excel.

Când aveți o mulțime de date de graficat, introducerea caracteristicilor interactive în graficele Excel - fie că sunt liste derulante, casete de selectare sau bare de derulare - vă ajută să păstrați spațiul tabloului de bord și să decupați graficul graficului, făcându-l mai confortabil pentru utilizator pentru a analiza datele.

De exemplu, aruncați o privire la diagrama de coloane prezentată mai jos. În loc să afișeze un grafic dezordonat care conține întregul tabel de date, optarea pentru această diagramă interactivă cu o listă derulantă permite utilizatorului să mărească cifrele vânzărilor pentru un anumit an în doar două clicuri.

Urmați pașii detaliați descriși mai jos pentru a afla cum puteți face același lucru.

Pasul 1: puneți bazele.

Înainte de a începe, accesați fișierul Dezvoltator filă care conține unele dintre caracteristicile avansate Excel pe care le vom întoarce mai departe pe drum. Fila este ascunsă în mod prestabilit, dar este nevoie doar de câteva clicuri pentru a o adăuga panglica.

Mai întâi, faceți clic dreapta pe orice spațiu gol din panglica și selectați „Personalizați panglica.”

În Opțiuni Excel caseta de dialog, navigați la Filele principale lista, verificați „Dezvoltator”Și apăsați pe„BINE.”

The Dezvoltator fila ar trebui să apară acum pe panglica. În acest moment, alegeți orice celulă goală pentru afișarea ieșirii din meniul derulant (B8). Acest lucru va ajuta Excel să afișeze datele corecte pe baza opțiunii selectate din lista derulantă.

Pasul 2: adăugați lista derulantă.

Următorul nostru pas este să adăugăm meniul derulant la foaia de lucru:

1. Du-te la Dezvoltator filă.
2. Faceți clic pe „Introduce.”
3. Sub "Controale de formular," Selectați "Combo Box.”

Desenați acea casetă combo goală oriunde doriți. Pentru a ilustra, iată cum arată un meniu drop-down gol:

Pasul 3: conectați lista derulantă la celulele foii de lucru.

Pentru ca meniul să importe lista anilor din tabelul de date, trebuie să o legați la intervalul de date respectiv din foaia de lucru. Pentru a face acest lucru, faceți clic dreapta pe caseta combinată și alegeți „Controlul formatului.”

În caseta de dialog, efectuați următoarele:

1. Pentru "Gama de intrare, ”Evidențiați toate valorile reale din tabelul de date ($ A $ 3: $ G $ 6).
2. Pentru "Legătură celulară, ”Selectați celula goală alocată pentru stocarea rezultatului meniului derulant ($ B $ 8).
3. Pentru "Liniile derulante, ”Introduceți numărul de puncte de date în setul de date (în cazul nostru, patru) și faceți clic pe„BINE.”

Această acțiune ar trebui să ducă la adăugarea numelor punctelor de date la lista derulantă în timp ce valoarea celulei de ajutor (B8) caracterizează elementul selectat curent din meniu ca opțiune 1, 2, 3 sau 4:

Pasul 4: configurați tabelul de date al graficului.

Deoarece graficul ar trebui să ilustreze o singură serie de date la un moment dat, ca o soluție alternativă, configurați un tabel de ajutor dinamic care va trece prin datele brute și va returna numai cifrele de vânzări pentru anul ales din listă.

Tabelul respectiv va fi utilizat în cele din urmă ca sursă de date a graficului, permițând în mod eficient utilizatorului să schimbe vizualizările cu meniul derulant.

Mai întâi, copiați antetul tabelului cu date de vânzări și lăsați un rând gol pentru afișarea cifrelor de vânzări filtrate.

Dar cum vă asigurați că datele sunt extrase corect? Aici intră în funcțiune funcția INDEX. Introduceți următoarea formulă în A11 și copiați-l în G11:

1	= INDEX (A3: A6, $ B $ 8)

Practic, formula filtrează prin coloană An (coloana A) și returnează numai valoarea din rând care număr corespunde elementului selectat în prezent în lista derulantă.

Pasul 5: configurați un grafic pe baza datelor din graficul dinamic.

Ați reușit să treceți prin partea cea mai dificilă, așa că tot ce mai rămâne de făcut este să creați o diagramă 2-D pe baza tabelului de ajutor (evidențiați tabelul de ajutor [A10: G11]> Inserați> creați o diagramă de coloane grupată).

Ca ultimă atingere, dacă doriți ca meniul derulant să fie plasat deasupra diagramei, faceți clic dreapta pe caseta combinată, faceți clic pe „Ordin, ”Și alegeți„Aduceți în față. ” (Alternativ, faceți clic dreapta pe graficul grafic, faceți clic pe „Ordin, ”Și alegeți„Trimite înapoi.”)

Asta e! Aici aveți diagrama interactivă a coloanelor legată de meniul derulant.

Cum să creați o diagramă interactivă cu o bară de derulare

Adevărat, procesul de adăugare a unei bare de derulare în diagramele Excel este similar cu pașii descriși mai sus.

Dar dacă doriți să analizați dinamica vânzărilor fiecărei categorii de cărți (coloanele), spre deosebire de afișarea cifrelor anuale de vânzări (rândurile)? Pentru a ilustra, aruncați o privire la diagrama interactivă de mai jos făcând exact acest lucru:

Cum o scoți? Ei bine, aici lucrurile încep să devină un pic mai complexe. Cu toate acestea, dacă rămâneți doar la procesul pas cu pas de mai jos, respectarea instrucțiunilor ar trebui să vă simtă ca o plimbare în parc, chiar dacă sunteți un începător complet.

Pasul 1: puneți bazele.

Înainte de a începe, adăugați Dezvoltator filă la panglica, deoarece conține caracteristica care ne permite să desenăm o bară de derulare (faceți clic dreapta pe orice spațiu gol din panglică> personalizați panglica> Filele principale> bifați caseta „Dezvoltator”> OK).

Pentru a face lucrurile să funcționeze cum ar fi ceasul, atribuiți o celulă goală pentru afișarea ieșirii barei de defilare, astfel încât Excel să poată poziționa corect caseta de defilare în bara de defilare (B8).

Pasul 2: Desenați bara de derulare.

Următorul nostru pas este să introducem bara de defilare în foaia de lucru:

1. Du-te la Dezvoltator filă.
2. Loveste "Introduce”Buton.
3. Sub "Controale de formular," Selectați "Bara de derulare.”

În acest moment, puteți desena bara de derulare oriunde doriți. O vom muta în loc mai târziu.

Pasul 2: legați bara de defilare la datele foii de lucru.

Faceți clic dreapta pe bara de derulare și selectați „Controlul formatului.”

În Controlul formatului caseta de dialog, modificați setările barei de derulare:

• Valoarea curentă: Această valoare definește poziția implicită a casetei de derulare din bara de derulare. Setați valoarea la „1.”
• Valoare minimă: Această setare înseamnă cea mai mică valoare a barei de derulare. În cazul nostru, tastați „1”În câmp.
• Valoare maximă: Acest parametru determină cea mai mare valoare a barei de derulare și ar trebui să corespundă cu numărul de puncte de date care trebuie utilizate pentru reprezentarea diagramei interactive. Deoarece tabelul de date cuprinde șase categorii de cărți, modificați valoarea în „6.”
• Schimbare incrementală: Această valoare determină cât de ușor își schimbă poziția caseta de derulare atunci când o mutați. Setați întotdeauna valoarea la „1.”
• Schimbarea paginii: Această valoare determină cât de ușor își schimbă poziția caseta de derulare atunci când faceți clic pe zona dintre caseta de derulare și săgeți. Setați întotdeauna valoarea la „1.”
• Link celular: Această valoare caracterizează poziția curentă a casetei de derulare. Evidențiați pur și simplu celula din foaia de lucru atribuită anterior în acest scop ($ B $ 8).

Pasul 3: Creați tabelul de date al graficului.

După ce ați terminat procesul de configurare, creați un nou tabel de asistență pentru stocarea datelor grafice dinamice extrase din datele brute. Datele graficului vor regla automat valorile în funcție de poziția curentă a casetei de derulare, legând în mod eficient bara de derulare la graficul graficului.

Pentru a face acest lucru, începeți cu copierea coloana An așa cum se arată în captura de ecran de mai jos (A2: A6).

Odată ajuns acolo, implementați următoarea funcție INDEX care alege intervalul de celule care conține numele categoriilor de carte și returnează o valoare corespunzătoare pe baza poziției casetei de derulare.

Tastați următoarea formulă în celulă B10:

1	= INDEX ($ B $ 2: $ G $ 2, $ B $ 8)

Acum, importați cifrele de vânzări corespunzătoare categoriei de cărți curente, introducând această formulă în B11 și copiați-l (B11: B14):

1	= INDEX ($ B $ 3: $ G $ 6, MATCH (A11, $ A $ 3: $ A $ 6,0), MATCH ($ B $ 10, $ B $ 2: $ G $ 2, 0))

Combinația dintre funcțiile INDEX și MATCH ajută la returnarea cu precizie a valorii numărului de rânduri și coloane care corespunde criteriilor specificate.

Formula filtrează mai întâi coloana An ($ A $ 3: $ A $ 6) și alege rândul care se potrivește cu anul corespunzător în tabelul de ajutor (A11). Apoi, formula trece prin numele categoriilor de cărți în același mod ($ B $ 2: $ G $ 2). Ca rezultat, formula returnează valoarea dinamică corectă care se reglează ori de câte ori mutați caseta de derulare.

Pasul 4: Creați o diagramă pe baza tabelului de ajutor.

Odată ce tabelul de date al graficului a fost pus laolaltă, construiți un grafic simplu pentru a vedea bara de derulare în acțiune.

Evidențiați întreaga masă de ajutor (A10: B14), mergi la Introduce tab și graficați orice diagramă 2-D, de exemplu, o diagramă liniară (Introduceți Diagrama liniei sau a zonei> Linia cu markere).

NOTĂ: Dacă doriți ca bara de defilare să fie afișată deasupra diagramei, faceți clic dreapta pe bara de defilare, alegeți „Ordin, ”Și selectați„Aduceți înainte. ” (Alternativ, faceți clic dreapta pe graficul graficului, faceți clic pe „Ordin, ”Și alegeți„Trimite înapoi.”)

Și așa o faci. Tocmai ați învățat tot ceea ce trebuie să știți despre tehnicile avansate puternice care vă vor îmbunătăți jocul de vizualizare a datelor.