Как исправить #REF! ошибка
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще…Меньше
Ошибка #REF! отображается, когда формула ссылается на недопустимую ячейку. Чаще всего это происходит потому, что формула ссылается на ячейки, которые были удалены или заменены другими данными.
В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).
Удаление столбцов B, C или D приведет к #REF! . В этом случае мы удалим столбец C (Продажи 2007), а формула теперь будет читать =СУММ(B2,#REF!,C2). Если вы используете явные ссылки на ячейки, как это (когда вы ссылаетесь на каждую ячейку по отдельности, разделенные запятой) и удаляете строку или столбец, на которые ссылается ссылка, Excel не может разрешить их, поэтому он возвращает #REF! могут вызвать текст и специальные знаки в ячейке. Это основная причина, по которой не рекомендуется использовать явные ссылки на ячейки в функциях.
Решение
-
Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку «Отменить» на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
-
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).
В следующем примере =ВПР(A8;A2:D5;5;FALSE) вернет #REF! ошибка, так как она ищет значение, возвращаемое из столбца 5, но диапазон ссылок — A:D, то есть только 4 столбца.
Решение
Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).
В этом примере формула =INDEX(B2:E5,5;5) возвращает #REF! Ошибка, так как диапазон INDEX состоит из 4 строк на 4 столбца, но формула запрашивает возврат того, что находится в 5-й и 5-й строках.
Решение
Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.
В следующем примере функция INDIRECT пытается ссылаться на закрытую книгу, вызывая #REF! могут вызвать текст и специальные знаки в ячейке.
Решение
Откройте книгу, на который ссылается ссылка. Эта же ошибка возникает при ссылке на закрытую книгу с динамической функцией массива.
Структурированные ссылки на имена таблиц и столбцов в связанных книгах не поддерживаются.
Вычисляемые ссылки на связанные книги не поддерживаются.
Перемещение или удаление ячеек вызвало недопустимую ссылку на ячейку, или функция возвращает ошибку ссылки.
Если вы использовали ссылку OLE, которая возвращает #REF! ошибка, а затем запустите программу, которую вызывает ссылка.
Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.
Проблемы с макросами
Если макрос вводит на листе функцию, которая ссылается на ячейку над функцией, а ячейка, содержащая функцию, находится в строке 1, функция вернет #REF! поскольку нет ячеек над строкой 1. Проверьте функцию, чтобы узнать, ссылается ли аргумент на ячейку или диапазон ячеек, которые недопустимы. Для этого может потребоваться изменить макрос в редакторе Visual Basic (VBE), чтобы учесть эту ситуацию.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Исправить ошибку #ССЫЛКА! в Excel можно только непосредственно после того как она была замечена, т.к., чаще всего, она возникает, если был удален диапазон (обычно через удаление строк или столбцов) на который ссылалась некая формула.
Пример: в диапазоне B4:B9 находятся числа, которые нам нужно просуммировать. Пишем формулу =СУММ(B4:B9) и получаем их сумму. Если же удалить (предварительно выделив) строки с 4-й по 9-ю, то удалятся все значения в этих строках, а также во всех формулах, в которых этот диапазон был использован полностью (именно с 4-й по 9-ю строку) появится ошибка #ССЫЛКА!. То есть, удалив строки — получим вместо СУММ(B4:B9) уже =СУММ(#ССЫЛКА!)
Соответственно, исправить можно только сразу. Если мы видим, что в каких-то ячейках
появилось значение ошибки #ССЫЛКА! сразу же отменяем удаление строк (или столбцов).
Исправить эту ошибку через какое-то время намного сложнее, так как уже будет непонятно на какие ячейки ссылалась формула.
В некоторых случаях исправить эту ошибку невозможно даже сразу обнаружив ее. Если удалить лист, то также возникнет эта ошибка, только рядом будет еще идти адрес (ячейки или диапазона). Пример: =#ССЫЛКА!A2. Однако, так как удаление листа невозможно отменить, то, соответственно, исправить эту ошибку тоже не получится. (как вариант, можно закрыть файл без сохранения, а потом открыть его — тогда удаленный лист еще будет на своем месте и ошибка #ССЫЛКА! просто не возникнет)
Также ошибка #ССЫЛКА! может возникать, если мы пытаемся сослаться на ячейки, которых нет на листе Excel.
Пример
=СМЕЩ(A1;-1;0)
результат работы этой формулы — #ССЫЛКА! так как ячейки выше A1 в Excel не существует, а эта формула, как раз пытается сгенерировать ссылку на ячейку выше A1.
Это так, если вкратце…
При работе в Excel можно столкнуться с ошибкой #ССЫЛКА!. Эта ошибка возникает тогда, когда функция ссылается на ячейку, которой не существует. В этой статье мы разберем основные способы, как исправить эту ситуацию.
Вариант 1. Исправление формул
Представим такую ситуацию. У нас есть файл с базой данных, где хранятся данные по продажам за январь — февраль (лист в файле назван «БД янв — фев») и файл со сводными данными, который суммирует выручку по месяцам. Мы открыли файл с базой данных, внесли в него данные по продажам за март и внимание(!) переименовали лист в «БД янв — март», после этого открываем файл со сводом и видим там вместо данных по выручке за январь, февраль, что мы делали раньше, ошибку #ССЫЛКА!. Это произошло из-за того, что функция ссылается на данные в листе «БД янв — фев», которого нет, так как мы его переименовали.
Что бы исправить эту ситуацию, закрываем файл со сводом, не сохраняя изменения (!), если вы сохраните изменения — то формулы будут навсегда испорчены и вам придется писать их заново. Далее в файле с базой данных переименовываете лист так, как он назывался ранее, т.е. «БД янв — фев» и снова открываете файл со сводом. Ошибка пропала, функции снова работают. Теперь при открытых двух файлах, вы можете переименовать лист в базе данных и ошибка не появится, так как Excel изменит ссылки в функциях (он умеет это делать только в открытых книгах).
Вариант 2. Функция ЕСЛИОШИБКА
При помощи функции ЕСЛИОШИБКА можно обработать ошибки, которые возникают при написании формул, в том числе ошибку #ССЫЛКА!.
Синтаксис функции ЕСЛИОШИБКА следующий: первым аргументом идет функция, которая может вызвать ошибку, вторым аргументом функционал, который будет вызван, если ошибка произошла.
В данном случае мы оставили напоминание себе о том, что листы, при закрытой книге со сводом переименовывать нельзя. Это позволит быстро сориентироваться в ситуации и исправить формулы, как описано в варианте 1.
Спасибо, что прочитали статью. Надеюсь она была для вас полезной.
При работе в Excel вы можете столкнуться с ошибкой #REF !. Эта ошибка возникает, когда функция ссылается на несуществующую ячейку. В этой статье мы рассмотрим основные способы выхода из этой ситуации.
Вариант 1. Исправление формул
Представим себе следующую ситуацию. У нас есть файл с базой данных, в которой хранятся данные о продажах за январь — февраль (лист в файле называется «БД январь — февраль») и файл со сводными данными, в которых суммируется доход за несколько месяцев. Мы открыли файл с базой данных, ввели данные о продажах за март и внимание (!) Переименовали лист в «БД январь — март», затем открываем файл со сводкой и видим там вместо данных о выручке за январь, февраль, что мы и делали ранее, ошибка # ССЫЛКА !. Это произошло из-за того, что функция ссылается на данные в таблице «БД Янв — Фев», которой не существует, поскольку мы ее переименовали.
Чтобы исправить эту ситуацию, закройте файл сводки без сохранения изменений (!). Если вы сохраните изменения, формулы будут безвозвратно повреждены, и вам придется их переписать. Затем в файле с базой данных переименуйте лист так, как он назывался ранее, например «БД, январь — февраль» и повторно откройте файл в Vault. Ошибка исчезла, функции снова работают. Теперь, когда два файла открыты, вы можете переименовать лист в базе данных, и ошибка не появится, так как Excel изменит ссылки в функциях (это можно сделать только в открытых книгах).
Вариант 2. Функция ЕСЛИОШИБКА
Используя функцию SEERROR, вы можете обрабатывать ошибки, возникающие при написании формул, включая ошибку #REF!.
Синтаксис функции SEERROR следующий: первый аргумент — это функция, которая может вызвать ошибку, второй аргумент — это функция, которая будет вызываться в случае возникновения ошибки.
В этом случае мы вспомнили, что листы с закрытой книгой с хранилищем переименовать нельзя. Это позволит быстро сориентироваться в ситуации и исправить формулы, как описано в варианте 1.
Спасибо, что прочитали статью. Надеюсь, это было вам полезно.
В ячейках стоят формулы, тянущие данные из других книг Excel, в некоторых случаях чтобы оставить формулы , но занулить значения , ссылка делается на пустую ячейку. Проблема в том , что после обновления файлов появляется ошибка #ссылка! , если перейти по ссылке то , после окртыия файлов ошибка исчезает. |
|
LVL Пользователь Сообщений: 903 |
вероянее всего после открытия книги Вы пытаетесь пересчитать формулы вручную, а книги на которые есть ссылки закрыты, соответственно ошибка. Поменяйте режим пересчёта на авто, тогда ссылки будут обновляться корректно, но только по запросу |
бобочек Пользователь Сообщений: 14 |
Ошибка «ссылка!» появляется при копировании ячейки из одного листа в другой на одной книге (Excel2010). Активных надстроек нет, Chrome отключен. |
Bema Пользователь Сообщений: 6903 |
Файл бы увидеть. Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл |
БМВ Модератор Сообщений: 21624 Excel 2013, 2016 |
Не посомтрел что ТС иной Изменено: БМВ — 09.03.2018 14:38:16 По вопросам из тем форума, личку не читаю. |
бобочек Пользователь Сообщений: 14 |
Ячейка С15 =(C3*КОРЕНЬ(2)-C4/2)*C8/C29/K2 превращается в =(#ССЫЛКА!*КОРЕНЬ(2)-#ССЫЛКА!/2)*#ССЫЛКА!/C18/#ССЫЛКА! Ну и так со всеми остальными |
БМВ Модератор Сообщений: 21624 Excel 2013, 2016 |
бобочек, изучайте относительные и абсолютные ссылки и изменении адресов при копировании содержимого формул. P.S. Z. да согласен, сбила с толку 8 ка в числе. и 2013 со слепу за 2018 принял Изменено: БМВ — 09.03.2018 14:49:01 По вопросам из тем форума, личку не читаю. |
Z Пользователь Сообщений: 6111 Win 10, MSO 2013 SP1 |
#8 09.03.2018 14:45:36
off Да и «возраст» темы почтительный — стоило ли тревожить?!. «Ctrl+S» — достойное завершение ваших гениальных мыслей!.. |
||
бобочек Пользователь Сообщений: 14 |
Как-то раньше не было таких проблем, сейчас нашел только то, что если ячейку С15 копирую на другой лист на тот же С15, то все нормально, после этого даже копия по другому адресу проходит, если же сразу копировать не на С15, то идут неустранимые ошибки ССЫЛКА! Если Вы о адресации с $, то я не знаю как это поможет, неужели перед копированием надо в исходной формуле вставлять $ а потом это чистить ? Изменено: бобочек — 09.03.2018 15:18:11 |
vikttur Пользователь Сообщений: 47199 |
Показать пример с проблемой — намного эффективнее, чем кричать «Караул!» |
бобочек Пользователь Сообщений: 14 |
Еще примеры : Исходная С16 =C2*КОРЕНЬ(2)*C8/C29/K2 перенос на другой лист на С16 =C2*КОРЕНЬ(2)*C8/C29/K2 |
БМВ Модератор Сообщений: 21624 Excel 2013, 2016 |
бобочек, 22-18=4, 4-4=0 адрес со строкой 0 не существует. Еще нужны пояснения? По вопросам из тем форума, личку не читаю. |
бобочек Пользователь Сообщений: 14 |
#13 09.03.2018 15:51:21 Кажется дошло, спасибо ! В первом примере G4 должен был стать D0, а это низя. Изменено: бобочек — 14.03.2018 16:10:47 |