Когато създавате диаграма в Excel и изходните данни се променят, трябва да актуализирате източника на данни в диаграмата, за да сте сигурни, че отразява новите данни.
В случай, че работите с диаграми, които често се актуализират, е по -добре да създадете динамичен диапазон от диаграми.
Какво е диапазон на динамични диаграми?
Диапазонът на динамична диаграма е диапазон от данни, който се актуализира автоматично, когато промените източника на данни.
Този динамичен диапазон след това се използва като изходни данни в диаграма. С промяната на данните динамичният диапазон се актуализира незабавно, което води до актуализация в диаграмата.
По -долу е даден пример за диаграма, която използва динамичен диапазон от диаграми.
Имайте предвид, че диаграмата се актуализира с новите точки от данни за май и юни веднага след въвеждането на данните.
Как да създадете диапазон на динамични диаграми в Excel?
Има два начина за създаване на динамичен диапазон от диаграми в Excel:
- Използване на Excel таблица
- Използване на формули
В повечето случаи използването на Excel Table е най -добрият начин за създаване на динамични диапазони в Excel.
Нека да видим как работи всеки от тези методи.
Щракнете тук, за да изтеглите примерния файл.
Използване на Excel таблица
Използването на Excel Table е най -добрият начин за създаване на динамични диапазони, тъй като се актуализира автоматично, когато към него се добави нова точка от данни.
Функцията Excel Table е въведена във версията на Excel 2007 на Windows и ако имате версии преди нея, няма да можете да я използвате (вижте следващия раздел за създаване на динамичен диапазон на диаграми с формули).
Професионален съвет: За да преобразувате диапазон от клетки в таблица на Excel, изберете клетките и използвайте клавишната комбинация - Control + T (задръжте клавиша Control и натиснете клавиша T).В примера по -долу можете да видите, че веднага щом добавя нови данни, таблицата на Excel се разширява, за да включи тези данни като част от таблицата (имайте предвид, че рамката и форматирането се разширяват, за да ги включат в таблицата).
Сега трябва да използваме тази таблица в Excel, докато създаваме диаграмите.
Ето точните стъпки за създаване на динамична линейна диаграма с помощта на таблицата на Excel:
- Изберете цялата таблица на Excel.
- Отидете на раздела Вмъкване.
- В групата Графики изберете „Линия с маркери“.
Това е!
Горните стъпки биха вмъкнали линейна диаграма, която автоматично да се актуализира, когато добавите още данни към таблицата на Excel.
Имайте предвид, че докато добавянето на нови данни автоматично актуализира диаграмата, изтриването на данни няма да премахне напълно точките от данни. Например, ако премахнете 2 точки с данни, диаграмата ще покаже малко празно място вдясно. За да коригирате това, плъзнете синята маркировка в долния десен ъгъл на таблицата на Excel, за да премахнете изтритите точки от данни от таблицата (както е показано по -долу).
Въпреки че взех примера с линейна диаграма, можете също да създадете други типове диаграми, като колонни/стълбови диаграми, използвайки тази техника.
Използване на формули на Excel
Както споменах, използването на таблица Excel е най -добрият начин за създаване на динамични диапазони на диаграми.
Ако обаче не можете да използвате таблица на Excel по някаква причина (вероятно, ако използвате Excel 2003), има друг (леко сложен) начин за създаване на динамични диапазони на диаграми с помощта на формули на Excel и именовани диапазони.
Да предположим, че имате набор от данни, както е показано по -долу:
За да създадем динамичен диапазон от диаграми от тези данни, трябва:
- Създайте два динамични именовани диапазона, използвайки формулата OFFSET (по една за колоните „Стойности“ и „Месеци“). Добавянето/изтриването на точка от данни автоматично ще актуализира тези именовани диапазони.
- Вмъкнете диаграма, която използва посочените диапазони като източник на данни.
Нека сега обясня подробно всяка стъпка.
Стъпка 1 - Създаване на динамични именовани диапазони
По -долу са стъпките за създаване на динамични именови диапазони:
- Отидете на раздела „Формули“.
- Кликнете върху „Мениджър на имена“.
- В диалоговия прозорец Мениджър на имена посочете името като ChartValues и въведете следната формула в Отнася се до част: = OFFSET (Формула! $ B $ 2 ,,, COUNTIF (Формула! $ B $ 2: $ B $ 100, ””))
- Щракнете върху OK.
- В диалоговия прозорец Мениджър на имена щракнете върху Ново.
- В диалоговия прозорец Мениджър на имена посочете името като ChartMonths и въведете следната формула в Отнася се за част: = OFFSET (Формула! $ A $ 2 ,,, COUNTIF (Формула! $ A $ 2: $ A $ 100, ””))
- Щракнете върху OK.
- Щракнете върху Затвори.
Горните стъпки създадоха два именовани диапазона в работната книга - ChartValue и ChartMonth (те се отнасят съответно до стойностите и месечния диапазон в набора от данни).
Ако отидете и актуализирате колоната със стойност, като добавите още една точка от данни, именованият диапазон ChartValue сега ще се актуализира автоматично, за да покаже допълнителната точка от данни в нея.
Магията се извършва чрез функцията OFFSET тук.
Във формулата за име на диапазон „ChartValue“ сме посочили B2 като отправна точка. Формулата OFFSET започва оттам и се простира до всички запълнени клетки в колоната.
Същата логика работи и във формулата за име на диапазон ChartMonth.
Стъпка 2 - Създайте диаграма, използвайки тези именовани диапазони
Сега всичко, което трябва да направите, е да вмъкнете диаграма, която ще използва посочените диапазони като източник на данни.
Ето стъпките за вмъкване на диаграма и използване на динамични диапазони на диаграми:
- Отидете на раздела Вмъкване.
- Кликнете върху „Вмъкване на линия или областна диаграма“ и поставете диаграмата „Линия с маркери“. Това ще вмъкне диаграмата в работния лист.
- С избраната диаграма отидете в раздела Дизайн.
- Щракнете върху Избор на данни.
- В диалоговия прозорец „Избор на източник на данни“ кликнете върху бутона Добавяне в „Легендни записи (поредица)“.
- В полето Стойност на серия въведете = Formula! ChartValues (имайте предвид, че трябва да посочите името на работния лист преди именования диапазон, за да работи това).
- Щракнете върху OK.
- Кликнете върху бутона Редактиране в „Хоризонтални етикети на оси (категория)“.
- В диалоговия прозорец „Етикети на ос“ въведете = Формула! ChartMonths
- Щракнете върху OK.
Това е! Сега вашата диаграма използва динамичен диапазон и ще се актуализира, когато добавите/изтриете точки от данни в диаграмата.
Няколко важни неща, които трябва да знаете, когато използвате именовани диапазони с диаграми:
- В данните от диаграмата не трябва да има празни клетки. Ако има празно, именуваният диапазон няма да се отнася до правилния набор от данни (тъй като общият брой би довел до него, отнасящ се до по -малък брой клетки).
- Трябва да следвате договора за именуване, когато използвате името на листа в източника на диаграма. Например, ако името на листа е една дума, например Формула, тогава можете да използвате = Formula! ChartValue. Но ако има повече от една дума, като например диаграма на формулата, тогава трябва да използвате = 'диаграма на формулата'! ChartValue.
