Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Интерактивный график

Скачать пример из видеоурока:

  Интерактивная диаграмма.xls (47,5 KiB, 866 скачиваний)


Вводные данные: имеется таблица с данными по выручке от продаж на нескольких торговых точках:
Исходные данные для диаграммы

Если построить график сразу по всем данным, то он будет смотреться вполне неплохо в качестве инструмента для сравнения выручки между торговыми точками:
Все точки на одном графике

Но что если необходимо показать динамику по каждой точке отдельно? Приведенный выше график не очень подходит для этой цели - слишком много лишних данных, в результате чего он смотрится довольно нагроможденно. Можно создать несколько одинаковых графиков, каждый из которых будет показывать данные по одной торговой точке. Это будет наглядно и удобно, если торговых точек 3-5. Но если их 10 и более, то такое нагромождение графиков мало того, что не наглядно - это еще весьма затратно по времени.
Поэтому, если есть необходимость показать динамику по отдельным торговым точкам, но при этом не обязательно делать много графиков, можно воспользоваться таким решением:
Интерактивная диаграмма
Скачать пример:

  Интерактивная диаграмма.xls (47,5 KiB, 866 скачиваний)


А теперь разберем, как это можно сделать.

  • Для начала необходимо создать диаграмму нужного типа: выделяем диапазон 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 и ищет её в диапазоне $A$5:$A$9. Когда находит, то возвращает номер строки, в которой она находится в этом диапазоне. Т.е. для "К-р Октябрьский" это будет значение 1, для "ул.Ленина" - 2 и т.д.
    Это значит, что как только мы изменим значение в ячейке 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

Остается только назначить этот диапазон созданной диаграмме: выделяем диаграмму -правый щелчок мыши -Исходные данные(Source data) или Выбрать данные(Select data)
Выбрать данные для диаграммы

выделяем единственный ряд в поле Элементы легенды(ряды)(Legeng Entries (Series)) и нажимаем Изменить(Edit)
Изменить источник ряда диаграммы

  • в поле Имя ряда(Series name) вписываем =Interactive!$B$11
  • в поле Значения(Series values) вписываем: =Interactive!_forchart
    Сменить значения ряда
    совет: изначально в этом поле записана прямая ссылка: =Interactive!$B$5:$K$5. Чтобы ничего не упустить надо просто заменить адреса ячеек на именованный диапазон, который создали ранее - _forchart: =Interactive!$B$5:$K$5 => =Interactive!_forchart
    восклицательный знак и возможные другие знаки надо оставить.
  • Два раза нажимаем Ок. Все, наша диаграмма готова. Теперь можно выбирать название из выпадающего списка и диаграмма поменяет свои значения на продажи выбранной торговой точки.
    Скачать пример:

      Интерактивная диаграмма.xls (47,5 KiB, 866 скачиваний)

    Так же см.:
    Два в одном - как сделать?
    Динамическая диаграмма
    Ступенчатый график в Excel


    Статья помогла? Поделись ссылкой с друзьями!
      Плейлист   Видеоуроки

    Поиск по меткам

    Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
    Обсуждение: 2 комментария
    1. Здравствуйте, спасибо за детальное пояснение построения данных таблиц!
      В данном материале рассматривалась таблица, где было 2 показателя:
      название магазина и выручка по периодам.
      У меня вопрос: как можно сделать такую диаграмму если есть такие показатели: название магазина, выручка по периодам, оборот по периодам, количество (шт.) по периодам.
      И возможно ли вообще это сделать с помощью такого инструмента?
      Спасибо!

      • Анна, сложно что-то посоветовать, не видя данных и их расположения. Обратитесь в форум или вышлите файл мне на почту(контакты есть на сайте). Или выложите файл на сетевой обменник и приложите в комментариях ссылку.

    Поделитесь своим мнением

    Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


    Для оформления сообщений Вы можете использовать следующие тэги:
    <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

    Тренинги

    Заказать
    Юридическая информация

    Использование материалов сайта

    Политика Конфиденциальности

    ИП Щербаков Дмитрий Валентинович
    ОГРНИП: 318502700083307
    ИНН: 504013350772

    Наши партнеры

    Перейти
    Перейти

    Счетчики

    Рейтинг@Mail.ru Яндекс.Метрика
    © 2018 Excel для всех   Войти