Диаграмма по выделенной ячейке

Предположим, что нам с вами требуется визуализировать данные из вот такой таблицы со значениями продаж автомобилей по разным странам в 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;ЯЧЕЙКА("строка")))

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




25.01.2022 21:41:54
А как добавить подсветку на диаграмме ячейки, которая выбрана?
31.01.2022 09:20:51
Серьезный минус применения макроса для условного форматирования - невозможность скопировать значения в ячейку листа
Обошел путем
создания на листе ячейки с именем "MacrosOff"
Добавления к коду проверки: если ячейка непустая - не выполнять пересчет тогда спокойно можно копировать/вставлять на листе для подготовки данных, а затем включить макрос


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Range("MacrosOff").Value <> "" Then Exit Sub
   Target.Parent.Calculate
End Sub
31.05.2022 13:18:23
Заменил график с маркерами на круг с процентами. Все настроил для одной строки. При переходе к следующей формат сбивается. При возвращении на исходную строку формат также уже сбит. Возможно ли зафиксировать форматы, чтобы переходя от одно строки к другой форматы не сбивались?
19.08.2022 16:10:26
Зайдите в параметры Excel - раздел Диаграммы и уберите галочку с поля Свойства изменяются при изменении точек данных диаграммы для текущей книги. Формат не будет слетать.
19.08.2022 22:49:45
Наталья, огромное спасибо! Все сработало!
24.03.2023 07:36:41
Добрый день! Большое спасибо за такой интересный трюк! Возник вопрос. В видео по этому приёму есть упоминание, что для сравнения исходного графика с какими-то ещё данными можно прописать их ниже таблицы и добавить на график. А как быть, если эти данные постоянно меняются (динамичны)? Скажем, хочется сравнить продажи Австралии не только с Россией, но и с Германией или Бельгией. Подскажите, пожалуйста!
09.04.2023 15:08:54
В гугл таблице такое возможно сделать?  
19.09.2023 21:43:45
Увы... при повторном открытии (сохранён правильно в .xlsm) макрос перестаёт работать (((((
Стоит чекбокс "включить все макросы" - не помогает
Наверх