Перейти к основному контенту
Office

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

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

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

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

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

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

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

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

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

#

Варианта

Формула

1

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

Используйте функции = ВПР (a2: A7, А:К, 2, ложь), чтобы возвращать динамический массив, который не приведет к #SPILL! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

= ВПР (a2: A7; а:к; 2; ложь)

2

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

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

= ВПР (a2; а:к; 2; ложь)

3

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

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

= ВПР (@ а:а; а:к; 2; ложь)

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

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

См. также

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

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

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

Функция СОРТ

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

Функция УНИК

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

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

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

Примечание:  Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

×