Как автоматически обновить диаграмму в Excel после ввода новых данных?
Предположим, вы создали диаграмму в Excel для визуального отслеживания ежедневных данных о продажах и регулярно обновляете эти данные по мере поступления новых сведений о продажах. Обычно при вставке или изменении данных в диапазоне требуется вручную корректировать Диапазон данных диаграммы, чтобы она отображала самые свежие значения. Такая ручная процедура со временем становится утомительной и подверженной ошибкам, особенно при работе с большими наборами данных или часто меняющейся информацией. К счастью, существуют практичные способы автоматического обновления диаграмм при добавлении новых данных, что позволяет поддерживать актуальность ваших панелей мониторинга и отчётов.
В Excel доступно несколько способов автоматического обновления диаграмм, каждый из которых идеально подходит для определённых версий Excel и структур данных. Ниже представлены три эффективных решения: преобразование данных в таблицу Excel, использование динамических формул с именованными диапазонами и применение макроса VBA — особенно ценного при работе со сложными или специфическими требованиями.
Автоматическое обновление диаграммы после ввода новых данных с помощью создания таблицы
Автоматическое обновление диаграммы после ввода новых данных с помощью динамической формулы
Автоматическое обновление диаграммы после ввода новых данных с помощью кода VBA
Автоматическое обновление диаграммы после ввода новых данных с помощью создания таблицы
Если у вас есть непрерывный диапазон данных и построенная на его основе круговая диаграмма, вы можете обеспечить мгновенное обновление диаграммы при добавлении новых данных — просто преобразуйте диапазон в таблицу Excel. Эта функция доступна начиная с Excel 2007 и значительно упрощает работу с растущими наборами данных. Главное преимущество: диаграммы, ссылающиеся на такую таблицу, автоматически подхватывают новые строки, добавленные в неё. Вот как это сделать:
1. Выделите имеющийся диапазон данных, включающий как заголовки, так и ежедневные значения. Затем перейдите на вкладку Вставка и нажмите Таблица. См. снимок экрана:
2. В диалоговом окне Создание таблицы убедитесь, что установлен флажок Таблица содержит заголовки, если ваши данные содержат заголовки. Затем нажмите ОК. (Если ваш диапазон не содержит заголовков, оставьте этот флажок снятым.)
3. Теперь ваш диалог «Выбрать данные» отформатирован как структурированная таблица Excel. Обратите внимание: стиль таблицы применяется автоматически, как показано ниже:
4. Теперь, когда вы добавляете новые строки сразу под последней строкой таблицы (например, вводите данные за июнь), и таблица, и связанная с ней диаграмма автоматически расширяются, отображая актуальные данные — без каких-либо дополнительных действий. См. пример ниже:
Примечания и практические советы:
1. Новые данные следует вводить непосредственно рядом с существующими — без пустых строк или столбцов между ними, иначе таблица (и диаграмма) не распознают расширение.
2. Вы можете вставлять новые строки в любое место таблицы — диаграмма автоматически обновится, что особенно удобно, например, при добавлении исторических записей.
3. Если диаграмма не обновляется корректно, убедитесь, что диапазон «Исходные данные», используемый диаграммой, ссылается на таблицу, а не на статический диапазон.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Автоматическое обновление диаграммы после ввода новых данных с помощью динамической формулы
Если вы не хотите преобразовывать данные в таблицу Excel, можно использовать динамические именованные диапазоны на основе формул. Этот метод использует функции СМЕЩ и СЧЁТЗ, чтобы автоматически определять диапазоны, изменяющие свой размер в зависимости от объёма данных. Такой подход особенно полезен, когда структура данных остаётся неизменной, но записи регулярно добавляются или удаляются. Практические шаги приведены ниже:
1. Начните с создания динамического именованного диапазона для каждого столбца данных. Перейдите на вкладку Формулы и нажмите Имя диапазона.
2. В диалоговом окне Новое имявведите подходящее имя (например,)Дата для столбца с датами), выберите нужный лист в поле Область и укажите динамическую формулу в поле Ссылка на. Например: =OFFSET($A$2,0,0,COUNTA($A:$A)-1). См. снимок экрана:
3. Нажмите ОК, чтобы сохранить. Повторите эти действия для каждого соответствующего ряда или столбца данных, используя следующие формулы:
- Столбец B: Руби: =OFFSET($B$2,0,0,COUNTA($B:$B)-1);
- Столбец C: Джеймс: =OFFSET($C$2,0,0,COUNTA($C:$C)-1);
- Столбец D: Фреда: =OFFSET($D$2,0,0,COUNTA($D:$D)-1)
Эти динамические именованные диапазоны автоматически расширяются или сужаются при добавлении новых данных в каждый столбец. Имейте в виду, что формула СМЕЩ начинается с первой строки данных, а функция СЧЁТЗ подстраивает размер диапазона в зависимости от общего количества непустых ячеек в указанном столбце.
4. После того как все именованные диапазоны определены, щёлкните правой кнопкой мыши по любому из столбцов на связанной диаграмме и выберите в контекстном меню пункт Выбрать данные.
5. В диалоговом окне Выбрать данные источника выделите нужный ряд (например, «Руби»), нажмите Изменить и укажите соответствующий динамический диапазон в поле Значения ряда(например,)=Sheet3!Ruby). См. ниже:
 |
 |
 |
6.Повторите для каждого дополнительного ряда, указав соответствующий динамический именованный диапазон:
- Джеймс: Значения ряда: =Sheet3!James;
- Фреда: Значения ряда: =Sheet3!Freda
7. Для горизонтальных меток оси нажмите Изменить в разделе Горизонтальные метки оси и укажите динамическое имя ячейки для столбца дат.
 |
|
 |
8. Нажмите ОК, чтобы подтвердить изменения и закрыть все диалоговые окна. Теперь при добавлении новых записей в рабочий лист диаграмма будет автоматически обновляться, отображая самые свежие данные.
- 1. Данные следует вводить в смежные ячейки столбцов — динамическая формула не учитывает пропуски между строками. Если строки будут пропущены, автоматическое расширение может работать некорректно.
- 2. При таком подходе добавление новых заголовков не приводит к автоматическому созданию дополнительных строк или столбцов; вам придётся вручную создать новые именованные диапазоны и соответствующим образом обновить исходный диапазон диаграммы.
- 3. Если динамический диапазон не расширяется, дважды проверьте диапазон функции СЧЁТЗ и убедитесь, что под вашими данными нет посторонних записей.
- 4. Если вы измените имя листа или расположение ячеек, обязательно обновите ссылки именованных диапазонов, чтобы сохранить их динамическое поведение.
Автоматическое обновление диаграммы после ввода новых данных с помощью кода VBA
Для решения сложных задач — таких как работа с несмежными данными, автоматическое обнаружение полностью новых рядов данных или одновременное обновление нескольких диаграмм — макрос VBA обеспечивает значительно бо́льшую гибкость и автоматизацию. Создав короткий макрос, реагирующий на изменения в данных, вы сможете автоматически обновлять исходный диапазон диаграммы и эффективно справляться с более сложными сценариями, которые недоступны при использовании предыдущих методов.
Это решение рекомендуется, если ваши данные разбросаны или не образуют регулярный блок, либо если вы регулярно добавляете новые ряды или столбцы в диаграмму. Следуйте приведённым ниже шагам для настройки:
1. Сначала вставьте диаграмму обычным способом.
2. Нажмите Alt + F11, чтобы открыть редактор VBA.
3. В редакторе VBA выберите Вставка > Модуль, чтобы добавить новый модуль кода. Затем вставьте следующий макрокод в окно модуля:
Sub AutoUpdateChartData()
Dim ws As Worksheet
Dim chrt As ChartObject
Dim lastRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set chrt = ws.ChartObjects(1) ' Modify if you have more than 1 chart on the sheet
' Find the last row of data in column A (assume your data starts from A1, adjust as needed)
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Set the data range for the chart dynamically (Modify range as per your data location)
chrt.Chart.SetSourceData Source:=ws.Range("A1:D" &, lastRow)
On Error GoTo 0
End Sub3. Чтобы запустить макрос, нажмите кнопку Выполнить. Ваша диаграмма немедленно обновится и отразит все текущие данные до последней заполненной строки.
Чтобы повысить уровень автоматизации, настройте автоматический запуск этого макроса при вводе новых данных.
Чтобы применить этот код, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код, затем вставьте приведённый выше код в модуль листа. Макрос будет автоматически запускаться при любых изменениях на листе, обеспечивая актуальность диаграммы.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
xTitleId = "KutoolsforExcel"
Call AutoUpdateChartData
End SubСоветы и примечания:
- Ваш диапазон данных (например, «A1:D» & lastRow) необходимо скорректировать в соответствии с реальным расположением и структурой вашего набора данных. Для несмежных диапазонов рекомендуется задавать строку диапазона непосредственно в коде.
- Если на листе несколько диаграмм, возможно, потребуется изменить ChartObjects(1), чтобы указать нужную диаграмму, или организовать цикл по всем объектам ChartObjects на листе — в зависимости от задачи.
- Это решение на VBA обеспечивает максимальную гибкость при работе с динамическими и сложными наборами данных, но требует включения макросов и сохранения файла в формате книги с поддержкой макросов (.xlsm).
- Если диаграмма не обновляется корректно, убедитесь, что диапазон «Исходные данные» в макросе совпадает с вашим фактическим блоком данных, а также проверьте, включены ли макросы в вашей среде Excel.
Связанные статьи:
Как добавить на диаграмму в Excel горизонтальную линию среднего значения?
Как создать комбинированную диаграмму и добавить к ней вторую ось в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек