Динамическая диаграмма отличается от обычной только тем, что автоматически перестраивается каждый раз при добавлении в таблицу новых данных. Такие диаграммы удобны в оперативном наблюдении за каким-либо процессом и могут быть построены в двух базовых видах: те, что отображают ретроспективу данных полностью и те, что держат на виду только актуальную по времени ее часть. В обоих случаях для оформления подобных кривых Excel предлагает использовать достаточно специфичные средства, суть работы которых не всегда ясна сразу. Прежде всего сюда относятся диспетчер имен и динамические диапазоны. В свою очередь, для создания последних применяется команда «Смещение» — вспомогательный инструмент, почти всегда действующий в паре с какой-либо основной функцией и просто указывающий ей, какие именно данные нужно взять в обработку. Так, в рассмотренных ниже примерах с использованием динамических диапазонов роль такой основной функции в итоге играет мастер диаграмм.
Представим, что ежедневно отмечаем в специальном журнале температуру воздуха за окном и требуется построить график, иллюстрирующий эти измерения. Таблица исходных данных тогда будет содержать всего два столбца: номер дня и соответствующая этому дню температура, рисунок 1.
Дни на графике откладываются по оси абсцисс, температура — по оси ординат. Поскольку осей две, то и динамических диапазонов понадобится столько же. Для диаграммы первого типа они соответственно примут вид:
=СМЕЩ(Лист1!$A$2;0;0;(СЧЁТ(Лист1!$A:$A));1)
=СМЕЩ(Лист1!$B$2;0;0;(СЧЁТ(Лист1!$B:$B));1)
Из приведенных записей первая буквально значит следующее: данные для оси абсцисс взять из диапазона, отстоящего от зафиксированной точки отсчета в виде ячейки A2 на листе 1 на 0 строк вниз и на 0 столбцов вправо (т.е., верхняя граница диапазона совпадает с ячейкой A2), при этом высота диапазона будет равна количеству подсчитанных с помощью функции «Счет» численных значений в столбце A, а ширина — 1 столбцу. Что касается ячейки A1, она пропущена и не закреплена только потому, что содержит заголовок столбца, который не должен учитываться в построении графика. Аналогичный смысл с поправкой на столбец B имеет и вторая запись, относящаяся к ординатам.
Формулы диапазонов необходимо зарегистрировать в диспетчере имен. Для этого на вкладке «Формулы» нужно нажать кнопку «Диспетчер имен» и, потом — «Создать». В открывшемся окне, в графе «Имя» для первого диапазона ввести «Абсциссы», а для второго «Ординаты», в графе «Область» для обоих случаев указать текущий лист, в графу «Диапазон» скопировать соответствующую формулу и нажать кнопку «OK», рисунок 2.
Последним шагом на лист вставляется стандартная точечная диаграмма, для которой при выборе данных записываются зарегистрированные имена: в графе «Значения X» — «=Лист1!Абсциссы», в графе «Значения Y» — «=Лист1!Ординаты», рисунок 3. Если все сделано верно, то при добавлении в таблицу новых данных, их количество будет автоматически пересчитываться, верхняя граница диапазона — оставаться на месте, нижняя — смещаться, а сам график — достраиваться, охватывая все введенные значения, файл примера 1.
Когда значений в таблице наблюдений изрядно прибавится, построенный на их основе график станет хуже читаться. Тогда имеет смысл вывести на диаграмму только некоторое число последних точек, убрав из визуализации все предыдущие. Скажем, если в таблице на данный момент 25 строк, а оставить для графика удобно 10 последних, то, очевидно, 15 первых строк должны быть исключены. Другими словами, диапазон будет отстоять от зафиксированной ячейки A2 на 15 строк, тем самым пропуская их и охватывая собой только 10 строк в конце. Тогда динамические формулы примут вид:
=СМЕЩ(Лист1!$A$2;СЧЁТ(Лист1!$A:$A)-10;0;10;1)
=СМЕЩ(Лист1!$B$2;СЧЁТ(Лист1!$B:$B)-10;0;10;1)
Как и раннее, в данных записях функция «Счет» сначала подсчитывает количество введенных значений, затем из него вычитается обозначенное число 10 и на полученную разность диапазон смещается от зафиксированной ячейки вниз, в нашем случае — на 15 строк. Смещение по столбцам отсутствует, ширина диапазона — прежняя, а вот длина его изменилась и стала равной 10 в соответствии с заданным условием. Из расчета также видно, что с ростом таблицы увеличится и разность между числом строк в ней и числом строк к отображению, заставляя диапазон постоянно смещаться и, таким образом, переписывая график, файл примера 2.
Кроме рассмотренных существуют и более простые способы построить динамическую диаграмму. Так, две следующие формулы не требуют использования диспетчера имен и даже, по сути, не являются динамическими. Они вводятся сразу в окно с выбором данных для графика и кривая, программируемая ими, будет обладать в определенной степени всеми качествами диаграммы первого типа:
=Лист1!$A$2:$A$1000
=Лист1!$B$2:$B$1000
Как видно из формул, в них вообще нет ни одной функции. Они просто резервируют к построению статичный диапазон от второй до тысячной ячейки и по мере заполнения таблицы каждая следующая строка будет отображаться на диаграмме, файл примера 3. Можно зарезервировать и большее число ячеек, однако не стоит указывать тот или другой столбец полностью, поскольку это приведет к замедлению работы Excel.
Диаграмму же второго типа можно построить посредством простейших функций «Макс» и «Просмотр». С помощью первой из них в любом свободном месте выводится максимальное значение из столбца с абсциссами, а с помощью второй — соответствующее ей значение из соседнего столбца с ординатами. Таким образом находятся координаты для последней, десятой как в уже рассмотренном примере точки будущего графика. Далее, в пустой ячейке строчкой выше вычтем от найденного максимума 1, получив значение предыдущей абсциссы, а функция «Просмотр» аналогичным образом отобразит для нее соседнюю ординату. В результате, шаг за шагом получится новая таблица, которая всегда будет копией определенного числа последних строк оригинальной. По этой новой таблице и строится график, получающий в итоге возможность изменяться по мере того, как в исходную таблицу добавляются новые данные, файл примера 4.
Файл примера 1: https://disk.yandex.ru/i/3qAXYmCu0ym0vA
Файл примера 2: https://disk.yandex.ru/i/WSb4-hQQ5s7lcw
Файл примера 3: https://disk.yandex.ru/i/RrrrGLyt1YJOFw
Файл примера 4: https://disk.yandex.ru/i/odNFwNE23S3lww