Динамическая диаграмма в Excel

Возможно ли в Excel создать динамическую диаграмму? В определенном смысле да. Если под динамическим понимать, автоматическое дополнение диаграммы новыми данными и выделение максимального и минимального значения. Динамическая диаграмма в Excel – возможна.

Исходные данные

Итак, пусть будут такие данные:

Есть некие значения, характеризующие определенный месяц. Это могут быть продажи, производство товаров, да что угодно. Необходимо на основе этих данных построить гистограмму с группировкой. Если делать как обычно, то есть выделить весь диапазон, или просто встав на ячейке с данными выбрать «Вставка» — «Гистограмма» — «Гистограмма с группировкой». Диаграмма будет вставлена, но это будет диаграмма на основе данных. Диаграммы такого типа не поддерживают первый необходимый элемент динамичности, а именно динамическое добавление колонок при внесении новых строк в таблицу.

Динамическое добавление колонок

Только диаграммы, основанные на таблицах Excel, поддерживают динамическое изменение количества строк. Для того что бы из диапазона ячеек В2:С8 сделать таблицу, так же достаточно встать на любой ячейки и выбрать меню «Вставка» — «Таблица», или нажать комбинацию клавиш Ctrl+T.

Откроется диалоговое окно добавления таблицы. В поле для ввода, диалогового окна будет указан диапазон ячеек, которые будут составлять таблицу. Убедимся, что в таблицу входят все ячейки и нажмем кнопку «ОК». Вместо диапазона ячеек, должна будет появиться таблица.

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

Пока что всё как обычно. Где же динамичность? На самом деле она уже есть. Если перейти в ячейку В9, которая не является частью таблицы и ввести данные, в нашем случае это будет «Июль», то благодаря свойству таблиц, новая строка будет включена в состав таблицы, произойдет авто расширение таблицы. Так как диаграмма связанна не с диапазоном ячеек, а с таблицей то произойдет добавление нового столбца в диаграмму. Вот так:

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

Выделение столбцов, максимальное-минимальное

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

Во первых, как определить максимальный или минимальный столбец? Только визуально, для того что бы в данных столбец с максимальным значением был выделен другим цветом, добавим еще один столбец в таблицу. Назовем столбец «Максимум» и запишем в нем такую формулу:

=ЕСЛИ([@Показатель]=МАКС([Показатель]);[@Показатель];НД())

Да, не забываем про особенность таблиц к авто расширению. Для того что бы добавить столбец в таблицу достаточно в соседнем с последним столбцом таблицы ввести значение, так в нашем случае введя  в поле D3 значение «Максимум» мы добавим столбец в таблицу. В первой строке таблицы введем формулу и при нажатии на всплывающее меню распространим действие формулы на весь столбец (но можно и привычным образом растянуть формулу на весь столбец).

Обратите внимание, что в формуле используются специальные табличные ссылки, а не привычные вида «столбец-строка». Если табличная ссылка начинается с символа «@» — это означает текущую строку, столбец с указанным именем. Если имя столбца указанно просто в квадратных скобках – это означает ссылку на столбец целиком. Таким образом, наша формула проверяет если значение в текущей строке, столбца «Показатель» равно максимальному значению столбца «Показатель», то выводится значение текущей строки столбца «Показатель», если значение не равно, выводится «#Н/Д» — нет данных. Специальное значение «#Н/Д» используем, что бы все столбцы, где значение не максимальное отсутствовали в диаграмме. После ввода формулы наша таблица и диаграмма будут иметь вид:

Минимальное значение

Эм, пока немного странно выглядит, но это не страшно. На самом деле добавив новый столбец в таблицу, мы добавили и новый столбец в диаграмму, что логично. У нас в таблице два одинаковых максимальных значения, поэтому и два столбца красного цвета. Особенностью гистограмм является использование одного цвета для значений одной группы (одного столбца), другой столбец другой цвет. Если необходимо изменить цвет, то лучше это делать на этом этапе. Как изменить цвет и настроить внешний вид диаграммы не относится к этой статье, поэтому рассматривать этот аспект не будем.

Теперь добавим еще один столбец в таблицу, назвав его «Минимум» и введя в него формулу:

=ЕСЛИ([@Показатель]=МИН([Показатель]);[@Показатель];НД())

Формула аналогична предыдущей, за тем исключением что вместо функции «МАКС» используем «МИН», ведь проверяем мы на минимальное значение. В итоге получим такой вид таблицы и диаграммы.

Да у элемента «Январь» в диаграмме появился еще один столбец. В нашей диаграмме каждый столбец, прижат вплотную к соседнему, на каждый элемент по три столбца. Просто для элементов значения, которых не являются ни максимальным, ни минимальным, нет данных, а значит, столбцы в диаграмме отсутствуют. То, что столбца именно три можно убедиться на примере зеленого минимального показателя, между синим и зеленым пиком есть промежуток. Тогда как в максимальных элементах между синим и красным промежутка нет. Всё потому что для элемента «Январь» значение «Максимум» отсутствует, те равно «#Н/Д».

Параметры ряда диаграммы

А сейчас самое интересное. Выделим нажатием левой кнопкой мыши синие столбцы в диаграмме. Или еще быстрее сразу нажатием правой кнопки мыши выделим столбцы и вызовем контекстное меню, в котором выберем пункт «Формат ряда данных».

В открывшемся диалоговом окне, в разделе «Параметры ряда» первый ползунок называется «Перекрытие рядов» по умолчанию его значение – 0%. Переместим его вправо к надписи «С перекрытием», или введем значение 100 в поле для ввода. Вот так:

Нажмем кнопку «Закрыть» что бы избавиться от диалогового окна. Почему закрыть, а не привычное «ОК», да потому что изменения вносимые в этом диалоговом окне тут же отражаются в диаграмме, и если у вас из под диалогового окна была видна диаграмма, то вы уже увидели что вместо трех столбцов остался только один. Два других оказались просто перекрыты. Каждая следующая колонка перекрывает предыдущую, где одна колонка остался синий столбец. Где были синий и красный, следующий красный столбец перекрыл синий, а так как зеленого не было, в диаграмме виден красный столбец. Для минимального значения получалось, что синий должен перекрываться следующим, но в следующем красном нет значения, он пустой и невидимый, зато есть третий зеленый он то и перекрывает первый. Так наша диаграмма приняла красивый законченный вид:

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

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