Kako stvoriti interaktivne grafikone s dinamičkim elementima u Excelu

Ovaj će vodič pokazati kako stvoriti interaktivne grafikone s dinamičkim elementima u svim verzijama programa Excel: 2007, 2010, 2013, 2016 i 2022.

Interaktivni grafikon je grafikon koji korisniku omogućuje da kontrolira koji niz podataka treba prikazati na grafikonu, što omogućuje prikaz ili skrivanje podataka pomoću kontrola obrasca. Oni dolaze u mnogo oblika i oblika, u rasponu od gumba s opcijama do traka za pomicanje i padajućih popisa.

S druge strane, čineći neke elemente grafikona dinamičkim-poput naslova grafikona-omogućuje automatiziranje procesa ažuriranja grafikona kad god dodate ili uklonite stavke iz osnovnih izvornih podataka.

Dakle, stvaranje interaktivnih karata s dinamičkim elementima omogućuje vam da jednim udarcem ubijete dvije ptice.

Za početak, interaktivni grafikoni pomažu vam da uredno prikažete ogromne količine podataka sa samo jednim grafikonom:

Osim toga, pretvaranje nekih elemenata grafikona iz statičkog u dinamički dugoročno štedi nebrojene sate i sprječava vas da zaboravite ažurirati grafikon kad god promijenite tablicu podataka.

S obzirom na to, ovaj je vodič brzi tečaj naprednih tehnika izgradnje grafikona u Excelu: naučit ćete kako stvoriti interaktivne grafikone i s padajućeg popisa i s trakom za pomicanje, kao i savladati načine za stvaranje naslova grafikona i raspon podataka grafikona dinamički.

Uzorci podataka

Da bismo vam pokazali što je što, potrebni su nam sirovi podaci za rad. Iz tog razloga razmotrite sljedeću tablicu koja prikazuje podatke o prodaji knjiga izmišljene internetske knjižare:

Kako stvoriti naslov dinamičkog grafikona

Prijeđimo na lakši dio: učinimo naslov grafikona dinamičnim.

Naslov možete učiniti dinamičnim povezivanjem s određenom ćelijom na radnom listu tako da se sve promjene vrijednosti u toj ćeliji automatski odražavaju u naslovu grafikona.

Ljepota ove metode je u tome što je potrebno samo nekoliko lakih koraka za pretvaranje naslova grafikona iz statičkog u dinamički:

1. Dvaput kliknite na naslov grafikona.
2. Kliknite na Traka formule i upišite „=”U to.
3. Odaberite bilo koju ćeliju da biste je dodijelili novom naslovu grafikona.

Na prvi pogled, ideja se čini prilično primitivnom. Ali jedva smo čak i izgrebali površinu stvari koje su omogućene primjenom ove jednostavne tehnike. Da biste saznali više, detaljnije pogledajte dubinski vodič koji pokriva naslove dinamičkih grafikona u Excelu.

Kako stvoriti raspon dinamičkih grafikona

Sljedeće: postavljanje dinamičkog raspona grafikona. Opet, uobičajeno, svaki put kad dodate ili uklonite stavke iz raspona podataka koji se koriste pri izgradnji Excel grafikona, izvorne podatke treba prilagoditi i ručno.

Srećom, možete si uštedjeti gnjavažu da se morate nositi sa svim tim stvaranjem dinamičkog raspona grafikona koji automatski ažurira grafikon svaki put kad se promijene izvorni podaci.

U osnovi, postoje dva načina za to: metoda tablice i tehnika imenovanih raspona. Ovdje ćemo ukratko obraditi lakšu metodu tablice, ali kako biste u potpunosti shvatili kako iskoristiti snagu dinamičkih raspona grafikona u Excelu, detaljnije pogledajte vodič koji pokriva sve ulaze i izlaze.

U svakom slučaju, vratimo se na metodu tablice. Prvo okrenite raspon ćelija koje želite koristiti za izradu grafikona u tablicu.

1. Odaberite cijeli raspon podataka (A2: G6).
2. Prijeđite na Umetnuti tab.
3. Kliknite na “Stol" dugme.

Ako vaši podaci nemaju zaglavlja, poništite okvir "Moj stol ima zaglavlja”Kutija.

Tamo će se raspon podataka pretvoriti u tablicu.

Sada sve što trebate učiniti je iscrtati grafikon dok tablicu koristite kao izvorne podatke grafikona:

1. Označite tablicu (A2: G6).
2. Idite na Umetnuti tab.
3. Izradite bilo koji 2-D grafikon-na primjer, klasterizirani stupčasti grafikon (Umetni stupac ili trakasti grafikon> Grupirana traka).

Voilà! Spreman si. Sada isprobajte umetanjem novih stavki u tablicu kako biste ih vidjeli automatski ponovno napunjene na grafikonu.

Kako stvoriti interaktivni grafikon s padajućim popisom

Sada kada ste napravili prve korake u sferi naprednih alata za vizualizaciju podataka u Excelu, vrijeme je da prijeđete na najteži dio: interaktivne grafikone programa Excel.

Kada imate mnogo podataka za grafikon, uvođenje interaktivnih značajki u vaše Excel grafikone-bilo da se radi o padajućim popisima, potvrdnim okvirima ili trakama za pomicanje-pomaže vam u očuvanju prostora na nadzornoj ploči i odmašćivanju grafikona, čineći ga ugodnijim za korisnika za analizu podataka.

Kao primjer, pogledajte stupacni grafikon prikazan ispod. Umjesto prikazivanja neurednog grafikona koji sadrži cijelu tablicu podataka, odabir ovog interaktivnog grafikona s padajućim popisom omogućuje korisniku da u samo dva klika poveća zbrojeve prodaje za određenu godinu.

Samo slijedite dolje navedene detaljne korake kako biste saznali kako možete učiniti istu stvar.

Korak #1: Postavite temelje.

Prije nego počnemo, pristupite datoteci Programer karticu koja sadrži neke od naprednih Excel značajki koje ćemo okrenuti dalje. Kartica je prema zadanim postavkama skrivena, ali je za dodavanje potrebno samo nekoliko klikova vrpcu.

Najprije desnom tipkom miša kliknite bilo koji prazan prostor u vrpcu i odaberite “Prilagodite vrpcu.”

U Opcije programa Excel dijaloški okvir, idite na Glavne kartice popis, provjerite "Programer”, Pa pritisnite“U REDU.”

The Programer kartica bi se sada trebala uključiti vrpcu. U ovom trenutku odaberite bilo koju praznu ćeliju za prikaz rezultata padajućeg izbornika (B8). To će pomoći Excelu da prikaže prave podatke ovisno o tome koja je opcija odabrana s padajućeg popisa.

Korak 2: Dodajte padajući popis.

Naš sljedeći korak je dodavanje padajućeg izbornika na radni list:

1. Idite na Programer tab.
2. Kliknite “Umetnuti.”
3. Pod, ispod "Kontrole obrazaca," Odaberi "Kombinirana kutija.”

Nacrtajte taj prazni kombinirani okvir gdje god želite. Za ilustraciju, evo kako izgleda prazan padajući izbornik:

Korak #3: Povežite padajući popis s ćelijama radnog lista.

Da bi izbornik uvezao popis godina u tablici podataka, morate ga povezati s odgovarajućim rasponom podataka na radnom listu. Da biste to učinili, desnom tipkom miša kliknite kombinirani okvir i odaberite "Kontrola formata.”

U dijaloškom okviru učinite sljedeće:

1. Za “Raspon unosa, ”Označite sve stvarne vrijednosti u tablici podataka ($ A $ 3: $ G $ 6).
2. Za “Stanična veza, ”Odaberite praznu ćeliju dodijeljenu za spremanje rezultata padajućeg izbornika ($ B $ 8).
3. Za “Padajuće linije, "Unesite broj podatkovnih točaka u svoj skup podataka (u našem slučaju četiri) i kliknite"U REDU.”

Ova radnja trebala bi rezultirati dodavanjem imena podatkovnih točaka na padajući popis dok vrijednost ćelije pomoćnika (B8) karakterizira trenutno odabranu stavku s izbornika kao opciju 1, 2, 3 ili 4:

Korak #4: Postavite tablicu podataka grafikona.

Budući da grafikon treba ilustrirati samo jednu seriju podataka odjednom, kao zaobilazno rješenje, postavite dinamičku pomoćnu tablicu koja će pregledavati sirove podatke i vraćati samo podatke o prodaji za godinu odabranu s popisa.

Ta će se tablica na kraju koristiti kao izvorni podaci grafikona, učinkovito omogućujući korisniku promjenu prikaza pomoću padajućeg izbornika.

Najprije kopirajte zaglavlje tablice podataka o prodaji i ostavite prazan redak za prikaz filtriranih podataka o prodaji.

No, kako ćete osigurati da se podaci ispravno izvuku? Tu dolazi u obzir funkcija INDEX. Unesite sljedeću formulu u A11 i kopirajte ga u G11:

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

U osnovi, formula filtrira stupac Godina (stupac A) i vraća samo vrijednost u retku čiji broj odgovara trenutno odabranoj stavci na padajućem popisu.

Korak #5: Postavite grafikon na temelju podataka dinamičkog grafikona.

Uspjeli ste proći najteži dio pa vam preostaje samo stvoriti 2-D grafikon na temelju pomoćne tablice (označite pomoćnu tablicu [A10: G11]> Umetni> izradite klasterizirani stupac grafikona).

Za kraj, ako želite da se padajući izbornik postavi na vrh grafikona, desnom tipkom miša kliknite kombinirani okvir, a zatim kliknite “Narudžba, "I odaberite"Donijeti naprijed. ” (Alternativno, desnom tipkom miša kliknite grafikon, pa kliknite "Narudžba, "I odaberite"Poslati natrag.”)

To je to! Tu imate svoj interaktivni stupac grafikon povezan s padajućim izbornikom.

Kako stvoriti interaktivni grafikon s trakom za pomicanje

Istini za volju, postupak dodavanja trake za pomicanje vašim Excel tablicama sličan je gore navedenim koracima.

No što ako želite analizirati dinamiku prodaje svake kategorije knjiga (stupce), umjesto da prikazujete godišnje podatke o prodaji (retke)? Za ilustraciju, pogledajte dolje interaktivni linijski grafikon radeći upravo to:

Kako to uspijevate? Pa, tu stvari počinju postajati malo složenije. Međutim, ako se samo držite dolje opisanog postupka korak po korak, slijedeći upute trebalo bi se osjećati kao šetnja parkom, čak i ako ste tek početnik.

Korak #1: Postavite temelje.

Prije nego počnete, dodajte Programer karticu do vrpcu, jer sadrži značajku koja nam omogućuje crtanje trake za pomicanje (desnom tipkom miša kliknite bilo koji prazan prostor na vrpci> prilagodite vrpcu> glavne kartice> potvrdite okvir "Developer"> OK).

Da bi stvari funkcionirale kao sat, dodijelite praznu ćeliju za prikaz izlaza trake za pomicanje tako da Excel može ispravno postaviti okvir za pomicanje u traku za pomicanje (B8).

Korak 2: Nacrtajte traku za pomicanje.

Naš sljedeći korak je umetanje trake za pomicanje u radni list:

1. Idite na Programer tab.
2. Udari "Umetnuti" dugme.
3. Pod, ispod "Kontrole obrazaca," Odaberi "Traka za pomicanje.”

U ovom trenutku možete povući traku za pomicanje gdje god želite. Kasnije ćemo ga premjestiti na mjesto.

Korak 2: Povežite traku za pomicanje s podacima radnog lista.

Desnom tipkom miša kliknite traku za pomicanje i odaberite “Kontrola formata.”

U Kontrola formata dijaloški okvir, izmijenite postavke trake za pomicanje:

• Trenutna vrijednost: Ova vrijednost definira zadani položaj okvira za pomicanje na traci za pomicanje. Postavite vrijednost na "1.”
• Minimalna vrijednost: Ova postavka označava najnižu vrijednost trake za pomicanje. U našem slučaju upišite „1”U polje.
• Maksimalna vrijednost: Ovaj parametar određuje najveću vrijednost trake za pomicanje i trebao bi odgovarati broju podatkovnih točaka koje će se koristiti za crtanje interaktivnog grafikona. Budući da se tablica podataka sastoji od šest kategorija knjiga, promijenite vrijednost u „6.”
• Inkrementalna promjena: Ova vrijednost određuje koliko glatko okvir za pomicanje mijenja svoj položaj kada ga pomaknete. Uvijek postavite vrijednost na “1.”
• Promjena stranice: Ova vrijednost određuje koliko glatko okvir za pomicanje mijenja svoj položaj kada pritisnete područje između okvira za pomicanje i strelica. Uvijek postavite vrijednost na “1.”
• Mobilna veza: Ova vrijednost karakterizira trenutni položaj okvira za pomicanje. Jednostavno označite ćeliju na radnom listu koji je prethodno dodijeljen u tu svrhu ($ B $ 8).

Korak #3: Izradite tablicu podataka grafikona.

Nakon što dovršite postupak postavljanja, izradite novu pomoćnu tablicu za spremanje podataka dinamičkog grafikona izvučenih iz sirovih podataka. Podaci grafikona automatski će prilagoditi vrijednosti ovisno o trenutnom položaju okvira za pomicanje, učinkovito povezujući traku za pomicanje s grafikonom.

Da biste to učinili, počnite s kopiranjem stupac Godina kao što je prikazano na slici ispod (A2: A6).

Kad stignete tamo, implementirajte sljedeću INDEX funkciju koja odabire raspon ćelija koji sadrži nazive kategorija knjiga i vraća odgovarajuću vrijednost na temelju položaja okvira za pomicanje.

Upišite sljedeću formulu u ćeliju B10:

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

Sada uvezite podatke o prodaji koji odgovaraju trenutnoj kategoriji knjiga unošenjem ove formule u B11 i kopirajte ga (B11: B14):

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

Kombinacija INDEX i MATCH funkcija pomaže u točnom vraćanju vrijednosti broja retka i stupca koji odgovara navedenim kriterijima.

Formula najprije filtrira stupac Godina ($ A $ 3: $ A $ 6) i odabire red koji odgovara odgovarajućoj godini u pomoćnoj tablici (A11). Zatim formula prosijava nazive kategorija knjiga na isti način ($ B $ 2: $ G $ 2). Kao rezultat toga, formula vraća ispravnu dinamičku vrijednost koja se prilagođava svaki put kad pomaknete okvir za pomicanje.

Korak #4: Izradite grafikon na temelju pomoćne tablice.

Nakon što sastavite tablicu podataka grafikona, samo sastavite jednostavan grafikon da biste vidjeli kliznu traku na djelu.

Označite cijelu pomoćnu tablicu (A10: B14), idite na Umetnuti karticu i iscrtajte bilo koji 2-D grafikon-na primjer, linijski grafikon (Umetnite linijski ili područni grafikon> Linija s oznakama).

NAPOMENA: Ako želite da se traka za pomicanje prikazuje na vrhu grafikona, desnom tipkom miša kliknite traku za pomicanje, odaberite „Narudžba, "I odaberite"Iznijeti. ” (Alternativno, desnom tipkom miša kliknite grafikon, pa kliknite "Narudžba, "I odaberite"Poslati natrag.”)

I tako to radite. Upravo ste naučili sve što trebate znati o moćnim naprednim tehnikama koje će ubrzati igru ​​vizualizacije podataka.