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

В этом руководстве будет показано, как создать диапазон динамических диаграмм во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

По умолчанию, когда вы расширяете или сокращаете набор данных, используемый для построения диаграммы в Excel, базовые исходные данные также должны быть скорректированы вручную.

Однако, создав динамические диапазоны диаграмм, вы можете избежать этой проблемы.

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

В этом уроке вы узнаете все, что вам нужно знать, чтобы раскрыть всю мощь Диапазоны динамических диаграмм.

Диапазоны динамических диаграмм - Введение

Рассмотрим следующий набор данных для анализа колебаний прибыли:

В принципе, есть два способа настроить динамический диапазон диаграммы:

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

У обоих методов есть свои плюсы и минусы, поэтому мы поговорим о каждом из них более подробно, чтобы помочь вам определить, какой из них лучше всего подойдет вам.

Без лишних слов, приступим.

Табличный метод

Позвольте мне начать с того, что покажу вам самый быстрый и простой способ выполнить поставленную задачу. Итак, вот упражнение: превратите диапазон данных в таблицу, и вам будет проще, чем чистить горох.

Таким образом, все, что вы вводите в ячейки в конце этой таблицы, будет автоматически включено в исходные данные диаграммы.

Вот как это сделать всего за два простых шага.

Шаг №1: Преобразуйте диапазон данных в таблицу.

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

1. Выделите весь диапазон данных (A1: B6).
2. Щелкните значок Вставлять таб.
3. Ударь "Стол" кнопка.

в Создать таблицу диалоговом окне выполните следующие действия:

1. Дважды проверьте, соответствует ли выделенный диапазон ячеек всей таблице данных.
2. Если в вашей таблице нет строки заголовка, снимите флажок «В моей таблице есть заголовки" коробка.
3. Нажмите «OK.“

В результате у вас должна получиться такая таблица:

Шаг №2: Создайте диаграмму на основе таблицы.

Фундамент заложен, а это значит, что теперь вы можете составить график с помощью таблицы.

1. Выделите всю таблицу (A1: B6).
2. Перейдите к Вставлять таб.
3. Создайте любую двумерную диаграмму. В целях иллюстрации давайте создадим простую столбчатую диаграмму (Вставить столбец или гистограмму> Кластерный столбец).

Вот и все! Чтобы протестировать технику, попробуйте добавить новые точки данных. внизу таблицы чтобы увидеть их автоматически на графике. Насколько это может быть проще?

ЗАМЕТКА: При таком подходе набор данных должен никогда содержать в нем пустые ячейки - это испортит график.

Метод динамического именованного диапазона

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

Выбор именованных диапазонов может занять немного больше времени и усилий с вашей стороны, но эта техника сводит на нет недостатки табличного метода и, вдобавок ко всему, делает динамический диапазон намного более удобным для работы в долгосрочной перспективе.

Шаг №1: Создайте динамические именованные диапазоны.

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

1. Перейти к Формулы таб.
2. Нажмите «Имя Manager.”
3. в Имя Менеджер в появившемся диалоговом окне выберите «Новый.”

в Новое имя диалоговом окне создайте новый именованный диапазон:

1. Тип "Четверть" сразу после "Имя" поле. Для вашего удобства сделайте так, чтобы имя динамического диапазона соответствовало соответствующей ячейке строки заголовка столбец А (A1).
2. В "Сфера»Выберите текущий рабочий лист. В нашем случае это Лист1.
3. Введите следующую формулу в поле «Относится к" поле: = СМЕЩЕНИЕ (Лист1! $ A $ 2,0,0; СЧЁТ (Лист1! $ A: $ A) -1,1)

Говоря простым языком, каждый раз, когда вы изменяете любую ячейку на листе, функция СМЕЩЕНИЕ возвращает только фактические значения в столбец А, исключая ячейку строки заголовка (A1), а функция COUNTA пересчитывает количество значений в столбце каждый раз при обновлении рабочего листа, фактически делая всю грязную работу за вас.

Давайте разберем формулу более подробно, чтобы помочь вам понять, как она работает:

ЗАМЕТКА: Имя именованного диапазона должно начинаться с буквы или символа подчеркивания и не должно содержать пробелов.

Точно так же настройте другой именованный диапазон на основе столбец Маржа прибыли (столбец B) с помощью этой формулы и обозначьте ее "Рентабельность”:

1	= СМЕЩЕНИЕ (Лист1! $ B $ 2,0,0; СЧЁТ (Лист1! $ B: $ B) -1,1)

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

Шаг № 2: Создайте пустую диаграмму.

Мы прошли через самую сложную часть. Теперь пришло время создать пустую диаграмму, чтобы вы могли вручную вставить в нее динамические именованные диапазоны.

1. Выберите любую пустую ячейку на текущем листе (Лист1).
2. Вернитесь к Вставлять таб.
3. Настройте любую двумерную диаграмму, которую хотите. В нашем примере мы создадим столбчатую диаграмму (Вставить столбец или гистограмму> Кластерный столбец).

Шаг № 3: Добавьте именованный диапазон / диапазоны, содержащие фактические значения.

Сначала вставьте именованный диапазон (Рентабельность) связаны с фактическими значениями (столбец B) в график.

Щелкните правой кнопкой мыши пустую диаграмму и выберите «Выбрать данные»Из контекстного меню.

в Выберите источник данных в диалоговом окне нажмите «Добавлять.”

в Редактировать серию поле, создайте новую серию данных:

1. Под "Название серии, »Выделите соответствующую ячейку строки заголовка (B1).
2. Под "Значения серии, »Укажите именованный диапазон, который будет отображаться на диаграмме, набрав следующее:«= Лист1! Маржа_прибыли.»Справка состоит из двух частей: названия текущего рабочего листа. (= Лист1) и соответствующий динамический именованный диапазон (Рентабельность). Восклицательный знак используется для связывания двух переменных вместе.
3. Выбирать "OK.”

Оказавшись там, Excel автоматически отобразит значения:

Шаг №4: Вставьте именованный диапазон с метками осей.

Наконец, замените метки оси категорий по умолчанию на именованный диапазон, состоящий из столбец А (Четверть).

в Выберите источник данных диалоговое окно в разделе «Ярлыки горизонтальной оси (категории),"Выберите"Редактировать" кнопка.

Затем вставьте названный диапазон в диаграмму, указав следующую ссылку в разделе «Диапазон меток оси:”

1	= Лист1! Квартал

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

Проверьте это: диаграмма автоматически обновляется всякий раз, когда вы добавляете или удаляете данные в динамическом диапазоне.