Ошибка #ПЕРЕНОС! в Excel (эксель): причины и решение

#ПЕРЕНОС! (по-английски #SPILL!) означает, что формула возвращает несколько значений (список или таблицу), а Excel некуда их записать: хотя бы одна ячейка в диапазоне, который нужен результату, не пуста. Таблицы на этой странице показывают ошибки под английскими именами. =УНИК(A2:A6) ниже нужны три ячейки, с C2 по C4, а в C4 стоит x. Удалите C4, и список появится. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой: =ИНДЕКС(СОРТ(A2:A7);1).

ФормулыСбросить

C2fx

=UNIQUE(A2:A6)

	A	B	C
1	Product		Unique list
2	Apple		#SPILL!
3	Pear
4	Apple		x
5	Plum
6	Pear

#SPILL! Результату нужно больше свободных ячеек. Очистите ячейки, которые ему мешают.В русском Excel: =УНИК(A2:A6)

Щёлкните C2: подпись под сеткой объясняет, что не так. Затем щёлкните C4 и нажмите Delete. Три товара разольются в C2:C4, а рамка отметит диапазон переноса. Введите что-нибудь в C3, и ошибка вернётся. Excel работает так же: функции, которые возвращают массивы, например ФИЛЬТР, УНИК, СОРТ, ПОСЛЕД и ТЕКСТРАЗД, работают, только когда весь их диапазон переноса свободен. Им нужен Excel 2021 или новее (ТЕКСТРАЗД: Microsoft 365 или Excel 2024); старые версии показывают для них #ИМЯ? (по-английски #NAME?), поэтому #ПЕРЕНОС! там не бывает (сама функция описана на странице УНИК).

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

1. Щёлкните ячейку с #ПЕРЕНОС!. В Excel пунктирная рамка покажет диапазон, который хочет заполнить результат.
2. Нажмите значок предупреждения рядом с ячейкой и выберите Select Obstructing Cells (выделить мешающие ячейки). Excel выделит все ячейки, которые мешают.
3. Нажмите Delete или перенесите эти ячейки в другое место (вырежьте и вставьте).

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

#ПЕРЕНОС!, когда ячейки выглядят пустыми

Самый запутанный случай: диапазон переноса выглядит пустым, но Excel всё равно пишет #ПЕРЕНОС!. Ячейка с одним пробелом или формула, которая возвращает пустую строку "", не пусты и блокируют перенос так же, как значение.

ФормулыСбросить

A2fx

=SEQUENCE(3)

	A	B	C
1	Numbers		Numbers
2	#SPILL!		#SPILL!
3
4

#SPILL! Результату нужно больше свободных ячеек. Очистите ячейки, которые ему мешают.В русском Excel: =ПОСЛЕД(3)

A2 нужен диапазон A2:A4, а в A4 стоит пробел. C2 нужен C2:C3, а в C3 стоит ="". Щёлкните A4 или C3, чтобы увидеть, что там, удалите это, и числа разольются. В Excel Select Obstructing Cells находит такие ячейки, даже когда в них ничего не видно. Белый текст на белой заливке прячется так же.

#ПЕРЕНОС!, когда одна формула разливается в другую

Две разливающиеся формулы могут мешать друг другу, или формула, которую кто-то ввёл ниже, может оказаться внутри диапазона переноса формулы сверху.

ФормулыСбросить

D2fx

=FILTER(A2:A6,B2:B6="IT")

	A	B	C	D	E
1	Name	Dept		IT	Sales
2	Ana	IT		#SPILL!	Ben
3	Ben	Sales			Dee
4	Cy	IT		5
5	Dee	Sales
6	Eve	IT

#SPILL! Результату нужно больше свободных ячеек. Очистите ячейки, которые ему мешают.В русском Excel: =ФИЛЬТР(A2:A6;B2:B6="IT")

Списку IT нужны три ячейки, D2:D4, а в D4 стоит формула СЧЁТЗ. Списку Sales в E2 хватает двух нужных ему ячеек, поэтому он работает. Перенесите формулу СЧЁТЗ в D6 (или в любую ячейку под списком), и имена IT разольются. Оставляйте списку место для роста: если позже добавится четвёртый сотрудник IT, результату понадобится ещё одна ячейка.

#ПЕРЕНОС! с ВПР и целыми столбцами

Частая причина в формулах, написанных для старого Excel, это искомое значение в виде целого столбца:

=VLOOKUP(A:A,Prices!A:B,2,FALSE)      #SPILL!  (one result for every row of the sheet)
=VLOOKUP(A2,Prices!A:B,2,FALSE)       one result, fill it down
=VLOOKUP(A2:A100,Prices!A:B,2,FALSE)  100 results that spill
=VLOOKUP(@A:A,Prices!A:B,2,FALSE)     one result, the value on the formula's own row

В русском Excel вторая формула записывается как =ВПР(A2;Prices!A:B;2;ЛОЖЬ). В A:A 1 048 576 ячеек, поэтому первая формула запрашивает 1 048 576 результатов, а начиная со строки 2 строк уже не хватает: меню предупреждения сообщает, что диапазон переноса выходит за край листа. Этот приём пришёл из старого Excel, который молча брал только значение из строки самой формулы. Excel 365 сохраняет такое поведение в старых книгах и показывает формулу как =ВПР(@A:A;...), но та же формула, введённая заново, разливается на весь столбец. То же происходит с =A:A*2 и любой другой формулой, которая считает что-то по целому столбцу. Используйте одну ячейку и протяните вниз, диапазон реального размера или @. Подробнее о самом поиске на странице ВПР.

Один результат на строку вместо переноса

Формула, которая считает что-то по диапазону, тоже разливается. Часто это и нужно, а иногда лучше иметь по формуле на строку.

ФормулыСбросить

C2fx

=B2:B5*1.1

	A	B	C	D
1	Month	Sales	Spilled +10%	Filled +10%
2	Jan	100	110	110
3	Feb	120	132	132
4	Mar	90	99	99
5	Apr	140	154	154

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =B2:B5*1,1

Оба столбца показывают 110, 132, 99 и 154. В C2 одна формула, а C3:C5 это её перенос: щёлкните C3, и подпись под сеткой скажет, что значение перенесено из C2. Введите что-нибудь в C4, и C2 превратится в #SPILL!. D2:D5 это четыре отдельные формулы, поэтому каждую ячейку можно менять отдельно, и ничто не может им помешать. Используйте второй вариант, когда люди будут вписывать что-то поверх отдельных результатов.

#ПЕРЕНОС! в таблице, с объединёнными ячейками или неизвестного размера

Эти причины зависят от книги, а не от формулы:

• Внутри таблицы Excel (Вставка > Таблица): таблицы не поддерживают разлитые результаты, поэтому ФИЛЬТР или УНИК в столбце таблицы показывает #ПЕРЕНОС!. Поставьте формулу вне таблицы или выделите таблицу и выберите Конструктор таблиц > Преобразовать в диапазон.
• Объединённые ячейки в диапазоне переноса: выделите их и выберите Главная > Объединить и поместить в центре > Отменить объединение ячеек или перенесите формулу.
• Диапазон переноса неизвестен: размер результата меняется при каждом пересчёте, как в =ПОСЛЕД(СЛУЧМЕЖДУ(1;10)). Excel отказывается разливать результат изменчивого размера. Задайте фиксированный размер.
• Диапазон переноса слишком велик или выходит за край листа: результат вышел бы за последнюю строку или столбец. Недостаточно памяти: массив слишком большой для вычисления. Во всех трёх случаях уменьшите диапазоны, обычно с целых столбцов до реальных данных.

Вернуть одно значение, чтобы ничто не мешало

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

ФормулыСбросить

E2fx

	A	B	C	D	E
1	Product				Different products
2	Apple
3	Pear
4	Apple				x
5	Plum
6	Pear
7	Apple

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: E2 должна показать, сколько разных товаров в A2:A7. Простая =UNIQUE(A2:A7) разлилась бы на x в E4. Напишите в E2 одну формулу, которая возвращает количество.

ПроверитьПоказать ответ

СЧЁТЗ считает значения, которые возвращает УНИК, и выдаёт одно число. Та же идея работает с =ИНДЕКС(СОРТ(A2:A7);1) для первого значения отсортированного списка, с =ИНДЕКС(ФИЛЬТР(...);1) для первого совпадения или с =СУММ(ФИЛЬТР(...)) для итога.