Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Динамическая диаграмма

 

В диаграммах Excel есть один небольшой минус. Если построить диаграмму на основе данных, которые планируется добавлять со временем, то по мере добавления данных в таблицу, на которой основана диаграмма, придется также менять диапазон данных для диаграммы, чтобы включить их в отображение. Либо сразу указать заведомо больший диапазон, но тогда диаграмма весьма некрасиво выглядит. Это не такая уж и проблема, если данные добавляются один раз в месяц. А если это необходимо делать каждый день? Или несколько раз в день?
Итак, у нас имеется таблица в столбцах A, B и С - Дата, Количество посетителей и Количество просмотров соответственно.


Вариант для всех версий Excel
Данный вариант более сложен в реализации, но может быть использован в любой версии Excel и помимо этого в диаграмму можно добавить любой столбец таблицы.
Создаем на основании данной таблицы диаграмму. Для того, чтобы создать динамически обновляемую диаграмму, нам понадобиться создать три динамических именованных диапазона из таблицы данных:

  • Жмем Ctrl+F3
  • В поле Диапазон(Refers to) пишем формулу =СМЕЩ(Таблица!$A$2:$A$2;;;СЧЁТЗ(Таблица!$A$1:$A$1000);)
  • В поле Имя(Name) - желаемое имя для данного диапазона(в данном случае лучше давать понятное имя - имя ряда) - Дата.

Тоже самое делаем с двумя остальными рядами. В результате у нас получится три именованных диапазона:

  • Дата =СМЕЩ(Таблица!$A$2:$A$2;;;СЧЁТЗ(Таблица!$A$1:$A$1000);)
  • Количество посетителей =СМЕЩ(Таблица!$B$2:$B$2;;;СЧЁТЗ(Таблица!$B$1:$B$1000);)
  • Количество просмотров =СМЕЩ(Таблица!$C$2:$C$2;;;СЧЁТЗ(Таблица!$C$1:$C$1000);)

Теперь кликаем правой кнопкой мыши по диаграмме:

для Excel 2007-2010 - Выбрать данные(Select Data);
для Excel 2003 - Исходные данные(Source data).

Выбираем нужный ряд, нажимаем "Изменить" и вписываем имя динамического диапазона в качестве диапазона для данных этого ряда.

Важно: при замене диапазона заменить надо только адрес ссылки, а имя листа и книги оставить неизменными. Т.е. должно получиться - =Таблица!Дата или =Dinamic_Chart.xls!Дата

Жмем Ок. Все, теперь при добавлении данных в таблицу, данные в диаграмме тоже добавятся.


Вариант для Excel 2007 и выше
Это быстрый и просто способ, который рассчитан на то, что диаграмма строится на основании всех столбцов таблицы. Основан он на том, что Excel позволяет создавать такой объект, как таблица(в 2003 Excel это называется Список). Хоть такой объект есть и в 2003 версии, но в 2003 невозможно узнать имя этой таблицы без применения VBA.
Для начала потребуется создать так называемую "умную таблицу". Создать её можно двумя способами
Способ 1:

  1. Выделить любую ячейку области данных, перейти на вкладку Вставка(Insert) -Таблица(Table)
  2. В появившемся окне согласиться с указанным диапазоном или выбрать свой
    Создание таблицы
  3. Нажать OK

Способ 2:
Выделить любую ячейку области данных
Выбрать команду на вкладке Главная(Home) -группа Стили(Styles) -Форматировать как таблицу(Format as Table)

После этого идем в диспетчер имен(Ctrl+F3 или вкладка Формулы -Диспетчер имен) и находим там нашу таблицу. На скрине ниже это "Таблица1":
Диспетчер имен
это и есть нужная нам таблица.
Создаем диаграмму, правая кнопка мыши по области диаграммы:

для Excel 2007-2010 - Выбрать данные(Select Data);
для Excel 2003 - Исходные данные(Source data).

Вписываем имя таблицы в качестве диапазона для данных диаграммы:
Выбор источника данных
Обращаю внимание на то, что перед именем таблицы обязательно должен быть указан знак равенства "=". Иначе будет ошибка.
Нажимаем Ок. Теперь при расширении таблицы и добавлении строк эти данные будут автоматически добавлены и в диаграмму.

В примере ниже файл с реализацией обоими вариантами.
Скачать пример:

  Tips_Chart_Dinamic_Chart.xls (29,5 KiB, 6 765 скачиваний)

Так же см.:
Именованные диапазоны
Динамические именованные диапазоны


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

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

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

    Щелкнул по диаграмме прав кнопкой -> исходные данные -> вкладка "диапазон данных" и ниже строка "Диапазон" туда я и вписываю название именованного диапазона, нажимаю ок. Там не написано для какого именно ряда. Кстати вместо СЧЕТЗ использовал СЧЕТ, т.к. у меня формула в том столбце от куда диаграмма берет данные. А вот формула которую я использовал:
    =СМЕЩ(Мощность!$AE$2500;СЧЁТ(Мощность!$AE$2500:$AE$20000)-24;;24;)
    На форумах рыскал, все сюда отправляют, так что если что не ругайтесь.)

  2. Petrovitch:

    Петрович :
    Я зарегистрировался если что, чтоб можно было писать в личку.

    То есть я вот.

  3. Петрович, Вы назначаете диапазон для всей диаграммы. прочитайте внимательно статью - Вам надо создать несколько динамических диапазонов - для кадого ряда свой диапазон. А затем рядам назначить эти диапазоны:
    Выбираем нужный ряд, нажимаем «Изменить» и вписываем имя динамического диапазона в качестве диапазона для данных этого ряда.

  4. Petrovitch:

    Дмитрий(Админ), Все, разобрался_) Спасибо большое.

  5. Ирина:

    Статья отличная! Спасибо автору! Все очень просто и доступно тому, кто умеет думать. Жаль,что диапазон сдвигается только при пустых ячейках, а если в ячейки вбита формула вроде =IF(F335>0;F335;""), то он это за пустую ячейку не считает и не сдвигается:( будем думать.

  6. rokise:

    Здравствуйте!
    Петрович, подскажите пожалуйста, как у Вас получилось разобраться со временем в диапазоне? чтобы оно не стояло на 00:00:00, а отображались значения в диаграмме и за другое время в сутках. Спасибо заранее.

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

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


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

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти