Интерактивная диаграмма.xls (47,5 КиБ, 4 279 скачиваний)
Вводные данные: имеется таблица с данными по выручке от продаж на нескольких торговых точках:
Если построить график сразу по всем данным, то он будет смотреться вполне неплохо в качестве инструмента для сравнения выручки между торговыми точками:
Но что если необходимо показать динамику по каждой точке отдельно? Приведенный выше график не очень подходит для этой цели - слишком много лишних данных, в результате чего он смотрится довольно нагроможденно. Можно создать несколько одинаковых графиков, каждый из которых будет показывать данные по одной торговой точке. Это будет наглядно и удобно, если торговых точек 3-5. Но если их 10 и более, то такое нагромождение графиков мало того, что не наглядно - это еще весьма затратно по времени.
Поэтому, если есть необходимость показать динамику по отдельным торговым точкам, но при этом не обязательно делать много графиков, можно воспользоваться таким решением:
Интерактивная диаграмма.xls (47,5 КиБ, 4 279 скачиваний)
А теперь разберем, как это можно сделать.
- Для начала необходимо создать диаграмму нужного типа: выделяем диапазон
A4:K5 -вкладка Вставка(Insert) -группа Диаграммы(Charts) -Вставить график или диаграмму с областями(Line) -График(Line) - в удобном месте на основании названий торговых точек создаем обычный выпадающий список
В файле примере список создан в ячейке : выделяем ячейкуB11 -вкладка ДанныеB11 (Data) -Проверка данных(Data Validation) . В поле Тип данных(Allow) выбираем Список(List) , в поле Источник(Source) указываем ссылку на диапазон с именами торговых точек:=$A$5:$A$9 - Теперь необходимо создать именованный диапазон, который в зависимости от выбранной в списке торговой точки сформирует диапазон данных для диаграммы. Переходим на вкладку Формулы
(Formulas) -Диспетчер имен(Name Manager) -Создать(New) . В поле Имя(Name) пишем:_forchart , а в поле Диапазон(Refers to) следующую формулу:
=СМЕЩ( $B$4:$K$4 ;ПОИСКПОЗ($B$11 ;$A$5:$A$9 ;0);)
=OFFSET($B$4:$K$4,MATCH($B$11,$A$5:$A$9,0),)
функция СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]) - OFFSET
берет ссылку на указанные ячейки и смещает эту ссылку на указанное количество строк и столбцов. В качестве ссылки указываем заголовок с датами из таблицы выручки:$B$4:$K$4
ПОИСКПОЗ - эта функция берет ячейку(MATCH) и ищет её в диапазоне$B$11 . Когда находит, то возвращает номер строки, в которой она находится в этом диапазоне. Т.е. для "К-р Октябрьский" это будет значение 1, для "ул.Ленина" - 2 и т.д.$A$5:$A$9
Это значит, что как только мы изменим значение в ячейке B11(а там у нас список торговых точек), то функция СМЕЩ сразу переопределит диапазон:
=СМЕЩ( $B$4:$K$4 ;ПОИСКПОЗ($B$11 ;$A$5:$A$9 ;0);)=>
=СМЕЩ($B$4:$K$4 ;ПОИСКПОЗ("ул.Фурманова" ;$A$5:$A$9 ;0);)=>
=СМЕЩ($B$4:$K$4 ;5;)=>
=$B$9:$K$9
Остается только назначить этот диапазон созданной диаграмме: выделяем диаграмму -правый щелчок мыши -Исходные данные
выделяем единственный ряд в поле Элементы легенды(ряды)

восклицательный знак и возможные другие знаки надо оставить.
Два раза нажимаем Ок. Все, наша диаграмма готова. Теперь можно выбирать название из выпадающего списка и диаграмма поменяет свои значения на продажи выбранной торговой точки.
Интерактивная диаграмма.xls (47,5 КиБ, 4 279 скачиваний)
Так же см.:
Два в одном - как сделать?
Динамическая диаграмма
Ступенчатый график в Excel
Здравствуйте, спасибо за детальное пояснение построения данных таблиц!
В данном материале рассматривалась таблица, где было 2 показателя:
название магазина и выручка по периодам.
У меня вопрос: как можно сделать такую диаграмму если есть такие показатели: название магазина, выручка по периодам, оборот по периодам, количество (шт.) по периодам.
И возможно ли вообще это сделать с помощью такого инструмента?
Спасибо!
Анна, сложно что-то посоветовать, не видя данных и их расположения. Обратитесьв форум или вышлите файл мне на почту(контакты есть на сайте). Или выложите файл на сетевой обменник и приложите в комментариях ссылку.