Почему ЕОШИБКА не работает с массивами в Excel

Q: Чем заменить ЕОШИБКА при работе с массивами?

Лучше использовать связку СУММПРОИЗВ(--ЕОШИБКА(диапазон))>0 или современную функцию ЕСЛИОШИБКА для мгновенной замены некорректных значений.

Формула ЕОШИБКА (ISERROR) в Excel отлично справляется с проверкой одиночных ячеек, но при попытке передать ей диапазон данных пользователи часто сталкиваются с некорректной работой: формула возвращает только одно значение, выдает ошибку #ЗНАЧ! или ведет себя непредсказуемо. Это происходит из-за особенностей обработки массивов в разных версиях табличного процессора.

Содержание

Видеоинструкция
Почему ЕОШИБКА не работает с массивами напрямую
Пошаговое руководство: как заставить ЕОШИБКА работать с массивами
Шаг 1. Использование формулы массива (для старых Excel)
Шаг 2. Агрегирование результата через СУММПРОИЗВ
Шаг 3. Использование функции ЕСЛИОШИБКА для обработки
Частые ошибки / Устранение неполадок
Часто задаваемые вопросы

Видеоинструкция

Почему ЕОШИБКА не работает с массивами напрямую

По умолчанию функция ЕОШИБКА ожидает на вход один аргумент. Если передать ей диапазон (например, A1:A10), старые версии Excel используют метод неявного пересечения и проверяют только одну ячейку из строки, где введена формула. В новых версиях Excel 365 и 2021 формула вернет динамический массив из значений ИСТИНА/ЛОЖЬ, что тоже не всегда удобно, если вам нужен один итоговый результат проверки всего диапазона.

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

Пошаговое руководство: как заставить ЕОШИБКА работать с массивами

Шаг 1. Использование формулы массива (для старых Excel)

Если у вас Excel 2019 или более ранняя версия, формулу необходимо вводить как классическую формулу массива. Введите формулу:

=ЕОШИБКА(A1:B10)

Но вместо обычного нажатия клавиши Enter, обязательно нажмите комбинацию клавиш Ctrl + Shift + Enter. Excel автоматически обернет формулу в фигурные скобки.

Шаг 2. Агрегирование результата через СУММПРОИЗВ

Чтобы получить одно логическое значение (есть ли вообще ошибки в проверяемом диапазоне), объедините ЕОШИБКА с функцией СУММПРОИЗВ. Это работает во всех версиях Excel без нажатия сложных клавиш:

=СУММПРОИЗВ(--ЕОШИБКА(A1:A10))>0

Двойное отрицание -- преобразует логические ИСТИНА/ЛОЖЬ в 1 и 0, а СУММПРОИЗВ складывает их. Если сумма больше нуля, значит, в массиве есть ошибки.

Шаг 3. Использование функции ЕСЛИОШИБКА для обработки

Часто вместо проверки массива на ошибки требуется сразу заменить их на пустоту или нули. Для этого лучше использовать современную альтернативу:

=ЕСЛИОШИБКА(A1:A10; '')

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

Частые ошибки / Устранение неполадок

Проблема: Формула возвращает только ошибку #ЗНАЧ!.

Решение: Вы забыли нажать Ctrl + Shift + Enter в старой версии Excel. Перейдите в режим редактирования ячейки с помощью клавиши F2 и нажмите это сочетание.
Проблема: Динамический массив перекрывает другие данные и выдает ошибку #ПЕРЕНОС!.

Решение: Убедитесь, что под ячейкой с формулой достаточно пустых строк и столбцов для вывода результатов. Если вы случайно удалили важную формулу и столкнулись с тем, что не отображается кнопка Отменить в Excel: решение этой проблемы поможет вернуть данные.
Проблема: Нужно продублировать логику проверки на весь столбец.

Решение: Если вы хотите протянуть формулу, изучите, как автоматически дублировать ячейки в Excel.

Дополнительно

Стандартная комбинация ИЛИ(ЕОШИБКА(диапазон)), введенная как формула массива, остается самым надежным и обратно-совместимым способом проверки диапазона на наличие любых ошибок в старых версиях Excel, если вам не хочется использовать СУММПРОИЗВ.