Как обновить данные в диаграмме excel при изменении данных в таблице

Как обновить данные в диаграмме excel при изменении данных в таблице

Здравствуйте!
Существует ли способ настроить автообновление диаграммы при добавлении новых значений в таблицу,
либо присвоить определенный временной интервал обновления

Сейчас обновляется только при нажатии кнопки «Обновить данные», либо при открытии книги.

Пользователь
Сообщений: 1212 Регистрация: 31.01.2014
31.03.2015 16:25:30

без файла, могу сказать. если сделайте таблицу умной и на нее будет ссылаться диаграмма, то при добавлении в таблицу. кажется и диаграмма сразу меняется. (хотя могу ошибиться)

Пользователь
Сообщений: 38 Регистрация: 31.03.2015
31.03.2015 16:43:26
alexthegreat, Автоматически не меняется Только после нажатия кнопки «Обновить данные»
Пользователь
Сообщений: 24 Регистрация: 31.03.2015
31.03.2015 17:26:02

Пробовал с умными таблицами в Excel 2013 все работает. Как на удаление так и на добавление.
Приложите ваш файл, посмотрим.

Прикрепленные файлы

• Book3.xlsx (14.28 КБ)

Пользователь
Сообщений: 38 Регистрация: 31.03.2015
31.03.2015 17:26:46
Записал макрос обновления диаграмы.
Вопрос, как его заставить автоматически повторяться раз в минуту?

Sub Refresh() ActiveSheet.ChartObjects("Äèàãðàììà 3").Activate ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh End Sub

Пользователь
Сообщений: 38 Регистрация: 31.03.2015
31.03.2015 17:58:42

Цитата
dvoinykh написал:Приложите ваш файл, посмотрим.

Файл во вложении.
На листе 1 вводятся данные, обрабатываются формулами в результате получаем значения в H12:T12,
Затем, на листе 2 фиксируются все изменения этого диапазона H12:T12 в виде таблицы
На листе 3, я попытался создать сводную диаграмму. Но обновлять ее приходится только в ручную

Прикрепленные файлы

• Chart.xlsm (59.99 КБ)

Обновление данных в существующей диаграмме

Если вам нужно изменить данные на диаграмме, это можно сделать из источника.

Проверьте, как это работает!

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

Чтобы скрыть категорию на диаграмме, щелкните диаграмму правой кнопкой мыши и выберите пункт «Выбрать данные». Отмените выбор элемента в списке и нажмите кнопку «ОК».

Чтобы отобразить скрытый элемент на диаграмме, щелкните правой кнопкой мыши и выберите «Данные» и выберите его в списке, а затем нажмите кнопку «ОК».

Проверьте, как это работает!

Вы можете обновить данные на диаграмме в Word, PowerPoint для macOS и Excel, обновив исходный Excel листе.

Доступ к исходному листу данных из Word илиPowerPoint для macOS

Диаграммы, отображаемые в Word или PowerPoint для macOS, создаются Excel. При изменении данных на листе Excel изменения отображаются на диаграмме в Word или PowerPoint для macOS.

1. Выберите режим >макета печати.
2. Выделите диаграмму.
3. Выберите «Конструктор диаграммы >изменить данные в Excel. Откроется приложение Excel с таблицей данных для диаграммы.

PowerPoint для macOS

1. Выделите диаграмму.
2. Выберите «Конструктор диаграммы >изменить данные в Excel. Откроется приложение Excel с таблицей данных для диаграммы.

Изменение данных в диаграмме

1. Выберите исходную таблицу данных на Excel таблице.

• Серой заливкой выделяется строка или столбец, используемые для оси категорий.
• Красной заливкой выделяется строка или столбец с метками рядов данных.
• Синей заливкой обозначаются точки данных, построенные на диаграмме.

Изменение акцентированной оси диаграммы

Вы можете изменить способ отображения строк и столбцов таблицы на диаграмме. Диаграмма отображает строки данных из таблицы по вертикальной оси (значению) диаграммы и столбцы данных на горизонтальной оси (категории). Вы можете изменить способ построения диаграммы в обратном направлении.

Выделение продаж по инструменту

Выделение продаж по месяцам

1. Выделите диаграмму.
2. Выберите «Конструктор >«, чтобы переключить строку или столбец.

Изменение порядка рядов данных

Вы можете изменить порядок ряда данных на диаграмме с несколькими рядами данных.

1. На диаграмме выберите ряд данных. Например, если щелкнуть столбец гистограммы, будут выделены все столбцы этого ряда данных.
2. Выберите конструктор диаграммы >«Выбор данных».
3. В диалоговом окне Выбор источника данных в разделе Элементы легенды (ряды) используйте стрелки вверх и вниз для перемещения ряда в списке. В зависимости от типа диаграммы некоторые параметры могут быть недоступны.

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

Изменение цвета заливки ряда данных

1. На диаграмме выберите ряд данных. Например, если щелкнуть столбец гистограммы, будут выделены все столбцы этого ряда данных.
2. Выберите Формат.
3. В разделе «Стили элементов диаграммы» выберите и выберите цвет.

Добавление меток данных

Можно добавить метки для отображения значений точек данных из Excel на диаграмме.

1. Выделите диаграмму, а затем выберите «Конструктор диаграммы».
2. Выберите «Добавить элемент диаграммы >меток данных».
3. Выберите расположение метки данных (например, » Внешние точки»). В зависимости от типа диаграммы некоторые параметры могут быть недоступны.

Добавление таблицы данных

1. Выберите диаграмму и щелкните вкладку.
2. Выберите элемент «>диаграммы», чтобы >таблицу данных.
3. Выберите параметры. В зависимости от типа диаграммы, некоторые параметры могут быть недоступны.

Excel. Диаграмма, изменяющаяся при добавлении данных

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

В качестве пример возьмем курс доллара (рис. 1). Для начала создадим обычную диаграмму (тип «График с маркерами»).

Рис. 1. График с маркерами

Скачать заметку в формате Word, примеры в формате Excel

Далее создадим два именованных динамических диапазона: один для меток категорий (Даты), второй – для точек данных (Курс $). Для создания именованного диапазона пройдите по меню Формулы → Диспетчер имен (рис. 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 последних значений

Использование динамических именованных диапазонов с диаграммами обеспечит исключительную гибкость и сэкономит огромное количество времени и усилий, которые вы потратили бы на настройку диаграмм после добавления еще одной записи к исходным данным!

Трюк №54. Три быстрых способа обновления диаграмм

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

Перетаскивание данных

Можно добавить данные к существующему ряду или создать абсолютно новый ряд данных, просто перетащив данные на диаграмму. 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 первая часть ссылки, Sheet1!$B$1, относится к имени или заголовку диаграммы — 2004. Вторая часть ссылки, Sheet1!$A$2:$A$5, относится к значениям по оси X, в данном случае — к месяцам. Третья часть ссылки, Sheet1!$B$2:$B$5, относится к значениям по оси Y, то есть 7.43, 15, 21.3 и 11.6. Наконец, последняя часть формулы, 1, относится к порядковому номеру графика, или к номеру ряда. В данном случае, когда у нас только один ряд, значение может быть равно только 1. Если бы рядов было несколько, у первого ряда был бы номер 1, у второго — номер 2 и т. д.

Рис. 5.17. Обыкновенная гистограмма с выделенной строкой формул

Чтобы изменить диаграмму, измените ссылки на ячейки в строке формул. Помимо ссылок на ячейки, в диаграммы можно вводить и явные значения, известные как массивы констант (подробнее об этом в разделе «Константы в формулах массива» справки по Excel — для вызова справки нажмите кнопку F1). Для этого добавьте <> (фигурные скобки) вокруг значений по осям X и Y, как показано в следующей формуле: =SERIES(«My Ваr»;;;1), в русской версии Excel =РЯД(«My Ваr»;;;1). В этой формуле РЯД (SERIES) А, В, С и D — это значения по оси X, а 1, 2, 3 и 4 — соответствующие им значения по оси Y. Используя этот метод, можно создавать и обновлять диаграммы, не храня данные в ячейках.

Перетаскивание граничной области

Если диаграмма содержит ссылки на последовательные ячейки, можно легко увеличивать или уменьшать данные ряда, перетаскивая граничную область в желаемую точку. Медленно щелкните ряд данных, который хотите увеличить или уменьшить. После двух медленных щелчков по краям ряда появятся черные квадратики (маркеры). Все, что нужно, — щелкнуть квадратик и перетащить границу в желаемом направлении (рис. 5.18).

Рис. 5.18. Выделенная граничная область ряда диаграммы

Если увеличивать или уменьшать ряд данных, исходный источник данных, а также подписи осей, если для них выбран параметр Авто (Auto), обновляются, отражая сделанные изменения. Это прекрасная возможность проверки сценариев, если вы хотите узнать, какие получатся результаты для различных наборов данных.