Подсветка заданных столбцов на диаграмме
Предположим, что мы построили простую гистограмму для наглядного отображения значений по нескольким категориям - например прибыли по городам-филиалам:
Задача: выделить цветом нужный столбец(ы) по заданному условию, например город с максимальными продажами или город выбранный в выпадающем списке и т.д. Причем сделать это не вручную поменяв цвет заливки столбца, а "на автопилоте", т.к. завтра данные поменяются и подсветка должна также обновиться. Другими словами, нужно реализовать что-то типа условного форматирования, но не для ячеек, а для столбцов в диаграмме.
Суть способа
Идея, на самом деле, простая. Добавим к нашей таблице с данными еще один вспомогательный столбец:
Обратите внимание, что:
- Напротив города, который мы хотим подсветить (в примере - Королев), стоит его же значение.
- Во всех остальных строчках мы намеренно создаем ошибку #Н/Д! с помощью функции НД() без аргументов (в английской версии - NA())
Если теперь построить гистограмму по такой таблице или добавить вспомогательный столбец как новый ряд в имеющуюся диаграмму, то мы увидим прежнюю картинку, но нужный нам столбец будет задублирован в новом ряду данных:
Теперь главное: щелкаем правой кнопкой мыши по любому столбцу, выбираем команду Формат ряда данных (Format Data Series) и в появившемся окне двигаем ползунок Перекрытие рядов (Overlap) на 100%:
|
|
Дубликат столбца наедет на исходный, полностью его перекрывая, но, по факту, выглядеть это будет как подсветка заданной колонки. А ячейки, в которых есть любые ошибки на диаграммах не отображаются. Вместо НД также можно выводить пустую строку "" - эффект примерно тот же.
Ну, а если использовать в дополнительном столбце формулы, то можно реализовать подсветку по любым критериям - простор для фантазии тут большой. Давайте разберем несколько примеров для затравки.
Подсветка столбца в диаграмме с выбором из выпадающего списка
Предположим, что мы сделали выпадающий список в ячейке G2 для выбора нужного города с помощью классической команды Данные - Проверка данных (Data - Data Validation) и указали в качестве источника наш список городов A2:A15:
Теперь можно во вспомогательном столбце подсветки ввести простую формулу, которая будет подсвечивать именно выбранный город:
Как легко сообразить, функция ЕСЛИ (IF) проверяет тот ли это город, который нам нужен, и выводит его значение либо генерирует ошибку #Н/Д.
Подсветка самого большого или маленького столбца
Очень похоже на предыдущий способ, но функцией ЕСЛИ по очереди проверяем- равно ли значение продаж для каждого города максимальному по столбцу:
Само-собой, можно функцию МАКС заменить на МИН.
Подсветка столбцов выше/ниже среднего
А если заменить в предыдущей формуле знак равно на больше или меньше, а функцию МАКС на СРЗНАЧ (AVERAGE), то можно подсвечивать все города, где продажи были выше или ниже среднего по стране:
Подсветка Top-3
Если вместо функции МАКС, которая находит только одно самое большое значение, использовать функцию НАИБОЛЬШИЙ (LARGE), то можно легко подсветить Top-3:
Ну, и так далее - думаю, идею вы уже уловили.
Ссылки по теме
- Как построить в Excel диаграмму, где ширина столбцов - переменная и тоже несет смысл
- Несколько способов визуализировать соотношение План-Факт на диаграмме в Excel
- Как быстро добавить новые ряды с данными в готовую диаграмму
В первую очередь спасибо огромное за этот сайт и возможности, которые он открывает!
Ну а теперь сам вопрос, возможно ли такое же сделать на объемных круговых диаграммах?
Если да то как? Вопрос очень актуален сейчас.
Заранее благодарю за разъяснения.