Диаграмма по выделенной ячейке
Предположим, что нам с вами требуется визуализировать данные из вот такой таблицы со значениями продаж автомобилей по разным странам в 2021 году (реальные данные, взятые отсюда, кстати):
Поскольку количество рядов данных (стран) велико, то попытка запихнуть их все сразу в один график приведёт либо к ужасной "спагетти-диаграмме", либо к построению отдельных диаграмм на каждый ряд, что весьма громоздко.
Изящным решением этой проблемы может стать построение диаграммы только по данным из текущей строки, т. е. строки, где стоит активная ячейка:
Реализовать такое очень легко - потребуется лишь две формулы и один крохотный макрос в 3 строки.
Шаг 1. Номер текущей строки
Первое, что нам потребуется - это именованный диапазон, вычисляющий номер строки на листе, где сейчас стоит наша активная ячейка. Открываем на вкладке Формулы - Диспетчер имен (Formulas - Name manager), жмём на кнопку Создать (Create) и вводим туда следующую конструкцию:
- Имя - любое подходящее имя для нашей переменной (в нашем случае это ТекСтрока)
- Область - здесь и далее нужно выбрать текущий лист, чтобы создаваемые имена были локальными
- Диапазон - тут используем функцию ЯЧЕЙКА (CELL), которая умеет выдавать кучу разных параметров для заданной ячейки, в том числе и нужный нам номер строки - за это отвечает аргумент "строка".
Шаг 2. Ссылка на заголовок
Для отображения выбранной страны в заголовке и легенде диаграммы, нам нужно получить ссылку на ячейку с её (страны) названием из первого столбца. Для этого создаём еще один локальный (т.е. Область = текущий лист, а не Книга!) именованный диапазон со следующей формулой:
Здесь функция ИНДЕКС выбирает из заданного диапазона (столбца А, где лежат наши страны-подписи) ячейку с номером строки, который мы до этого определили.
Шаг 3. Ссылка на данные
Теперь аналогичным образом давайте получим ссылку на диапазон со всеми данными по продажам из текущей строки, где стоит сейчас активная ячейка. Создаём ещё один именованный диапазон со следующей формулой:
Здесь третий аргумент равный нулю заставляет ИНДЕКС вернуть в качестве результата не отдельное значение, а всю строку.
Шаг 4. Подставляем ссылки в диаграмму
Теперь выделим шапку таблицы и первую строку с данными (диапазон ) и построим по ним диаграмму через Вставка - Диаграммы (Insert - Charts). Если выделить на диаграмме ряд с данными, то в строке формул отобразится функция РЯД (SERIES) - специальная функция, которую Excel автоматически использует при создании любой диаграммы, чтобы сослаться на исходные данные и подписи:
Аккуратно подменим в этой функции первый (подпись) и третий (данные) аргументы названиями наших диапазонов с шагов 2 и 3:
Диаграмма начнет отображать данные по продажам из текущей строки.
Шаг 5. Макрос пересчета
Остался последний штрих. Microsoft Excel пересчитывает формулы только при изменении данных на листе или при нажатии на клавишу F9, а мы хотим, чтобы пересчёт происходил при изменении выделения, т. е. при любом перемещении активной ячейки по листу. Для этого потребуется добавить в нашу книгу простой макрос.
Щёлкните правой кнопкой мыши по ярлычку листа с данными и выберите команду Исходный код (Source code). В открывшееся окно введём код макроса-обработчика события изменения выделения:
Как легко сообразить, всё, что он делает - это запускает пересчет листа при любом изменении положения активной ячейки.
Шаг 6. Подсветка текущей строки
Для наглядности, можно добавить ещё и правило условного форматирования для выделения цветом страны, которая сейчас отображается на графике. Для этого выделим таблицу и выберем Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - New rule - Use a formula to determine which cells to format):
Здесь формула проверяет для каждой ячейки в таблице совпадение её номера строки с тем номером, что хранится в переменной ТекСтрока, и если совпадение имеет место, то срабатывает заливка выбранным цветом.
Вот и всё - просто и красиво, правда?
Примечания
- На больших таблицах вся эта красота может тормозить - условное форматирование штука ресурсоёмкая, да и пересчёт на каждое выделение тоже может быть тяжеловат.
- Чтобы на графике не пропадали данные при случайном выделении ячейки выше или ниже таблицы, можно добавить в имя ТекСтрока дополнительную проверку вложенными функциями ЕСЛИ вида:
=ЕСЛИ(ЯЧЕЙКА("строка")<4;4;ЕСЛИ(ЯЧЕЙКА("строка")>20;20;ЯЧЕЙКА("строка")))
Ссылки по теме
- Подсветка заданных столбцов на диаграмме
- Как создать в Excel интерактивную диаграмму
- Координатное выделение
создания на листе ячейки с именем "MacrosOff"
Добавления к коду проверки: если ячейка непустая - не выполнять пересчет тогда спокойно можно копировать/вставлять на листе для подготовки данных, а затем включить макрос
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("MacrosOff").Value <> "" Then Exit Sub
Target.Parent.Calculate
End Sub
Стоит чекбокс "включить все макросы" - не помогает