Подсветка заданных столбцов на диаграмме

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

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

Задача: выделить цветом нужный столбец(ы) по заданному условию, например город с максимальными продажами или город выбранный в выпадающем списке и т.д. Причем сделать это не вручную поменяв цвет заливки столбца, а "на автопилоте", т.к. завтра данные поменяются и подсветка должна также обновиться. Другими словами, нужно реализовать что-то типа условного форматирования, но не для ячеек, а для столбцов в диаграмме.

Суть способа

Идея, на самом деле, простая. Добавим к нашей таблице с данными еще один вспомогательный столбец:

Вспомогательный столбец подсветки

Обратите внимание, что:

  • Напротив города, который мы хотим подсветить (в примере - Королев), стоит его же значение.
  • Во всех остальных строчках мы намеренно создаем ошибку #Н/Д! с помощью функции НД() без аргументов (в английской версии - NA())

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

Дубликат столбца

Теперь главное: щелкаем правой кнопкой мыши по любому столбцу, выбираем команду Формат ряда данных (Format Data Series) и в появившемся окне двигаем ползунок Перекрытие рядов (Overlap) на 100%:

 Перекрытие рядов в диаграмме     Результат    

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

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

Подсветка столбца в диаграмме с выбором из выпадающего списка

Предположим, что мы сделали выпадающий список в ячейке G2 для выбора нужного города с помощью классической команды Данные - Проверка данных (Data - Data Validation) и указали в качестве источника наш список городов A2:A15:

Выпадающий список

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

Подсветка города из выпадающего списка

Как легко сообразить, функция ЕСЛИ (IF) проверяет тот ли это город, который нам нужен, и выводит его значение либо генерирует ошибку #Н/Д.

Подсветка самого большого или маленького столбца

Очень похоже на предыдущий способ, но функцией ЕСЛИ по очереди проверяем- равно ли значение продаж для каждого города максимальному по столбцу:

Подсветка самого большого или маленького столбца

Само-собой, можно функцию МАКС заменить на МИН.

Подсветка столбцов выше/ниже среднего

А если заменить в предыдущей формуле знак равно на больше или меньше, а функцию МАКС на СРЗНАЧ (AVERAGE), то можно подсвечивать все города, где продажи были выше или ниже среднего по стране:

Подсветка столбцов выше-ниже среднего

Подсветка Top-3

Если вместо функции МАКС, которая находит только одно самое большое значение, использовать функцию НАИБОЛЬШИЙ (LARGE), то можно легко подсветить Top-3:

Подсветка Топ-3 столбцов на диаграмме

Ну, и так далее - думаю, идею вы уже уловили.

Ссылки по теме




29.09.2017 04:55:00
Великолепно. Давно искал решение. Спасибо.
30.09.2017 09:55:30
Здорово! Даже если печатать ч/б вариант выбираем узорную заливку столбца и радуемся). Спасибо, Николай!
30.09.2017 12:47:02
Не за что ;)
09.01.2018 06:27:13
гениально!!! :)
15.09.2018 20:49:18
Как тоже самое сделать в сводной диаграмме? Там запрещены ссылки и массивы! Выхода пока не нашел...:)
а как сделать подсветку текущей даты на диаграмме Ганта
Наверх