Как автоматически обновить диаграмму после ввода новых данных в Excel?
Предположим, вы создали диаграмму в Excel для визуального отслеживания ежедневных данных о продажах и регулярно обновляете эти данные по мере регистрации новых продаж. Обычно, когда вы добавляете или изменяете данные в вашем диапазоне, может потребоваться вручную настраивать диапазон данных диаграммы, чтобы убедиться, что она показывает последние значения. Этот ручной процесс может стать повторяющимся и подверженным ошибкам, особенно при работе с большими наборами данных или часто меняющейся информацией. К счастью, существуют практические методы для автоматического обновления ваших диаграмм при добавлении новых данных, помогая держать ваши панели управления или отчеты постоянно актуальными.
Существует несколько способов автоматического обновления диаграммы в Excel, каждый из которых подходит для разных версий Excel и структур данных. Рассмотренные ниже решения включают преобразование ваших данных в таблицу Excel, использование динамических формул с именованными диапазонами и — особенно полезное для сложных или специальных требований — применение макроса VBA.
Автоматическое обновление диаграммы после ввода новых данных путем создания таблицы
Автоматическое обновление диаграммы после ввода новых данных с использованием динамической формулы
Автоматическое обновление диаграммы после ввода новых данных с помощью кода VBA
Автоматическое обновление диаграммы после ввода новых данных путем создания таблицы
Если у вас есть непрерывный диапазон данных вместе с соответствующей гистограммой, вы можете гарантировать мгновенное обновление диаграммы при добавлении новой информации, преобразовав диапазон данных в таблицу Excel. Этот подход доступен в Excel 2007 и более новых версиях и значительно упрощает управление расширяющимися наборами данных. Главное преимущество заключается в том, что диаграммы, ссылающиеся на таблицу, будут автоматически включать новые строки, добавленные в таблицу. Вот как это можно сделать:
1. Выберите существующий диапазон данных, включающий заголовки и ежедневные значения. Затем перейдите на вкладку Вставка и нажмите Таблица. См. скриншот:
2. В диалоговом окне Создание таблицы убедитесь, что опция «Моя таблица содержит заголовки» отмечена, если ваши данные содержат заголовки. Затем нажмите ОК. (Если ваш диапазон не содержит заголовков, оставьте этот флажок неотмеченным.)
3. Ваш выбранный диапазон данных теперь будет отформатирован как структурированная таблица Excel. Обратите внимание, что стиль таблицы применяется автоматически, как показано ниже:
4. Теперь, когда вы добавляете новые строки прямо под последней строкой таблицы (например, ввод данных за июнь), и таблица, и связанная диаграмма будут автоматически расширяться, показывая последние данные без дополнительных действий. Смотрите пример ниже для справки:
Примечания и практические советы:
1. Новые данные должны быть напрямую смежными — то есть между новыми и существующими данными не должно быть пустых строк или столбцов — иначе таблица (и диаграмма) не распознает расширение.
2. Вы можете вставлять новые строки в любом месте внутри таблицы; диаграмма автоматически обновится соответственно, что полезно для обновления исторических записей.
3. Если диаграмма не обновляется должным образом, проверьте, что источник данных диаграммы ссылается на таблицу, а не на статический диапазон.
Раскройте магию Excel с Kutools AI
- Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
- Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формул: Легко разбирайтесь в сложных формулах.
- Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Автоматическое обновление диаграммы после ввода новых данных с использованием динамической формулы
Если вы не хотите преобразовывать свои данные в таблицу Excel, вы можете использовать динамические именованные диапазоны, работающие на формулах. Этот метод использует функции OFFSET и COUNTA для определения диапазонов, которые автоматически изменяются в зависимости от фактического объема данных. Этот подход особенно полезен, когда структура данных фиксирована, но записи могут регулярно добавляться или удаляться. См. практические шаги ниже:
1. Начните с определения динамического именованного диапазона для каждого столбца данных. Перейдите на вкладку Формулы и нажмите Определить имя.
2. В диалоге Новое имя введите подходящее имя (например, Дата для столбца дат), выберите правильный лист в разделе Область, и введите динамическую формулу в поле Ссылается на. Например: =OFFSET($A$2,0,0,COUNTA($A:$A)-1). См. скриншот для справки:
3. Нажмите ОК для сохранения. Повторите шаги для каждого соответствующего ряда или столбца данных, используя формулы, такие как:
- Столбец B: Ruby: =OFFSET($B$2,0,0,COUNTA($B:$B)-1);
- Столбец C: James: =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. Повторите для каждого дополнительного ряда, ссылаясь на соответствующий динамический именованный диапазон:
- James: Значения ряда: =Sheet3!James;
- Freda: Значения ряда: =Sheet3!Freda
7. Для меток горизонтальной (категориальной) оси нажмите Изменить под Метки горизонтальной (категориальной) оси и введите динамическое имя диапазона для столбца дат.
 |
|
 |
8. Нажмите ОК для подтверждения и выхода из всех диалоговых окон. Теперь, когда вы продолжите добавлять новые записи данных в ваш рабочий лист, диаграмма автоматически обновится, чтобы отразить последние точки данных.
- 1. Данные должны быть введены в смежные ячейки в столбцах — динамическая формула не учитывает пробелы между строками. Если вы пропустите строки, автоматическое расширение может не работать должным образом.
- 2. Этот подход не учитывает дополнительные ряды или столбцы при добавлении новых заголовков; вам нужно будет создать новые именованные диапазоны и соответствующим образом обновить источник данных диаграммы.
- 3. Если динамический диапазон не расширяется, дважды проверьте диапазон COUNTA и убедитесь, что под вашими предполагаемыми данными нет лишних записей.
- 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек