Как обновить диаграмму в excel при изменении данных
При построении графика в excel по диапазону данных, которые будут добавляться со временем (например, котировки), приходится после каждого добавления данных увеличивать диапазон значений в настройках графика. Если задать сразу диапазон с запасом, то график не будет полностью заполнять область диаграммы. Те, кто часто строит подобные графики, могут сэкономить немного времени в будущем, воспользовавшись предварительными настройками.
Допустим, строим графики изменения недельных значений индекса РТС и EMA5 к ним. У нас есть три столбца значений, по которым строится график:
| A | B | C |
| DATE_W | RTSI | EMA |
Чтобы график сам обновлялся после новых введенных значений в столбцах, надо диапазонам значений присвоить имена:
1. CTRL+F3 (Откроется «Диспетчер имен»)
2. В поле «Имя» пишем имя диапазона: DATE_W (для диапазона значений дат)
3. В поле «Диапазон» вводим формулу =СМЕЩ(Лист1!$A$2:$A$2;;;СЧЁТЗ(Лист1!$A$2:$A$5000);)
Пункты 2 и 3 повторяем для двух оставшихся столбцов. В результате получится три именованных диапазона:
| Имя | Диапазон |
| DATE_W | =СМЕЩ(Лист1!$A$2:$A$2;;;СЧЁТЗ(Лист1!$A$2:$A$5000);) |
| RTSI | =СМЕЩ(Лист1!$B$2:$B$2;;;СЧЁТЗ(Лист1!$B$2:$B$5000);) |
| EMA | =СМЕЩ(Лист1!$C$2:$C$2;;;СЧЁТЗ(Лист1!$C$2:$C$5000);) |
Теперь кликаем правой кнопкой мыши по диаграмме / «Выбрать данные…» / Выделяем нужный ряд и жмём «Изменить» / Вместо исходного диапазона данных вставляем присвоенное имя диапазона вот так:
Пример
| Было | Стало |
| =Лист1!$B$2:$B$52 | =Лист1!RTSI |
То же самое для других рядов.
Жмём «Ok».
Всё. Теперь при добавлении новых данных в столбцы, график автоматически будет изменяться. Далее можете попробовать ввести Close этой недели по РТС и проверить.
Кому нужен пример – пишите в личку, отправлю на почту.
Хотя создавать новые диаграммы очень легко, их также необходимо обновлять, чтобы они отражали новые обстоятельства, и для этого могут потребоваться определенные усилия. Сократить объем работы, необходимый для изменения данных, на основе которых построена диаграмма, можно несколькими способами.
Перетаскивание данных
Можно добавить данные к существующему ряду или создать абсолютно новый ряд данных, просто перетащив данные на диаграмму. Excel попытается решить, как следует обработать данные, но при этом он может добавить их ,к существующему ряду данных, тогда как вы хотели создать новый. Однако можно заставить Excel открыть диалоговое окно, в котором можно будет выбрать необходимое действие. Попробуйте добавить на лист какие-то данные (рис. 5.13).
Рис. 5.13. Данные для обыкновенной гистограммы
При помощи мастера диаграмм создайте обыкновенную гистограмму только для диапазона $A$1:$D$5 (рис. 5.14).
Рис. 5.14. Обыкновенная диаграмма только для определенного диапазона
Выделите диапазон A6:D6, правой кнопкой мыши щелкните рамку выделения и, удерживая правую кнопку, перетащите данные на диаграмму. Когда вы отпустите кнопку, появится диалоговое окно Специальная вставка (Paste Special) (рис. 5.15).
Рис. 5.15. Обыкновенная гистограмма и диалоговое окно специальной вставки
Выберите параметр В столбцах (Columns) и щелкните на кнопке ОК. Ряд данных для мая (May) будет добавлен на диаграмму (рис. 5.16).
Рис. 5.16. Обыкновенная гистограмма с новым рядом данных
Диалоговое окно Специальная вставка (Paste Special) выполняет большинство действий, которые нужны для этого искусного трюка.
Диаграмма и строка формул
Диаграмму можно обновить и при помощи строки формул. Выделив диаграмму и щелкнув на ней ряд данных, посмотрите на строку формул: вы увидите формулу, которую Excel использует для ряда данных. В этой формуле, которая называется функцией РЯД (SERIES), обычно указывается четыре аргумента, хотя для пузырьковой диаграммы требуется дополнительный пятый аргумент, обозначающий размер ([Size]).
Синтаксис (или порядок структуры) функции РЯД (SERIES) выглядит так: =SERIES([Name];[X Values];[Y Values];[Plot Order]), в русской версии Excel =РЯД([Имя];[Значения X];[Значения Y];[Номер графика]). Так, допустимая функция РЯД (SERIES) может выглядеть, как на рис. 5.17: =SERIES(Sheet1!$В$1;Sheet1!$А$2:$А$5;Sheet1!$В$2:$В$5;1), в русской версии Excel =РЯД(Лист1!$В$1;Лист!!$А$2:$А$5;Лист1!$В$2:$В$5;1).
Рис. 5.17. Обыкновенная гистограмма с выделенной строкой формул
Перетаскивание граничной области
Если диаграмма содержит ссылки на последовательные ячейки, можно легко увеличивать или уменьшать данные ряда, перетаскивая граничную область в желаемую точку. Медленно щелкните ряд данных, который хотите увеличить или уменьшить. После двух медленных щелчков по краям ряда появятся черные квадратики (маркеры). Все, что нужно, — щелкнуть квадратик и перетащить границу в желаемом направлении (рис. 5.18).
Рис. 5.18. Выделенная граничная область ряда диаграммы
Если увеличивать или уменьшать ряд данных, исходный источник данных, а также подписи осей, если для них выбран параметр Авто (Auto), обновляются, отражая сделанные изменения. Это прекрасная возможность проверки сценариев, если вы хотите узнать, какие получатся результаты для различных наборов данных.
Вас, наверное, не раз напрягало, что после добавления данных область диаграммы следует увеличить. Этого можно избежать, если в диаграммах вместо ссылок на ячейки использовать ссылки на именованные динамические диапазоны.
В качестве пример возьмем курс доллара (рис. 1). Для начала создадим обычную диаграмму (тип «График с маркерами»).
Рис. 1. График с маркерами
Далее создадим два именованных динамических диапазона: один для меток категорий (Даты), второй – для точек данных (Курс $). Для создания именованного диапазона пройдите по меню Формулы → Диспетчер имен (рис. 2).
Рис. 2. Диспетчер имен
В открывшемся окне «Диспетчер имен» нажмите кнопку создать, и в окне «Создание имени» введите имя диапазона – «Даты» и формулу для ссылки на диапазон: =СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A$1:$A$100)-1;1)
Рис. 3. Присвоение имени динамическому диапазону
Обратите внимание, что сразу же за аргументом функции СЧЁТЗ стоит «–1». Благодаря этому заголовок ряда не будет включен в именованный диапазон. Заметьте также, что в качестве аргумента функции СЧЁТЗ указан не весь столбец А, а лишь первые 100 ячеек. Если вы используете большой массив данных, укажите соответствующее число, например, 1000 или 10 000. В ранних версиях Excel такое ограничение весьма желательно, дабы не перегружать вычисления. Указывая колонку полностью, вы заставляете Excel просматривать тысячи ненужных ячеек. Некоторые функции Excel достаточно умны, чтобы определить, какие ячейки содержат данные, некоторые сделать этого не могут. В новых версиях Excel не обязательно строго ограничивать диапазон, так как обработка больших диапазонов в них улучшена.
Затем создайте второй именованный диапазон для данных столбца В (рис. 4)
Рис. 4. Динамический диапазон «Курс»
Теперь можно заменить в диаграмме ссылки на диапазоны данных именами динамических диапазонов. Выделяем диаграмму и щелчком правой кнопкой мыши вызываем контекстное меню, строчку «Выбрать данные» (рис. 5).
Рис. 5. Выбрать данные
В открывшемся окне «Выбор источника данных» выделяем ряд и жмем «Изменить» (рис. 6).
Рис. 6. Изменить ряд
В открывшемся окне «Изменение ряда» заменяем ссылки на ячейки на имя ряда «Курс» (рис. 7). Обратите внимание, что имя листа Excel следует оставить в неизменном виде «=Лист1!»
Рис. 7. Замена ссылок на имя диапазона
Аналогично заменяем подписи горизонтальной оси (категории): жмем другую кнопку «Изменить» в правой части окна «Выбор источника данных» (см. рис. 6) и вводим имя «Даты» вместо ссылок на ячейки (рис. 8).
Рис. 8. Замена подписей оси (категорий)
Все наши манипуляции не привели к изменению диаграммы. Мы лишь подготовились к грядущим изменениям. Как говорится: «подальше положишь, поближе возьмешь». А теперь наслаждайтесь автоматическим расширением области диаграммы при добавлении новых значений в таблицу данных, например, как на рис. 9.
Рис. 9. Новые данные, добавленные в таблицу (выделены желтым) автоматически отражаются на диаграмме
В своей работе менеджера мне приходится контролировать довольно много параметров, так что подобные хитрости я использую давно, и они значительно облегчают мне работу. А вот недавно в книге Д.Холи, Р. Холи «Excel 2007. Трюки» я прочитал о еще одной возможности, основанной на том же свойстве.
Добавление от 19 июня 2018 г. Эту же проблему гораздо проще решить, если встать на любую ячейку диапазона, и нажать Ctrl+T (англ.). Диапазон превратится в Таблицу. Создайте на ее основе диаграмму. При добавлении строк в Таблицу, диаграмма будет отражать их автоматически.
Построение диаграммы для фиксированного числа последних данных
Еще один тип именованных диапазонов, который можно использовать с диаграммами, – это диапазоны, выбирающие только последние N значений (можно указать любое число).
См. пример на Лист2 в Excel-файле. Для данных в столбце А создайте динамический именованный диапазон с именем Даты30 (последние 30 дней), который ссылается на следующие данные: =СМЕЩ($A$1;СЧЁТЗ($A$1:$A$100)-30;0;30;1). Для данных в столбце В создайте динамический именованный диапазон с именем Курс30, который ссылается на следующие данные: =СМЕЩ($B$1;СЧЁТЗ($B$1:$B$100)-30;0;30;1). Замените в диаграмме ссылки на диапазоны данных именами динамических диапазонов. Получится диаграмма, отражающая последние 30 значений (рис. 10).
Рис. 10. На диаграмме отражаются 30 последних значений
При добавлении данных в таблицу область отражения на диаграмме сместится (рис 11).
Рис. 11. При добавлении данных диаграмма по-прежнему отражает 30 последних значений
Использование динамических именованных диапазонов с диаграммами обеспечит исключительную гибкость и сэкономит огромное количество времени и усилий, которые вы потратили бы на настройку диаграмм после добавления еще одной записи к исходным данным!
17 комментариев для “Excel. Диаграмма, изменяющаяся при добавлении данных”
К примеру, я делаю отчет, в котором собираюсь путем копирования вкладок получать данные по каждой следующей переменной. После этого на новой вкладке диаграмма будет отражать данные с предыдущей и нужно будет создавать под эту вкладку новое имя, что немного не подходит. Есть ли способ ссылаться на динамически изменяемый диапазон с данными текущего листа?
Николай, попробуйте создать в диаграмме ссылки на данные с помощью функции ДВССЫЛ. А в этой ДВССЫЛ сошлитесь на название листа, которое забивайте во всех листах в одну и туже ячейку
В диаграмме изначально пытался подсунуть экселю СМЕЩ, и сослаться на нужный мне диапазон. Однако он ругается что данная функция не действительна. Тоже пишет и о ДВССЫЛ.
Алексей, а у меня получилось на полтыка)) В прилагаемом файле Excel на Лист1 просто поменял тип диаграммы График на Точечная (у меня Excel 2013)
А если данные по горизонтали? как быть в таком случае?
Сергей, добрый день!
Вроде все делаю, как у вас написано. В варианте с таблицей все получилось,но обновляться не хочет график. С динамическими данными при изменении ряда пишет недопустимые ссылки(( Сможете помочь? ))
я столкнулся с этой же проблемой - не уверен, почему, и когда это происходит, единственный способ, которым я когда-либо получал диаграмму для принудительного обновления, - это изменить что-то в самом определении диаграммы, что можно легко сделать через VBA, как в:
там может быть лучший ответ, который добирается до сути проблемы - но я думал, что это может помочь. Работая над листом, я бы сделал быстрый Ctrl-X, Ctrl-V на части диаграммы (или на всем), чтобы заставить диаграмму обновляться.
это единственное, что я нашел для последовательного обновления диаграммы. Это сокращает основную причину проблемы (я предполагаю): данные серии кэшируются в диаграмме. Заставляя диаграмму переоценивать ряды, мы очищаем кэш.
у меня была эта проблема при генерации 1000 + графиков через VBA . Я сгенерировал графики и назначил диапазон для их рядов. Однако при пересчете листа графики не будут обновляться по мере изменения значений диапазонов данных.
решение --> Я выключил WrapText перед For. Следующий цикл, который генерирует графики, а затем снова включил его после цикла.
Это отличное решение, потому что он обновляет 1000 + графики сразу, не петляя через них все и изменяя что-то индивидуально.
кроме того, я не совсем уверен, почему это работает; я полагаю, что когда WrapText изменяет одно свойство диапазона данных, он обновляет график, хотя у меня нет документации по этому вопросу.
у меня была такая же проблема с простой круговой диаграммы.
ни один из макросов не работал, что я пытался. Ничего не работало на cut , pasting , relocating диаграмма.
обходной путь, который я нашел, состоял в редактировании текста диаграммы, удалении меток, а затем повторном выборе меток. Как только они снова появились, они были обновлены.
Это абсурдная ошибка, которая серьезно затрудняет мою работу с Excel.
основываясь на опубликованных работах, я пришел к следующим действиям как упрощенный способ двигаться вперед.
нажмите на график, который вы хотите обновить-выберите CTRL-X, CTRL-V, чтобы вырезать и вставить график на место. он будет вынужден обновляться.
это работает очень хорошо для меня-он переворачивает оси на всех графиках, а затем переворачивает их обратно, что заставляет их обновляться без изменения вообще.
это известная ошибка Excel.
лучшим и самым быстрым обходным путем является Columns.AutoFit - Trick:
У меня другая проблема освежающий графика. При автоматической генерации диаграмм некоторые диаграммы отображаются поверх и кэшируют текст на листе. Это проблема обновления сгенерированных графиков. Когда я увеличиваю или уменьшаю масштаб, я могу получить ожидаемые результаты. Поэтому я размещаю решение здесь, если оно кого-то интересует. Программно я добавил Это после создания диаграмм:
я столкнулся с той же проблемой. Проблема связана с ограничением в no. расчетных формул на листе. вы можете решить его двумя способами:
ручная сила пересчитать:
макрос, чтобы заставить пересчитать: добавить ниже код в конец функции, которая изменяет данные
я нашел решение это: Из параметров excel обязательно измените параметры расчета, как показано ниже. Она изменилась иногда к ручному после тяжелой работы в excel.
эта проблема смешна! В 2010 году ни одно решение не работало для меня, но я основал свое на tpascale:
просто переопределил диапазон исходных данных. Если это именованный диапазон, это может быть разумно чистым. Я думаю, лучшее решение для этого-продолжать пытаться изменить различные свойства диаграммы, пока она не обновится.
Ok у меня есть решение, действительно.
Я обнаружил, что проблема с моими диаграммами, не обновляемыми сначала, произошла вскоре после того, как я скрыл некоторые столбцы данных, подающие диаграмму, и проверил "показать данные, скрытые в строках и столбцах" в поле msg "выбрать источник данных" диаграммы).
Я обнаружил, что если я вернулся в поле" выбрать источник данных " msg и снял / перепроверил "показать данные, скрытые в строках и столбцах", что диаграмма обновляет.
программно я вставил следующее в макрос, с которым я связал кнопку, он обновляет все мои диаграммы достаточно быстро для обхода известной ошибки. Этот код предполагает, что одна диаграмма на листе, но другой оператор for для диаграмм от 1 до N может быть добавлен при желании:
У меня была эта проблема, и я обнаружил, что это было вызвано наличием двух приложений excel, работающих одновременно. Если я закрыл все и открыл только файл, у меня возникли проблемы с графиками, где они должны быть динамическими. Может быть, это помогает
мы нашли решение, которое не включает VBA: умножение некоторого элемента диапазона данных диаграммы на TODAY()-TODAY()+1 .
хотя диапазон пересчитывался без этого, волатильная природа TODAY() почему-то придает ему дополнительный импульс, который вызывает пересчета графика.
это сработало для меня, он сокращает и повторно вставляет диаграммы на активном листе. Я основал это на коде Джейсона и блоге Я нашел в быстром поиске Google.
просто потратил полдня на это сам.
у меня есть макрос, который изменяет значения данных для диаграммы. В Excel 2003 все работало нормально, но в Excel 2007 диаграмма, похоже, теряет все соединение со своими данными, хотя ручное изменение значений данных в двух столбцах вызвало пересчет.
мое решение состояло в том, чтобы сделать все диаграммы на активном листе невидимыми до изменения данных, а затем сделать их видимыми снова и вызвать обновление диаграммы для хорошей меры. (Это только кажется, видны диаграммы, которые имеют эту проблему обновления ).
это работает для меня, а также обрабатывает аналогичные проблемы с диаграммами, а также объектами диаграммы. Обновление может не потребоваться - требуется дополнительное тестирование.
СДЕЛАТЬ МАКРОС МАТЕРИАЛ, КОТОРЫЙ ИЗМЕНЯЕТ ДАННЫЕ
У меня была та же проблема, что и плакат. В основном я запускаю панель мониторинга, и у меня есть куча именованных диапазонов, которые заполнены возвращаемыми значениями из некоторых UDFs. На панели мониторинга есть несколько круговых диаграмм с рядами данных, привязанными к ячейкам, которые содержат эти именованные диапазоны (проблема также возникает, если целевые ячейки ряда данных содержат UDFs напрямую, минуя именованные диапазоны).
Я изменяю значение ячейки, которое содержит, например, диапазон дат для базовой панели on, и именованные диапазоны и UDFs вынуждены вычислять. Однако круговые диаграммы не обновляются-по какой-то причине это делают другие типы диаграмм. И, кстати, это объекты диаграммы, а не листы диаграммы. В любом случае, давайте перейдем к решению:
Я не хотел заметно менять название диаграммы или какой-либо другой ее аспект, и в любом случае я заметил, что это не обновляет мои диаграммы последовательно. Иногда при первом запуске расчета пироги будут обновляться, но с последующим расчетов у пирогов не будет. Однако я заметил, что каждый раз, когда я вносил изменения в код, моя приборная панель работала. Таким образом:
устранение:
Если вы используете модуль книги (меня в этом случае не было), просто создайте новый модуль и ссылайтесь на него.
Я столкнулся с той же проблемой с моей работой на прошлой неделе, когда я добавил еще несколько вычислений в свой лист. После этого использование переключателей для выбора данных, которые будут представлены на графиках, больше не обновляет графики.
Если я правильно понял, если есть более 65536 формул, которые имеют другую ячейку в качестве ссылки в вашем файле, Excel начинает оптимизировать расчет и в некоторых случаях графики не обновлялись.
Если есть обходной путь для этого без использования макросов VBA, я был бы рад услышать это (не могу использовать их, поскольку файлы должны быть разделены через SharePoint без макросов VBA).
Что сработало для меня, это использование макроса для вставки / удаления столбца в таблице данных для диаграммы. Это приведет к тому, что диаграмма обновит выделение данных.
у меня была такая же проблема при работе с учебником (очень неприятно, когда вы выполняете шаги и не получаете ожидаемого результата).
учебник по созданию круговой диаграммы хотел, чтобы я выбрал диапазон A3:A10 , затем также выберите несмежный диапазон E3:E10 . Я так и сделал. У меня есть карта.
затем он попросил меня изменить значение и посмотреть процентное изменение, а затем посмотреть на круговую диаграмму и увидеть обновление.
он не обновлялся.
I посмотрел на источник данных для диаграммы и диапазон был странным. Он имел A3:A10 диапазон обозначен правильно, но E10 ссылка на ячейку повторялась несколько раз, и у нее было все E ячейки, перечисленные в случайном порядке. Это выглядело как
Я изменил источник данных для чтения:
проблема решена. Иногда это вопрос очистки вашего кода, поэтому процессору вычислений приходится меньше сортировать.
Я тоже боролся с этой проблемой. Наконец, решил его путем пересчета листа, который имеет данные диаграммы после того, как пользовательская функция пересчитала. Итак, в листе 1 у меня есть ячейка, содержащая
в модуле VBA функция определяется как Volatile, чтобы гарантировать, что ComputeScore () запускается после любых обновлений электронной таблицы.
затем, в VBA листа 1, это:
Итак, лист с именем Charts Data, на который ссылается пользовательская ячейка функции листа 1 будет выполнять пересчет после того, как функция ComputeScore() обновила ячейку листа 1, так как Worksheet_Calculate() срабатывает после пересчета ComputeScore (). Этот дополнительный раунд расчета данных диаграммы заставляет диаграмму обновляться сейчас, а не позже или вообще не обновляться. Установка EnableEvents и xlManual удерживает бесконечные циклы recalc и другие проблемы с событиями.
Это может выглядеть очень просто, но я просто попробовал ручной расчет в электронной таблице, где были диаграммы (нажав F9), и это сработало! Код Tha VBA для него просто:
поскольку я пробовал почти все представленные решения, и поскольку ни один из них не работал в моем случае, я добавлю свои два цента здесь. Надеюсь, это поможет кому-то еще.
консенсус по этому вопросу, похоже, заключается в том, что нам нужно каким-то образом заставить excel перерисовать график, поскольку он не делает этого, когда должен.
мое решение состояло в том, чтобы убить данные оси X и заменить их ничем, прежде чем изменить их на то, что я хотел. Вот мой код:
мои два цента за эту проблему-у меня была аналогичная проблема с диаграммой в отчете Access 2010. Я динамически создавал querydef, устанавливая его в качестве источника строк в моем отчете, а затем пытался выполнить цикл через каждую серию и установить свойства каждой серии. В конце концов мне пришлось разбить создание querydef и настройку свойства на отдельные подменю. Кроме того, я поставил
в нижней части каждой из двух сабов.
при изменении значений исходных данных диаграмма не обновлялась соответствующим образом. Просто закрыл все экземпляры excel и перезапустил, проблема исчезла.
У меня была аналогичная проблема-диаграммы, похоже, не обновлялись. Я пробовал почти все в этой теме без везения. Наконец я понял, что диаграммы, которые я копировал и вставлял, были связаны с исходными данными, и поэтому все они показывали одни и те же результаты.
убедитесь, что вы копируете и вставляете фотографии, прежде чем пройти все остальные движения.
У меня была та же проблема, а также выяснилось, что строка будет отображаться только в том случае, если я помещу плохие данные (символы вместо чисел). Это вызвало появление строки, но изменение обратно на действительные данные заставило ее снова исчезнуть.
Я обнаружил, что если я дважды щелкнул линию (появившись с плохими данными), это показало мне, что она по какой-то причине находится на вторичной оси. Изменение этого на PRIMARY axis решило мою проблему.
листах("лист1").Диапазон ("A1: K1")=рабочие листы("sheet2").Диапазон ("A4:K4").Значение
сегодня у меня была аналогичная проблема с файлом 2010 с большим количеством формул и несколькими подключениями к базе данных. Ось диаграммы, которая не обновляла диапазоны ссылок со скрытыми столбцами, аналогично другим в этой цепочке, и метки отображали месяц и год "MMM-YY" динамических данных. Я попробовал все перечисленные решения, кроме опций VBA, поскольку я бы предпочел решить без кода.
Я смог решить проблемы, инкапсулировав мои даты (метки оси) в текстовой формуле как таковой: =TEXT (A10,"MMM-YY"). И все немедленно обновляется, когда значения меняются. Снова счастливые дни.
от чтения других вопросов участников выше я начал думать, что диаграммы имели проблемы с типом данных даты конкретно, и поэтому преобразование значений в текст с помощью функции TEXT решило мою проблему. Надеюсь, это поможет и вам. Просто измените формат в двойных кавычках (второй аргумент текстовой функции) на одеть ваши потребности.
просто активируйте лист, на котором находится диаграмма:
и ваша проблема исчезнет.
у меня была та же проблема, и ни одна из вещей, о которых вы упомянули, не работала для меня, пока я не активировал лист. Принятый ответ тоже не сработал.
Предположим, вы создали диаграмму для отслеживания ежедневных продаж на основе ряда данных в вашей книге. Но вам нужно менять или редактировать количество данных каждый день, в этом случае вам нужно обновить диаграмму вручную, чтобы она включала новые данные. Существуют ли какие-либо быстрые приемы, которые помогут вам автоматически обновить диаграмму при добавлении новых данных в существующий диапазон диаграммы в Excel?
Автоматическое обновление диаграммы после ввода новых данных с созданием таблица
Автоматическое обновление диаграммы после ввода новых данных с помощью динамической формулы
![]()
Автоматически обновлять диаграмму после ввода новых данных с созданием таблицы
Если у вас есть следующий диапазон данных и столбчатая диаграмма, теперь вы хотите, чтобы диаграмма обновлялась автоматически при вводе новой информации. В Excel 2007, 2010 или 2013 вы можете создать таблицу для расширения диапазона данных, и диаграмма будет обновляться автоматически. Пожалуйста, сделайте следующее:
1 . Выберите диапазон данных и нажмите Таблица на вкладке Вставить , см. Снимок экрана:
. В диалоговом окне Создать таблицу , если у ваших данных есть заголовки, установите флажок Моя таблица имеет заголовки , затем нажмите ОК . См. Снимок экрана:
3 . Диапазон данных отформатирован как таблица, см. Снимок экрана:
4 . Теперь при добавлении значений за июнь график обновится автоматически. См. Снимок экрана:
Примечания:
1. Новые вводимые данные должны находиться рядом с указанными выше, это означает, что между новыми и существующими данными нет пустых строк или столбцов.
2. В таблице вы можете вставлять данные между существующими значениями.
![]()
Автоматическое обновление диаграммы после ввода новых данных с помощью динамической формулы
Но иногда вы не хотите изменять диапазон на таблицу, и описанный выше метод не работает. доступно в Excel 2003 или более ранней версии. Здесь я могу познакомить вас с методом сложных динамических формул. В качестве примера возьмем следующие данные и диаграмму:
1 . Во-первых, вам нужно создать определенное имя и динамическую формулу для каждого столбца. Нажмите Формулы > Определить имя .
2 . В диалоговом окне Новое имя введите Дата в поле Имя и выберите имя текущего листа из Scope и введите формулу = OFFSET ($ A $ 2,0,0, COUNTA ($ A: $ A) -1) в Относится к блоку , см. снимок экрана:
3 . Нажмите OK , а затем повторите два вышеуказанных шага, вы можете создать динамический диапазон для каждой серии, используя следующие имена диапазонов и формулы:
- Столбец B: Ruby: = OFFSET ($ B $ 2,0,0, COUNTA ($ B: $ B) -1);
- Столбец C: Джеймс: = OFFSET ($ C $ 2,0,0, COUNTA ($ C: $ C) -1);
- Столбец D : Freda: = OFFSET ($ D $ 2,0,0, COUNTA ($ D: $ D) -1)
Примечание : в приведенных выше формулах функция OFFSET относится к первой точке данных, а COUNTA относится ко всему столбцу данных.
4 . После определения имен и формул для данных каждого столбца щелкните правой кнопкой мыши любой столбец на диаграмме и выберите Выбрать данные , см. Снимок экрана:
5 . В диалоговом окне Выбор источника данных в разделе Записи легенд (серии) нажмите Ruby , а затем нажмите Изменить , в появившемся диалоговом окне Редактировать серию введите = Sheet3! Ruby под Значения серии. раздел, см. скриншоты:
 |
 |
 |
6. Затем нажмите OK , чтобы вернуться в диалоговое окно Выбрать источник данных , повторите шаг 5, чтобы обновить оставшиеся серии, чтобы отразить их динамические диапазоны:
- James: Значения серий: = Sheet3! James;
- Freda: Значения серии: = Sheet3! Freda
7 . После настройки левых данных теперь вам нужно нажать кнопку Edit под Ярлыки горизонтальной (категории) оси , чтобы установить этот параметр, см. Снимки экрана:
 |
| |
 |
8 . Затем нажмите OK > OK , чтобы закрыть диалоговое окно Выбрать источник данных . Выполнив эти шаги, вы обнаружите, что диаграмма обновляется автоматически, когда вы добавляете новые данные на лист.
Примечания:
- 1. Вы должны вводить новые данные непрерывно, если вы пропустите строки, этот метод не будет работать должным образом.
- 2. Если вы введете данные нового столбца, этот метод не вступит в силу.
Экспорт графики (изображения/диаграммы/фигуры/все типы) из книги в папка как Gif/Tif/PNG/JPEG
Как добавить горизонтальную среднюю линию на диаграмму в Excel?
Как создать комбинированные диаграммы и добавить для нее вторичную ось в Excel?
Читайте также: