Ошибки #ПЕРЕНОС! ошибка — выходит за границу листа

Формула, которую вы пытаетесь ввести, будет продлена за пределами диапазона на листе. Повторите попытку с небольшим диапазоном или массивом.

В приведенном ниже примере при перемещении формулы в ячейку F1 будет устранена ошибка, а формула будет перенесена правильно.

Ошибки #ПЕРЕНОС! ошибка, при нажатии которой = SORT (D:D) в ячейке F2 будет выполняться за границами книги. Переместить его в ячейку F1, и он будет работать правильно.

Распространенные причины: полные ссылки на столбцы

Часто неправильно понимается метод создания формул ВПР с помощью указания аргумента lookup_value . Перед тем как динамические функции, поддерживающие массивы , будут считать значение в той же строке, что и в формуле, и игнорировать другие, так как ВПР предполагает только одно значение. С появлением динамических массивов Excel считает все значения, предоставленные для lookup_value. Это означает, что если в качестве аргумента lookup_value указан весь столбец, Excel попытается просмотреть все значения 1 048 576 в столбце. После того как вы закончите, она попытается перенести их в сетку и, скорее чем, будет достигнут конец сетки, в результате чего #SPILL! ошибка "#ЗНАЧ!".  

Например, при помещении в ячейку E2, как в приведенном ниже примере, формула = ВПР (A:A, A:C, 2, ложь) ранее проводила только к просмотру идентификатора в ячейке A2. Однако в динамическом массиве Excel формула вызовет #SPILL! ошибка из-за того, что Excel пройдет весь столбец, возвратит 1 048 576 результаты и найдет конец сетки Excel.

Ошибки #ПЕРЕНОС! ошибка, связанная с = ВПР (A:A, A:D, 2, ложь) в ячейке E2, так как результаты будут перенесены за край листа. Переместить формулу в ячейку E1, и она будет работать правильно.

Существует три простых способа решения этой проблемы:

#

Варианта

Формула

1

Сослаться только на интересующие вас значения подстановки. Этот стиль формулы будет возвращать динамический массив, но не работает с таблицами Excel

Используйте функции = ВПР (a2: A7, A:C, 2, ложь), чтобы возвращать динамический массив, который не приведет к #SPILL! ошибка "#ЗНАЧ!".

= ВПР (a2: A7; A:C; 2; ложь)

2

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

Используйте традиционный класс ВПР с одной lookup_valueной ссылкой: = ВПР (a2; A:C, 32, ложь). Эта формула не будет возвращать динамический массив, но может использоваться с таблицами Excel.

= ВПР (a2; A:C; 2; ложь)

3

Запрос на то, что Excel выполняет неявное пересечение с помощью оператора @, а затем копирует формулу вниз. Этот стиль формулы работает в таблицах, но не будет возвращать динамический массив.

Используйте оператор @ и скопируйте: = ВПР (@A: A, A:C, 2, ложь). Этот стиль ссылки будет работать в таблицах, но не будет возвращать динамический массив.

= ВПР (@A: A; A:C; 2; ложь)

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Ошибки #ПЕРЕНОС! в Excel

Динамические массивы и поведение рассеянного массива

Оператор неявного пересечения: @

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×