Содержание:
Предположим, что нам с вами нужно визуализировать данные из следующей таблицы со стоимостью продаж автомобилей по разным странам в 2021 году (реальные данные, кстати, взяты отсюда):
Поскольку количество рядов данных (стран) велико, попытка втиснуть их все сразу в один график приведет либо к ужасной «диаграмме спагетти», либо к построению отдельных графиков для каждого ряда, что очень громоздко.
Элегантным решением этой проблемы может быть построение диаграммы только на основе данных текущей строки, то есть строки, в которой находится активная ячейка:
Реализовать это очень просто — вам понадобятся всего две формулы и один крохотный макрос в 3 строки.
Шаг 1. Текущий номер строки
Первое, что нам нужно — это именованный диапазон, который вычисляет номер строки на листе, где сейчас находится наша активная ячейка. Открытие на вкладке Формулы – Менеджер имен (Формулы — Менеджер имен)нажмите на кнопку Создавай (Создавать) и введите туда следующую структуру:
- Имя — любое подходящее имя для нашей переменной (в нашем случае это TekString)
- Площадь – здесь и далее нужно выбрать текущий лист, чтобы созданные имена были локальными
- Диапазон – здесь мы используем функцию CELL (КЛЕТКА), который может выдавать кучу разных параметров для данной ячейки, включая нужный нам номер строки — за это отвечает аргумент «строка».
Шаг 2. Ссылка на заголовок
Чтобы отобразить выбранную страну в заголовке и легенде диаграммы, нам необходимо получить ссылку на ячейку с ее (страной) названием из первого столбца. Для этого мы создаем еще один локальный (т.е. Площадь = текущий лист, а не Книга!) именованный диапазон по следующей формуле:
Здесь функция ИНДЕКС выбирает из заданного диапазона (столбец A, где лежат наши подписавшие страны) ячейку с номером строки, который мы определили ранее.
Шаг 3. Ссылка на данные
Теперь аналогичным образом получим ссылку на диапазон со всеми данными о продажах из текущей строки, где сейчас находится активная ячейка. Создайте еще один именованный диапазон с помощью следующей формулы:
Здесь третий аргумент, равный нулю, заставляет INDEX возвращать не одно значение, а в результате всю строку.
Шаг 4. Замена ссылок на диаграмме
Теперь выделите заголовок таблицы и первую строку с данными (диапазоном) и постройте на их основе диаграмму с помощью Вставка – Графики (Вставка — Графики). Если вы выберете строку с данными на диаграмме, то функция отобразится в строке формул. РЯД (РЯД) — это специальная функция, которую Excel автоматически использует при создании любой диаграммы для ссылки на исходные данные и метки:
Давайте аккуратно заменим первый (сигнатура) и третий (данные) аргументы в этой функции на имена наших диапазонов из шагов 2 и 3:
На диаграмме начнут отображаться данные о продажах из текущей строки.
Шаг 5. Макрос пересчета
Остается последний штрих. Microsoft Excel пересчитывает формулы только при изменении данных на листе или при нажатии клавиши F9, и мы хотим, чтобы перерасчет происходил при изменении выделения, т. е. при перемещении активной ячейки по листу. Для этого нам нужно добавить в нашу книгу простой макрос.
Щелкните правой кнопкой мыши вкладку таблицы данных и выберите команду Источник (Исходный код). В открывшемся окне введите код макрообработчика события изменения выбора:
Как вы можете легко себе представить, все, что он делает, — это запускает пересчет листа при каждом изменении положения активной ячейки.
Шаг 6. Выделение текущей строки
Для наглядности вы также можете добавить правило условного форматирования, чтобы выделить страну, которая в данный момент отображается на диаграмме. Для этого выделите таблицу и выберите Главная страница — Условное форматирование — Создать правило — Использование формулы для определения ячеек для форматирования (Главная страница — Условное форматирование — Новое правило — Используйте формулу, чтобы определить, какие ячейки форматировать):
Здесь формула проверяет для каждой ячейки таблицы, что номер ее строки соответствует номеру, хранящемуся в переменной TekRow, и если совпадение есть, то срабатывает заливка выбранным цветом.
Вот и все – просто и красиво, правда?
Заметки
- На больших таблицах вся эта красота может тормозить — условное форматирование — вещь ресурсоемкая, да и пересчет для каждой выборки тоже может быть тяжелым.
- Чтобы данные не пропадали на диаграмме при случайном выборе ячейки над или под таблицей, можно добавить к имени TekRow дополнительную проверку с помощью вложенных функций ЕСЛИ вида:
=IF(CELL("строка")<4,ЕСЛИ(CELL("строка")>4,CELL("строка")))
- Выделение указанных столбцов на диаграмме
- Как создать интерактивную диаграмму в Excel
- Выбор координат