Ошибка #ССЫЛКА! в Excel (эксель): почему и как исправить

#ССЫЛКА! (по-английски #REF!) означает, что формула ссылается на ячейку, которой нет. Обычная причина в удалённой строке, столбце или листе: когда удаляют столбец C, Excel переписывает =B2*C2 как =B2*#ССЫЛКА!, и с этого момента результат всегда #ССЫЛКА!. Нажмите Ctrl+Z (на Mac Cmd+Z) сразу после удаления, чтобы вернуть и столбец, и формулу. Таблицы на этой странице показывают ошибки под английскими именами, а формулы в них можно вводить и по-русски, с точкой с запятой.

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

D2fx

=B2*#REF!

	A	B	C	D
1	Product	Price	Qty	Total
2	Apple	1.2	10	#REF!
3	Pear	1.5	20	#REF!
4	Plum	0.8	15	#REF!
5	Bread	2.4	5	#REF!

#REF! Формула ссылается на ячейку, которой не существует.В русском Excel: =B2*#REF!

Столбец с количеством удалили и ввели заново, но формула всё равно содержит #REF!: Excel никогда не восстанавливает ссылку, которая пропала. Щёлкните D2, замените #REF! на C2 и нажмите Enter. Весь столбец изменится следом, и D2 покажет 12.

Как #ССЫЛКА! попадает в формулу

Excel вписывает #ССЫЛКА! в формулу, когда исчезает ячейка, которую она использовала:

Удалять ячейки внутри диапазона безопасно: =СУММ(B2:D2) превращается в =СУММ(B2:C2), когда удаляют столбец C. Удаление первой или последней ячейки диапазона только сжимает его. Поэтому =СУММ(B2:D2) надёжнее, чем =B2+C2+D2, которая превращается в =B2+#ССЫЛКА!+C2.

Почему ВПР возвращает #ССЫЛКА!

Третий аргумент ВПР отсчитывает столбцы внутри диапазона таблицы. Если он больше числа столбцов в диапазоне, результат будет #ССЫЛКА!.

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

F2fx

=VLOOKUP(E2,A2:C6,4,FALSE)

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Pear	#REF!
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

#REF! Формула ссылается на ячейку, которой не существует.В русском Excel: =ВПР(E2;A2:C6;4;ЛОЖЬ)

В A2:C6 три столбца, поэтому 4-го нет. Замените 4 на 3, и F2 покажет 25. Чаще всего так бывает после удаления столбца из таблицы поиска: диапазон сжимается, а вписанный номер столбца нет. ПРОСМОТРX или ИНДЕКС с ПОИСКПОЗ этого избегают, потому что прямо называют столбец результата, как в =ПРОСМОТРX(E2;A2:A6;C2:C6). Остальные аргументы описаны на странице ВПР.

#ССЫЛКА! с ИНДЕКС и СМЕЩ

ИНДЕКС возвращает #ССЫЛКА!, когда номер строки или столбца выходит за её диапазон, а СМЕЩ, когда сдвиг уходит выше строки 1 или левее столбца A.

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

B2fx

=INDEX(A2:A6,6)

	A	B	C
1	Score	Result	What it asks for
2	88	#REF!	6th value of 5
3	72	95	3rd value of 5
4	95	#REF!	2 rows above A2
5	64	81	4 rows below A2
6	81

#REF! Формула ссылается на ячейку, которой не существует.В русском Excel: =ИНДЕКС(A2:A6;6)

В A2:A6 пять баллов, поэтому ИНДЕКС(A2:A6;6) даёт #ССЫЛКА!, а ИНДЕКС(A2:A6;3) возвращает 95. Строки 0 не существует, поэтому СМЕЩ(A2;-2;0) даёт #ССЫЛКА!, а СМЕЩ(A2;4;0) попадает в A6: 81. Если позиция берётся из другой формулы (ПОИСКПОЗ, СЧЁТ), сначала проверьте её. Подробнее на странице ИНДЕКС.

ДВССЫЛ тоже даёт #ССЫЛКА!, когда её текст не является допустимым адресом (=ДВССЫЛ("ZZZ1"), ведь последний столбец XFD) или указывает на закрытую книгу.

#ССЫЛКА! при копировании формулы

Относительная ссылка сдвигается вместе с формулой. Скопируйте её достаточно далеко вверх или в сторону, и ссылка уйдёт за пределы листа:

C3:  =B2*2        (one row up, one column back)
copy C3 to B2:  =A1*2
copy C3 to A2:  =#REF!*2    (there is no column before A)

То же происходит, когда формула, скопированная на другой лист или в другую книгу, указывает на ячейки, которых там нет. Закрепите знаком $ ячейки, которые не должны сдвигаться (=$B$2*2), или копируйте текст формулы из строки формул, а не саму ячейку. Про $ рассказано на странице абсолютные ссылки.

Найти и убрать все #ССЫЛКА! в книге

1. Нажмите Ctrl+F (на Mac Cmd+F), введите #ССЫЛКА!, откройте Параметры, в поле Область поиска выберите формулы и нажмите Найти все. В списке будут все формулы со сломанной ссылкой.
2. Чтобы исправить много сразу, используйте Ctrl+H (на Mac Control+H): найдите #ССЫЛКА! и замените правильной ссылкой, но только если во всех найденных местах должна стоять одна и та же ячейка.
3. Проверьте Формулы > Диспетчер имён: имя, у которого в столбце Диапазон стоит #ССЫЛКА!, ломает каждую формулу, которая его использует.
4. Если удалённых данных уже нет, а формула больше не нужна, выделите ячейки и замените формулы их значениями (скопируйте, затем Главная > Вставить > Значения). Ошибки при этом останутся ошибками, поэтому такие ячейки потом удалите.

Исправить поиск, который возвращает #ССЫЛКА!

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

F2fx

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Plum
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

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

Ваша очередь: =VLOOKUP(E2,A2:C6,4,FALSE) вернула #REF!. Напишите в F2 рабочий поиск, который возвращает остаток товара из E2.

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

Проходит любой поиск, который здесь возвращает 60 и следует за данными: ВПР со столбцом 3, =ПРОСМОТРX(E2;A2:A6;C2:C6) или =ИНДЕКС(C2:C6;ПОИСКПОЗ(E2;A2:A6;0)).