Динамические графики нравятся всем, это факт!
Чего? Что такое «динамические графики»? Да вы шутите…
Ставите флажок – появляется график. Ещё флажок – ещё график. Убираете флажок – картинка исчезает. То же самое легко воспроизвести даже из выпадающего списка. При этом вам будет достаточно обычного Excel, с версией, которая вышла ещё при Царе Горохе.
В этой статье мы рассмотрим построение динамических графиков с помощью флажков, а следующей – уже посредством именованных диапазонов. Если хотите узнать еще больше о возможностях Excel, то рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel».
У нас есть простая табличка (но вы можете взять и сложную) с выручкой различных команд по месяцам. Нам нужно, чтобы показатели каждой команды выводились в виде интерактивного панно, то есть на одном поле диаграммы, разными графиками, чтобы они могли появляться и исчезать по нажатию на любой тумблер.
Отталкиваться будем от функции ЕСЛИ, вставки флажков и промышленного жульничества.
Создадим новый столбец слева от столбца с командами, хотя его расположение совершенно непринципиально.
Напоминаем, что для быстрой вставки столбца можно кликнуть левой кнопкой мыши по ближайшему и нажать комбинацию Ctrl и +, и плюс этот должен находиться на числовой панели (NumPad), иначе не сработает (удалить столбец можно на Ctrl и –). Либо правой кнопкой по названию столбца, затем «Вставить», и столбец появится автоматически.
Далее нам понадобятся флажки. Флажки находятся на вкладке «Разработчик».
Если у вас нет вкладки разработчик, кликните правой кнопкой мыши по пустому месте на Ленте, выберите «Настройка Ленты» и в правом поле справа поставьте галочку на вкладку «Разработчик». Потом ОК.
Теперь вкладка «Разработчик» появилась на Ленте.
Нажимаем на команду «Вставить» и в блоке «Элементы управления форм» выбираем «Флажок». Далее «рисуем» (растягиваем) этот флажок в ячейке слева от «Команда 1». Excel помогает вам и автоматически подстраивает флажок под размер ячейки.
Кликаем по флажку правой кнопкой мыши (чтобы подсветились границы объекта), стираем дежурную надпись «Флажок» (кликните по полю с курсором, если нужно) и выравниваем этот «квадратик», чтобы было красиво.
Копируем флажок в ячейки ниже: клик правой кнопкой мыши по флажку – копировать. Кликаем по ячейке – вставить. Можно быстро выделять ячейки (ибо объект уже в буфере обмена) и нажимать Ctrl+V. Классика.
Одной только вставки флажка недостаточно, чтобы он заработал. Галочка будет появляться и исчезать – но толку?
Первое, что надо сделать после создании флажка – указать связывающую ячейку, в которой как раз и будут переключаться значения ИСТИНА (ВКЛ) и ЛОЖЬ (ВЫКЛ). Ход вычислений, последовательность формул и любой алгоритм будут зависеть от того, что отображается в данной ячейке.
И где же разместить связующую ячейку? Да где угодно! Тут важно другое – какое действие прикрутить к значению ИСТИНА и ЛОЖЬ.
Самое время прибегнуть к жульничеству.
Копируем диапазон B2:N7 на новый лист «Лист2» и вставляем в ячейку В1 как начальную. Таким образом мы формируем массив данных, на которые будет ссылаться флажок. Их можно оставить и на первом листе, их можно закрыть диаграммой – делайте что угодно, лишь бы спрятать вторую табличку.
Возвращаемся на Лист1.
Кликаем правой кнопкой по флажку, выбираем «Формат объекта».
Переходим на вкладку «Элемент управления» и в поле «Связь с ячейкой» указываем ячейку там, где находится запасная табличка, левее от столбца с командами. То есть путь будет следующим:
Лист2!$A$2
Повторяем это действие для всех флажков, указывая ячейки ниже предыдущей, то есть у вас должны получиться пути:
Лист2!$A$2
Лист2!$A$3
Лист2!$A$4
Лист2!$A$5
Лист2!$A$6
Попробуем включить все флажки на Листе1 и посмотреть результат на Листе2.
Всё работает! Если выключить флажки, то в этих ячейках появится логическое значение ЛОЖЬ. Теперь давайте сыграем на этом.
На Листе2, в ячейку «Команда 1», пишем условие:
=ЕСЛИ($A2=ИСТИНА;Лист1!B3;0)
Если флажок (ячейка $A2) показывает ИСТИНА, то мы забираем значение из ячейки B3 с Листа1; в противном случае (если показывает ЛОЖЬ), выводим 0.
На Листе2 протягиваем формулу по строке, то есть от В2 до N2 и, соответственно, вниз, по всей таблице.
И если вы сейчас решите переключить несколько флажков, то получите похожую картинку.
Работает! Остаётся только вставить диаграмму на Лист2 (потом мы её можем двигать куда угодно, место вставки значения не имеет).
Выделяем диапазон B2:N6, нашей таблицы на Листе2.
Переходим на вкладку «Вставка» и указываем рекомендуемые диаграммы. Пусть будет График. Нажимаем ОК. Обратите внимание, что при отображении всех графиков во всех полях горит ИСТИНА – флажки включены.
Со спокойной душой кликаем по блоку с диаграммой правой кнопкой мыши, выбираем «Вырезать» и вставляем её на Лист1 (либо левой кнопкой и Ctrl+X).
Вставив (Ctrl+V) диаграмму на Лист1 мы можем более наглядно оценить работу флажков. Кликаем по ним и смотрим, как появляются и растворяются графики. Вот оно, пресловутое чувство динамики!
Если хотите всё разместить на одном листе и так, чтобы зрители не догадались о вашем фокусе, то заберите (вырежьте) абсолютно всю таблицу с Листа2, вставьте на Листе1 и разместите поверх неё блок с диаграммой; растяните его так, чтобы данных не было видно. Мы специально убрали прозрачность блока с диаграммой, дабы показать, как это выглядит.
Рекомендуем скачать наш бесплатный гайд с горячими клавишами для работы в Excel, если вы хотите выполнять рутинную работу быстрее.
