Формула Excel для подсчёта и суммирования при изменении значения: инструкция и примеры

Как суммировать изменения значений в Excel с помощью VBA: пошаговое руководство для новичков

Если вы только начинаете работать с VBA и Excel, при обработке быстро меняющихся данных от внешних источников может возникнуть вопрос: как правильно суммировать изменения значений в ячейках? В этой статье мы подробно разберем задачу, когда двум потокам данных соответствуют пары ячеек, значения которых обновляются каждую секунду. Нам нужно считать сумму уменьшений одного параметра и увеличений другого, а также наоборот, и каждые 90 событий обнулять результаты.

Постановка задачи: что нужно реализовать

Представим, что у нас есть две основные ячейки с данными — A1 и B1, значения которых обновляются каждую секунду. Например, значения в A1 меняются в последовательности: 10, 16, 8, 12, 3, а в B1 — 8, 1, 6, 12, 19.

Требуется:

• В ячейке C1 суммировать общие величины уменьшения A1 (когда значение снижается) и увеличения B1 (когда значение растёт). Для приведенных данных это будет: (10 до 16 — нет уменьшения; 16 до 8 — уменьшение 8; 8 до 12 — нет; 12 до 3 — уменьшение 9) + (8 до 1 — нет; 1 до 6 — увеличение 5; 6 до 12 — увеличение 6; 12 до 19 — увеличение 7) = (8 + 9) + (5 + 6 + 7) = 35.

• В ячейке D1 нужно суммировать общие величины увеличения A1 и уменьшения B1 (например, 16-10 = 6, 12-8 = 4 и 8-1 = 7), таким образом итог будет равен 17.

Аналогично данные представлены в 6 парах ячеек (A1 и B1, A2 и B2,… A6 и B6), результаты нужно выводить в соответствующих парах столбцов C и D.

Дополнительно, в ячейке E1 находится таймер, отсчитывающий время от 90 до -90. Когда это время закончится, все суммы должны сброситься и начать вычисляться заново.

Решение: структура книги Excel и подготовка листов

Для удобства и оптимальной производительности рекомендуется разделить рабочую книгу на два листа:

• Feed — принимает данные от потоков; здесь находятся исходные ячейки с быстро обновляющимися значениями.
• HistoryTables — на этом листе будут сохраняться таблицы с историей значений для последующего подсчета.

Важный момент: имя листа "History" не подходит, поэтому используйте "HistoryTables" или любое другое.

Имя листов в VBA:

• Лист Feed назовите wsFeed
• Лист HistoryTables назовите wsHistory

Настройка таблиц для истории значений

На листе HistoryTables создайте две таблицы с одним столбцом:

• history1 с одним столбцом history1
• history2 с одним столбцом history2

В этих таблицах будут храниться все значения из ячеек feed1 и feed2.

Настройка именованных диапазонов для входных данных

Назовите ячейки на листе Feed именами:

• feed1 — для первой ячейки с потоком данных (например, A1)
• feed2 — для второй ячейки (например, B1)

Это позволит VBA-коду однозначно обращаться к ним независимо от позиции в листе.

Программная часть: запись новых значений в таблицы

В коде листа wsFeed воспользуемся событием Worksheet_Change — обработчиком изменения значения ячейки.

vba
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = ThisWorkbook.Names("feed1").RefersToRange Then
RecordNewValue 1, Target.Value2
ElseIf Target = ThisWorkbook.Names("feed2").RefersToRange Then
RecordNewValue 2, Target.Value2
End If
End Sub

Этот обработчик проверяет какая из ячеек с именами feed1 или feed2 изменилась, и вызывает функцию RecordNewValue, которая сохраняет новое значение в соответствующей таблице истории.

В отдельном модуле напишите функцию:

vba
Option Explicit

Public Function RecordNewValue(i As Integer, newValue As Variant)
Dim t As ListObject
Set t = wsHistory.ListObjects("history" & i)

Dim newRow As ListRow
Set newRow = t.ListRows.Add

newRow.Range(1, 1).Value = newValue

End Function

Она добавляет новую строку в таблицу history1 либо history2 в зависимости от значения i.

Пользовательская функция на основе LAMBDA для подсчёта сумм

Чтобы вычислять суммы увеличений и уменьшений, создадим пользовательскую LAMBDA функцию, которую можно сохранить в Диспетчере имен.

excel
CUSTOM_SUM = LAMBDA(decrease, increase,
LET(
f, LAMBDA(x, y,
LET(delta, DROP(x, 1) — DROP(x, -1),
SUM(
IF(
y = "decrease",
IF(delta < 0, ABS(delta), 0),
IF(delta > 0, ABS(delta), 0)
)
)
)
),
f(decrease, "decrease") + f(increase, "increase")
)
)

Как эта функция работает:

• Функция принимает две последовательности данных: где нужно считать уменьшения и где — увеличения.
• Внутри определяется лямбда f, которая вычисляет разницу между последовательными элементами и суммирует только нужные изменения (только уменьшения или только увеличения).
• Итог — сумма всех требуемых изменений.

Как применять функцию в Excel

Для получения суммы уменьшений A и увеличений B используйте формулу:

excel
=CUSTOM_SUM(history1[history1], history2[history2])

Для обратного варианта (увеличения A и уменьшения B):

excel
=CUSTOM_SUM(history2[history2], history1[history1])

Важный момент: сброс данных по таймеру

В ячейке с таймером (например, E1) отсчитывается число от 90 до -90, которое обозначает время события. Вам нужно добавить VBA-логику, которая будет следить за значением таймера и после достижения -90:

• Очищать таблицы истории history1 и history2 (удалять все строки).
• Обнулять ячейки с результатами в столбцах C и D.

Вот пример, как очистить таблицу с помощью VBA:

vba
Sub ResetHistoryTables()
Dim t1 As ListObject, t2 As ListObject
Set t1 = wsHistory.ListObjects("history1")
Set t2 = wsHistory.ListObjects("history2")

Do While t1.ListRows.Count > 0
    t1.ListRows(1).Delete
Loop

Do While t2.ListRows.Count > 0
    t2.ListRows(1).Delete
Loop

' Очистить результаты на листе Feed, например, в C1:D6
wsFeed.Range("C1:D6").ClearContents

End Sub

Вы можете вызывать этот сброс по событию изменения значения в ячейке таймера.

Тестирование: генерация случайных значений

Для теста и демонстрации работы алгоритма можно использовать следующий макрос, который будет подставлять случайные значения в ячейки feed1 и feed2 каждую секунду.

vba
Sub SimulateFeedUpdates()
Dim i As Integer
Dim rng1 As Range, rng2 As Range
Set rng1 = ThisWorkbook.Names("feed1").RefersToRange
Set rng2 = ThisWorkbook.Names("feed2").RefersToRange

For i = 1 To 10
    rng1.Value = WorksheetFunction.RandBetween(1, 20)
    rng2.Value = WorksheetFunction.RandBetween(1, 20)
    Application.Wait Now + TimeValue("0:00:01")
Next i

End Sub

Запустив макрос, можно наблюдать динамическое заполнение истории и автоматическое обновление сумм.

Итоги и рекомендации

• Для работы с быстро изменяющимися данными разделяйте исходные данные и историю изменений на разные листы.
• Записывайте каждое новое значение в таблицу истории с помощью VBA события изменения.
• Используйте современные функции Excel, такие как LAMBDA, для подсчётов на основании истории.
• Обязательно реализуйте логику сброса данных по таймеру, чтобы не накапливать бесконечно историю.
• Для удобного написания и тестирования LAMBDA функций рекомендуется использовать надстройку Excel Labs и Advanced Formula Environment.

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

Желаем успехов в освоении VBA и Excel! Если возникнут вопросы — всегда можно доработать представленный код под конкретные нужды.

Источник