Как использовать SCAN в Excel для надёжных накопительных сумм

Раньше для накопительной суммы я использовал трюк с абсолютно‑относительными ссылками. На первый взгляд это работает — но такие решения хрупки и требуют ручного сопровождения.

В простейшем случае столбец B содержит ежемесячную прибыль, а столбец C должен показывать накопительную сумму.

В ячейке C2 обычно писали:

=SUM($B$2:B2)

Потом двойной щелчок по маркеру заполнения — и формула копируется вниз. Но при вставке строки в середину диапазона Excel часто не заполняет новую строку формулой, а оставляет пустую ячейку, и тогда накопительная сумма ломается.

Если заранее протянуть формулу вниз на весь сезон, появляется другая проблема: пустые строки приводят к повторению итогов и путанице.

В случае, когда исходные данные оформлены как таблица Excel, приходится либо отказываться от структурированных ссылок, либо применять более сложные конструкции с INDEX, например:

=SUM(INDEX([Profit],1):[@Profit])

Эти обходные пути работают, но усложняют поддержку и легко ломаются при изменении структуры данных.

Функция SCAN выполняет сканирование массива и возвращает массив тех же размеров, разлитый из одной ячейки вниз. Это делает накопительные расчёты структурно устойчивыми: никакой ручной копипасты и меньше человеческих ошибок.

Синтаксис SCAN

=SCAN([initial_value], array, lambda)

• initial_value — начальное значение аккумулятора (часто 0).
• array — массив (или столбец) значений, который нужно просканировать.
• lambda — функция LAMBDA, которая описывает, как обновлять аккумулятор на каждом шаге.

Структура LAMBDA для SCAN

Третий аргумент принимает LAMBDA с тремя параметрами:

=LAMBDA(accumulator, value, body)

• accumulator — текущий накопленный результат (тот, что возвращается в выдаче).
• value — текущее значение массива на шаге сканирования.
• body — выражение, которое обновляет accumulator с учётом value.

Важно: SCAN не работает внутри объекта «Таблица» (Table) как единственная формула в столбце, потому что таблицы ожидают отдельную формулу для каждой строки. Поэтому результат SCAN выводится в обычный диапазон рядом с таблицей — но сам источник данных можно продолжать хранить в таблице.

Важно: SCAN требует динамических массивов (функция «spill»). Если видите ошибку #SPILL!, сначала убедитесь, что в поле вывода нет мешающих значений.

Предположим, что столбцы Month и Profit находятся в таблице Excel с именем T_Profits, а столбец Running Total — в обычном диапазоне (например, в столбце D). Между таблицей и этим столбцом оставлен пустой буферный столбец, чтобы таблица не «захватила» внешний столбец.

В ячейку D2 я пишу одну формулу:

=SCAN(0, T_Profits[Profit], LAMBDA(a, b, a + b))

Где:

• initial_value = 0 — первый ряд содержит первую запись набора.
• T_Profits[Profit] — столбец значений прибыли в таблице T_Profits.
• LAMBDA(a,b,a+b) — a = накопленный итог, b = текущая прибыль, a+b = обновлённый итог.

Преимущества этого метода:

• При вставке строки в середину таблицы результат автоматически адаптируется и ждёт ввода данных в новую ячейку.

• Нет необходимости «тянуть» формулы вниз — достаточно ввести новую строку в таблице, и SCAN отразит изменения без ручного обновления.

Скользящее среднее можно получить, комбинируя SCAN и SEQUENCE. SCAN подсчитывает накопительный итог, а SEQUENCE формирует список индексов 1,2,3…, по которому делим этот итог.

Формула в D2:

=SCAN(0, T_Profits[Profit], LAMBDA(a, b, a + b)) / SEQUENCE(ROWS(T_Profits[Profit]))

• SCAN вычисляет накопительную сумму.
• SEQUENCE(ROWS(…)) создаёт массив {1;2;3;…} одинаковой длины.
• Деление даёт среднее от начала до текущей строки.

Ограничение: если есть внутренние пустые строки (например, пропущенный май 2025), SEQUENCE не «знает» о пропусках и воспринимает пустоту как значение ноль, что исказит среднее. Перед применением убедитесь, что в исходных данных нет неожиданных пустых записей или замените пустые значения на NA()/исключение по логике.

Если нужно, чтобы накопление сбрасывалось в начале нового года, можно добавить переключатель сброса в LAMBDA, используя INDEX и проверку месяца.

Пример команды:

=SCAN(0, SEQUENCE(ROWS(T_Profits[Profit])), LAMBDA(a, i, IF(MONTH(INDEX(T_Profits[Month], i)) = 1, INDEX(T_Profits[Profit], i), a + INDEX(T_Profits[Profit], i))))

Как это работает:

• SEQUENCE(ROWS(…)) формирует последовательность индексов строк.
• В LAMBDA параметр i — текущий номер строки.
• INDEX(T_Profits[Month], i) извлекает дату; если это январь (MONTH(…) = 1), возвращается только текущая прибыль (сброс), иначе — обычное a + profit.

• Правило «одной ячейки»: все spill‑формулы требуют ввода только в верхней ячейке диапазона. Изменение логики — одно изменение в одной ячейке.

• Ошибка #SPILL!: если появляется #SPILL!, проверьте, нет ли в столбце старых ручных формул и текстовых значений. Удалите мешающие ячейки — и разлив восстановится.

• Сохраняйте источник данных в формате таблицы (Table). Это даёт преимущества фильтрации и структурированных ссылок. Размещайте результаты SCAN в соседнем обычном диапазоне; таблица плюс внешний spill — устойчивый паттерн.

• Наименования: используйте понятные имена таблиц и столбцов (например, T_Profits, Month, Profit) — так формулы читаются легче.

• Если ваш рабочий лист строго требует, чтобы каждая строка в столбце имела собственную формулу (например, при экспорте в системы, которые читают только ячейку за ячейкой), таблицы и отдельные формулы предпочтительнее.

• Если нужно, чтобы результат был именно в столбце таблицы как структурированная формула на каждую строку, SCAN не подходит сам по себе — придётся либо использовать INDEX/SUM комбинации, либо генерировать вспомогательный столбец через Power Query.

• Power Query и Power Pivot — отличные альтернативы для больших наборов данных или когда нужны сложные агрегации и исторические срезы; SCAN удобен для динамических листов и визуальных отчётов.

• Модель «аккумулятора»: при использовании SCAN думайте о переменной «аккумулятор», которая проходит через массив и обновляется для каждой строки.

• Эвристика «одна формула — одна логика»: держите логику расчётов в одном месте — так проще тестировать и обновлять.

• Модель «таблица как источник, spill как вывод»: таблицы хранят данные; spill‑формулы читают и выводят результаты рядом.

1. Преобразуйте исходные данные в таблицу Excel (Ctrl+T) и проверьте имена столбцов.
2. Вставьте пустой столбец между таблицей и зоной вывода, чтобы таблица не захватила область spill.
3. Введите формулу SCAN в верхней ячейке вывода.
4. Убедитесь, что нет мешающих значений; устраните #SPILL!, если он появится.
5. Протестируйте: вставьте строку, удалите строку, измените дату — проверьте устойчивость.
6. Документируйте формулу (в комментарии к ячейке или в отдельном листе справки).

• После вставки строки в середину накопительные значения корректно пересчитываются без ручной правки формул.
• После удаления строки итоговые значения корректны и не дают ошибок #REF.
• При добавлении новой строки в конец таблицы SCAN автоматически расширяет разлив и показывает новые итоги.
• Отчёт легко поддерживать: изменение логики требует правки только в одной ячейке.

Тест 1 — вставка строки:

• Действие: вставить пустую строку в середину таблицы и ввести значение прибыли.
• Ожидание: накопительная колонка обновилась автоматически.

Тест 2 — удаление строки:

• Действие: удалить строку в середине набора.
• Ожидание: накопительная колонка корректно пересчитана.

Тест 3 — пустые значения:

• Действие: оставить пустую запись в Profit и вычислить скользящее среднее.
• Ожидание: логика обработки пустот определена (либо пропуск, либо явная обработка NA), и результаты соответствуют ожиданиям.

1. Найдите верхнюю ячейку формулы SCAN и посмотрите сообщение ошибки (#SPILL! будет указывать причину при наведении).
2. Проверьте столбец вывода на наличие старых значений, текстов или формул.
3. Очистите мешающие ячейки (Delete) или перенесите их в другое место.
4. Если в таблице появились конфликтующие элементы, переместите таблицу или добавьте пустой буферный столбец.
5. После очистки ошибка должна исчезнуть — разлив восстановится автоматически.

Для аналитика данных:

• Использовать таблицы как источник.
• Называть столбцы читаемо.
• Вводить SCAN в отдельный диапазон и документировать логику.

Для менеджера отчётов:

• Проверить сценарии вставки/удаления строк.
• Согласовать формат вывода с требованиями потребителей отчёта.

Для аудитора/контролёра:

• Убедиться, что есть тесты на корректность при изменениях структуры.
• Проверить обработку пустых значений и сбросов по годам.

Базовый накопительный итог:

=SCAN(0, T_Profits[Profit], LAMBDA(a, b, a + b))

Скользящее среднее:

=SCAN(0, T_Profits[Profit], LAMBDA(a, b, a + b)) / SEQUENCE(ROWS(T_Profits[Profit]))

Сброс по началу года:

=SCAN(0, SEQUENCE(ROWS(T_Profits[Profit])), LAMBDA(a, i, IF(MONTH(INDEX(T_Profits[Month], i)) = 1, INDEX(T_Profits[Profit], i), a + INDEX(T_Profits[Profit], i))))

Совет: используйте короткие имена переменных (a, b, i) для компактности, или длинные (accumulator, value) для ясности — выбирайте по команде.

• Уровень 1 — «ручные формулы»: SUM с абсолютными ссылками, ручная поддержка.
• Уровень 2 — «таблицы + INDEX»: использование структурированных ссылок и INDEX для привязки.
• Уровень 3 — «динамические массивы»: SCAN + SEQUENCE + LAMBDA; автоматическая поддержка вставок/удалений.
• Уровень 4 — «ETL/Model»: перенос в Power Query/Power Pivot для больших наборов и повторяемых трансформаций.

• SCAN разливает массив из одной ячейки — удобно для накопительных объектов.
• Нельзя использовать SCAN как единственную формулу столбца внутри объекта Table; результат должен выводиться в обычный диапазон.
• SCAN доступен в Excel для Microsoft 365, на вебе, в мобильных приложениях и в отдельных версиях Excel 2024+.

Обработка данных в Excel не отправляет их автоматически в облако; однако при использовании Microsoft 365 проверьте корпоративную политику по хранению конфиденциальных данных.

flowchart TD
  A[Нужна накопительная сумма?] --> B{Данные в таблице (Table)?}
  B -->|Да| C{Нужен результат внутри Table?}
  B -->|Нет| D[Используйте SCAN в обычном диапазоне]
  C -->|Да| E[Используйте INDEX/SUM или отдельную формулу в столбце Table]
  C -->|Нет| D
  D --> F{Требуется сброс по годам?}
  F -->|Да| G[Добавьте логику IF/MONTH в LAMBDA]
  F -->|Нет| H[Просто SCAN+LAMBDA]

• SCAN — функция, возвращающая массив накопленных значений по входному массиву.
• LAMBDA — конструкция для определения пользовательского вычисления прямо в формуле.
• SEQUENCE — функция, генерирующая последовательность чисел (1,2,3…).
• Spill — поведение динамических массивов, когда результат «разливается» в соседние ячейки.

Используйте SCAN в Excel, чтобы сделать накопительные суммы устойчивыми к вставкам и удалению строк. Одна формула, меньше ошибок, меньше ручной работы.

Новая функция SCAN в Excel позволяет считать накопительные суммы без постоянного копирования формул. Вводите одну формулу в верхней ячейке — и результат автоматически разольётся по столбцу. Такой подход устойчив к вставке и удалению строк, упрощает сопровождение отчётов и уменьшает число ошибок. В статье показаны практические примеры: простая накопительная сумма, скользящее среднее и ежегодный сброс. Также описаны методика внедрения, тесты приёмки, план обработки ошибок #SPILL! и роль‑ориентированные чек‑листы.

SCAN — не всегда замена всем старым приёмам работы в Excel, но мощный инструмент для динамических, растущих и меняющихся наборов данных. Он делает расчёты более предсказуемыми и снижает количество ручных исправлений.

Microsoft 365 Personal

ОС

Windows, macOS, iPhone, iPad, Android

Бесплатная пробная версия

1 месяц

Microsoft 365 включает Office‑приложения (Word, Excel, PowerPoint) на нескольких устройствах, 1 ТБ в OneDrive и дополнительные сервисы.

100 $ на сайте Microsoft

Expand

Collapse