Динамическая диаграмма
В диаграммах Excel есть один небольшой минус. Если построить диаграмму на основе данных, которые планируется добавлять со временем, то по мере добавления данных в таблицу, на которой основана диаграмма, придется также менять диапазон данных для диаграммы, чтобы включить их в отображение. Либо сразу указать заведомо больший диапазон, но тогда диаграмма весьма некрасиво выглядит. Это не такая уж и проблема, если данные добавляются один раз в месяц. А если это необходимо делать каждый день? Или несколько раз в день?
Итак, у нас имеется таблица в столбцах A, B и С - Дата, Количество посетителей и Количество просмотров соответственно.
Данный вариант более сложен в реализации, но может быть использован в любой версии 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 позволяет создавать такой объект, как таблица(в 2003 Excel это называется Список). Хоть такой объект есть и в 2003 версии, но в 2003 невозможно узнать имя этой таблицы без применения VBA.
Для начала потребуется создать так называемую "умную таблицу". Создать её можно двумя способами
- Выделить любую ячейку области данных, перейти на вкладку Вставка
(Insert) -Таблица(Table) - В появившемся окне согласиться с указанным диапазоном или выбрать свой
- Нажать OK
Выделить любую ячейку области данных
Выбрать команду на вкладке Главная
После этого идем в диспетчер имен(
это и есть нужная нам таблица.
Создаем диаграмму, правая кнопка мыши по области диаграммы:
для Excel 2007-2010 - Выбрать данные
для Excel 2003 - Исходные данные
Вписываем имя таблицы в качестве диапазона для данных диаграммы:
Нажимаем Ок. Теперь при расширении таблицы и добавлении строк эти данные будут автоматически добавлены и в диаграмму.
В примере ниже файл с реализацией обоими вариантами.
Tips_Chart_Dinamic_Chart.xls (29,5 KiB, 9 976 скачиваний)
Так же см.:
Именованные диапазоны
Динамические именованные диапазоны
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Дошел до момента нажимаем "Изменить", диапазоны созданы. Там нужно написать имя ряда и значение. Имя ряда, как я понимаю вообще можно писать все что угодно - графику это не повредит. А вот со значением у меня проблемы. Что там писать? Писал "=Таблица!Дата" пишет не допустимая ссылка. Excel 2007.
То что написано в примере в качестве ссылки на диапазон, уверен можно как-то попроще записать, кроме как вбивать один в один то что там написано.
Я уже разобрался, что при создании динамического именного диапазона, писать буквально "Таблица" не верно, нужно писать имя моей таблицы (то бишь название эксельки)
Что я имею: таблицу с датами, количеством посетителей, количеством просмотров.
Три динамических именных диапазона:
=СМЕЩ(Лист5!$A$2:$A$2;;;СЧЁТЗ(Лист5!$A$2:$A$981);) - Дата
=СМЕЩ(Лист5!$B$2:$B$2;;;СЧЁТЗ(Лист5!$A$2:$A$981);) - Количество_посещений
=СМЕЩ(Лист5!$C$2:$C$2;;;СЧЁТЗ(Лист5!$A$2:$A$981);) - Количество_просмотров
Пустая диаграмма в которой я выбираю "выбрать данные" "добавить", а дальше ничего не могу сделать
А возможно ли сделать динамическую диаграмму, но так чтобы первые несколько значений убирались из неё? Например, в первом случае, диграмма берет непустые значения с 1 по 10 из ряда, а во втором случае уже должна взять значения с 3 по 10. Сколько ни искал в просторах интернета - ничего не нашел, к сожалению.
Получается простая диаграмма с добавлением данных, которая со временем накапливает их в себе и накапливает, и в итоге получается диаграмма с огромным промежутком времени. Я думал эта диаграмма будет смещаться как то, т.е. при добавлении нового значения, самое последнее будет убираться типа - [1.2...9.10] добавили данные, получили [2.3...10.11] и т.д как то вот так.
Статья отстой!
колямба, а Вы, наверное, считали, что статья будет написана конкретно под Ваши потребности? Т.е. воспользоваться предложенным примером и немного под себя подкрутить не хватает знаний, а виноват автор статьи? Я честно написал в статье, что будет в ней описано и многим, кстати, именно такое накопление и требуется. А то, что хотите Вы делается просто изменением формулы на подобную:
=СМЕЩ(Таблица!$A$2;СЧЁТЗ(Таблица!$A$2:$A$1000)-10;;10;)
P.S. Прежде чем называть чью-то статью отстойной сделайте что-то получше. Критиковать и хаять умеет каждый. Спасибо хоть что автора козлом не назвали, добрый человек колямба.
Моя диаграмма основывается всего из двух данных, это время и цыфры (расход воды м3/ч), данные заносятся каждый час. Мне нужно чтобы диаграмма постоянно показывала только последние 24 часа. Получается что мне нужно сделать только один диапазон? Я его сделал, вроде все получается, только проблема с временем, оно фиксируется и не двигается, стоит на 00:00 часов и все. Для времени диапазон ведь не задашь я так понимаю. Что делать, подскажите?
Петрович, советую обратиться на форум - без примера Ваших данных здесь не разобраться, а нафоруме есть возможность выкладывать файлы.
Дмитрий(Админ), Спасибо!)
p.s. Вроде разобрался. но появилась другие непонятки: задал я в диаграмме динамический диапазон, она сдвинулась на последнюю ячейку с данными, как мне и надо было. Но после заполнения новых данных диаграмма стоит на месте, и только после того как я снова прописал в диаграмме именованный диапазон (Выбираем нужный ряд, нажимаем «Изменить» и вписываем имя динамического диапазона в качестве диапазона для данных этого ряда.), то диаграмма снова сдвинулась до последнего значения. Вопрос - нужно постоянно вписывать в диапазон диаграммы именованный диапазон, что бы она обновилась?
Петрович, насколько я знаю подобный эффект наблюдается при задании динамического диапазона в качестве данный для всей диаграммы. Если же задавать динамический диапазон для каждого ряда отдельно - то все работает как должно.