Ko ustvarite grafikon v Excelu in se izvorni podatki spremenijo, morate posodobiti vir podatkov grafikona, da se prepričate, da odraža nove podatke.
Če delate s grafikoni, ki se pogosto posodabljajo, je bolje ustvariti dinamični obseg grafikonov.
Kaj je dinamični razpon grafikonov?
Dinamični obseg grafikona je obseg podatkov, ki se samodejno posodobi, ko spremenite vir podatkov.
Ta dinamični razpon se nato uporabi kot izvorni podatek v grafikonu. Ko se podatki spreminjajo, se dinamični razpon takoj posodobi, kar vodi do posodobitve v grafikonu.
Spodaj je primer grafikona, ki uporablja dinamični obseg grafikona.
Upoštevajte, da se grafikon posodobi z novimi podatkovnimi točkami za maj in junij takoj, ko so vneseni podatki.
Kako ustvariti dinamični razpon grafikonov v Excelu?
Dinamični obseg grafikonov v Excelu lahko ustvarite na dva načina:
- Uporaba Excelove tabele
- Uporaba formul
V večini primerov je uporaba Excelove tabele najboljši način za ustvarjanje dinamičnih obsegov v Excelu.
Poglejmo, kako deluje vsaka od teh metod.
Kliknite tukaj, če želite prenesti primer datoteke.
Uporaba Excelove tabele
Uporaba Excelove tabele je najboljši način za ustvarjanje dinamičnih obsegov, saj se samodejno posodobi, ko ji dodate novo podatkovno točko.
Funkcija Excelove tabele je bila predstavljena v različici sistema Windows 2007 za Windows 2007 in če ste pred tem različice, je ne boste mogli uporabljati (glejte naslednji razdelek o ustvarjanju dinamičnega obsega grafikonov s formulami).
Nasvet za profesionalce: Če želite pretvoriti obseg celic v Excelovo tabelo, izberite celice in uporabite bližnjico na tipkovnici - Control + T (pridržite tipko Control in pritisnite tipko T).V spodnjem primeru lahko vidite, da se tabela Excel takoj, ko dodam nove podatke, razširi in te podatke vključi v del tabele (upoštevajte, da se obroba in oblikovanje razširita, da ju vključita v tabelo).
Zdaj moramo pri ustvarjanju grafikonov uporabiti to Excelovo tabelo.
Tukaj so natančni koraki za ustvarjanje dinamičnega linijskega grafikona z uporabo tabele Excel:
- Izberite celotno tabelo programa Excel.
- Pojdite na zavihek Vstavljanje.
- V skupini grafikonov izberite grafikon »Črta z označevalci«.
To je to!
Zgornji koraki bi vstavili črtni grafikon, ki bi se samodejno posodobil, ko v tabelo Excel dodate več podatkov.
Upoštevajte, da medtem ko dodajanje novih podatkov samodejno posodablja grafikon, brisanje podatkov ne bo v celoti odstranilo podatkovnih točk. Če na primer odstranite 2 podatkovni točki, bo grafikon na desni strani pokazal nekaj praznega prostora. Če želite to popraviti, povlecite modro oznako v spodnjem desnem kotu Excelove tabele, da iz tabele odstranite izbrisane podatkovne točke (kot je prikazano spodaj).
Čeprav sem vzel primer linijskega grafikona, lahko s to tehniko ustvarite tudi druge vrste grafikonov, kot so stolpci/stolpci.
Uporaba Excelovih formul
Kot sem že omenil, je uporaba Excelove tabele najboljši način za ustvarjanje dinamičnih razponov grafikonov.
Če pa iz nekega razloga ne morete uporabiti tabele Excel (po možnosti, če uporabljate Excel 2003), obstaja še en (nekoliko zapleten) način za ustvarjanje dinamičnih obsegov grafikonov z uporabo Excelovih formul in imenovanih obsegov.
Recimo, da imate nabor podatkov, kot je prikazano spodaj:
Če želimo iz teh podatkov ustvariti dinamični razpon grafikonov, moramo:
- Ustvarite dva dinamična poimenovana obsega s formulo OFFSET (po eno za stolpca »Vrednosti« in »Meseci«). Dodajanje/brisanje podatkovne točke bi samodejno posodobilo te imenovane obsege.
- Vstavite grafikon, ki uporablja imenovane obsege kot vir podatkov.
Naj zdaj podrobno razložim vsak korak.
Korak 1 - Ustvarjanje dinamičnih imenovanih obsegov
Spodaj so navedeni koraki za ustvarjanje dinamičnih poimenovanih obsegov:
- Pojdite na zavihek "Formule".
- Kliknite »Upravitelj imen«.
- V pogovornem oknu Upravitelj imen podajte ime kot ChartValues in vnesite naslednjo formulo v Se nanaša na del: = OFFSET (Formula! $ B $ 2 ,,, COUNTIF (Formula! $ B $ 2: $ B $ 100, ""))
- Kliknite V redu.
- V pogovornem oknu Upravitelj imen kliknite Novo.
- V pogovornem oknu Upravitelj imen podajte ime kot ChartMonths in vnesite naslednjo formulo v Se nanaša na del: = OFFSET (Formula! $ A $ 2 ,,, COUNTIF (Formula! $ A $ 2: $ A $ 100, ""))
- Kliknite V redu.
- Kliknite Zapri.
Zgornji koraki so ustvarili dva poimenovana obsega v delovnem zvezku - ChartValue in ChartMonth (ta se nanašata na vrednosti in mesečno območje v naboru podatkov).
Če odprete stolpec vrednosti in dodate še eno podatkovno točko, se bo imenovano območje ChartValue zdaj samodejno posodobilo in prikazalo dodatno podatkovno točko v njem.
Čarovnijo naredi funkcija OFFSET tukaj.
V formuli imenovanega obsega „ChartValue“ smo kot referenčno točko določili B2. Formula OFFSET se začne tam in sega do vseh zapolnjenih celic v stolpcu.
Ista logika deluje tudi v formuli imenovanega obsega ChartMonth.
2. korak - Ustvarite grafikon z uporabo teh poimenovanih razponov
Zdaj morate le vstaviti grafikon, ki bo imenovane obsege uporabil kot vir podatkov.
Tukaj so koraki za vstavljanje grafikona in uporabo dinamičnih razponov grafikonov:
- Pojdite na zavihek Vstavljanje.
- Kliknite »Vstavi vrstico ali površinski grafikon« in vstavite grafikon »Črta z oznakami«. To bo grafikon vstavilo na delovni list.
- Ko je grafikon izbran, pojdite na zavihek Oblikovanje.
- Kliknite Izberi podatke.
- V pogovornem oknu »Izberi vir podatkov« kliknite gumb Dodaj v »Vnosi legend (serija)«.
- V polje Vrednost serije vnesite = Formula! ChartValues (upoštevajte, da morate pred imenovanim obsegom podati ime delovnega lista, da bo to delovalo).
- Kliknite V redu.
- Kliknite gumb Uredi v "Vodoravne oznake osi kategorije".
- V pogovornem oknu »Oznake osi« vnesite = Formula! ChartMonths
- Kliknite V redu.
To je to! Zdaj vaš grafikon uporablja dinamični razpon in se bo posodobil, ko dodate/izbrišete podatkovne točke v grafikonu.
Pri uporabi imenovanih obsegov s grafikoni morate vedeti nekaj pomembnih stvari:
- V podatkih grafikona ne sme biti praznih celic. Če je prazno, se imenovano območje ne bi nanašalo na pravilen nabor podatkov (saj bi ga skupno število vodilo do manjšega števila celic).
- Pri uporabi imena lista v viru grafikona morate upoštevati konvencijo poimenovanja. Na primer, če je ime lista ena beseda, na primer Formula, lahko uporabite = Formula! ChartValue. Če pa obstaja več besed, na primer grafikon formule, morate uporabiti = 'grafikon formule'! ChartValue.
