В прошлой статье мы разобрали динамические графики, построенные с помощью флажков и применения функции ЕСЛИ. Тогда графики появлялись и растворялись по включению и выключению флажков.
Давайте сегодня рассмотрим альтернативную ситуацию, когда нужно, чтобы при выборе команды из выпадающего списка отображался соответствующий график; новая команда – новый график и так далее.
Табличка у нас та же самая. Разница лишь в том, что рядом нам нужно вывести выпадающий список.
Определяемся с ячейкой, переходим на вкладку «Данные», потом «Проверка» данных, в поле «Тип данных» выбираем «Список», а в качестве источника выделяем диапазон (столбец) от Команды 1 до Команды 5.
Иными словами, говорим Excel, какие обозначения будут переключаться в нашем списке.
Проверяем список, на всякий случай.
Теперь самое интересное – прописывание формулы в именованный диапазон. Вот только давайте сначала мы расскажем логику этого построения.
Во главе всего у нас будет стоять функция СМЕЩ. В ней целых 5 аргументов, и многие пользователи с опаской используют данную функцию. Однако, суть её проста, да и аргументы нам понадобятся далеко не все. Если хотите узнать больше полезных функций, то рекомендуем скачать бесплатный гайд «Ключевые формулы Excel».
Пока не заморачивайтесь с тем, куда её нужно писать, лишь поймайте ход мысли. Вот формула:
=СМЕЩ($B$4:$K$4; ПОИСКПОЗ(Магазины!$B$11;Магазины!$A$5:$A$9;0);)
1-й аргумент (ссылка) – $B$2:$M$2. Это шапка (часть шапки) нашей таблицы, то есть кусочек, который как раз и будет всё время перемещаться. Фактически тут мы задаём желаемый диапазон для забора данных.
2-й аргумент (Смещ_по_строкам) – на сколько строк будет осуществляться смещение. Можно задать номер конкретный строки, но нам нужна автоматизация и динамика, чтобы номер строки выбирался в зависимости от определённой команды. Следовательно, когда речь заходит о поиске номера строки, мы обращаемся к функции ПОИСКПОЗ, которая должна брать команду для поиска из ячейки с выпадающим списком ($P$7 у нас), а уже затем перемещаться по строками в диапазоне $A$3:$A$7 (перебирать команды) и максимально точно (3-й аргумент – 0) выдавать строку (её мы прописали в 1-ом аргументе функции СМЕЩ).
Весь фокус – в правильном создании имени.
Нажимаем Ctrl+F3 (Диспетчер имён – Создать) или идём на вкладку «Формулы» и кликаем по «Задать имя».
Имя – Commands (можно выбрать любое, которое в состоянии запомнить), сразу скопируйте его.
Примечание – ничего.
А в диапазоне как раз и вбиваем ту самую зловещую формулу:
=СМЕЩ($B$2:$M$2;ПОИСКПОЗ($P$7;$A$3:$A$7;0);)
Результат работы этой формулы вы можете проверить без «Диспетчера имён»: предварительно вводите эту формулу в любую свободную ячейку и смотрите, чтобы появились числа.
Приступаем к самому волнительному моменту – вставке диаграммы. Выделяем всю нашу таблицу через Ctrl+A или обводкой, затем идём по адресу: «Вставка» – «Диаграмма» – выбираем рекомендованный тип, почему бы и нет.
Кликаем правой кнопкой на поле диаграммы, а затем – «Выбрать данные» (либо с Ленты, в «Конструкторе диаграмм»).
В левом поле под названием «Элементы легенды (ряды)» очищаем все подписи (кнопка «Удалить») и нажимаем «Создать»:
«Имя ряда» – это ячейка с выпадающим списком, просто выбираем её.
В «Значения» мы должны прописать созданное имя, не забыв скрепить его с листом, то есть должно быть так:
=Лист2!Commands
Commands (здесь может быть заданное вами имя) – это тот самый именованный диапазон с главной функцией СМЕЩ.
По сути, готово! Если вы будете переключать команды из выпадающего списка, то график отобразит результаты по конкретному номеру.
Выбирайте решение для того типа задачи, которое вам необходимо, будь то флажки или именованный диапазон с выпадающим списком, и приступайте!
Автор: Павлов Роман, эксперт SF Education