Kako ustvariti interaktivne grafikone z dinamičnimi elementi v Excelu

Ta vadnica bo pokazala, kako ustvariti interaktivne grafikone z dinamičnimi elementi v vseh različicah Excela: 2007, 2010, 2013, 2016 in 2022.

Interaktivni grafikon je graf, ki uporabniku omogoča nadzor nad tem, katere serije podatkov je treba prikazati v grafikonu, kar omogoča prikaz ali skrivanje podatkov z uporabo kontrolnikov obrazcev. Te so različnih oblik in oblik, od gumbov z možnostmi do drsnih trakov in spustnih seznamov.

Če pa nekateri elementi grafikona postanejo dinamični-na primer naslov grafikona-, lahko avtomatizirate postopek posodabljanja grafov, kadar koli dodate ali odstranite elemente iz osnovnih izvornih podatkov.

Tako ustvarjanje interaktivnih grafikonov z dinamičnimi elementi omogoča, da z enim samim udarcem ubijete dve ptici.

Za začetek vam interaktivni grafikoni pomagajo lepo prikazati ogromne količine podatkov samo z enim grafom:

Poleg tega spreminjanje nekaterih elementov grafikona iz statičnega v dinamično dolgoročno prihrani nešteto ur in vam prepreči, da bi pozabili posodobiti grafikon, kadar koli spremenite podatkovno tabelo.

Ob tem je ta vadnica hiter tečaj naprednih tehnik ustvarjanja grafikonov v Excelu: naučili se boste ustvarjati interaktivne grafikone s spustnim seznamom in drsnim trakom ter obvladati načine, kako narediti naslov grafikona in grafično območje podatkov dinamično.

Vzorčni podatki

Da bi vam pokazali, kaj je kaj, potrebujemo nekaj surovih podatkov za delo. Zato razmislite o naslednji tabeli, ki ponazarja prodajo knjig v izmišljeni spletni knjigarni:

Kako ustvariti naslov dinamičnega grafikona

Najprej se lotimo lažjega dela: naredimo naslov grafikona dinamičen.

Naslov lahko naredite dinamičnega tako, da ga povežete z določeno celico na delovnem listu, tako da se vse spremembe vrednosti v tej celici samodejno odražajo v naslovu grafikona.

Lepota te metode je, da je potrebnih le nekaj preprostih korakov, da naslov grafikona spremenite iz statičnega v dinamičnega:

1. Dvokliknite naslov grafikona.
2. Kliknite na Vrstica formule in vnesite "=«Vanj.
3. Izberite katero koli celico, da jo dodelite novemu naslovu grafikona.

Na prvi pogled se zdi ideja precej primitivna. Komaj smo celo opraskali površino stvari, ki so bile omogočene z uporabo te preproste tehnike. Če želite izvedeti več, si podrobneje oglejte poglobljeno vadnico, ki zajema naslove dinamičnih grafikonov v Excelu.

Kako ustvariti obseg dinamičnih grafikonov

Naslednji korak: nastavitev dinamičnega obsega grafikona. Ponovno, običajno, vsakič, ko dodate ali odstranite elemente iz podatkovnega obsega, uporabljenega pri izdelavi Excelovega grafikona, je treba tudi ročno prilagoditi izvorne podatke.

Na srečo si lahko prihranite težave, ko se morate spoprijeti z vsem tem, tako da ustvarite dinamični obseg grafikonov, ki samodejno posodobi grafikon vsakič, ko spremenite izvorne podatke.

V bistvu obstajata dva načina: metoda tabele in tehnika imenovanih obsegov. Na kratko bomo obravnavali enostavnejšo metodo tabele, toda če želite v celoti razumeti, kako izkoristiti moč dinamičnih razponov grafikonov v Excelu, si podrobneje oglejte vadnico, ki zajema vse podrobnosti.

Kakorkoli, nazaj k metodi tabel. Najprej obseg celic, ki ga želite uporabiti za izgradnjo grafikona, pretvorite v tabelo.

1. Izberite celoten obseg podatkov (A2: G6).
2. Odpravite se do Vstavi zavihek.
3. Kliknite na “Tabela”Gumb.

Če vaši podatki nimajo glave, počistite polje »Moja miza ima glave" škatla.

Ko bo tam, bo obseg podatkov pretvorjen v tabelo.

Zdaj morate le narisati grafikon, medtem ko tabelo uporabljate kot izvorne podatke grafikona:

1. Označite tabelo (A2: G6).
2. Pomaknite se do Vstavi zavihek.
3. Ustvarite kateri koli 2-D grafikon-na primer gručasti stolpčni grafikon (Vstavite stolpec ali palični grafikon> Grozdasta vrstica).

Voilà! Vse ste pripravljeni. Zdaj ga preizkusite tako, da v tabelo vstavite nove elemente, da jih vidite samodejno na grafikonu.

Kako ustvariti interaktivni grafikon s spustnim seznamom

Zdaj, ko ste naredili prve korake na področju naprednih orodij za vizualizacijo podatkov v Excelu, je čas, da preidete na težji del: interaktivne grafikone Excel.

Ko imate na zemljevidu veliko podatkov, vam uvedba interaktivnih funkcij v Excelove grafikone-pa naj bodo to spustni seznami, potrditvena polja ali drsne vrstice-pomaga ohraniti prostor na nadzorni plošči in razčleniti grafikon, hkrati pa bo uporabniku bolj udoben za analizo podatkov.

Za primer si oglejte spodnji stolpčni grafikon. Namesto prikaza neurejenega grafa, ki vsebuje celotno tabelo s podatki, izbira tega interaktivnega grafikona s spustnim seznamom omogoča uporabniku, da v samo dveh klikih poveča podatke o prodaji za določeno leto.

Samo sledite podrobnim korakom, opisanim spodaj, če želite izvedeti, kako lahko storite isto.

1. korak: Postavite temelje.

Preden začnemo, dostopajte do Razvijalec zavihek, ki vsebuje nekatere napredne funkcije programa Excel, ki jih bomo nadaljevali. Zavihek je privzeto skrit, vendar ga dodate le nekaj klikov traku.

Najprej z desno miškino tipko kliknite kateri koli prazen prostor v traku in izberite »Prilagodite trak.”

V Možnosti programa Excel pogovorno okno, pojdite na Glavni zavihki seznam, preverite polje »Razvijalec"Polje in pritisnite"V REDU.”

The Razvijalec zavihek bi se moral zdaj prikazati traku. Na tej točki izberite katero koli prazno celico za prikaz izhoda spustnega menija (B8). To bo Excelu pomagalo prikazati prave podatke glede na to, katera možnost je izbrana na spustnem seznamu.

2. korak: Dodajte spustni seznam.

Naš naslednji korak je, da na delovni list dodamo spustni meni:

1. Pojdite na Razvijalec zavihek.
2. Kliknite »Vstavi.”
3. Spodaj "Kontrolniki obrazcev, "Izberite"Kombinirana škatla.”

Narišite to prazno kombinirano polje kjer koli želite. Za ponazoritev je videti, kako izgleda prazen spustni meni:

3. korak: Povežite spustni seznam s celicami delovnega lista.

Če želite, da meni uvozi seznam let v podatkovni tabeli, ga morate povezati z ustreznim obsegom podatkov na delovnem listu. Če želite to narediti, z desno miškino tipko kliknite kombinirano polje in izberite »Nadzor oblike.”

V pogovornem oknu naredite naslednje:

1. Za "Razpon vnosa, ”Označite vse dejanske vrednosti v podatkovni tabeli ($ A $ 3: $ G $ 6).
2. Za "Celična povezava, ”Izberite prazno celico, namenjeno shranjevanju izhodnega menija ($ B $ 8).
3. Za "Spustne vrstice, "Vnesite število podatkovnih točk v svoj nabor podatkov (v našem primeru štiri) in kliknite"V REDU.”

To dejanje bi moralo povzročiti, da se imena podatkovnih točk dodajo na spustni seznam, medtem ko je vrednost celice pomočnika (B8) označuje trenutno izbrano postavko v meniju kot možnost 1, 2, 3 ali 4:

4. korak: Nastavite tabelo s podatki grafikona.

Ker mora grafikon prikazati samo eno podatkovno serijo naenkrat, kot rešitev uporabite vzpostavitev dinamične pomožne tabele, ki bo pregledala neobdelane podatke in vrnila izključno podatke o prodaji za leto, izbrano s seznama.

Ta tabela bo sčasoma uporabljena kot izvorni podatki grafikona, kar uporabniku dejansko omogoča preklapljanje pogledov s spustnim menijem.

Najprej kopirajte glavo tabele s podatki o prodaji in pustite prazno vrstico za prikaz filtriranih podatkov o prodaji.

Kako pa poskrbite, da bodo podatki pravilno pridobljeni? Tu pride v poštev funkcija INDEX. Vnesite naslednjo formulo A11 in ga prekopirajte v G11:

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

V bistvu formula filtrira skozi stolpec Leto (stolpec A) in vrne izključno vrednost v vrstici, katere številka ustreza trenutno izbrani postavki na spustnem seznamu.

5. korak: Nastavite grafikon na podlagi podatkov dinamičnega grafikona.

Najtežji del ste prenesli, zato morate le ustvariti 2-D grafikon na podlagi pomožne tabele (označite pomožno tabelo [A10: G11]> Vstavi> ustvarite gručasti stolpčni grafikon).

Končno, če želite, da se spustni meni postavi na vrh grafikona, z desno miškino tipko kliknite kombinirano polje in kliknite »Naročite, "In izberite"Pridi spredaj. " (Druga možnost je, da z desno miškino tipko kliknete grafikon in kliknete »Naročite, "In izberite"Pošlji nazaj.”)

To je to! Tam imate svoj interaktivni stolpčni grafikon, povezan s spustnim menijem.

Kako ustvariti interaktivni grafikon z drsnikom

Resnici na ljubo je postopek dodajanja drsnega traku v Excelove grafikone podoben zgoraj opisanim korakom.

Kaj pa, če želite analizirati dinamiko prodaje vsake kategorije knjig (stolpci) in ne prikazati letnih podatkov o prodaji (vrstice)? Za ponazoritev si oglejte spodnji interaktivni črtni grafikon:

Kako ga izvlečete? No, tam se stvari začnejo nekoliko bolj zapletati. Če pa se le držite spodnjega postopka po korakih, bi se po navodilih moralo počutiti kot sprehod po parku, tudi če ste popoln novinec.

1. korak: Postavite temelje.

Preden začnete, dodajte Razvijalec zavihek do traku, saj vsebuje funkcijo, ki nam omogoča risanje drsnega traku (z desno miškino tipko kliknite kateri koli prazen prostor na traku> prilagodite trak> glavni zavihki> potrdite polje »Razvijalec«> V redu).

Če želite, da stvari delujejo kot ura, dodelite prazno celico za prikaz izhoda drsnika, tako da lahko Excel pravilno postavi drsno polje v drsnik (B8).

2. korak: Narišite drsnik.

Naš naslednji korak je, da v delovni list vstavimo drsnik:

1. Pojdite na Razvijalec zavihek.
2. Pritisnite gumb »Vstavi”Gumb.
3. Spodaj "Kontrolniki obrazcev, "Izberite"Drsni trak.”

Na tej točki lahko povlečete drsni trak, kamor koli želite. Kasneje ga bomo premaknili na svoje mesto.

2. korak: drsni trak povežite s podatki delovnega lista.

Z desno tipko miške kliknite drsnik in izberite »Nadzor oblike.”

V Nadzor oblike pogovorno okno spremenite nastavitve drsnega traku:

• Trenutna vrednost: Ta vrednost določa privzeti položaj drsnega polja v vrstici za drsenje. Vrednost nastavite na »1.”
• Najmanjša vrednost: Ta nastavitev označuje najnižjo vrednost drsnega traku. V našem primeru vnesite »1«Na polje.
• Največja vrednost: Ta parameter določa najvišjo vrednost drsnega traku in mora ustrezati številu podatkovnih točk, ki jih je treba uporabiti za risanje interaktivne karte. Ker podatkovno tabelo sestavlja šest kategorij knjig, spremenite vrednost v »6.”
• Postopna sprememba: Ta vrednost določa, kako gladko drsno polje spremeni svoj položaj, ko ga premaknete. Vrednost vedno nastavite na »1.”
• Sprememba strani: Ta vrednost določa, kako gladko drsno polje spremeni svoj položaj, ko kliknete območje med drsnim poljem in puščicami. Vrednost vedno nastavite na »1.”
• Celična povezava: Ta vrednost označuje trenutni položaj drsnega polja. Preprosto označite celico na delovnem listu, ki je bil prej dodeljen v ta namen ($ B $ 8).

3. korak: Ustvarite tabelo s podatki grafikona.

Ko končate postopek namestitve, ustvarite novo pomožno tabelo za shranjevanje podatkov dinamičnega grafikona, pridobljenih iz neobdelanih podatkov. Podatki grafikona samodejno prilagodijo vrednosti glede na trenutni položaj drsnega polja in učinkovito povežejo drsni trak z grafikonom.

Če želite to narediti, začnite s kopiranjem stolpec Leto kot je prikazano na spodnjem posnetku zaslona (A2: A6).

Ko ste tam, uvedite naslednjo funkcijo INDEX, ki izbere obseg celic, ki vsebuje imena kategorij knjig, in vrne ustrezno vrednost glede na položaj drsnega polja.

V celico vnesite naslednjo formulo B10:

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

Zdaj uvozite podatke o prodaji, ki ustrezajo trenutni kategoriji knjig, tako da vnesete to formulo B11 in ga kopirajte (B11: B14):

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

Kombinacija funkcij INDEX in MATCH pomaga natančno vrniti vrednost številke vrstice in stolpca, ki ustreza določenim merilom.

Formula se najprej filtrira stolpec Leto ($ 3 $: $ 6 dolarjev) in izbere vrstico, ki se ujema z ustreznim letom, v pomožni tabeli (A11). Nato formula na enak način preseja imena kategorij knjig ($ B $ 2: $ G $ 2). Posledično formula vrne pravilno dinamično vrednost, ki se prilagodi vsakič, ko premaknete drsno polje.

4. korak: Ustvarite grafikon na podlagi pomožne tabele.

Ko sestavite tabelo s podatki grafikona, preprosto zgradite preprost grafikon, da vidite drsni trak v akciji.

Označite celotno pomožno tabelo (A10: B14), pojdite na Vstavi zavihek in narišite kateri koli 2-D grafikon-na primer linijski grafikon (Vstavi črtni ali področni grafikon> Vrstica z oznakami).

OPOMBA: Če želite, da se drsni trak prikaže na vrhu grafikona, z desno miškino tipko kliknite drsni trak in izberite »Naročite"In izberite"Prinesi naprej. " (Druga možnost je, da z desno miškino tipko kliknete grafikon in kliknete »Naročite, "In izberite"Pošlji nazaj.”)

In tako to počnete. Pravkar ste se naučili vsega, kar morate vedeti o zmogljivih naprednih tehnikah, ki bodo vašo igro vizualizacije podatkov poskočile.