Динамические массивы в Excel: где подвох и почему формулы “разливаются” не туда. Разбор проблем с динамическими массивами в Excel: #ПЕРЕПОЛН!, блокировка диапазона, совместимость версий, конфликт с обычными формулами. Пошаговая диагностика + файл.
Мы уже разобрали ошибки, сводные, тормоза. Теперь — современный Excel.
Формула одна. Результатов — десятки. Они “разливаются” вниз сами. И в какой-то момент вы получаете:
#ПЕРЕПОЛН!
Или таблица начинает вести себя странно. Сегодня разберём реальные проблемы динамических массивов, а не рекламные преимущества.
Файл с примерами конфликтов лежит в Telegram — можно воспроизвести каждый сценарий.
Мини-кейс
Формула:
=ФИЛЬТР(A2:B100;B2:B100>10000)
Вчера работала. Сегодня — ошибка #ПЕРЕПОЛН!. Что произошло?
Причина 1. Заблокирован диапазон разлива
Динамическая формула требует свободное место ниже. Если в одной из ячеек под формулой стоит значение — появляется #ПЕРЕПОЛН!.
Проверка
Кликнуть на ячейку с ошибкой → Excel подсветит блокирующую ячейку.
Решение №1
Очистить ячейки под формулой.
Решение №2
Перенести формулу на отдельный лист.
Причина 2. Разлив внутри умной таблицы
Динамические массивы не работают внутри структурированной таблицы (Ctrl+T).
Проверка
Если формула введена в таблице и “не разливается” — причина здесь.
Решение
Вынести формулу за пределы таблицы.
Причина 3. Конфликт с обычными формулами
Если рядом есть формулы, которые ссылаются на диапазон разлива, они могут ломаться при изменении размера массива.
Проверка
Использовать символ “#” для ссылки на весь разлив:
=СУММ(E2#)
Причина 4. Совместимость версий
Если файл открывают в старом Excel (без динамических массивов), формула не работает.
Решение
Заменить ФИЛЬТР на альтернативу через ИНДЕКС + ПОИСКПОЗ или использовать Power Query.
Причина 5. Пустые строки в источнике
ФИЛЬТР возвращает пустые значения, если условие задано некорректно.
Проверка
Добавить третий аргумент:
=ФИЛЬТР(A2:B100;B2:B100>10000;"Нет данных")
Причина 6. Динамический массив изменил размер
Сегодня 10 строк. Завтра 12. А формула справа рассчитана на 10. Результат — сдвиг логики.
Решение
Всегда ссылаться на разлив через символ #.
Диагностический алгоритм
Когда динамический массив “ломается”:
- Проверить блокировку диапазона
- Проверить, не внутри ли таблицы формула
- Проверить ссылки с #
- Проверить совместимость версии
- Проверить наличие данных
Что если…
…нужно стабилизировать размер массива?
Обернуть формулу в ИНДЕКС, чтобы ограничить размер.
…массив слишком большой?
Сначала отфильтровать данные через Power Query.
Контроль результата
После исправления:
- Добавьте новую строку в источник — изменился ли разлив?
- Проверьте формулы справа — не “поехали” ли ссылки?
- Проверьте, нет ли скрытых значений под массивом.
В Telegram — файл:
— формула с #ПЕРЕПОЛН!
— конфликт с таблицей
— пример со ссылкой через #
— пример совместимости
В следующей части разберём, когда “умная таблица” делает хуже, а не лучше.