Формула разливаемого массива, которую вы пытаетесь ввести, выходит за пределы диапазона листа. Повторите попытку с меньшим диапазоном или массивом.
В следующем примере при перемещении формулы в ячейку F1 ошибка устранена, и формула будет правильно разлита.
Распространенные причины: полные ссылки на столбцы
Существует часто неправильно понятый метод создания формул ВПР путем указания аргумента lookup_value . До excel с поддержкой динамического массива Excel рассматривал только значение в той же строке, что и формула, и пропускал все остальные, так как ВПР ожидал только одно значение. С появлением динамических массивов Excel учитывает все значения, предоставленные lookup_value. Это означает, что если в качестве аргумента lookup_value указан весь столбец, Excel попытается найти все 1 048 576 значений в столбце. Как только это будет сделано, он попытается разлить их в сетку, и, скорее всего, ударит в конец сетки, что приведет к #SPILL! .
Например, при размещении в ячейке E2 формулы =ВПР(A:A;A:C;2;ЛОЖЬ) (как в примере ниже) ранее выполнялся поиск ИД только в ячейке A2. Однако в динамическом массиве Excel формула вызывает ошибку #ПЕРЕНОС!, так как Excel выполняет поиск во всем столбце, возвращает 1 048 576 результатов и достигает конца сетки Excel.
Эту проблему можно устранить 3 простыми способами:
| # | Способ | Формула |
|---|---|---|
| 1 | Ссылайтесь только на нужные значения поиска. Этот стиль формулы возвращает динамический массив, но не работает с таблицами Excel.
|
=ВПР(A2:A7;A:C;2;ЛОЖЬ) |
| 2 | Сошлитесь на значение в той же строке, а затем скопируйте формулу. Этот традиционный стиль формулы работает в таблицах, но не возвращает динамический массив.
|
=ВПР(A2;A:C;2;ЛОЖЬ) |
| 3 | Создайте в Excel запрос неявного пересечения с помощью оператора @, а затем скопируйте формулу. Этот стиль формулы работает в таблицах, но не возвращает динамический массив.
|
=ВПР(@A:A;A:C;2;ЛОЖЬ) |
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.