Как исправить ошибку «#ПЕРЕНОС!» в Excel

Вы столкнулись с ошибкой Excel «#ПЕРЕНОС!» и не знаете, что делать? Это одна из самых распространенных проблем и легко решается. Он просто указывает на то, что в ячейке недостаточно места, чтобы отобразить все. При этом формулы и данные остаются целыми и нетронутыми; Excel просто не может их правильно показать.

В основном эта ошибка возникает, когда вы вводите длинный текст или число с слишком малой шириной столбца. Хотя Excel пытается «перенести» данные, если ячейка маленькая и слова не включены, он просто показывает эту ошибку. Объединенные ячейки, которые ограничивают доступное пространство для отображения, являются еще одной распространенной причиной.

К счастью, эту проблему обычно можно решить за пару секунд. Самый простой способ — просто дважды щелкнуть на границе столбца между заголовками, чтобы он автоматически подогнать ширину под самое длинное значение. Если это не работает, можно вручную увеличить ширину столбца или высоту строки или включить перенос текста в настройках ячейки.

Способ 1: Очистка диапазона переноса

Данные, которые уже находятся в ячейках предполагаемого диапазона вывода результатов формулы массива, являются наиболее распространенной причиной ошибки «#ПЕРЕНОС!». Выделите ячейку с ошибкой, и Microsoft Excel автоматически покажет пунктирной рамкой область, в которую должны быть выведены результаты. Затем кликните по желтому значку с восклицательным знаком рядом с ячейкой и выберите «Выбрать блокирующие ячейки», чтобы сразу перейти к ячейкам с данными, которые представляют собой проблемы.

Следует нажать клавишу «Отменить», чтобы удалить содержимое блокирующих ячеек. Формула массива сразу же выведет свои результаты в освободившееся пространство. Иногда данные в блокирующих ячейках могут быть отформатированы белым цветом или в них могут быть пробелы, что придает ячейкам вид пустых. В таких ситуациях выделите весь предполагаемый диапазон переноса и воспользуйтесь комбинацией клавиш Ctrl + H, чтобы открыть окно замены. В верхнем поле введите один пробел, а в нижнем поле оставьте пустым. Затем нажмите «Заменить все», чтобы убедиться, что скрытое содержимое будет полностью очищено.

Способ 2: Ограничение размера формулы с целыми столбцами

В предыдущих версиях Excel при использовании ссылки на целый столбец в аргументах функций программа автоматически брала только значение из первой ячейки диапазона. Динамические массивы изменили это поведение, теперь Excel пытается обработать все 1 048 576 строк столбца, что приводит к выходу результатов за границы листа и появлению ошибки «#ПЕРЕНОС!». Например, формула =ВПР(A:A;A:C;2;ЛОЖЬ) в современных версиях попытается найти соответствия для всех миллиона строк столбца A.

Вместо того, чтобы ссылаться на весь столбец, можно использовать конкретный диапазон. Для этого достаточно изменить формулу на =ВПР(A2:A100;A:C;2;ЛОЖЬ), указав количество строк данных. Примените функции СЧЁТЗ для определения последней заполненной строки в диапазоне, если необходимо сохранить универсальность формулы для автоматического расширения при добавлении новых данных. Добавление символа @ перед ссылкой на диапазон, например =ВПР(@A:A;A:C;2;ЛОЖЬ), позволяет Microsoft Excel использовать только значение текущей строки, а не весь массив. Это позволяет Microsoft Excel использовать более старое поведение.

Когда формула в Microsoft Excel ссылается на ячейку, которую нельзя найти (например, если вы удалили строку, столбец или лист, на которые ссылались), появляется ошибка «#ПЕРЕНОС!» Чтобы исправить эту ошибку, необходимо определить, какие ячейки или листы были удалены или перемещены, а также обновить формулу, указав правильные ссылки. Иногда достаточно восстановить удаленные данные или изменить формулу, чтобы она ссылалась на ячейки, которые уже существуют. Главное — определить, куда пропала необходимая информация, и снова связаться.

Способ 3: Перемещение формулы в другое место

Когда результаты динамического массива выходят за пределы доступной области листа Excel, может возникать ошибка «#ПЕРЕНОС!». Программа не может вывести все результаты, если формула находится слишком близко к правому краю или нижней границе листа. Чтобы определить, выходит ли массив за границы листа, выберите ячейку с формулой и посмотрите на пунктирную границу предполагаемого диапазона переноса.

Формула должна быть перемещена справа и снизу в ячейку с большим количеством свободного пространства, просто скопировав содержимое проблемной ячейки и вставив его в новое место, после чего удалив старую формулу. Если перемещение формулы невозможно из-за структуры таблицы, пересмотрите саму формулу и попробуйте использовать дополнительные условия фильтрации, чтобы уменьшить количество результатов, которые возвращаются. Формула, работающая с большими массивами

Способ 4: Разъединение объединенных ячеек

Поскольку Microsoft Excel не способен правильно распределить данные массива по ячейкам с нестандартной структурой, динамические массивы не могут выводить результаты в объединенные ячейки. Ошибка «#ПЕРЕНОС!» и соответствующее уведомление появляются, когда путь переноса пересекается с объединенными ячейками. Кликайте по желтому значку возле ячейки с ошибкой и используйте функцию проверки ошибок, чтобы увидеть проблемные объединенные ячейки в диапазоне переноса.

Выбрав объединенные ячейки, перейдите на вкладку «Главная», найдите кнопку «Объединить и поместить в центре» в разделе «Выравнивание». Затем кликните по ней для отмены объединения.

Формула массива автоматически выведет свои результаты без ошибок после разъединения всех объединенных ячеек в предполагаемом диапазоне переноса; поэтому, если объединенные ячейки содержат важные данные, скопируйте их перед разъединением в другое место.

Способ 5: Вынос формулы из таблицы Excel

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

1. Определите, находится ли формула внутри таблицы Excel. Для этого выделите ячейку с формулой и проверьте, появляется ли на панели инструментов вкладка «Конструктор таблиц» или «Работа с таблицами».
2. 
3. Переместите формулу в любую ячейку за пределами таблицы, где у динамического массива будет достаточно свободного пространства для вывода результатов. Скопируйте формулу, выделите новую ячейку вне таблицы и вставьте содержимое.
4. 
5. Если необходимость размещения результатов именно внутри таблицы критична для вашей работы, преобразуйте таблицу в обычный диапазон. Выделите любую ячейку таблицы, перейдите на вкладку «Конструктор таблиц» и в группе «Инструменты» нажмите кнопку «Преобразовать в диапазон».
6. 
7. Подтвердите преобразование в появившемся диалоговом окне. После этого формула динамического массива сможет корректно работать в бывшей таблице, которая теперь является обычным диапазоном ячеек.
8. 

Способ 6: Работа с изменчивыми функциями

Ошибка «#ПЕРЕНОС!» может быть вызвана формами массивов с изменчивой функцией, такими как СЛЧИС, СЛМАССИВ и СЛУЧМЕЖДУ, из-за постоянного изменения размера выходного массива между циклами пересчета. Хотя Excel пытается определить размер динамического массива, если его размер изменяется с каждым пересчетом листа, программа не может стабилизировать свой вывод и возвращает ошибку.

1. Проверьте формулу на наличие изменчивых функций, которые могут влиять на размер результирующего массива. Функции СМЕЩ, ДВССЫЛ и СЕГОДНЯ также являются изменчивыми, но обычно не меняют размер массива при каждом пересчете.
2. 
3. Для стабилизации размера массива используйте дополнительные функции ограничения результата. Например, оберните формулу в функцию ПОСЛЕД с фиксированным количеством строк: =ПОСЛЕД(10) * СЛЧИС() вместо просто =СЛМАССИВ(СЛЧИС();СЛЧИС()).
4. 
5. Если формула должна возвращать разное количество результатов, примените функцию ФИЛЬТР с конкретными условиями или ЕСЛИ для ограничения выходного диапазона. Это позволит контролировать максимальный размер массива и предотвратить циклические изменения.
6. В случаях, когда изменчивые функции необходимы для расчетов, но вызывают проблемы с переносом, рассмотрите вариант использования промежуточных ячеек для фиксации результатов перед их использованием в формуле динамического массива.

Как видно, ошибка «#ПЕРЕНОС!» обычно возникает из-за небольших ошибок в формуле или структуре данных. В большинстве случаев ее легко исправить, проверив аргументы функции или изменив ширину столбца. Главное — не пугаться этого сообщения; это просто указывает на то, что программе не хватает места для отображения результатов.

Старайтесь заранее планировать расположение данных на листе, чтобы избежать этой ошибки в будущем. Если вы знаете, что результат формулы будет длинным, сразу настройте ширину ячейки. Также убедитесь, что диапазоны функций, таких как ВПР или ИНДЕКС/ПОИСКПОЗ, не ссылаются на пустые или недостаточно широкие ячейки.

Работы с Microsoft Excel часто являются процессом проб и ошибок. Понимание причин появления сообщений, таких как «#ПЕРЕНОС!», дает вам больше уверенности в себе как пользователе. Теперь вы знаете, как быстро определить и устранить причину, по которой ваши таблицы выглядят и работают правильно.