Как рассчитать кумулятивную сумму (скользящий итог) для столбца в Excel?
В анализе данных отслеживание того, как значения накапливаются со временем или по записям, может иметь решающее значение. Кумулятивная сумма, также известная как скользящий итог, позволяет непрерывно следить за совокупным значением чисел в последовательности, автоматически обновляясь при добавлении новых записей. Это особенно полезно для ведения финансовых регистров, управления запасами, учёта продаж и любых других задач, где важны прогрессивные итоги. В этой статье вы найдёте подробные пошаговые инструкции по расчёту скользящих итогов в Excel, а также практические советы, примечания и рекомендации по устранению неполадок, адаптированные под распространённые реальные сценарии.
Расчёт кумулятивной суммы или скользящего итога столбца с помощью формул
Расчёт кумулятивной суммы или скользящего итога столбца с помощью формул
Чтобы получить кумулятивную сумму для столбца данных в Excel, используйте простые формулы, которые динамически рассчитывают скользящий итог по мере добавления новых значений.
1. Введите эту формулу: =SUM($B$2:B2) в пустую ячейку рядом с вашими данными (например, если ваши числа находятся в столбце B, начиная со строки 2, введите эту формулу в ячейку C2), как показано на снимке экрана:
2. Нажмите Enter, чтобы подтвердить формулу. В ячейке появится кумулятивная сумма до этой строки. Чтобы применить расчёт к дополнительным строкам, выделите ячейку с формулой и перетащите маркер заполнения (небольшой квадрат в правом нижнем углу активной ячейки) вниз до нужного количества ячеек. См. снимок экрана:
Примечание: По умолчанию приведённая выше формула продолжает отображать последний рассчитанный итог даже в строках, где столбец B пуст. Это может вызывать путаницу при просмотре результатов под вашими исходными данными. Для более чистого и аккуратного вывода — особенно в отчётах или на панелях мониторинга — используйте улучшенную формулу ниже, которая оставляет ячейки пустыми, если отсутствуют соседние значения:
=IF(B2="","",SUM($B$2:B2))
Таким образом, скользящий итог отображается только в тех строках, где в столбце B есть фактические значения. Улучшенный результат показан на снимке экрана ниже:
При копировании этих формул убедитесь, что диапазон ссылок ($B$2:B2) остаётся неизменным. Если вы начинаете расчёты с другой строки, скорректируйте ссылки соответствующим образом. Для больших наборов данных рекомендуем использовать таблицы Excel — они автоматически адаптируют диапазоны по мере роста данных.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Код VBA — автоматизация расчёта кумулятивной суммы для динамических наборов данных или больших диапазонов
Пользователям, работающим с очень большими диапазонами, постоянно растущими наборами данных или нуждающимся в автоматизации расчётов на нескольких листах, макрос VBA предлагает эффективную альтернативу для вычисления кумулятивных сумм. С его помощью можно избавиться от необходимости постоянно перетаскивать формулы, автоматизировать пересчёт после обновления данных и легко адаптироваться к изменяющейся структуре. Этот подход идеально подходит для пользователей среднего уровня, знакомых с основами работы с макросами.
1. Сначала откройте редактор VBA: нажмите Инструменты разработчика > Visual Basic. В новом окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль и вставьте приведённый ниже код в окно модуля:
Sub CalculateCumulativeSum()
Dim selRange As Range
Dim outputCol As Integer
Dim i As Long
Dim cumSum As Double
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set selRange = Application.Selection
Set selRange = Application.InputBox("Select the range for running total calculation (single column):", xTitleId, selRange.Address, Type:=8)
If selRange Is Nothing Then Exit Sub
outputCol = selRange.Columns(selRange.Columns.Count).Column + 1
cumSum = 0
For i = 1 To selRange.Rows.Count
If IsNumeric(selRange.Cells(i, 1).Value) And selRange.Cells(i, 1).Value <> "" Then
cumSum = cumSum + selRange.Cells(i, 1).Value
selRange.Cells(i, 1).Offset(0, 1).Value = cumSum
Else
selRange.Cells(i, 1).Offset(0, 1).Value = ""
End If
Next i
End Sub2 Чтобы запустить макрос, нажмите кнопку 
или клавишу F5. При появлении запроса выберите диапазон данных (только один столбец), который необходимо обработать. Макрос сразу заполнит скользящие итоги в столбце справа.
Этот метод особенно эффективен для больших или динамически изменяющихся наборов данных, позволяя повторно запускать макрос всякий раз при обновлении данных. Однако всегда создавайте резервную копию данных перед запуском любого макроса, так как он напрямую перезаписывает значения ячеек. Для повышения производительности не запускайте этот макрос на Сортировать все столбцы или несмежных диапазонах.
Если макрос не вычисляет ожидаемые значения, убедитесь, что в диапазоне «Выберите диапазон» содержатся только числовые данные и отсутствуют объединённые ячейки.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек