Способов создать динамическую диаграмму несколько. Самым популярным из них является создание диаграммы на базе сводной таблицы. В этом случае, если вы применяете фильтр к сводной таблице, в диаграмме также меняется диапазон данных. На самой диаграмме также появляются фильтры, с помощью которых вы можно менять отображаемые данные.
Но что делать, если у вас в качестве исходных данных нет возможности использовать сводную таблицу, а есть только отчет, в котором выведены итоговые значения? На помощь приходят формулы.
Вот здесь есть подборка видео о том, как строить необычные диаграммы - Фишки Excel - подборка видеороликов.
У нас есть исходные данные по объемам продаж менеджеров за несколько месяцев:
Для начала сделаем выпадающий список, состоящий из названий месяцев. Он будет "управлять" нашей диаграммой. Для этого на ленте во вкладке Данные выбираем Проверка данных.
В этой статье я подробно рассмотрела как создавать выпадающие списки - Выпадающий список в Excel.
В открывшемся диалоговом окне в поле Тип данных выбираем Список и в поле для ввода диапазона Источник выбираем наш диапазон с месяцами:
Для того, чтобы в диаграмме выбирался нужный нам диапазон данных - необходимо сформировать отдельный список "ФИО - месяц" и прописать в него формулу, с помощью которой будет выводиться объем продаж в зависимости от месяца. Формула, введенная в ячейку L2 и протянутая по диапазону L2:L7 будет выглядеть так:
=ИНДЕКС($B$2:$I$7;ПОИСКПОЗ(K2;$A$2:$A$7;0);ПОИСКПОЗ(L$1;$B$1:$I$1;0))
В ней нет ничего сложного, здесь используется две функции - ИНДЕКС и ПОИСКПОЗ. Они ищут положение определенной ячейки в диапазоне и подтягивают соответствующее этой ячейке значение.
В этой статье вы можете ознакомиться с тем, как они работают - Просто о функциях ИНДЕКС и ПОИСКПОЗ в Excel.
Теперь на основе появившегося диапазона необходимо сформировать диаграмму, я выбрала гистограмму (выделите диапазон K1:L7, перейдите на ленте во вкладку Вставка-> Диаграммы и выберите нужную диаграмму):
Для того, чтобы еще и легенда к гистограмме была динамической и зависела от месяца, который выбран, введите еще одну формулу в любую из ячеек и после на диаграмме текст Легенды замените на ссылку на эту ячейку:
="Объем продаж за "&A9&", тыс. руб."
Символ конкатенации (&) объединяет текстовое значение и значение из ячейки. О нем я писала в статье - Как объединить ячейки в Excel: конкатенация (&) и СЦЕПИТЬ
В итоге вы получите диаграмму, которая перестраивается в зависимости от выбранного месяца.
Подписывайтесь, чтобы первыми получать самые интересные публикации! Ещё больше полезных советов и лайфхаков Excel – в моем Telegram-канале!
Для удобства - статья навигатор по каналу: Путеводитель по каналу