Как да създадете диапазон на динамични диаграми в Excel

Този урок ще покаже как да създадете динамичен диапазон от диаграми във всички версии на Excel: 2007, 2010, 2013, 2016 и 2022.

По подразбиране, когато разширявате или свивате набор от данни, използвани за начертаване на диаграма в Excel, основните източници на данни също трябва да бъдат коригирани ръчно.

Въпреки това, чрез създаване на динамични диапазони на диаграми, можете да избегнете тази неприятност.

Диапазоните на динамичните диаграми ви позволяват автоматично да актуализирате изходните данни всеки път, когато добавяте или премахвате стойности от диапазона от данни, спестявайки много време и усилия.

В този урок ще научите всичко, което трябва да знаете, за да разгърнете силата на Диапазони на динамични диаграми.

Диапазони на динамични диаграми - Въведение

Помислете за следния набор от примерни данни, анализиращи колебанията на маржа на печалбата:

По принцип има два начина за настройване на динамичен диапазон от диаграми:

1. Преобразуване на диапазона от данни в таблица
2. Използване на динамични именови диапазони като изходни данни на диаграмата.

И двата метода имат своите плюсове и минуси, затова ще поговорим за всеки от тях по -подробно, за да ви помогнем да определите кой ще работи най -добре за вас.

Без повече шум, нека започнем.

Методът на таблицата

Нека да започна, като ви покажа най -бързия и лесен начин за изпълнение на поставената задача. И така, ето тренировката: Превърнете диапазона от данни в таблица и сте по-лесни от злато, отколкото да обелвате грах.

По този начин всичко, което въведете в клетките в края на таблицата, автоматично ще бъде включено в изходните данни на диаграмата.

Ето как можете да направите това само с две прости стъпки.

Стъпка #1: Преобразувайте диапазона от данни в таблица.

Точно от портата, трансформирайте диапазона от клетки, съдържащ данните от вашата диаграма, в таблица.

1. Маркирайте целия диапазон от данни (A1: B6).
2. Щракнете върху Вмъкване раздел.
3. Удари "Таблица”Бутон.

В Създаване на таблица диалогов прозорец, направете следното:

1. Проверете отново дали маркираният диапазон от клетки съвпада с цялата таблица с данни.
2. Ако вашата таблица не съдържа заглавен ред, премахнете отметката от „Моята маса има заглавки" кутия.
3. Щракнете върху „ДОБРЕ.“

В резултат на това трябва да завършите с тази таблица:

Стъпка 2: Създайте диаграма въз основа на таблицата.

Основата е положена, което означава, че вече можете да настроите диаграма с помощта на таблицата.

1. Маркирайте цялата таблица (A1: B6).
2. Придвижете се до Вмъкване раздел.
3. Създайте всяка 2-D диаграма. За илюстративни цели, нека създадем проста колонна диаграма (Вмъкване на колона или лентова диаграма> Групирана колона).

Това е! За да тествате техниката, опитайте да добавите нови точки от данни в долната част на масата за да ги видите автоматично нанесени в сюжета. Колко по -просто може да стане?

ЗАБЕЛЕЖКА: При този подход наборът от данни трябва никога съдържа празни клетки в него-това ще съсипе диаграмата.

Методът на динамичния именован диапазон

Макар и лесен за нанасяне, преди това демонстрираното, Табличен метод има някои сериозни недостатъци. Например диаграмата се обърква винаги, когато новият набор от данни се окаже по-малък от първоначалната таблица с данни плюс, понякога просто не искате обхватът на данните да се преобразува в таблица.

Избирането на именовани диапазони може да отнеме малко повече време и усилия от ваша страна, но техниката отрича минусите на метода на таблицата и на всичкото отгоре прави динамичния диапазон много по -удобен за работа в дългосрочен план.

Стъпка 1: Създайте динамичните именовани диапазони.

За да започнете, настройте именуваните диапазони, които в крайна сметка ще се използват като изходни данни за вашата бъдеща диаграма.

1. Отидете на Формули раздел.
2. Щракнете върху „Мениджър на имена.”
3. В Мениджър на имена в диалоговия прозорец, който се показва, изберете „Ново.”

В Ново име диалогов прозорец, създайте чисто нов именен диапазон:

1. Тип "Квартал”До„Име”Поле. За ваше удобство, направете името на динамичния диапазон съвпадащо със съответната клетка на заглавния ред на колона А (А1).
2. В „Обхват”, Изберете текущия работен лист. В нашия случай това е така Лист 1.
3. Въведете следната формула в „Отнася се до”Поле: = OFFSET (Sheet1! $ A $ 2,0,0, COUNTA (Sheet1! $ A: $ A) -1,1)

На обикновен английски, всеки път, когато промените някоя клетка в работния лист, функцията OFFSET връща само действителните стойности в колона А, оставяйки клетката на заглавния ред (А1), докато функцията COUNTA преизчислява броя на стойностите в колоната всеки път, когато работният лист се актуализира-ефективно извършвайки цялата мръсна работа вместо вас.

Нека разбием формулата по -подробно, за да ви помогнем да разберете как работи:

ЗАБЕЛЕЖКА: Името на именован диапазон трябва да започва с буква или долна черта и да не съдържа интервали.

По същия начин настройте друг именован диапазон въз основа на колона Марж на печалбата (колона Б) с помощта на тази формула и я маркирайте „Марж на печалбата”:

1	= OFFSET (Sheet1! $ B $ 2,0,0, COUNTA (Sheet1! $ B: $ B) -1,1)

Повторете същия процес, ако вашата таблица с данни съдържа множество колони с действителни стойности. В нашия случай в резултат на това трябва да имате два именовани диапазона, готови за действие:

Стъпка 2: Създайте празна диаграма.

Преминахме през най -сложната част. Сега е време да настроите празна диаграма, така че да можете ръчно да вмъкнете динамичните именовани диапазони в нея.

1. Изберете всяка празна клетка в текущия работен лист (Лист 1).
2. Върнете се към Вмъкване раздел.
3. Настройте всяка 2-D диаграма, която искате. За нашия пример ще създадем колонна диаграма (Вмъкване на колона или лентова диаграма> Клъстерна колона).

Стъпка #3: Добавете именения диапазон/диапазони, съдържащи действителните стойности.

Първо вмъкнете именования диапазон (Марж на печалбата) свързани с действителните стойности (колона Б) в диаграмата.

Щракнете с десния бутон върху празната диаграма и изберете „Изберете Данни”От контекстното меню.

В Изберете източник на данни диалоговия прозорец, щракнете върху „Добавяне.”

В Редактиране на поредицата box, създайте нова поредица от данни:

1. Под „Име на серия, “Маркирайте съответната клетка от ред в заглавката (В1).
2. Под „Серийни стойности, ”Посочете именования диапазон, който да бъде нанесен на диаграмата, като въведете следното:“= Sheet1! Profit_Margin.”Препратката се състои от две части: имената на текущия работен лист (= Лист 1) и съответния динамичен именен диапазон (Марж на печалбата). Удивителен знак се използва за свързване на двете променливи заедно.
3. Изберете „ДОБРЕ.”

Веднъж там Excel автоматично ще начертае стойностите:

Стъпка #4: Вмъкнете именувания диапазон с етикетите на оста.

И накрая, заменете етикетите на ос по категория по подразбиране с именувания диапазон, състоящ се от колона А (Квартал).

В Изберете източник на данни диалогов прозорец, под „Етикети на хоризонтална ос (категория),”Изберете„редактиране”Бутон.

След това вмъкнете посочения диапазон в диаграмата, като въведете следната справка под „Обхват на етикета на оста:”

1	= Лист1! Тримесечие

И накрая, колоновата диаграма, базирана на динамичния диапазон от диаграми, е готова:

Вижте това: Диаграмата се актуализира автоматично, когато добавяте или премахвате данни в динамичния диапазон.